Re: order by slowing down a query by 80 times

От: Tom Lane
Тема: Re: order by slowing down a query by 80 times
Дата: ,
Msg-id: 26902.1277734649@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Re: order by slowing down a query by 80 times  ("Kevin Grittner")
Ответы: 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)
Список: 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, )

"Kevin Grittner" <> writes:
> 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.

I'm guessing that most of the cost is in repeated evaluations of the
filter clause
    (co_name_vec)::tsvector @@ to_tsquery('manufacturer'::text)

There are two extremely expensive functions involved there (cast to
tsvector and to_tsquery) and they're being done over again at what
I suspect is practically every table row.  The unordered query is
so much faster because it stops after having evaluated the text
search clause just a few times.

The way to make this go faster is to set up the actually recommended
infrastructure for full text search, namely create an index on
(co_name_vec)::tsvector (either directly or using an auxiliary tsvector
column).  If you don't want to maintain such an index, fine, but don't
expect full text search queries to be quick.

            regards, tom lane


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

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