Re: BitmapScan mishaps

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