DATE TIME INDEX ?

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

Responses

Browse pgsql-sql by date

  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