Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fAE2Kvm65806 for ; Tue, 13 Nov 2001 21:20:57 -0500 (EST) (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 fAE2KZc04516; Tue, 13 Nov 2001 21:20:35 -0500 (EST) To: Jason Earl cc: Jean-Christophe Boggio , pgsql-sql@postgresql.org Subject: Re: Optimization with dates In-reply-to: <87g07iji5r.fsf@npa01zz001.simplot.com> References: <72319017913.20011114005656@thefreecat.org> <87g07iji5r.fsf@npa01zz001.simplot.com> Comments: In-reply-to Jason Earl message dated "13 Nov 2001 17:44:00 -0700" Date: Tue, 13 Nov 2001 21:20:34 -0500 Message-ID: <4513.1005704434@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200111/182 X-Sequence-Number: 5566 Jason Earl writes: > I have a similar table (~16M rows) with an indexed timestamp, and have > had similar problems. I have found that even when I am using a > constant timestamp like in this query. > SELECT * FROM caseweights1 WHERE dt > '2001-11-01'; > I start getting sequential scans with 7.1.3 long before they are > faster than index based queries. Just out of curiosity, do the indexed timestamps correlate closely to the physical order of the table? I'd expect that to happen if you are timestamping records by insertion time and there are few or no updates. 7.1 and before assume that the index order is random with respect to the physical order, which is a good conservative assumption ... but it results in drastically overestimating the cost of an indexscan when strong correlation exists. 7.2 keeps some statistics about ordering correlation, and may perhaps do better with this sort of situation. (I have no doubt that its estimates will need further tweaking, but at least the effect is somewhat accounted for now.) regards, tom lane