| From: | PostgreSQL Server <postgres(at)rsd(dot)it> |
|---|---|
| To: | pgsql-sql(at)postgresql(dot)org |
| Subject: | DATE TIME INDEX ? |
| Date: | 2002-11-16 17:01:02 |
| Message-ID: | Pine.LNX.4.44.0211161743400.8254-100000@sunrise2.radiostudiodelta.it |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-sql |
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | PostgreSQL Server | 2002-11-16 18:04:32 | INDEX PROBLEMS ? |
| Previous Message | Oleg Bartunov | 2002-11-16 16:58:55 | Re: Proposal of hierarchical queries, a la Oracle |