From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Stefan Keller <sfkeller(at)gmail(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: FTS performance issue probably due to wrong planner estimate of detoasting |
Date: | 2013-02-08 05:45:57 |
Message-ID: | CAFj8pRCanxTsX6-bLCwqr7SEi6zYC9BARFBUhMBNx+pSyFEdWg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Hello
you can try to wrap searching to immutable function and use following trick
http://postgres.cz/wiki/PostgreSQL_SQL_Tricks#Using_IMMUTABLE_functions_as_hints_for_the_optimizer
Regards
Pavel Stehule
2013/2/8 Stefan Keller <sfkeller(at)gmail(dot)com>:
> 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
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
From | Date | Subject | |
---|---|---|---|
Next Message | Jesper Krogh | 2013-02-08 06:56:03 | Re: FTS performance issue probably due to wrong planner estimate of detoasting |
Previous Message | Stefan Keller | 2013-02-08 00:52:46 | FTS performance issue probably due to wrong planner estimate of detoasting |