September 7, 2008

Проблема использования индексов с 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

No comments:

Post a Comment