FTS performance issue probably due to wrong planner estimate of detoasting

From: Stefan Keller <sfkeller(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: FTS performance issue probably due to wrong planner estimate of detoasting
Date: 2013-02-08 00:52:46
Message-ID: CAFcOn29nT_u0PKV-3hsY=Z9iOpy8-vx5XdcQm2iCSsaJvZEZKQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Pavel Stehule 2013-02-08 05:45:57 Re: FTS performance issue probably due to wrong planner estimate of detoasting
Previous Message Josh Krupka 2013-02-07 17:49:36 Re: postgresql.conf recommendations