Re: Timestamp indexes (why ">" or "between" does not use index?)

"Phoenix Kiula" <phoenix.kiula@gmail.com> writes:
> orguser=# explain analyze select alias from clientswhere modify_date >
> '2008-01-01' ;
>                                                    QUERY PLAN
> ------------------------------------------------------------------------------------------------------------------
>  Seq Scan on clients (cost=0.00..237043.09 rows=136617 width=10)
> (actual time=0.391..4007.188 rows=148225 loops=1)
>   Filter: (modify_date > '2008-01-01 00:00:00'::timestamp without time zone)
>  Total runtime: 4539.242 ms
> (3 rows)

Given the large number of rows being retrieved, I'm not sure that the
seqscan choice is wrong.  You could force the issue by setting
enable_seqscan = off; see what EXPLAIN ANALYZE gives you then.

If it does come out significantly faster, this may mean that you need
to dial down random_page_cost to make the planner's cost estimates
for indexscans be closer to reality on your machine.  Be wary however
of changing that parameter on the basis of only one test case.

            regards, tom lane