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

Commit1eff827

Browse files
committed
Add memory/disk usage for Material nodes in EXPLAIN
Up until now, there was no ability to easily determine if a Materialnode caused the underlying tuplestore to spill to disk or even see howmuch memory the tuplestore used if it didn't.Here we add some new functions to tuplestore.c to query this informationand add some additional output in EXPLAIN ANALYZE to display thisinformation for the Material node.There are a few other executor node types that use tuplestores, so wecould also consider adding these details to the EXPLAIN ANALYZE forthose nodes too. Let's consider those independently from this. Havingthe tuplestore.c infrastructure in to allow that is step 1.Author: David RowleyReviewed-by: Matthias van de Meent, Dmitry DolgovDiscussion:https://postgr.es/m/CAApHDvp5Py9g4Rjq7_inL3-MCK1Co2CRt_YWFwTU2zfQix0p4A@mail.gmail.com
1 parentaa86129 commit1eff827

File tree

5 files changed

+148
-12
lines changed

5 files changed

+148
-12
lines changed

‎src/backend/commands/explain.c

Lines changed: 37 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -125,6 +125,7 @@ static void show_sort_info(SortState *sortstate, ExplainState *es);
125125
staticvoidshow_incremental_sort_info(IncrementalSortState*incrsortstate,
126126
ExplainState*es);
127127
staticvoidshow_hash_info(HashState*hashstate,ExplainState*es);
128+
staticvoidshow_material_info(MaterialState*mstate,ExplainState*es);
128129
staticvoidshow_memoize_info(MemoizeState*mstate,List*ancestors,
129130
ExplainState*es);
130131
staticvoidshow_hashagg_info(AggState*aggstate,ExplainState*es);
@@ -2251,6 +2252,9 @@ ExplainNode(PlanState *planstate, List *ancestors,
22512252
caseT_Hash:
22522253
show_hash_info(castNode(HashState,planstate),es);
22532254
break;
2255+
caseT_Material:
2256+
show_material_info(castNode(MaterialState,planstate),es);
2257+
break;
22542258
caseT_Memoize:
22552259
show_memoize_info(castNode(MemoizeState,planstate),ancestors,
22562260
es);
@@ -3322,6 +3326,39 @@ show_hash_info(HashState *hashstate, ExplainState *es)
33223326
}
33233327
}
33243328

3329+
/*
3330+
* Show information on material node, storage method and maximum memory/disk
3331+
* space used.
3332+
*/
3333+
staticvoid
3334+
show_material_info(MaterialState*mstate,ExplainState*es)
3335+
{
3336+
Tuplestorestate*tupstore;
3337+
constchar*storageType;
3338+
int64spaceUsedKB;
3339+
3340+
if (!es->analyze)
3341+
return;
3342+
3343+
tupstore=mstate->tuplestorestate;
3344+
storageType=tuplestore_storage_type_name(tupstore);
3345+
spaceUsedKB=BYTES_TO_KILOBYTES(tuplestore_space_used(tupstore));
3346+
3347+
if (es->format!=EXPLAIN_FORMAT_TEXT)
3348+
{
3349+
ExplainPropertyText("Storage",storageType,es);
3350+
ExplainPropertyInteger("Maximum Storage","kB",spaceUsedKB,es);
3351+
}
3352+
else
3353+
{
3354+
ExplainIndentText(es);
3355+
appendStringInfo(es->str,
3356+
"Storage: %s Maximum Storage: "INT64_FORMAT"kB\n",
3357+
storageType,
3358+
spaceUsedKB);
3359+
}
3360+
}
3361+
33253362
/*
33263363
* Show information on memoize hits/misses/evictions and memory usage.
33273364
*/

‎src/backend/utils/sort/tuplestore.c

Lines changed: 53 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -109,6 +109,7 @@ struct Tuplestorestate
109109
booltruncated;/* tuplestore_trim has removed tuples? */
110110
int64availMem;/* remaining memory available, in bytes */
111111
int64allowedMem;/* total memory allowed, in bytes */
112+
int64maxSpace;/* maximum space used in memory */
112113
int64tuples;/* number of tuples added */
113114
BufFile*myfile;/* underlying file, or NULL if none */
114115
MemoryContextcontext;/* memory context for holding tuples */
@@ -238,6 +239,7 @@ static Tuplestorestate *tuplestore_begin_common(int eflags,
238239
intmaxKBytes);
239240
staticvoidtuplestore_puttuple_common(Tuplestorestate*state,void*tuple);
240241
staticvoiddumptuples(Tuplestorestate*state);
242+
staticvoidtuplestore_updatemax(Tuplestorestate*state);
241243
staticunsignedintgetlen(Tuplestorestate*state,booleofOK);
242244
staticvoid*copytup_heap(Tuplestorestate*state,void*tup);
243245
staticvoidwritetup_heap(Tuplestorestate*state,void*tup);
@@ -262,6 +264,7 @@ tuplestore_begin_common(int eflags, bool interXact, int maxKBytes)
262264
state->truncated= false;
263265
state->allowedMem=maxKBytes*1024L;
264266
state->availMem=state->allowedMem;
267+
state->maxSpace=0;
265268
state->myfile=NULL;
266269
state->context=CurrentMemoryContext;
267270
state->resowner=CurrentResourceOwner;
@@ -420,6 +423,9 @@ tuplestore_clear(Tuplestorestate *state)
420423
inti;
421424
TSReadPointer*readptr;
422425

426+
/* update the maxSpace before doing any USEMEM/FREEMEM adjustments */
427+
tuplestore_updatemax(state);
428+
423429
if (state->myfile)
424430
BufFileClose(state->myfile);
425431
state->myfile=NULL;
@@ -1402,6 +1408,9 @@ tuplestore_trim(Tuplestorestate *state)
14021408
Assert(nremove >=state->memtupdeleted);
14031409
Assert(nremove <=state->memtupcount);
14041410

1411+
/* before freeing any memory, update maxSpace */
1412+
tuplestore_updatemax(state);
1413+
14051414
/* Release no-longer-needed tuples */
14061415
for (i=state->memtupdeleted;i<nremove;i++)
14071416
{
@@ -1444,6 +1453,49 @@ tuplestore_trim(Tuplestorestate *state)
14441453
}
14451454
}
14461455

1456+
/*
1457+
* tuplestore_updatemax
1458+
*Update maxSpace field
1459+
*/
1460+
staticvoid
1461+
tuplestore_updatemax(Tuplestorestate*state)
1462+
{
1463+
if (state->status==TSS_INMEM)
1464+
state->maxSpace=Max(state->maxSpace,
1465+
state->allowedMem-state->availMem);
1466+
}
1467+
1468+
/*
1469+
* tuplestore_storage_type_name
1470+
*Return a string description of the storage method used to store the
1471+
*tuples.
1472+
*/
1473+
constchar*
1474+
tuplestore_storage_type_name(Tuplestorestate*state)
1475+
{
1476+
if (state->status==TSS_INMEM)
1477+
return"Memory";
1478+
else
1479+
return"Disk";
1480+
}
1481+
1482+
/*
1483+
* tuplestore_space_used
1484+
*Return the maximum space used in memory unless the tuplestore has spilled
1485+
*to disk, in which case, return the disk space used.
1486+
*/
1487+
int64
1488+
tuplestore_space_used(Tuplestorestate*state)
1489+
{
1490+
/* First, update the maxSpace field */
1491+
tuplestore_updatemax(state);
1492+
1493+
if (state->status==TSS_INMEM)
1494+
returnstate->maxSpace;
1495+
else
1496+
returnBufFileSize(state->myfile);
1497+
}
1498+
14471499
/*
14481500
* tuplestore_in_memory
14491501
*
@@ -1513,6 +1565,7 @@ writetup_heap(Tuplestorestate *state, void *tup)
15131565
if (state->backward)/* need trailing length word? */
15141566
BufFileWrite(state->myfile,&tuplen,sizeof(tuplen));
15151567

1568+
/* no need to call tuplestore_updatemax() when not in TSS_INMEM */
15161569
FREEMEM(state,GetMemoryChunkSpace(tuple));
15171570
heap_free_minimal_tuple(tuple);
15181571
}

‎src/include/utils/tuplestore.h

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -65,6 +65,10 @@ extern void tuplestore_copy_read_pointer(Tuplestorestate *state,
6565

6666
externvoidtuplestore_trim(Tuplestorestate*state);
6767

68+
externconstchar*tuplestore_storage_type_name(Tuplestorestate*state);
69+
70+
externint64tuplestore_space_used(Tuplestorestate*state);
71+
6872
externbooltuplestore_in_memory(Tuplestorestate*state);
6973

7074
externbooltuplestore_gettupleslot(Tuplestorestate*state,boolforward,

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

Lines changed: 29 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,24 @@
11
--
22
-- Test partitioning planner code
33
--
4+
-- Helper function which can be used for masking out portions of EXPLAIN
5+
-- ANALYZE which could contain information that's not consistent on all
6+
-- platforms.
7+
create function explain_analyze(query text) returns setof text
8+
language plpgsql as
9+
$$
10+
declare
11+
ln text;
12+
begin
13+
for ln in
14+
execute format('explain (analyze, costs off, summary off, timing off) %s',
15+
query)
16+
loop
17+
ln := regexp_replace(ln, 'Maximum Storage: \d+', 'Maximum Storage: N');
18+
return next ln;
19+
end loop;
20+
end;
21+
$$;
422
-- Force generic plans to be used for all prepared statements in this file.
523
set plan_cache_mode = force_generic_plan;
624
create table lp (a char) partition by list (a);
@@ -2826,9 +2844,9 @@ deallocate ab_q5;
28262844
deallocate ab_q6;
28272845
-- UPDATE on a partition subtree has been seen to have problems.
28282846
insert into ab values (1,2);
2829-
explain (analyze, costs off, summary off, timing off)
2830-
update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a;
2831-
QUERY PLAN
2847+
select explain_analyze('
2848+
update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a;');
2849+
explain_analyze
28322850
-------------------------------------------------------------------------------------------
28332851
Update on ab_a1 (actual rows=0 loops=1)
28342852
Update on ab_a1_b1 ab_a1_1
@@ -2851,6 +2869,7 @@ update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a;
28512869
-> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1 loops=1)
28522870
Index Cond: (a = 1)
28532871
-> Materialize (actual rows=1 loops=1)
2872+
Storage: Memory Maximum Storage: NkB
28542873
-> Append (actual rows=1 loops=1)
28552874
-> Bitmap Heap Scan on ab_a1_b1 ab_1 (actual rows=0 loops=1)
28562875
Recheck Cond: (a = 1)
@@ -2866,7 +2885,7 @@ update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a;
28662885
Heap Blocks: exact=1
28672886
-> Bitmap Index Scan on ab_a1_b3_a_idx (actual rows=1 loops=1)
28682887
Index Cond: (a = 1)
2869-
(36 rows)
2888+
(37 rows)
28702889

28712890
table ab;
28722891
a | b
@@ -2877,9 +2896,9 @@ table ab;
28772896
-- Test UPDATE where source relation has run-time pruning enabled
28782897
truncate ab;
28792898
insert into ab values (1, 1), (1, 2), (1, 3), (2, 1);
2880-
explain (analyze, costs off, summary off, timing off)
2881-
update ab_a1 set b = 3 from ab_a2 where ab_a2.b = (select 1);
2882-
QUERY PLAN
2899+
select explain_analyze('
2900+
update ab_a1 set b = 3 from ab_a2 where ab_a2.b = (select 1);');
2901+
explain_analyze
28832902
------------------------------------------------------------------------------
28842903
Update on ab_a1 (actual rows=0 loops=1)
28852904
Update on ab_a1_b1 ab_a1_1
@@ -2893,14 +2912,15 @@ update ab_a1 set b = 3 from ab_a2 where ab_a2.b = (select 1);
28932912
-> Seq Scan on ab_a1_b2 ab_a1_2 (actual rows=1 loops=1)
28942913
-> Seq Scan on ab_a1_b3 ab_a1_3 (actual rows=1 loops=1)
28952914
-> Materialize (actual rows=1 loops=3)
2915+
Storage: Memory Maximum Storage: NkB
28962916
-> Append (actual rows=1 loops=1)
28972917
-> Seq Scan on ab_a2_b1 ab_a2_1 (actual rows=1 loops=1)
28982918
Filter: (b = (InitPlan 1).col1)
28992919
-> Seq Scan on ab_a2_b2 ab_a2_2 (never executed)
29002920
Filter: (b = (InitPlan 1).col1)
29012921
-> Seq Scan on ab_a2_b3 ab_a2_3 (never executed)
29022922
Filter: (b = (InitPlan 1).col1)
2903-
(19 rows)
2923+
(20 rows)
29042924

29052925
select tableoid::regclass, * from ab;
29062926
tableoid | a | b
@@ -4419,3 +4439,4 @@ explain (costs off) select * from hp_contradict_test where a === 1 and b === 1 a
44194439
drop table hp_contradict_test;
44204440
drop operator class part_test_int4_ops2 using hash;
44214441
drop operator ===(int4, int4);
4442+
drop function explain_analyze(text);

‎src/test/regress/sql/partition_prune.sql

Lines changed: 25 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -2,6 +2,25 @@
22
-- Test partitioning planner code
33
--
44

5+
-- Helper function which can be used for masking out portions of EXPLAIN
6+
-- ANALYZE which could contain information that's not consistent on all
7+
-- platforms.
8+
createfunctionexplain_analyze(querytext) returns setoftext
9+
language plpgsqlas
10+
$$
11+
declare
12+
lntext;
13+
begin
14+
for lnin
15+
execute format('explain (analyze, costs off, summary off, timing off) %s',
16+
query)
17+
loop
18+
ln := regexp_replace(ln,'Maximum Storage:\d+','Maximum Storage: N');
19+
return next ln;
20+
end loop;
21+
end;
22+
$$;
23+
524
-- Force generic plans to be used for all prepared statements in this file.
625
set plan_cache_mode= force_generic_plan;
726

@@ -676,15 +695,15 @@ deallocate ab_q6;
676695

677696
-- UPDATE on a partition subtree has been seen to have problems.
678697
insert into abvalues (1,2);
679-
explain (analyze, costs off, summary off, timing off)
680-
update ab_a1set b=3from abwhereab.a=1andab.a=ab_a1.a;
698+
select explain_analyze('
699+
update ab_a1 set b = 3 from ab where ab.a = 1 and ab.a = ab_a1.a;');
681700
table ab;
682701

683702
-- Test UPDATE where source relation has run-time pruning enabled
684703
truncate ab;
685704
insert into abvalues (1,1), (1,2), (1,3), (2,1);
686-
explain (analyze, costs off, summary off, timing off)
687-
update ab_a1set b=3from ab_a2whereab_a2.b= (select1);
705+
select explain_analyze('
706+
update ab_a1 set b = 3 from ab_a2 where ab_a2.b = (select 1);');
688707
select tableoid::regclass,*from ab;
689708

690709
droptable ab, lprt_a;
@@ -1318,3 +1337,5 @@ explain (costs off) select * from hp_contradict_test where a === 1 and b === 1 a
13181337
droptable hp_contradict_test;
13191338
dropoperator class part_test_int4_ops2 using hash;
13201339
dropoperator===(int4, int4);
1340+
1341+
dropfunction explain_analyze(text);

0 commit comments

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