Предисловие

Исследование сравнило два метода пагинации — ROW_NUMBER() и Deferred Join — под нагрузкой до 22 параллельных сессий. Прогноз нейросети предсказывал преимущество ROW_NUMBER(), но реальные тесты показали обратное: Deferred Join оказался на 29,3% быстрее, создавал на 70% меньше ожиданий и лучше масштабировался. Этот кейс демонстрирует, как теоретические оптимизации могут не учитывать реальные ограничения СУБД: работу с памятью, параллелизм и стоимость операций ввода-вывода.

postgres=# show work_mem;
 work_mem
----------
 128MB

 relname  | n_live_tup
----------+------------
 tickets  |   15575748
 segments |   20598447

demo=# \d bookings.tickets
               Unlogged table "bookings.tickets"
     Column     |     Type     | Collation | Nullable | Default
----------------+--------------+-----------+----------+---------
 ticket_no      | text         |           | not null |
 book_ref       | character(6) |           | not null |
 passenger_id   | text         |           | not null |
 passenger_name | text         |           | not null |
 outbound       | boolean      |           | not null |
Indexes:
    "tickets_pkey" PRIMARY KEY, btree (ticket_no)
    "idx_tickets_book_ref" btree (book_ref)
    "idx_tickets_covering" btree (ticket_no) INCLUDE (book_ref, passenger_name)
    "tickets_book_ref_passenger_id_outbound_key" UNIQUE CONSTRAINT, btree (book_ref, passenger_id, outbound)
Foreign-key constraints:
    "tickets_book_ref_fkey" FOREIGN KEY (book_ref) REFERENCES bookings(book_ref)
Referenced by:
    TABLE "segments" CONSTRAINT "segments_ticket_no_fkey" FOREIGN KEY (ticket_no) REFERENCES tickets(ticket_no)

demo=# \d bookings.segments
                Unlogged table "bookings.segments"
     Column      |     Type      | Collation | Nullable | Default
-----------------+---------------+-----------+----------+---------
 ticket_no       | text          |           | not null |
 flight_id       | integer       |           | not null |
 fare_conditions | text          |           | not null |
 price           | numeric(10,2) |           | not null |
Indexes:
    "segments_pkey" PRIMARY KEY, btree (ticket_no, flight_id)
    "idx_segments_flight_id" btree (flight_id)
    "idx_segments_flight_id_fare_conditions" btree (flight_id, fare_conditions)
    "idx_segments_ticket_fare" btree (ticket_no, fare_conditions)
    "idx_segments_ticket_no" btree (ticket_no)
    "segments_flight_id_idx" btree (flight_id)
Check constraints:
    "segments_fare_conditions_check" CHECK (fare_conditions = ANY (ARRAY['Economy'::text, 'Comfort'::text, 'Business'::text]))
    "segments_price_check" CHECK (price >= 0::numeric)
Foreign-key constraints:
    "segments_flight_id_fkey" FOREIGN KEY (flight_id) REFERENCES flights(flight_id)
    "segments_ticket_no_fkey" FOREIGN KEY (ticket_no) REFERENCES tickets(ticket_no)
Referenced by:
    TABLE "boarding_passes" CONSTRAINT "boarding_passes_ticket_no_flight_id_fkey" FOREIGN KEY (ticket_no, flight_id) REFERENCES segments(ticket_no, flight_id)

Прогноз нейросети

DeepSeek : прогноз ROW_NUMBER vs Deffered Join при оптимальном сценарии для ROW_NUMBER | Postgres DBA | Дзен

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

--ROW_NUMBER()
EXPLAIN (ANALYZE, COSTS , SUMMARY )
WITH filtered_tickets AS (
    SELECT ticket_no, book_ref, passenger_name
    FROM bookings.tickets t
    WHERE EXISTS (
        SELECT 1 FROM bookings.segments s 
        WHERE s.ticket_no = t.ticket_no 
        AND s.fare_conditions = 'Business'
    )
),
page_info AS (
    SELECT floor(random() * ((SELECT COUNT(*) FROM filtered_tickets) / 100)) as random_page
),
numbered_rows AS (
    SELECT ticket_no, book_ref, passenger_name,
           ROW_NUMBER() OVER (ORDER BY ticket_no) AS rn
    FROM filtered_tickets
)
SELECT ticket_no, book_ref, passenger_name
FROM numbered_rows
CROSS JOIN page_info
WHERE rn > (random_page * 100) AND rn <= (random_page * 100 + 100)
ORDER BY rn;

 Sort  (cost=1220194.35..1220826.23 rows=252751 width=100) (actual time=15605.839..15638.150 rows=100 loops=1)
   Sort Key: (row_number() OVER (?))
   Sort Method: quicksort  Memory: 31kB
   CTE filtered_tickets
     ->  Gather  (cost=245668.76..689978.88 rows=2274755 width=34) (actual time=3322.278..5569.143 rows=2158585 loops=1)
           Workers Planned: 4
           Workers Launched: 4
           ->  Parallel Hash Semi Join  (cost=244668.76..461503.38 rows=568689 width=34) (actual time=3300.293..5731.808 rows=431717 loops=5)
                 Hash Cond: (t.ticket_no = s.ticket_no)
                 ->  Parallel Seq Scan on tickets t  (cost=0.00..200286.37 rows=3893937 width=34) (actual time=0.023..578.750 rows=3115150 loops=5)
                 ->  Parallel Hash  (cost=237560.15..237560.15 rows=568689 width=14) (actual time=3284.107..3284.109 rows=450149 loops=5)
                       Buckets: 4194304  Batches: 1  Memory Usage: 138464kB
                       ->  Parallel Seq Scan on segments s  (cost=0.00..237560.15 rows=568689 width=14) (actual time=1.170..2905.383 rows=450149 loops=5)
                             Filter: (fare_conditions = 'Business'::text)
                             Rows Removed by Filter: 3669541
   CTE page_info
     ->  Result  (cost=51182.00..51182.02 rows=1 width=8) (actual time=6642.176..6642.179 rows=1 loops=1)
           InitPlan 2
             ->  Aggregate  (cost=51181.99..51182.00 rows=1 width=8) (actual time=6642.137..6642.139 rows=1 loops=1)
                   ->  CTE Scan on filtered_tickets filtered_tickets_1  (cost=0.00..45495.10 rows=2274755 width=0) (actual time=3322.281..6479.457 rows=2158585 loops=1)
   ->  Nested Loop  (cost=353988.41..456352.38 rows=252751 width=100) (actual time=14419.610..15605.738 rows=100 loops=1)
         Join Filter: ((((row_number() OVER (?)))::double precision > (page_info.random_page * '100'::double precision)) AND (((row_number() OVER (?)))::double precision <= ((page_info.random_page * '100'::double precision) + '100'::doub
le precision)))
         Rows Removed by Join Filter: 2158485
         ->  CTE Scan on page_info  (cost=0.00..0.02 rows=1 width=8) (actual time=6642.179..6642.181 rows=1 loops=1)
         ->  WindowAgg  (cost=353988.41..393796.60 rows=2274755 width=100) (actual time=7640.496..8702.353 rows=2158585 loops=1)
               ->  Sort  (cost=353988.39..359675.28 rows=2274755 width=92) (actual time=7640.471..7953.436 rows=2158585 loops=1)
                     Sort Key: filtered_tickets.ticket_no
                     Sort Method: external sort  Disk: 102104kB
                     ->  CTE Scan on filtered_tickets  (cost=0.00..45495.10 rows=2274755 width=92) (actual time=0.038..346.632 rows=2158585 loops=1)
 Planning Time: 3.613 ms
 Execution Time: 15718.659 ms
(31 rows)

--Deffered Join
EXPLAIN (ANALYZE, COSTS , SUMMARY )
WITH filtered_tickets AS (
    SELECT ticket_no, book_ref, passenger_name
    FROM bookings.tickets t
    WHERE EXISTS (
        SELECT 1 FROM bookings.segments s 
        WHERE s.ticket_no = t.ticket_no 
        AND s.fare_conditions = 'Business'
    )
),
page_info AS (
    SELECT floor(random() * ((SELECT COUNT(*) FROM filtered_tickets) / 100)) * 100 as random_offset
)
SELECT t.ticket_no, t.book_ref, t.passenger_name
FROM filtered_tickets t
CROSS JOIN page_info
INNER JOIN (
    SELECT ticket_no
    FROM filtered_tickets
    ORDER BY ticket_no
    OFFSET (SELECT random_offset FROM page_info) LIMIT 100
) AS sub ON t.ticket_no = sub.ticket_no
ORDER BY t.ticket_no;

 Sort  (cost=1081440.18..1081440.43 rows=100 width=92) (actual time=11695.806..11723.977 rows=100 loops=1)
   Sort Key: t.ticket_no
   Sort Method: quicksort  Memory: 30kB
   CTE filtered_tickets
     ->  Gather  (cost=245668.76..689978.88 rows=2274755 width=34) (actual time=1029.072..2462.765 rows=2158585 loops=1)
           Workers Planned: 4
           Workers Launched: 4
           ->  Parallel Hash Semi Join  (cost=244668.76..461503.38 rows=568689 width=34) (actual time=1014.678..2836.522 rows=431717 loops=5)
                 Hash Cond: (t_1.ticket_no = s.ticket_no)
                 ->  Parallel Seq Scan on tickets t_1  (cost=0.00..200286.37 rows=3893937 width=34) (actual time=0.017..458.490 rows=3115150 loops=5)
                 ->  Parallel Hash  (cost=237560.15..237560.15 rows=568689 width=14) (actual time=1005.594..1005.599 rows=450149 loops=5)
                       Buckets: 4194304  Batches: 1  Memory Usage: 138464kB
                       ->  Parallel Seq Scan on segments s  (cost=0.00..237560.15 rows=568689 width=14) (actual time=0.139..728.207 rows=450149 loops=5)
                             Filter: (fare_conditions = 'Business'::text)
                             Rows Removed by Filter: 3669541
   CTE page_info
     ->  Result  (cost=51182.00..51182.02 rows=1 width=8) (actual time=3383.275..3383.279 rows=1 loops=1)
           InitPlan 2
             ->  Aggregate  (cost=51181.99..51182.00 rows=1 width=8) (actual time=3383.261..3383.264 rows=1 loops=1)
                   ->  CTE Scan on filtered_tickets filtered_tickets_1  (cost=0.00..45495.10 rows=2274755 width=0) (actual time=1029.077..3224.327 rows=2158585 loops=1)
   ->  Nested Loop  (cost=286248.50..340275.95 rows=100 width=92) (actual time=11137.369..11695.540 rows=100 loops=1)
         ->  CTE Scan on page_info  (cost=0.00..0.02 rows=1 width=0) (actual time=3383.281..3383.285 rows=1 loops=1)
         ->  Hash Join  (cost=286248.50..340274.93 rows=100 width=92) (actual time=7754.082..8312.232 rows=100 loops=1)
               Hash Cond: (t.ticket_no = filtered_tickets.ticket_no)
               ->  CTE Scan on filtered_tickets t  (cost=0.00..45495.10 rows=2274755 width=92) (actual time=0.059..327.146 rows=2158585 loops=1)
               ->  Hash  (cost=286247.25..286247.25 rows=100 width=32) (actual time=7641.911..7641.916 rows=100 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 13kB
                     ->  Limit  (cost=286247.00..286247.25 rows=100 width=32) (actual time=7641.845..7641.868 rows=100 loops=1)
                           InitPlan 4
                             ->  CTE Scan on page_info page_info_1  (cost=0.00..0.02 rows=1 width=8) (actual time=0.002..0.004 rows=1 loops=1)
                           ->  Sort  (cost=285678.29..291365.18 rows=2274755 width=32) (actual time=7554.414..7607.424 rows=772000 loops=1)
                                 Sort Key: filtered_tickets.ticket_no
                                 Sort Method: top-N heapsort  Memory: 16673kB
                                 ->  CTE Scan on filtered_tickets  (cost=0.00..45495.10 rows=2274755 width=32) (actual time=0.003..421.538 rows=2158585 loops=1)
 Planning Time: 0.977 ms
 Execution Time: 11802.626 ms
(36 rows)

Задача

Экспериментально проверить прогноз нейросети об эффективности метода пагинации с использованием ROW_NUMBER для заданного сценария.

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

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

• СУБД: PostgreSQL 17

• Инструмент нагрузочного тестирования: pg_expecto

• Тестовая база данных: "Демобаза 2.0" (большой размер, сложная схема)

• Условия тестирования: параллельная нагрузка, запрос к большой таблице.

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

Производительность и ожидания СУБД в ходе нагрузочного тестирования

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

Результат

Среднее превышение операционной скорости , при использовании метода DIFFERED JOIN составило 29.3%

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

Результат

Среднее снижение ожиданий СУБД, при использовании метода DIFFERED JOIN составило 70.12%

Характерные особенности тестовых запросов и планов выполнения

1. Обработка данных (основная разница)

Запрос с ROW_NUMBER():

• Полная сортировка всех 2.1M строк (2274755 строк) на диске (Sort Method: external sort Disk: 102104kB)

• Применяет ROW_NUMBER() ко всем строкам (2.1M), затем фильтрует до 100

• Время сортировки: 7640ms - 7953ms для полной сортировки

Запрос с Deferred Join:

• Top-N сортировка с heap sort в памяти (Sort Method: top-N heapsort Memory: 16673kB)

• Сначала находит 100 нужных ticket_no, затем джойнит

• Сортирует 2.1M строк, но использует эффективный алгоритм для OFFSET/LIMIT

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

ROW_NUMBER():

• Sort Method: external sort Disk: 102104kB - сортировка на диске

• Причина: work_mem=128MB недостаточно для полной сортировки 2.1M строк

• Параллельный hash join использует Memory Usage: 138464kB

Deferred Join:

• Sort Method: top-N heapsort Memory: 16673kB - в памяти

• Более эффективное использование work_mem благодаря top-N алгоритму

3. Производительность

Время выполнения:

• ROW_NUMBER(): 15718.659 ms (15.7 секунд)

• Deferred Join: 11802.626 ms (11.8 секунд)

• Разница: Deferred Join на 25% быстрее

Ключевые точки замедления для ROW_NUMBER():

1. Полная сортировка на диске (7.6-8.0 секунд)

2. Применение оконной функции ко всем строкам (8.7 секунд)

3. Фильтрация после нумерации (удаление 2,158,485 строк)

4. Эффективность алгоритмов

ROW_NUMBER() паттерн:

• Сложность: O(n log n) для полной сортировки + O(n) для оконной функции

• Проблема: Выполняет всю работу, даже для получения 100 строк

Deferred Join паттерн:

• Сложность: O(n log k) для top-N сортировки (k = OFFSET + LIMIT)

• Преимущество: Сортирует только для определения нужных строк

5. Индексы и доступ к данным

Оба запроса НЕ используют существующие индексы оптимально:

• Нет использования индекса idx_segments_flight_id_fare_conditions

• Нет использования покрывающего индекса idx_tickets_covering

• Оба используют Parallel Seq Scan на таблицах

6. CTE и оптимизация

Общие проблемы обоих запросов:

1. CTE материализуется (выполняется один раз, сохраняется в память/диск)

2. Двойной подсчет строк: Сначала считает общее количество для random_page, затем снова сортирует

3. Неоптимальное соединение: Hash Semi Join вместо использования индексов

Принципиальные выводы:

1. Deferred Join эффективнее ROW_NUMBER() в этом случае, потому что:
   Избегает полной сортировки всех данных
   Использует top-N алгоритм вместо полной сортировки
   Меньше использует дисковые операции

2. Оба запроса имеют проблемы с производительностью из-за:
   Отсутствия использования индексов
   Полного сканирования таблиц
   CTE материализации

3. ROW_NUMBER() становится неэффективным при:
   Большом объеме данных (более 2M строк)
   Недостаточной work_mem для полной сортировки
   Глубокой пагинации (большой OFFSET)

Характерные особенности производительности СУБД

1. Анализ операционной скорости:

ROW_NUMBER():

• Начальная скорость: 30

• Максимальная: 36 (с 61 по 88 измерения)

• Спад: Падение до 26 при максимальной нагрузке (22 сессии)

• Коэффициент детерминации R² = 0 - нет линейной зависимости от нагрузки

• Угол наклона 1.59 - минимальный рост производительности

Deferred Join:

• Начальная скорость: 30

• Максимальная: 48 (с 59 по 93 измерения)

• Стабильность: Сохраняет 46-48 даже при 22 сессиях

• Коэффициент детерминации R² = 0.79 - сильная линейная зависимость

• Угол наклона 41.6 - значительный рост производительности с нагрузкой

Вывод: Deferred Join показывает на 33-45% выше производительность и лучше масштабируется.

2. Анализ ожиданий СУБД (WAITINGS):

ROW_NUMBER():

• Начальные ожидания: 2047 событий

• Максимальные: 17212 событий (рост в 8.4 раза)

• Коэффициент детерминации R² = 0.93 - очень сильная линейная зависимость

• Угол наклона 43.91 - резкий рост ожиданий с нагрузкой

Deferred Join:

• Начальные ожидания: 842 событий (в 2.4 раза меньше!)

• Максимальные: 4683 события (рост в 5.6 раза)

• Коэффициент детерминации R² = 0.76 - умеренная зависимость

• Угол наклона 41.08 - более плавный рост

Вывод: ROW_NUMBER() создает в 2-4 раза больше ожиданий и хуже масштабируется.

3. Анализ типов ожиданий:

Корреляция SPEED-WAITINGS:

• ROW_NUMBER(): -0.04 (отсутствие корреляции)

• Deferred Join: 0.64 (умеренная положительная корреляция)

Интерпретация: Для ROW_NUMBER() рост ожиданий не приводит к росту производительности, в то время как Deferred Join эффективно использует ожидания для увеличения throughput.

Основные типы ожиданий:

ROW_NUMBER():

1. IO ожидания (корр. 1.0) - доминирующий тип, рост с 693 до 3,585

2. IPC ожидания (корр. 1.0) - рост с 1313 до 13415

3. LWLOCK ожидания (корр. 0.98) - рост с 37 до 136

Deferred Join:

1. IO ожидания (корр. 0.97) - рост с 588 до 2,518

2. IPC ожидания (корр. 0.92) - рост с 247 до 1912

3. LWLOCK ожидания (корр. 0.97) - рост с 6 до 226

4. Паттерны поведения под нагрузкой:

Критические точки ROW_NUMBER():

• Измерение 46 (10 сессий): Скачок ожиданий с 2,396 до 4,861 (+103%)

• Измерение 92 (18 сессий): Падение скорости с 34 до 32 ops/sec

• Измерение 93 (22 сессии): Резкое падение скорости с 32 до 26 ops/sec (-19%)

Stability Deferred Join:

• Плавный рост скорости до 48 ops/sec

• Отсутствие резких падений даже при 22 сессиях

• Более эффективное использование ресурсов (меньше ожиданий на единицу производительности)

5. Анализ нагрузки :

Корреляция с производительностью:

ROW_NUMBER():

• 5-10 сессий: 30-32

• 12-13 сессий: 32-36 (оптимальный диапазон)

• 15+ сессий: Начинается деградация

• 22 сессии: 26 ops/sec (падение на 28% от пика)

Deferred Join:

• 5-10 сессий: 30-34

• 12-13 сессий: 38-48 (рост на 58%)

• 15+ сессий: Стабильные 46-48

• 22 сессии: 46-48 (сохранение пиковой производительности)

6. Принципиальные выводы:

Преимущества Deferred Join:

1. Выше производительность: +33-45%

2. Лучшая масштабируемость: Линейный рост до 48

3. Меньше ожиданий: В 2-4 раза меньше системных ожиданий

4. Стабильность: Сохранение производительности при максимальной нагрузке

5. Эффективность использования ресурсов: Меньше IO/операций на запрос

Проблемы ROW_NUMBER():

1. Плохая масштабируемость: Деградация при 15+ сессиях

2. Высокие системные ожидания: Особенно IO и IPC

3. Неэффективное использование памяти: Disk sort при work_mem=128MB

4. Резкие переходы: Скачки в ожиданиях и падения производительности

Итог: Deferred Join демонстрирует превосходство по всем ключевым метрикам производительности и масштабируемости в данном сценарии нагрузочного тестирования.

Метрики производительности инфраструктуры

1. Анализ загрузки CPU:

ROW_NUMBER():

• cpu_us (user): 57% → 70% (рост на 23%)

• cpu_sy (system): 5% → 7% (рост на 40%)

• cpu_id (idle): 37% → 18% (падение на 51%)

• cpu_wa (iowait): 1% → 3% (рост в 3 раза)

Deferred Join:

• cpu_us: 60% → 88% (рост на 47%)

• cpu_sy: 3% → 5% (рост на 67%)

• cpu_id: 36% → 4% (падение на 89%)

• cpu_wa: 0% → 1% (минимальный рост)

Вывод: Deferred Join эффективнее использует CPU:

• На 26% выше user time (88% vs 70%)

• В 2.7 раза меньше idle time (4% vs 18%)

• Минимальные iowait ожидания

2. Анализ памяти:

ROW_NUMBER():

• swpd (swap): 231 → 300 (рост на 30%)

• free (свободно): 1231 → 1258 MB (минимальный рост)

• buff/cache: 4842 → 2965 MB (падение на 39%)

Deferred Join:

• swpd: 235 → 295 (рост на 26%)

• free: 858 → 1525 MB (рост на 78%)

• buff/cache: 5244 → 3151 MB (падение на 40%)

Вывод: Оба паттерна активно используют кэш, но Deferred Join:

• Освобождает больше памяти по мере работы

• Имеет меньший рост использования swap

• Более эффективное управление памятью

3. Анализ ввода-вывода (IO):

ROW_NUMBER():

• bi (block in): 2 → 37,001 (рост в 18,500 раз!)

• bo (block out): 49,280 → 61,210 (рост на 24%)

Deferred Join:

• bi: 160 → 28,771 (рост в 180 раз)

• bo: 4,655 → 33,109 (рост в 7 раз)

Критические различия:

1. ROW_NUMBER() создает в 100+ раз больше операций чтения

2. Пик чтения ROW_NUMBER(): 37001 vs 28771 (на 29% выше)

3. ROW_NUMBER() имеет массовый disk sort (bi 37,001 при bo 61,210)

4. Анализ системных вызовов:

ROW_NUMBER():

• in (interrupts): 13,852 → 61,210 (рост в 4.4 раза)

• cs (context switches): 17,483 → 15,379 (падение на 12%)

Deferred Join:

• in: 7,215 → 31,557 (рост в 4.4 раза)

• cs: 3,905 → 11,470 (рост в 2.9 раза)

Вывод: ROW_NUMBER() создает:

• В 2.2 раза больше прерываний (61,210 vs 31,557)

• На 34% больше контекстных переключений (15,379 vs 11,470)

5. Паттерны поведения под нагрузкой:

Критические точки ROW_NUMBER():

• Измерение 50-60 (10-12 сессий):
  Резкий рост IO (bi до 11,056, bo до 77,593)
  Снижение idle CPU с 37% до 12%
  Начало активного использования swap

• Измерение 80-90 (15-18 сессий):
  Пик IO (bi до 33,075, bo до 80,290)
  CPU idle падает до 14-15%
  Рост iowait до 2%

Stability Deferred Join:

• Плавный рост всех метрик

• Без резких скачков в IO

• Стабильное использование CPU с постепенным снижением idle

6. Корреляция с нагрузкой (load.txt):

При 5-10 сессиях:

• ROW_NUMBER(): CPU 57-59%, IO низкий

• Deferred Join: CPU 60-61%, IO умеренный

При 12-13 сессиях:

• ROW_NUMBER(): CPU 59-80%, IO взрывной рост

• Deferred Join: CPU 61-92%, IO плавный рост

При 22 сессиях:

• ROW_NUMBER(): CPU 69-70%, IO 32-37K, высокий iowait

• Deferred Join: CPU 88-94%, IO 28-33K, минимальный iowait

7. Ключевые выводы:

Преимущества Deferred Join:

1. Эффективнее использует CPU: 94% user time vs 70%

2. Меньше системных прерываний: В 2.2 раза меньше

3. Контролируемый рост IO: Без резких скачков

4. Минимальный iowait: 0-1% vs 2-3%

5. Лучшее управление памятью: Больше свободной памяти

Проблемы ROW_NUMBER():

1. Экспоненциальный рост IO операций: В 18,500 раз!

2. Высокий iowait: В 3 раза больше

3. Массовые контекстные переключения: Снижают эффективность

4. Активное использование swap: Признак нехватки памяти

5. Резкие переходы: Скачки в метриках

8. Принципиальные различия в архитектуре:

ROW_NUMBER():

• Дисковый паттерн: Частые операции чтения/записи

• Перегрузка памяти: Активное использование swap

• CPU-IO дисбаланс: Высокий iowait мешает CPU

Deferred Join:

• CPU-центричный паттерн: Максимальная загрузка процессора

• Эффективный кэш: Плавное использование диска

• Сбалансированная работа: Минимальные простои

Итог:

Deferred Join демонстрирует более эффективное использование системных ресурсов, особенно CPU и памяти, при меньшей нагрузке на подсистему ввода-вывода. ROW_NUMBER() страдает от проблем с производительностью из-за массовых дисковых операций и неэффективного использования памяти.

Анализ ошибочного прогноза нейросети

Вопрос нейросети

Почему не оправдался прогноз нейросети о большей эффективности метода ROW_NUMBER для заданных условий ?

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

Критический анализ причин несостоятельности прогноза нейросети:

1. Ошибка в исходных предпосылках:

Прогноз предполагал:

• Фильтрация fare_conditions = 'Business' сократит данные до 10-15% (1.5-2.3M строк)

Реальность из планов выполнения:

• filtered_tickets возвращает 2.1M строк (не 1.5-2.3M)

• Это 13.5% от tickets, но:
  15.5M → 2.1M = 13.5% ✓
  Однако объем данных для сортировки все еще огромен

2. Фундаментальные ошибки в анализе:

Ошибка 1: Игнорирование стоимости соединения таблиц

Прогноз не учел, что:

• Фильтрация требует Hash Semi Join между tickets (15.5M) и segments (20.5M)

• Это дорогостоящая операция, которая выполняется до применения ROW_NUMBER()

• В плане: Parallel Hash Semi Join занимает 3.3-5.7 секунд

Ошибка 2: Непонимание работы оконных функций в PostgreSQL

• ROW_NUMBER() не может использовать индексы для сортировки после фильтрации

• Даже с idx_tickets_covering, сортировка происходит после фильтрации

• В плане: Sort Method: external sort Disk: 102104kB

Ошибка 3: Неправильная оценка покрывающих индексов

• Индекс idx_tickets_covering уже существует (из описания схемы)

• Но он не используется в плане ROW_NUMBER()

• Причина: оптимизатор выбирает параллельное сканирование вместо индексного

3. Ключевые факторы, которые нейросеть проигнорировала:

Фактор 1: Work_mem ограничения

• При work_mem=128MB сортировка 2.1M строк происходит на диске

• Это добавляет 7.6-8.0 секунд к времени выполнения

• Deferred Join использует top-N сортировку в памяти

Фактор 2: Параллельные workers

• ROW_NUMBER() использует 4 параллельных workers

• Каждый worker использует 138MB памяти для hash join

• При 22 сессиях это 14.85GB - больше доступной памяти

Фактор 3: Реальные метрики производительности

Из тестирования видно:

• ROW_NUMBER(): 15.7 секунд, деградация при 15+ сессиях

• Deferred Join: 11.8 секунд, стабильная работа при 22 сессиях

4. Почему прогноз был оптимистичным для ROW_NUMBER():

Теоретические предположения vs реальность:

5. Условия, при которых ROW_NUMBER() действительно был бы эффективнее:

Сценарий 1: Маленький результат фильтрации

Если бы fare_conditions = 'Business' возвращал не 2.1M, а ≤ 100K строк

Сценарий 2: Адекватный размер work_mem

Если бы work_mem был ≥ 256MB для сортировки в памяти

Сценарий 3: Индексная организация данных

Если бы таблица была кластеризована по ticket_no

Сценарий 4: Отсутствие параллелизма

Если бы запросы выполнялись последовательно, а не параллельно

6. Что прогноз упустил из реальных планов выполнения:

Из плана ROW_NUMBER():

Sort Method: external sort Disk: 102104kB # Критично!
WindowAgg: 7640ms - 8702ms # Очень дорого
Rows Removed by Join Filter: 2158485 # Неэффективно

Из плана Deferred Join:

Sort Method: top-N heapsort Memory: 16673kB # Эффективно
Limit: rows=100 # Минимум работы

7. Уроки для будущих прогнозов:

Обязательно учитывать:

1. Размер work_mem и его влияние на сортировку

2. Реальные планы выполнения, а не теоретические индексы

3. Параллелизм и конкуренцию за ресурсы

4. Объем данных после фильтрации

5. Стоимость соединений перед применением оконных функций

8. Заключение:

Прогноз нейросети не оправдался потому, что:

1. Переоценил эффект фильтрации (2.1M строк все равно много)

2. Игнорировал ограничения work_mem (дисковая сортировка)

3. Не учел стоимость предварительных соединений

4. Предположил использование индексов, которые не применяются

5. Не учел влияние параллелизма на производительность

Реальный вывод:

Deferred Join оказался эффективнее из-за использования top-N сортировки в памяти, меньшего потребления ресурсов и лучшей масштабируемости при параллельных сессиях. ROW_NUMBER() страдает от необходимости полной сортировки больших объемов данных и ограничений памяти.