Re: performance drop on 8.2.4, reverting to 8.1.4 - Mailing list pgsql-performance

From Tom Lane
Subject Re: performance drop on 8.2.4, reverting to 8.1.4
Date
Msg-id 28604.1179503689@sss.pgh.pa.us
Whole thread Raw
In response to Re: performance drop on 8.2.4, reverting to 8.1.4  ("Steinar H. Gunderson" <sgunderson@bigfoot.com>)
Responses Re: performance drop on 8.2.4, reverting to 8.1.4  ("Steven Flatt" <steven.flatt@gmail.com>)
List pgsql-performance
"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.426rows=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

pgsql-performance by date:

Previous
From: "Liviu Ionescu"
Date:
Subject: Re: performance drop on 8.2.4, reverting to 8.1.4
Next
From: Abu Mushayeed
Date:
Subject: CPU Intensive query