Re: timestamp query doesn't use an index ...

"Marc G. Fournier" <scrappy@postgresql.org> writes:
>     ->  Bitmap Heap Scan on page_schedule ps2  (cost=573.65..2822.86 rows=91614 width=16) (actual
time=149.788..505.438rows=94798 loops=1)
 
>           Recheck Cond: (timezone('MST7MDT'::text, start_time) <= '2006-05-17 08:09:18'::timestamp without time
zone)
>           ->  Bitmap Index Scan on start_time_page_schedule  (cost=0.00..573.65 rows=91614 width=0) (actual
time=127.761..127.761rows=94798 loops=1)
 
>                 Index Cond: (timezone('MST7MDT'::text, start_time) <= '2006-05-17 08:09:18'::timestamp without time
zone)

> And yup, it is definitely returning just 128 rows out of the 93k or so:

No, the scan is pulling 94798 rows from the table, according to the
EXPLAIN ANALYZE --- the number of resulting groups isn't much of a
factor here.

We don't currently have any index optimization for MIN/MAX in a GROUP BY
context, and even if we did, it wouldn't apply here: the planner
couldn't assume that the sort order of an index on "start_time at time
zone 'MST7MDT'" would have anything to do with the ordering of just
"start_time".  Is there a reason you're writingwhere ps2.start_time at time zone 'MST7MDT' <= '2006-5-17 8:9:18'
and notwhere ps2.start_time <= '2006-5-17 8:9:18' at time zone 'MST7MDT'
The latter seems less likely to have strange behaviors near DST
transitions.  I don't think it'll be any faster at the moment, but you
could at least save maintaining a specialized index.
        regards, tom lane