On 2012-01-10 18:04, Tom Lane wrote:
darklow<darklow@xxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
