Re: Query planner doesn't use index scan on tsvector GIN index if LIMIT is specifiedQuery planner doesn't use index scan on tsvector GIN index if LIMIT is specified

On 2012-01-10 18:04, Tom Lane wrote:
> darklow<darklow(at)gmail(dot)com> writes:
>> But the performance problems starts when i do the same query specifying
>> LIMIT.
>> *SELECT id FROM cache WHERE tsv @@ to_tsquery('test:*') limit 20;*
>> By some reason index is not used.
> It apparently thinks there are enough matches that it might as well just
> seqscan the table and expect to find some matches at random, in less
> time than using the index would take.
>
> The estimate seems to be off quite a bit, so maybe raising the stats
> target for this column would help.
The cost of matching ts_match_vq against a toasted column
is not calculated correctly. This is completely parallel with
http://archives.postgresql.org/pgsql-hackers/2011-11/msg01754.php

Try raising the cost for ts_match_vq(tsvector,tsquery) that help a bit, but
its hard to get the cost high enough.

Raising statistics target helps too..

--
Jesper