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

=?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!

            regards, tom lane