FTS performance issue probably due to wrong planner estimate of detoasting

Поиск
Список
Период
Сортировка
От Stefan Keller
Тема FTS performance issue probably due to wrong planner estimate of detoasting
Дата
Msg-id CAFcOn29nT_u0PKV-3hsY=Z9iOpy8-vx5XdcQm2iCSsaJvZEZKQ@mail.gmail.com
обсуждение исходный текст
Ответы Re: FTS performance issue probably due to wrong planner estimate of detoasting  (Pavel Stehule <pavel.stehule@gmail.com>)
Re: FTS performance issue probably due to wrong planner estimate of detoasting  (Jesper Krogh <jesper@krogh.cc>)
Список pgsql-performance
Hi,

I have problems with the performance of FTS in a query like this:

  SELECT * FROM FullTextSearch WHERE content_tsv_gin @@
plainto_tsquery('english', 'good');

It's slow (> 30 sec.) for some GB (27886 html files, originally 73 MB zipped).
The planner obviously always chooses table scan: http://explain.depesz.com/s/EEE
I have to check again, if I'm doing something wrong but I'm pretty
sure it has to do with de-toasting and (wrong?) cost estimations.

I've seen some comments here saying that estimating detoasting costs
(especially with operator "@@" and GIN index) is an open issue (since
years?).
And I found a nice blog here [1] which uses 9.2/9.1 and proposes to
disable sequential table scan (SET enable_seqscan off;). But this is
no option for me since other queries still need seqscan.
Can anyone tell me if is on some agenda here (e.g. as an open item for >9.2)?

Yours, Stefan

[1] http://palominodb.com/blog/2012/03/06/considerations-about-text-searchs-big-fields-and-planner-costs


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

Предыдущее
От: Josh Krupka
Дата:
Сообщение: Re: postgresql.conf recommendations
Следующее
От: Pavel Stehule
Дата:
Сообщение: Re: FTS performance issue probably due to wrong planner estimate of detoasting