Re: SELECT ignoring index even though ORDER BY and LIMIT present

От: Matthew Wakeling
Тема: Re: SELECT ignoring index even though ORDER BY and LIMIT present
Дата: ,
Msg-id: alpine.DEB.2.00.1006031110330.4083@aragorn.flymine.org
(см: обсуждение, исходный текст)
Ответ на: SELECT ignoring index even though ORDER BY and LIMIT present  (Jori Jovanovich)
Список: pgsql-performance

Скрыть дерево обсуждения

SELECT ignoring index even though ORDER BY and LIMIT present  (Jori Jovanovich, )
 Re: SELECT ignoring index even though ORDER BY and LIMIT present  ("Kevin Grittner", )
  Re: SELECT ignoring index even though ORDER BY and LIMIT present  (Tom Lane, )
 Re: SELECT ignoring index even though ORDER BY and LIMIT present  (Szymon Guz, )
 Re: SELECT ignoring index even though ORDER BY and LIMIT present  (Bob Lunney, )
  Re: SELECT ignoring index even though ORDER BY and LIMIT present  (Jori Jovanovich, )
 Re: SELECT ignoring index even though ORDER BY and LIMIT present  (Matthew Wakeling, )

On Wed, 2 Jun 2010, Jori Jovanovich wrote:
> (2) Making the query faster by making the string match LESS specific (odd,
> seems like it should be MORE)

No, that's the way round it should be. The LIMIT changes it all. Consider
if you have a huge table, and half of the entries match your WHERE clause.
To fetch the ORDER BY ... LIMIT 20 using an index scan would involve
accessing only on average 40 entries from the table referenced by the
index. Therefore, the index is quick. However, consider a huge table that
only has twenty matching entries. The index scan would need to touch every
single row in the table to return the matching rows, so a sequential scan,
filter, and sort would be much faster. Of course, if you had an index
capable of answering the WHERE clause, that would be even better for that
case.

Matthew

--
 Don't criticise a man until you have walked a mile in his shoes; and if
 you do at least he will be a mile behind you and bare footed.


В списке pgsql-performance по дате сообщения:

От: Jori Jovanovich
Дата:
Сообщение: Re: SELECT ignoring index even though ORDER BY and LIMIT present
От: Greg Smith
Дата:
Сообщение: Re: Weird XFS WAL problem