Date: Fri, 18 May 2007 13:14:55 +0200
From: "Steinar H. Gunderson" <sgunderson@bigfoot.com>
To: pgsql-performance@postgresql.org
Subject: Re: performance drop on 8.2.4, reverting to 8.1.4
Message-ID: <20070518111455.GA13978@uio.no>
Mail-Followup-To: pgsql-performance@postgresql.org
References: <20070518104646.GA13811@uio.no>
	<008201c7993c$76c88a10$653e10ac@ilgvaio>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
In-Reply-To: <008201c7993c$76c88a10$653e10ac@ilgvaio>
User-Agent: Mutt/1.5.13 (2006-08-11)

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.426 rows=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) (actual time=0.071..4.417 rows=2270 loops=1)"
> "                                                  ->  Hash  (cost=1.64..1.64 rows=64 width=4) (actual time=0.152..0.152 rows=64 loops=1)"
> "                                                        ->  Seq Scan on templates  (cost=0.00..1.64 rows=64 width=4) (actual time=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/