六月 5, 2024
摘要：在本教程中，您将了解到普通索引扫描的成本估算。
目录
PostgreSQL 索引扫描
索引返回行版本 ID（元组 ID，或简称 TID），可以通过两种方式的一种进行处理。第一种是索引扫描。大多数（但不是全部）索引访问方法都具有 INDEX SCAN 属性，并支持该方式。
让我们开始设置一个小的测试用例：
CREATE TABLE events (event_id char(7) PRIMARY KEY, occurred_at timestamptz);
INSERT INTO events (event_id, occurred_at)
SELECT to_hex(i),
TIMESTAMP '2024-01-01 00:00:00+08' + floor(random() * 100000)::int * '1 minutes'::interval
FROM generate_series(x'1000000'::int, x'2000000'::int, 16) AS s(i);
ANALYZE events;
该操作在计划中表示为 Index Scan 节点：
EXPLAIN SELECT * FROM events
WHERE event_id = '19AC6C0' AND occurred_at = '2024-01-06 12:00:00+08';
QUERY PLAN
------------------------------------------------------------------------------
Index Scan using events_pkey on events (cost=0.42..8.45 rows=1 width=16)
Index Cond: (event_id = '19AC6C0'::bpchar)
Filter: (occurred_at = '2024-01-06 04:00:00+00'::timestamp with time zone)
(3 rows)
在这里，访问方法一次返回一个 TID。索引的处理机制为：接收一个 TID，读取相关的表页，获取元组，检查其可见性，如果一切正常，则返回所需的字段。整个过程重复进行，直到访问方法获取完与查询匹配的 TID。
计划中的 Index Cond 行仅显示可使用索引筛选的条件。只能根据表行来检查的其他条件，列出在 Filter 行。
换言之，索引和表的扫描操作不会分成两个不同的节点，而是将其作为索引扫描节点的一部分来执行。但是，有一个称为 Tid Scan 的特殊节点。它使用已知的 TID 从表中读取元组：
EXPLAIN SELECT * FROM events WHERE ctid = '(0,1)'::tid;
QUERY PLAN
-------------------------------------------------------
Tid Scan on events (cost=0.00..4.01 rows=1 width=16)
TID Cond: (ctid = '(0,1)'::tid)
(2 rows)
成本估算
在计算索引扫描的成本时，系统会估算索引访问成本和表页获取成本，然后将它们相加。
索引访问成本完全取决于所选的访问方法。如果使用 B 树，则大部分成本来自索引页的获取和处理。根据数据总量和查询条件的选择率，可以推断出获取的页数和行数。索引页的访问模式本质上是随机的（因为逻辑上相邻的页面，在磁盘上通常并不相邻），因此访问单个页面的成本估算为 random_page_cost。此处还会包括从 B 树的根页降到叶子页的成本，以及额外的表达式计算成本。
访问表的过程，在 CPU 部分的开销，包括了每行的处理时间（cpu_tuple_cost）乘以行数。
在 I/O 部分的开销，取决于索引访问的选择率，以及磁盘上元组的顺序和访问方法返回元组 ID 的顺序，两者之间的相关性。
相关性高的好处
在理想情况下，当磁盘上元组的顺序和索引中 TID 的逻辑顺序完全匹配时，索引扫描将按顺序从一页移动到另一页，每页仅读取一次。
这种相关性数据是收集的统计信息：
SELECT attname, correlation
FROM pg_stats WHERE tablename = 'events'
ORDER BY abs(correlation) DESC;
attname | correlation
-------------+-------------
event_id | 1
occurred_at | 0.00227215
(2 rows)
绝对的event_id值接近 1，表示一种高的相关性；而接近零的值，表示一种更混乱的分布。
下面是一个对大量行进行索引扫描的示例：
EXPLAIN SELECT * FROM events WHERE event_id < '1100000';
QUERY PLAN
----------------------------------------------------------------------------------
Index Scan using events_pkey on events (cost=0.42..2358.77 rows=73391 width=16)
Index Cond: (event_id < '1100000'::bpchar)
(2 rows)
总成本约为 2358。
选择率估计为：
SELECT round(73391::numeric/reltuples::numeric, 4)
FROM pg_class WHERE relname = 'events';
round
--------
0.0700
(1 row)
这大约是 1/16，考虑到event_id值的范围从 1000000 到 2000000，这是可以预期的。
对于 B 树，索引部分的成本只是所需页面的页面获取成本。与 B 树支持的任何过滤条件匹配的索引记录，始终是顺序的，并存储在逻辑上连接的叶子页中，因此要读取的索引页数会估计为索引大小乘以选择率。但是，页面在磁盘上不是按顺序存储的，因此扫描的模式是随机的。
成本估算包括了处理每个索引记录的成本（按 cpu_index_tuple_cost 计算），和每行的过滤成本（每个操作符按 cpu_operator_cost 计算，在本例中我们只有一个）。
在表 I/O 部分的开销，是所需页面的顺序读取开销。在相关性理想的情况下，磁盘上的元组是顺序的，因此获取的页数将等于表大小乘以选择率。
然后，将处理每个扫描元组 （cpu_tuple_cost） 的成本添加到顶层节点。
WITH costs(idx_cost, tbl_cost) AS (
SELECT
( SELECT round(
current_setting('random_page_cost')::real * pages +
current_setting('cpu_index_tuple_cost')::real * tuples +
current_setting('cpu_operator_cost')::real * tuples
)
FROM (
SELECT relpages * 0.0700 AS pages, reltuples * 0.0700 AS tuples
FROM pg_class WHERE relname = 'events_pkey'
) c
),
( SELECT round(
current_setting('seq_page_cost')::real * pages +
current_setting('cpu_tuple_cost')::real * tuples
)
FROM (
SELECT relpages * 0.0700 AS pages, reltuples * 0.0700 AS tuples
FROM pg_class WHERE relname = 'events'
) c
)
)
SELECT idx_cost, tbl_cost, idx_cost + tbl_cost AS total FROM costs;
idx_cost | tbl_cost | total
----------+----------+-------
1364 | 989 | 2353
(1 row)
此公式说明了成本计算背后的逻辑，结果与规划器的估计值非常接近。如果您愿意再探究几个小细节，还可以获得更确切的结果，但这超出了本文的范围。
相关性低的坏处
当相关性较低时，整个情况会发生变化。让我们为occurred_at列创建一个索引，该列与索引的相关性几乎为零，并运行一个查询，该查询会获取与上一个示例中相似的行数。该索引的访问成本非常高（16812，而在前面的示例中为 2358），以至于规划器只有在明确指示时才会使用该索引：
CREATE INDEX ON events(occurred_at);
SET enable_seqscan = off;
SET enable_bitmapscan = off;
EXPLAIN SELECT * FROM events
WHERE occurred_at < '2024-01-06 12:00:00+08';
QUERY PLAN
----------------------------------------------------------------------------------------------
Index Scan using events_occurred_at_idx on events (cost=0.42..16812.36 rows=78629 width=16)
Index Cond: (occurred_at < '2024-01-06 04:00:00+00'::timestamp with time zone)
(2 rows)
较低的相关性意味着，访问方法返回元组 ID 的顺序与元组在磁盘上的物理位置关系不大，因此每次访问的下一个元组都将位于不同的页面中。这使得索引扫描节点要从一个页面跳到另一个页面，在最坏的情况下，页面获取的次数将等于元组的数量。
然而，这并不意味着简单地将 seq_page_cost 替换为 random_page_cost，将relpages替换为reltuples倒是能给出正确的成本。事实上，这将使我们得出 316820 的估计值，这远远高出了规划器的估计。
这里的关键是缓存。频繁访问的页面会存储在缓冲区缓存中（也存储在操作系统缓存中），因此缓存的大小越大，在其中找到请求的页面并避免访问磁盘的机会就越高。规划时使用的缓存大小由 effective_cache_size 参数定义（默认为 4GB）。参数值设置较低，会增加预期要获取的页面数。这里我们不讨论确切的计算公式（你可以查看backend/optimizer/path/costsize.c文件，找到其中的index_pages_fetched函数进行了解）。我们来看一个图表，说明获取的页面数量与表大小的关系（选择率为 1/2，每页行数为 10）：
虚线显示了获取次数等于页面数的一半（相关性理想时的最佳结果）和行数的一半（相关性为零且无缓存时的最差结果）。
从理论上讲，effective_cache_size 参数应该与总的可用缓存大小（包括 PostgreSQL 缓冲区缓存和系统缓存）匹配，但该参数仅用于成本估算，实际上并不会保留任何缓存的内存，因此您可以根据需要来更改它，而不用考虑实际的缓存值。
如果将参数调低到最小值，则会获得非常接近前面提到的无缓存最坏情况下的成本估算值：
SET effective_cache_size = '8kB';
EXPLAIN SELECT * FROM events
WHERE occurred_at < '2024-01-06 12:00:00+08';
QUERY PLAN
-----------------------------------------------------------------------------------------------
Index Scan using events_occurred_at_idx on events (cost=0.42..316678.81 rows=78629 width=16)
Index Cond: (occurred_at < '2024-01-06 04:00:00+00'::timestamp with time zone)
(2 rows)
RESET effective_cache_size;
RESET enable_seqscan;
RESET enable_bitmapscan;
在计算表 I/O 的成本时，规划器会同时考虑最坏和最佳情况的成本，然后根据实际的相关性选择一个中间值。
当你只扫描表中的某些元组时，索引扫描是非常有效的。表中的元组位置与访问方法返回 TID 的顺序相关性越高，使用此访问方法可以高效检索的元组就越多。相反，在相关性很低的情况下，此访问方法在元组数非常少时就会变得低效。