PostgreSQL中一个索引的三种不同的扫描类型： 位图、索引和仅索引

性能是数据库管理系统的基本要素之一。性能低下意味着查询时间过长和前端响应时间过高。提高性能的最有效方法之一是为表列建立适当的索引。索引可以节省大量的数据访问时间，并引导查询以最快的方式收集结果。在PostgreSQL中，有不同的方法可以利用索引来产生最有效的计划。

本文中，我们将回顾以下三种不同的索引扫描类型，这取决于表、查询检索的内容和使用的过滤器：

·位图索引扫描

·索引扫描

·仅索引扫描

构建测试场景

在下面的练习和示例中，我们将使用带有单一索引的表，并回顾扫描策略如何根据查询条件而改变。

定义表：

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);

插入1000W行记录：

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()函数加载了1000万条记录，所以这三列中，register_date唯一的值是最多的。

·age：当我们加载数据时，也使用了random()函数，但是用floor()函数限制了结果，所以所有不同的值都在1到99之间。

·is_active. 这一列的数据类型为布尔型，因此只有两个不同的值，即true和false。

在规划索引时，必须考虑列的选择性。

例如，在上面的列中，在is_active列上使用单个索引不会增加任何优势，因为在所有的10M行中，只有两个值是可能的，所以如果我们想过滤所有is_active = true的行，优化器将毫无疑问地使用顺序扫描。

验证列的不同值数量的一种方法是查询数据库中的pg_stats视图。在这种情况下，我们运行ANALYZE命令：

db1=# ANALYZE person;ANALYZEdb1=# 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           |                  99person     | is_active     |                   2person     | register_date |                  -1(3 rows)

可以看到，age列不同的值是99，而is_active列只是2；对于register_date列的值，之所以是-1，是应为analyze认为该列不同的值和所有行数相等。

一个索引，不同的扫描类型

现在我们有了表数据和索引，我们可以测试不同的扫描类型。首先，为了有一个起点，让我们验证一下PostgreSQL将如何解决一个不带过滤器的表所有数据的查询：

db01=# EXPLAIN (ANALYZE) SELECT * FROM person;                                                       QUERY PLAN                                                       ------------------------------------------------------------------------------------------------------------------------ Seq Scan on person  (cost=0.00..304082.00 rows=10000000 width=126) (actual time=0.008..5286.228 rows=10000000 loops=1) Planning Time: 1.672 ms Execution Time: 5716.896 ms(3 rows)​​db01=# 

不出所料，为了从表中获取所有数据，优化器决定进行顺序扫描，获取全部1000万条记录。这是有道理的，因为它是一次性获取所有行。总耗时超过5716.896 ms。 

位图索引扫描

当查询请求的数据量足够大，可以利用批量读取的优势（如顺序扫描），但又不至于需要处理整个表时，优化器就会选择这种索引扫描方法。我们可以把位图索引扫描看作是介于顺序扫描和索引扫描之间的一种方法。

位图索引扫描总是和位图堆扫描一起工作；第一次扫描索引找到所有合适的行位置并建立位图，然后第二次使用位图逐个扫描堆页面并收集行。

下面是一个使用我们之前建立的表和索引进行位图索引扫描的示例：

db01=# EXPLAIN (ANALYZE) SELECT * FROM person WHERE age = 20 ;                                                                       QUERY PLAN                                                                       -------------------------------------------------------------------------------------------------------------------------------------------------------- Gather  (cost=3832.23..217695.48 rows=102667 width=126) (actual time=52.070..847.662 rows=100782 loops=1)   Workers Planned: 2   Workers Launched: 2   ->  Parallel Bitmap Heap Scan on person  (cost=2832.23..206428.78 rows=42778 width=126) (actual time=42.797..797.402 rows=33594 loops=3)         Recheck Cond: (age = 20)         Rows Removed by Index Recheck: 534364         Heap Blocks: exact=17732 lossy=12921         ->  Bitmap Index Scan on idx_person_age_date_active  (cost=0.00..2806.56 rows=102667 width=0) (actual time=40.628..40.629 rows=100782 loops=1)               Index Cond: (age = 20) Planning Time: 6.571 ms Execution Time: 858.086 ms(11 rows)​​db01=# 

在执行计划的内部节点（首先执行）中，对idx_person_age_date_active索引进行位图索引扫描。它用所有合适的行位置创建位图，并将其传递给它的父节点（在其后执行），

在person表上执行并行位图堆扫描。这第二阶段将逐个检索页，执行过滤条件的重新检查，并返回结果数据集。

为了比较一下，看看同样的操作仅使用顺序扫描是如何执行的：

db01=# START TRANSACTION ;START TRANSACTIONdb01=*# DROP INDEX idx_person_age_date_active;DROP INDEXdb01=*# EXPLAIN (ANALYZE) SELECT * FROM person WHERE age = 20 ;                                                           QUERY PLAN                                                            --------------------------------------------------------------------------------------------------------------------------------- Gather  (cost=1000.00..267432.03 rows=102667 width=126) (actual time=0.227..1284.268 rows=100782 loops=1)   Workers Planned: 2   Workers Launched: 2   ->  Parallel Seq Scan on person  (cost=0.00..256165.33 rows=42778 width=126) (actual time=0.045..1213.880 rows=33594 loops=3)         Filter: (age = 20)         Rows Removed by Filter: 3299739 Planning Time: 0.771 ms Execution Time: 1290.852 ms(8 rows)​​db01=*# ROLLBACK ;ROLLBACK

考虑到该查询涉及101K行，约占总行数的1%。位图索引扫描利用了顺序扫描方式批量读取有限页的优势，结果比直接顺序扫描更好，速度提高了。

索引扫描

当听到"嘿，这个查询做得不错；它使用了索引.... "之类的话时，你可能会想到这种扫描方法。这种方法是通过索引访问数据的基本定义。

索引扫描包括两步，第一步是从索引中获取行位置，第二步是从堆或表页中收集实际数据。因此，每次索引扫描访问都是两次读操作。但是，这仍然是从表中检索数据的最有效方法之一。

当需要检索的行数较少时，优化器会选择这种扫描方法，因此执行两步Index Scan操作比单独处理表页来收集数据成本低和快。

下面是使用测试表进行索引扫描的示例：

db01=# 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.040..0.040 rows=0 loops=1)   Index Cond: ((age = 20) AND (register_date = '2023-03-23 19:50:03.22938'::timestamp without time zone)) Planning Time: 1.393 ms Execution Time: 0.062 ms(4 rows)​​db01=#

在我们之前使用的查询中，添加了一个新的过滤表达式： AND register_date = '2023-03-23 19:50:03.22938+00'::timestamp。

register_date列是多列索引idx_person_age_date_active的一部分。由于我们是通过一个单一的值进行过滤，因此只有一个索引条目，所以PostgreSQL从索引中一次读取特定的行位置，然后从表页中读取该位置内的所有行数据。

整个查询耗时0.062 ms，非常快！

在上面的示例中，查询通过register_date列的特定时间戳值进行过滤，但是如果行数较少，PostgreSQL仍然会选择索引扫描来获取多条记录，例如在下面的示例中：

db01=# 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.010..0.011 rows=0 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: 1.722 ms Execution Time: 0.031 ms(4 rows)​​db01=# 

仅索引扫描

最后，将回顾一下仅索引扫描方法。这是PostgreSQL用来改进标准索引扫描方法的一种非常好的方法。

换句话说，在SELECT和WHERE子句中的列/表达式应该是索引的一部分，这样就可以避免从表页中获取数据的二次读操作，而只从索引读操作中返回结果数据。

在下面的示例中，我们使用了与索引扫描示例中几乎相同的查询，但是我们并没有询问所有的行列(*)，而是仅仅检索了我们用来建立多列索引的三列：

db01=# 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.021..0.021 rows=0 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.088 ms Execution Time: 0.048 ms(5 rows)​​db01=# 

EXPLAIN输出现在显示仅索引扫描，而且，通过Heap Fetches这行确认没有访问堆（表页）： 时间甚至比之前的索引扫描更好，只有0.048ms。这种扫描方法可以帮助符合条件的查询获得最佳性能。

请记住，为所有列建立索引，使索引包含与表相同的所有数据，并不是一个好主意。如果是这种情况，PostgreSQL将不会看到使用索引的任何好处，而会选择顺序扫描方法。请看下面的内容：

db01=# START TRANSACTION ;START TRANSACTIONdb01=*# DROP INDEX "idx_person_age_date_active";DROP INDEXdb01=*# CREATE INDEX idx_person_all ON person(id,first_name,last_name,age,email,register_date,is_active);CREATE INDEXdb01=*# ANALYZE person;ANALYZEdb01=*# 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=8496.878..8500.434 rows=0 loops=1)   Workers Planned: 2   Workers Launched: 2   ->  Parallel Seq Scan on person  (cost=0.00..266582.11 rows=1 width=126) (actual time=8464.351..8464.352 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.053 ms Execution Time: 8502.746 ms(8 rows)​​db01=*# ROLLBACK ;ROLLBACKdb01=# 

在上面的示例中，在一个事务中，我们放弃了之前示例中使用的多列索引，并创建了一个包含了表中所有列的新索引，然后刷新了统计信息，并尝试了一个查询，查询特定过滤器上的所有列(*)，结果，优化器选择了顺序扫描，它希望通过并行执行操作来提高执行时间。尽管如此，最终的执行时间与我们的预期结果相差甚远。

最后的建议

1.在选择性高的列上创建索引

2.尽量查询少量数据

3.只返回你需要的列

4.调优参数random_page_cost，降低这个参数会让优化器更倾向于索引扫描，而不是顺序扫描。固态硬盘可以为随机读取访问提供更好的吞吐量，因此你可以分析调整这个参数。

5.调整有效缓存大小（effective_cache_size）参数。将该参数设置为较高值（如果您的机器专用于PostgreSQL服务，则接近总RAM的75%）将有助于规划器选择索引扫描而不是顺序扫描。