Message-ID: <51CA2370.7040803@agliodbs.com>
Date: Tue, 25 Jun 2013 16:10:40 -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>
In-Reply-To: 
 <WM!3d3eae0e86259037d7cc8f266e99c1488701babdb289547e8aced935ff477b652b0f7fc1c528612a767a4f62d9d07222!@asav-1.01.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


> 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.

Well, it was more in the form of:

tab1.x = COALESCE(tab2.y,tab1.x)

... which some programmer 8 years ago though would be a cute shorthand for:

tab.x = tab2.y OR tab2.y IS NULL

Still stupid, sure, but when you're dealing with partly-third-party
legacy software which was ported from MSSQL (which has issues with "IS
NULL"), that's what you get.

> 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.

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 wouldn't be accurate for highly correlated columns (or for
colX = colx) but it would be a damsight better than defaultsel. Heck,
even multiplying the the two ndistincts together would be an improvement ...

-- 
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