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
Date: Sat, 22 Jun 2013 16:24:48 -0400
Message-ID: <24522.1371932688@sss.pgh.pa.us> (raw)
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
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: tgl@sss.pgh.pa.us, josh@agliodbs.com
Subject: Re: Weird, bad 0.5% selectivity estimate for a column equal to itself
In-Reply-To: <24522.1371932688@sss.pgh.pa.us>
* 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