二月 12, 2025
摘要：在本教程中，您将学习如何在 PostgreSQL 中优化索引扫描。
目录
介绍
人们在查看查询计划时，经常会出现的一个问题是：检查到所有的扫描都使用了索引，因此会认为查询可能已经尽可能的快了。
很多人都知道，一个 Seq Scan 下面 Rows Removed by Filter 的数字大，表明可以用一个索引进行优化。但是，对于带有大量过滤行数的其他扫描，同样可以用一个索引来加快速度，可以采用更合适的索引来加快速度！虽然我们经常会看到索引过滤数显示为 “Rows Removed by Filter”，但遗憾的是，有些情况目前 EXPLAIN 还没有报告出来。
在本教程中，我们将介绍几个示例、一些需要注意的指标，以及如何加快速度。
请注意，还有其他的场景，可以进一步优化索引的使用。一个这样的例子是，当 Rows Removed by Index Recheck 的数字很大时。
初始设置
让我们创建一个简单的示例表，来进行演示：
CREATE TABLE example (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
a smallint NOT NULL,
b smallint NOT NULL);
INSERT INTO example (a, b)
SELECT random (1, 1_000), random (1, 1_000)
FROM generate_series(1, 1_000_000);
VACUUM ANALYZE example;
SELECT * FROM example LIMIT 5;
id | a | b
----+-----+-----
1 | 803 | 627
2 | 978 | 702
3 | 15 | 506
4 | 966 | 335
5 | 247 | 172
因此，我们总共有了 100 万行，有一个整型主键列，还有额外的两列 a 和 b，其中包含了 1 到 1000 之间的随机整数。
注意 Rows Removed by Filter
让我们看一下几种索引的方式，用于优化一个同时筛选 a 和 b 的简单查询：
CREATE INDEX example_a_idx ON example (a);
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
SELECT id FROM example WHERE a = 42 AND b = 42;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on public.example (cost=11.86..2542.34 rows=1 width=8) (actual time=4.024..4.489 rows=1 loops=1)
Output: id
Recheck Cond: (example.a = 42)
Filter: (example.b = 42)
Rows Removed by Filter: 1016
Heap Blocks: exact=925
Buffers: shared hit=928
-> Bitmap Index Scan on example_a_idx (cost=0.00..11.86 rows=991 width=0) (actual time=0.407..0.407 rows=1017 loops=1)
Index Cond: (example.a = 42)
Buffers: shared hit=3
Planning Time: 0.143 ms
Execution Time: 4.538 ms
使用在 a 列上的一个索引，Postgres 选择了执行 Bitmap Index Scan，以查找 a=42 的行，然后对包含这些行的页面进行 Bitmap Heap Scan，筛选出 b 等于 42 的行。
虽然这总共只花了大约 5 毫秒。我们可以看到一个过滤条件删除了 1016 行，但最终只返回了 1 行 — 并不算很高效！
另一个线索（依据 Buffers 行）是 Postgres 总共必须读取 928 个块（约 7MB），其中除了 3 个之外，其余所有块都来自 Bitmap Heap Scan 阶段。
选择普通索引扫描
普通的索引扫描会比这种位图扫描更好吗？
让我们来尝试关闭位图扫描，然后再次运行查询：
SET enable_bitmapscan = off;
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
SELECT id FROM example WHERE a = 42 AND b = 42;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Index Scan using example_a_idx on public.example (cost=0.42..3655.99 rows=1 width=8) (actual time=1.438..1.682 rows=1 loops=1)
Output: id
Index Cond: (example.a = 42)
Filter: (example.b = 42)
Rows Removed by Filter: 1016
Buffers: shared hit=928
Settings: enable_bitmapscan = 'off'
Planning Time: 0.144 ms
Execution Time: 1.706 ms
这样更快了，大约 2 毫秒，但我们仍然可以看到这 1016 行被一个过滤条件删除了。Buffers 行还显示出，我们仍然读取了 928 个块。所以，这两点都是一个很好的迹象，表明我们还可以优化得更好。
建立多个索引
如果我们在 b 列上也有一个索引呢？
RESET enable_bitmapscan;
CREATE INDEX example_b_idx ON example (b);
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
SELECT id FROM example WHERE a = 42 AND b = 42;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on public.example (cost=24.00..28.01 rows=1 width=8) (actual time=0.674..0.676 rows=1 loops=1)
Output: id
Recheck Cond: ((example.a = 42) AND (example.b = 42))
Heap Blocks: exact=1
Buffers: shared hit=8
-> BitmapAnd (cost=24.00..24.00 rows=1 width=0) (actual time=0.652..0.653 rows=0 loops=1)
Buffers: shared hit=7
-> Bitmap Index Scan on example_a_idx (cost=0.00..11.86 rows=991 width=0) (actual time=0.259..0.259 rows=1017 loops=1)
Index Cond: (example.a = 42)
Buffers: shared hit=3
-> Bitmap Index Scan on example_b_idx (cost=0.00..11.89 rows=995 width=0) (actual time=0.208..0.208 rows=991 loops=1)
Index Cond: (example.b = 42)
Buffers: shared hit=4
Planning Time: 0.143 ms
Execution Time: 0.706 ms
我们现在减少到了不到 1 毫秒，但至关重要的是，Buffers 行的数字要小多了（8 个块，即 64KB）！这一次，Postgres 在对每个结果中的匹配行执行交集运算之前，先单独地扫描了每个索引，以查找同时匹配两个条件的行。
采用多列索引
虽然没有再出现明确的 “Rows Removed by Filter” 字段，但这些位图索引扫描还是读取了一些最终未返回的索引条目。让我们来看看是否还可以优化得更好，通过尝试在 (a, b) 上创建一个复合索引，它将会在单个索引结构中，将我们需要的条目彼此相邻地排序：
CREATE INDEX example_a_b_idx ON example (a, b);
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
SELECT id FROM example WHERE a = 42 AND b = 42;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Index Scan using example_a_b_idx on public.example (cost=0.42..8.45 rows=1 width=8) (actual time=0.032..0.033 rows=1 loops=1)
Output: id
Index Cond: ((example.a = 42) AND (example.b = 42))
Buffers: shared hit=4
Planning Time: 0.160 ms
Execution Time: 0.058 ms
选择只用索引的扫描
我们减少到了总共 4 次缓冲页命中数，而且速度要快得多！最后一次尝试，让我们尝试在 (a, b, id) 上创建一个复合索引，以尝试获得只用索引的扫描：
CREATE INDEX example_a_b_id_idx ON example (a, b, id);
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
SELECT id FROM example WHERE a = 42 AND b = 42;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using example_a_b_id_idx on public.example (cost=0.42..4.44 rows=1 width=8) (actual time=0.029..0.031 rows=1 loops=1)
Output: id
Index Cond: ((example.a = 42) AND (example.b = 42))
Heap Fetches: 0
Buffers: shared hit=4
Planning Time: 0.115 ms
Execution Time: 0.046 ms
尽管避免了对堆表的访问，我们仍然总共读取了 4 个块（可能是因为索引稍微大了），因此查询并没有快多少。
从第一个查询计划（已经非常快了，并且使用一个索引）来看，我们仍然设法在执行时间和读取的块数方面，实现了几个数量级的提升，主要是因为，通过尝试准确地获取到我们需要的数据，而无需读入和过滤行。
因此，回顾一下，无论扫描类型如何，如果 Postgres 必须读取的数据超出了其需要的数据，而必须将其过滤掉，则可能会有更有效的方法。请注意 Rows Removed by Filter。
注意循环次数
重要的一点是，Rows Removed by Filter 字段是每次循环的平均值，四舍五入到最接近的整数。在上面的示例中，我们总是看到 “loops=1”，所以这无关紧要，但在其他查询中，尤其是那些涉及 Nested Loops 的查询，这可能是一个巨大的影响因素。
想象一下，你看到 “actual […] rows=1 loops=10000” 和 “Rows Removed by Filter: 5”。这意味着返回了大约 10000 行，但为了执行该操作，会另外读取和筛选掉 50000 行。5 比 1 已经不是一个很好的效率比，但是当乘以 10000 时，可能节省出来的时间会很有潜力！
因此，如果你看到 Rows Removed by Filter 的行数很小，但 loops 的次数很大，那么可能仍然有很大的提升空间（同样可以使用更好的索引来实现）。
调整索引列顺序
由于 PostgreSQL 不支持 Index Skip Scan，因此 B 树索引中的列顺序对于性能极为重要。
筛选单个列时的列顺序
一个常见的示例是，在 (a, b) 上的索引不能高效地用于只筛选 b 列的查询。为了演示，让我们删除只在 b 列上创建的另一个索引，并关闭顺序扫描：
DROP INDEX example_b_idx;
SET enable_seqscan = off;
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
SELECT id FROM example WHERE b = 42;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on public.example (cost=17132.67..19669.35 rows=995 width=8) (actual time=38.616..39.629 rows=991 loops=1)
Output: id
Recheck Cond: (example.b = 42)
Heap Blocks: exact=909
Buffers: shared hit=3308
-> Bitmap Index Scan on example_a_b_idx (cost=0.00..17132.42 rows=995 width=0) (actual time=38.443..38.443 rows=991 loops=1)
Index Cond: (example.b = 42)
Buffers: shared hit=2399
Settings: enable_seqscan = 'off'
Planning Time: 0.304 ms
Execution Time: 39.736 ms
这样确实使用到了我们的 example_a_b_idx 索引，但花费了近 40 毫秒，总共读取了 3308 个块（约 26MB）。
让我们尝试添加一个顺序为 (b, a) 的索引，看看它在用于同一个查询时的效率有多高：
RESET enable_seqscan;
CREATE INDEX example_b_a_idx ON example (b, a);
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
SELECT id FROM example WHERE b = 42;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on public.example (cost=20.14..2556.81 rows=995 width=8) (actual time=0.649..3.212 rows=991 loops=1)
Output: id
Recheck Cond: (example.b = 42)
Heap Blocks: exact=909
Buffers: shared hit=915
-> Bitmap Index Scan on example_b_a_idx (cost=0.00..19.89 rows=995 width=0) (actual time=0.393..0.394 rows=991 loops=1)
Index Cond: (example.b = 42)
Buffers: shared hit=6
Planning Time: 0.232 ms
Execution Time: 3.406 ms
这花了不到 4 毫秒的时间，总共读取了 915 个块（约 7MB） — 好多了！
遗憾的是，Postgres 在上面的效率较低的计划中没有报告出任何的 “Rows Removed by Filter”，尽管这是在位图索引扫描期间实际发生了的事情。我们目前也无法从 Index Cond 行中判断出它是否被高效地使用了，但执行时间、成本和缓冲页数都提供了很好的线索。
筛选多个列时的列顺序
列顺序对于要筛选一个索引中所有条件的查询也非常重要。
一个简洁的示例是，如果其中一个筛选条件是一个范围条件的时候。为了演示，让我们尝试一个查询，该查询应该会顺利地使用到我们在 (b, a) 上建立的新索引：
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
SELECT id FROM example WHERE b = 42 AND a < 100;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on public.example (cost=5.44..362.73 rows=99 width=8) (actual time=0.128..0.502 rows=116 loops=1)
Output: id
Recheck Cond: ((example.b = 42) AND (example.a < 100))
Heap Blocks: exact=115
Buffers: shared hit=118
-> Bitmap Index Scan on example_b_a_idx (cost=0.00..5.42 rows=99 width=0) (actual time=0.073..0.074 rows=116 loops=1)
Index Cond: ((example.b = 42) AND (example.a < 100))
Buffers: shared hit=3
Settings: enable_seqscan = 'off'
Planning Time: 0.304 ms
Execution Time: 0.577 ms
尽管这返回了 116 行，但它的运行时间不到 1 毫秒，读取了 118 个块（约 1MB）。
让我们删除在 (b, a) 上建立的索引，并关闭顺序扫描，看看在 (a, b) 上的索引，在相同查询上表现的效率如何：
DROP INDEX example_b_a_idx;
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
SELECT id FROM example WHERE b = 42 AND a < 100;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on public.example (cost=1955.74..2313.03 rows=99 width=8) (actual time=4.721..5.123 rows=116 loops=1)
Output: id
Recheck Cond: ((example.a < 100) AND (example.b = 42))
Heap Blocks: exact=115
Buffers: shared hit=355
-> Bitmap Index Scan on example_a_b_idx (cost=0.00..1955.71 rows=99 width=0) (actual time=4.623..4.624 rows=116 loops=1)
Index Cond: ((example.a < 100) AND (example.b = 42))
Buffers: shared hit=240
Planning Time: 0.305 ms
Execution Time: 5.200 ms
这花了 5 毫秒多一点，并读取了 355 个块（约 3MB），来运行相同的查询。
根据数据的分布情况和其他因素（如 LIMIT 子句），这种差异可能会变得更加极端，因此密切关注索引中的列顺序非常重要！
总结
虽然我们没有对给定的查询，讨论到索引扫描可能表现低效的每种场景，但我们已经涵盖了最常见的情况。
现在，您至少对查询计划中需要注意的事项有了更多的想法，从而能够诊断下面这些类型的低效情况：
- Rows Removed by Filter 的行数大，即使是在索引扫描类型上
- Loops 的次数乘以 Rows Removed by Filter 的行数，结果值大
- Buffers 行的数字大，尤其是与返回的行数相比
- 索引中的列顺序，以及它是否非常适合对应的查询
最重要的一点是，如果您只是看到了 “Index Scan” 一词的时候，这并不意味着扫描的速度已经尽可能快了！