➡️pg_expecto + Демобаза 2.0 : анализ эффективности замены агрегатной функции MAX на конструкцию ARRAY.

Не верь - проверяй !

Предпосылка к эксперименту

Проверка в ходе нагрузочного тестирования гипотезы о эффективном влиянии на рост производительности СУБД замены паттерна SQL "MAX" на паттерн "ARRAY"

Тестовая база данных, сценарии 1-3 и нагрузка - аналогичны ранее проведенным экспериментам

Тестовый запрос: использование агрегатной функции max

План выполнения тестового запроса: использование агрегатной функции max

Result (cost=321155.20..321155.22 rows=1 width=48) (actual time=7098.553..7098.594 rows=1 loops=1)

Тестовый запрос: использование паттерна ARRAY

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

Result (cost=320322.22..320322.24 rows=1 width=48) (actual time=7273.048..7273.080 rows=1 loops=1)

Результаты нагрузочного тестирования - производительность СУБД

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

График изменения нагрузки на СУБД в ходе нагрузочного тестирования

Операционная скорость СУБД

График изменения операционной скорости СУБД для нагрузочного тестирования с использованием max и array.

График изменения относительной разницы операционной скорости для нагрузочного тестирования с использованием array по сравнению с использованием max.

Средняя разница операционной скорости СУБД при использовании MAX и ARRAY составила 0.08%.

Результаты нагрузочного тестирования - метрики инфраструктуры (vmstat)

Сравнительный анализ ключевых метрик

1. Нагрузка на CPU

• CPU пользовательское время (us): Оба теста показывают стабильно высокие значения 97-98%
• CPU системное время (sy): Оба теста показывают 2-3%
• Процессы в состоянии выполнения (r):
• TEST-1 (MAX): 20 → 54 (рост в 2.7 раза)
• TEST-2 (ARRAY): 20 → 56 (рост в 2.8 раза)

Вывод: Нагрузка на процессор практически идентична в обоих тестах.

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

• Своп (swpd): В обоих тестах постоянное значение 202
• Свободная память (free):
• TEST-1 (MAX): 272 → 400 (+47%)
• TEST-2 (ARRAY): 1215 → 565 (-53%)

Вывод: Тест с ARRAY показывает большее потребление оперативной памяти.

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

• Блоки вывода (bo):
• TEST-1 (MAX): 69 → 72 (минимальный рост)
• TEST-2 (ARRAY): 320 → 83 (снижение на 74%, но с пиками до 525)

Вывод: Тест с ARRAY демонстрирует более нестабильную и в целом более высокую нагрузку на I/O.

4. Системная активность

• Прерывания (in):
• TEST-1 (MAX): 8663 → 8786 (+1.4%)
• TEST-2 (ARRAY): 8677 → 8756 (+0.9%)
• Переключения контекста (cs):
• TEST-1 (MAX): 4236 → 4763 (+12.4%)
• TEST-2 (ARRAY): 4223 → 4831 (+14.4%)

Итоговый вывод по показателям инфраструктуры

Статистически значимого изменения общей производительности СУБД при замене MAX на ARRAY не обнаружено.

Ключевые наблюдения:

1. Производительность CPU: Одинакова в обоих тестах (97-98% пользовательского времени)
2. Потребление памяти: Подход с ARRAY использует больше оперативной памяти
3. I/O нагрузка: ARRAY демонстрирует более волатильный профиль ввода-вывода
4. Системная нагрузка: Оба подхода создают сопоставимую нагрузку на ядро ОС

Общий итог

В условиях данной тестовой среды с параллельной нагрузкой замена агрегатной функции MAX на конструкцию ARRAY не дает ожидаемого прироста производительности, что ставит под сомнение универсальность данной рекомендации из статьи "PostgreSQL Antipatterns".

Выбор между подходами должен основываться на конкретных требованиях к использованию памяти и стабильности I/O операций.