From: | darklow <darklow(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | 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: | 2012-01-10 12:30:41 |
Message-ID: | CANxtv6XiuiqEkXRJU2vk=xKAFXrLeP7uVhgR-XMCyjgQz29EFQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Mike Blackwell | 2012-01-10 16:57:04 | Partitioning by status? |
Previous Message | darklow | 2012-01-10 11:30:53 | Query planner doesn't use index scan on tsvector GIN index if LIMIT is specified |