Simple Query Very Slow

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.

Responses

Browse pgsql-admin by date

  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

Browse pgsql-general by date

  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