Slow query when using ORDER BY *and* LIMIT

Поиск
Список
Период
Сортировка
I have a query that uses ORDER BY and LIMIT to get a set of image data
rows that match a given tag.  When both ORDER BY and LIMIT are included
for some reason the planner chooses a very slow query plan.  Dropping
one or the other results in a much faster query going from 4+ seconds ->
30 ms.  Database schema, EXPLAIN ANALYZE and other information can be
found at http://pgsql.privatepaste.com/31113c27bf  Is there a way to
convince the planner to use the faster plan when doing both ORDER BY and
LIMIT without using SET options or will I need to disable the slow plan
options to force the planner to use the fast plan?

I found some stuff in the mailing list archives that looks related but I
didn't see any fixes.  Apparently the planner hopes the merge join will
find the LIMIT # of rows fairly quickly but instead it winds up scanning
almost the entire table.

Thanks,
Jonathan

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

Предыдущее
От: Greg Smith
Дата:
Сообщение: Re: bitmask index
Следующее
От: "Matthias Howell"
Дата:
Сообщение: Query in 9.0.2 not using index in 9.0.0 works fine