Thread: Query that uses lots of memory in PostgreSQL 9.2.1 in Windows 7

Hi

I tried to run quite simple query. For some reason query took lots of memory, more than 6GB.
System start swapping, so I canceled it after 4 minutes. There were no other queries in same time.

If I I understood my config correctly that is more than it should be. Is it bug or is there some other explanation?

query:

SELECT name, artist_count, aid INTO res FROM ac
EXCEPT
SELECT name, artist_count, aid FROM artist_credit;

Explain gives following:

HashSetOp Except  (cost=0.00..297100.69 rows=594044 width=30)
  ->  Append  (cost=0.00..234950.32 rows=8286716 width=30)
        ->  Subquery Scan on "*SELECT* 1"  (cost=0.00..168074.62 rows=5940431 width=29)
              ->  Seq Scan on ac  (cost=0.00..108670.31 rows=5940431 width=29)
        ->  Subquery Scan on "*SELECT* 2"  (cost=0.00..66875.70 rows=2346285 width=32)
              ->  Seq Scan on artist_credit  (cost=0.00..43412.85 rows=2346285 width=32)

PostgreSQL version: "PostgreSQL 9.2.1, compiled by Visual C++ build 1600, 64-bit"
OS: Windows 7 (x64)

Memory config:
effective_cache_size=2048MB
shared_buffers=1024MB
work_mem=64MB
maintenance_work_mem=256MB

P.S. I got result witch I was after by changing query to use left join and isnull comparison.
That query took little more than 500MB memory and execution took 41 seconds.

Yours,
Antti Jokipii

Hello

HashSetOp is memory expensive operation, and should be problematic
when statistic estimation is bad.

Try to rewritre this query to JOIN

Regards

Pavel Stehule

2012/11/15 Antti Jokipii <anttijokipii@gmail.com>:
> Hi
>
> I tried to run quite simple query. For some reason query took lots of
> memory, more than 6GB.
> System start swapping, so I canceled it after 4 minutes. There were no other
> queries in same time.
>
> If I I understood my config correctly that is more than it should be. Is it
> bug or is there some other explanation?
>
> query:
>
> SELECT name, artist_count, aid INTO res FROM ac
> EXCEPT
> SELECT name, artist_count, aid FROM artist_credit;
>
> Explain gives following:
>
> HashSetOp Except  (cost=0.00..297100.69 rows=594044 width=30)
>   ->  Append  (cost=0.00..234950.32 rows=8286716 width=30)
>         ->  Subquery Scan on "*SELECT* 1"  (cost=0.00..168074.62
> rows=5940431 width=29)
>               ->  Seq Scan on ac  (cost=0.00..108670.31 rows=5940431
> width=29)
>         ->  Subquery Scan on "*SELECT* 2"  (cost=0.00..66875.70 rows=2346285
> width=32)
>               ->  Seq Scan on artist_credit  (cost=0.00..43412.85
> rows=2346285 width=32)
>
> PostgreSQL version: "PostgreSQL 9.2.1, compiled by Visual C++ build 1600,
> 64-bit"
> OS: Windows 7 (x64)
>
> Memory config:
> effective_cache_size=2048MB
> shared_buffers=1024MB
> work_mem=64MB
> maintenance_work_mem=256MB
>
> P.S. I got result witch I was after by changing query to use left join and
> isnull comparison.
> That query took little more than 500MB memory and execution took 41 seconds.
>
> Yours,
> Antti Jokipii

On Tue, Nov 20, 2012 at 1:27 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> Hello
>
> HashSetOp is memory expensive operation, and should be problematic
> when statistic estimation is bad.
>
> Try to rewritre this query to JOIN

or, 'WHERE NOT EXISTS'.   if 41 seconds seems like it's too long, go
ahead and post that plan and maybe that can be optimized.

merlin