Hi Marc
Thanks a lot for your hint!
You mean doing a "SET track_counts (true);" for the whole session?
That would be ok if it would be possible just for the gin index.
It's obviously an issue of the planner estimation costs.
The data I'm speaking about ("movies") has a text attribute which has
a length of more than 8K so it's obviously having to do with
detoasting.
But the thoughts about @@ operators together with this GIN index seem
also to be valid.
I hope this issue is being tracked in preparation for 9.3.
Regards, Stefan
2013/7/19 Marc Mamin <M.Mamin@intershop.de>:
>
>> 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 least 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
datayou 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