From: | Jose Martinez <jmartinez(at)opencrowd(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org, pgsql-admin(at)postgresql(dot)org |
Subject: | Simple Query Very Slow |
Date: | 2012-12-21 21:23:36 |
Message-ID: | CA+ixmX37YK+RZcLab_xXKywojFG8sXJFpHD3GWGZqNoo8yRrVA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin pgsql-general |
Hello,
I have the following table and indices defined:
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Hariraman Jayaraj | 2012-12-22 07:35:03 | Postgres 9.1.6 Startup Warning |
Previous Message | ynux | 2012-12-21 13:12:06 | Re: allow a user to see current_query in pg_stat_activity in 8.4 |
From | Date | Subject | |
---|---|---|---|
Next Message | Chris Angelico | 2012-12-21 22:22:59 | Re: Coalesce bug ? |
Previous Message | Bryan Lee Nuse | 2012-12-21 18:49:10 | UNION and pg_restore |