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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: amdmi3(at)amdmi3(dot)ru
Cc: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Re: BUG #15592: Memory overuse with subquery containing unnest() and set operations (11.x regression)
Date: 2019-01-14 16:24:40
Message-ID: 26375.1547483080@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

=?utf-8?q?PG_Bug_reporting_form?= <noreply(at)postgresql(dot)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

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Andres Freund 2019-01-14 16:39:21 Re: BUG #15592: Memory overuse with subquery containing unnest() and set operations (11.x regression)
Previous Message Alvaro Herrera 2019-01-14 15:59:26 Re: psql and readline comments