Re: performance drop on 8.2.4, reverting to 8.1.4

On Fri, May 18, 2007 at 02:05:36PM +0300, Liviu Ionescu wrote:
> "                                            ->  Hash Left Join  (cost=2.44..63.29 rows=1 width=49) (actual
time=0.361..14.426rows=2206 loops=1)" 
> "                                                  Hash Cond: (n.nodeid = templates.nodeid)"
> "                                                  Filter: (templates.nodeid IS NULL)"
> "                                                  ->  Seq Scan on nodes n  (cost=0.00..51.70 rows=2270 width=49)
(actualtime=0.071..4.417 rows=2270 loops=1)" 
> "                                                  ->  Hash  (cost=1.64..1.64 rows=64 width=4) (actual
time=0.152..0.152rows=64 loops=1)" 
> "                                                        ->  Seq Scan on templates  (cost=0.00..1.64 rows=64 width=4)
(actualtime=0.032..0.082 rows=64 loops=1)" 

This seems to be the source of the misestimation. You might want to try using
"n WHERE n.nodein NOT IN (SELECT nodeid FROM templates)" instead of "n LEFT
JOIN templates USING (nodeid) WHERE templates.nodeid IS NULL" and see if it
helps.

> "Total runtime: 231929.656 ms"

Note that this is better than the version with collapse_limit set to 1. :-)

/* Steinar */
--
Homepage: http://www.sesse.net/