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

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 specified
Date: 2012-01-10 11:30:53
Message-ID: CANxtv6VJ=Beygt13y2-0SB66WHrSFZAVXnH0zw=38Fae=EMUPQ@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

Browse pgsql-performance by date

  From Date Subject
Next Message darklow 2012-01-10 12:30:41 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
Previous Message antoine 2012-01-09 12:59:59 Re: Duplicate deletion optimizations