From 49d9058eab1d4009de8e82eb1a87ad49372f297b Mon Sep 17 00:00:00 2001 From: Tomas Vondra Date: Fri, 21 Oct 2022 15:33:16 +0200 Subject: [PATCH 3/6] wip: brinsort explain stats Show some internal stats about BRIN Sort in EXPLAIN output. --- src/backend/commands/explain.c | 115 ++++++++++++++++++++++++++++ src/backend/executor/nodeBrinSort.c | 35 +++++++++ src/include/nodes/execnodes.h | 33 ++++++++ 3 files changed, 183 insertions(+) diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index e15b29246b1..c5ace02a10d 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -87,6 +87,8 @@ static void show_incremental_sort_keys(IncrementalSortState *incrsortstate, List *ancestors, ExplainState *es); static void show_brinsort_keys(BrinSortState *sortstate, List *ancestors, ExplainState *es); +static void show_brinsort_stats(BrinSortState *sortstate, List *ancestors, + ExplainState *es); static void show_merge_append_keys(MergeAppendState *mstate, List *ancestors, ExplainState *es); static void show_agg_keys(AggState *astate, List *ancestors, @@ -1814,6 +1816,7 @@ ExplainNode(PlanState *planstate, List *ancestors, planstate, es); show_scan_qual(plan->qual, "Filter", planstate, ancestors, es); show_brinsort_keys(castNode(BrinSortState, planstate), ancestors, es); + show_brinsort_stats(castNode(BrinSortState, planstate), ancestors, es); if (plan->qual) show_instrumentation_count("Rows Removed by Filter", 1, planstate, es); @@ -2432,6 +2435,118 @@ show_brinsort_keys(BrinSortState *sortstate, List *ancestors, ExplainState *es) ancestors, es); } +static void +show_brinsort_stats(BrinSortState *sortstate, List *ancestors, ExplainState *es) +{ + BrinSortStats *stats = &sortstate->bs_stats; + + if (stats->sort_count > 0) + { + ExplainPropertyInteger("Ranges Processed", NULL, (int64) + stats->range_count, es); + + if (es->format == EXPLAIN_FORMAT_TEXT) + { + ExplainPropertyInteger("Sorts", NULL, (int64) + stats->sort_count, es); + + ExplainIndentText(es); + appendStringInfo(es->str, "Tuples Sorted: " INT64_FORMAT " Per-sort: " INT64_FORMAT " Direct: " INT64_FORMAT " Spilled: " INT64_FORMAT " Respilled: " INT64_FORMAT "\n", + stats->ntuples_tuplesort_all, + stats->ntuples_tuplesort_all / stats->sort_count, + stats->ntuples_tuplesort_direct, + stats->ntuples_spilled, + stats->ntuples_respilled); + } + else + { + ExplainOpenGroup("Sorts", "Sorts", true, es); + + ExplainPropertyInteger("Count", NULL, (int64) + stats->sort_count, es); + + ExplainPropertyInteger("Tuples per sort", NULL, (int64) + stats->ntuples_tuplesort_all / stats->sort_count, es); + + ExplainPropertyInteger("Sorted tuples (all)", NULL, (int64) + stats->ntuples_tuplesort_all, es); + + ExplainPropertyInteger("Sorted tuples (direct)", NULL, (int64) + stats->ntuples_tuplesort_direct, es); + + ExplainPropertyInteger("Spilled tuples", NULL, (int64) + stats->ntuples_spilled, es); + + ExplainPropertyInteger("Respilled tuples", NULL, (int64) + stats->ntuples_respilled, es); + + ExplainCloseGroup("Sorts", "Sorts", true, es); + } + } + + if (stats->sort_count_in_memory > 0) + { + if (es->format == EXPLAIN_FORMAT_TEXT) + { + ExplainIndentText(es); + appendStringInfo(es->str, "Sorts (in-memory) Count: " INT64_FORMAT " Space Total: " INT64_FORMAT " kB Maximum: " INT64_FORMAT " kB Average: " INT64_FORMAT " kB\n", + stats->sort_count_in_memory, + stats->total_space_used_in_memory, + stats->max_space_used_in_memory, + stats->total_space_used_in_memory / stats->sort_count_in_memory); + } + else + { + ExplainOpenGroup("In-Memory Sorts", "In-Memory Sorts", true, es); + + ExplainPropertyInteger("Count", NULL, (int64) + stats->sort_count_in_memory, es); + + ExplainPropertyInteger("Average space", "kB", (int64) + stats->total_space_used_in_memory / stats->sort_count_in_memory, es); + + ExplainPropertyInteger("Maximum space", "kB", (int64) + stats->max_space_used_in_memory, es); + + ExplainPropertyInteger("Total space", "kB", (int64) + stats->total_space_used_in_memory, es); + + ExplainCloseGroup("In-Memory Sorts", "In-Memory Sorts", true, es); + } + } + + if (stats->sort_count_on_disk > 0) + { + if (es->format == EXPLAIN_FORMAT_TEXT) + { + ExplainIndentText(es); + appendStringInfo(es->str, "Sorts (on-disk) Count: " INT64_FORMAT " Space Total: " INT64_FORMAT " kB Maximum: " INT64_FORMAT " kB Average: " INT64_FORMAT " kB\n", + stats->sort_count_on_disk, + stats->total_space_used_on_disk, + stats->max_space_used_on_disk, + stats->total_space_used_on_disk / stats->sort_count_on_disk); + } + else + { + ExplainOpenGroup("On-Disk Sorts", "On-Disk Sorts", true, es); + + ExplainPropertyInteger("Count", NULL, (int64) + stats->sort_count_on_disk, es); + + ExplainPropertyInteger("Average space", "kB", (int64) + stats->total_space_used_on_disk / stats->sort_count_on_disk, es); + + ExplainPropertyInteger("Maximum space", "kB", (int64) + stats->max_space_used_on_disk, es); + + ExplainPropertyInteger("Total space", "kB", (int64) + stats->total_space_used_on_disk, es); + + ExplainCloseGroup("On-Disk Sorts", "On-Disk Sorts", true, es); + } + } +} + /* * Likewise, for a MergeAppend node. */ diff --git a/src/backend/executor/nodeBrinSort.c b/src/backend/executor/nodeBrinSort.c index ca72c1ed22d..c7d417d6e57 100644 --- a/src/backend/executor/nodeBrinSort.c +++ b/src/backend/executor/nodeBrinSort.c @@ -454,6 +454,8 @@ brinsort_load_tuples(BrinSortState *node, bool check_watermark, bool null_proces if (null_processing && !(range->has_nulls || range->not_summarized || range->all_nulls)) return; + node->bs_stats.range_count++; + brinsort_start_tidscan(node); scan = node->ss.ss_currentScanDesc; @@ -526,7 +528,10 @@ brinsort_load_tuples(BrinSortState *node, bool check_watermark, bool null_proces /* Stash it to the tuplestore (when NULL, or ignore * it (when not-NULL). */ if (isnull) + { tuplestore_puttupleslot(node->bs_tuplestore, slot); + node->bs_stats.ntuples_spilled++; + } /* NULL or not, we're done */ continue; @@ -546,9 +551,16 @@ brinsort_load_tuples(BrinSortState *node, bool check_watermark, bool null_proces &node->bs_sortsupport); if (cmp <= 0) + { tuplesort_puttupleslot(node->bs_tuplesortstate, slot); + node->bs_stats.ntuples_tuplesort_direct++; + node->bs_stats.ntuples_tuplesort_all++; + } else + { tuplestore_puttupleslot(node->bs_tuplestore, slot); + node->bs_stats.ntuples_spilled++; + } } ExecClearTuple(slot); @@ -610,9 +622,15 @@ brinsort_load_spill_tuples(BrinSortState *node, bool check_watermark) &node->bs_sortsupport); if (cmp <= 0) + { tuplesort_puttupleslot(node->bs_tuplesortstate, slot); + node->bs_stats.ntuples_tuplesort_all++; + } else + { tuplestore_puttupleslot(tupstore, slot); + node->bs_stats.ntuples_respilled++; + } } /* @@ -890,12 +908,29 @@ IndexNext(BrinSortState *node) if (node->bs_tuplesortstate) { tuplesort_performsort(node->bs_tuplesortstate); + node->bs_stats.sort_count++; + #ifdef BRINSORT_DEBUG { TuplesortInstrumentation stats; tuplesort_get_stats(node->bs_tuplesortstate, &stats); + if (stats.spaceType == SORT_SPACE_TYPE_DISK) + { + node->bs_stats.sort_count_on_disk++; + node->bs_stats.total_space_used_on_disk += stats.spaceUsed; + node->bs_stats.max_space_used_on_disk = Max(node->bs_stats.max_space_used_on_disk, + stats.spaceUsed); + } + else if (stats.spaceType == SORT_SPACE_TYPE_MEMORY) + { + node->bs_stats.sort_count_in_memory++; + node->bs_stats.total_space_used_in_memory += stats.spaceUsed; + node->bs_stats.max_space_used_in_memory = Max(node->bs_stats.max_space_used_in_memory, + stats.spaceUsed); + } + elog(DEBUG1, "method: %s space: %ld kB (%s)", tuplesort_method_name(stats.sortMethod), stats.spaceUsed, diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index 381c2fcd3d6..e8f7b25549f 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -1609,6 +1609,38 @@ typedef struct BrinRanges BrinRange ranges[FLEXIBLE_ARRAY_MEMBER]; } BrinRanges; +typedef struct BrinSortStats +{ + /* number of sorts */ + int64 sort_count; + + /* number of ranges loaded */ + int64 range_count; + + /* tuples written directly to tuplesort */ + int64 ntuples_tuplesort_direct; + + /* tuples written to tuplesort (all) */ + int64 ntuples_tuplesort_all; + + /* tuples written to tuplestore */ + int64 ntuples_spilled; + + /* tuples copied from old to new tuplestore */ + int64 ntuples_respilled; + + /* number of in-memory/on-disk sorts */ + int64 sort_count_in_memory; + int64 sort_count_on_disk; + + /* total/maximum amount of space used by either sort */ + int64 total_space_used_in_memory; + int64 total_space_used_on_disk; + int64 max_space_used_in_memory; + int64 max_space_used_on_disk; + +} BrinSortStats; + typedef struct BrinSortState { ScanState ss; /* its first field is NodeTag */ @@ -1643,6 +1675,7 @@ typedef struct BrinSortState bool bs_watermark_set; BrinSortPhase bs_phase; SortSupportData bs_sortsupport; + BrinSortStats bs_stats; /* * We need two tuplesort instances - one for current range, one for -- 2.37.3