From f278bd14ca6fcd27b02ebb14feed797d0da721ad Mon Sep 17 00:00:00 2001 From: Tomas Vondra Date: Tue, 17 Mar 2026 22:06:46 +0100 Subject: [PATCH v3 3/6] cleanup and simplification - Reduce code duplication in explain.c by introducing a couple helper methods for repeated blocks - ACCUMULATE_TABLE_STATS() and show_io_info(). - Replace ReadStreamInstrumentation with generic IOStats. - new read_stream_update_stats_stall() wrapper - enable EXPLAIN (IO) by default, just like BUFFERS - fix the explain group to have matching objtype/label - don't update prefetch stats after stream ended fixup: track prefetch distance --- doc/src/sgml/ref/explain.sgml | 2 +- src/backend/access/heap/heapam.c | 2 +- src/backend/commands/explain.c | 242 +++++++----------- src/backend/commands/explain_state.c | 5 + src/backend/executor/nodeBitmapHeapscan.c | 11 +- src/backend/executor/nodeSeqscan.c | 11 +- src/backend/storage/aio/read_stream.c | 26 +- src/include/access/relscan.h | 14 + src/include/executor/instrument_node.h | 8 +- src/include/storage/read_stream.h | 4 +- src/test/regress/expected/explain.out | 39 ++- .../regress/expected/incremental_sort.out | 4 +- src/test/regress/expected/memoize.out | 2 +- src/test/regress/expected/merge.out | 2 +- src/test/regress/expected/partition_prune.out | 75 ++++-- src/test/regress/expected/select_parallel.out | 6 +- src/test/regress/expected/subselect.out | 2 +- src/test/regress/sql/explain.sql | 24 +- src/test/regress/sql/incremental_sort.sql | 4 +- src/test/regress/sql/memoize.sql | 2 +- src/test/regress/sql/merge.sql | 2 +- src/test/regress/sql/partition_prune.sql | 2 +- src/test/regress/sql/select_parallel.sql | 6 +- src/test/regress/sql/subselect.sql | 2 +- 24 files changed, 252 insertions(+), 245 deletions(-) diff --git a/doc/src/sgml/ref/explain.sgml b/doc/src/sgml/ref/explain.sgml index 9da16c77d73..83f27badfc8 100644 --- a/doc/src/sgml/ref/explain.sgml +++ b/doc/src/sgml/ref/explain.sgml @@ -302,7 +302,7 @@ ROLLBACK; Include information on I/O performed by each node. This parameter may only be used when ANALYZE is also - enabled. It defaults to FALSE. + enabled. It defaults to TRUE. diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c index b13f843cfad..323badd27f1 100644 --- a/src/backend/access/heap/heapam.c +++ b/src/backend/access/heap/heapam.c @@ -1426,7 +1426,7 @@ TableScanStats heap_scan_stats(TableScanDesc sscan) { HeapScanDesc scan = (HeapScanDesc) sscan; - ReadStreamInstrumentation stats; + IOStats stats; TableScanStats res; if (!scan->rs_read_stream) diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index ad86fb752ca..7a4009ae74f 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -141,11 +141,11 @@ static void show_hashagg_info(AggState *aggstate, ExplainState *es); static void show_indexsearches_info(PlanState *planstate, ExplainState *es); static void show_tidbitmap_info(BitmapHeapScanState *planstate, ExplainState *es); -static void show_scan_io_info(ScanState *planstate, - ExplainState *es); -static void show_worker_io_info(PlanState *planstate, - ExplainState *es, - int worker); +static void show_scan_io_usage(ScanState *planstate, + ExplainState *es); +static void show_io_usage(PlanState *planstate, + ExplainState *es, + int worker); static void show_instrumentation_count(const char *qlabel, int which, PlanState *planstate, ExplainState *es); static void show_foreignscan_info(ForeignScanState *fsstate, ExplainState *es); @@ -2016,7 +2016,7 @@ ExplainNode(PlanState *planstate, List *ancestors, show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); show_tidbitmap_info((BitmapHeapScanState *) planstate, es); - show_scan_io_info((ScanState *) planstate, es); + show_scan_io_usage((ScanState *) planstate, es); break; case T_SampleScan: show_tablesample(((SampleScan *) plan)->tablesample, @@ -2035,7 +2035,7 @@ ExplainNode(PlanState *planstate, List *ancestors, planstate, es); if (IsA(plan, CteScan)) show_ctescan_info(castNode(CteScanState, planstate), es); - show_scan_io_info((ScanState *) planstate, es); + show_scan_io_usage((ScanState *) planstate, es); break; case T_Gather: { @@ -2324,9 +2324,8 @@ ExplainNode(PlanState *planstate, List *ancestors, show_buffer_usage(es, &instrument->bufusage); if (es->wal) show_wal_usage(es, &instrument->walusage); - - /* show prefetch info for the given worker */ - show_worker_io_info(planstate, es, n); + if (es->io) + show_io_usage(planstate, es, n); ExplainCloseWorker(n, es); } @@ -3998,14 +3997,74 @@ show_tidbitmap_info(BitmapHeapScanState *planstate, ExplainState *es) } } +static void +print_io_usage(ExplainState *es, TableScanStats stats) +{ + /* don't print stats if there's nothing to report */ + if (stats->prefetch_count > 0) + { + if (es->format == EXPLAIN_FORMAT_TEXT) + { + /* prefetch distance info */ + ExplainIndentText(es); + appendStringInfo(es->str, "Prefetch: avg=%.3f max=%d capacity=%d", + (stats->distance_sum * 1.0 / stats->prefetch_count), + stats->distance_max, + stats->distance_capacity); + appendStringInfoChar(es->str, '\n'); + + /* prefetch I/O info (only if there were actual I/Os) */ + if (stats->io_count > 0) + { + ExplainIndentText(es); + appendStringInfo(es->str, "I/O: stalls=%" PRIu64, + stats->stall_count); + + appendStringInfo(es->str, " size=%.3f inprogress=%.3f", + (stats->io_nblocks * 1.0 / stats->io_count), + (stats->io_in_progress * 1.0 / stats->io_count)); + + appendStringInfoChar(es->str, '\n'); + } + } + else + { + ExplainOpenGroup("Prefetch", "Prefetch", true, es); + + ExplainPropertyFloat("Average Distance", NULL, + (stats->distance_sum * 1.0 / stats->prefetch_count), 3, es); + ExplainPropertyInteger("Max Distance", NULL, + stats->distance_max, es); + ExplainPropertyInteger("Capacity", NULL, + stats->distance_capacity, es); + + ExplainCloseGroup("Prefetch", "Prefetch", true, es); + + if (stats->io_count > 0) + { + ExplainOpenGroup("I/O", "I/O", true, es); + + ExplainPropertyUInteger("Stalls", NULL, + stats->stall_count, es); + ExplainPropertyFloat("Average IO Size", NULL, + (stats->io_nblocks * 1.0 / stats->io_count), 3, es); + ExplainPropertyFloat("Average IOs In Progress", NULL, + (stats->io_in_progress * 1.0 / stats->io_count), 3, es); + + ExplainCloseGroup("I/O", "I/O", true, es); + } + } + } +} + /* - * show_scan_io_info + * show_scan_io_usage * show info about prefetching for a seq/bitmap scan * * Shows summary of stats for leader and workers (if any). */ static void -show_scan_io_info(ScanState *planstate, ExplainState *es) +show_scan_io_usage(ScanState *planstate, ExplainState *es) { Plan *plan = planstate->ps.plan; TableScanStats leader_stats; @@ -4044,17 +4103,7 @@ show_scan_io_info(ScanState *planstate, ExplainState *es) for (int i = 0; i < sinstrument->num_workers; ++i) { SeqScanInstrumentation *winstrument = &sinstrument->sinstrument[i]; - - stats.prefetch_count += winstrument->stream.prefetch_count; - stats.distance_sum += winstrument->stream.distance_sum; - if (winstrument->stream.distance_max > stats.distance_max) - stats.distance_max = winstrument->stream.distance_max; - if (winstrument->stream.distance_capacity > stats.distance_capacity) - stats.distance_capacity = winstrument->stream.distance_capacity; - stats.stall_count += winstrument->stream.stall_count; - stats.io_count += winstrument->stream.io_count; - stats.io_nblocks += winstrument->stream.io_nblocks; - stats.io_in_progress += winstrument->stream.io_in_progress; + ACCUMULATE_TABLE_STATS(&stats, &winstrument->io); } } @@ -4071,17 +4120,7 @@ show_scan_io_info(ScanState *planstate, ExplainState *es) for (int i = 0; i < sinstrument->num_workers; ++i) { BitmapHeapScanInstrumentation *winstrument = &sinstrument->sinstrument[i]; - - stats.prefetch_count += winstrument->stream.prefetch_count; - stats.distance_sum += winstrument->stream.distance_sum; - if (winstrument->stream.distance_max > stats.distance_max) - stats.distance_max = winstrument->stream.distance_max; - if (winstrument->stream.distance_capacity > stats.distance_capacity) - stats.distance_capacity = winstrument->stream.distance_capacity; - stats.stall_count += winstrument->stream.stall_count; - stats.io_count += winstrument->stream.io_count; - stats.io_nblocks += winstrument->stream.io_nblocks; - stats.io_in_progress += winstrument->stream.io_in_progress; + ACCUMULATE_TABLE_STATS(&stats, &winstrument->io); } } @@ -4092,73 +4131,23 @@ show_scan_io_info(ScanState *planstate, ExplainState *es) return; } - /* don't print anything without prefetching */ - if (stats.prefetch_count > 0) - { - if (es->format == EXPLAIN_FORMAT_TEXT) - { - /* prefetch distance info */ - ExplainIndentText(es); - appendStringInfo(es->str, "Prefetch: avg=%.3f max=%d capacity=%d", - (stats.distance_sum * 1.0 / stats.prefetch_count), - stats.distance_max, - stats.distance_capacity); - appendStringInfoChar(es->str, '\n'); - - /* prefetch I/O info (only if there were actual I/Os) */ - if (stats.stall_count > 0 || stats.io_count > 0) - { - ExplainIndentText(es); - appendStringInfo(es->str, "I/O: stalls=%" PRIu64, - stats.stall_count); - - if (stats.io_count > 0) - { - appendStringInfo(es->str, " size=%.3f inprogress=%.3f", - (stats.io_nblocks * 1.0 / stats.io_count), - (stats.io_in_progress * 1.0 / stats.io_count)); - } - - appendStringInfoChar(es->str, '\n'); - } - } - else - { - ExplainOpenGroup("Prefetch", "I/O", true, es); - - ExplainPropertyFloat("Average Distance", NULL, - (stats.distance_sum * 1.0 / stats.prefetch_count), 3, es); - ExplainPropertyInteger("Max Distance", NULL, - stats.distance_max, es); - ExplainPropertyInteger("Capacity", NULL, - stats.distance_capacity, es); - ExplainPropertyUInteger("Stalls", NULL, - stats.stall_count, es); - - if (stats.io_count > 0) - { - ExplainPropertyFloat("Average IO Size", NULL, - (stats.io_nblocks * 1.0 / stats.io_count), 3, es); - ExplainPropertyFloat("Average IOs In Progress", NULL, - (stats.io_in_progress * 1.0 / stats.io_count), 3, es); - } - - ExplainCloseGroup("Prefetch", "I/O", true, es); - } - } + print_io_usage(es, &stats); } /* - * show_io_worker_info - * show info about prefetching for a single worker + * show_io_usage + * show info about I/O prefetching for a single worker * * Shows prefetching stats for a parallel scan worker. */ static void -show_worker_io_info(PlanState *planstate, ExplainState *es, int worker) +show_io_usage(PlanState *planstate, ExplainState *es, int worker) { Plan *plan = planstate->plan; - ReadStreamInstrumentation *stats = NULL; + IOStats *stats = NULL; + + // XXX + TableScanStatsData tmp; if (!es->io) return; @@ -4172,7 +4161,7 @@ show_worker_io_info(PlanState *planstate, ExplainState *es, int worker) SharedBitmapHeapInstrumentation *sinstrument = state->sinstrument; BitmapHeapScanInstrumentation *instrument = &sinstrument->sinstrument[worker]; - stats = &instrument->stream; + stats = &instrument->io; break; } @@ -4182,7 +4171,7 @@ show_worker_io_info(PlanState *planstate, ExplainState *es, int worker) SharedSeqScanInstrumentation *sinstrument = state->sinstrument; SeqScanInstrumentation *instrument = &sinstrument->sinstrument[worker]; - stats = &instrument->stream; + stats = &instrument->io; break; } @@ -4191,60 +4180,17 @@ show_worker_io_info(PlanState *planstate, ExplainState *es, int worker) return; } - /* don't print stats if there's nothing to report */ - if (stats->prefetch_count > 0) - { - if (es->format == EXPLAIN_FORMAT_TEXT) - { - /* prefetch distance info */ - ExplainIndentText(es); - appendStringInfo(es->str, "Prefetch: avg=%.3f max=%d capacity=%d", - (stats->distance_sum * 1.0 / stats->prefetch_count), - stats->distance_max, - stats->distance_capacity); - appendStringInfoChar(es->str, '\n'); - - /* prefetch I/O info (only if there were actual I/Os) */ - if (stats->stall_count > 0 || stats->io_count > 0) - { - ExplainIndentText(es); - appendStringInfo(es->str, "I/O: stalls=%" PRIu64, - stats->stall_count); - - if (stats->io_count > 0) - { - appendStringInfo(es->str, " size=%.3f inprogress=%.3f", - (stats->io_nblocks * 1.0 / stats->io_count), - (stats->io_in_progress * 1.0 / stats->io_count)); - } - - appendStringInfoChar(es->str, '\n'); - } - } - else - { - ExplainOpenGroup("Prefetch", "I/O", true, es); - - ExplainPropertyFloat("Average Distance", NULL, - (stats->distance_sum * 1.0 / stats->prefetch_count), 3, es); - ExplainPropertyInteger("Max Distance", NULL, - stats->distance_max, es); - ExplainPropertyInteger("Capacity", NULL, - stats->distance_capacity, es); - ExplainPropertyUInteger("Stalls", NULL, - stats->stall_count, es); - - if (stats->io_count > 0) - { - ExplainPropertyFloat("Average IO Size", NULL, - (stats->io_nblocks * 1.0 / stats->io_count), 3, es); - ExplainPropertyFloat("Average IOs In Progress", NULL, - (stats->io_in_progress * 1.0 / stats->io_count), 3, es); - } - - ExplainCloseGroup("Prefetch", "I/O", true, es); - } - } + /* XXX convert to TableScanStats */ + tmp.distance_capacity = stats->distance_capacity; + tmp.distance_max = stats->distance_max; + tmp.distance_sum = stats->distance_sum; + tmp.io_count = stats->io_count; + tmp.io_in_progress = stats->io_in_progress; + tmp.io_nblocks = stats->io_nblocks; + tmp.prefetch_count = stats->prefetch_count; + tmp.stall_count = stats->stall_count; + + print_io_usage(es, &tmp); } /* diff --git a/src/backend/commands/explain_state.c b/src/backend/commands/explain_state.c index f5cbdd21aaa..3d5f92bfa91 100644 --- a/src/backend/commands/explain_state.c +++ b/src/backend/commands/explain_state.c @@ -79,6 +79,7 @@ ParseExplainOptionList(ExplainState *es, List *options, ParseState *pstate) ListCell *lc; bool timing_set = false; bool buffers_set = false; + bool io_set = false; bool summary_set = false; /* Parse options list. */ @@ -161,6 +162,7 @@ ParseExplainOptionList(ExplainState *es, List *options, ParseState *pstate) } else if (strcmp(opt->defname, "io") == 0) { + io_set = true; es->io = defGetBoolean(opt); } else if (!ApplyExtensionExplainOption(es, opt, pstate)) @@ -183,6 +185,9 @@ ParseExplainOptionList(ExplainState *es, List *options, ParseState *pstate) /* if the buffers was not set explicitly, set default value */ es->buffers = (buffers_set) ? es->buffers : es->analyze; + /* if the IO was not set explicitly, set default value */ + es->io = (io_set) ? es->io : es->analyze; + /* check that timing is used with EXPLAIN ANALYZE */ if (es->timing && !es->analyze) ereport(ERROR, diff --git a/src/backend/executor/nodeBitmapHeapscan.c b/src/backend/executor/nodeBitmapHeapscan.c index bf9af7596ce..0f1459f11a4 100644 --- a/src/backend/executor/nodeBitmapHeapscan.c +++ b/src/backend/executor/nodeBitmapHeapscan.c @@ -333,16 +333,7 @@ ExecEndBitmapHeapScan(BitmapHeapScanState *node) /* collect prefetch info for this process from the read_stream */ if ((stats = table_scan_stats(node->ss.ss_currentScanDesc)) != NULL) { - si->stream.prefetch_count += stats->prefetch_count; - si->stream.distance_sum += stats->distance_sum; - if (stats->distance_max > si->stream.distance_max) - si->stream.distance_max = stats->distance_max; - if (stats->distance_capacity > si->stream.distance_capacity) - si->stream.distance_capacity = stats->distance_capacity; - si->stream.stall_count += stats->stall_count; - si->stream.io_count += stats->io_count; - si->stream.io_nblocks += stats->io_nblocks; - si->stream.io_in_progress += stats->io_in_progress; + ACCUMULATE_TABLE_STATS(stats, &si->io); } } diff --git a/src/backend/executor/nodeSeqscan.c b/src/backend/executor/nodeSeqscan.c index 7992946a5be..4fa85a1b6bc 100644 --- a/src/backend/executor/nodeSeqscan.c +++ b/src/backend/executor/nodeSeqscan.c @@ -319,16 +319,7 @@ ExecEndSeqScan(SeqScanState *node) /* collect prefetch info for this process from the read_stream */ if ((stats = table_scan_stats(node->ss.ss_currentScanDesc)) != NULL) { - si->stream.prefetch_count += stats->prefetch_count; - si->stream.distance_sum += stats->distance_sum; - if (stats->distance_max > si->stream.distance_max) - si->stream.distance_max = stats->distance_max; - if (stats->distance_capacity > si->stream.distance_capacity) - si->stream.distance_capacity = stats->distance_capacity; - si->stream.stall_count += stats->stall_count; - si->stream.io_count += stats->io_count; - si->stream.io_nblocks += stats->io_nblocks; - si->stream.io_in_progress += stats->io_in_progress; + ACCUMULATE_TABLE_STATS(stats, &si->io); } } diff --git a/src/backend/storage/aio/read_stream.c b/src/backend/storage/aio/read_stream.c index f560047afd6..faa52ca4b2c 100644 --- a/src/backend/storage/aio/read_stream.c +++ b/src/backend/storage/aio/read_stream.c @@ -108,8 +108,8 @@ struct ReadStream bool advice_enabled; bool temporary; - /* stats counters */ - ReadStreamInstrumentation stats; + /* scan stats counters */ + IOStats stats; /* * One-block buffer to support 'ungetting' a block number, to resolve flow @@ -177,8 +177,7 @@ block_range_read_stream_cb(ReadStream *stream, } /* - * read_stream_update_stats_prefetch - * update read_stream stats with current pinned buffer depth + * Update stream stats with current pinned buffer depth. * * Called once per buffer returned to the consumer in read_stream_next_buffer(). * Records the number of pinned buffers at that moment, so we can compute the @@ -194,8 +193,7 @@ read_stream_update_stats_prefetch(ReadStream *stream) } /* - * read_stream_update_stats_io - * update read_stream stats about size of I/O requests + * Update stream stats about size of I/O requests. * * We count the number of I/O requests, size of requests (counted in blocks) * and number of in-progress I/Os. @@ -208,6 +206,12 @@ read_stream_update_stats_io(ReadStream *stream, int nblocks, int in_progress) stream->stats.io_in_progress += in_progress; } +static inline void +read_stream_update_stats_stall(ReadStream *stream) +{ + stream->stats.stall_count++; +} + /* * Ask the callback which block it would like us to read next, with a one block * buffer in front to allow read_stream_unget_block() to work. @@ -743,7 +747,7 @@ read_stream_begin_impl(int flags, stream->temporary = SmgrIsTemp(smgr); /* zero the stats, then set capacity */ - memset(&stream->stats, 0, sizeof(ReadStreamInstrumentation)); + memset(&stream->stats, 0, sizeof(IOStats)); stream->stats.distance_capacity = max_pinned_buffers; /* @@ -907,6 +911,9 @@ read_stream_next_buffer(ReadStream *stream, void **per_buffer_data) /* update I/O stats */ read_stream_update_stats_io(stream, 1, stream->ios_in_progress); + + /* update prefetch distance */ + read_stream_update_stats_prefetch(stream); } else { @@ -918,7 +925,6 @@ read_stream_next_buffer(ReadStream *stream, void **per_buffer_data) } stream->fast_path = false; - read_stream_update_stats_prefetch(stream); return buffer; } #endif @@ -974,7 +980,7 @@ read_stream_next_buffer(ReadStream *stream, void **per_buffer_data) /* Count it as a stall if we need to wait for IO */ if (needed_wait) - stream->stats.stall_count += 1; + read_stream_update_stats_stall(stream); Assert(stream->ios_in_progress > 0); stream->ios_in_progress--; @@ -1175,7 +1181,7 @@ read_stream_end(ReadStream *stream) } /* return the prefetch stats for the read_stream */ -ReadStreamInstrumentation +IOStats read_stream_prefetch_stats(ReadStream *stream) { return stream->stats; diff --git a/src/include/access/relscan.h b/src/include/access/relscan.h index cada9f28bd8..c0488987b2d 100644 --- a/src/include/access/relscan.h +++ b/src/include/access/relscan.h @@ -141,6 +141,20 @@ typedef struct TableScanStatsData } TableScanStatsData; typedef struct TableScanStatsData *TableScanStats; +#define ACCUMULATE_TABLE_STATS(dst, src) \ + do { \ + (dst)->prefetch_count += (src)->prefetch_count; \ + (dst)->distance_sum += (src)->distance_sum; \ + if ((src)->distance_max > (dst)->distance_max) \ + (dst)->distance_max = (src)->distance_max; \ + if ((src)->distance_capacity > (dst)->distance_capacity) \ + (dst)->distance_capacity = (src)->distance_capacity; \ + (dst)->stall_count += (src)->stall_count; \ + (dst)->io_count += (src)->io_count; \ + (dst)->io_nblocks += (src)->io_nblocks; \ + (dst)->io_in_progress += (src)->io_in_progress; \ + } while (0) + /* * Base class for fetches from a table via an index. This is the base-class * for such scans, which needs to be embedded in the respective struct for diff --git a/src/include/executor/instrument_node.h b/src/include/executor/instrument_node.h index d94dbbc917d..cda00ad40e9 100644 --- a/src/include/executor/instrument_node.h +++ b/src/include/executor/instrument_node.h @@ -44,7 +44,7 @@ typedef struct SharedAggInfo * Instrumentation information about read streams * --------------------- */ -typedef struct ReadStreamInstrumentation +typedef struct IOStats { /* number of buffers returned to consumer (for averaging distance) */ uint64 prefetch_count; @@ -65,7 +65,7 @@ typedef struct ReadStreamInstrumentation uint64 io_count; /* number of I/Os */ uint64 io_nblocks; /* sum of blocks for all I/Os */ uint64 io_in_progress; /* sum of in-progress I/Os */ -} ReadStreamInstrumentation; +} IOStats; /* --------------------- @@ -74,7 +74,7 @@ typedef struct ReadStreamInstrumentation */ typedef struct SeqScanInstrumentation { - ReadStreamInstrumentation stream; + IOStats io; } SeqScanInstrumentation; /* @@ -113,7 +113,7 @@ typedef struct BitmapHeapScanInstrumentation { uint64 exact_pages; uint64 lossy_pages; - ReadStreamInstrumentation stream; + IOStats io; } BitmapHeapScanInstrumentation; /* diff --git a/src/include/storage/read_stream.h b/src/include/storage/read_stream.h index ec396db5369..0676bdc9b14 100644 --- a/src/include/storage/read_stream.h +++ b/src/include/storage/read_stream.h @@ -65,7 +65,7 @@ struct ReadStream; typedef struct ReadStream ReadStream; -typedef struct ReadStreamInstrumentation ReadStreamInstrumentation; +typedef struct IOStats IOStats; /* for block_range_read_stream_cb */ typedef struct BlockRangeReadStreamPrivate @@ -105,6 +105,6 @@ extern void read_stream_resume(ReadStream *stream); extern void read_stream_reset(ReadStream *stream); extern void read_stream_end(ReadStream *stream); -extern ReadStreamInstrumentation read_stream_prefetch_stats(ReadStream *stream); +extern IOStats read_stream_prefetch_stats(ReadStream *stream); #endif /* READ_STREAM_H */ diff --git a/src/test/regress/expected/explain.out b/src/test/regress/expected/explain.out index 7c1f26b182c..2d247f73c14 100644 --- a/src/test/regress/expected/explain.out +++ b/src/test/regress/expected/explain.out @@ -23,6 +23,10 @@ begin -- Ignore text-mode buffers output because it varies depending -- on the system state CONTINUE WHEN (ln ~ ' +Buffers: .*'); + -- Ignore text-mode prefetch and I/O output because it varies + -- depending on the system state + CONTINUE WHEN (ln ~ ' +Prefetch: .*'); + CONTINUE WHEN (ln ~ ' +I/O: .*'); -- Ignore text-mode "Planning:" line because whether it's output -- varies depending on the system state CONTINUE WHEN (ln = 'Planning:'); @@ -68,7 +72,7 @@ select explain_filter('explain select * from int8_tbl i8'); Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N) (1 row) -select explain_filter('explain (analyze, buffers off) select * from int8_tbl i8'); +select explain_filter('explain (analyze, buffers off, io off) select * from int8_tbl i8'); explain_filter ------------------------------------------------------------------------------------------------- Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N.N loops=N) @@ -76,7 +80,7 @@ select explain_filter('explain (analyze, buffers off) select * from int8_tbl i8' Execution Time: N.N ms (3 rows) -select explain_filter('explain (analyze, buffers off, verbose) select * from int8_tbl i8'); +select explain_filter('explain (analyze, buffers off, io off, verbose) select * from int8_tbl i8'); explain_filter -------------------------------------------------------------------------------------------------------- Seq Scan on public.int8_tbl i8 (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N.N loops=N) @@ -93,7 +97,7 @@ select explain_filter('explain (analyze, buffers, format text) select * from int Execution Time: N.N ms (3 rows) -select explain_filter('explain (analyze, buffers, format xml) select * from int8_tbl i8'); +select explain_filter('explain (analyze, buffers, io off, format xml) select * from int8_tbl i8'); explain_filter -------------------------------------------------------- + @@ -144,7 +148,7 @@ select explain_filter('explain (analyze, buffers, format xml) select * from int8 (1 row) -select explain_filter('explain (analyze, serialize, buffers, format yaml) select * from int8_tbl i8'); +select explain_filter('explain (analyze, serialize, buffers, io off, format yaml) select * from int8_tbl i8'); explain_filter ------------------------------- - Plan: + @@ -310,6 +314,11 @@ select explain_filter('explain (analyze, buffers, format json) select * from int "Actual Rows": N.N, + "Actual Loops": N, + "Disabled": false, + + "Prefetch": { + + "Average Distance": N.N, + + "Max Distance": N, + + "Capacity": N + + }, + "Shared Hit Blocks": N, + "Shared Read Blocks": N, + "Shared Dirtied Blocks": N, + @@ -396,7 +405,7 @@ select explain_filter('explain (memory) select * from int8_tbl i8'); Memory: used=NkB allocated=NkB (2 rows) -select explain_filter('explain (memory, analyze, buffers off) select * from int8_tbl i8'); +select explain_filter('explain (memory, analyze, buffers off, io off) select * from int8_tbl i8'); explain_filter ------------------------------------------------------------------------------------------------- Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N.N loops=N) @@ -445,6 +454,11 @@ select explain_filter('explain (memory, analyze, format json) select * from int8 "Actual Rows": N.N, + "Actual Loops": N, + "Disabled": false, + + "Prefetch": { + + "Average Distance": N.N, + + "Max Distance": N, + + "Capacity": N + + }, + "Shared Hit Blocks": N, + "Shared Read Blocks": N, + "Shared Dirtied Blocks": N, + @@ -568,6 +582,11 @@ select jsonb_pretty( ], + "Schema": "public", + "Disabled": false, + + "Prefetch": { + + "Capacity": 0, + + "Max Distance": 0, + + "Average Distance": 0.0 + + }, + "Node Type": "Seq Scan", + "Plan Rows": 0, + "Plan Width": 0, + @@ -744,7 +763,7 @@ select explain_filter('explain (verbose) create table test_ctas as select 1'); (3 rows) -- Test SERIALIZE option -select explain_filter('explain (analyze,buffers off,serialize) select * from int8_tbl i8'); +select explain_filter('explain (analyze,buffers off,io off,serialize) select * from int8_tbl i8'); explain_filter ------------------------------------------------------------------------------------------------- Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N.N loops=N) @@ -772,7 +791,7 @@ select explain_filter('explain (analyze,serialize binary,buffers,timing) select (4 rows) -- this tests an edge case where we have no data to return -select explain_filter('explain (analyze,buffers off,serialize) create temp table explain_temp as select * from int8_tbl i8'); +select explain_filter('explain (analyze,buffers off,io off,serialize) create temp table explain_temp as select * from int8_tbl i8'); explain_filter ------------------------------------------------------------------------------------------------- Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N.N loops=N) @@ -782,7 +801,7 @@ select explain_filter('explain (analyze,buffers off,serialize) create temp table (4 rows) -- Test tuplestore storage usage in Window aggregate (memory case) -select explain_filter('explain (analyze,buffers off,costs off) select sum(n) over() from generate_series(1,10) a(n)'); +select explain_filter('explain (analyze,buffers off,io off,costs off) select sum(n) over() from generate_series(1,10) a(n)'); explain_filter ---------------------------------------------------------------------------------- WindowAgg (actual time=N.N..N.N rows=N.N loops=N) @@ -795,7 +814,7 @@ select explain_filter('explain (analyze,buffers off,costs off) select sum(n) ove -- Test tuplestore storage usage in Window aggregate (disk case) set work_mem to 64; -select explain_filter('explain (analyze,buffers off,costs off) select sum(n) over() from generate_series(1,2500) a(n)'); +select explain_filter('explain (analyze,buffers off,io off,costs off) select sum(n) over() from generate_series(1,2500) a(n)'); explain_filter ---------------------------------------------------------------------------------- WindowAgg (actual time=N.N..N.N rows=N.N loops=N) @@ -807,7 +826,7 @@ select explain_filter('explain (analyze,buffers off,costs off) select sum(n) ove (6 rows) -- Test tuplestore storage usage in Window aggregate (memory and disk case, final result is disk) -select explain_filter('explain (analyze,buffers off,costs off) select sum(n) over(partition by m) from (SELECT n < 3 as m, n from generate_series(1,2500) a(n))'); +select explain_filter('explain (analyze,buffers off,io off,costs off) select sum(n) over(partition by m) from (SELECT n < 3 as m, n from generate_series(1,2500) a(n))'); explain_filter ---------------------------------------------------------------------------------------- WindowAgg (actual time=N.N..N.N rows=N.N loops=N) diff --git a/src/test/regress/expected/incremental_sort.out b/src/test/regress/expected/incremental_sort.out index 1e6e020fea8..55d5a52d96b 100644 --- a/src/test/regress/expected/incremental_sort.out +++ b/src/test/regress/expected/incremental_sort.out @@ -39,7 +39,7 @@ declare line text; begin for line in - execute 'explain (analyze, costs off, summary off, timing off, buffers off) ' || query + execute 'explain (analyze, costs off, summary off, timing off, buffers off, io off) ' || query loop out_line := regexp_replace(line, '\d+kB', 'NNkB', 'g'); return next; @@ -55,7 +55,7 @@ declare element jsonb; matching_nodes jsonb := '[]'::jsonb; begin - execute 'explain (analyze, costs off, summary off, timing off, buffers off, format ''json'') ' || query into strict elements; + execute 'explain (analyze, costs off, summary off, timing off, buffers off, io off, format ''json'') ' || query into strict elements; while jsonb_array_length(elements) > 0 loop element := elements->0; elements := elements - 0; diff --git a/src/test/regress/expected/memoize.out b/src/test/regress/expected/memoize.out index 00c30b91459..425aed4d749 100644 --- a/src/test/regress/expected/memoize.out +++ b/src/test/regress/expected/memoize.out @@ -10,7 +10,7 @@ declare ln text; begin for ln in - execute format('explain (analyze, costs off, summary off, timing off, buffers off) %s', + execute format('explain (analyze, costs off, summary off, timing off, buffers off, io off) %s', query) loop if hide_hitmiss = true then diff --git a/src/test/regress/expected/merge.out b/src/test/regress/expected/merge.out index 9cb1d87066a..f1331c307d3 100644 --- a/src/test/regress/expected/merge.out +++ b/src/test/regress/expected/merge.out @@ -1623,7 +1623,7 @@ $$ DECLARE ln text; BEGIN FOR ln IN - EXECUTE 'explain (analyze, timing off, summary off, costs off, buffers off) ' || + EXECUTE 'explain (analyze, timing off, summary off, costs off, buffers off, io off) ' || query LOOP ln := regexp_replace(ln, '(Memory( Usage)?|Buckets|Batches): \S*', '\1: xxx', 'g'); diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out index deacdd75807..1332cd0e646 100644 --- a/src/test/regress/expected/partition_prune.out +++ b/src/test/regress/expected/partition_prune.out @@ -11,7 +11,7 @@ declare ln text; begin for ln in - execute format('explain (analyze, costs off, summary off, timing off, buffers off) %s', + execute format('explain (analyze, costs off, summary off, timing off, buffers off, io off) %s', query) loop ln := regexp_replace(ln, 'Maximum Storage: \d+', 'Maximum Storage: N'); @@ -2322,7 +2322,8 @@ explain (analyze, costs off, summary off, timing off, buffers off) select * from Subplans Removed: 3 -> Seq Scan on list_part1 list_part_1 (actual rows=1.00 loops=1) Filter: (a = list_part_fn(1)) -(4 rows) + Prefetch: avg=1.000 max=1 capacity=94 +(5 rows) -- Ensure pruning does not take place when the function has a Var parameter explain (analyze, costs off, summary off, timing off, buffers off) select * from list_part where a = list_part_fn(a); @@ -2331,13 +2332,17 @@ explain (analyze, costs off, summary off, timing off, buffers off) select * from Append (actual rows=4.00 loops=1) -> Seq Scan on list_part1 list_part_1 (actual rows=1.00 loops=1) Filter: (a = list_part_fn(a)) + Prefetch: avg=1.000 max=1 capacity=94 -> Seq Scan on list_part2 list_part_2 (actual rows=1.00 loops=1) Filter: (a = list_part_fn(a)) + Prefetch: avg=1.000 max=1 capacity=94 -> Seq Scan on list_part3 list_part_3 (actual rows=1.00 loops=1) Filter: (a = list_part_fn(a)) + Prefetch: avg=1.000 max=1 capacity=94 -> Seq Scan on list_part4 list_part_4 (actual rows=1.00 loops=1) Filter: (a = list_part_fn(a)) -(9 rows) + Prefetch: avg=1.000 max=1 capacity=94 +(13 rows) -- Ensure pruning does not take place when the expression contains a Var. explain (analyze, costs off, summary off, timing off, buffers off) select * from list_part where a = list_part_fn(1) + a; @@ -2347,16 +2352,20 @@ explain (analyze, costs off, summary off, timing off, buffers off) select * from -> Seq Scan on list_part1 list_part_1 (actual rows=0.00 loops=1) Filter: (a = (list_part_fn(1) + a)) Rows Removed by Filter: 1 + Prefetch: avg=1.000 max=1 capacity=94 -> Seq Scan on list_part2 list_part_2 (actual rows=0.00 loops=1) Filter: (a = (list_part_fn(1) + a)) Rows Removed by Filter: 1 + Prefetch: avg=1.000 max=1 capacity=94 -> Seq Scan on list_part3 list_part_3 (actual rows=0.00 loops=1) Filter: (a = (list_part_fn(1) + a)) Rows Removed by Filter: 1 + Prefetch: avg=1.000 max=1 capacity=94 -> Seq Scan on list_part4 list_part_4 (actual rows=0.00 loops=1) Filter: (a = (list_part_fn(1) + a)) Rows Removed by Filter: 1 -(13 rows) + Prefetch: avg=1.000 max=1 capacity=94 +(17 rows) rollback; drop table list_part; @@ -2524,6 +2533,7 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on -> Nested Loop (actual rows=N loops=N) -> Parallel Seq Scan on lprt_a a (actual rows=N loops=N) Filter: (a = ANY ('{0,0,1}'::integer[])) + Prefetch: avg=1.000 max=1 capacity=94 -> Append (actual rows=N loops=N) -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 ab_1 (actual rows=N loops=N) Index Cond: (a = a.a) @@ -2543,7 +2553,7 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on Index Cond: (a = a.a) -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed) Index Cond: (a = a.a) -(27 rows) +(28 rows) -- Ensure the same partitions are pruned when we make the nested loop -- parameter an Expr rather than a plain Param. @@ -2558,6 +2568,7 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on -> Nested Loop (actual rows=N loops=N) -> Parallel Seq Scan on lprt_a a (actual rows=N loops=N) Filter: (a = ANY ('{0,0,1}'::integer[])) + Prefetch: avg=1.000 max=1 capacity=94 -> Append (actual rows=N loops=N) -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 ab_1 (actual rows=N loops=N) Index Cond: (a = (a.a + 0)) @@ -2577,7 +2588,7 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on Index Cond: (a = (a.a + 0)) -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed) Index Cond: (a = (a.a + 0)) -(27 rows) +(28 rows) insert into lprt_a values(3),(3); select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 3)'); @@ -2591,6 +2602,7 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on -> Nested Loop (actual rows=N loops=N) -> Parallel Seq Scan on lprt_a a (actual rows=N loops=N) Filter: (a = ANY ('{1,0,3}'::integer[])) + Prefetch: avg=1.000 max=1 capacity=94 -> Append (actual rows=N loops=N) -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 ab_1 (actual rows=N loops=N) Index Cond: (a = a.a) @@ -2610,7 +2622,7 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on Index Cond: (a = a.a) -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (actual rows=N loops=N) Index Cond: (a = a.a) -(27 rows) +(28 rows) select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 0)'); explain_parallel_append @@ -2624,6 +2636,7 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on -> Parallel Seq Scan on lprt_a a (actual rows=N loops=N) Filter: (a = ANY ('{1,0,0}'::integer[])) Rows Removed by Filter: N + Prefetch: avg=1.000 max=1 capacity=94 -> Append (actual rows=N loops=N) -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 ab_1 (actual rows=N loops=N) Index Cond: (a = a.a) @@ -2643,7 +2656,7 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on Index Cond: (a = a.a) -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed) Index Cond: (a = a.a) -(28 rows) +(29 rows) delete from lprt_a where a = 1; select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on ab.a = a.a where a.a in(1, 0, 0)'); @@ -2658,6 +2671,7 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on -> Parallel Seq Scan on lprt_a a (actual rows=N loops=N) Filter: (a = ANY ('{1,0,0}'::integer[])) Rows Removed by Filter: N + Prefetch: avg=1.000 max=1 capacity=94 -> Append (actual rows=N loops=N) -> Index Scan using ab_a1_b1_a_idx on ab_a1_b1 ab_1 (never executed) Index Cond: (a = a.a) @@ -2677,7 +2691,7 @@ select explain_parallel_append('select avg(ab.a) from ab inner join lprt_a a on Index Cond: (a = a.a) -> Index Scan using ab_a3_b3_a_idx on ab_a3_b3 ab_9 (never executed) Index Cond: (a = a.a) -(28 rows) +(29 rows) reset enable_hashjoin; reset enable_mergejoin; @@ -2695,9 +2709,11 @@ select * from ab where a = (select max(a) from lprt_a) and b = (select max(a)-1 InitPlan expr_1 -> Aggregate (actual rows=1.00 loops=1) -> Seq Scan on lprt_a (actual rows=102.00 loops=1) + Prefetch: avg=1.000 max=1 capacity=94 InitPlan expr_2 -> Aggregate (actual rows=1.00 loops=1) -> Seq Scan on lprt_a lprt_a_1 (actual rows=102.00 loops=1) + Prefetch: avg=1.000 max=1 capacity=94 -> Bitmap Heap Scan on ab_a1_b1 ab_1 (never executed) Recheck Cond: (a = (InitPlan expr_1).col1) Filter: (b = (InitPlan expr_2).col1) @@ -2752,7 +2768,7 @@ select * from ab where a = (select max(a) from lprt_a) and b = (select max(a)-1 -> Bitmap Index Scan on ab_a3_b3_a_idx (never executed) Index Cond: (a = (InitPlan expr_1).col1) Index Searches: 0 -(61 rows) +(63 rows) -- Test run-time partition pruning with UNION ALL parents explain (analyze, costs off, summary off, timing off, buffers off) @@ -2880,13 +2896,14 @@ explain (analyze, costs off, summary off, timing off, buffers off) execute ab_q6 -> Seq Scan on xy_1 (actual rows=0.00 loops=1) Filter: ((x = $1) AND (y = (InitPlan expr_1).col1)) Rows Removed by Filter: 1 + Prefetch: avg=1.000 max=1 capacity=94 -> Seq Scan on ab_a1_b1 ab_4 (never executed) Filter: ((a = $1) AND (b = (InitPlan expr_1).col1)) -> Seq Scan on ab_a1_b2 ab_5 (never executed) Filter: ((a = $1) AND (b = (InitPlan expr_1).col1)) -> Seq Scan on ab_a1_b3 ab_6 (never executed) Filter: ((a = $1) AND (b = (InitPlan expr_1).col1)) -(19 rows) +(20 rows) -- Ensure we see just the xy_1 row. execute ab_q6(100); @@ -3025,6 +3042,7 @@ select * from tbl1 join tprt on tbl1.col1 > tprt.col1; ----------------------------------------------------------------------------- Nested Loop (actual rows=6.00 loops=1) -> Seq Scan on tbl1 (actual rows=2.00 loops=1) + Prefetch: avg=1.000 max=1 capacity=94 -> Append (actual rows=3.00 loops=2) -> Index Scan using tprt1_idx on tprt_1 (actual rows=2.00 loops=2) Index Cond: (col1 < tbl1.col1) @@ -3044,7 +3062,7 @@ select * from tbl1 join tprt on tbl1.col1 > tprt.col1; -> Index Scan using tprt6_idx on tprt_6 (never executed) Index Cond: (col1 < tbl1.col1) Index Searches: 0 -(21 rows) +(22 rows) explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 join tprt on tbl1.col1 = tprt.col1; @@ -3052,6 +3070,7 @@ select * from tbl1 join tprt on tbl1.col1 = tprt.col1; ----------------------------------------------------------------------------- Nested Loop (actual rows=2.00 loops=1) -> Seq Scan on tbl1 (actual rows=2.00 loops=1) + Prefetch: avg=1.000 max=1 capacity=94 -> Append (actual rows=1.00 loops=2) -> Index Scan using tprt1_idx on tprt_1 (never executed) Index Cond: (col1 = tbl1.col1) @@ -3071,7 +3090,7 @@ select * from tbl1 join tprt on tbl1.col1 = tprt.col1; -> Index Scan using tprt6_idx on tprt_6 (never executed) Index Cond: (col1 = tbl1.col1) Index Searches: 0 -(21 rows) +(22 rows) select tbl1.col1, tprt.col1 from tbl1 inner join tprt on tbl1.col1 > tprt.col1 @@ -3103,6 +3122,7 @@ select * from tbl1 inner join tprt on tbl1.col1 > tprt.col1; ----------------------------------------------------------------------------- Nested Loop (actual rows=23.00 loops=1) -> Seq Scan on tbl1 (actual rows=5.00 loops=1) + Prefetch: avg=1.000 max=1 capacity=94 -> Append (actual rows=4.60 loops=5) -> Index Scan using tprt1_idx on tprt_1 (actual rows=2.00 loops=5) Index Cond: (col1 < tbl1.col1) @@ -3122,7 +3142,7 @@ select * from tbl1 inner join tprt on tbl1.col1 > tprt.col1; -> Index Scan using tprt6_idx on tprt_6 (never executed) Index Cond: (col1 < tbl1.col1) Index Searches: 0 -(21 rows) +(22 rows) explain (analyze, costs off, summary off, timing off, buffers off) select * from tbl1 inner join tprt on tbl1.col1 = tprt.col1; @@ -3130,6 +3150,7 @@ select * from tbl1 inner join tprt on tbl1.col1 = tprt.col1; ----------------------------------------------------------------------------- Nested Loop (actual rows=3.00 loops=1) -> Seq Scan on tbl1 (actual rows=5.00 loops=1) + Prefetch: avg=1.000 max=1 capacity=94 -> Append (actual rows=0.60 loops=5) -> Index Scan using tprt1_idx on tprt_1 (never executed) Index Cond: (col1 = tbl1.col1) @@ -3149,7 +3170,7 @@ select * from tbl1 inner join tprt on tbl1.col1 = tprt.col1; -> Index Scan using tprt6_idx on tprt_6 (never executed) Index Cond: (col1 = tbl1.col1) Index Searches: 0 -(21 rows) +(22 rows) select tbl1.col1, tprt.col1 from tbl1 inner join tprt on tbl1.col1 > tprt.col1 @@ -3200,6 +3221,7 @@ select * from tbl1 join tprt on tbl1.col1 < tprt.col1; ----------------------------------------------------------------------------- Nested Loop (actual rows=1.00 loops=1) -> Seq Scan on tbl1 (actual rows=1.00 loops=1) + Prefetch: avg=1.000 max=1 capacity=94 -> Append (actual rows=1.00 loops=1) -> Index Scan using tprt1_idx on tprt_1 (never executed) Index Cond: (col1 > tbl1.col1) @@ -3219,7 +3241,7 @@ select * from tbl1 join tprt on tbl1.col1 < tprt.col1; -> Index Scan using tprt6_idx on tprt_6 (actual rows=1.00 loops=1) Index Cond: (col1 > tbl1.col1) Index Searches: 1 -(21 rows) +(22 rows) select tbl1.col1, tprt.col1 from tbl1 inner join tprt on tbl1.col1 < tprt.col1 @@ -3238,6 +3260,7 @@ select * from tbl1 join tprt on tbl1.col1 = tprt.col1; ------------------------------------------------------------------- Nested Loop (actual rows=0.00 loops=1) -> Seq Scan on tbl1 (actual rows=1.00 loops=1) + Prefetch: avg=1.000 max=1 capacity=94 -> Append (actual rows=0.00 loops=1) -> Index Scan using tprt1_idx on tprt_1 (never executed) Index Cond: (col1 = tbl1.col1) @@ -3257,7 +3280,7 @@ select * from tbl1 join tprt on tbl1.col1 = tprt.col1; -> Index Scan using tprt6_idx on tprt_6 (never executed) Index Cond: (col1 = tbl1.col1) Index Searches: 0 -(21 rows) +(22 rows) select tbl1.col1, tprt.col1 from tbl1 inner join tprt on tbl1.col1 = tprt.col1 @@ -3485,11 +3508,14 @@ select * from mc3p where a < 3 and abs(b) = 1; Append (actual rows=3.00 loops=1) -> Seq Scan on mc3p0 mc3p_1 (actual rows=1.00 loops=1) Filter: ((a < 3) AND (abs(b) = 1)) + Prefetch: avg=1.000 max=1 capacity=94 -> Seq Scan on mc3p1 mc3p_2 (actual rows=1.00 loops=1) Filter: ((a < 3) AND (abs(b) = 1)) + Prefetch: avg=1.000 max=1 capacity=94 -> Seq Scan on mc3p2 mc3p_3 (actual rows=1.00 loops=1) Filter: ((a < 3) AND (abs(b) = 1)) -(7 rows) + Prefetch: avg=1.000 max=1 capacity=94 +(10 rows) -- -- Check that pruning with composite range partitioning works correctly when @@ -3508,7 +3534,8 @@ execute ps1(1); -> Result (actual rows=1.00 loops=1) -> Seq Scan on mc3p1 mc3p_1 (actual rows=1.00 loops=1) Filter: ((a = $1) AND (abs(b) < (InitPlan expr_1).col1)) -(6 rows) + Prefetch: avg=1.000 max=1 capacity=94 +(7 rows) deallocate ps1; prepare ps2 as @@ -3523,9 +3550,11 @@ execute ps2(1); -> Result (actual rows=1.00 loops=1) -> Seq Scan on mc3p0 mc3p_1 (actual rows=1.00 loops=1) Filter: ((a <= $1) AND (abs(b) < (InitPlan expr_1).col1)) + Prefetch: avg=1.000 max=1 capacity=94 -> Seq Scan on mc3p1 mc3p_2 (actual rows=1.00 loops=1) Filter: ((a <= $1) AND (abs(b) < (InitPlan expr_1).col1)) -(8 rows) + Prefetch: avg=1.000 max=1 capacity=94 +(10 rows) deallocate ps2; drop table mc3p; @@ -3544,11 +3573,12 @@ select * from boolp where a = (select value from boolvalues where value); -> Seq Scan on boolvalues (actual rows=1.00 loops=1) Filter: value Rows Removed by Filter: 1 + Prefetch: avg=1.000 max=1 capacity=94 -> Seq Scan on boolp_f boolp_1 (never executed) Filter: (a = (InitPlan expr_1).col1) -> Seq Scan on boolp_t boolp_2 (actual rows=0.00 loops=1) Filter: (a = (InitPlan expr_1).col1) -(9 rows) +(10 rows) explain (analyze, costs off, summary off, timing off, buffers off) select * from boolp where a = (select value from boolvalues where not value); @@ -3559,11 +3589,12 @@ select * from boolp where a = (select value from boolvalues where not value); -> Seq Scan on boolvalues (actual rows=1.00 loops=1) Filter: (NOT value) Rows Removed by Filter: 1 + Prefetch: avg=1.000 max=1 capacity=94 -> Seq Scan on boolp_f boolp_1 (actual rows=0.00 loops=1) Filter: (a = (InitPlan expr_1).col1) -> Seq Scan on boolp_t boolp_2 (never executed) Filter: (a = (InitPlan expr_1).col1) -(9 rows) +(10 rows) drop table boolp; -- diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out index 933921d1860..bfaf10a2906 100644 --- a/src/test/regress/expected/select_parallel.out +++ b/src/test/regress/expected/select_parallel.out @@ -580,7 +580,7 @@ select count(*) from bmscantest where a>1; -- test accumulation of stats for parallel nodes reset enable_seqscan; alter table tenk2 set (parallel_workers = 0); -explain (analyze, timing off, summary off, costs off, buffers off) +explain (analyze, timing off, summary off, costs off, buffers off, io off) select count(*) from tenk1, tenk2 where tenk1.hundred > 1 and tenk2.thousand=0; QUERY PLAN @@ -606,7 +606,7 @@ $$ declare ln text; begin for ln in - explain (analyze, timing off, summary off, costs off, buffers off) + explain (analyze, timing off, summary off, costs off, buffers off, io off) select * from (select ten from tenk1 where ten < 100 order by ten) ss right join (values (1),(2),(3)) v(x) on true @@ -1170,7 +1170,7 @@ explain (costs off) -- to increase the parallel query test coverage SAVEPOINT settings; SET LOCAL debug_parallel_query = 1; -EXPLAIN (analyze, timing off, summary off, costs off, buffers off) SELECT * FROM tenk1; +EXPLAIN (analyze, timing off, summary off, costs off, buffers off, io off) SELECT * FROM tenk1; QUERY PLAN ---------------------------------------------------------------- Gather (actual rows=10000.00 loops=1) diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out index 200236a0a69..0a1de6d7259 100644 --- a/src/test/regress/expected/subselect.out +++ b/src/test/regress/expected/subselect.out @@ -2051,7 +2051,7 @@ $$ declare ln text; begin for ln in - explain (analyze, summary off, timing off, costs off, buffers off) + explain (analyze, summary off, timing off, costs off, buffers off, io off) select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3 loop ln := regexp_replace(ln, 'Memory: \S*', 'Memory: xxx'); diff --git a/src/test/regress/sql/explain.sql b/src/test/regress/sql/explain.sql index ebdab42604b..e9a3c2caf91 100644 --- a/src/test/regress/sql/explain.sql +++ b/src/test/regress/sql/explain.sql @@ -25,6 +25,10 @@ begin -- Ignore text-mode buffers output because it varies depending -- on the system state CONTINUE WHEN (ln ~ ' +Buffers: .*'); + -- Ignore text-mode prefetch and I/O output because it varies + -- depending on the system state + CONTINUE WHEN (ln ~ ' +Prefetch: .*'); + CONTINUE WHEN (ln ~ ' +I/O: .*'); -- Ignore text-mode "Planning:" line because whether it's output -- varies depending on the system state CONTINUE WHEN (ln = 'Planning:'); @@ -63,11 +67,11 @@ set track_io_timing = off; explain (costs off) select 1 as a, 2 as b having false; select explain_filter('explain select * from int8_tbl i8'); -select explain_filter('explain (analyze, buffers off) select * from int8_tbl i8'); -select explain_filter('explain (analyze, buffers off, verbose) select * from int8_tbl i8'); +select explain_filter('explain (analyze, buffers off, io off) select * from int8_tbl i8'); +select explain_filter('explain (analyze, buffers off, io off, verbose) select * from int8_tbl i8'); select explain_filter('explain (analyze, buffers, format text) select * from int8_tbl i8'); -select explain_filter('explain (analyze, buffers, format xml) select * from int8_tbl i8'); -select explain_filter('explain (analyze, serialize, buffers, format yaml) select * from int8_tbl i8'); +select explain_filter('explain (analyze, buffers, io off, format xml) select * from int8_tbl i8'); +select explain_filter('explain (analyze, serialize, buffers, io off, format yaml) select * from int8_tbl i8'); select explain_filter('explain (buffers, format text) select * from int8_tbl i8'); select explain_filter('explain (buffers, format json) select * from int8_tbl i8'); @@ -102,7 +106,7 @@ select explain_filter('explain (analyze, generic_plan) select unique1 from tenk1 -- MEMORY option select explain_filter('explain (memory) select * from int8_tbl i8'); -select explain_filter('explain (memory, analyze, buffers off) select * from int8_tbl i8'); +select explain_filter('explain (memory, analyze, buffers off, io off) select * from int8_tbl i8'); select explain_filter('explain (memory, summary, format yaml) select * from int8_tbl i8'); select explain_filter('explain (memory, analyze, format json) select * from int8_tbl i8'); prepare int8_query as select * from int8_tbl i8; @@ -174,17 +178,17 @@ select explain_filter('explain (verbose) declare test_cur cursor for select * fr select explain_filter('explain (verbose) create table test_ctas as select 1'); -- Test SERIALIZE option -select explain_filter('explain (analyze,buffers off,serialize) select * from int8_tbl i8'); +select explain_filter('explain (analyze,buffers off,io off,serialize) select * from int8_tbl i8'); select explain_filter('explain (analyze,serialize text,buffers,timing off) select * from int8_tbl i8'); select explain_filter('explain (analyze,serialize binary,buffers,timing) select * from int8_tbl i8'); -- this tests an edge case where we have no data to return -select explain_filter('explain (analyze,buffers off,serialize) create temp table explain_temp as select * from int8_tbl i8'); +select explain_filter('explain (analyze,buffers off,io off,serialize) create temp table explain_temp as select * from int8_tbl i8'); -- Test tuplestore storage usage in Window aggregate (memory case) -select explain_filter('explain (analyze,buffers off,costs off) select sum(n) over() from generate_series(1,10) a(n)'); +select explain_filter('explain (analyze,buffers off,io off,costs off) select sum(n) over() from generate_series(1,10) a(n)'); -- Test tuplestore storage usage in Window aggregate (disk case) set work_mem to 64; -select explain_filter('explain (analyze,buffers off,costs off) select sum(n) over() from generate_series(1,2500) a(n)'); +select explain_filter('explain (analyze,buffers off,io off,costs off) select sum(n) over() from generate_series(1,2500) a(n)'); -- Test tuplestore storage usage in Window aggregate (memory and disk case, final result is disk) -select explain_filter('explain (analyze,buffers off,costs off) select sum(n) over(partition by m) from (SELECT n < 3 as m, n from generate_series(1,2500) a(n))'); +select explain_filter('explain (analyze,buffers off,io off,costs off) select sum(n) over(partition by m) from (SELECT n < 3 as m, n from generate_series(1,2500) a(n))'); reset work_mem; diff --git a/src/test/regress/sql/incremental_sort.sql b/src/test/regress/sql/incremental_sort.sql index bbe658a7588..1c8036faade 100644 --- a/src/test/regress/sql/incremental_sort.sql +++ b/src/test/regress/sql/incremental_sort.sql @@ -21,7 +21,7 @@ declare line text; begin for line in - execute 'explain (analyze, costs off, summary off, timing off, buffers off) ' || query + execute 'explain (analyze, costs off, summary off, timing off, buffers off, io off) ' || query loop out_line := regexp_replace(line, '\d+kB', 'NNkB', 'g'); return next; @@ -38,7 +38,7 @@ declare element jsonb; matching_nodes jsonb := '[]'::jsonb; begin - execute 'explain (analyze, costs off, summary off, timing off, buffers off, format ''json'') ' || query into strict elements; + execute 'explain (analyze, costs off, summary off, timing off, buffers off, io off, format ''json'') ' || query into strict elements; while jsonb_array_length(elements) > 0 loop element := elements->0; elements := elements - 0; diff --git a/src/test/regress/sql/memoize.sql b/src/test/regress/sql/memoize.sql index 8d1cdd6990c..d1f80e18b41 100644 --- a/src/test/regress/sql/memoize.sql +++ b/src/test/regress/sql/memoize.sql @@ -11,7 +11,7 @@ declare ln text; begin for ln in - execute format('explain (analyze, costs off, summary off, timing off, buffers off) %s', + execute format('explain (analyze, costs off, summary off, timing off, buffers off, io off) %s', query) loop if hide_hitmiss = true then diff --git a/src/test/regress/sql/merge.sql b/src/test/regress/sql/merge.sql index 2660b19f238..c82e194078e 100644 --- a/src/test/regress/sql/merge.sql +++ b/src/test/regress/sql/merge.sql @@ -1074,7 +1074,7 @@ $$ DECLARE ln text; BEGIN FOR ln IN - EXECUTE 'explain (analyze, timing off, summary off, costs off, buffers off) ' || + EXECUTE 'explain (analyze, timing off, summary off, costs off, buffers off, io off) ' || query LOOP ln := regexp_replace(ln, '(Memory( Usage)?|Buckets|Batches): \S*', '\1: xxx', 'g'); diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql index d93c0c03bab..212de0e6285 100644 --- a/src/test/regress/sql/partition_prune.sql +++ b/src/test/regress/sql/partition_prune.sql @@ -12,7 +12,7 @@ declare ln text; begin for ln in - execute format('explain (analyze, costs off, summary off, timing off, buffers off) %s', + execute format('explain (analyze, costs off, summary off, timing off, buffers off, io off) %s', query) loop ln := regexp_replace(ln, 'Maximum Storage: \d+', 'Maximum Storage: N'); diff --git a/src/test/regress/sql/select_parallel.sql b/src/test/regress/sql/select_parallel.sql index 71a75bc86ea..56e1ceac492 100644 --- a/src/test/regress/sql/select_parallel.sql +++ b/src/test/regress/sql/select_parallel.sql @@ -230,7 +230,7 @@ select count(*) from bmscantest where a>1; -- test accumulation of stats for parallel nodes reset enable_seqscan; alter table tenk2 set (parallel_workers = 0); -explain (analyze, timing off, summary off, costs off, buffers off) +explain (analyze, timing off, summary off, costs off, buffers off, io off) select count(*) from tenk1, tenk2 where tenk1.hundred > 1 and tenk2.thousand=0; alter table tenk2 reset (parallel_workers); @@ -242,7 +242,7 @@ $$ declare ln text; begin for ln in - explain (analyze, timing off, summary off, costs off, buffers off) + explain (analyze, timing off, summary off, costs off, buffers off, io off) select * from (select ten from tenk1 where ten < 100 order by ten) ss right join (values (1),(2),(3)) v(x) on true @@ -450,7 +450,7 @@ explain (costs off) -- to increase the parallel query test coverage SAVEPOINT settings; SET LOCAL debug_parallel_query = 1; -EXPLAIN (analyze, timing off, summary off, costs off, buffers off) SELECT * FROM tenk1; +EXPLAIN (analyze, timing off, summary off, costs off, buffers off, io off) SELECT * FROM tenk1; ROLLBACK TO SAVEPOINT settings; -- provoke error in worker diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql index 4cd016f4ac3..3e930d8b56d 100644 --- a/src/test/regress/sql/subselect.sql +++ b/src/test/regress/sql/subselect.sql @@ -1016,7 +1016,7 @@ $$ declare ln text; begin for ln in - explain (analyze, summary off, timing off, costs off, buffers off) + explain (analyze, summary off, timing off, costs off, buffers off, io off) select * from (select pk,c2 from sq_limit order by c1,pk) as x limit 3 loop ln := regexp_replace(ln, 'Memory: \S*', 'Memory: xxx'); -- 2.53.0