Thread: Very simple select, using index for ordering, but not for selecting. How to make it faster?
Very simple select, using index for ordering, but not for selecting. How to make it faster?
From
Antonio Goméz Soto
Date:
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
Re: Very simple select, using index for ordering, but not for selecting. How to make it faster?
From
Shaun Thomas
Date:
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