Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit95d6e9a

Browse files
committed
Add memory/disk usage for Window aggregate nodes in EXPLAIN.
This commit is similar to1eff827 and expands the idea to Windowaggregate nodes so that users can know how much memory or disk thetuplestore used.This commit uses newly introduced tuplestore_get_stats() to inquire thisinformation and add some additional output in EXPLAIN ANALYZE todisplay the information for the Window aggregate node.Reviewed-by: David Rowley, Ashutosh Bapat, Maxim Orlov, Jian HeDiscussion:https://postgr.es/m/20240706.202254.89740021795421286.ishii%40postgresql.org
1 parent1bbf1e2 commit95d6e9a

File tree

3 files changed

+97
-18
lines changed

3 files changed

+97
-18
lines changed

‎src/backend/commands/explain.c

Lines changed: 50 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -120,13 +120,15 @@ static void show_sort_group_keys(PlanState *planstate, const char *qlabel,
120120
List*ancestors,ExplainState*es);
121121
staticvoidshow_sortorder_options(StringInfobuf,Node*sortexpr,
122122
OidsortOperator,Oidcollation,boolnullsFirst);
123+
staticvoidshow_storage_info(Tuplestorestate*tupstore,ExplainState*es);
123124
staticvoidshow_tablesample(TableSampleClause*tsc,PlanState*planstate,
124125
List*ancestors,ExplainState*es);
125126
staticvoidshow_sort_info(SortState*sortstate,ExplainState*es);
126127
staticvoidshow_incremental_sort_info(IncrementalSortState*incrsortstate,
127128
ExplainState*es);
128129
staticvoidshow_hash_info(HashState*hashstate,ExplainState*es);
129130
staticvoidshow_material_info(MaterialState*mstate,ExplainState*es);
131+
staticvoidshow_windowagg_info(WindowAggState*winstate,ExplainState*es);
130132
staticvoidshow_memoize_info(MemoizeState*mstate,List*ancestors,
131133
ExplainState*es);
132134
staticvoidshow_hashagg_info(AggState*aggstate,ExplainState*es);
@@ -2231,6 +2233,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
22312233
planstate,es);
22322234
show_upper_qual(((WindowAgg*)plan)->runConditionOrig,
22332235
"Run Condition",planstate,ancestors,es);
2236+
show_windowagg_info(castNode(WindowAggState,planstate),es);
22342237
break;
22352238
caseT_Group:
22362239
show_group_keys(castNode(GroupState,planstate),ancestors,es);
@@ -2894,6 +2897,34 @@ show_sortorder_options(StringInfo buf, Node *sortexpr,
28942897
}
28952898
}
28962899

2900+
/*
2901+
* Show information on storage method and maximum memory/disk space used.
2902+
*/
2903+
staticvoid
2904+
show_storage_info(Tuplestorestate*tupstore,ExplainState*es)
2905+
{
2906+
char*maxStorageType;
2907+
int64maxSpaceUsed,
2908+
maxSpaceUsedKB;
2909+
2910+
tuplestore_get_stats(tupstore,&maxStorageType,&maxSpaceUsed);
2911+
maxSpaceUsedKB=BYTES_TO_KILOBYTES(maxSpaceUsed);
2912+
2913+
if (es->format!=EXPLAIN_FORMAT_TEXT)
2914+
{
2915+
ExplainPropertyText("Storage",maxStorageType,es);
2916+
ExplainPropertyInteger("Maximum Storage","kB",maxSpaceUsedKB,es);
2917+
}
2918+
else
2919+
{
2920+
ExplainIndentText(es);
2921+
appendStringInfo(es->str,
2922+
"Storage: %s Maximum Storage: "INT64_FORMAT"kB\n",
2923+
maxStorageType,
2924+
maxSpaceUsedKB);
2925+
}
2926+
}
2927+
28972928
/*
28982929
* Show TABLESAMPLE properties
28992930
*/
@@ -3350,9 +3381,6 @@ static void
33503381
show_material_info(MaterialState*mstate,ExplainState*es)
33513382
{
33523383
Tuplestorestate*tupstore=mstate->tuplestorestate;
3353-
char*maxStorageType;
3354-
int64maxSpaceUsed,
3355-
maxSpaceUsedKB;
33563384

33573385
/*
33583386
* Nothing to show if ANALYZE option wasn't used or if execution didn't
@@ -3361,22 +3389,26 @@ show_material_info(MaterialState *mstate, ExplainState *es)
33613389
if (!es->analyze||tupstore==NULL)
33623390
return;
33633391

3364-
tuplestore_get_stats(tupstore,&maxStorageType,&maxSpaceUsed);
3365-
maxSpaceUsedKB=BYTES_TO_KILOBYTES(maxSpaceUsed);
3392+
show_storage_info(tupstore,es);
3393+
}
33663394

3367-
if (es->format!=EXPLAIN_FORMAT_TEXT)
3368-
{
3369-
ExplainPropertyText("Storage",maxStorageType,es);
3370-
ExplainPropertyInteger("Maximum Storage","kB",maxSpaceUsedKB,es);
3371-
}
3372-
else
3373-
{
3374-
ExplainIndentText(es);
3375-
appendStringInfo(es->str,
3376-
"Storage: %s Maximum Storage: "INT64_FORMAT"kB\n",
3377-
maxStorageType,
3378-
maxSpaceUsedKB);
3379-
}
3395+
/*
3396+
* Show information on WindowAgg node, storage method and maximum memory/disk
3397+
* space used.
3398+
*/
3399+
staticvoid
3400+
show_windowagg_info(WindowAggState*winstate,ExplainState*es)
3401+
{
3402+
Tuplestorestate*tupstore=winstate->buffer;
3403+
3404+
/*
3405+
* Nothing to show if ANALYZE option wasn't used or if execution didn't
3406+
* get as far as creating the tuplestore.
3407+
*/
3408+
if (!es->analyze||tupstore==NULL)
3409+
return;
3410+
3411+
show_storage_info(tupstore,es);
33803412
}
33813413

33823414
/*

‎src/test/regress/expected/explain.out

Lines changed: 38 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -691,3 +691,41 @@ select explain_filter('explain (analyze,serialize) create temp table explain_tem
691691
Execution Time: N.N ms
692692
(4 rows)
693693

694+
-- Test tuplestore storage usage in Window aggregate (memory case)
695+
select explain_filter('explain (analyze,costs off) select sum(n) over() from generate_series(1,10) a(n)');
696+
explain_filter
697+
--------------------------------------------------------------------------------
698+
WindowAgg (actual time=N.N..N.N rows=N loops=N)
699+
Storage: Memory Maximum Storage: NkB
700+
-> Function Scan on generate_series a (actual time=N.N..N.N rows=N loops=N)
701+
Planning Time: N.N ms
702+
Execution Time: N.N ms
703+
(5 rows)
704+
705+
-- Test tuplestore storage usage in Window aggregate (disk case)
706+
set work_mem to 64;
707+
select explain_filter('explain (analyze,costs off) select sum(n) over() from generate_series(1,2000) a(n)');
708+
explain_filter
709+
--------------------------------------------------------------------------------
710+
WindowAgg (actual time=N.N..N.N rows=N loops=N)
711+
Storage: Disk Maximum Storage: NkB
712+
-> Function Scan on generate_series a (actual time=N.N..N.N rows=N loops=N)
713+
Planning Time: N.N ms
714+
Execution Time: N.N ms
715+
(5 rows)
716+
717+
-- Test tuplestore storage usage in Window aggregate (memory and disk case, final result is disk)
718+
select explain_filter('explain (analyze,costs off) select sum(n) over(partition by m) from (SELECT n < 3 as m, n from generate_series(1,2000) a(n))');
719+
explain_filter
720+
--------------------------------------------------------------------------------------
721+
WindowAgg (actual time=N.N..N.N rows=N loops=N)
722+
Storage: Disk Maximum Storage: NkB
723+
-> Sort (actual time=N.N..N.N rows=N loops=N)
724+
Sort Key: ((a.n < N))
725+
Sort Method: external merge Disk: NkB
726+
-> Function Scan on generate_series a (actual time=N.N..N.N rows=N loops=N)
727+
Planning Time: N.N ms
728+
Execution Time: N.N ms
729+
(8 rows)
730+
731+
reset work_mem;

‎src/test/regress/sql/explain.sql

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -169,3 +169,12 @@ select explain_filter('explain (analyze,serialize text,buffers,timing off) selec
169169
select explain_filter('explain (analyze,serialize binary,buffers,timing) select * from int8_tbl i8');
170170
-- this tests an edge case where we have no data to return
171171
select explain_filter('explain (analyze,serialize) create temp table explain_temp as select * from int8_tbl i8');
172+
173+
-- Test tuplestore storage usage in Window aggregate (memory case)
174+
select explain_filter('explain (analyze,costs off) select sum(n) over() from generate_series(1,10) a(n)');
175+
-- Test tuplestore storage usage in Window aggregate (disk case)
176+
set work_mem to64;
177+
select explain_filter('explain (analyze,costs off) select sum(n) over() from generate_series(1,2000) a(n)');
178+
-- Test tuplestore storage usage in Window aggregate (memory and disk case, final result is disk)
179+
select explain_filter('explain (analyze,costs off) select sum(n) over(partition by m) from (SELECT n < 3 as m, n from generate_series(1,2000) a(n))');
180+
reset work_mem;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp