PostgreSQL 中的三种索引扫描方式，你都遇到过吗？-51CTO.COM

介绍

性能是数据库管理系统的一个重要方面。对于用户来说，没有什么比性能不佳更令人烦恼和沮丧的了，这意味着查询的运行时间很长和前端的响应时间慢。

解决性能问题的最有效方法之一是，为表列设置适当的索引。索引可以节省大量的数据访问时间，并引导查询以最快的方式收集结果。

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

在这里，我们考虑了具有不同基数的三列，基数表示不同值占行总数的比例。以下是按照基数从高到低排序的列：

1. register_date. 我们借助 random() 函数设置此列，加载了 10M 行，因此非重复值的数量是这三列中最大的。

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

3. 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 可以根据存储的数据和查询过滤条件，对单个索引使用的不同扫描方法。这里，再给大家提供一些您在规划查询的过滤条件和表索引时，会发现有用的最后想法。

1. 为基数最高的列建立索引。在此之后，在按同一列进行过滤时，查询可以发挥最佳性能。在基数低的列上建立索引将产生相反的效果，因为它会增加额外的维护工作，并且很大可能的结果是，规划器不会使用它们。
2. 针对小型（特定）数据集而不是大型数据集规划查询。如果您的工作负载和服务设计能够满足您的需求，请考虑在仅检索几行时过滤数据。正如我们所看到的，索引扫描是一种有效的优化技术，可以更快地检索数据，如果结果数据足够小，PostgreSQL 将使用它。
3. 仅检索所需要的列。通过这样做，PostgreSQL 可以利用只用索引的扫描，并避免从堆（表页）中进行“额外”读取。这些节省将在高查询量环境中产生广泛适用的良好效果。请记住，多列索引并不是引导规划器选择 Index Only Scan 的唯一方法；您还可以考虑覆盖索引。
4. 调整 random_page_cost 参数。降低该值会让规划器更倾向索引扫描，而不是顺序扫描。现代 SSD 可以为随机读取访问提供更好的吞吐量，因此您可以进行测试分析，并相应地调整该参数。
5. 调整 effective_cache_size 参数。将该参数设置为更高的值（如果您的计算机专用于 PostgreSQL 服务，则为总内存的近 75%），将有助于规划器选择索引扫描，而不是顺序扫描。

请记住，每个实施都是不同的，细节很重要，因此，在对生产进行任何调整之前，建议先在较低的环境中分析和测试效果。