Re: Very simple select, using index for ordering, but not for selecting. How to make it faster?

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