From: | Jesper Krogh <jesper(at)krogh(dot)cc> |
---|---|
To: | Stefan Keller <sfkeller(at)gmail(dot)com>, "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 06:56:03 |
Message-ID: | 5114A183.3090502@krogh.cc |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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 | Karolis Pocius | 2013-02-08 12:36:43 | Slow query even with aggressive auto analyze |
Previous Message | Pavel Stehule | 2013-02-08 05:45:57 | Re: FTS performance issue probably due to wrong planner estimate of detoasting |