Обсуждение: Timestamp indexes (why ">" or "between" does not use index?)

I have a table with an index on a field called "modify_date".

This works well if I have SQL which ends in

    WHERE modify_date = '2008-01-01'

But if I try this condition:

    WHERE modify_date = '2008-01-01'

THis index is not used. The EXPLAIN tells me it needs to do a seq
scan. Why is this? How can I make a date/time field index which uses
both equality criteria and the greater than/lesser than/between
criteria?

Thanks.

IN the second SQL, I meant this:

>     WHERE modify_date > '2008-01-01'


On 16/02/2008, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
> I have a table with an index on a field called "modify_date".
>
> This works well if I have SQL which ends in
>
>     WHERE modify_date = '2008-01-01'
>
> But if I try this condition:
>
>     WHERE modify_date = '2008-01-01'
>
> THis index is not used. The EXPLAIN tells me it needs to do a seq
> scan. Why is this? How can I make a date/time field index which uses
> both equality criteria and the greater than/lesser than/between
> criteria?
>
> Thanks.
>

On Feb 16, 2008, at 9:42 , Phoenix Kiula wrote:

> The EXPLAIN tells me it needs to do a seq
> scan. Why is this? How can I make a date/time field index which uses
> both equality criteria and the greater than/lesser than/between
> criteria?
>


The planner will choose a seq scan if it thinks that it will be
faster than using an index: if based on its statistics it thinks a
large portion of rows will match the criteria, a seq scan may well be
faster than an index scan.

Have you analyzed recently? Showing us the EXPLAIN ANALYZE for your
query would be helpful. Have you tried running the EXPLAIN ANALYZE
with seq scans disabled?

Michael Glaesemann
grzm seespotcode net

Michael Glaesemann <grzm@seespotcode.net> writes:
> The planner will choose a seq scan if it thinks that it will be
> faster than using an index: if based on its statistics it thinks a
> large portion of rows will match the criteria, a seq scan may well be
> faster than an index scan.

> Have you analyzed recently?

If you've never analyzed at all, the default assumptions will
discourage the planner from using an indexscan for a one-sided
inequality condition (like "x > constant").  On the other hand,
it usually will use an indexscan for a range inequality (like
"x > constant1 and x < constant2", or a BETWEEN construct).

If you do have ANALYZE stats then it all depends on what fraction of
the column's range is selected by the inequality or range condition.

            regards, tom lane

This table is vacuumed and analyzed every hour, so yes, it's been
analyzed recently.

These are the EXPLAIN ANALYZE outputs for both the equality condition
and the greater than condition:


orguser=# explain analyze select alias from clientswhere modify_date =
'2008-01-01' ;
                                                        QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------
 Index Scan using new_idx_modify_date on clients (cost=0.00..30.23
rows=8 width=10) (actual time=0.136..0.136 rows=0 loops=1)
  Index Cond: (modify_date = '2008-01-01 00:00:00'::timestamp without
time zone)
 Total runtime: 0.220 ms
(3 rows)

Time: 2.832 ms


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)

Time: 4539.850 ms



Welcome any thoughts. Thanks!

"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