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

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