From: Josh Berkus <josh@agliodbs.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Weird, bad 0.5% selectivity estimate for a column equal to itself
Date: Fri, 21 Jun 2013 15:21:28 -0700
Message-ID: <51C4D1E8.6090602@agliodbs.com> (raw)
In-Reply-To: <WM!4eb25a3fbe8042ccb0903816e82218721a32a15fe7d4f0f346812d5698b17cac20d3d2bcf90001bcd0aa39dc65be6ac0!@asav-1.01.com>
References: <51C4AEE5.8040704@agliodbs.com>
<25930.1371850378@sss.pgh.pa.us>
<WM!4eb25a3fbe8042ccb0903816e82218721a32a15fe7d4f0f346812d5698b17cac20d3d2bcf90001bcd0aa39dc65be6ac0!@asav-1.01.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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: josh@agliodbs.com, tgl@sss.pgh.pa.us
Subject: Re: Weird, bad 0.5% selectivity estimate for a column equal to itself
In-Reply-To: <51C4D1E8.6090602@agliodbs.com>
* 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