Предыдущая работа по теме пагинации PostgreSQL
Пагинация в PostgreSQL: ROW_NUMBER убивает производительность / Хабр
Предисловие
Для высоконагруженных систем выбор оптимального метода пагинации становится критически важным для производительности приложений. Данное исследование представляет собой сравнительный анализ трех основных подходов к пагинации в PostgreSQL при работе с таблицей в 15+ миллионов записей. Результаты не просто демонстрируют количественные различия в скорости выполнения запросов, но и раскрывают фундаментальные различия в использовании системных ресурсов, что позволяет принимать архитектурные решения на основе данных, а не предположений.
Задача
Сравнить методы пагинации получения случайной страницы размером 100 строк для SQL-запроса, позволяющего получить информацию для анализа о проданных билетах из таблицы >15M строк.
Методология исследования
Тестовая среда и инструменты:
СУБД: PostgreSQL 17
Тестовая база данных: "Демобаза 2.0" (большой размер, сложная схема)
Условия тестирования: параллельная нагрузка, запрос к большой таблице.
Нагрузка на СУБД
Исследованные методы пагинации
OFFSET+LIMIT
SELECT ticket_no, book_ref, passenger_name
FROM bookings.tickets
ORDER BY ticket_no
OFFSET (FLOOR(RANDOM() * (15575748 - 100))) LIMIT 100;
Limit (cost=46131.57..46134.53 rows=100 width=34) (actual time=8450.971..8451.044 rows=100 loops=1)
-> Index Scan using tickets_pkey on tickets (cost=0.56..461310.62 rows=15575748 width=34) (actual time=0.051..7687.347 rows=14109362 loops=1)
Planning Time: 1.739 ms
Execution Time: 8451.195 ms
(4 rows)
DEFFERED JOIN
SELECT t.ticket_no, t.book_ref, t.passenger_name
FROM bookings.tickets t
INNER JOIN (
SELECT ticket_no
FROM bookings.tickets
ORDER BY ticket_no
OFFSET (FLOOR(RANDOM() * (15575748 - 100))) LIMIT 100
) AS sub ON t.ticket_no = sub.ticket_no
ORDER BY t.ticket_no;
Nested Loop (cost=30127.01..30406.13 rows=100 width=34) (actual time=1727.260..1727.952 rows=100 loops=1)
-> Limit (cost=30126.45..30128.38 rows=100 width=14) (actual time=1727.170..1727.198 rows=100 loops=1)
-> Index Only Scan using tickets_pkey on tickets (cost=0.56..301259.40 rows=15575748 width=14) (actual time=0.044..1397.473 rows=7118209 loops=1)
Heap Fetches: 0
-> Index Scan using tickets_pkey on tickets t (cost=0.56..2.78 rows=1 width=34) (actual time=0.007..0.007 rows=1 loops=100)
Index Cond: (ticket_no = tickets.ticket_no)
Planning Time: 2.423 ms
Execution Time: 1728.081 ms
(8 rows)KEYSET
WITH cursor_value AS (
SELECT ticket_no as cursor_ticket
FROM bookings.tickets
ORDER BY ticket_no
OFFSET (FLOOR(RANDOM() * (15575748 - 100))) LIMIT 1
)
SELECT ticket_no, book_ref, passenger_name
FROM bookings.tickets
CROSS JOIN cursor_value
WHERE ticket_no > cursor_ticket
ORDER BY ticket_no
LIMIT 100;
Limit (cost=30127.03..30142.66 rows=100 width=34) (actual time=8206.724..8206.867 rows=100 loops=1)
CTE cursor_value
-> Limit (cost=30126.45..30126.47 rows=1 width=14) (actual time=1432.098..1432.101 rows=1 loops=1)
-> Index Only Scan using tickets_pkey on tickets tickets_1 (cost=0.56..301259.40 rows=15575748 width=14) (actual time=0.042..1137.944 rows=6284350 loops=1)
Heap Fetches: 0
-> Nested Loop (cost=0.56..811764.96 rows=5191916 width=34) (actual time=8206.722..8206.851 rows=100 loops=1)
Join Filter: (tickets.ticket_no > cursor_value.cursor_ticket)
Rows Removed by Join Filter: 6284350
-> Index Scan using tickets_pkey on tickets (cost=0.56..461310.62 rows=15575748 width=34) (actual time=0.037..3083.611 rows=6284450 loops=1)
-> CTE Scan on cursor_value (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=6284450)
Planning Time: 0.261 ms
Execution Time: 8206.951 ms
(12 rows)Метод ROW_NUMBER - исключён из тестов, по результатам предыдущих экспериментов.
Производительность СУБД в ходе нагрузочного тестирования
Результат
Среднее превышение производительности , при использовании метода "Deffered Join" :
По сравнению с методом "Offset + Limit" : 263.08%
По сравнению с методом "Keyset" : 644.13%
Характерные особенности тестовых запросов и планов выполнения
1. Прямой OFFSET-LIMIT
Основные характеристики:
Использует простой OFFSET с LIMIT для получения случайных 100 строк
Выполняет полное сканирование индекса по первичному ключу
Проблемы производительности:
OFFSET требует просмотра всех пропускаемых строк
Каждая строка читается из таблицы (Index Scan), а не только из индекса
Большие накладные расходы на чтение и отбрасывание миллионов строк
2. Deferred Join
Основные характеристики:
Использует двухэтапный подход: сначала выбирает ключи, затем данные
Преимущества:
Вложенный запрос использует Index Only Scan - читает только индекс, без обращения к таблице
Внешний запрос делает точечные выборки по индексу (Index Scan)
Меньше чтений с диска благодаря работе с индексом на первом этапе
3. Keyset-пагинация
Основные характеристики:
Использует курсорный подход с условием WHERE
Проблемы производительности:
Хотя используется CTE и индексное сканирование, возникает проблема с Join Filter
Rows Removed by Join Filter: 6,284,350 - фильтр отбрасывает миллионы строк
Nested Loop выполняется 6.2 млн раз (один раз для каждой строки)
Ключевые выводы:
Deferred Join показал лучшую производительность благодаря:
Разделению запроса на два этапа
Использованию Index Only Scan для первоначальной фильтрации
Минимизации обращений к табличной части данныхОсновная проблема OFFSET - необходимость материализации и пропуска всех предыдущих строк
Keyset-пагинация в данном варианте реализации неэффективна из-за:
Неоптимального использования условия WHERE
Огромного количества отбрасываемых строк через Join Filter
Рекомендация:
Для выборки случайных строк из большой таблицы подход Deferred Join является наиболее оптимальным, так как минимизирует I/O операции и эффективно использует индексы.
Характерные особенности производительности СУБД
Общая картина производительности:
Deferred Join (cluster-2) - Лучшая производительность
Начальная скорость: 580-588
Пиковая скорость: 920-922
Средняя стабильная скорость: 918-922OFFSET-LIMIT (cluster-1) - Средняя производительность
Начальная скорость: 160-164
Пиковая скорость: 254-258
Средняя стабильная скорость: 254-256Keyset (cluster-3) - Наихудшая производительность
Начальная скорость: 78-82
Пиковая скорость: 120-124
Средняя стабильная скорость: 122-124
Ключевые особенности по метрикам:
1. WAITINGS (общее количество ожиданий):
Keyset: Высокие ожидания в начале (28), затем снижаются до 2-3
Deferred Join: Умеренные ожидания в начале (3-5), затем снижаются до 1-3
OFFSET-LIMIT: Минимальные ожидания (0-4), но с более низкой производительностью
2. IO (ожидания ввода-вывода):
Keyset и Deferred Join: Имеют IO ожидания только в начальной фазе (9 и 3 соответственно), затем падают до 0
Это указывает на кэширование данных - после начальной загрузки в память IO ожидания исчезают
3. IPC (межпроцессное взаимодействие):
Только Keyset показывает IPC ожидания (19 в начале), что объясняется сложной структурой запроса с CTE и соединениями
После прогрева IPC ожидания исчезают
4. LWLOCK (легковесные блокировки):
Все подходы показывают LWLOCK ожидания после прогрева
Keyset: 2-3 ожидания
Deferred Join: 1-3 ожидания
OFFSET-LIMIT: 2-4 ожидания
Фазы выполнения:
Фаза прогрева (первые ~30 измерений):
Deferred Join: Быстрый рост от 580 до 600
OFFSET-LIMIT: Рост от 160 до 170
Keyset: Медленный рост от 78 до 82
Фаза стабилизации (измерения 40-110):
Deferred Join: Стабильно высокие значения 900-922
OFFSET-LIMIT: Стабильно средние значения 254-258
Keyset: Стабильно низкие значения 122-124
Принципиальные выводы:
Deferred Join быстрее в 3.6 раза, чем OFFSET-LIMIT, и в 7.4 раза, чем Keyset
Наличие IO ожиданий только в начале у Deferred Join и Keyset подтверждает эффективное использование кэша после прогрева
Высокие IPC ожидания у Keyset объясняют его худшую производительность - дополнительные накладные расходы на управление курсорами и CTE
Несмотря на минимальные waitings, OFFSET-LIMIT показывает худшую производительность, чем Deferred Join - проблема в алгоритмической сложности OFFSET, а не в системных ожиданиях
Deferred Join демонстрирует лучшую масштабируемость - его производительность продолжает расти дольше и достигает более высоких значений
Рекомендации:
Для production-систем с высокой нагрузкой Deferred Join - оптимальный выбор
OFFSET-LIMIT следует избегать для больших смещений
Keyset-пагинация в данной реализации неэффективна и требует пересмотра
Все подходы выигрывают от кэширования, что важно учитывать при проектировании
Метрики производительности инфраструктуры
Все три теста показывают схожую динамику:
Первая фаза (измерения 1-50): умеренная нагрузка, CPU idle ~36%
Переходная фаза (измерения 48-53): резкий рост нагрузки
Вторая фаза (измерения 54-110): полная загрузка CPU (99% user time)
Ключевые различия по метрикам:
1. procs_r (процессы в состоянии выполнения):
OFFSET-LIMIT: 6 → 15 процессов (рост в 2.5 раза)
Deferred Join: 6 → 15 процессов (рост в 2.5 раза)
Keyset: 5 → 15 процессов (рост в 3 раза)
Вывод: Keyset создает более равномерную нагрузку на планировщик процессов.
2. Память (memory_swpd, memory_free, memory_cache):
Начальное состояние памяти:
OFFSET-LIMIT: 277МБ свободно, 6816МБ кэш
Deferred Join: 393МБ свободно, 6686МБ кэш (лучший старт)
Keyset: 168МБ свободно, 6855МБ кэш (худший старт)
Динамика использования памяти:
Deferred Join показывает наиболее агрессивное использование кэша (рост до 6794МБ)
OFFSET-LIMIT освобождает память в конце теста (рост free с 167 до 290МБ)
Keyset имеет стабильно низкий free memory (168-191МБ)
3. Ввод-вывод (io_bo - блоки out):
OFFSET-LIMIT: 54 → 60 (рост 11%)
Deferred Join: 53 → 52 (снижение 2%) - лучший показатель
Keyset: 48 → 58 (рост 21%) - наибольший рост
Вывод: Deferred Join создает минимальную нагрузку на диск.
4. Системные события (system_in - прерывания, system_cs - переключения контекста):
Прерывания:
OFFSET-LIMIT: 5721 → 8245 (рост 44%)
Deferred Join: 5672 → 8252 (рост 46%)
Keyset: 5704 → 8261 (рост 45%)
Переключения контекста:
OFFSET-LIMIT: 652 → 1008 (рост 55%)
Deferred Join: 632 → 1002 (рост 59%)
Keyset: 642 → 998 (рост 55%)
Вывод: Все подходы создают сравнимую системную нагрузку.
5. Использование CPU (cpu_us, cpu_sy, cpu_id):
Критическое различие - переход к полной загрузке:
OFFSET-LIMIT: Переход на 51-м измерении (8209 прерываний)
Deferred Join: Переход на 50-м измерении (8196 прерываний)
Keyset: Переход на 53-м измерении (8189 прерываний)
Вывод: Deferred Join быстрее достигает максимальной производительности.
Стабильность в фазе полной нагрузки:
Все три подхода стабильно держат 99% user CPU time
1% system CPU time - нормальная работа ядра
0% idle - система полностью загружена
Принципиальные выводы:
Deferred Join демонстрирует лучшую эффективность использования ресурсов:
Наименьшая нагрузка на диск (io_bo)
Наиболее агрессивное кэширование
Быстрый выход на максимальную производительностьKeyset имеет проблемы с памятью:
Стабильно низкий free memory
Наибольший рост нагрузки на дискOFFSET-LIMIT показывает промежуточные результаты:
Умеренная нагрузка на все ресурсы
Способность освобождать память под конец тестаВсе подходы создают сопоставимую нагрузку на CPU и системные ресурсы после выхода на стабильный режим.
Рекомендации для продуктивной нагрузки:
Для систем с ограниченной памятью: Осторожно с Keyset-подходом
Для систем с медленным диском: Deferred Join - оптимальный выбор
Для систем с высокой параллельной нагрузкой: Все подходы создают сравнимую нагрузку на CPU
Для смешанной нагрузки: Deferred Join показывает лучший баланс ресурсов
Главный вывод:
Несмотря на схожую итоговую нагрузку на CPU, Deferred Join демонстрирует лучшую эффективность на уровне подсистем ввода-вывода и использования памяти.