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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Some info:
PostgreSQL version: 9.1.2

Table "cache":
Rows count: 3 471 081
Column "tsv" tsvector
Index "cache_tsv" USING gin (tsv)

If i do query like THIS:

SELECT id FROM table WHERE tsv @@ to_tsquery('test:*');
It uses index and returns results immediately:

explain analyze 
'Bitmap Heap Scan on cache  (cost=1441.78..63802.63 rows=19843 width=4) (actual time=29.309..31.518 rows=1358 loops=1)'
'  Recheck Cond: (tsv @@ to_tsquery('test:*'::text))'
'  ->  Bitmap Index Scan on cache_tsv  (cost=0.00..1436.82 rows=19843 width=0) (actual time=28.966..28.966 rows=1559 loops=1)'
'        Index Cond: (tsv @@ to_tsquery('test:*'::text))'
'Total runtime: 31.789 ms'


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. 

explain analyze 
'Limit  (cost=0.00..356.23 rows=20 width=4) (actual time=7.984..765.550 rows=20 loops=1)'
'  ->  Seq Scan on cache  (cost=0.00..353429.50 rows=19843 width=4) (actual time=7.982..765.536 rows=20 loops=1)'
'        Filter: (tsv @@ to_tsquery('test:*'::text))'
'Total runtime: 765.620 ms'


Some more debug notes:
1) If i set SET enable_seqscan=off; then query uses indexes correctly
2) Also i notified, if i use: to_tsquery('test') without wildcard search :*, then index is used correctly in both queries, with or without LIMIT

Any ideas how to fix the problem?
Thank you

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux