Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Urek8-0002sJ-9M for pgsql-performance@arkaria.postgresql.org; Wed, 26 Jun 2013 01:41:56 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Urek7-0004Em-KD for pgsql-performance@arkaria.postgresql.org; Wed, 26 Jun 2013 01:41:55 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Urek6-0004Ef-Hy for pgsql-performance@postgresql.org; Wed, 26 Jun 2013 01:41:54 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Urek3-0000Y2-N2 for pgsql-performance@postgresql.org; Wed, 26 Jun 2013 01:41:54 +0000 Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.5/8.14.5) with ESMTP id r5Q1flsM022654; Tue, 25 Jun 2013 21:41:47 -0400 (EDT) From: Tom Lane To: Josh Berkus cc: "pgsql-performance@postgresql.org" Subject: Re: Weird, bad 0.5% selectivity estimate for a column equal to itself In-reply-to: <51CA2370.7040803@agliodbs.com> References: <51C4AEE5.8040704@agliodbs.com> <25930.1371850378@sss.pgh.pa.us> <51C4D1E8.6090602@agliodbs.com> <24522.1371932688@sss.pgh.pa.us> <51CA2370.7040803@agliodbs.com> Comments: In-reply-to Josh Berkus message dated "Tue, 25 Jun 2013 16:10:40 -0700" Date: Tue, 25 Jun 2013 21:41:47 -0400 Message-ID: <22653.1372210907@sss.pgh.pa.us> X-Pg-Spam-Score: -3.2 (---) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org Josh Berkus 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