PostgreSQL ignoring index on timestamp column

Question

I have the following table and index created:

CREATE TABLE cdc_auth_user
(
  cdc_auth_user_id bigint NOT NULL DEFAULT nextval('cdc_auth_user_id_seq'::regclass),
  cdc_timestamp timestamp without time zone DEFAULT ('now'::text)::timestamp without time zone,
  cdc_operation text,
  id integer,
  username character varying(30)
);

CREATE INDEX idx_cdc_auth_user_cdc_timestamp
          ON cdc_auth_user
       USING btree (cdc_timestamp);

However, when I perform a select using the timestamp field, the index is being ignored and my query takes almost 10 seconds to return:

EXPLAIN SELECT *
          FROM cdc_auth_user
         WHERE cdc_timestamp BETWEEN '1900/02/24 12:12:34.818'
                             AND '2012/02/24 12:17:45.963';


Seq Scan on cdc_auth_user  (cost=0.00..1089.05 rows=30003 width=126)
  Filter: ((cdc_timestamp >= '1900-02-24 12:12:34.818'::timestamp without time zone) AND (cdc_timestamp <= '2012-02-24 12:17:45.963'::timestamp without time zone))

Answer 1

If there are a lot of results, the btree can be slower than just doing a table scan. btree indices are really not designed for this kind of "range-selection" kind of query you're doing here; the entries are placed in a big unsorted file and the index is built against that unsorted group, so every result potentially requires a disk seek after it is found in the btree. Sure, the btree can be easily read in order but the results still need to get pulled from the disk.

Clustered indices solve this problem by ordering the actual database records according to what's in the btree, so they actually are helpful for ranged queries like this. Consider using a clustered index instead and see how it works.