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
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 |