Re: order by slowing down a query by 80 times

От: Kevin Grittner
Тема: Re: order by slowing down a query by 80 times
Дата: ,
Msg-id: 4C2862C90200002500032BCD@gw.wicourts.gov
(см: обсуждение, исходный текст)
Ответ на: order by slowing down a query by 80 times  (Rajesh Kumar Mallah)
Ответы: Re: order by slowing down a query by 80 times  (Tom Lane)
Список: pgsql-performance

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

order by slowing down a query by 80 times  (Rajesh Kumar Mallah, )
 Re: order by slowing down a query by 80 times  (Yeb Havinga, )
  Re: order by slowing down a query by 80 times  (Andres Freund, )
  Re: order by slowing down a query by 80 times  (Rajesh Kumar Mallah, )
 Re: order by slowing down a query by 80 times  ("Kevin Grittner", )
  Re: order by slowing down a query by 80 times  (Tom Lane, )
   Re: order by slowing down a query by 80 times  (Rajesh Kumar Mallah, )
   Re: order by slowing down a query by 80 times  (Rajesh Kumar Mallah, )
    Re: order by slowing down a query by 80 times  (Tom Lane, )

Rajesh Kumar Mallah <> wrote:

> just by removing the order by co_name reduces the query time
> dramatically from  ~ 9 sec  to 63 ms. Can anyone please help.

The reason is that one query allows it to return *any* 25 rows,
while the other query requires it to find a *specific* set of 25
rows.  It happens to be faster to just grab any old set of rows than
to find particular ones.

If the actual results you need are the ones sorted by name, then
forget the other query and focus on how you can retrieve the desired
results more quickly.  One valuable piece of information would be to
know how many rows the query would return without the limit.  It's
also possible that your costing factors may need adjustment.  Or you
may need to get finer-grained statistics -- the optimizer thought it
would save time to use an index in the sequence you wanted, but it
had to scan through 2212 rows to find 25 rows which matched the
selection criteria.  It might well have been faster to use a table
scan and sort than to follow the index like that.

-Kevin


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

От: Rajesh Kumar Mallah
Дата:
Сообщение: Re: order by slowing down a query by 80 times
От: Rajesh Kumar Mallah
Дата:
Сообщение: Re: order by slowing down a query by 80 times