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: | Raw Message | Whole Thread | 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 |