why the similar query takes so difference time in tsearch2?

Поиск
Список
Период
Сортировка
От Miao Jiang
Тема why the similar query takes so difference time in tsearch2?
Дата
Msg-id h1vf28$2nb$1@ger.gmane.org
обсуждение исходный текст
Список 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

В списке pgsql-general по дате отправления:

Предыдущее
От: "Chris Barnes"
Дата:
Сообщение: Postgres online backup and restore
Следующее
От: Frank Heikens
Дата:
Сообщение: Re: create a table inside a function