Не компенсируйте производительность вашего SQL-запроса
data programming postgres postgresql sql

Не компенсируйте производительность вашего SQL-запроса

5 апреля 2023 г.

Что такое СМЕЩЕНИЕ?

В SQL LIMIT и OFFSET позволяют получить только часть строк, сгенерированных остальной частью запроса. Это полезно, когда вам нужно отображать результаты страница за страницей в веб-приложении. Звучит неплохо, но есть серьезная проблема: по мере увеличения количества пропущенных строк время запроса становится медленнее.

Допустим, у нас есть таблица PostgreSQL с именем tasks со 100 миллион строк в нем.

Во-первых, попробуем получить 10-ю страницу из 10 задач, используя классический метод OFFSET:

SELECT * FROM tasks ORDER BY id LIMIT 10 OFFSET 10

Теперь взгляните на план запроса, который является результатом EXPLAIN <query>:

Limit  (cost=1.34..2.11 rows=10 width=628)
  ->  Index Scan using tasks_pkey on tasks
            (cost=0.57..5439151.65 rows=100000000 width=628)

Прежде чем двигаться дальше, давайте посмотрим, что говорится в документации PostgreSQL:

<цитата>
  • Ориентировочная начальная стоимость. Это время, затрачиваемое до начала фазы вывода, например, время для сортировки в узле сортировки.

  • Ориентировочная общая стоимость. Это указано в предположении, что узел плана выполняется до конца, т. е. извлекаются все доступные строки. На практике родительский узел узла может не прочитать все доступные строки.

  • Приблизительное количество строк, выводимых этим узлом плана. Опять же предполагается, что узел запущен до конца.

  • Расчетная средняя ширина строк, выводимых этим узлом плана (в байтах).

Используя эти знания, мы можем разбить план запроса:

  1. Ограничение: возвращает 10 строк. Стоимость исполнения оценивается между 1,34 и 2,11. Ширина результата составляет 628 байт для каждой строки.

2. Сканирование индекса. Используется индекс, что хорошо с точки зрения производительности. Стоимость оценивается между 0,57 и 5 439 151,65. Однако, поскольку у нас есть шаг Limit, в действительности он никогда не будет использовать все 10 миллионов строк, поэтому фактическая стоимость ближе к минимальной. Ширина такая же, как в шаге Limit.

В целом все не так уж и плохо. Однако не делайте поспешных выводов.

Попробуем получить миллионную страницу из 10 задач, используя тот же подход:

SELECT * FROM tasks ORDER BY id LIMIT 10 OFFSET 1000000

Limit  (cost=77270.24..77271.02 rows=10 width=628)
  ->  Index Scan using tasks_pkey on tasks
            (cost=0.57..5439162.17 rows=100000000 width=628)

Как видите, шаг Limit в этом случае стал намного хуже. Он стоит минимум 77 270,24, что немало, но сканирование индекса практически не изменилось. Поэтому, чем больше строк мы должны пропустить с помощью OFFSET, тем тяжелее становится запрос. Почему это так? Ответ находится в документации:

<цитата>

Строки, пропущенные предложением OFFSET, все равно должны быть вычислены внутри сервера; поэтому большое OFFSET может быть неэффективным.

Лучший подход

Альтернативный подход — использовать WHERE id > N вместо OFFSET:

SELECT * FROM tasks WHERE id > 1000000 ORDER BY id LIMIT 10

Limit  (cost=0.57..1.36 rows=10 width=628)
  ->  Index Scan using tasks_pkey on tasks
            (cost=0.57..5504715.15 rows=100000000 width=628)
        Index Cond: (id > 1000000)

Как видите, в этом случае у нас есть дополнительное условие индекса (id > 1 000 000), которое помогает нам отфильтровывать строки, не соответствующие критериям, при сканировании индекса. Это условие повышает производительность запроса за счет сужения области поиска.

Предупреждения

Основным недостатком этого подхода является необходимость сохранения последнего идентификатора на странице между запросами. Это требование может стать определяющим фактором при принятии решения не использовать этот подход.

Заключение

При реализации нумерации страниц в SQL избегайте использования OFFSET для больших наборов данных, так как это может замедлить выполнение запроса. Вместо этого используйте подход WHERE id > N для повышения производительности. Имейте в виду, что этот подход в некоторых случаях требует обновления вашей архитектуры. Выберите подход, который вам больше подходит.

Ссылки

  1. PostgreSQL: ПРЕДЕЛ и СМЕЩЕНИЕ
  2. PostgreSQL: использование EXPLAIN

Оригинал

Recent Post

Categories

PREVIOUS ARTICLE
NEXT ARTICLE