Re: Weird, bad 0.5% selectivity estimate for a column equal to itself

On 06/21/2013 02:32 PM, Tom Lane wrote:
> Josh Berkus <josh@agliodbs.com> writes:
>> I'm getting something really odd in 9.2.4, where the planner estimates
>> that the selectivity of a column equal to itself is always exactly 0.5%
>> (i.e. 0.005X).  I can't figure out where this constant is coming from,
>> or why it's being applied.
>
> 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?

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

(a) that test case is a substantial simplication of a much more complex
query, one which exhibits actual execution time issues because of this
selectivity bug.

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

(c) PostgreSQL ought to perform well even on the stupid queries.

Obviously, we're going to code around this for the existing software,
but this is an example of a planner bug which should be on the fix list.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com