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: Tue, 25 Jun 2013 21:41:47 -0400
Message-ID: <22653.1372210907@sss.pgh.pa.us> (raw)
In-Reply-To: <51CA2370.7040803@agliodbs.com>
References: <51C4AEE5.8040704@agliodbs.com>
<25930.1371850378@sss.pgh.pa.us>
<WM!4eb25a3fbe8042ccb0903816e82218721a32a15fe7d4f0f346812d5698b17cac20d3d2bcf90001bcd0aa39dc65be6ac0!@asav-1.01.com>
<51C4D1E8.6090602@agliodbs.com>
<24522.1371932688@sss.pgh.pa.us>
<WM!3d3eae0e86259037d7cc8f266e99c1488701babdb289547e8aced935ff477b652b0f7fc1c528612a767a4f62d9d07222!@asav-1.01.com>
<51CA2370.7040803@agliodbs.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Josh Berkus <josh@agliodbs.com> writes:
>> 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.
> Well, it was more in the form of:
> tab1.x = COALESCE(tab2.y,tab1.x)
Hm. I'm not following how you get from there to complaining about not
being smart about X = X, because that surely ain't the same.
> Well, I'd be more satisfied with having a solution for:
> WHERE tab1.x = tab1.y
> ... in general, even if it didn't have correlation stats. Like, what's
> preventing us from using the same selectivity logic we would on a join
> for that?
It's a totally different case. In the join case you expect that each
element of one table will be compared with each element of the other.
In the single-table case, that's exactly *not* what will happen, and
I don't see how you get to anything very useful without knowing
something about the value pairs that actually occur. As a concrete
example, applying the join selectivity logic would certainly give a
completely wrong answer for X = X, unless there were only one value
occurring in the column.
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: <22653.1372210907@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