Проблема использования индексов с OFFSET LIMIT

Недавно вёл обсуждение касающееся pagination для очень большого количества записей (кстати, кое-кто обещал мне показать реализацию ;) и пришел к такому-же выводу...

Перевод Indexing problem with OFFSET LIMIT с pgsql-general

Oliver Weichhold для pgsql-general

Привет

У меня проблема в моём приложении и я не знаю как её решить.

Вот таблица и один из индексов:

CREATE TABLE foo
(
  id serial NOT NULL,
  foo_name character varying(100),
  realm_id integer

  ... и ещё около 50 колонок
)

CREATE INDEX idx_foo_name_realm
  ON foo
  USING btree
  (realm_id, foo_name);

В таблице foo около 8 миллионов строк.

Сама проблема:

Рассмотрим запрос:

SELECT * FROM foo WHERE realm_id = 228 order by foo_name LIMIT 200 OFFSET 15000

И его план выполнения:

Limit  (cost=57527.13..58294.16 rows=200 width=575) (actual time=182.302..184.971 rows=200 loops=1)
  ->  Index Scan using idx_foo_name_realm on foo  (cost=0.00..62159.98 rows=16208 width=575) (actual time=0.085..166.861 rows=15200 loops=1)
        Index Cond: (realm_id = 228)
Total runtime: 185.591 ms

А теперь рассмотрим этот:

SELECT * FROM foo WHERE realm_id = 228 order by foo_name LIMIT 200 OFFSET 15999

Limit  (cost=59601.92..59602.42 rows=200 width=575) (actual time=1069.759..1072.310 rows=200 loops=1)
  ->  Sort  (cost=59561.92..59602.44 rows=16208 width=575) (actual time=929.948..1052.620 rows=16199 loops=1)
        Sort Key: foo_name
        Sort Method:  external merge  Disk: 8984kB
        ->  Bitmap Heap Scan on foo  (cost=306.69..54270.62 rows=16208 width=575) (actual time=9.612..235.902 rows=21788 loops=1)
              Recheck Cond: (realm_id = 228)
              ->  Bitmap Index Scan on foo_realm_id  (cost=0.00..302.64 rows=16208 width=0) (actual time=8.733..8.733 rows=21810 loops=1)
                    Index Cond: (realm_id = 228)
Total runtime: 1084.706 ms

Время выполнения увеличилось в 10 раз из-за того, что postgres перестал использовать индексы.

Может кто-нибудь объяснить что происходит и как это обойти? Является ли это проблемой памяти?

Merlin Moncure для Oliver, pgsql-general

Oliver Weichhold:
> Hello
>
> I have problem in my applications and don't know how to fix it.
>
> ...

Попробуйте это:

SELECT * FROM foo WHERE realm_id = 228 order by realm_id, foo_name
LIMIT 200 OFFSET
 15000

Или, что даже лучше, не используйте 'offset' вообще. Это просто не потребно. Если вы хотите пропустить 200 строк разом, сделайте это отдельным запросом:

1 раз:

select * from foo order by realm_id, foo_name limit 200;

после этого:

select * from foo where (realm_id, foo_name) > (last_realm_id,
last_foo_name) order by realm_id, foo_name limit 200;

и вы будете приятно удивлены :-). Это также будет более правильно по отношению к другим сессиям, которые делают операции добавления/удаления.

merlin

Tom Lane для Merlin, Oliver, pgsql-general

Merlin Moncure:
> Oliver Weichhold:
>> Рассмотрим запрос:
>>
>> SELECT * FROM foo WHERE realm_id = 228 order by foo_name LIMIT 200 OFFSET
>> 15000
> Попробуйте это:
>
> SELECT * FROM foo WHERE realm_id = 228 order by realm_id, foo_name
> LIMIT 200 OFFSET
> 15000
>
> Или, что даже лучше, не используйте 'offset' вообще. Это просто
> не потребно. Если вы хотите пропустить 200 строк разом, сделайте
> это отдельным запросом:

Да, большие значения OFFSET всегда вредны для производительности, т.к. пропуск этих строк конечно же не волшебство - бэкэнд должен прочесть их и потом откинуть. Избегайте OFFSET.

Отступив от темы хочу спросить - вы не рассматривали вариант с использованием курсоров?

regards, tom lane