public inbox for [email protected]
help / color / mirror / Atom feedpgsql: Show index search count in EXPLAIN ANALYZE.
3+ messages / 1 participants
[nested] [flat]
* pgsql: Show index search count in EXPLAIN ANALYZE.
@ 2025-03-05 14:37 Peter Geoghegan <[email protected]>
2025-03-05 15:01 ` Re: pgsql: Show index search count in EXPLAIN ANALYZE. Peter Geoghegan <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Peter Geoghegan @ 2025-03-05 14:37 UTC (permalink / raw)
To: [email protected]
Show index search count in EXPLAIN ANALYZE.
Expose the count of index searches/index descents in EXPLAIN ANALYZE's
output for index scan nodes. This information is particularly useful
with scans that use ScalarArrayOp quals, where the number of index scans
isn't predictable in advance (at least not with optimizations like the
one added to nbtree by Postgres 17 commit 5bf748b8). It will also be
useful when EXPLAIN ANALYZE shows details of an nbtree index scan that
uses skip scan optimizations set to be introduced by an upcoming patch.
The instrumentation works by teaching 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 must already
increment the index's pg_stat_*_indexes.idx_scan counter (we're counting
the same event, but at the scan level rather than the relation level).
The new counter is stored in the scan descriptor (IndexScanDescData),
which explain.c reaches by going through the scan node's PlanState.
This approach doesn't match the approach used when tracking other index
scan specific costs (e.g., "Rows Removed by Filter:"). It is similar to
the approach used in other cases where we must track costs that are only
readily accessible inside an access method, and not from the executor
(e.g., "Heap Blocks:" output for a Bitmap Heap Scan). It is inherently
necessary to maintain a counter that can be incremented multiple times
during a single amgettuple call (or amgetbitmap call), and directly
exposing PlanState.instrument to index access methods seems unappealing.
Author: Peter Geoghegan <[email protected]>
Reviewed-By: Tomas Vondra <[email protected]>
Reviewed-By: Robert Haas <[email protected]>
Reviewed-By: Masahiro Ikeda <[email protected]>
Reviewed-By: Matthias van de Meent <[email protected]>
Discussion: https://postgr.es/m/CAH2-Wz=PKR6rB7qbx+Vnd7eqeB5VTcrW=iJvAsTsKbdG+kW_UA@mail.gmail.com
Discussion: https://postgr.es/m/CAH2-WzkRqvaqR2CTNqTZP0z6FuL4-3ED6eQB0yx38XBNj1v-4Q@mail.gmail.com
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/5ead85fbc81162ab1594f656b036a22e814f96b3
Modified Files
--------------
contrib/bloom/blscan.c | 1 +
doc/src/sgml/bloom.sgml | 7 +-
doc/src/sgml/monitoring.sgml | 28 ++++++--
doc/src/sgml/perform.sgml | 60 ++++++++++++++++
doc/src/sgml/ref/explain.sgml | 3 +-
doc/src/sgml/rules.sgml | 2 +
src/backend/access/brin/brin.c | 1 +
src/backend/access/gin/ginscan.c | 1 +
src/backend/access/gist/gistget.c | 2 +
src/backend/access/hash/hashsearch.c | 1 +
src/backend/access/index/genam.c | 1 +
src/backend/access/nbtree/nbtree.c | 15 ++++
src/backend/access/nbtree/nbtsearch.c | 1 +
src/backend/access/spgist/spgscan.c | 1 +
src/backend/commands/explain.c | 40 +++++++++++
src/include/access/relscan.h | 7 ++
src/test/regress/expected/brin_multi.out | 27 ++++---
src/test/regress/expected/memoize.out | 49 +++++++++----
src/test/regress/expected/partition_prune.out | 100 ++++++++++++++++++++++----
src/test/regress/expected/select.out | 3 +-
src/test/regress/sql/memoize.sql | 5 +-
src/test/regress/sql/partition_prune.sql | 4 ++
22 files changed, 310 insertions(+), 49 deletions(-)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pgsql: Show index search count in EXPLAIN ANALYZE.
2025-03-05 14:37 pgsql: Show index search count in EXPLAIN ANALYZE. Peter Geoghegan <[email protected]>
@ 2025-03-05 15:01 ` Peter Geoghegan <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Peter Geoghegan @ 2025-03-05 15:01 UTC (permalink / raw)
To: [email protected]
On Wed, Mar 5, 2025 at 9:37 AM Peter Geoghegan <[email protected]> wrote:
> Show index search count in EXPLAIN ANALYZE.
I'm aware of the fact that there are currently buildfarm failures due
to test flappiness.
I'll work to fix the issues today. If I am unable to find a resolution
within a few hours, then I'll revert.
--
Peter Geoghegan
^ permalink raw reply [nested|flat] 3+ messages in thread
* pgsql: Show index search count in EXPLAIN ANALYZE, take 2.
@ 2025-03-11 13:21 Peter Geoghegan <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Peter Geoghegan @ 2025-03-11 13:21 UTC (permalink / raw)
To: [email protected]
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
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/0fbceae841cb5a31b13d3f284ac8fdd19822eceb
Modified Files
--------------
contrib/bloom/blscan.c | 2 +
doc/src/sgml/bloom.sgml | 7 +-
doc/src/sgml/monitoring.sgml | 28 +++++--
doc/src/sgml/perform.sgml | 60 +++++++++++++
doc/src/sgml/ref/explain.sgml | 3 +-
doc/src/sgml/rules.sgml | 2 +
src/backend/access/brin/brin.c | 2 +
src/backend/access/gin/ginscan.c | 2 +
src/backend/access/gist/gistget.c | 4 +
src/backend/access/hash/hashsearch.c | 2 +
src/backend/access/heap/heapam_handler.c | 2 +-
src/backend/access/index/genam.c | 5 +-
src/backend/access/index/indexam.c | 70 +++++++++++++---
src/backend/access/nbtree/nbtree.c | 10 +--
src/backend/access/nbtree/nbtsearch.c | 2 +
src/backend/access/spgist/spgscan.c | 2 +
src/backend/commands/explain.c | 63 ++++++++++++++
src/backend/executor/execIndexing.c | 2 +-
src/backend/executor/execParallel.c | 59 +++++++++----
src/backend/executor/execReplication.c | 2 +-
src/backend/executor/nodeBitmapIndexscan.c | 116 ++++++++++++++++++++++++++
src/backend/executor/nodeIndexonlyscan.c | 96 ++++++++++++++++++++-
src/backend/executor/nodeIndexscan.c | 97 ++++++++++++++++++++-
src/backend/utils/adt/selfuncs.c | 2 +-
src/include/access/genam.h | 34 +++++++-
src/include/access/relscan.h | 11 ++-
src/include/executor/nodeBitmapIndexscan.h | 6 ++
src/include/executor/nodeIndexonlyscan.h | 1 +
src/include/executor/nodeIndexscan.h | 1 +
src/include/nodes/execnodes.h | 12 +++
src/test/regress/expected/brin_multi.out | 27 ++++--
src/test/regress/expected/memoize.out | 49 +++++++----
src/test/regress/expected/partition_prune.out | 100 ++++++++++++++++++----
src/test/regress/expected/select.out | 3 +-
src/test/regress/sql/memoize.sql | 5 +-
src/test/regress/sql/partition_prune.sql | 4 +
src/tools/pgindent/typedefs.list | 2 +
37 files changed, 797 insertions(+), 98 deletions(-)
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-03-11 13:21 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-03-05 14:37 pgsql: Show index search count in EXPLAIN ANALYZE. Peter Geoghegan <[email protected]>
2025-03-05 15:01 ` Peter Geoghegan <[email protected]>
2025-03-11 13:21 pgsql: Show index search count in EXPLAIN ANALYZE, take 2. Peter Geoghegan <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox