PostgreSQL 18 preview - explain analyze支持index searches统计

PostgreSQL 18 preview - explain analyze支持index searches统计

在一个唯一值较少的列上求count distinct时, 使用index skip scan通常是非常有效的方法. 但是PostgreSQL目前还不支持这种扫描方式, 问题和解决方案如下:

• 《DB吐槽大会,第62期 - PG 不支持index skip scan》
• 《递归+排序字段加权 skip scan 解决 窗口查询多列分组去重的性能问题》
• 《PostgreSQL Oracle 兼容性之 - INDEX SKIP SCAN (递归查询变态优化) 非驱动列索引扫描优化》
• 《distinct xx和count(distinct xx)的变态递归优化方法 - 索引收敛(skip scan)扫描》

PostgreSQL 18有望新增index skip scan? 看懂这个patch, 就知道为什么了. 因为里面有这么句话:

The information shown also provides useful context when EXPLAIN ANALYZE runs a plan with an index scan node that successfully applied the skip scan optimization (set to be added to nbtree by an upcoming patch).

这个patch在explain analyze打印index / index only / bitmap index scan的节点中, index被扫描的次数. 注意不是通过index scan fetch tuple的次数, 是search次数, 一次search可能fetch很多条tuple.  search次数也反应在pg_stat_all_indexes.idx_scan的统计信息中.

在 = any (array[]) 或 = x OR = y OR =...的场景中, 不知道会产生多少次的index search, 通过这个patch, 可以知道了. 例如

SELECT * FROM brin_date_test WHERE a = '2023-01-01'::date;  
                                 QUERY PLAN                                   
----------------------------------------------------------------------------  
 Bitmap Heap Scan on brin_date_test (actual rows=0.00 loops=1)  
   Recheck Cond: (a = '2023-01-01'::date)  
   ->  Bitmap Index Scan on brin_date_test_a_idx (actual rows=0.00 loops=1)  
         Index Cond: (a = '2023-01-01'::date)  
         Index Searches: 1  
(5 rows)  

-- actually run the query with an analyze to use the partial index  
explain (costs off, analyze on, timing off, summary off, buffers off)  
select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';  
                             QUERY PLAN                               
--------------------------------------------------------------------  
 Index Scan using onek2_u2_prtl on onek2 (actual rows=1.00 loops=1)  
   Index Cond: (unique2 = 11)  
   Filter: (stringu1 = 'ATAAAA'::name)  
   Index Searches: 1  
(4 rows)  

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=0fbceae841cb5a31b13d3f284ac8fdd19822eceb

Show index search count in EXPLAIN ANALYZE, take 2.  
author Peter Geoghegan <[email protected]>   
Tue, 11 Mar 2025 13:20:50 +0000 (09:20 -0400)  
committer Peter Geoghegan <[email protected]>   
Tue, 11 Mar 2025 13:20:50 +0000 (09:20 -0400)  
commit 0fbceae841cb5a31b13d3f284ac8fdd19822eceb  
tree 0a4de79065d137bc86c1455adc317373b490a880 tree  
parent 12c5f797ea6a8e96de661e3838410b9775061796 commit | diff  
Show index search count in EXPLAIN ANALYZE, take 2.  

Expose the count of index searches/index descents in EXPLAIN ANALYZE's  
output for index scan/index-only scan/bitmap index scan nodes.  This  
information is particularly useful with scans that use ScalarArrayOp  
quals, where the number of index searches can be unpredictable due to  
implementation details that interact with physical index characteristics  
(at least with nbtree SAOP scans, since Postgres 17 commit 5bf748b8).  
The information shown also provides useful context when EXPLAIN ANALYZE  
runs a plan with an index scan node that successfully applied the skip  
scan optimization (set to be added to nbtree by an upcoming patch).  

The instrumentation works by teaching all index AMs to increment a new  
nsearches counter whenever a new index search begins.  The counter is  
incremented at exactly the same point that index AMs already increment  
the pg_stat_*_indexes.idx_scan counter (we're counting the same event,  
but at the scan level rather than the relation level).  Parallel queries  
have workers copy their local counter struct into shared memory when an  
index scan node ends -- even when it isn't a parallel aware scan node.  
An earlier version of this patch that only worked with parallel aware  
scans became commit 5ead85fb (though that was quickly reverted by commit  
d00107cd following "debug_parallel_query=regress" buildfarm failures).  

Our approach doesn't match the approach used when tracking other index  
scan related costs (e.g., "Rows Removed by Filter:").  It is comparable  
to the approach used in similar cases involving costs that are only  
readily accessible inside an access method, not from the executor proper  
(e.g., "Heap Blocks:" output for a Bitmap Heap Scan, which was recently  
enhanced to show per-worker costs by commit 5a1e6df3, using essentially  
the same scheme as the one used here).  It is necessary for index AMs to  
have direct responsibility for maintaining the new counter, since the  
counter might need to be incremented multiple times per amgettuple call  
(or per amgetbitmap call).  But it is also necessary for the executor  
proper to manage the shared memory now used to transfer each worker's  
counter struct to the leader.  

Author: Peter Geoghegan <[email protected]>  
Reviewed-By: Robert Haas <[email protected]>  
Reviewed-By: Tomas Vondra <[email protected]>  
Reviewed-By: Masahiro Ikeda <[email protected]>  
Reviewed-By: Matthias van de Meent <[email protected]>  
Discussion: https://postgr.es/m/CAH2-WzkRqvaqR2CTNqTZP0z6FuL4-3ED6eQB0yx38XBNj1v-4Q@mail.gmail.com  
Discussion: https://postgr.es/m/CAH2-Wz=PKR6rB7qbx+Vnd7eqeB5VTcrW=iJvAsTsKbdG+kW_UA@mail.gmail.com  

AI解读如下

这个补丁（patch）的目的是在 EXPLAIN ANALYZE 的输出中显示索引搜索计数，以便更好地理解查询执行计划中索引扫描的性能。 这是对之前一个类似补丁的改进版本（"take 2"）。

核心功能：

• 显示索引搜索计数：  对于索引扫描（index scan）、仅索引扫描（index-only scan）和位图索引扫描（bitmap index scan）节点，EXPLAIN ANALYZE 的输出将显示索引搜索/索引下降（index descent）的次数。
• 适用场景：
• ScalarArrayOp quals：  当扫描使用 ScalarArrayOp 限定词时，索引搜索的数量可能难以预测。这个补丁可以帮助理解这种情况下索引搜索的实际数量。
• 跳跃扫描优化（skip scan optimization）：  即将添加到 nbtree 索引的跳跃扫描优化也会受益于这个补丁，因为它能提供有用的上下文信息。
• 实现方式：
• 索引访问方法（Index AM）负责计数：  所有索引访问方法都会被修改，以便在每次开始新的索引搜索时递增一个新的 nsearches 计数器。
• 计数时机：  计数器递增的时机与索引访问方法递增 pg_stat_*_indexes.idx_scan 计数器（统计索引扫描次数）的时机完全相同。  区别在于，nsearches 是在扫描级别计数，而 idx_scan 是在关系级别计数。
• 并行查询：  对于并行查询，工作进程（worker）会在索引扫描节点结束时将其本地计数器结构复制到共享内存中，即使该扫描节点不是并行感知的（parallel aware）。
• 与现有成本跟踪方法的不同：
• 直接在访问方法中维护：  与其他索引扫描相关的成本跟踪（例如 "Rows Removed by Filter:"）不同，这个补丁选择直接在索引访问方法中维护计数器。
• 原因：nsearches 计数器可能需要在每次 amgettuple 调用（或每次 amgetbitmap 调用）中递增多次，因此需要在访问方法中直接维护。
• 共享内存管理：
• 执行器（Executor）负责共享内存：  执行器负责管理用于将每个工作进程的计数器结构传输到领导进程（leader）的共享内存。

背景和历史：

• 之前的尝试：  之前有一个只适用于并行感知扫描的补丁（commit 5ead85fb），但由于构建场（buildfarm）的故障（"debug_parallel_query=regress"），该补丁很快被回滚（commit d00107cd）。
• 类似方法的借鉴：  该补丁借鉴了类似情况下成本跟踪的方法，这些成本只能在访问方法内部访问，而无法从执行器直接访问（例如，Bitmap Heap Scan 的 "Heap Blocks:" 输出）。

总结：

这个补丁通过在 EXPLAIN ANALYZE 的输出中显示索引搜索计数，增强了 PostgreSQL 的查询性能分析能力。 它特别有助于理解 ScalarArrayOp 限定词和跳跃扫描优化等复杂场景下的索引扫描行为。 通过在索引访问方法中直接维护计数器并使用共享内存来处理并行查询，该补丁提供了一种有效且准确的索引扫描性能度量方法。