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?


Reply via email to