07.12.2025 08:17
pg_expecto 4 4800 Источник
ROW_NUMBER: там, где другие летят — он ползёт
ROW_NUMBER: там, где другие летят — он ползёт

Эффективная пагинация — не просто удобство, а необходимость. Анализ нагрузочного тестирования, метрик системы и планов выполнения показывает, что выбор неправильного метода может замедлить приложение в 15 раз, создавая катастрофическую нагрузку на СУБД. Одни методы работают с молниеносной скоростью, а другие превращаются в «убийц производительности».

Задача

Сравнить методы пагинации получения первой страницы размером 100 строк для SQL-запроса, позволяющего получить информацию для анализа:

  • Неполных бронирований

  • Билетов без привязки к рейсам

  • Рейсов без процедуры посадки

  • Статистики по незавершённым операциям

Исходный SQL-запрос

LEFT JOIN
WITH random_period AS (
    SELECT 
        start_date,
        start_date + INTERVAL '30 days' AS end_date
    FROM (
        SELECT 
            book_date AS start_date
        FROM bookings
        WHERE book_date <= (SELECT MAX(book_date) FROM bookings) - INTERVAL '30 days'
        ORDER BY RANDOM()
        LIMIT 1
    ) AS random_date
)
SELECT 
    b.book_ref,
    b.book_date,
    t.ticket_no,
    t.passenger_name,
    s.flight_id,
    f.status,
    f.scheduled_departure,
    f.actual_departure,
    bp.seat_no,
    bp.boarding_no,
    rp.start_date AS period_start,
    rp.end_date AS period_end
FROM random_period rp
CROSS JOIN LATERAL (
    SELECT *
    FROM bookings b
    WHERE b.book_date BETWEEN rp.start_date AND rp.end_date
) b
LEFT JOIN tickets t ON b.book_ref = t.book_ref
LEFT JOIN segments s ON t.ticket_no = s.ticket_no
LEFT JOIN flights f ON s.flight_id = f.flight_id
LEFT JOIN boarding_passes bp ON 
    s.ticket_no = bp.ticket_no 
    AND s.flight_id = bp.flight_id
ORDER BY b.book_date, t.ticket_no;

Подробности

https://habr.com/p/971606/

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

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

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

Исследованные методы пагинации (тестовые запросы и планы выполнения)

LIMIT
WITH random_period AS (
    SELECT 
        start_date,
        start_date + INTERVAL '30 days' AS end_date
    FROM (
        SELECT 
            book_date AS start_date
        FROM bookings
        WHERE book_date <= (SELECT MAX(book_date) FROM bookings) - INTERVAL '30 days'
        ORDER BY RANDOM()
        LIMIT 1
    ) AS random_date
)
SELECT 
    b.book_ref,
    b.book_date,
    t.ticket_no,
    t.passenger_name,
    s.flight_id,
    f.status,
    f.scheduled_departure,
    f.actual_departure,
    bp.seat_no,
    bp.boarding_no,
    rp.start_date AS period_start,
    rp.end_date AS period_end
FROM random_period rp
CROSS JOIN LATERAL (
    SELECT *
    FROM bookings b
    WHERE b.book_date BETWEEN rp.start_date AND rp.end_date
) b
LEFT JOIN tickets t ON b.book_ref = t.book_ref
LEFT JOIN segments s ON t.ticket_no = s.ticket_no
LEFT JOIN flights f ON s.flight_id = f.flight_id
LEFT JOIN boarding_passes bp ON 
    s.ticket_no = bp.ticket_no 
    AND s.flight_id = bp.flight_id
ORDER BY b.book_date, t.ticket_no
LIMIT 100;

 Limit  (cost=66433.47..66583.84 rows=100 width=93) (actual time=4680.875..4730.341 rows=100 loops=1)
   CTE random_period
     ->  Subquery Scan on random_date  (cost=66429.70..66429.71 rows=1 width=16) (actual time=2855.224..2855.233 rows=1 loops=1)
           ->  Limit  (cost=66429.70..66429.70 rows=1 width=16) (actual time=2855.211..2855.218 rows=1 loops=1)
                 InitPlan 2
                   ->  Result  (cost=0.45..0.46 rows=1 width=8) (actual time=0.062..0.064 rows=1 loops=1)
                         InitPlan 1
                           ->  Limit  (cost=0.43..0.45 rows=1 width=8) (actual time=0.059..0.060 rows=1 loops=1)
                                 ->  Index Only Scan Backward using idx_bookings_book_date on bookings  (cost=0.43..128154.91 rows=7113192 width=8) (actual time=0.058..0.058 rows=1 loops=1)
                                       Heap Fetches: 0
                 ->  Sort  (cost=66429.24..72356.90 rows=2371064 width=16) (actual time=2855.210..2855.212 rows=1 loops=1)
                       Sort Key: (random())
                       Sort Method: top-N heapsort  Memory: 25kB
                       ->  Index Only Scan using idx_bookings_book_date on bookings bookings_1  (cost=0.43..54573.91 rows=2371064 width=16) (actual time=0.175..1589.643 rows=6894590 loops=1)
                             Index Cond: (book_date <= ((InitPlan 2).col1 - '30 days'::interval))
                             Heap Fetches: 0
   ->  Incremental Sort  (cost=3.76..3441408.68 rows=2288717 width=93) (actual time=4680.874..4730.319 rows=100 loops=1)
         Sort Key: b.book_date, t.ticket_no
         Presorted Key: b.book_date
         Full-sort Groups: 4  Sort Method: quicksort  Average Memory: 29kB  Peak Memory: 29kB
         ->  Nested Loop Left Join  (cost=2.29..3326972.83 rows=2288717 width=93) (actual time=4659.703..4730.047 rows=104 loops=1)
               ->  Nested Loop Left Join  (cost=1.73..1954951.97 rows=2288717 width=100) (actual time=4657.982..4725.300 rows=104 loops=1)
                     ->  Nested Loop Left Join  (cost=1.43..1869885.54 rows=2288717 width=76) (actual time=4654.305..4701.630 rows=104 loops=1)
                           ->  Nested Loop Left Join  (cost=0.87..788348.77 rows=1730639 width=58) (actual time=4651.619..4698.103 rows=87 loops=1)
                                 ->  Nested Loop  (cost=0.43..355611.45 rows=790355 width=31) (actual time=4649.883..4650.092 rows=42 loops=1)
                                       Join Filter: ((b.book_date >= rp.start_date) AND (b.book_date <= rp.end_date))
                                       Rows Removed by Join Filter: 1684634
                                       ->  Index Scan using idx_bookings_book_date on bookings b  (cost=0.43..177781.64 rows=7113192 width=15) (actual time=0.094..1002.530 rows=1684676 loops=1)
                                       ->  CTE Scan on random_period rp  (cost=0.00..0.02 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=1684676)
                                 ->  Index Scan using idx_tickets_book_ref on tickets t  (cost=0.43..0.52 rows=3 width=34) (actual time=1.139..1.141 rows=2 loops=42)
                                       Index Cond: (book_ref = b.book_ref)
                           ->  Index Only Scan using segments_pkey on segments s  (cost=0.56..0.60 rows=2 width=18) (actual time=0.039..0.039 rows=1 loops=87)
                                 Index Cond: (ticket_no = t.ticket_no)
                                 Heap Fetches: 0
                     ->  Memoize  (cost=0.30..0.32 rows=1 width=28) (actual time=0.227..0.227 rows=1 loops=104)
                           Cache Key: s.flight_id
                           Cache Mode: logical
                           Hits: 41  Misses: 63  Evictions: 0  Overflows: 0  Memory Usage: 8kB
                           ->  Index Scan using flights_pkey on flights f  (cost=0.29..0.31 rows=1 width=28) (actual time=0.327..0.327 rows=1 loops=63)
                                 Index Cond: (flight_id = s.flight_id)
               ->  Index Scan using boarding_passes_pkey on boarding_passes bp  (cost=0.56..0.60 rows=1 width=25) (actual time=0.044..0.044 rows=1 loops=104)
                     Index Cond: ((ticket_no = s.ticket_no) AND (flight_id = s.flight_id))
 Planning Time: 13.899 ms
 Execution Time: 4731.761 ms
FETCH
WITH random_period AS (
    SELECT 
        start_date,
        start_date + INTERVAL '30 days' AS end_date
    FROM (
        SELECT 
            book_date AS start_date
        FROM bookings
        WHERE book_date <= (SELECT MAX(book_date) FROM bookings) - INTERVAL '30 days'
        ORDER BY RANDOM()
        LIMIT 1
    ) AS random_date
)
SELECT 
    b.book_ref,
    b.book_date,
    t.ticket_no,
    t.passenger_name,
    s.flight_id,
    f.status,
    f.scheduled_departure,
    f.actual_departure,
    bp.seat_no,
    bp.boarding_no,
    rp.start_date AS period_start,
    rp.end_date AS period_end
FROM random_period rp
CROSS JOIN LATERAL (
    SELECT *
    FROM bookings b
    WHERE b.book_date BETWEEN rp.start_date AND rp.end_date
) b
LEFT JOIN tickets t ON b.book_ref = t.book_ref
LEFT JOIN segments s ON t.ticket_no = s.ticket_no
LEFT JOIN flights f ON s.flight_id = f.flight_id
LEFT JOIN boarding_passes bp ON 
    s.ticket_no = bp.ticket_no 
    AND s.flight_id = bp.flight_id
ORDER BY b.book_date, t.ticket_no
FETCH FIRST 100 ROWS ONLY;

 Limit  (cost=66433.47..66583.84 rows=100 width=93) (actual time=9916.798..9924.414 rows=100 loops=1)
   CTE random_period
     ->  Subquery Scan on random_date  (cost=66429.70..66429.71 rows=1 width=16) (actual time=2730.613..2730.622 rows=1 loops=1)
           ->  Limit  (cost=66429.70..66429.70 rows=1 width=16) (actual time=2730.601..2730.608 rows=1 loops=1)
                 InitPlan 2
                   ->  Result  (cost=0.45..0.46 rows=1 width=8) (actual time=0.058..0.062 rows=1 loops=1)
                         InitPlan 1
                           ->  Limit  (cost=0.43..0.45 rows=1 width=8) (actual time=0.055..0.057 rows=1 loops=1)
                                 ->  Index Only Scan Backward using idx_bookings_book_date on bookings  (cost=0.43..128154.91 rows=7113192 width=8) (actual time=0.055..0.056 rows=1 loops=1)
                                       Heap Fetches: 0
                 ->  Sort  (cost=66429.24..72356.90 rows=2371064 width=16) (actual time=2730.600..2730.601 rows=1 loops=1)
                       Sort Key: (random())
                       Sort Method: top-N heapsort  Memory: 25kB
                       ->  Index Only Scan using idx_bookings_book_date on bookings bookings_1  (cost=0.43..54573.91 rows=2371064 width=16) (actual time=0.137..1580.152 rows=6894590 loops=1)
                             Index Cond: (book_date <= ((InitPlan 2).col1 - '30 days'::interval))
                             Heap Fetches: 0
   ->  Incremental Sort  (cost=3.76..3441408.68 rows=2288717 width=93) (actual time=9916.783..9924.379 rows=100 loops=1)
         Sort Key: b.book_date, t.ticket_no
         Presorted Key: b.book_date
         Full-sort Groups: 4  Sort Method: quicksort  Average Memory: 29kB  Peak Memory: 29kB
         ->  Nested Loop Left Join  (cost=2.29..3326972.83 rows=2288717 width=93) (actual time=9914.639..9924.139 rows=101 loops=1)
               ->  Nested Loop Left Join  (cost=1.73..1954951.97 rows=2288717 width=100) (actual time=9913.623..9920.012 rows=101 loops=1)
                     ->  Nested Loop Left Join  (cost=1.43..1869885.54 rows=2288717 width=76) (actual time=9910.603..9915.937 rows=101 loops=1)
                           ->  Nested Loop Left Join  (cost=0.87..788348.77 rows=1730639 width=58) (actual time=9909.638..9913.502 rows=75 loops=1)
                                 ->  Nested Loop  (cost=0.43..355611.45 rows=790355 width=31) (actual time=9908.194..9908.314 rows=38 loops=1)
                                       Join Filter: ((b.book_date >= rp.start_date) AND (b.book_date <= rp.end_date))
                                       Rows Removed by Join Filter: 6219779
                                       ->  Index Scan using idx_bookings_book_date on bookings b  (cost=0.43..177781.64 rows=7113192 width=15) (actual time=0.096..3899.508 rows=6219817 loops=1)
                                       ->  CTE Scan on random_period rp  (cost=0.00..0.02 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=6219817)
                                 ->  Index Scan using idx_tickets_book_ref on tickets t  (cost=0.43..0.52 rows=3 width=34) (actual time=0.134..0.135 rows=2 loops=38)
                                       Index Cond: (book_ref = b.book_ref)
                           ->  Index Only Scan using segments_pkey on segments s  (cost=0.56..0.60 rows=2 width=18) (actual time=0.031..0.031 rows=1 loops=75)
                                 Index Cond: (ticket_no = t.ticket_no)
                                 Heap Fetches: 0
                     ->  Memoize  (cost=0.30..0.32 rows=1 width=28) (actual time=0.040..0.040 rows=1 loops=101)
                           Cache Key: s.flight_id
                           Cache Mode: logical
                           Hits: 19  Misses: 82  Evictions: 0  Overflows: 0  Memory Usage: 11kB
                           ->  Index Scan using flights_pkey on flights f  (cost=0.29..0.31 rows=1 width=28) (actual time=0.010..0.010 rows=1 loops=82)
                                 Index Cond: (flight_id = s.flight_id)
               ->  Index Scan using boarding_passes_pkey on boarding_passes bp  (cost=0.56..0.60 rows=1 width=25) (actual time=0.039..0.040 rows=1 loops=101)
                     Index Cond: ((ticket_no = s.ticket_no) AND (flight_id = s.flight_id))
 Planning Time: 11.676 ms
 Execution Time: 9925.704 ms
ROW_NUMBER
WITH random_period AS (
    SELECT 
        start_date,
        start_date + INTERVAL '30 days' AS end_date
    FROM (
        SELECT 
            book_date AS start_date
        FROM bookings
        WHERE book_date <= (SELECT MAX(book_date) FROM bookings) - INTERVAL '30 days'
        ORDER BY RANDOM()
        LIMIT 1
    ) AS random_date
),
numbered_rows AS (
    SELECT 
        b.book_ref,
        b.book_date,
        t.ticket_no,
        t.passenger_name,
        s.flight_id,
        f.status,
        f.scheduled_departure,
        f.actual_departure,
        bp.seat_no,
        bp.boarding_no,
        rp.start_date AS period_start,
        rp.end_date AS period_end,
        ROW_NUMBER() OVER (ORDER BY b.book_date, t.ticket_no) AS rn
    FROM random_period rp
    CROSS JOIN LATERAL (
        SELECT *
        FROM bookings b
        WHERE b.book_date BETWEEN rp.start_date AND rp.end_date
    ) b
    LEFT JOIN tickets t ON b.book_ref = t.book_ref
    LEFT JOIN segments s ON t.ticket_no = s.ticket_no
    LEFT JOIN flights f ON s.flight_id = f.flight_id
    LEFT JOIN boarding_passes bp ON 
        s.ticket_no = bp.ticket_no 
        AND s.flight_id = bp.flight_id
)
SELECT 
    book_ref,
    book_date,
    ticket_no,
    passenger_name,
    flight_id,
    status,
    scheduled_departure,
    actual_departure,
    seat_no,
    boarding_no,
    period_start,
    period_end
FROM numbered_rows
WHERE rn <= 100
ORDER BY rn;

 Sort  (cost=2905200.29..2910922.08 rows=2288717 width=101) (actual time=41630.589..41630.615 rows=100 loops=1)
   Sort Key: (row_number() OVER (?))
   Sort Method: quicksort  Memory: 36kB
   CTE random_period
     ->  Subquery Scan on random_date  (cost=66429.70..66429.71 rows=1 width=16) (actual time=2743.988..2743.997 rows=1 loops=1)
           ->  Limit  (cost=66429.70..66429.70 rows=1 width=16) (actual time=2743.977..2743.983 rows=1 loops=1)
                 InitPlan 2
                   ->  Result  (cost=0.45..0.46 rows=1 width=8) (actual time=0.083..0.084 rows=1 loops=1)
                         InitPlan 1
                           ->  Limit  (cost=0.43..0.45 rows=1 width=8) (actual time=0.080..0.081 rows=1 loops=1)
                                 ->  Index Only Scan Backward using idx_bookings_book_date on bookings  (cost=0.43..128154.91 rows=7113192 width=8) (actual time=0.079..0.079 rows=1 loops=1)
                                       Heap Fetches: 0
                 ->  Sort  (cost=66429.24..72356.90 rows=2371064 width=16) (actual time=2743.975..2743.976 rows=1 loops=1)
                       Sort Key: (random())
                       Sort Method: top-N heapsort  Memory: 25kB
                       ->  Index Only Scan using idx_bookings_book_date on bookings bookings_1  (cost=0.43..54573.91 rows=2371064 width=16) (actual time=0.225..1597.230 rows=6894590 loops=1)
                             Index Cond: (book_date <= ((InitPlan 2).col1 - '30 days'::interval))
                             Heap Fetches: 0
   ->  WindowAgg  (cost=2477925.75..2523700.07 rows=2288717 width=101) (actual time=41630.432..41630.509 rows=100 loops=1)
         Run Condition: (row_number() OVER (?) <= 100)
         ->  Sort  (cost=2477925.73..2483647.52 rows=2288717 width=93) (actual time=41630.415..41630.441 rows=101 loops=1)
               Sort Key: b.book_date, t.ticket_no
               Sort Method: external merge  Disk: 133696kB
               ->  Hash Left Join  (cost=685483.12..2046557.76 rows=2288717 width=93) (actual time=31077.081..40116.074 rows=1270849 loops=1)
                     Hash Cond: (s.flight_id = f.flight_id)
                     ->  Merge Right Join  (cost=681880.33..2036946.86 rows=2288717 width=69) (actual time=31019.028..39381.457 rows=1270849 loops=1)
                           Merge Cond: (s.ticket_no = t.ticket_no)
                           ->  Merge Left Join  (cost=1.12..1264914.18 rows=20598448 width=25) (actual time=2.025..14806.724 rows=2691914 loops=1)
                                 Merge Cond: ((s.ticket_no = bp.ticket_no) AND (s.flight_id = bp.flight_id))
                                 ->  Index Only Scan using segments_pkey on segments s  (cost=0.56..424624.50 rows=20598448 width=18) (actual time=1.182..958.718 rows=2691914 loops=1)
                                       Heap Fetches: 0
                                 ->  Index Scan using boarding_passes_pkey on boarding_passes bp  (cost=0.56..637940.93 rows=19870556 width=25) (actual time=0.836..11643.525 rows=2691914 loops=1)
                           ->  Materialize  (cost=681879.21..690532.40 rows=1730639 width=58) (actual time=22408.387..23182.812 rows=1270849 loops=1)
                                 ->  Sort  (cost=681879.21..686205.81 rows=1730639 width=58) (actual time=22408.379..22951.105 rows=933895 loops=1)
                                       Sort Key: t.ticket_no
                                       Sort Method: external merge  Disk: 63448kB
                                       ->  Nested Loop Left Join  (cost=0.87..464448.59 rows=1730639 width=58) (actual time=2746.476..19723.076 rows=933895 loops=1)
                                             ->  Nested Loop  (cost=0.43..31711.28 rows=790355 width=31) (actual time=2745.245..3191.752 rows=417283 loops=1)
                                                   ->  CTE Scan on random_period rp  (cost=0.00..0.02 rows=1 width=16) (actual time=2743.993..2744.000 rows=1 loops=1)
                                                   ->  Index Scan using idx_bookings_book_date on bookings b  (cost=0.43..23807.71 rows=790355 width=15) (actual time=1.243..361.761 rows=417283 loops=1)
                                                         Index Cond: ((book_date >= rp.start_date) AND (book_date <= rp.end_date))
                                             ->  Index Scan using idx_tickets_book_ref on tickets t  (cost=0.43..0.52 rows=3 width=34) (actual time=0.038..0.039 rows=2 loops=417283)
                                                   Index Cond: (book_ref = b.book_ref)
                     ->  Hash  (cost=2247.35..2247.35 rows=108435 width=28) (actual time=57.865..57.867 rows=108435 loops=1)
                           Buckets: 131072  Batches: 1  Memory Usage: 7377kB
                           ->  Seq Scan on flights f  (cost=0.00..2247.35 rows=108435 width=28) (actual time=0.032..24.961 rows=108435 loops=1)
 Planning Time: 11.232 ms
 Execution Time: 41687.558 ms
OFFSET
WITH random_period AS (
    SELECT 
        start_date,
        start_date + INTERVAL '30 days' AS end_date
    FROM (
        SELECT 
            book_date AS start_date
        FROM bookings
        WHERE book_date <= (SELECT MAX(book_date) FROM bookings) - INTERVAL '30 days'
        ORDER BY RANDOM()
        LIMIT 1
    ) AS random_date
)
SELECT 
    b.book_ref,
    b.book_date,
    t.ticket_no,
    t.passenger_name,
    s.flight_id,
    f.status,
    f.scheduled_departure,
    f.actual_departure,
    bp.seat_no,
    bp.boarding_no,
    rp.start_date AS period_start,
    rp.end_date AS period_end
FROM random_period rp
CROSS JOIN LATERAL (
    SELECT *
    FROM bookings b
    WHERE b.book_date BETWEEN rp.start_date AND rp.end_date
) b
LEFT JOIN tickets t ON b.book_ref = t.book_ref
LEFT JOIN segments s ON t.ticket_no = s.ticket_no
LEFT JOIN flights f ON s.flight_id = f.flight_id
LEFT JOIN boarding_passes bp ON 
    s.ticket_no = bp.ticket_no 
    AND s.flight_id = bp.flight_id
ORDER BY b.book_date, t.ticket_no
OFFSET 0 ROWS
FETCH FIRST 100 ROWS ONLY;

 Limit  (cost=66433.47..66583.83 rows=100 width=93) (actual time=8109.469..8139.696 rows=100 loops=1)
   CTE random_period
     ->  Subquery Scan on random_date  (cost=66429.70..66429.71 rows=1 width=16) (actual time=2902.525..2902.533 rows=1 loops=1)
           ->  Limit  (cost=66429.70..66429.70 rows=1 width=16) (actual time=2902.515..2902.520 rows=1 loops=1)
                 InitPlan 2
                   ->  Result  (cost=0.45..0.46 rows=1 width=8) (actual time=0.621..0.624 rows=1 loops=1)
                         InitPlan 1
                           ->  Limit  (cost=0.43..0.45 rows=1 width=8) (actual time=0.617..0.618 rows=1 loops=1)
                                 ->  Index Only Scan Backward using idx_bookings_book_date on bookings  (cost=0.43..128154.91 rows=7113192 width=8) (actual time=0.616..0.617 rows=1 loops=1)
                                       Heap Fetches: 0
                 ->  Sort  (cost=66429.24..72356.90 rows=2371064 width=16) (actual time=2902.512..2902.513 rows=1 loops=1)
                       Sort Key: (random())
                       Sort Method: top-N heapsort  Memory: 25kB
                       ->  Index Only Scan using idx_bookings_book_date on bookings bookings_1  (cost=0.43..54573.91 rows=2371064 width=16) (actual time=0.724..1752.423 rows=6894590 loops=1)
                             Index Cond: (book_date <= ((InitPlan 2).col1 - '30 days'::interval))
                             Heap Fetches: 0
   ->  Incremental Sort  (cost=3.76..3441334.90 rows=2288717 width=93) (actual time=8109.467..8139.673 rows=100 loops=1)
         Sort Key: b.book_date, t.ticket_no
         Presorted Key: b.book_date
         Full-sort Groups: 4  Sort Method: quicksort  Average Memory: 29kB  Peak Memory: 29kB
         ->  Nested Loop Left Join  (cost=2.29..3326899.05 rows=2288717 width=93) (actual time=8086.849..8139.450 rows=101 loops=1)
               ->  Nested Loop Left Join  (cost=1.73..1954878.19 rows=2288717 width=100) (actual time=8086.773..8137.565 rows=101 loops=1)
                     ->  Nested Loop Left Join  (cost=1.43..1869885.25 rows=2288717 width=76) (actual time=8083.238..8121.675 rows=101 loops=1)
                           ->  Nested Loop Left Join  (cost=0.87..788348.48 rows=1730639 width=58) (actual time=8083.110..8120.860 rows=82 loops=1)
                                 ->  Nested Loop  (cost=0.43..355611.17 rows=790355 width=31) (actual time=8081.365..8081.512 rows=40 loops=1)
                                       Join Filter: ((b.book_date >= rp.start_date) AND (b.book_date <= rp.end_date))
                                       Rows Removed by Join Filter: 4682404
                                       ->  Index Scan using idx_bookings_book_date on bookings b  (cost=0.43..177781.36 rows=7113192 width=15) (actual time=0.631..2943.459 rows=4682444 loops=1)
                                       ->  CTE Scan on random_period rp  (cost=0.00..0.02 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=4682444)
                                 ->  Index Scan using idx_tickets_book_ref on tickets t  (cost=0.43..0.52 rows=3 width=34) (actual time=0.981..0.982 rows=2 loops=40)
                                       Index Cond: (book_ref = b.book_ref)
                           ->  Index Only Scan using segments_pkey on segments s  (cost=0.56..0.60 rows=2 width=18) (actual time=0.008..0.009 rows=1 loops=82)
                                 Index Cond: (ticket_no = t.ticket_no)
                                 Heap Fetches: 0
                     ->  Memoize  (cost=0.30..0.32 rows=1 width=28) (actual time=0.156..0.156 rows=1 loops=101)
                           Cache Key: s.flight_id
                           Cache Mode: logical
                           Hits: 29  Misses: 72  Evictions: 0  Overflows: 0  Memory Usage: 9kB
                           ->  Index Scan using flights_pkey on flights f  (cost=0.29..0.31 rows=1 width=28) (actual time=0.178..0.178 rows=1 loops=72)
                                 Index Cond: (flight_id = s.flight_id)
               ->  Index Scan using boarding_passes_pkey on boarding_passes bp  (cost=0.56..0.60 rows=1 width=25) (actual time=0.017..0.017 rows=1 loops=101)
                     Index Cond: ((ticket_no = s.ticket_no) AND (flight_id = s.flight_id))
 Planning Time: 62.729 ms
 Execution Time: 8140.876 ms

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

График изменения операционной скорости в ходе нагрузочного тестирования
График изменения операционной скорости в ходе нагрузочного тестирования
График изменения операционной скорости в ходе нагрузочного тестирования для методов LIMIT, FETCH , OFFSET
График изменения операционной скорости в ходе нагрузочного тестирования для методов LIMIT, FETCH , OFFSET

Результат:

  1. Метод ROW_NUMBER показал кардинально низкую производительность.

  2. Зависимость производительности от метода пагинации, для методов LIMIT/FETCH/OFFSET - практически отсутствует.

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

Общая структура запросов

Все запросы выполняют одну и ту же задачу:

  1. Выбирают случайный 30-дневный период из таблицы bookings

  2. Находят все бронирования за этот период

  3. Собирают связанные данные из tickets, segments, flights, boarding_passes

  4. Сортируют по book_date, ticket_no

  5. Возвращают только первые 100 строк

Различия в способах ограничения результата:

1. Запрос с LIMIT

ORDER BY b.book_date, t.ticket_no
LIMIT 100;

Характеристики плана:

  • Время выполнения: 4.73 секунды

  • Стратегия: Incremental Sort с ранней остановкой

  • Особенности:
    Использует Presorted Key: b.book_date для оптимизации сортировки
    Обрабатывает только необходимое количество строк для достижения лимита
    Full-sort Groups: 4 - потребовалось отсортировать только 4 группы
    Join Filter удаляет много строк (Rows Removed by Join Filter: 1,684,634)
    Использует Memoize для кэширования результатов flights

2. Запрос с FETCH

ORDER BY b.book_date, t.ticket_no
FETCH FIRST 100 ROWS ONLY;

Характеристики плана:

  • Время выполнения: 9.93 секунды

  • Стратегия: Аналогична LIMIT, но дольше из-за большего объема данных

  • Особенности:
    Обрабатывает больше строк в Index Scan (6219,817 vs 1684676 у LIMIT)
    Join Filter удаляет 6219779 строк
    Та же стратегия Incremental Sort с Full-sort Groups: 4

3. Запрос с ROW_NUMBER

ROW_NUMBER() OVER (ORDER BY b.book_date, t.ticket_no) AS rn
WHERE rn <= 100

Характеристики плана:

  • Время выполнения: 41.69 секунды (в 8-10 раз медленнее!)

  • Стратегия: Кардинально отличается - полное вычисление всех строк

  • Принципиальные различия:
    WindowAgg вычисляет ROW_NUMBER для всех 1,270,849 строк
    Run Condition отфильтровывает после вычисления
    Использует Hash Join и Merge Join вместо Nested Loop
    Внешняя сортировка на диске: Sort Method: external merge Disk: 133696kB
    Материализация промежуточных результатов с записью на диск

4. Запрос с OFFSET

ORDER BY b.book_date, t.ticket_no
OFFSET 0 ROWS
FETCH FIRST 100 ROWS ONLY;

Характеристики плана:

  • Время выполнения: 8.14 секунды

  • Стратегия: Идентична FETCH, но с дополнительным узлом OFFSET

  • Особенности:
    OFFSET 0 не добавляет накладных расходов
    Обрабатывает 4,682,444 строк в Index Scan
    Join Filter удаляет 4,682,404 строк

Ключевые выводы и рекомендации:

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

  1. ROW_NUMBER vs LIMIT/FETCH:
    ROW_NUMBER вычисляет все строки, затем фильтрует - O(n)
    LIMIT/FETCH используют early termination - останавливаются после нахождения 100 строк
    Разница в производительности: 41.69с vs 4.73-9.93с (в 4-9 раз!)

  2. Проблемы с Join Filter:
    Во всех планах (кроме ROW_NUMBER) используется Join Filter с большим количеством удаляемых строк
    Это указывает на неоптимальное соединение: условие BETWEEN применяется как фильтр, а не как индексное условие

  3. Различия в стратегиях соединения:
    LIMIT/FETCH/OFFSET: Nested Loop с индексами (быстро для первых строк)
    ROW_NUMBER: Hash/Merge Join (эффективны для полных сканирований, но не для лимитированных выборок)

Вывод:

Для ограничения выборки всегда предпочитайте LIMIT или FETCH над ROW_NUMBER().

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

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

1. ROW_NUMBER - катастрофическая производительность

  • SPEED: Всего 14-16 (на порядок ниже других запросов)

  • WAITINGS: Чрезвычайно высокие значения (26830-42065)

  • IO: Высокие значения (14545-18510)

  • IPC: Очень высокие (12227-23436) - интенсивное межпроцессное взаимодействие

  • LWLOCK: Растёт от 46 до 102 - проблемы с блокировками

  • TIMEOUT: 12-17 - наличие таймаутов

Вывод: Запрос с ROW_NUMBER работает в 14-15 раз медленнее и создаёт огромную нагрузку на систему.

2. LIMIT, FETCH FIRST, OFFSET - стабильная высокая производительность

Эти три метода показывают схожую производительность:

  • SPEED: 202-206

  • WAITINGS: 106-131 (FETCH FIRST имеет немного меньше ожиданий)

  • IO: 104-128

  • LWLOCK: 2-3 (минимальные блокировки)

  • TIMEOUT: 0-1 (почти нет таймаутов)

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

  1. ROW_NUMBER абсолютно неэффективен для лимитированных выборок:
    Создает огромную нагрузку на систему
    Вызывает чрезмерные ожидания и блокировки
    Имеет на порядок худшую производительность

  2. FETCH FIRST показывает лучшие показатели по ожиданиям и IO:
    На 12% меньше WAITINGS чем LIMIT
    На 12% меньше IO чем LIMIT

  3. OFFSET 0 не добавляет накладных расходов:
    Производительность сопоставима с LIMIT/FETCH
    Незначительно лучше по WAITINGS/IO чем LIMIT

  4. Стабильность системы:
    Запросы с LIMIT/FETCH/OFFSET не создают проблем с блокировками
    ROW_NUMBER вызывает рост LWLOCK в 50 раз больше

Рекомендации:

  1. НИКОГДА не используйте ROW_NUMBER для простого ограничения выборки
    Используйте только когда нужна нумерация всех строк

  2. Предпочитайте FETCH FIRST над LIMIT:
    Лучшие показатели WAITINGS и IO
    Более стандартизированный SQL синтаксис

  3. Используйте OFFSET только при необходимости пропуска строк:
    OFFSET 0 безопасен, но OFFSET > 0 будет читать и отбрасывать строки

  4. Мониторинг LWLOCK:
    Рост LWLOCK в ROW_NUMBER указывает на проблемы с конкурентным доступом
    Остальные запросы работают без блокировочных проблем

Итог: Для ограничения выборки в PostgreSQL используйте FETCH FIRST (или LIMIT), избегайте ROW_NUMBER. Разница в производительности достигает 15 раз, что подтверждается как планами выполнения, так и метриками нагрузочного тестирования.

Метрики производительности подсистемы IO

Все метрики iostat абсолютно одинаковы для всех четырёх типов запросов
LIMIT, FETCH FIRST, ROW_NUMBER и OFFSET показывают идентичные значения
Это свидетельствует о том, что дисковые операции не являются дифференцирующим фактором в производительности этих запросов .

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

1. Данные полностью в памяти (In-Memory)

  • Все запросы работают с данными, находящимися в кэше PostgreSQL (shared_buffers) или операционной системы

  • Отсутствие операций чтения/записи на диск объясняет почему метрики одинаковы

2. Производительность определяется НЕ дисковыми операциями

Разница в производительности между запросами (особенно ROW_NUMBER vs остальные) объясняется:

  • Разными алгоритмами обработки данных в PostgreSQL

  • Разными планами выполнения (как видно из EXPLAIN ANALYZE)

  • Разной нагрузкой на CPU и память

3. Запрос ROW_NUMBER медленнее не из-за I/O

Несмотря на катастрофическую разницу в производительности (ROW_NUMBER в 15 раз медленнее), iostat не показывает различий
Это подтверждает, что проблема ROW_NUMBER - в вычислительной сложнос��и алгоритма, а не в дисковой подсистеме.

Основной вывод:

Разница в производительности между LIMIT/FETCH/OFFSET и ROW_NUMBER полностью обусловлена алгоритмической сложностью и внутренней обработкой данных PostgreSQL, а не дисковой подсистемой.

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

1. Общая картина по CPU

  • Для LIMIT, FETCH FIRST, OFFSET:
    В начале теста (cpu_id ≈ 36%, cpu_us ≈ 63%) — система находится в умеренной нагрузке.
    Начиная примерно с 50-й точки наблюдения происходит резкий переход:
    cpu_id падает до 0%.
    cpu_us возрастает до 98–99%.
    system_in (количество прерываний) резко увеличивается.
    Это указывает на рост нагрузки , который полностью загружает CPU в пользовательском режиме.

  • Для ROW_NUMBER:
    Нагрузка на CPU более стабильна:
    cpu_us ≈ 37–56%.
    cpu_id ≈ 16–44%.
    Появляется значительный cpu_wa (wait I/O) — от 11% до 20%.
    Это говорит о том, что запрос интенсивно работает с диском, а не только с CPU.

2. Очередь процессов (procs_r)

  • Во всех тестах, кроме ROW_NUMBER, procs_r увеличивается с 6–7 до 10–15 в момент пиковой нагрузки.

  • Это означает, что процессы начинают накапливаться в очереди на выполнение, так как CPU перегружен.

  • В ROW_NUMBER очередь процессов меньше (procs_r ≈ 4–6), но есть процессы в состоянии procs_b (блокированные на I/O).

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

  • memory_swpd почти не меняется во всех тестах (~215), что говорит об отсутствии активного своппинга.

  • memory_free немного уменьшается в ходе теста, но остаётся стабильной.

  • memory_cache постепенно снижается, что может указывать на использование кэша для дисковых операций (особенно в ROW_NUMBER).

4. Дисковый ввод-вывод (I/O)

  • Для ROW_NUMBER:
    io_bi (блоков ввода) достигает 73 954.
    io_bo (блоков вывода) достигает 6 069.
    Это подтверждает интенсивную работу с диском, что согласуется с высоким cpu_wa.

  • Для остальных запросов:
    I/O очень низкий (io_bi < 200, io_bo < 60), что говорит о том, что они работают в памяти.

5. Системные события (system_in, system_cs)

  • В LIMIT, FETCH FIRST, OFFSET наблюдается резкий скачок system_in (прерываний) в момент роста нагрузки .

  • system_cs (контекстны�� переключений) также возрастает, но не так сильно.

  • В ROW_NUMBER значения system_in и system_cs стабильно высоки, что связано с активным I/O.

Выводы:

  1. Запросы LIMIT, FETCH FIRST, OFFSET ведут себя схоже:
    Начинаются с умеренной нагрузки.
    Затем резко переключаются в режим полной загрузки CPU.
    I/O почти нет, работа идёт в памяти.

  2. Запрос ROW_NUMBER:
    Нагружает не только CPU, но и диск.
    Вызывает высокий I/O wait.
    Вероятно, использует временные таблицы или сортировку на диске.

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

Использование ROW_NUMBER() для разбиения данных на страницы при высокой одновременной нагрузке на базу данных неэффективно и не рекомендуется — этот метод создаёт значительную нагрузку на систему и резко снижает скорость работы.

Что касается получения первой страницы результатов, разница в скорости между методами LIMIT, FETCH FIRST и OFFSET практически незаметна — все они работают быстро и стабильно.

Выбор между этими тремя методами чаще всего зависит не от производительности, а от удобства реализации и необходимости поддержки перехода к следующим страницам данных.

Комментарии (4)


  1. Akina
    07.12.2025 10:52
    #29217698

    LIMIT, FETCH FIRST, OFFSET

    Вы располагаете информацией, что реализация указанных конструкций в чём-то различается? Потому как всё, что написано в документации, наводит на мысль, что это всё разные синтаксические формы одной и той же сущности. И ещё неизвестно, что из них чему есть синтаксический сахар.

    Кстати, а почему тестируется связка OFFSET+FETCH, но не тестируется связка OFFSET+LIMIT? Нет, я понимаю, что четвёртый раз делать одно и то же странно, но чисто в порядке строгости эксперимента...


  1. ProfBiss
    07.12.2025 10:52
    #29217774

    А где самая эффективная связка?

    limit + where по колонкам из order


    1. pg_expecto Автор
      07.12.2025 10:52
      #29217792

      Самая неэффективная - row_number.

      Разница между другими способами - в рамках статистической погрешности

      Что касается получения первой страницы результатов, разница в скорости между методами LIMIT, FETCH FIRST и OFFSET практически незаметна — все они работают быстро и стабильно.

      Выбор между этими тремя методами чаще всего зависит не от производительности, а от удобства реализации и необходимости поддержки перехода к следующим страницам данных.


  1. Kuklachev
    07.12.2025 10:52
    #29217798

    Это как будто и так очевидно было изначально. Ведь row_number - это по сути отдельный запрос. И понятно, что он будет обрабатывать всю таблицу целиком