Very simple select, using index for ordering, but not for selecting. How to make it faster?
Hi,
I have the following simple query on a simple table:
system=# select * from history where lookup = 'trunk' and lookupid = '248' order by created desc limit 1000;
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 * from history where lookup = 'trunk' and lookupid = '248' order by created desc limit 1000; QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..14799.28 rows=1000 width=58)
-> Index Scan Backward using history_created_index on history (cost=0.00..12201987.90 rows=824499 width=58)
Filter: ((lookup = 'trunk'::text) AND (lookupid = 248))
(3 rows)
Why doesn't it use the index specified in select? How can I change the query to make it faster?
Thanks,
Antonio
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 05/22/2013 02:38 PM, Antonio Gom�z Soto wrote:
Limit (cost=0.00..14799.28 rows=1000 width=58) -> Index Scan
Backward using history_created_index on history
(cost=0.00..12201987.90 rows=824499 width=58) Filter: ((lookup =
'trunk'::text) AND (lookupid = 248))
It's not using history_lookup_lookupid_creator_index, or even
history_lookup_lookupid_index, because it thinks, rightly or wrongly,
that it can get 1000 rows by reading history_creator_index backwards and
filtering out rows that don't match your where clause.
Since in this case, ordering is the most beneficial piece, it can't use
history_lookup_lookupid_creator_index to do this because creator is the
third column in the index. If you redefine that index to this instead:
CREATE INDEX history_lookup_lookupid_creator_index
ON public.history (creator, lookup, lookupid);
You *should* get a much faster result. That would also allow you to drop
history_creator_index. Since history_lookup_lookupid_index covers the
same first two columns, you shouldn't lose anything in queries that work
better with those in the front.
--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-676-8870
sthomas@optionshouse.com
______________________________________________
See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general