Re: performance drop on 8.2.4, reverting to 8.1.4

On 5/18/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>
> 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.
>
This problem is causing us a bit of grief as we plan to move from 8.1.4 to
8.2.4. We have many (on the order of a hundred) queries that are of the
form:

(A) LEFT JOIN (B) ON col WHERE B.col IS NULL

These queries are much slower on 8.2 than on 8.1 for what looks like the
reason outlined above. I have rewritten a few key queries to be of the
equivalent form:

(A) WHERE col NOT IN (SELECT col FROM (B))

which has resulted in a dramatic improvement. I'm really hoping that I'm
not going to need to re-write every single one of our queries that are of
the first form above. Is there any estimation as to if/when the fix will
become available? I'm hoping this isn't going to be a showstopper in us
moving to 8.2.

Thanks,
Steve