Optimize query on timestamp column in Postgresql 8.x

Question

Lets suppose you have and orders table, this table contains a timestamp column indicating the creation time of the orders. A normal query would be to obtain the orders between two dates. Does anybody know how to optimize this query because creating an index on the timestamp column has no effect as shown by EXPLAIN ANALYZE.

Please show us the query and the output of the explain analyze — user330315
– user330315, Commented Mar 2, 2011 at 17:57
Thats a lot of rep for one and a half year membership ;) +1 as a start :) — Daniel
– Daniel, Commented Mar 2, 2011 at 18:04

Daniel · Accepted Answer · 2011-03-02 18:05:40Z

2

Usually indexes are used, but only if the table is properly analyzed (VACUUM ANALYZE or just ANALYZE), and if the table size is large enough that index scans are faster than sequential scans.

answered Mar 2, 2011 at 18:05

Daniel

28.2k20 gold badges93 silver badges140 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

derobert · Accepted Answer · 2011-03-02 18:06:01Z

0

An index should work. I suspect its not working for you because your table is either tiny (PostgreSQL almost never uses indices for tiny tables), or you haven't done an analyze on it.

answered Mar 2, 2011 at 18:06

derobert

51.4k15 gold badges98 silver badges126 bronze badges

Collectives™ on Stack Overflow

Optimize query on timestamp column in Postgresql 8.x

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related