八月 23, 2024
摘要：在本教程中，您将学习 PostgreSQL 中三种不同的索引扫描类型：位图、索引和只用索引。
目录
介绍
性能是数据库管理系统的一个重要方面。对于用户来说，没有什么比性能不佳更令人烦恼和沮丧的了，这意味着查询的运行时间很长和前端的响应时间慢。
解决性能问题的最有效方法之一是，为表列设置适当的索引。索引可以节省大量的数据访问时间，并引导查询以最快的方式收集结果。
在 PostgreSQL 中，它可以通过不同的方式利用索引，来生成最有效的计划。
在本文中，我们将根据表、查询检索的内容以及使用的过滤条件，来观察以下三种不同的 PostgreSQL 索引扫描类型：
- 位图索引扫描
- 索引扫描
- 只用索引的扫描
构建测试场景
在以下练习和示例中，我们将使用一个带有单个索引的表，并观察扫描策略会如何根据查询条件发生变化。
接下来是表定义。我们为 id 列创建一个序列，因为一个最佳实践是始终要有一个主键列，但对于我们将要介绍的这些示例，我们并不需要主键。
CREATE SEQUENCE public.person_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
CREATE TABLE public.person(
id integer DEFAULT nextval('public.person_id_seq'::regclass) NOT NULL,
first_name text NOT NULL,
last_name text NOT NULL,
age integer NOT NULL,
email text NOT NULL,
register_date timestamp with time zone DEFAULT now() NOT NULL,
is_active boolean DEFAULT true NOT NULL
);
执行下面的 SQL 语句，向该表插入 1000 万行：
INSERT INTO public.person
SELECT generate_series
, md5(random()::text)
, md5(random()::text)
, floor(random() * 99)::int
, md5(random()::text) || '@gmail.com'
, now() - (random() * (interval '90 days'))
, case when random() > 0.5 then true else false end
FROM generate_series(1, 10000000);
现在我们有了带有一些虚拟数据的测试表，因此我们可以来进行练习了。
为数据建立索引
如前所述，最佳做法是为表添加主键，但我们跳过了此步骤，只添加一个复合索引，这将有助于我们查看不同的扫描类型。
我们使用下面的语句，来创建这个多列索引：
CREATE INDEX idx_person_age_date_active ON person(age,register_date,is_active);
在这里，我们考虑了具有不同基数的三列，基数表示不同值占行总数的比例。以下是按照基数从高到低排序的列：
- register_date. 我们借助 random() 函数设置此列，加载了 10M 行，因此非重复值的数量是这三列中最大的。
- age. 当我们加载数据时，我们也使用了 random() 函数，但我们用 floor() 函数“限制”了结果，让所有不同的值都在 1 到 99 之间。
- is_active. 此列数据类型为布尔值，因此只能使用两个不同的值，即 true 和 false。
在规划索引时，必须考虑列的数据基数，甚至在此之前，还要考虑我们将针对数据执行的过滤条件。
例如，在上面的列中，在 is_active 列上建立单个索引不会增加任何优势，因为在所有 10M 行中，只有可能的两个值，因此，如果我们想要过滤所有 is_active = true 的行，规划器将毫无疑问会使用顺序扫描。
要验证一个列的不同值的数目，一种方法是查询数据库中的 pg_stats 视图。要用这个方法的话，需要确保统计数据是最新的；在这种场景下，我们可以运行 ANALYZE 命令：
ANALYZE person;
对于前面的列，以下是查询 pg_stats 视图的结果：
SELECT tablename AS table_name,attname AS column_name,n_distinct AS num_distinct_values
FROM pg_stats
WHERE tablename = 'person'
AND attname IN ('age','register_date','is_active')
ORDER BY num_distinct_values DESC;
table_name | column_name | num_distinct_values
------------+---------------+---------------------
person | age | 99
person | is_active | 2
person | register_date | -1
(3 rows)
我们确认 age 列有 99 个不同的值，而 is_active 只有 2 个。register_date 列显示负值 -1，如文档中所述，ANALYZE 认为非重复值的数量可能与总行数相同：
注意：-1 表示唯一列，其中非重复值的数量与行数相同。
一个索引，不同的扫描类型
现在，我们已经有了表数据和索引，可以测试不同的扫描类型。首先，为了有一个起点，让我们验证一下在没有过滤条件的情况下，PostgreSQL 将如何解析获取所有表数据的查询：
EXPLAIN (ANALYZE) SELECT * FROM person;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Seq Scan on person (cost=0.00..304082.18 rows=10000018 width=126) (actual time=0.016..934.805 rows=10000000 loops=1)
Planning Time: 0.129 ms
Execution Time: 1183.355 ms
(3 rows)
正如预期的那样，为了从表中检索所有数据，规划器决定进行顺序扫描，对所有 10M 行进行扫描。这是有道理的，因为它要一次获取所有行。总时间超过了 1183 毫秒（~1.1 秒）。
位图索引扫描
当查询请求足够大的数据量时，规划器会选择该索引扫描方法，这样可以利用批量读取的优势，就像顺序扫描一样，但又不会大到实际需要处理所有表数据。我们可以将位图索引扫描视为，介于顺序扫描和索引扫描之间的扫描策略。
Bitmap Index Scan 始终与 Bitmap Heap Scan 配对工作；第一个索引扫描是要查找所有合适行的位置并构建位图，然后第二个扫描使用该位图逐个扫描堆表页并收集行。
以下是一个位图索引扫描示例，使用到了我们之前构建的表和索引：
EXPLAIN (ANALYZE) SELECT * FROM person WHERE age = 20;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
Gather (cost=3682.90..212050.63 rows=97334 width=126) (actual time=46.142..221.876 rows=101476 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Bitmap Heap Scan on person (cost=2682.90..201317.23 rows=40556 width=126) (actual time=24.783..189.769 rows=33825 loops=3)
Recheck Cond: (age = 20)
Rows Removed by Index Recheck: 534475
Heap Blocks: exact=17931 lossy=12856
-> Bitmap Index Scan on idx_person_age_date_active (cost=0.00..2658.57 rows=97334 width=0) (actual time=36.926..36.926 rows=101476 loops=1)
Index Cond: (age = 20)
Planning Time: 0.122 ms
Execution Time: 225.554 ms
(11 rows)
在内部节点（首先执行）中，是在 idx_person_age_date_active 索引上的位图索引扫描。它找到所有合适行的位置来创建位图，并将其传递给其父节点（在之后执行），即在 person 表上的并行化位图堆扫描。第二阶段会逐个访问页面，对过滤条件执行重新检查，并返回结果数据集。
为了进行比较，可以考虑下使用顺序扫描执行同一操作的方式：
START TRANSACTION;
DROP INDEX idx_person_age_date_active;
EXPLAIN (ANALYZE) SELECT * FROM person WHERE age = 20;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..266898.83 rows=97334 width=126) (actual time=0.852..402.355 rows=101476 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on person (cost=0.00..256165.43 rows=40556 width=126) (actual time=0.056..365.647 rows=33825 loops=3)
Filter: (age = 20)
Rows Removed by Filter: 3299508
Planning Time: 0.335 ms
Execution Time: 406.671 ms
(8 rows)
ROLLBACK;
考虑到这个查询返回了 101K 行，大约占总行数的 1%。位图索引扫描利用了“顺序扫描”形式的批量读取能力，对有限数量的页面进行了批量读取，与直接的顺序扫描相比，产生的结果更好，执行速度提高了 2 倍。
索引扫描
在您听到类似 “嘿，这个查询表现很好；它使用了索引……” 时，可能正好就是这种扫描了。这种方法是通过索引访问数据的基本形式。
索引扫描由两个步骤组成，第一个步骤是从索引中获取行位置，第二个步骤是从堆页或表页中收集实际数据。因此，每次索引扫描访问都是两次读取操作。但是，这仍然是从表中检索数据的最有效方法之一。
当要检索的行数较少时，规划器会选择该扫描方法，因此执行两步索引扫描操作，比通过单独处理表页来收集数据“成本更低”且更快。
使用我们的测试表，下面是一个索引扫描的示例：
EXPLAIN (ANALYZE) SELECT * FROM person WHERE age = 20
AND register_date = '2023-03-23 19:50:03.22938+00'::timestamp;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_person_age_date_active on person (cost=0.56..8.58 rows=1 width=126) (actual time=0.039..0.040 rows=1 loops=1)
Index Cond: ((age = 20) AND (register_date = '2023-03-23 19:50:03.22938'::timestamp without time zone))
Planning Time: 0.190 ms
Execution Time: 0.064 ms
(4 rows)
可以看到，在我们之前使用的查询中，我们添加了一个新的过滤条件表达式：AND register_date = ‘2023-03-23 19:50:03.22938+00’::timestamp。register_date 列是多列索引 idx_person_age_date_active 中的一部分。由于我们按一个单数值进行过滤，只有一个索引条目匹配相同的值，因此 PostgreSQL 在一次读取中从索引中获取到特定的行位置，然后从该位置内的表页中获取所有行数据。整个查询耗时 0.064 毫秒；太快了！
在上面的示例中，查询按 register_date 列的特定时间戳值进行过滤，但如果行数较小，PostgreSQL 仍会为多行选择索引扫描，例如，在下面的查询中：
EXPLAIN (ANALYZE) SELECT * FROM person WHERE age = 20
AND register_date BETWEEN '2023-03-23 19:50:00'::timestamp AND '2023-03-23 20:00:00'::timestamp;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_person_age_date_active on person (cost=0.56..8.58 rows=1 width=126) (actual time=0.044..0.167 rows=8 loops=1)
Index Cond: ((age = 20) AND (register_date >= '2023-03-23 19:50:00'::timestamp without time zone) AND (register_date <= '2023-03-23 20:00:00'::timestamp without time zone))
Planning Time: 0.127 ms
Execution Time: 0.337 ms
(4 rows)
查询使用了 BETWEEN 运算符，按范围对 register_date 列进行过滤。根据统计信息，规划器认为结果为一行，并选择了索引扫描。最后，实际结果集是八行，因此存在八对读取操作。尽管如此，查询还是很快完成了执行，只有 0.337 毫秒。
只用索引的扫描
最后，我们来观察只用索引的扫描方法。这是 PostgreSQL 用来改进标准索引扫描方法的一个非常好的方法。
当查询请求的所有数据都已存在于索引中时，PostgreSQL 会使用此方法；换句话说，SELECT 和 WHERE 子句中的列/表达式应该是索引的一部分，这样可以避免从表页获取数据的第二次读取操作，直接返回从索引读取到的结果数据。
在下面的示例中，我们使用的查询与索引扫描示例几乎相同，但不是请求所有行的列（*），而只是检索用于构建多列索引的那三列：
EXPLAIN (ANALYZE) SELECT age,register_date,is_active FROM person
WHERE age = 20
AND register_date = '2023-03-23 19:50:03.22938+00'::timestamp;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using idx_person_age_date_active on person (cost=0.56..4.58 rows=1 width=13) (actual time=0.034..0.036 rows=1 loops=1)
Index Cond: ((age = 20) AND (register_date = '2023-03-23 19:50:03.22938'::timestamp without time zone))
Heap Fetches: 0
Planning Time: 0.103 ms
Execution Time: 0.058 ms
(5 rows)
请注意 EXPLAIN 输出现在显示 Index Only Scan，此外，其中的行 Heap Fetches: 0，还确认了没有访问堆（表页）。完成时间甚至比之前的 Index Scan 更好，只有 0.058 毫秒。该扫描方法有助于为符合其条件的查询获得最佳性能。
请记住，为所有列建立索引，以便索引包含与表相同的所有数据“不是一个好主意”。如果是这种情况，PostgreSQL 将看不到使用该索引的任何优势，并且会选择顺序扫描方法。请看下面的示例：
START TRANSACTION ;
DROP INDEX "idx_person_age_date_active";
CREATE INDEX idx_person_all ON person(id,first_name,last_name,age,email,register_date,is_active);
ANALYZE person;
EXPLAIN (ANALYZE) SELECT * FROM person WHERE age = 20
AND register_date = '2023-03-23 19:50:03.22938+00'::timestamp;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Gather (cost=1000.00..267582.21 rows=1 width=126) (actual time=6662.141..6671.741 rows=1 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on person (cost=0.00..266582.11 rows=1 width=126) (actual time=5093.681..6636.195 rows=0 loops=3)
Filter: ((age = 20) AND (register_date = '2023-03-23 19:50:03.22938'::timestamp without time zone))
Rows Removed by Filter: 3333333
Planning Time: 2.704 ms
Execution Time: 6673.001 ms
(8 rows)
ROLLBACK;
在上面的单个事务中，我们删除了在前面示例中使用的多列索引，并创建了一个包含所有表列的新索引，然后刷新统计信息，并尝试运行一个查询，要求在特定过滤条件上获取所有列（*），结果，规划器选择了顺序扫描，它希望通过并行执行操作以提升性能。尽管如此，最终的执行时间与理想的结果相去甚远。
最后的建议
现在，我们已经观察了，PostgreSQL 可以根据存储的数据和查询过滤条件，对单个索引使用的不同扫描方法。这里，再给大家提供一些您在规划查询的过滤条件和表索引时，会发现有用的最后想法。
- 为基数最高的列建立索引。在此之后，在按同一列进行过滤时，查询可以发挥最佳性能。在基数低的列上建立索引将产生相反的效果，因为它会增加额外的维护工作，并且很大可能的结果是，规划器不会使用它们。
- 针对小型（特定）数据集而不是大型数据集规划查询。如果您的工作负载和服务设计能够满足您的需求，请考虑在仅检索几行时过滤数据。正如我们所看到的，索引扫描是一种有效的优化技术，可以更快地检索数据，如果结果数据足够小，PostgreSQL 将使用它。
- 仅检索所需要的列。通过这样做，PostgreSQL 可以利用只用索引的扫描，并避免从堆（表页）中进行“额外”读取。这些节省将在高查询量环境中产生广泛适用的良好效果。请记住，多列索引并不是引导规划器选择 Index Only Scan 的唯一方法；您还可以考虑覆盖索引。
- 调整 random_page_cost 参数。降低该值会让规划器更倾向索引扫描，而不是顺序扫描。现代 SSD 可以为随机读取访问提供更好的吞吐量，因此您可以进行测试分析，并相应地调整该参数。
- 调整 effective_cache_size 参数。将该参数设置为更高的值（如果您的计算机专用于 PostgreSQL 服务，则为总内存的近 75%），将有助于规划器选择索引扫描，而不是顺序扫描。
请记住，每个实施都是不同的，细节很重要，因此，在对生产进行任何调整之前，建议先在较低的环境中分析和测试效果。