Skip to content

Commit 42a8ab0

Browse files
robertmhaasrobertmhaas
committed
Augment EXPLAIN output with more details on Hash nodes.
We show the number of buckets, the number of batches (and also the original number if it has changed), and the peak space used by the hash table. Minor executor changes to track peak space used.
1 parent cccfc4e commit 42a8ab0

File tree

3 files changed

+59
-3
lines changed

3 files changed

+59
-3
lines changed

‎src/backend/commands/explain.c

Lines changed: 47 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1994-5, Regents of the University of California
88
*
99
* IDENTIFICATION
10-
* $PostgreSQL: pgsql/src/backend/commands/explain.c,v 1.199 2010/01/15 22:36:29 tgl Exp $
10+
* $PostgreSQL: pgsql/src/backend/commands/explain.c,v 1.200 2010/02/01 15:43:35 rhaas Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -20,6 +20,7 @@
2020
#include "commands/explain.h"
2121
#include "commands/prepare.h"
2222
#include "commands/trigger.h"
23+
#include "executor/hashjoin.h"
2324
#include "executor/instrument.h"
2425
#include "optimizer/clauses.h"
2526
#include "optimizer/planner.h"
@@ -67,6 +68,7 @@ static void show_upper_qual(List *qual, const char *qlabel, Plan *plan,
6768
ExplainState *es);
6869
static void show_sort_keys(Plan *sortplan, ExplainState *es);
6970
static void show_sort_info(SortState *sortstate, ExplainState *es);
71+
static void show_hash_info(HashState *hashstate, ExplainState *es);
7072
static const char *explain_get_index_name(Oid indexId);
7173
static void ExplainScanTarget(Scan *plan, ExplainState *es);
7274
static void ExplainMemberNodes(List *plans, PlanState **planstate,
@@ -1052,6 +1054,9 @@ ExplainNode(Plan *plan, PlanState *planstate,
10521054
"One-Time Filter", plan, es);
10531055
show_upper_qual(plan->qual, "Filter", plan, es);
10541056
break;
1057+
case T_Hash:
1058+
show_hash_info((HashState *) planstate, es);
1059+
break;
10551060
default:
10561061
break;
10571062
}
@@ -1404,6 +1409,47 @@ show_sort_info(SortState *sortstate, ExplainState *es)
14041409
}
14051410
}
14061411

1412+
/*
1413+
* Show information on hash buckets/batches.
1414+
*/
1415+
static void
1416+
show_hash_info(HashState *hashstate, ExplainState *es)
1417+
{
1418+
HashJoinTable hashtable;
1419+
1420+
Assert(IsA(hashstate, HashState));
1421+
hashtable = hashstate->hashtable;
1422+
1423+
if (hashtable)
1424+
{
1425+
long spacePeakKb = (hashtable->spacePeak + 1023) / 1024;
1426+
if (es->format != EXPLAIN_FORMAT_TEXT)
1427+
{
1428+
ExplainPropertyLong("Hash Buckets", hashtable->nbuckets, es);
1429+
ExplainPropertyLong("Hash Batches", hashtable->nbatch, es);
1430+
ExplainPropertyLong("Original Hash Batches",
1431+
hashtable->nbatch_original, es);
1432+
ExplainPropertyLong("Peak Memory Usage", spacePeakKb, es);
1433+
}
1434+
else if (hashtable->nbatch_original != hashtable->nbatch)
1435+
{
1436+
appendStringInfoSpaces(es->str, es->indent * 2);
1437+
appendStringInfo(es->str,
1438+
"Buckets: %d Batches: %d (originally %d) Memory Usage: %ldkB\n",
1439+
hashtable->nbuckets, hashtable->nbatch,
1440+
hashtable->nbatch_original, spacePeakKb);
1441+
}
1442+
else
1443+
{
1444+
appendStringInfoSpaces(es->str, es->indent * 2);
1445+
appendStringInfo(es->str,
1446+
"Buckets: %d Batches: %d Memory Usage: %ldkB\n",
1447+
hashtable->nbuckets, hashtable->nbatch,
1448+
spacePeakKb);
1449+
}
1450+
}
1451+
}
1452+
14071453
/*
14081454
* Fetch the name of an index in an EXPLAIN
14091455
*

‎src/backend/executor/nodeHash.c

Lines changed: 10 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/executor/nodeHash.c,v 1.126 2010/01/04 02:44:39 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/executor/nodeHash.c,v 1.127 2010/02/01 15:43:36 rhaas Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -287,6 +287,7 @@ ExecHashTableCreate(Hash *node, List *hashOperators)
287287
hashtable->innerBatchFile = NULL;
288288
hashtable->outerBatchFile = NULL;
289289
hashtable->spaceUsed = 0;
290+
hashtable->spacePeak = 0;
290291
hashtable->spaceAllowed = work_mem * 1024L;
291292
hashtable->spaceUsedSkew = 0;
292293
hashtable->spaceAllowedSkew =
@@ -719,6 +720,8 @@ ExecHashTableInsert(HashJoinTable hashtable,
719720
hashTuple->next = hashtable->buckets[bucketno];
720721
hashtable->buckets[bucketno] = hashTuple;
721722
hashtable->spaceUsed += hashTupleSize;
723+
if (hashtable->spaceUsed > hashtable->spacePeak)
724+
hashtable->spacePeak = hashtable->spaceUsed;
722725
if (hashtable->spaceUsed > hashtable->spaceAllowed)
723726
ExecHashIncreaseNumBatches(hashtable);
724727
}
@@ -1071,6 +1074,8 @@ ExecHashBuildSkewHash(HashJoinTable hashtable, Hash *node, int mcvsToUse)
10711074
+ mcvsToUse * sizeof(int);
10721075
hashtable->spaceUsedSkew += nbuckets * sizeof(HashSkewBucket *)
10731076
+ mcvsToUse * sizeof(int);
1077+
if (hashtable->spaceUsed > hashtable->spacePeak)
1078+
hashtable->spacePeak = hashtable->spaceUsed;
10741079

10751080
/*
10761081
* Create a skew bucket for each MCV hash value.
@@ -1119,6 +1124,8 @@ ExecHashBuildSkewHash(HashJoinTable hashtable, Hash *node, int mcvsToUse)
11191124
hashtable->nSkewBuckets++;
11201125
hashtable->spaceUsed += SKEW_BUCKET_OVERHEAD;
11211126
hashtable->spaceUsedSkew += SKEW_BUCKET_OVERHEAD;
1127+
if (hashtable->spaceUsed > hashtable->spacePeak)
1128+
hashtable->spacePeak = hashtable->spaceUsed;
11221129
}
11231130

11241131
free_attstatsslot(node->skewColType,
@@ -1205,6 +1212,8 @@ ExecHashSkewTableInsert(HashJoinTable hashtable,
12051212
/* Account for space used, and back off if we've used too much */
12061213
hashtable->spaceUsed += hashTupleSize;
12071214
hashtable->spaceUsedSkew += hashTupleSize;
1215+
if (hashtable->spaceUsed > hashtable->spacePeak)
1216+
hashtable->spacePeak = hashtable->spaceUsed;
12081217
while (hashtable->spaceUsedSkew > hashtable->spaceAllowedSkew)
12091218
ExecHashRemoveNextSkewBucket(hashtable);
12101219

‎src/include/executor/hashjoin.h

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2010, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/executor/hashjoin.h,v 1.52 2010/01/02 16:58:03 momjian Exp $
10+
* $PostgreSQL: pgsql/src/include/executor/hashjoin.h,v 1.53 2010/02/01 15:43:36 rhaas Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -149,6 +149,7 @@ typedef struct HashJoinTableData
149149

150150
Size spaceUsed; /* memory space currently used by tuples */
151151
Size spaceAllowed; /* upper limit for space used */
152+
Size spacePeak; /* peak space used */
152153
Size spaceUsedSkew; /* skew hash table's current space usage */
153154
Size spaceAllowedSkew; /* upper limit for skew hashtable */
154155

0 commit comments

Comments
 (0)