Hi, I finally found what I believe is the root cause for the hopeless performance, after a lot of query rewriting:
> Subquery Scan mdb_effektiv_tilgang (cost=19821.69..4920621.69 rows=10000 > width=48) > Filter: ((NOT (hashed subplan)) AND (NOT (subplan))) The problem here is simply that 8.1 refuses to hash this part of the plan: > -> Materialize (cost=546.45..742.37 rows=19592 width=38) > -> Seq Scan on rita_tilgang (cost=0.00..526.86 rows=19592 > width=38) > -> Seq Scan on personer_nylig_slettet (cost=0.00..31.40 rows=2140 > width=4) probably because of the NOT IN with a function inside; I rewrote it to an EXCEPT (which is not equivalent, but good enough for my use), and it instantly hashed the other subplan, and the query went speedily. Well, at least in four seconds and not several hours... Any good ideas why 8.1 would refuse to do this, when 7.4 would do it? It does not matter how high I set my work_mem; even at 2.000.000 it refused to hash the subplan. /* Steinar */ -- Homepage: http://www.sesse.net/ ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org
