Re: FTS performance issue probably due to wrong planner estimate of detoasting - Mailing list pgsql-performance

From Stefan Keller
Subject Re: FTS performance issue probably due to wrong planner estimate of detoasting
Date
Msg-id CAFcOn28oH_-VPYZgRkEc-5+snCW80YXe8MOPE2KMXO9vjUbEDQ@mail.gmail.com
Whole thread Raw
In response to Re: FTS performance issue probably due to wrong planner estimate of detoasting  (Jesper Krogh <jesper@krogh.cc>)
List pgsql-performance
Hi Jesper and Pavel

Thx for your hints.
I'm rather reluctant in tuning with unwanted side effects, We'll see.
I have to setup my system and db again before I can try out your tricks.

Yours, Stefan

2013/2/8 Jesper Krogh <jesper@krogh.cc>:
> On 08/02/13 01:52, Stefan Keller wrote:
>>
>> 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.
>
> If you havent done it .. bump up statistics target on the column and
> re-analyze, see what that gives.
>
> I have also been playing with the cost-numbers in order to get it to favour
> an index-scan more often. That is lowering random_page_cost to be close to
> seq_page_cost, dependent on your system, the amount of memory, etc, then
> this can have negative side-effects on non-gin-queries.
>
> --
> Jesper


pgsql-performance by date:

Previous
From: Karolis Pocius
Date:
Subject: Slow query even with aggressive auto analyze
Next
From: Jon Nelson
Date:
Subject: temp tablespaces and SSDs, etc..