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@xxxxxxxxx>: > 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
