Listmail <lists@xxxxxxxxxx> writes: > On Tue, 03 Apr 2007 19:23:31 +0200, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: >> Listmail <lists@xxxxxxxxxx> writes: >>> It bitmapscans about half the table... >> >> Which PG version is this exactly? We've fooled with the >> choose_bitmap_and heuristics quite a bit ... > Version is 8.2.3. Hmmm [ studies query a bit more... ] I think the reason why that index is so expensive to use is exposed here: >>> Index Cond: ((detect_time > (now() - '7 days'::interval)) AND (detect_time >= '2006-10-30 16:17:45.064793'::timestamp without time zone)) Evidently detect_time is timestamp without time zone, but you're comparing it to an expression that is timestamp with time zone (ie CURRENT_TIMESTAMP). That's an enormously expensive operator compared to straight comparisons of two timestamps of the same ilk, because it does some expensive stuff to convert across time zones. And you're applying it to a whole lot of index rows. If you change the query to use LOCALTIMESTAMP to avoid the type conversion, how do the two plans compare? regards, tom lane
