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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
