From: Justin Pryzby Date: Sun, 21 Apr 2019 05:04:09 -0500 Subject: [PATCH v1] account for size of BatchFile structure in hashJoin Avoid runaway number of Batches, overhead of which was previously not counted. The size of BatchFiles is now included in instrumentation for output of explain analyze. Analyzed-By: Tom Lane, Tomas Vondra, Justin Pryzby Discussion: bc138e9f-c89e-9147-5395-61d51a757b3b@gusw.net --- src/backend/executor/nodeHash.c | 29 +++++++++++++++++++++++++++-- 1 file changed, 27 insertions(+), 2 deletions(-) diff --git a/src/backend/executor/nodeHash.c b/src/backend/executor/nodeHash.c index ee93030..9a87265 100644 --- a/src/backend/executor/nodeHash.c +++ b/src/backend/executor/nodeHash.c @@ -1670,10 +1670,33 @@ ExecHashTableInsert(HashJoinTable hashtable, hashtable->spaceUsed += hashTupleSize; if (hashtable->spaceUsed > hashtable->spacePeak) hashtable->spacePeak = hashtable->spaceUsed; + + /* + * If addition of 2x as many batch files would cause the structure itself + * (without the hashtable) to exceed spaceAllowed, then do not grow...unless the + * spaceUsed has already exceeded Allowed by more than 2x... + * Note, spaceUsed doesn't include the overhead of BatchFile structure. + */ if (hashtable->spaceUsed + hashtable->nbuckets_optimal * sizeof(HashJoinTuple) - > hashtable->spaceAllowed) - ExecHashIncreaseNumBatches(hashtable); + // + hashtable->nbatch * sizeof(PGAlignedBlock) + > hashtable->spaceAllowed) { + if (hashtable->nbatch*sizeof(PGAlignedBlock) < hashtable->spaceAllowed) { + ExecHashIncreaseNumBatches(hashtable); + } else if (hashtable->spaceUsed/2 >= hashtable->spaceAllowed) { + /* Exceeded spaceAllowed by 2x, so we'll save RAM by allowing nbatches to increase */ + /* I think this branch would be hit almost same as below branch */ + ExecHashIncreaseNumBatches(hashtable); + } else if (hashtable->nbatch*sizeof(PGAlignedBlock) < hashtable->spaceUsed) { + /* + * spaceUsed is so large that it will save RAM to allow the BatchFile structures themselves to exceed work_mem. + * Increasing batches and exceeding work mem will use less space and be less bad than not increasing... + */ + elog(WARNING, "exceeding work mem: used:%ld allowed:%ld add:%ld", + hashtable->spaceUsed, hashtable->spaceAllowed, hashtable->nbatch*sizeof(PGAlignedBlock)); + ExecHashIncreaseNumBatches(hashtable); + } + } } else { @@ -2693,6 +2716,8 @@ ExecHashGetInstrumentation(HashInstrumentation *instrument, instrument->nbatch = hashtable->nbatch; instrument->nbatch_original = hashtable->nbatch_original; instrument->space_peak = hashtable->spacePeak; + /* Finally, account for size of structure: */ + instrument->space_peak += hashtable->nbatch * sizeof(PGAlignedBlock); } /* -- 2.7.4 --WChQLJJJfbwij+9x--