Message-ID: <51CB7362.5060605@agliodbs.com>
Date: Wed, 26 Jun 2013 16:04:02 -0700
From: Josh Berkus <josh@agliodbs.com>
Organization: PostgreSQL Experts Inc.
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:17.0) Gecko/20130510 Thunderbird/17.0.6
MIME-Version: 1.0
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
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>
In-Reply-To: 
 <WM!1be9038bd20cfe5d6ef6cf01382fc7f4b7b9a9334e30c28467e52dad221da1b2c8b8ffa0eab78a2af0ce5954961feadd!@asav-3.01.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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