Custom explain options

From: Konstantin Knizhnik <knizhnik(at)garret(dot)ru>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Custom explain options
Date: 2023-10-21 12:16:33
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Hi hackers,

EXPLAIN statement has a list of options (i.e. ANALYZE, BUFFERS,
COST,...) which help to provide useful details of query execution.
In Neon we have added PREFETCH option which shows information about page
prefetching during query execution (prefetching is more critical for Neon
architecture because of separation of compute and storage, so it is
implemented not only for bitmap heap scan as in Vanilla Postgres, but
also for seqscan, indexscan and indexonly scan). Another possible
candidate  for explain options is local file cache (extra caching layer
above shared buffers which is used to somehow replace file system cache
in standalone Postgres).

I think that it will be nice to have a generic mechanism which allows
extensions to add its own options to EXPLAIN.
I have attached the patch with implementation of such mechanism (also
available as PR: )

I have demonstrated this mechanism using Bloom extension - just to
report number of Bloom matches.
Not sure that it is really useful information but it is used mostly as

explain (analyze,bloom) select * from t where pk=2000;
Bitmap Heap Scan on t (cost=15348.00..15352.01 rows=1 width=4) (actual time=25.244..25.939 rows=1 loops=1)
Recheck Cond: (pk = 2000)
Rows Removed by Index Recheck: 292
Heap Blocks: exact=283
Bloom: matches=293
-> Bitmap Index Scan on t_pk_idx (cost=0.00..15348.00 rows=1 width=0) (actual time=25.147..25.147 rows=293 loops=1)
Index Cond: (pk = 2000)
Bloom: matches=293
Bloom: matches=0
Planning Time: 0.387 ms
Execution Time: 26.053 ms
(12 rows)

There are two known issues with this proposal:

1. I have to limit total size of all custom metrics - right now it is
limited by 128 bytes. It is done to keep|Instrumentation|and some other
data structures fixes size. Otherwise maintaining varying parts of this
structure is ugly, especially in shared memory

2. Custom extension is added by means
of|RegisterCustomInsrumentation|function which is called from|_PG_init|
But|_PG_init|is called when extension is loaded and it is loaded on
demand when some of extension functions is called (except when extension
is included
in shared_preload_libraries list), Bloom extension doesn't require it.
So if your first statement executed in your session is:

explain (analyze,bloom) select * from t where pk=2000; will get error:

ERROR: unrecognized EXPLAIN option "bloom"
LINE 1: explain (analyze,bloom) select * from t where pk=2000;

It happens because at the moment when explain statement parses options,
Bloom index is not yet selected and so bloom extension is not loaded
and|RegisterCustomInsrumentation|is not yet called. If we repeat the
query, then proper result will be displayed (see above).

Attachment Content-Type Size
custom_explain_options.patch text/plain 31.1 KB


Browse pgsql-hackers by date

  From Date Subject
Next Message Gurjeet Singh 2023-10-21 16:56:15 Re: Remove extraneous break condition in logical slot advance function
Previous Message Tom Lane 2023-10-21 06:32:06 Re: Remove last traces of HPPA support