In current EXPLAIN ANALYZE implementation, the Sort Node stats from each
workers are not summarized:
https://github.com/postgres/postgres/blob/d4ba8b51c76300f06cc23f4d8a41d9f7210c4866/src/backend/commands/explain.c#L2762
When the worker number is large, it will print out huge amount of node details
in the plan. I have created this patch to summarize the tuplesort stats by
AverageSpaceUsed / PeakSpaceUsed, make it behave just like in
`show_incremental_sort_group_info()`:
https://github.com/postgres/postgres/blob/d4ba8b51c76300f06cc23f4d8a41d9f7210c4866/src/backend/commands/explain.c#L2890
From 8970f3d0f4a4535457dbe7f625081e592ebc1901 Mon Sep 17 00:00:00 2001
From: Jian Guo <gj...@vmware.com>
Date: Mon, 21 Mar 2022 11:19:46 +0800
Subject: [PATCH] Summary Sort workers Stats.
Signed-off-by: Jian Guo <gj...@vmware.com>
---
src/backend/commands/explain.c | 48 +++++++++++++++++++---------------
1 file changed, 27 insertions(+), 21 deletions(-)
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 9f632285b6..5561231d7d 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -2758,41 +2758,47 @@ show_sort_info(SortState *sortstate, ExplainState *es)
if (sortstate->shared_info != NULL)
{
int n;
+ const char *sortMethod;
+ const char *spaceType;
+ int64 peakSpaceUsed = 0;
+ int64 totalSpaceUsed = 0;
for (n = 0; n < sortstate->shared_info->num_workers; n++)
{
TuplesortInstrumentation *sinstrument;
- const char *sortMethod;
- const char *spaceType;
- int64 spaceUsed;
sinstrument = &sortstate->shared_info->sinstrument[n];
if (sinstrument->sortMethod == SORT_TYPE_STILL_IN_PROGRESS)
continue; /* ignore any unfilled slots */
sortMethod = tuplesort_method_name(sinstrument->sortMethod);
spaceType = tuplesort_space_type_name(sinstrument->spaceType);
- spaceUsed = sinstrument->spaceUsed;
+ peakSpaceUsed = Max(peakSpaceUsed, sinstrument->spaceUsed);
+ totalSpaceUsed += sinstrument->spaceUsed;
+ }
- if (es->workers_state)
- ExplainOpenWorker(n, es);
+ int64 avgSpaceUsed = sortstate->shared_info->num_workers > 0 ?
+ totalSpaceUsed / sortstate->shared_info->num_workers : 0;
- if (es->format == EXPLAIN_FORMAT_TEXT)
- {
- ExplainIndentText(es);
- appendStringInfo(es->str,
- "Sort Method: %s %s: " INT64_FORMAT "kB\n",
- sortMethod, spaceType, spaceUsed);
- }
- else
- {
- ExplainPropertyText("Sort Method", sortMethod, es);
- ExplainPropertyInteger("Sort Space Used", "kB", spaceUsed, es);
- ExplainPropertyText("Sort Space Type", spaceType, es);
- }
+ if (es->workers_state)
+ ExplainOpenWorker(n, es);
- if (es->workers_state)
- ExplainCloseWorker(n, es);
+ if (es->format == EXPLAIN_FORMAT_TEXT)
+ {
+ ExplainIndentText(es);
+ appendStringInfo(es->str,
+ "Sort Method: %s %s: " INT64_FORMAT INT64_FORMAT "kB\n",
+ sortMethod, spaceType, avgSpaceUsed, peakSpaceUsed);
+ }
+ else
+ {
+ ExplainPropertyText("Sort Method", sortMethod, es);
+ ExplainPropertyInteger("Average Sort Space Used", "kB", avgSpaceUsed, es);
+ ExplainPropertyInteger("Peak Sort Space Used", "kB", peakSpaceUsed, es);
+ ExplainPropertyText("Sort Space Type", spaceType, es);
}
+
+ if (es->workers_state)
+ ExplainCloseWorker(n, es);
}
}
--
2.35.1
