Re: GIN improvements part2: fast scan

On Fri, Jan 24, 2014 at 6:48 AM, Tomas Vondra <tv(at)fuzzy(dot)cz> wrote:

> I plan to do more thorough testing over the weekend, but I'd like to
> make sure I understand what to expect. My understanding is that this
> patch should:
>
> - give the same results as the current code (e.g. the fulltext should
> not return different rows / change the ts_rank etc.)
>
> - improve the performance of fulltext queries
>
> Are there any obvious rules what queries will benefit most from this?
> The queries generated by the tool I'm using for testing are mostly of
> this form:
>
> SELECT id FROM messages
> WHERE body_tsvector @ plainto_tsquery('english', 'word1 word2 ...')
> ORDER BY ts_rank(...) DESC LIMIT :n;
>
> with varying number of words and LIMIT values. During the testing today
> I haven't noticed any obvious performance difference, but I haven't
> spent much time on that.
>

These patches optimizes only query with multiple WHERE clauses. For
instance:

SELECT id FROM messages
WHERE body_tsvector @ plainto_tsquery('english', 'word1')
AND body_tsvector @ plainto_tsquery('english', 'word2')
ORDER BY ts_rank(...) DESC LIMIT :n;

Optimizations inside single clause will be provided as separate patch.

------
With best regards,
Alexander Korotkov.