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: Wed, 26 Jun 2013 16:04:02 -0700
Message-ID: <51CB7362.5060605@agliodbs.com> (raw)
In-Reply-To: <WM!1be9038bd20cfe5d6ef6cf01382fc7f4b7b9a9334e30c28467e52dad221da1b2c8b8ffa0eab78a2af0ce5954961feadd!@asav-3.01.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>
<22653.1372210907@sss.pgh.pa.us>
<WM!1be9038bd20cfe5d6ef6cf01382fc7f4b7b9a9334e30c28467e52dad221da1b2c8b8ffa0eab78a2af0ce5954961feadd!@asav-3.01.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 06/25/2013 06:41 PM, Tom Lane wrote:
>> 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.
Actually, it was dominated by defaultsel, since tab2.y had a nullfrac of
70%. It took us a couple days of reducing the bad query plan to figure
out where the bad estimate was coming from. The real estimate should
have been 0.7 + ( est. tab2.y = tab1.x ), but instead we were getting
0.005 + ( est. tab2.y = tab1.x ), which was throwing the whole query
plan way off ... with an execution time difference of 900X.
> 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.
Sure you can. If you make the assumption that there is 0 correlation,
then you can simply estimate the comparison as between two random
columns. In the simplest approach, you would multiply the two
ndistincts, so that a column with 3 values would match a column with 10
values 0.033 of the time.
Now for a real estimator, we'd of course want to use the MCVs and the
histogram to calculate a better estimation; obviously our 3X10 table is
going to match 0% of the time if col1 is [1,2,3] and col2 contains
values from 1000 to 1100. The MCVs would be particularly valuable here;
if the same MCV appears in both columns, we can multiply the probabilities.
To me, this seems just like estimating on a foreign table match, only
simpler. Of course, a coefficient of corellation would make it even
more accurate, but even without one we can arrive at much better
estimates than defaultsel.
> 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.
Yeah, I think we'll eventually need to special-case that one. In the
particular case I ran across, though, using column match estimation
would have still yielded a better result than defaultsel.
--
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: <51CB7362.5060605@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