Order-by and indexes

Поиск
Список
Период
Сортировка
От Odd Hogstad
Тема Order-by and indexes
Дата
Msg-id BANLkTikP1Y9s6V2N7Pt2EqGYNmO8RfgZgw@mail.gmail.com
обсуждение исходный текст
Ответы Re: Order-by and indexes  (James David Smith <james.david.smith@gmail.com>)
Re: Order-by and indexes  ("Jean-Yves F. Barbier" <12ukwn@gmail.com>)
Re: Order-by and indexes  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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.


В списке pgsql-novice по дате отправления:

Предыдущее
От: "ktm@rice.edu"
Дата:
Сообщение: Re: Locking out a user after several failed login attempts
Следующее
От: "Jean-Yves F. Barbier"
Дата:
Сообщение: Re: Locking out a user after several failed login attempts