Re: tsearch2, large data and indexes

On Wed, Apr 23, 2014 at 4:08 AM, Ivan Voras <ivoras@freebsd.org> wrote:
> Ok, I found out what is happening, quoting from the documentation:
>
> "GIN indexes are not lossy for standard queries, but their performance
> depends logarithmically on the number of unique words. (However, GIN
> indexes store only the words (lexemes) oftsvector values, and not
> their weight labels. Thus a table row recheck is needed when using a
> query that involves weights.)"
>
> My query doesn't have weights but the tsvector in the table has them -
> I take it this is what is meant by "involves weights."
>
> So... there's really no way for tsearch2 to produce results based on
> the index alone, without recheck? This is... limiting.

My guess is that you could use strip() function [1] to get rid of
weights in your table or, that would probably be better, in your index
only by using expressions in it and in the query, eg.

...USING gin (strip(fts_data))

and

... WHERE strip(fts_data) @@ q

[1] http://www.postgresql.org/docs/9.3/static/textsearch-features.html

--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray.ru@gmail.com