Listmail <[EMAIL PROTECTED]> writes:
> On Tue, 03 Apr 2007 19:23:31 +0200, Tom Lane <[EMAIL PROTECTED]> wrote:
>> Listmail <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
