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
