pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

DATE TIME INDEX ?
2+ messages / 2 participants
[nested] [flat]

* DATE TIME INDEX ?
@ 2002-11-16 17:01  PostgreSQL Server <postgres@rsd.it>
  0 siblings, 1 reply; 2+ messages in thread

From: PostgreSQL Server @ 2002-11-16 17:01 UTC (permalink / raw)
  To: 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





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: DATE TIME INDEX ?
@ 2002-11-16 18:42  Tom Lane <tgl@sss.pgh.pa.us>
  parent: PostgreSQL Server <postgres@rsd.it>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2002-11-16 18:42 UTC (permalink / raw)
  To: PostgreSQL Server <postgres@rsd.it>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2002-11-16 18:42 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-11-16 17:01 DATE TIME INDEX ? PostgreSQL Server <postgres@rsd.it>
2002-11-16 18:42 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox