From 6a918c3ac8a6b1d8b53cead6fcb7cbd84eee5750 Mon Sep 17 00:00:00 2001 From: Tomas Vondra Date: Tue, 12 May 2020 20:04:39 +0200 Subject: [PATCH] Rework EXPLAIN format for incremental sort The explain format used by incremental sort was somewhat inconsistent with other nodes, making it harder to parse and understand. This commit addresses that by - adding an extra space to better separate groups of values - using colons instead of equal signs to separate key/value - properly capitalizing first letter of a key - using separate lines for full and pre-sorted groups These changes were proposed by Justin Pryzby and mostly copy the final explain format used to report WAL usage. Author: Justin Pryzby Reviewed-by: James Coleman Discussion: https://postgr.es/m/20200419023625.GP26953@telsasoft.com --- src/backend/commands/explain.c | 21 ++++++++----------- .../regress/expected/incremental_sort.out | 21 ++++++++++--------- src/test/regress/sql/incremental_sort.sql | 4 ++-- 3 files changed, 22 insertions(+), 24 deletions(-) diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index 06f4414109c..05f4d4c2c2b 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -2778,7 +2778,7 @@ show_incremental_sort_group_info(IncrementalSortGroupInfo *groupInfo, { if (indent) appendStringInfoSpaces(es->str, es->indent * 2); - appendStringInfo(es->str, "%s Groups: " INT64_FORMAT " Sort Method", groupLabel, + appendStringInfo(es->str, "%s Groups: " INT64_FORMAT " Sort Method", groupLabel, groupInfo->groupCount); /* plural/singular based on methodNames size */ if (list_length(methodNames) > 1) @@ -2798,9 +2798,9 @@ show_incremental_sort_group_info(IncrementalSortGroupInfo *groupInfo, const char *spaceTypeName; spaceTypeName = tuplesort_space_type_name(SORT_SPACE_TYPE_MEMORY); - appendStringInfo(es->str, " %s: avg=%ldkB peak=%ldkB", + appendStringInfo(es->str, " Average %s: %ldkB Peak %s: %ldkB", spaceTypeName, avgSpace, - groupInfo->maxMemorySpaceUsed); + spaceTypeName, groupInfo->maxMemorySpaceUsed); } if (groupInfo->maxDiskSpaceUsed > 0) @@ -2810,12 +2810,9 @@ show_incremental_sort_group_info(IncrementalSortGroupInfo *groupInfo, const char *spaceTypeName; spaceTypeName = tuplesort_space_type_name(SORT_SPACE_TYPE_DISK); - /* Add a semicolon separator only if memory stats were printed. */ - if (groupInfo->maxMemorySpaceUsed > 0) - appendStringInfo(es->str, ";"); - appendStringInfo(es->str, " %s: avg=%ldkB peak=%ldkB", + appendStringInfo(es->str, " Average %s: %ldkB Peak %s: %ldkB", spaceTypeName, avgSpace, - groupInfo->maxDiskSpaceUsed); + spaceTypeName, groupInfo->maxDiskSpaceUsed); } } else @@ -2899,8 +2896,8 @@ show_incremental_sort_info(IncrementalSortState *incrsortstate, if (prefixsortGroupInfo->groupCount > 0) { if (es->format == EXPLAIN_FORMAT_TEXT) - appendStringInfo(es->str, " "); - show_incremental_sort_group_info(prefixsortGroupInfo, "Presorted", false, es); + appendStringInfo(es->str, "\n"); + show_incremental_sort_group_info(prefixsortGroupInfo, "Pre-sorted", true, es); } if (es->format == EXPLAIN_FORMAT_TEXT) appendStringInfo(es->str, "\n"); @@ -2942,8 +2939,8 @@ show_incremental_sort_info(IncrementalSortState *incrsortstate, if (prefixsortGroupInfo->groupCount > 0) { if (es->format == EXPLAIN_FORMAT_TEXT) - appendStringInfo(es->str, " "); - show_incremental_sort_group_info(prefixsortGroupInfo, "Presorted", false, es); + appendStringInfo(es->str, "\n"); + show_incremental_sort_group_info(prefixsortGroupInfo, "Pre-sorted", true, es); } if (es->format == EXPLAIN_FORMAT_TEXT) appendStringInfo(es->str, "\n"); diff --git a/src/test/regress/expected/incremental_sort.out b/src/test/regress/expected/incremental_sort.out index 21d2d8b5fd8..53accd0df97 100644 --- a/src/test/regress/expected/incremental_sort.out +++ b/src/test/regress/expected/incremental_sort.out @@ -106,7 +106,7 @@ declare space_key text; begin for node in select * from jsonb_array_elements(explain_analyze_inc_sort_nodes(query)) t loop - for group_key in select unnest(array['Full-sort Groups', 'Presorted Groups']::text[]) t loop + for group_key in select unnest(array['Full-sort Groups', 'Pre-sorted Groups']::text[]) t loop for space_key in select unnest(array['Sort Space Memory', 'Sort Space Disk']::text[]) t loop node := jsonb_set(node, array[group_key, space_key, 'Average Sort Space Used'], '"NN"', false); node := jsonb_set(node, array[group_key, space_key, 'Peak Sort Space Used'], '"NN"', false); @@ -128,7 +128,7 @@ declare space_key text; begin for node in select * from jsonb_array_elements(explain_analyze_inc_sort_nodes(query)) t loop - for group_key in select unnest(array['Full-sort Groups', 'Presorted Groups']::text[]) t loop + for group_key in select unnest(array['Full-sort Groups', 'Pre-sorted Groups']::text[]) t loop group_stats := node->group_key; for space_key in select unnest(array['Sort Space Memory', 'Sort Space Disk']::text[]) t loop if (group_stats->space_key->'Peak Sort Space Used')::bigint < (group_stats->space_key->'Peak Sort Space Used')::bigint then @@ -533,13 +533,13 @@ select * from (select * from t order by a) s order by a, b limit 55; -- Test EXPLAIN ANALYZE with only a fullsort group. select explain_analyze_without_memory('select * from (select * from t order by a) s order by a, b limit 55'); - explain_analyze_without_memory ------------------------------------------------------------------------------------------------- + explain_analyze_without_memory +--------------------------------------------------------------------------------------------------------------- Limit (actual rows=55 loops=1) -> Incremental Sort (actual rows=55 loops=1) Sort Key: t.a, t.b Presorted Key: t.a - Full-sort Groups: 2 Sort Methods: top-N heapsort, quicksort Memory: avg=NNkB peak=NNkB + Full-sort Groups: 2 Sort Methods: top-N heapsort, quicksort Average Memory: NNkB Peak Memory: NNkB -> Sort (actual rows=101 loops=1) Sort Key: t.a Sort Method: quicksort Memory: NNkB @@ -708,18 +708,19 @@ select * from t left join (select * from (select * from t order by a) v order by rollback; -- Test EXPLAIN ANALYZE with both fullsort and presorted groups. select explain_analyze_without_memory('select * from (select * from t order by a) s order by a, b limit 70'); - explain_analyze_without_memory ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- + explain_analyze_without_memory +---------------------------------------------------------------------------------------------------------------- Limit (actual rows=70 loops=1) -> Incremental Sort (actual rows=70 loops=1) Sort Key: t.a, t.b Presorted Key: t.a - Full-sort Groups: 1 Sort Method: quicksort Memory: avg=NNkB peak=NNkB Presorted Groups: 5 Sort Methods: top-N heapsort, quicksort Memory: avg=NNkB peak=NNkB + Full-sort Groups: 1 Sort Method: quicksort Average Memory: NNkB Peak Memory: NNkB + Pre-sorted Groups: 5 Sort Methods: top-N heapsort, quicksort Average Memory: NNkB Peak Memory: NNkB -> Sort (actual rows=1000 loops=1) Sort Key: t.a Sort Method: quicksort Memory: NNkB -> Seq Scan on t (actual rows=1000 loops=1) -(9 rows) +(10 rows) select jsonb_pretty(explain_analyze_inc_sort_nodes_without_memory('select * from (select * from t order by a) s order by a, b limit 70')); jsonb_pretty @@ -747,7 +748,7 @@ select jsonb_pretty(explain_analyze_inc_sort_nodes_without_memory('select * from "Average Sort Space Used": "NN"+ } + }, + - "Presorted Groups": { + + "Pre-sorted Groups": { + "Group Count": 5, + "Sort Methods Used": [ + "top-N heapsort", + diff --git a/src/test/regress/sql/incremental_sort.sql b/src/test/regress/sql/incremental_sort.sql index fa2e320e9ae..373e62ac13a 100644 --- a/src/test/regress/sql/incremental_sort.sql +++ b/src/test/regress/sql/incremental_sort.sql @@ -82,7 +82,7 @@ declare space_key text; begin for node in select * from jsonb_array_elements(explain_analyze_inc_sort_nodes(query)) t loop - for group_key in select unnest(array['Full-sort Groups', 'Presorted Groups']::text[]) t loop + for group_key in select unnest(array['Full-sort Groups', 'Pre-sorted Groups']::text[]) t loop for space_key in select unnest(array['Sort Space Memory', 'Sort Space Disk']::text[]) t loop node := jsonb_set(node, array[group_key, space_key, 'Average Sort Space Used'], '"NN"', false); node := jsonb_set(node, array[group_key, space_key, 'Peak Sort Space Used'], '"NN"', false); @@ -105,7 +105,7 @@ declare space_key text; begin for node in select * from jsonb_array_elements(explain_analyze_inc_sort_nodes(query)) t loop - for group_key in select unnest(array['Full-sort Groups', 'Presorted Groups']::text[]) t loop + for group_key in select unnest(array['Full-sort Groups', 'Pre-sorted Groups']::text[]) t loop group_stats := node->group_key; for space_key in select unnest(array['Sort Space Memory', 'Sort Space Disk']::text[]) t loop if (group_stats->space_key->'Peak Sort Space Used')::bigint < (group_stats->space_key->'Peak Sort Space Used')::bigint then -- 2.39.5