To: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
From: Alexey Bashtanov <bashtanov@imap.cc>
Subject: don't allocate HashAgg hash tables when running explain only
Message-ID: <36823f65-050d-ae24-aa4d-a37726998240@imap.cc>
Date: Fri, 13 Nov 2020 16:10:30 +0000
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
 Thunderbird/68.10.0
MIME-Version: 1.0
Content-Type: multipart/mixed;
 boundary="------------315BCB43534CDFF4202764F7"
Content-Language: en-US
Precedence: bulk

This is a multi-part message in MIME format.
--------------315BCB43534CDFF4202764F7
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit

Hi,

I got somewhat scared when my explain took a few seconds to complete and 
used a few gigs of RAM.
To reproduce try the following:

discard temp;
create temp table a as select to_timestamp(generate_series(1, 7000)) i;
analyze a;
set work_mem to '3GB';
explain select distinct a1.i - a2.i from a a1, a a2;

I would appreciate if someone could have a look at the patch attached, 
which makes executor skip initializing hash tables when doing explain only.

Best, Alex

--------------315BCB43534CDFF4202764F7
Content-Type: text/x-patch; charset=UTF-8;
 name="dont-build-agg-hashtable-on-explain-only-v1.diff"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="dont-build-agg-hashtable-on-explain-only-v1.diff"

diff --git a/src/backend/executor/nodeAgg.c b/src/backend/executor/nodeAgg.c
index d87677d659..196fe09c52 100644
--- a/src/backend/executor/nodeAgg.c
+++ b/src/backend/executor/nodeAgg.c
@@ -3665,7 +3665,11 @@ ExecInitAgg(Agg *node, EState *estate, int eflags)
 							&aggstate->hash_ngroups_limit,
 							&aggstate->hash_planned_partitions);
 		find_hash_columns(aggstate);
-		build_hash_tables(aggstate);
+
+		/* Skip massive memory allocation when running only explain */
+		if (!(eflags & EXEC_FLAG_EXPLAIN_ONLY))
+			build_hash_tables(aggstate);
+
 		aggstate->table_filled = false;
 
 		/* Initialize this to 1, meaning nothing spilled, yet */

--------------315BCB43534CDFF4202764F7--