Order-by and indexes

From: Odd Hogstad <odd(dot)hogstad(at)smartm(dot)no>
To: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: Order-by and indexes
Date: 2011-06-29 13:48:56
Message-ID: BANLkTikP1Y9s6V2N7Pt2EqGYNmO8RfgZgw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

I need to get the latest entry of a large table matching a certain criteria.
This is my query:

SELECT * FROM "data" WHERE "data"."fk" = 238496 ORDER BY "data"."id" DESC
LIMIT 1

This query is quite slow. If I do a explain on it, it seems that it uses an
Index Scan Backward.

If I omit the order by on the query:

SELECT * FROM "data" WHERE "data"."fk" = 238496 LIMIT 1

It is very fast. And the explain says that it uses Index scan. This is also
very fast if there aren't any matches. But I've read that I'm not guaranteed
to get the correct match If I do not use a order by, postgres just returns
its fastest possible match. Is this right? But will not the fastest possible
match always be the first match in the index? Is there another way to make
the order by query go faster?

Thanks!

Odd-R.

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Mike Thomsen 2011-06-29 13:54:28 Locking out a user after several failed login attempts
Previous Message Michael Wood 2011-06-29 10:24:05 Re: psql copy paste failure