From: Tom Lane <tgl@sss.pgh.pa.us>
To: Josh Berkus <josh@agliodbs.com>
cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Weird, bad 0.5% selectivity estimate for a column equal to itself
In-reply-to: <51C4D1E8.6090602@agliodbs.com>
References: <51C4AEE5.8040704@agliodbs.com> <25930.1371850378@sss.pgh.pa.us>
 <WM!4eb25a3fbe8042ccb0903816e82218721a32a15fe7d4f0f346812d5698b17cac20d3d2bcf90001bcd0aa39dc65be6ac0!@asav-1.01.com>
 <51C4D1E8.6090602@agliodbs.com>
Comments: In-reply-to Josh Berkus <josh@agliodbs.com>
 message dated "Fri, 21 Jun 2013 15:21:28 -0700"
Date: Sat, 22 Jun 2013 16:24:48 -0400
Message-ID: <24522.1371932688@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Josh Berkus <josh@agliodbs.com> writes:
> On 06/21/2013 02:32 PM, Tom Lane wrote:
>> See DEFAULT_EQ_SEL.  

> Why is it using that?  We have statistics on the column.  What reason
> would it have for using a default estimate?

The stats are generally consulted for "Var Op Constant" scenarios.
It doesn't know what to do with "Var Op Var" cases that aren't joins.
As long as we lack cross-column-correlation stats I doubt it'd be very
helpful to try to derive a stats-based number for such cases.  Of
course, "X = X" is a special case, but ...

>> But why exactly do you care?  Surely it's a stupid
>> query and you should fix it.

> (b) that query is also auto-generated by external software, so "just fix
> it" isn't as easy as it sounds.

Personally, I'll bet lunch that that external software is outright
broken, ie it probably thinks "X = X" is constant true and they found
they could save two lines of code and a few machine cycles by emitting
that rather than not emitting anything.  Of course, the amount of
parsing/planning time wasted in dealing with the useless-and-incorrect
clause exceeds what was saved by multiple orders of magnitude, but hey
it was easy.

It wouldn't take too much new code to get the planner to replace "X = X"
with "X IS NOT NULL", but I think we're probably fixing the wrong piece
of software if we do.

			regards, tom lane


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance