Re: FTS performance issue probably due to wrong planner estimate of detoasting

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(at)krogh(dot)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