Listmail <lists@peufeu.com> writes:
> On Tue, 03 Apr 2007 19:23:31 +0200, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Listmail <lists@peufeu.com> 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'::timestampwithout 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
