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.
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 |