Re: FTS performance issue - planner problem identified (but only partially resolved)

> 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

Hello,

A probable reason for the time difference is the cost for decompressing toasted content.
At lest in 8.3, the planner was not good at estimating it.

I'm getting better overall performances since I've stopped collect statistic on tsvectors.
An alternative would have been to disallow compression on them.

I'm aware this is a drastic way and would not recommend it without testing. The benefit may depend on the type of data you are indexing.
In our use case these are error logs with many java stack traces, hence with many lexemes poorly discriminative.

see: http://www.postgresql.org/message-id/27953.1329434125@sss.pgh.pa.us
as a comment on
http://www.postgresql.org/message-id/C4DAC901169B624F933534A26ED7DF310861B363@JENMAIL01.ad.intershop.net

regards,

Marc Mamin