On Sat, 21 Oct 2023 at 18:34, Konstantin Knizhnik <knizh...@garret.ru> wrote: > > 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: https://github.com/knizhnik/postgres/pull/1 ) > > 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 > example: > > explain (analyze,bloom) select * from t where pk=2000; > QUERY PLAN > ------------------------------------------------------------------------------------------------------------------------- > 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 > Planning: > Bloom: matches=0 > Planning Time: 0.387 ms > Execution Time: 26.053 ms > (12 rows) > > There are two known issues with this proposal:
There are few compilation errors reported by CFBot at [1] with: [05:00:40.452] ../src/backend/access/brin/brin.c: In function ‘_brin_end_parallel’: [05:00:40.452] ../src/backend/access/brin/brin.c:2675:3: error: too few arguments to function ‘InstrAccumParallelQuery’ [05:00:40.452] 2675 | InstrAccumParallelQuery(&brinleader->bufferusage[i], &brinleader->walusage[i]); [05:00:40.452] | ^~~~~~~~~~~~~~~~~~~~~~~ [05:00:40.452] In file included from ../src/include/nodes/execnodes.h:33, [05:00:40.452] from ../src/include/access/brin.h:13, [05:00:40.452] from ../src/backend/access/brin/brin.c:18: [05:00:40.452] ../src/include/executor/instrument.h:151:13: note: declared here [05:00:40.452] 151 | extern void InstrAccumParallelQuery(BufferUsage *bufusage, WalUsage *walusage, char* custusage); [05:00:40.452] | ^~~~~~~~~~~~~~~~~~~~~~~ [05:00:40.452] ../src/backend/access/brin/brin.c: In function ‘_brin_parallel_build_main’: [05:00:40.452] ../src/backend/access/brin/brin.c:2873:2: error: too few arguments to function ‘InstrEndParallelQuery’ [05:00:40.452] 2873 | InstrEndParallelQuery(&bufferusage[ParallelWorkerNumber], [05:00:40.452] | ^~~~~~~~~~~~~~~~~~~~~ [05:00:40.452] In file included from ../src/include/nodes/execnodes.h:33, [05:00:40.452] from ../src/include/access/brin.h:13, [05:00:40.452] from ../src/backend/access/brin/brin.c:18: [05:00:40.452] ../src/include/executor/instrument.h:150:13: note: declared here [05:00:40.452] 150 | extern void InstrEndParallelQuery(BufferUsage *bufusage, WalUsage *walusage, char* custusage); [1] - https://cirrus-ci.com/task/5452124486631424?logs=build#L374 Regards, Vignesh
