Re: BUG #15592: Memory overuse with subquery containing unnest() and set operations (11.x regression)

On January 14, 2019 8:24:40 AM PST, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>=?utf-8?q?PG_Bug_reporting_form?= <noreply@postgresql.org> writes:
>> After upgrading from PostgreSQL 10.x to 11.x this query began to eat
>> inadequate amounts of memory (several gigabytes per hundred thousands
>rows).
>> I've narrowed it down to this simple case which demonstrates the
>problem:
>
>> CREATE TABLE test AS
>> SELECT generate_series(1,500000) AS id, '{a,b}'::text[] AS first,
>> '{a}'::text[] AS second;
>
>> SELECT DISTINCT EXISTS(SELECT unnest(first) INTERSECT SELECT
>unnest(second))
>> FROM test;
>
>Ugh.  Something is creating ExprContexts and not freeing them --- a
>memory
>context dump taken at ExecutorEnd shows
>
>ExecutorState: 100663296 total in 22 blocks; 4625424 free (19 chunks);
>96037872 used
>    ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
>    ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
>    ExprContext: 8192 total in 1 blocks; 7936 free (0 chunks); 256 used
>        ...
>499916 more child contexts containing 4095311872 total in 499916
>blocks; 3967322616 free (1 chunks); 127989256 used
>
>Not sure where the problem is, yet, but for sure this is a bug.
>Thanks for the report!

Think I know where the problem is - let me have a coffee and check? I think I might have a good lying around...
--
Sent from my Android device with K-9 Mail. Please excuse my brevity.