Thread: DATE TIME INDEX ?

I have been tring to use index on timestamps:


I have a table with a timestamp filed with index on it.

I nned to extract all the ids of the table  with datarx 

>= a date
<= a date 

or between 2 dates


I found that the only way to force postgres to use index is:

explain select id,datarx::date from docs where datarx between '2002-11-13' and '2002-11-14' ;

Index Scan using idx_documenti_datarx on documenti  (cost=0.00..7.86 rows=2 width=12)

In other cases the index is not used:


explain select id,datarx::date from docs where datarx >= '2002-11-16';
Seq Scan on documenti  (cost=0.00..12.01 rows=107 width=12)

explain select id,datarx::date from docs where datarx::date between '2002-11-13' and '2002-11-13' ;
Seq Scan on documenti  (cost=0.00..16.02 rows=36 width=12)

I found that the only way to force postgres to use index is:

explain select id,datarx::date from docs where datarx between '2002-11-13' and '2002-11-14' ;
Index Scan using idx_documenti_datarx on documenti  (cost=0.00..7.86 rows=2 width=12)




Do I need to use some other functons o trick?

Thanks in advance

Alex

PostgreSQL Server <postgres@rsd.it> writes:
> I found that the only way to force postgres to use index is:

> explain select id,datarx::date from docs where datarx between '2002-11-13' and '2002-11-14' ;

> Index Scan using idx_documenti_datarx on documenti  (cost=0.00..7.86 rows=2 width=12)

Given the small cost estimates, you either are testing on a toy table,
or you have not run ANALYZE since loading up the table.
        regards, tom lane