Предисловие
В мире высоконагруженных баз данных выбор метода пагинации может стать решающим фактором для производительности системы. Эксперимент, проведённый с двумя подходами — классическим ROW_NUMBER и отложенным соединением (Deferred Join) — показал, что даже архитектурно более совершенный метод не гарантирует победы без тонкой настройки СУБД. Исследование раскрывает, как правильная конфигурация памяти PostgreSQL перевесила преимущества Deferred Join и позволила ROW_NUMBER добиться превосходства на параллельной нагрузке .
Начало экспериментов с методом пагинации ROW_NUMBER
Пагинация в PostgreSQL: ROW_NUMBER убивает производительность / Хабр
Вопрос нейросети
tables.txt
relname | n_live_tup
-----------------+------------
segments | 20598447
tickets | 15575748
(12 rows)indexes.sql
-- Создаем покрывающий индекс для запроса ROW_NUMBER()
CREATE INDEX idx_tickets_covering ON bookings.tickets (ticket_no) INCLUDE (book_ref, passenger_name);
-- Для сравнения, создаем индекс для deferred join (если еще нет)
CREATE INDEX idx_tickets_ticket_no ON bookings.tickets (ticket_no); --УЖЕ БЫЛ
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)
-- Индекс для ускорения фильтрации
CREATE INDEX idx_segments_ticket_fare ON bookings.segments (ticket_no, fare_conditions);
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)plan-1.sql
--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=1228291.85..1228923.73 rows=252751 width=100) (actual time=12830.169..12858.743 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=2330.866..3900.461 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=2315.696..4300.950 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.033..542.310 rows=3115150 loops=5)
-> Parallel Hash (cost=237560.15..237560.15 rows=568689 width=14) (actual time=2306.370..2306.372 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.892..2010.351 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=4722.841..4722.844 rows=1 loops=1)
InitPlan 2
-> Aggregate (cost=51181.99..51182.00 rows=1 width=8) (actual time=4722.777..4722.778 rows=1 loops=1)
-> CTE Scan on filtered_tickets filtered_tickets_1 (cost=0.00..45495.10 rows=2274755 width=0) (actual time=2330.871..4569.074 rows=2158585 loops=1)
-> Nested Loop (cost=353988.41..456352.38 rows=252751 width=100) (actual time=11697.811..12830.111 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=4722.844..4722.848 rows=1 loops=1)
-> WindowAgg (cost=353988.41..393796.60 rows=2274755 width=100) (actual time=6805.951..7852.082 rows=2158585 loops=1)
-> Sort (cost=353988.39..359675.28 rows=2274755 width=92) (actual time=6805.933..7109.981 rows=2158585 loops=1)
Sort Key: filtered_tickets.ticket_no
Sort Method: external sort Disk: 102128kB
-> CTE Scan on filtered_tickets (cost=0.00..45495.10 rows=2274755 width=92) (actual time=0.069..340.965 rows=2158585 loops=1)
Planning Time: 15.340 ms
Execution Time: 12899.583 ms
(31 rows)plan-2.sql
--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=1113319.73..1113319.98 rows=100 width=92) (actual time=12122.320..12152.383 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=1930.285..3476.659 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=1916.383..3873.233 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.025..479.551 rows=3115150 loops=5)
-> Parallel Hash (cost=237560.15..237560.15 rows=568689 width=14) (actual time=1907.371..1907.372 rows=450149 loops=5)
Buckets: 4194304 Batches: 1 Memory Usage: 138432kB
-> Parallel Seq Scan on segments s (cost=0.00..237560.15 rows=568689 width=14) (actual time=0.777..1594.418 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=4298.997..4299.000 rows=1 loops=1)
InitPlan 2
-> Aggregate (cost=51181.99..51182.00 rows=1 width=8) (actual time=4298.985..4298.987 rows=1 loops=1)
-> CTE Scan on filtered_tickets filtered_tickets_1 (cost=0.00..45495.10 rows=2274755 width=0) (actual time=1930.288..4144.594 rows=2158585 loops=1)
-> Nested Loop (cost=318128.05..372155.50 rows=100 width=92) (actual time=12073.605..12122.078 rows=100 loops=1)
-> CTE Scan on page_info (cost=0.00..0.02 rows=1 width=0) (actual time=4299.001..4299.003 rows=1 loops=1)
-> Hash Join (cost=318128.05..372154.48 rows=100 width=92) (actual time=7774.597..7823.051 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.083..326.449 rows=2158585 loops=1)
-> Hash (cost=318126.80..318126.80 rows=100 width=32) (actual time=7185.320..7185.324 rows=100 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
-> Limit (cost=318126.55..318126.80 rows=100 width=32) (actual time=7185.247..7185.274 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.001..0.003 rows=1 loops=1)
-> Sort (cost=317557.84..323244.73 rows=2274755 width=32) (actual time=7070.748..7155.317 rows=639900 loops=1)
Sort Key: filtered_tickets.ticket_no
Sort Method: external sort Disk: 46472kB
-> CTE Scan on filtered_tickets (cost=0.00..45495.10 rows=2274755 width=32) (actual time=0.002..401.503 rows=2158585 loops=1)
Planning Time: 0.961 ms
Execution Time: 12189.914 ms
(36 rows)Используя информацию о размерах таблиц, схеме баз данных и тестовых запросах, дай прогноз и обоснование - какой тестовый запрос покажет большую производительность для нагрузочного тестирования с параллельной нагрузкой от 5 до 22 сессий для СУБД PostgreSQL 17 c ресурсами CPU=8 RAM=8GB
Прогноз:
Наибольшую производительность при параллельной нагрузке (5–22 сессии) покажет запрос plan-1 (ROW_NUMBER()) после оптимизации индексами.
Экспериментальная проверка
Анализ ошибочного прогноза нейросети
Вопрос нейросети
Почему не оправдался прогноз нейросети о большей эффективности метода ROW_NUMBER для заданных условий ?
Ответ нейросети
5. Условия, при которых ROW_NUMBER() действительно был бы эффективнее:
Сценарий 2: Адекватный размер work_mem
Если бы work_mem был ≥ 256MB для сортировки в памяти
Вопрос нейросети
какие значение work_mem необходимо установить для получения преимущества метода row_number при максимальной нагрузке 22 параллельные сессии при общем размере RAM=8GB ?
Ответ нейросети
-- Для получения преимущества ROW_NUMBER():SET work_mem = '180MB';SET max_parallel_workers_per_gather = 1;SET max_parallel_workers = 16;
-- В postgresql.conf:
work_mem = 180MB
max_parallel_workers_per_gather = 1
max_parallel_workers = 16
max_worker_processes = 16
shared_buffers = 2GB
effective_cache_size = 6GB
Задача
Экспериментальная проверка рекомендации нейросети по оптимизации метода пагинации ROW_NUMBER
Методология исследования
Тестовая среда и инструменты:
СУБД: PostgreSQL 17
Тестовая база данных: "Демобаза 2.0" (большой размер, сложная схема)
Условия тестирования: параллельная нагрузка, запрос к большой таблице.
Нагрузка на СУБД
Изменение конфигурационных параметров СУБД
ALTER SYSTEM SET work_mem = '180MB';
ALTER SYSTEM SET max_parallel_workers_per_gather = 1;
ALTER SYSTEM SET max_parallel_workers = 16;
ALTER SYSTEM SET max_worker_processes = 16 ;
ALTER SYSTEM SET shared_buffers = '2GB';
ALTER SYSTEM effective_cache_size = '6GB';Производительность и ожидания СУБД в ходе нагрузочного тестирования
Операционная скорость
Результат
Среднее превышение операционной скорости , при использовании метода ROW_NUMBER составило 12.59%
Ожидания СУБД
Результат
Среднее снижение ожиданий СУБД, при использовании метода ROW_NUMBER составило 18.06%
Тестовый запрос и план выполнения с использованием 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=1223612.75..1224238.52 rows=250309 width=100) (actual time=12525.009..12525.024 rows=100 loops=1)
Sort Key: (row_number() OVER (?))
Sort Method: quicksort Memory: 31kB
CTE filtered_tickets
-> Merge Semi Join (cost=1.12..698694.07 rows=2252784 width=34) (actual time=0.067..9304.741 rows=2158585 loops=1)
Merge Cond: (t.ticket_no = s.ticket_no)
-> Index Only Scan using idx_tickets_covering on tickets t (cost=0.56..353511.60 rows=15575748 width=34) (actual time=0.024..3368.547 rows=15575748 loops=1)
Heap Fetches: 120058
-> Index Only Scan using idx_segments_ticket_fare on segments s (cost=0.56..278084.36 rows=2252784 width=14) (actual time=0.036..1903.512 rows=2250743 loops=1)
Index Cond: (fare_conditions = 'Business'::text)
Heap Fetches: 35563
CTE page_info
-> Result (cost=50687.65..50687.67 rows=1 width=8) (actual time=10552.416..10552.418 rows=1 loops=1)
InitPlan 2
-> Aggregate (cost=50687.64..50687.65 rows=1 width=8) (actual time=10552.361..10552.362 rows=1 loops=1)
-> CTE Scan on filtered_tickets filtered_tickets_1 (cost=0.00..45055.68 rows=2252784 width=0) (actual time=0.069..10364.987 rows=2158585 loops=1)
-> Nested Loop (cost=350411.33..451786.61 rows=250309 width=100) (actual time=11599.226..12524.909 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=10552.418..10552.420 rows=1 loops=1)
-> WindowAgg (cost=350411.33..389835.03 rows=2252784 width=100) (actual time=850.669..1723.660 rows=2158585 loops=1)
-> Sort (cost=350411.31..356043.27 rows=2252784 width=92) (actual time=850.647..1001.073 rows=2158585 loops=1)
Sort Key: filtered_tickets.ticket_no
Sort Method: quicksort Memory: 170828kB
-> CTE Scan on filtered_tickets (cost=0.00..45055.68 rows=2252784 width=92) (actual time=0.001..242.030 rows=2158585 loops=1)
Planning Time: 3.135 ms
Execution Time: 12650.181 ms
(27 rows)
Тестовый запрос и план выполнения с использованием DIFFERED JOIN
--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=1086216.73..1086216.98 rows=100 width=92) (actual time=16100.696..16100.714 rows=100 loops=1)
Sort Key: t.ticket_no
Sort Method: quicksort Memory: 30kB
CTE filtered_tickets
-> Merge Semi Join (cost=1.12..698694.07 rows=2252784 width=34) (actual time=0.059..8756.097 rows=2158585 loops=1)
Merge Cond: (t_1.ticket_no = s.ticket_no)
-> Index Only Scan using idx_tickets_covering on tickets t_1 (cost=0.56..353511.60 rows=15575748 width=34) (actual time=0.019..3135.857 rows=15575748 loops=1)
Heap Fetches: 120058
-> Index Only Scan using idx_segments_ticket_fare on segments s (cost=0.56..278084.36 rows=2252784 width=14) (actual time=0.034..1746.359 rows=2250743 loops=1)
Index Cond: (fare_conditions = 'Business'::text)
Heap Fetches: 35563
CTE page_info
-> Result (cost=50687.65..50687.68 rows=1 width=8) (actual time=9966.901..9966.903 rows=1 loops=1)
InitPlan 2
-> Aggregate (cost=50687.64..50687.65 rows=1 width=8) (actual time=9966.889..9966.890 rows=1 loops=1)
-> CTE Scan on filtered_tickets filtered_tickets_1 (cost=0.00..45055.68 rows=2252784 width=0) (actual time=0.061..9801.079 rows=2158585 loops=1)
-> Nested Loop (cost=283326.03..336831.67 rows=100 width=92) (actual time=15724.580..16100.565 rows=100 loops=1)
-> CTE Scan on page_info (cost=0.00..0.02 rows=1 width=0) (actual time=9966.903..9966.904 rows=1 loops=1)
-> Hash Join (cost=283326.03..336830.65 rows=100 width=92) (actual time=5757.670..6133.639 rows=100 loops=1)
Hash Cond: (t.ticket_no = filtered_tickets.ticket_no)
-> CTE Scan on filtered_tickets t (cost=0.00..45055.68 rows=2252784 width=92) (actual time=0.001..241.766 rows=2158585 loops=1)
-> Hash (cost=283324.78..283324.78 rows=100 width=32) (actual time=5575.336..5575.340 rows=100 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
-> Limit (cost=283324.53..283324.78 rows=100 width=32) (actual time=5575.273..5575.298 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.001..0.003 rows=1 loops=1)
-> Sort (cost=282761.31..288393.27 rows=2252784 width=32) (actual time=5494.743..5543.951 rows=704900 loops=1)
Sort Key: filtered_tickets.ticket_no
Sort Method: top-N heapsort Memory: 15082kB
-> CTE Scan on filtered_tickets (cost=0.00..45055.68 rows=2252784 width=32) (actual time=0.001..312.698 rows=2158585 loops=1)
Planning Time: 0.981 ms
Execution TimХарактерные особенности тестовых запросов и планов выполнения
Как и в предыдущих статьях, для анализа результатов pg_expecto - используется нейросеть.
1. Общая цель запросов
Оба запроса решают задачу случайной пагинации (выбор случайной "страницы" из 100 строк) для отфильтрованных данных (билеты с бизнес-классом).
2. Основные различия в подходах
Первый запрос (ROW_NUMBER()):
Использует оконную функцию ROW_NUMBER() для нумерации всех строк
Фильтрует по диапазону номеров строк после нумерации
Ключевая проблема: Выполняет полную сортировку и нумерацию всех 2.15 млн строк
Второй запрос (Deferred Join):
Использует отложенное соединение (deferred join)
Сначала выбирает только ticket_no с помощью OFFSET/LIMIT
Затем соединяет остальные данные по отобранным ключам
Преимущество: Сортирует только ключи, а не все данные
3. Производительность
Первый запрос:
Время выполнения: 12.65 секунд
Основные затраты:
Merge Semi Join: 9.3 секунды
Сортировка 2.15 млн строк: 850-1000 мс (quicksort, 170 МБ памяти)
WindowAgg (оконная функция): 1723 мс
Второй запрос:
Время выполнения: ~16.1 секунды (неполные данные в выводе)
Основные затраты:
Merge Semi Join: 8.75 секунды
Сортировка для OFFSET: 5.5 секунды (top-N heapsort, 15 МБ памяти)
Hash Join: 5575 мс
4. Критические различия в планах
Сортировка:
Первый запрос: Сортирует все столбцы (ticket_no, book_ref, passenger_name) - 92 байта на строку
Второй запрос: Сортирует только ticket_no - 32 байта на строку
Использование памяти:
Первый: 170 МБ для сортировки всех данных
Второй: 15 МБ для сортировки только ключей
Фильтрация:
Первый: Сначала нумерует все строки, затем фильтрует 100
Второй: Сначала выбирает 100 ключей, затем соединяет данные
5. Проблемы обоих подходов
Общая проблема: floor(random() COUNT()/100) требует полного сканирования для подсчета строк
Производительность OFFSET: Во втором запросе OFFSET должен "пропустить" много строк (случайная страница * 100)
6. Вывод
Оба запроса имеют проблемы с производительностью из-за необходимости обработки всех 2.15 млн строк для случайной выборки.
Характерные особенности производительности СУБД
1. Производительность
ROW_NUMBER подход:
Начальная производительность: 60
Стабильная после 45 итерации: 96
Максимум: 96 (после адаптации)
Прирост: 60 → 96 (+60%)
Deferred Join подход:
Начальная производительность: 52
Стабильная после 55 итерации: 84
Максимум: 84
Прирост: 52 → 84 (+61.5%)
Вывод: ROW_NUMBER показывает на 14.3% выше абсолютную производительность (96 vs 84).
2. Динамика адаптации
ROW_NUMBER:
Быстрый рост до 96 за 55 итераций
Более резкие скачки производительности
Ранняя стабилизация (с 55 итерации)
Deferred Join:
Более плавный рост до 84
Дольше адаптируется (до 55 итерации)
Стабильнее на низких нагрузках
3. Ожидания и блокировки
ROW_NUMBER:
TIMEOUT появляются с итерации 46 (при нагрузке 10 соединений)
LWLOCK стабилизируются на уровне 3-6
WAITINGS: 4-7
Deferred Join:
IO ожидания появляются с итерации 73 (при нагрузке 15+ соединений)
LWLOCK: 4-8 (выше, чем у ROW_NUMBER)
WAITINGS: до 21 пикового значения
Более выраженные скачки в ожиданиях (74 итерация: 21 ожидание)
4. Корреляция с нагрузкой
Нагрузка растет от 5 до 22 соединений
Критические точки:
10 соединений (итерация 46): ROW_NUMBER начинает показывать TIMEOUT
15 соединений (итерация 73): Deferred Join показывает IO ожидания
18+ соединений: оба подхода стабилизируются на максимальных значениях ожиданий
5. Ключевые различия в поведении
ROW_NUMBER:
Выше пиковая производительность (96 vs 84)
Раннее появление TIMEOUT (с 10 соединений)
Меньше LWLOCK в среднем (3-6 vs 4-8)
Более предсказуемые паттерны ожиданий
Deferred Join:
Лучше масштабируется при низких нагрузках
Более стабильные WAITINGS (кроме пиков)
Появление IO ожиданий вместо TIMEOUT
Сильнее страдает от LWLOCK
6. Анализ проблемных точек
ROW_NUMBER:
Проблема: TIMEOUT при 10+ соединениях
Причина: WindowAgg + сортировка больших данных конкурируют за ресурсы
Симптом: Конкуренция за CPU/память
Deferred Join:
Проблема: IO ожидания при 15+ соединениях
Причина: Hash Join + сортировка для OFFSET требуют disk I/O
Симптом: Конкуренция за disk I/O и LWLOCK
7. Общий вывод
ROW_NUMBER лучше для:
Максимальной производительности (14.3% выше)
Систем с достаточными CPU/памятью
Сценариев с <10 конкурентных соединенийDeferred Join лучше для:
Систем с ограничениями по памяти
Сценариев с предсказуемыми IO паттернами
Приложений, чувствительных к TIMEOUTОба подхода требуют оптимизации при >10 конкурентных соединений
Критическая нагрузка: 10-15 соединений (точка деградации производительности)
Метрики производительности инфраструктуры
1. Использование CPU
ROW_NUMBER:
Начало: 61% user, 3% system, 36% idle
Критическая точка (итерация 50): резкий переход на 95-96% user, 4% system, 0% idle
Стабильная нагрузка: 96% user, 4% system, 0% idle
Вывод: Полная загрузка CPU с преобладанием пользовательского времени
Deferred Join:
Начало: 61% user, 2% system, 36% idle
Критическая точка (итерация 52): переход на 97% user, 3% system, 0% idle
Стабильная нагрузка: 97% user, 3% system, 0% idle
Вывод: Аналогичная полная загрузка, но меньше системного времени
Сравнение: Deferred Join показывает на 1% выше user CPU и на 1% ниже system CPU.
2. Использование памяти
ROW_NUMBER:
Свободная память: 1791 → 2753 KB (+53% рост)
Буферы: 47 → 6 KB (-87% снижение)
Кэш: 4955 → 2751 KB (-44% снижение)
Своп: 70 → 67 KB (минимальное изменение)
Вывод: Активное использование кэша и буферов
Deferred Join:
Свободная память: 4567 → 2207 KB (-51% снижение)
Буферы: 18 → 6 KB (-67% снижение)
Кэш: 2211 → 2728 KB (+23% рост)
Своп: 69 → 73 KB (незначительный рост)
Вывод: Увеличение использования кэша, снижение свободной памяти
Сравнение: ROW_NUMBER активнее использует кэш в начале, Deferred Join наращивает кэш в процессе.
3. Ввод-вывод (IO)
ROW_NUMBER:
io_bo: 52 → 58 (+11.5% рост)
Пиковые значения: до 59
Вывод: Умеренный рост IO при увеличении нагрузки
Deferred Join:
io_bo: 59 → 65 (+10% рост)
Пиковые значения: до 68
Вывод: Более высокий базовый уровень IO
Сравнение: Deferred Join имеет на 10-15% выше активность IO, что соответствует ожиданиям из предыдущего анализа (IO ожидания).
4. Системные метрики
ROW_NUMBER:
Прерывания (system_in): 5687 → 8279 (+45% рост)
Переключения контекста (system_cs): 657 → 1053 (+60% рост)
Процессы в run queue (procs_r): 6 → 15 (+150% рост)
Deferred Join:
Прерывания (system_in): 5652 → 8274 (+46% рост)
Переключения контекста (system_cs): 652 → 1063 (+63% рост)
Процессы в run queue (procs_r): 5 → 15 (+200% рост)
Сравнение: Оба подхода показывают схожий рост системных метрик, Deferred Join имеет немного больше переключений контекста.
5. Критические точки перехода
ROW_NUMBER:
Итерация 50 (нагрузка 10): переход на 0% idle CPU
Сопровождается: Ростом procs_r до 9
Симптом: Резкое изменение паттерна использования памяти
Deferred Join:
Итерация 52 (нагрузка 10): переход на 0% idle CPU
Сопровождается: Ростом procs_r до 9
Симптом: Снижение свободной памяти до 4000 KB
6. Корреляция с нагрузкой
5-8 соединений: Оба подхода работают с простоями CPU (36% idle)
10 соединений: Критическая точка (0% idle)
12-15 соединений: Стабилизация на максимальной нагрузке
18-22 соединений: Дальнейший рост системных метрик
7. Ключевые различия по метрикам
ROW_NUMBER преимущества:
Меньше IO операций (58 vs 68)
Лучшее использование памяти (растущая свободная память)
Более стабильный кэш (меньше колебаний)
Deferred Join преимущества:
Меньше системного времени CPU (3% vs 4%)
Более предсказуемое использование кэша (постепенный рост)
Меньше начальных буферов (более эффективное использование)
8. Проблемные паттерны
ROW_NUMBER:
Проблема: Резкое падение кэша с 4955 до 2751
Причина: Оконные функции требуют больших рабочих наборов
Риск: Потенциальные page faults при недостатке памяти
Deferred Join:
Проблема: Высокий базовый уровень IO (59 vs 52)
Причина: Hash Join и сортировка для OFFSET
Риск: Disk I/O bottlenecks при высокой нагрузке
9. Общие выводы по метрикам
Оба подхода CPU-bound: 0% idle при нагрузке ≥10 соединений
Deferred Join более IO-intensive: на 10-15% выше IO активность
ROW_NUMBER более memory-intensive: активнее использует кэш и буферы
Критическая нагрузка: 10 соединений для обоих подходов
Масштабирование: Deferred Join лучше сохраняет кэш, но требует больше IO
Общий итог
Выбор между подходами зависит от конфигурации системы -
ROW_NUMBER для CPU-обеспеченных архитектур
Deferred Join для систем с хорошим IO.
P.S. Классика, в качестве итога цикла экспериментов по пагинации
Паттерн "ROW_NUMBER" смог обойти по производительности паттерн "Differed Join".