🔢Пагинация "ROW_NUMBER"

SQL-пагинация: искусство делить данные

Методология исследования

Тестовая среда и инструменты:

• СУБД: PostgreSQL 17
• Инструмент нагрузочного тестирования: pg_expecto
• Тестовая база данных: "Демобаза 2.0" (большой размер, сложная схема)
• Условия тестирования: параллельная нагрузка, ресурсоемкий запрос

Нагрузка на СУБД

Пагинация с использованием ROW_NUMBER.

Тестовый запрос "ROW_NUMBER"

План выполнения тестового запроса "Пагинация ROW_NUMBER"

Сравнительный анализ планов выполнения запросов

❓Проведи сравнительный анализ и сформируй итог по планам выполнения тестовых запросов

Общие характеристики:

1. Оба запроса используют CTE random_period для выбора случайного 30-дневного периода из таблицы bookings
2. Основное различие:
3. JOIN: получает ВСЕ данные за период с сортировкой
4. ROW_NUMBER: использует пагинацию через ROW_NUMBER() (первые 1000 строк)

Анализ узких мест:

ROW_NUMBER:

1. Дополнительные затраты на оконную функцию ROW_NUMBER()
2. Несмотря на LIMIT 1000, сортирует ВСЕ данные (Run Condition)
3. Использует последовательное сканирование bookings в CTE (Seq Scan) вместо индекса

ROW_NUMBER демонстрирует антипаттерн пагинации - сортировка всех данных перед взятием первых N строк. Для реальной пагинации следует использовать подход с LIMIT и условиями по ключам.

Сравнительный анализ производительности и ожиданий СУБД в ходе нагрузочного тестирования при использовании запроса "JOIN" и запроса "ROW_NUMBER"

Операционная скорость

График изменения операционной скорости в ходе нагрузочного тестирования при использоваении тестового запроса "JOIN" и "ROW_NUMBER"

График изменения относительной разницы операционной скорости для запроса "ROW_NUMBER" по сравнению с тестовым запросом "JOIN"

Результат: применение пагинации с использованием ROW_NUMBER не влияет на производительность СУБД.

Ожидания СУБД

График изменения ожиданий СУБД в ходе нагрузочного тестирования при использоваении тестового запроса "JOIN" и "ROW_NUMBER"

График изменения относительной разницы ожиданий СУБД для запроса "ROW_NUMBER" по сравнению с тестовым запросом "JOIN"

❓Проведи сравнительный анализ и сформируй итог по показаниям wait_event_type для тестовых запросов

Сравнительный анализ wait_event_type

1. Проблема ввода/вывода (IO):

• Оба запроса имеют сопоставимое абсолютное число IO-событий
• "JOIN" работает интенсивнее (больше событий в единицу времени)
• Указывает на дисковые операции сортировки, что согласуется с планами выполнения

2. Межпроцессное взаимодействие (IPC):

• "JOIN" имеет более высокую интенсивность IPC
• Может указывать на более активное использование параллельных процессов
• Возможно связано с хэш-соединениями и сортировкой

3. Влияние на производительность:

• "JOIN": Высокая интенсивность IO/ICP в финальной фазе соответствует резкому росту использования диска при сортировке
• "ROW_NUMBER": Более равномерное распределение событий соответствует продолжительной сортировке с пагинацией

Корреляция с планами выполнения:

1. События IO соответствуют операциям:
2. External merge сортировка на диске
3. Чтение больших индексов
4. Запись временных данных
5. Резкий рост в конце "JOIN" объясняется:
6. Финальной сортировкой всех данных
7. Записью результата
8. Более плавный "ROW_NUMBER" связан:
9. С оконной функцией ROW_NUMBER()
10. Постепенной обработкой при пагинации

"ROW_NUMBER", несмотря на большее общее время выполнения, имеет более равномерную нагрузку и меньшую интенсивность событий ожидания. Однако это следствие неэффективной пагинации через ROW_NUMBER().

Сравнительный анализ метрик vmstat в ходе нагрузочного тестирования при использовании запроса "JOIN" и запроса "ROW_NUMBER"

❓Проведи сравнительный анализ и сформируй итог по показаниям vmstat для тестовых запросов в ходе нагрузочного тестирования

1. Использование процессора (CPU):

Метрика "ROW_NUMBER" Разница

Пользовательский режим (us) 38-58% (стабильный) Сопоставимо

Системный режим (sy) 4-5% Одинаково, норма

Ожидание ввода-вывода (wa) 9-15% (пик до 15%) на 13% ниже

Простой (id) 14-46% (менее выражено) меньше загружает систему

Вывод: "ROW_NUMBER" создает меньшую нагрузку на подсистему ввода-вывода (выше cpu_wa).

2. Ввод-вывод (I/O):

Метрика "ROW_NUMBER" Разница

Чтение с диска (bi) 55083-75857 на 15-20% менее интенсивно

Запись на диск (bo) 2318-4535 в 1.7 раза менее интенсивно

Вывод: "ROW_NUMBER" значительно менее активно работает с диском, особенно на запись.

3. Использование памяти:

Метрика "ROW_NUMBER" Разница

Swap used (swpd) 215(стабильно) Одинаково

Свободная память (free) 177-183 Одинаково, стабильно

Буферы (buff) 177-226 "ROW_NUMBER" использует буферы в 10 раз активнее

Кэш (cache) 6706-6906 "JOIN" использует больше кэша

Вывод: Разная стратегия использования памяти - "ROW_NUMBER" активно использует буферы, "JOIN" - системный кэш.

4. Активность процессов:

Метрика "ROW_NUMBER" Разница

Работающие процессы (r) 5-10(стабильный рост) Сопоставимо

Блокированные процессы (b) 1-2 (редко 2) Сопоставимо

Вывод: "ROW_NUMBER" создает меньше блокировок процессов.

5. Системные события:

Метрика "ROW_NUMBER" Разница

Прерывания (in) 10669-14185/сек на 5% ниже

Переключения контекста (cs) 11042-15271 на 8% выше

"ROW_NUMBER" имеет больше переключений контекста, что может указывать на более активную работу с памятью.

Ключевые инсайты:

1. Паттерны использования памяти:

• JOIN: Сначала активно использует буферы, затем переходит на кэш
• ROW_NUMBER: Постоянно активно использует буферы, возможно для оконных функций

2. Дисковые операции:

• JOIN: Больше операций записи (сортировка на диск)
• ROW_NUMBER: Более сбалансированное чтение/запись

3. Сравнение с планами выполнения:

• Высокий cpu_wa в обоих тестах подтверждает проблему с вводом-выводом
• Интенсивные дисковые операции соответствуют external merge сортировке
• Разница в использовании буферов объясняется разными алгоритмами обработки

ROW_NUMBER показывает более стабильный и предсказуемый профиль:

• Равномерная нагрузка на систему
• Стабильное использование буферов памяти
• Меньше блокировок процессов

Общий итог : Часть-6 "ROW_NUMBER"

ℹ️ Для условий высокой параллельной нагрузки и конкуренции за вычислительные ресурсы, с точки зрения производительности СУБД - использование пагинации с помощью ROW_NUMBER не оказывает заметного влияние на производительность СУБД .

❓Сформируй общий итог о сравнении производительности и показателей инфраструктуры в ходе нагрузочного тестирования.

Ключевые выводы по производительности запросов:

1. Временные характеристики:

• Пагинация через ROW_NUMBER() не дает выигрыша в скорости из-за полной сортировки данных

2. Эффективность пагинации:

• ROW_NUMBER демонстрирует антипаттерн - сортирует ВСЕ данные перед извлечением первых 1000 строк
• Run Condition не предотвращает полную сортировку
• Для реальной пагинации рекомендуется использовать LIMIT/OFFSET или keyset-пагинацию

Анализ использования ресурсов инфраструктуры:

1. Загрузка процессора:

• JOIN создает более высокую нагрузку на подсистему ввода-вывода (cpu_wa до 17% против 15%)
• Оба запроса активно используют CPU в пользовательском режиме (38-58%)
• Системная нагрузка (cpu_sy) стабильна и низка (4-5%)

2. Работа с памятью:

• ROW_NUMBER активно использует буферы (177-226 МБ против 10-218 МБ у JOIN)
• JOIN больше использует системный кэш
• Оба запроса работают в условиях недостатка оперативной памяти, что приводит к использованию swap

3. Дисковые операции:

• JOIN интенсивнее работает с диском:
• Чтение: на 15-20% выше
• Запись: в 1.7 раза интенсивнее
• ROW_NUMBER имеет более сбалансированный профиль ввода-вывода
• Высокий cpu_wa подтверждает проблему с производительностью хранилища

4. События ожидания СУБД:

• JOIN имеет более высокую интенсивность IO/ICP событий
• ROW_NUMBER распределяет нагрузку более равномерно
• Оба запроса страдают от external merge сортировки на диске

Профили нагрузки:

JOIN - "Агрессивный профиль":

• Быстрая обработка с пиковой нагрузкой в конце
• Интенсивные дисковые операции записи
• Резкий рост событий ожидания в финальной фазе
• Больше блокировок процессов

ROW_NUMBER - "Стабильный профиль":

• Равномерная нагрузка на протяжении выполнения
• Активное использование буферов памяти
• Меньше дисковых операций, но более продолжительных
• Меньше блокировок, но больше переключений контекста

Итоговое заключение:

JOIN более эффективен для получения полных наборов данных, но требует оптимизации использования памяти для уменьшения дисковых операций.

ROW_NUMBER демонстрирует неоптимальный подход к пагинации, который не дает ожидаемого выигрыша в производительности и создает дополнительную нагрузку на систему.

Ключевая проблема обоих запросов - недостаток оперативной памяти для обработки больших объемов данных, что приводит к дорогостоящим операциям на диске. Оптимизация использования памяти должна стать приоритетным направлением.