Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f83MXq745502 for ; Mon, 3 Sep 2001 18:33:52 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id f83MX5K06851; Mon, 3 Sep 2001 18:33:05 -0400 (EDT) To: Mark kirkwood cc: pgsql-sql@postgresql.org Subject: Re: On Differing Optimizer Choices ( Again) In-reply-to: <01082823525200.01374@spikey.slithery.org> References: <01082823525200.01374@spikey.slithery.org> Comments: In-reply-to Mark kirkwood message dated "Tue, 28 Aug 2001 23:52:52 +1200" Date: Mon, 03 Sep 2001 18:33:04 -0400 Message-ID: <6848.999556384@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200109/11 X-Sequence-Number: 4657 Mark kirkwood writes (heavily edited): > SELECT > ... > WHERE d0.d0key = f.d0key > AND f.d0key BETWEEN 270 AND 350 > So far this is all as one would expect. However suppose we substitute > 'd0.d0key' in the 'AND' clause instead of 'f.d0key' to obtain : > SELECT > ... > WHERE d0.d0key = f.d0key > AND d0.d0key BETWEEN 270 AND 350 > [ produces a different plan because of differing row-count estimates ] This surprises me not at all. While the planner has some rudimentary grasp of the notion that equality is transitive, that grasp does not extend as far as recognizing that the above queries are really equivalent. You'd probably get a better plan if you wrote out the entire WHERE condition that you are thinking is intuitively obvious: SELECT ... WHERE d0.d0key = f.d0key AND d0.d0key BETWEEN 270 AND 350 AND f0.d0key BETWEEN 270 AND 350 so that the planner could see that there is a range restriction on each of the tables. While it'd be possible to teach the planner to deduce the third clause from the first two, I'm unconvinced that adding such logic would be a good idea. It would slow down all queries (probably by quite a bit) for a benefit that I suspect arises relatively seldom. Might be worth looking at this sometime in the future, but... regards, tom lane