Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id SAA25717 for ; Sat, 26 Feb 2000 18:15:15 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id SAA15363; Sat, 26 Feb 2000 18:14:49 -0500 (EST) To: Mark Kirkwood cc: pgsql-sql@postgreSQL.org Subject: Re: [SQL] New Optimizer Behaviour In 7.0b1 In-reply-to: <38B8364F.FD8D1D3D@ihug.co.nz> References: <38B77942.8B5B6A85@ihug.co.nz> <26472.951552291@sss.pgh.pa.us> <38B8364F.FD8D1D3D@ihug.co.nz> Comments: In-reply-to Mark Kirkwood message dated "Sun, 27 Feb 2000 09:23:44 +1300" Date: Sat, 26 Feb 2000 18:14:49 -0500 Message-ID: <15360.951606889@sss.pgh.pa.us> From: Tom Lane Mark Kirkwood writes: > 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 At least part of the problem is that the selectivity code doesn't know how to deal with date/time datatypes :-(. I was waiting for Thomas to commit the great date/time reorganization before I wrote that code. But he's done now, so I've gone in and fixed convert_to_scalar to know about all the surviving date/time types. If you like, you can pick up the updated version of src/backend/utils/adt/selfuncs.c from the CVS server, or grab a snapshot tarball dated later than this message, and see whether the selectivity estimates get any better. regards, tom lane