From: | Stefan Keller <sfkeller(at)gmail(dot)com> |
---|---|
To: | Jesper Krogh <jesper(at)krogh(dot)cc> |
Cc: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: FTS performance issue probably due to wrong planner estimate of detoasting |
Date: | 2013-02-08 17:30:06 |
Message-ID: | CAFcOn28oH_-VPYZgRkEc-5+snCW80YXe8MOPE2KMXO9vjUbEDQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(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
From | Date | Subject | |
---|---|---|---|
Next Message | Jon Nelson | 2013-02-08 20:52:37 | temp tablespaces and SSDs, etc.. |
Previous Message | Karolis Pocius | 2013-02-08 12:36:43 | Slow query even with aggressive auto analyze |