Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.10.1/8.10.1) with ESMTP id e6LHQbh68548 for ; Fri, 21 Jul 2000 13:26:37 -0400 (EDT) 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 NAA13588; Fri, 21 Jul 2000 13:26:09 -0400 (EDT) To: "Mitch Vincent" cc: pgsql-sql@postgresql.org Subject: Re: Timestamp indexes In-reply-to: <007701bff32b$ce21d410$4100000a@doot> References: <001d01bff323$39cdb250$4100000a@doot> <13354.964192504@sss.pgh.pa.us> <007701bff32b$ce21d410$4100000a@doot> Comments: In-reply-to "Mitch Vincent" message dated "Fri, 21 Jul 2000 11:53:25 -0400" Date: Fri, 21 Jul 2000 13:26:09 -0400 Message-ID: <13585.964200369@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200007/212 "Mitch Vincent" writes: > select * from applicants as a where (a.created::date > '05-01-2000' or > a.resubmitted::date > '05-01-2000') order by (case when a.resubmitted > > a.created then a.resubmitted else a.created end) desc limit 10 offset 0 > There is one of the queries.. I just remembered that the order by was added > since last time I checked it's PLAN (in the 6.5.X days) -- could that be the > problem? Probably. With the ORDER BY in there, the LIMIT no longer applies directly to the scan (since a separate sort step is going to be necessary). Now it's looking at a lot more data to be fetched by the scan, not just 10 records, so the indexscan becomes less attractive. Might be interesting to compare the estimated and actual runtimes between this query and what you get with "set enable_seqscan to off;" regards, tom lane