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