Received: from smtp4.ihug.co.nz (smtp4.ihug.co.nz [203.109.252.5]) by hub.org (8.9.3/8.9.3) with ESMTP id PAA04676 for ; Sat, 26 Feb 2000 15:28:45 -0500 (EST) (envelope-from markir@ihug.co.nz) Received: from ihug.co.nz (IDENT:markir@p46-max10.wlg.ihug.co.nz [202.49.241.238]) by smtp4.ihug.co.nz (8.9.3/8.9.3/Debian/GNU) with ESMTP id JAA21391; Sun, 27 Feb 2000 09:26:55 +1300 Sender: markir@smtp4.ihug.co.nz Message-ID: <38B8364F.FD8D1D3D@ihug.co.nz> Date: Sun, 27 Feb 2000 09:23:44 +1300 From: Mark Kirkwood X-Mailer: Mozilla 4.61 [en] (X11; I; Linux 2.2.12-20 i686) X-Accept-Language: en MIME-Version: 1.0 To: Tom Lane CC: pgsql-sql@postgreSQL.org Subject: Re: [SQL] New Optimizer Behaviour In 7.0b1 References: <38B77942.8B5B6A85@ihug.co.nz> <26472.951552291@sss.pgh.pa.us> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Tom Lane wrote: > In this case I guess the first question to ask is whether its > selectivity estimates are any good. It seems to be estimating that your > "d0.d0f1 between '1999-11-01' and '1999-12-01'" clause will select about > 100 of the 900 rows in dim0; is that anywhere near right? Also, in the > nested-loop plan we can see that it thinks about 1500 rows from fact1 > will match on "d0.d0key = f.d0key" against any given selected row from > dim0; is that on the mark? Finally, is the estimate that the total > number of joined rows (before GROUP BY) is about 33333 any good? > > If you have not done VACUUM ANALYZE recently on these two tables, > it'd be worth trying that to see if it brings the estimates any > closer to reality. > > regards, tom lane Tom, Here is the row data for comparison with the selectivity estimates: select count(*) from dim0 d0 where d0.d0f1 between '1999-11-01' and '1999-12-01' 31 rows select count(*) from fact1 where d0key = 3000 rows total number of joined rows before group by 9000 rows ( i.e : there are only 3 distinct d0key values in fact1 for the "month" , and each one has 3000 rows ) It looks like the estimate on the big table ( fact1 ) are right order of magnitude, but the small table ( dim0 ) ones are too high (and presumably ) throwing the rest off I did a vacuum analyze of these tables again, just in case....( no change to the plans) Cheers Mark