why the similar query takes so difference time in tsearch2?

From: Miao Jiang <jiangfriend(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: why the similar query takes so difference time in tsearch2?
Date: 2009-06-25 09:12:39
Message-ID: h1vf28$2nb$1@ger.gmane.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

miao=> \d items
Table "public.items"
Column | Type | Modifiers

--------+------------------+----------------------------------------------------
id | integer | not null default
nextval('items_id_seq'::regclass)
tags | tsvector | not null
score | double precision | not null default random()
Indexes:
"items_pkey" PRIMARY KEY, btree (id)
"items_tags_idx" gin (tags)

-- table items have 1310000 rows.

miao=> explain select id from items where tags @@ to_tsquery('1') limit 151;
QUERY PLAN

----------------------------------------------------------------------------------------
Limit (cost=0.00..604.92 rows=151 width=4)
-> Index Scan using items_tags_idx on items (cost=0.00..5187.83
rows=1295 width=4)
Index Cond: (tags @@ to_tsquery('1'::text))
(3 rows)

Time: 0.775 ms
miao=> explain select id from items where tags @@ to_tsquery('1') limit 152;
QUERY PLAN

----------------------------------------------------------------------------------------
Limit (cost=103.21..608.85 rows=152 width=4)
-> Bitmap Heap Scan on items (cost=103.21..4411.17 rows=1295 width=4)
Recheck Cond: (tags @@ to_tsquery('1'::text))
-> Bitmap Index Scan on items_tags_idx (cost=0.00..102.88
rows=1295 width=0)
Index Cond: (tags @@ to_tsquery('1'::text))
(5 rows)

Time: 0.838 ms

miao=> select id from items where tags @@ to_tsquery('1') limit 151;
Time: 1.494 ms
miao=> select id from items where tags @@ to_tsquery('1') limit 152; --
Only 1 more than limit 151, but takes about 300 times time.
Time: 413.360 ms
miao=>

Why limit 152 takes so much long time than limit 151? How to improve
that limit 152 even limit 500 could as fast as limit 151.

Thanks
Miao

Browse pgsql-general by date

  From Date Subject
Next Message Henk van Lingen 2009-06-25 09:22:41 Re: Add Space symbols for TSvector
Previous Message Oleg Bartunov 2009-06-25 09:05:41 Re: Add Space symbols for TSvector