To: "Steinar H. Gunderson" <sgunderson@bigfoot.com>
cc: pgsql-performance@postgresql.org
Subject: Re: performance drop on 8.2.4, reverting to 8.1.4 
In-reply-to: <20070518111455.GA13978@uio.no> 
References: <20070518104646.GA13811@uio.no>
	<008201c7993c$76c88a10$653e10ac@ilgvaio>
	<20070518111455.GA13978@uio.no>
Comments: In-reply-to "Steinar H. Gunderson" <sgunderson@bigfoot.com>
	message dated "Fri, 18 May 2007 13:14:55 +0200"
Date: Fri, 18 May 2007 11:54:49 -0400
Message-ID: <28604.1179503689@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Steinar H. Gunderson" <sgunderson@bigfoot.com> writes:
> 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)"

> This seems to be the source of the misestimation.

Yeah.  8.2 is estimating that the "nodeid IS NULL" condition will
discard all or nearly all the rows, presumably because there aren't any
null nodeid's in the underlying table --- it fails to consider that the
LEFT JOIN may inject some nulls.  8.1 was not any brighter; the reason
it gets a different estimate is that it doesn't distinguish left-join
and WHERE clauses at all, but assumes that the result of the left join
can't have fewer rows than its left input, even after applying the
filter condition.  In this particular scenario that happens to be a
better estimate.  So even though 8.2 is smarter, and there is no bug
here that wasn't in 8.1 too, it's getting a worse estimate leading to
a worse plan.

This is a sufficiently common idiom that I think it's a must-fix
problem.  Not sure about details yet, but it seems somehow the
selectivity estimator had better start accounting for
outer-join-injected NULLs.

			regards, tom lane