CREATE TABLE ticket
(
wid bigint NOT NULL DEFAULT nextval('tickets_id_seq'::regclass),
eid bigint,
created timestamp with time zone NOT NULL DEFAULT now(),
status integer NOT NULL DEFAULT 0,
argsxml text,
moduleid character varying(255),
source_id bigint,
file_type_id bigint,
file_name character varying(255),
status_reason character varying(255),
...
)
I created an index on the 'created' timestamp as fallows:
CREATE INDEX ticket_1_idx
ON ticket
USING btree
(created );
and here's my query
select * from ticket
where created between '2012-12-19 00:00:00' and '2012-12-20 00:00:00'
This was working fine until the number of records started to grow (about 5 million) and now it's taking forever to return.
Explain analyze reveals this:
"Index Scan using ticket_1_idx on ticket (cost=0.00..10202.64 rows=52543 width=1297) (actual time=0.109..125.704 rows=53340 loops=1)"
" Index Cond: ((created >= '2012-12-19 00:00:00+00'::timestamp with time zone) AND (created <= '2012-12-20 00:00:00+00'::timestamp with time zone))"
"Total runtime: 175.853 ms"
So far I've tried setting
random_page_cost = 1.75
effective_cache_size = 3
Also created
create CLUSTER ticket USING ticket_1_idx;
Nothing works. What am I doing wrong? why is it selecting sequential scan? the indexes are supposed to make the query fast. Anything that can be done to optimize it?
Help is appreciated! Thx.