ПРОГНОЗ: ROW_NUMBER vs Deffered Join при оптимальном сценарии для ROW_NUMBER

Вопрос

Используя информацию о размерах таблиц, схеме баз данных и тестовых запросах, дай прогноз и обоснование - какой тестовый запрос покажет большую производительность для нагрузочного тестирования с параллельной нагрузкой от 5 до 22 сессий для СУБД PostgreSQL 17 c ресурсами CPU=8 RAM=8GB

Ответ нейросети

Прогноз:

Наибольшую производительность при параллельной нагрузке (5–22 сессии) покажет запрос plan-1 (ROW_NUMBER()) после оптимизации индексами.

Обоснование:

1. Анализ текущих планов выполнения

• plan-1 (ROW_NUMBER):
• Время выполнения: ~12.9 сек
• Основные затраты: сортировка 2.15M строк на диске (102 MB), WindowAgg, Join Filter.
• Проблема: external sort на диске из-за недостатка work_mem.
• plan-2 (Deferred Join):
• Время выполнения: ~12.2 сек
• Основные затраты: сортировка 2.15M строк на диске (46 MB), Hash Join.
• Проблема: также external sort, два прохода по данным (сортировка + join).

2. Влияние созданных индексов

Созданы ключевые индексы:

1. idx_tickets_covering (ticket_no) INCLUDE (book_ref, passenger_name)
2. → Покрывающий индекс для tickets. Позволит Index Only Scan вместо Seq Scan.
3. idx_segments_ticket_fare (ticket_no, fare_conditions)
4. → Фильтрация по fare_conditions + join по ticket_no. Ускорит EXISTS.

Ожидаемый эффект:

• Устранение Parallel Seq Scan на таблице tickets → Index Only Scan.
• Устранение Parallel Seq Scan на segments → Index Scan с фильтром.
• Сокращение времени выполнения в 3–5 раз.

3. Сравнение оптимизированных запросов

После применения индексов:

• plan-1 (ROW_NUMBER):
• Будет использовать idx_segments_ticket_fare для быстрого поиска Business билетов.
• idx_tickets_covering позволит получить все данные из индекса без обращения к heap.
• WindowAgg будет работать в памяти (сокращение данных с 2.15M строк до ~100 строк).
• Ожидаемое время: ~2–3 сек.
• plan-2 (Deferred Join):
• Также выиграет от индексов, но сохранит два этапа: сортировка всего набора + join.
• Hash Join может стать быстрее, но останется необходимость сортировки 2.15M строк.
• Ожидаемое время: ~3–4 сек.

4. Факторы параллельной нагрузки

• Конкуренция за I/O:
• Оба запроса после оптимизации будут использовать индексные сканы, что снизит нагрузку на диск.
• Использование памяти:
• plan-1: WindowAgg требует памяти для нумерации строк, но после фильтрации данных станет меньше.
• plan-2: Hash Join и сортировка могут использовать много памяти, но при параллельных запросах это вызовет конкуренцию.
• Масштабируемость:
• plan-1 с индексами будет выполнять Index Only Scan + Index Scan → минимальные блокировки.
• plan-2 требует сортировки всего набора данных, что при параллельных сессиях создаст contention на work_mem и дисковые операции.

5. Прогноз для 5–22 сессий

• plan-1 лучше масштабируется:
• Каждый запрос быстро фильтрует данные через индексы.
• ROW_NUMBER() работает только на отфильтрованном подмножестве.
• Нет тяжелых операций сортировки больших наборов.
• plan-2 будет страдать от конкуренции:
• Сортировка 2.15M строк даже с индексами останется ресурсоемкой.
• При 22 сессиях одновременные сортировки приведут к деградации.

6. Рекомендации по тестированию

1. Увеличьте work_mem до 64–128 MB, чтобы сортировка выполнялась в памяти.
2. Проверьте использование индексов через EXPLAIN (ANALYZE, BUFFERS).

Итог:

После применения индексов plan-1 (ROW_NUMBER) станет быстрее (~2–3 сек) и будет лучше масштабироваться при параллельной нагрузке благодаря:

• Покрывающему индексу idx_tickets_covering.
• Эффективному индексу idx_segments_ticket_fare.
• Минимальной конкуренции за ресурсы при множественных сессиях.

Ожидаемая пропускная способность при 22 сессиях:

~22 * (100 строк / 2.5 сек) ≈ 880 строк/сек.