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
Appearance settings

Commit 1eff827

Browse filesBrowse files
david-rowleydavid-rowley
committed
Add memory/disk usage for Material nodes in EXPLAIN
Up until now, there was no ability to easily determine if a Material node caused the underlying tuplestore to spill to disk or even see how much memory the tuplestore used if it didn't. Here we add some new functions to tuplestore.c to query this information and add some additional output in EXPLAIN ANALYZE to display this information for the Material node. There are a few other executor node types that use tuplestores, so we could also consider adding these details to the EXPLAIN ANALYZE for those nodes too. Let's consider those independently from this. Having the tuplestore.c infrastructure in to allow that is step 1. Author: David Rowley Reviewed-by: Matthias van de Meent, Dmitry Dolgov Discussion: https://postgr.es/m/CAApHDvp5Py9g4Rjq7_inL3-MCK1Co2CRt_YWFwTU2zfQix0p4A@mail.gmail.com
1 parent aa86129 commit 1eff827
Copy full SHA for 1eff827

File tree

Expand file treeCollapse file tree

5 files changed

+148
-12
lines changed
Filter options
Expand file treeCollapse file tree

5 files changed

+148
-12
lines changed

‎src/backend/commands/explain.c

Copy file name to clipboardExpand all lines: src/backend/commands/explain.c
+37Lines 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
static void show_incremental_sort_info(IncrementalSortState *incrsortstate,
126126
ExplainState *es);
127127
static void show_hash_info(HashState *hashstate, ExplainState *es);
128+
static void show_material_info(MaterialState *mstate, ExplainState *es);
128129
static void show_memoize_info(MemoizeState *mstate, List *ancestors,
129130
ExplainState *es);
130131
static void show_hashagg_info(AggState *aggstate, ExplainState *es);
@@ -2251,6 +2252,9 @@ ExplainNode(PlanState *planstate, List *ancestors,
22512252
case T_Hash:
22522253
show_hash_info(castNode(HashState, planstate), es);
22532254
break;
2255+
case T_Material:
2256+
show_material_info(castNode(MaterialState, planstate), es);
2257+
break;
22542258
case T_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+
static void
3334+
show_material_info(MaterialState *mstate, ExplainState *es)
3335+
{
3336+
Tuplestorestate *tupstore;
3337+
const char *storageType;
3338+
int64 spaceUsedKB;
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

Copy file name to clipboardExpand all lines: src/backend/utils/sort/tuplestore.c
+53Lines changed: 53 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -109,6 +109,7 @@ struct Tuplestorestate
109109
bool truncated; /* tuplestore_trim has removed tuples? */
110110
int64 availMem; /* remaining memory available, in bytes */
111111
int64 allowedMem; /* total memory allowed, in bytes */
112+
int64 maxSpace; /* maximum space used in memory */
112113
int64 tuples; /* number of tuples added */
113114
BufFile *myfile; /* underlying file, or NULL if none */
114115
MemoryContext context; /* memory context for holding tuples */
@@ -238,6 +239,7 @@ static Tuplestorestate *tuplestore_begin_common(int eflags,
238239
int maxKBytes);
239240
static void tuplestore_puttuple_common(Tuplestorestate *state, void *tuple);
240241
static void dumptuples(Tuplestorestate *state);
242+
static void tuplestore_updatemax(Tuplestorestate *state);
241243
static unsigned int getlen(Tuplestorestate *state, bool eofOK);
242244
static void *copytup_heap(Tuplestorestate *state, void *tup);
243245
static void writetup_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
int i;
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+
static void
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+
const char *
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+
return state->maxSpace;
1495+
else
1496+
return BufFileSize(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

Copy file name to clipboardExpand all lines: src/include/utils/tuplestore.h
+4Lines 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
extern void tuplestore_trim(Tuplestorestate *state);
6767

68+
extern const char *tuplestore_storage_type_name(Tuplestorestate *state);
69+
70+
extern int64 tuplestore_space_used(Tuplestorestate *state);
71+
6872
extern bool tuplestore_in_memory(Tuplestorestate *state);
6973

7074
extern bool tuplestore_gettupleslot(Tuplestorestate *state, bool forward,

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

Copy file name to clipboardExpand all lines: src/test/regress/expected/partition_prune.out
+29-8Lines 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

Copy file name to clipboardExpand all lines: src/test/regress/sql/partition_prune.sql
+25-4Lines 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+
create function explain_analyze(query text) returns setof text
9+
language plpgsql as
10+
$$
11+
declare
12+
ln text;
13+
begin
14+
for ln in
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 ab values (1,2);
679-
explain (analyze, costs off, summary off, timing off)
680-
update ab_a1 set b = 3 from ab where ab.a = 1 and ab.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 ab values (1, 1), (1, 2), (1, 3), (2, 1);
686-
explain (analyze, costs off, summary off, timing off)
687-
update ab_a1 set b = 3 from ab_a2 where ab_a2.b = (select 1);
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
drop table ab, lprt_a;
@@ -1318,3 +1337,5 @@ explain (costs off) select * from hp_contradict_test where a === 1 and b === 1 a
13181337
drop table hp_contradict_test;
13191338
drop operator class part_test_int4_ops2 using hash;
13201339
drop operator ===(int4, int4);
1340+
1341+
drop function explain_analyze(text);

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.