From: Tom Lane <tgl@sss.pgh.pa.us>
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
Date: Fri, 18 May 2007 11:54:49 -0400
Message-ID: <28604.1179503689@sss.pgh.pa.us> (raw)
In-Reply-To: <20070518111455.GA13978@uio.no>
References: <20070518104646.GA13811@uio.no>
<008201c7993c$76c88a10$653e10ac@ilgvaio>
<20070518111455.GA13978@uio.no>
"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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, sgunderson@bigfoot.com
Subject: Re: performance drop on 8.2.4, reverting to 8.1.4
In-Reply-To: <28604.1179503689@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox