public inbox for [email protected]
help / color / mirror / Atom feedFrom: Peter Geoghegan <[email protected]>
To: [email protected]
Subject: pgsql: Show index search count in EXPLAIN ANALYZE.
Date: Wed, 05 Mar 2025 14:37:00 +0000
Message-ID: <[email protected]> (raw)
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(-)
view thread (3+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: pgsql: Show index search count in EXPLAIN ANALYZE.
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox