Re: order by slowing down a query by 80 times

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

Rajesh Kumar Mallah <> writes:
> co_name_vec is actually the auxiliary tsvector column that is mantained via
> a
> an update trigger. and the index that you suggested is there .

Well, in that case it's just a costing/statistics issue.  The planner is
probably estimating there are more tsvector matches than there really
are, which causes it to think the in-order indexscan will terminate
earlier than it really will, so it goes for that instead of a full scan
and sort.  If this is 8.4 then increasing the statistics target for the
co_name_vec column should help that.  In previous versions I'm not sure
how much you can do about it other than raise random_page_cost, which is
likely to be a net loss overall.

            regards, tom lane


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

От: Bruce Momjian
Дата:
Сообщение: Re: PostgreSQL as a local in-memory cache
От: Robert Haas
Дата:
Сообщение: Re: PostgreSQL as a local in-memory cache