From: | Antonio Goméz Soto <antonio(dot)gomez(dot)soto(at)gmail(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Slow query and using wrong index, how to fix? Probably naive question.. |
Date: | 2013-05-22 08:50:13 |
Message-ID: | 519C86C5.60203@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
I am using postgresql 8.1 (CentOS5). I have the following table:
system # \d history
Table "public.history"
Column | Type | Modifiers
----------+--------------------------+------------------------------------------------------
id | integer | not null default nextval('history_id_seq'::regclass)
created | timestamp with time zone |
creator | integer | not null default 1
contact | integer | not null default 1
type | character varying | not null default ''::character varying
lookup | text |
lookupid | integer | not null default 1
value | text |
Indexes:
"history_pkey" PRIMARY KEY, btree (id)
"history_created_index" btree (created)
"history_creator_index" btree (creator)
"history_lookup_lookupid_creator_index" btree (lookup, lookupid, creator)
"history_lookup_lookupid_index" btree (lookup, lookupid)
Foreign-key constraints:
"history_contact_constraint" FOREIGN KEY (contact) REFERENCES contact(id)
"history_creator_constraint" FOREIGN KEY (creator) REFERENCES contact(id)
system # explain select history.id, history.created, creator, contact, history.type, lookup, lookupid, value from history where (lookup = 'phone' and lookupid = '672') or creator = '790' order by history.creator desc limit 1000;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..132041.59 rows=1000 width=58)
-> Index Scan Backward using history_creator_index on history (cost=0.00..11746815.97 rows=88963 width=58)
Filter: (((lookup = 'phone'::text) AND (lookupid = 672)) OR (creator = 790))
(3 rows)
This table contains 2 million rows, the query takes 800 seconds on SSD HD.
I think - probably naive - the query should use the history_lookup_lookupid_creator_index.
Why doesn't it, and how can I speed up the query?
Thanks,
Antonio.
From | Date | Subject | |
---|---|---|---|
Next Message | Albe Laurenz | 2013-05-22 09:23:19 | Re: Slow query and using wrong index, how to fix? Probably naive question.. |
Previous Message | Raghavendra | 2013-05-22 07:39:21 | Re: Table Partitioning |