28.11.2025 13:19
pg_expecto 0 998 Источник
Универсальных решений не существует. Никому не верь - всё проверяй !
Универсальных решений не существует. Никому не верь - всё проверяй !

Предисловие

Статья на Хабре "PostgreSQL Antipatterns: отказ от агрегатных функций = кратное ускорение" послужила отправной точкой для данного исследования. После ее изучения возникла гипотеза о возможности значительного повышения производительности PostgreSQL через замену агрегатных функций на конструкции ARRAY.

Однако, в статье не учитываются важные аспекты реальной эксплуатации, что и побудило провести комплексную проверку методики в условиях:

  1. Параллельной нагрузки - имитация реальной производственной среды

  2. Ресурсоемких запросов - обработка больших объемов данных

  3. Тестовой базы данных большого размера - "Демобаза 2.0" с объемом данных, соответствующим реальным проектам

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

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

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

  • Тестирование в условиях конкурентной параллельной нагрузки, а не одиночных запросов

  • Работа с базой данных большого объема, а не упрощенными тестовыми наборами

  • Анализ комплексного влияния на всю систему, а не только времени выполнения запроса

Тестовый запрос с агрегатной функцией MAX
WITH validities AS (
  SELECT validity, lower( validity ) AS validity_begin, upper( validity ) AS validity_end
  FROM ( SELECT DISTINCT validity FROM bookings.routes ) AS validities
), dates AS (
  SELECT validity, tz, dt::date, extract( isodow FROM dt ) AS dow, validity_begin, validity_end
  FROM validities v
    CROSS JOIN ( SELECT DISTINCT timezone FROM bookings.airports ) tz(tz) 
    CROSS JOIN generate_series(
        timezone( tz, v.validity_begin )::date::timestamp,
        timezone( tz, v.validity_end )::date::timestamp,
        '1 day'::interval ) dt
), schedules AS (
  SELECT r.route_no, a.timezone AS tz, r.validity, r.scheduled_time, unnest( r.days_of_week ) AS dow
  FROM bookings.routes r
    JOIN bookings.airports a ON a.airport_code = r.departure_airport
), dates_to_be AS (
  SELECT s.route_no, s.tz, s.validity, s.dow, timezone( s.tz, d.dt + s.scheduled_time ) AS scheduled_dep
  FROM schedules s
    JOIN dates d ON d.validity = s.validity AND d.tz = s.tz AND d.dow = s.dow
  WHERE timezone( s.tz, d.dt + s.scheduled_time ) >= d.validity_begin
    AND timezone( s.tz, d.dt + s.scheduled_time ) < d.validity_end
), absent AS (
  SELECT dtb.route_no AS route_no, dtb.tz, dtb.validity, dtb.scheduled_dep AS sched_dep_to_be
  FROM bookings.flights f
    JOIN bookings.routes r ON r.route_no = f.route_no AND r.validity @> f.scheduled_departure
    JOIN bookings.airports a ON a.airport_code = r.departure_airport
    RIGHT JOIN dates_to_be dtb ON dtb.route_no = f.route_no AND dtb.validity = r.validity AND dtb.scheduled_dep = f.scheduled_departure
  WHERE f.route_no IS NULL
    AND dtb.scheduled_dep < (SELECT max(scheduled_departure) FROM flights)
), excess AS (
  SELECT f.route_no, a.timezone, r.validity, f.scheduled_departure AS sched_dep_not_to_be
  FROM bookings.flights f
    JOIN bookings.routes r ON r.route_no = f.route_no AND r.validity @> f.scheduled_departure
    JOIN bookings.airports a ON a.airport_code = r.departure_airport
    LEFT JOIN dates_to_be dtb ON dtb.route_no = f.route_no AND dtb.validity = r.validity AND dtb.scheduled_dep = f.scheduled_departure
  WHERE dtb.route_no IS NULL
)
SELECT
  (SELECT count(*) FROM absent) absent_flights,
  (SELECT count(*) FROM excess) excess_flights,
  CASE
    WHEN (SELECT count(*) FROM absent) > 0 AND (SELECT count(*) FROM excess) > 0 THEN 'ERROR: absent and excess flights'
    WHEN (SELECT count(*) FROM absent) > 0 THEN 'ERROR: absent flights'
    WHEN (SELECT count(*) FROM excess) > 0 THEN 'ERROR: excess flights'
    ELSE 'Ok'
  END verdict;

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

  • Cost: 321155.20..321155.22

  • Actual time: 7098.553..7098.594 ms

План выполнения тестового запроса с агрегатной функцией MAX
 Result  (cost=321155.20..321155.22 rows=1 width=48) (actual time=7098.553..7098.594 rows=1 loops=1)
   CTE dates_to_be
     ->  Nested Loop  (cost=526.60..305422.97 rows=3218 width=56) (actual time=122.063..1036.124 rows=109241 loops=1)
           Join Filter: ((((unnest(r.days_of_week)))::numeric = EXTRACT(isodow FROM dt.dt)) AND (timezone(ml.timezone, ((dt.dt)::date + r.scheduled_time)) >= lower(routes.validity)) AND (timezone(ml.timezon
e, ((dt.dt)::date + r.scheduled_time)) < upper(routes.validity)))
           Rows Removed by Join Filter: 680711
           ->  Hash Join  (cost=526.58..1318.82 rows=5792 width=93) (actual time=60.819..117.054 rows=25118 loops=1)
                 Hash Cond: (ml.timezone = ml_1.timezone)
                 ->  Hash Join  (cost=294.82..1071.64 rows=5792 width=78) (actual time=52.662..96.762 rows=25118 loops=1)
                       Hash Cond: (r.validity = routes.validity)
                       ->  ProjectSet  (cost=0.29..484.61 rows=23168 width=56) (actual time=2.183..27.647 rows=25118 loops=1)
                             ->  Nested Loop  (cost=0.29..325.33 rows=5792 width=90) (actual time=2.173..16.775 rows=5792 loops=1)
                                   ->  Seq Scan on routes r  (cost=0.00..155.92 rows=5792 width=79) (actual time=0.977..5.227 rows=5792 loops=1)
                                   ->  Memoize  (cost=0.29..0.34 rows=1 width=19) (actual time=0.001..0.001 rows=1 loops=5792)
                                         Cache Key: r.departure_airport
                                         Cache Mode: logical
                                         Hits: 5719  Misses: 73  Evictions: 0  Overflows: 0  Memory Usage: 9kB
                                         ->  Index Scan using airports_data_pkey on airports_data ml  (cost=0.28..0.33 rows=1 width=19) (actual time=0.030..0.030 rows=1 loops=73)
                                               Index Cond: (airport_code = r.departure_airport)
                       ->  Hash  (cost=222.13..222.13 rows=5792 width=22) (actual time=50.411..50.413 rows=20 loops=1)
                             Buckets: 8192  Batches: 1  Memory Usage: 66kB
                             ->  HashAggregate  (cost=164.21..222.13 rows=5792 width=22) (actual time=50.321..50.382 rows=20 loops=1)
                                   Group Key: routes.validity
                                   Batches: 1  Memory Usage: 217kB
                                   ->  Index Only Scan using routes_route_no_validity_excl on routes  (cost=0.15..149.73 rows=5792 width=22) (actual time=40.852..47.890 rows=5792 loops=1)
                                         Heap Fetches: 0
                 ->  Hash  (cost=227.87..227.87 rows=311 width=15) (actual time=8.128..8.130 rows=311 loops=1)
                       Buckets: 1024  Batches: 1  Memory Usage: 23kB
                       ->  HashAggregate  (cost=224.76..227.87 rows=311 width=15) (actual time=7.998..8.044 rows=311 loops=1)
                             Group Key: ml_1.timezone
                             Batches: 1  Memory Usage: 61kB
                             ->  Seq Scan on airports_data ml_1  (cost=0.00..211.01 rows=5501 width=15) (actual time=0.881..6.353 rows=5501 loops=1)
           ->  Function Scan on generate_series dt  (cost=0.02..10.02 rows=1000 width=8) (actual time=0.013..0.015 rows=31 loops=25118)
   CTE absent
     ->  Nested Loop Anti Join  (cost=2519.30..4216.02 rows=1070 width=104) (actual time=6554.975..6554.983 rows=0 loops=1)
           InitPlan 2
             ->  Aggregate  (cost=2518.44..2518.45 rows=1 width=8) (actual time=47.305..47.307 rows=1 loops=1)
                   ->  Seq Scan on flights  (cost=0.00..2247.35 rows=108435 width=8) (actual time=0.749..33.984 rows=108435 loops=1)
           ->  CTE Scan on dates_to_be dtb  (cost=0.00..72.41 rows=1073 width=104) (actual time=169.383..1183.977 rows=108433 loops=1)
                 Filter: (scheduled_dep < (InitPlan 2).col1)
                 Rows Removed by Filter: 808
           ->  Nested Loop  (cost=0.85..1.50 rows=1 width=37) (actual time=0.049..0.049 rows=1 loops=108433)
                 ->  Nested Loop  (cost=0.56..1.20 rows=1 width=41) (actual time=0.046..0.046 rows=1 loops=108433)
                       Join Filter: (dtb.validity = r_1.validity)
                       ->  Index Only Scan using flights_route_no_scheduled_departure_key on flights f  (cost=0.42..1.02 rows=1 width=15) (actual time=0.005..0.005 rows=1 loops=108433)
                             Index Cond: ((route_no = dtb.route_no) AND (scheduled_departure = dtb.scheduled_dep))
                             Heap Fetches: 0
                       ->  Index Scan using routes_route_no_validity_excl on routes r_1  (cost=0.15..0.17 rows=1 width=33) (actual time=0.039..0.039 rows=1 loops=108433)
                             Index Cond: ((route_no = f.route_no) AND (validity @> f.scheduled_departure))
                 ->  Index Only Scan using airports_data_pkey on airports_data ml_2  (cost=0.28..0.30 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=108433)
                       Index Cond: (airport_code = r_1.departure_airport)
                       Heap Fetches: 0
   CTE excess
     ->  Nested Loop  (cost=121.39..11366.23 rows=1151 width=52) (actual time=543.514..543.526 rows=0 loops=1)
           ->  Hash Anti Join  (cost=121.09..11312.85 rows=1151 width=41) (actual time=543.513..543.522 rows=0 loops=1)
                 Hash Cond: ((f_1.route_no = dtb_1.route_no) AND (r_2.validity = dtb_1.validity) AND (f_1.scheduled_departure = dtb_1.scheduled_dep))
                 ->  Nested Loop  (cost=0.42..11171.32 rows=1176 width=41) (actual time=0.086..390.445 rows=108435 loops=1)
                       ->  Seq Scan on routes r_2  (cost=0.00..155.92 rows=5792 width=33) (actual time=0.024..1.198 rows=5792 loops=1)
                       ->  Index Only Scan using flights_route_no_scheduled_departure_key on flights f_1  (cost=0.42..1.89 rows=1 width=15) (actual time=0.036..0.064 rows=19 loops=5792)
                             Index Cond: (route_no = r_2.route_no)
                             Filter: (r_2.validity @> scheduled_departure)
                             Rows Removed by Filter: 184
                             Heap Fetches: 0
                 ->  Hash  (cost=64.36..64.36 rows=3218 width=72) (actual time=75.384..75.385 rows=109241 loops=1)
                       Buckets: 131072 (originally 4096)  Batches: 1 (originally 1)  Memory Usage: 8706kB
                       ->  CTE Scan on dates_to_be dtb_1  (cost=0.00..64.36 rows=3218 width=72) (actual time=0.027..26.568 rows=109241 loops=1)
           ->  Memoize  (cost=0.29..0.34 rows=1 width=19) (never executed)
                 Cache Key: r_2.departure_airport
                 Cache Mode: logical
                 ->  Index Scan using airports_data_pkey on airports_data ml_3  (cost=0.28..0.33 rows=1 width=19) (never executed)
                       Index Cond: (airport_code = r_2.departure_airport)
   InitPlan 5
     ->  Aggregate  (cost=24.08..24.09 rows=1 width=8) (actual time=6554.986..6554.987 rows=1 loops=1)
           ->  CTE Scan on absent  (cost=0.00..21.40 rows=1070 width=0) (actual time=6554.978..6554.978 rows=0 loops=1)
   InitPlan 6
     ->  Aggregate  (cost=25.90..25.91 rows=1 width=8) (actual time=543.534..543.534 rows=1 loops=1)
           ->  CTE Scan on excess  (cost=0.00..23.02 rows=1151 width=0) (actual time=543.517..543.517 rows=0 loops=1)
   InitPlan 7
     ->  Aggregate  (cost=24.08..24.09 rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=1)
           ->  CTE Scan on absent absent_1  (cost=0.00..21.40 rows=1070 width=0) (actual time=0.002..0.002 rows=0 loops=1)
   InitPlan 8
     ->  Aggregate  (cost=25.90..25.91 rows=1 width=8) (never executed)
           ->  CTE Scan on excess excess_1  (cost=0.00..23.02 rows=1151 width=0) (never executed)
   InitPlan 9
     ->  Aggregate  (cost=24.08..24.09 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=1)
           ->  CTE Scan on absent absent_2  (cost=0.00..21.40 rows=1070 width=0) (actual time=0.000..0.001 rows=0 loops=1)
   InitPlan 10
     ->  Aggregate  (cost=25.90..25.91 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1)
           ->  CTE Scan on excess excess_2  (cost=0.00..23.02 rows=1151 width=0) (actual time=0.000..0.001 rows=0 loops=1)
 Planning Time: 45.294 ms
 Execution Time: 7108.159 ms
(90 rows)
Тестовый запрос с паттерном ARRAY
WITH validities AS (
  SELECT 
    validity, 
    lower(validity) AS validity_begin, 
    upper(validity) AS validity_end
  FROM (SELECT DISTINCT validity FROM bookings.routes) AS validities
), 
dates AS (
  SELECT 
    validity, 
    tz, 
    dt::date, 
    extract(isodow FROM dt) AS dow, 
    validity_begin, 
    validity_end
  FROM validities v
  CROSS JOIN (SELECT DISTINCT timezone FROM bookings.airports) tz(tz)
  CROSS JOIN generate_series(
      timezone(tz, v.validity_begin)::date::timestamp,
      timezone(tz, v.validity_end)::date::timestamp,
      '1 day'::interval
  ) dt
), 
schedules AS (
  SELECT 
    r.route_no, 
    a.timezone AS tz, 
    r.validity, 
    r.scheduled_time, 
    unnest(r.days_of_week) AS dow
  FROM bookings.routes r
  JOIN bookings.airports a ON a.airport_code = r.departure_airport
), 
dates_to_be AS (
  SELECT 
    s.route_no, 
    s.tz, 
    s.validity, 
    s.dow, 
    timezone(s.tz, d.dt + s.scheduled_time) AS scheduled_dep
  FROM schedules s
  JOIN dates d ON d.validity = s.validity AND d.tz = s.tz AND d.dow = s.dow
  WHERE timezone(s.tz, d.dt + s.scheduled_time) >= d.validity_begin
    AND timezone(s.tz, d.dt + s.scheduled_time) < d.validity_end
),
flight_schedule_departures AS (
  -- Используем массив вместо MAX
  SELECT array_agg(scheduled_departure ORDER BY scheduled_departure DESC) AS dep_array
  FROM bookings.flights
),
absent AS (
  SELECT 
    dtb.route_no AS route_no, 
    dtb.tz, 
    dtb.validity, 
    dtb.scheduled_dep AS sched_dep_to_be
  FROM bookings.flights f
  JOIN bookings.routes r ON r.route_no = f.route_no AND r.validity @> f.scheduled_departure
  JOIN bookings.airports a ON a.airport_code = r.departure_airport
  RIGHT JOIN dates_to_be dtb ON dtb.route_no = f.route_no 
    AND dtb.validity = r.validity 
    AND dtb.scheduled_dep = f.scheduled_departure
  CROSS JOIN flight_schedule_departures fsd
  WHERE f.route_no IS NULL
    -- Берем первый элемент отсортированного массива (максимальный)
    AND dtb.scheduled_dep < (fsd.dep_array[1])
), 
excess AS (
  SELECT 
    f.route_no, 
    a.timezone, 
    r.validity, 
    f.scheduled_departure AS sched_dep_not_to_be
  FROM bookings.flights f
  JOIN bookings.routes r ON r.route_no = f.route_no AND r.validity @> f.scheduled_departure
  JOIN bookings.airports a ON a.airport_code = r.departure_airport
  LEFT JOIN dates_to_be dtb ON dtb.route_no = f.route_no 
    AND dtb.validity = r.validity 
    AND dtb.scheduled_dep = f.scheduled_departure
  WHERE dtb.route_no IS NULL
)
SELECT
  (SELECT count(*) FROM absent) AS absent_flights,
  (SELECT count(*) FROM excess) AS excess_flights,
  CASE
    WHEN (SELECT count(*) FROM absent) > 0 AND (SELECT count(*) FROM excess) > 0 
      THEN 'ERROR: absent and excess flights'
    WHEN (SELECT count(*) FROM absent) > 0 THEN 'ERROR: absent flights'
    WHEN (SELECT count(*) FROM excess) > 0 THEN 'ERROR: excess flights'
    ELSE 'Ok'
  END AS verdict;

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

  • Cost: 320322.22..320322.24

  • Actual time: 7273.048..7273.080 ms

План выполнения тестового запроса с паттерном ARRAY
Result  (cost=320322.22..320322.24 rows=1 width=48) (actual time=7273.048..7273.080 rows=1 loops=1)
   CTE dates_to_be
     ->  Nested Loop  (cost=382.21..305276.56 rows=3218 width=56) (actual time=6.424..799.256 rows=109241 loops=1)
           Join Filter: ((((unnest(r.days_of_week)))::numeric = EXTRACT(isodow FROM dt.dt)) AND (timezone(ml.timezone, ((dt.dt)::date + r.scheduled_time)) >= lower(routes.validity)) AND (timezone(ml.timezone, ((dt.dt)::date + r.scheduled
_time)) < upper(routes.validity)))
           Rows Removed by Join Filter: 680711
           ->  Hash Join  (cost=382.19..1172.41 rows=5792 width=93) (actual time=6.325..53.776 rows=25118 loops=1)
                 Hash Cond: (ml.timezone = ml_1.timezone)
                 ->  Hash Join  (cost=272.95..1047.75 rows=5792 width=78) (actual time=4.453..41.110 rows=25118 loops=1)
                       Hash Cond: (r.validity = routes.validity)
                       ->  ProjectSet  (cost=0.29..482.58 rows=23168 width=56) (actual time=0.141..19.832 rows=25118 loops=1)
                             ->  Nested Loop  (cost=0.29..323.30 rows=5792 width=90) (actual time=0.127..10.876 rows=5792 loops=1)
                                   ->  Seq Scan on routes r  (cost=0.00..155.92 rows=5792 width=79) (actual time=0.034..2.579 rows=5792 loops=1)
                                   ->  Memoize  (cost=0.29..0.32 rows=1 width=19) (actual time=0.001..0.001 rows=1 loops=5792)
                                         Cache Key: r.departure_airport
                                         Cache Mode: logical
                                         Hits: 5719  Misses: 73  Evictions: 0  Overflows: 0  Memory Usage: 9kB
                                         ->  Index Only Scan using idx_airports_data_code_timezone on airports_data ml  (cost=0.28..0.31 rows=1 width=19) (actual time=0.014..0.014 rows=1 loops=73)
                                               Index Cond: (airport_code = r.departure_airport)
                                               Heap Fetches: 0
                       ->  Hash  (cost=200.26..200.26 rows=5792 width=22) (actual time=4.276..4.278 rows=20 loops=1)
                             Buckets: 8192  Batches: 1  Memory Usage: 66kB
                             ->  HashAggregate  (cost=142.34..200.26 rows=5792 width=22) (actual time=4.202..4.264 rows=20 loops=1)
                                   Group Key: routes.validity
                                   Batches: 1  Memory Usage: 217kB
                                   ->  Index Only Scan using idx_routes_route_no_validity on routes  (cost=0.28..127.86 rows=5792 width=22) (actual time=0.023..1.780 rows=5792 loops=1)
                                         Heap Fetches: 0
                 ->  Hash  (cost=105.35..105.35 rows=311 width=15) (actual time=1.833..1.835 rows=311 loops=1)
                       Buckets: 1024  Batches: 1  Memory Usage: 23kB
                       ->  Unique  (cost=0.28..105.35 rows=311 width=15) (actual time=0.110..1.752 rows=311 loops=1)
                             ->  Index Only Scan using idx_airports_data_timezone on airports_data ml_1  (cost=0.28..91.60 rows=5501 width=15) (actual time=0.105..1.031 rows=5501 loops=1)
                                   Heap Fetches: 0
           ->  Function Scan on generate_series dt  (cost=0.02..10.02 rows=1000 width=8) (actual time=0.007..0.010 rows=31 loops=25118)
   CTE absent
     ->  Nested Loop Anti Join  (cost=2205.65..3530.57 rows=1070 width=104) (actual time=6061.647..6061.655 rows=0 loops=1)
           ->  Nested Loop  (cost=2204.81..2309.40 rows=1073 width=104) (actual time=44.269..950.579 rows=108433 loops=1)
                 Join Filter: (dtb.scheduled_dep < (array_agg(flights.scheduled_departure ORDER BY flights.scheduled_departure DESC))[1])
                 Rows Removed by Join Filter: 808
                 ->  Aggregate  (cost=2204.81..2204.82 rows=1 width=32) (actual time=37.831..37.833 rows=1 loops=1)
                       ->  Index Only Scan using idx_flights_scheduled_departure on flights  (cost=0.29..1933.72 rows=108435 width=8) (actual time=0.095..23.309 rows=108435 loops=1)
                             Heap Fetches: 0
                 ->  CTE Scan on dates_to_be dtb  (cost=0.00..64.36 rows=3218 width=104) (actual time=6.428..881.187 rows=109241 loops=1)
           ->  Nested Loop  (cost=0.85..1.13 rows=1 width=37) (actual time=0.047..0.047 rows=1 loops=108433)
                 ->  Nested Loop  (cost=0.56..0.82 rows=1 width=41) (actual time=0.043..0.043 rows=1 loops=108433)
                       Join Filter: (dtb.validity = r_1.validity)
                       ->  Index Only Scan using flights_route_no_scheduled_departure_key on flights f  (cost=0.42..0.64 rows=1 width=15) (actual time=0.004..0.004 rows=1 loops=108433)
                             Index Cond: ((route_no = dtb.route_no) AND (scheduled_departure = dtb.scheduled_dep))
                             Heap Fetches: 0
                       ->  Index Scan using routes_route_no_validity_excl on routes r_1  (cost=0.15..0.17 rows=1 width=33) (actual time=0.038..0.038 rows=1 loops=108433)
                             Index Cond: ((route_no = f.route_no) AND (validity @> f.scheduled_departure))
                 ->  Index Only Scan using airports_data_pkey on airports_data ml_2  (cost=0.28..0.30 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=108433)
                       Index Cond: (airport_code = r_1.departure_airport)
                       Heap Fetches: 0
   CTE excess
     ->  Nested Loop  (cost=121.26..11365.11 rows=1151 width=52) (actual time=1211.355..1211.358 rows=0 loops=1)
           ->  Hash Anti Join  (cost=120.97..11313.75 rows=1151 width=41) (actual time=1211.353..1211.355 rows=0 loops=1)
                 Hash Cond: ((f_1.route_no = dtb_1.route_no) AND (r_2.validity = dtb_1.validity) AND (f_1.scheduled_departure = dtb_1.scheduled_dep))
                 ->  Nested Loop  (cost=0.29..11172.22 rows=1176 width=41) (actual time=0.076..1055.050 rows=108435 loops=1)
                       ->  Seq Scan on routes r_2  (cost=0.00..155.92 rows=5792 width=33) (actual time=0.030..1.534 rows=5792 loops=1)
                       ->  Index Scan using idx_flights_route_no on flights f_1  (cost=0.29..1.89 rows=1 width=15) (actual time=0.061..0.179 rows=19 loops=5792)
                             Index Cond: (route_no = r_2.route_no)
                             Filter: (r_2.validity @> scheduled_departure)
                             Rows Removed by Filter: 184
                 ->  Hash  (cost=64.36..64.36 rows=3218 width=72) (actual time=69.346..69.347 rows=109241 loops=1)
                       Buckets: 131072 (originally 4096)  Batches: 1 (originally 1)  Memory Usage: 8706kB
                       ->  CTE Scan on dates_to_be dtb_1  (cost=0.00..64.36 rows=3218 width=72) (actual time=0.030..26.166 rows=109241 loops=1)
           ->  Memoize  (cost=0.29..0.32 rows=1 width=19) (never executed)
                 Cache Key: r_2.departure_airport
                 Cache Mode: logical
                 ->  Index Only Scan using idx_airports_data_code_timezone on airports_data ml_3  (cost=0.28..0.31 rows=1 width=19) (never executed)
                       Index Cond: (airport_code = r_2.departure_airport)
                       Heap Fetches: 0
   InitPlan 4
     ->  Aggregate  (cost=24.08..24.09 rows=1 width=8) (actual time=6061.655..6061.656 rows=1 loops=1)
           ->  CTE Scan on absent  (cost=0.00..21.40 rows=1070 width=0) (actual time=6061.649..6061.649 rows=0 loops=1)
   InitPlan 5
     ->  Aggregate  (cost=25.90..25.91 rows=1 width=8) (actual time=1211.364..1211.364 rows=1 loops=1)
           ->  CTE Scan on excess  (cost=0.00..23.02 rows=1151 width=0) (actual time=1211.357..1211.357 rows=0 loops=1)
   InitPlan 6
     ->  Aggregate  (cost=24.08..24.09 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=1)
           ->  CTE Scan on absent absent_1  (cost=0.00..21.40 rows=1070 width=0) (actual time=0.001..0.001 rows=0 loops=1)
   InitPlan 7
     ->  Aggregate  (cost=25.90..25.91 rows=1 width=8) (never executed)
           ->  CTE Scan on excess excess_1  (cost=0.00..23.02 rows=1151 width=0) (never executed)
   InitPlan 8
     ->  Aggregate  (cost=24.08..24.09 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=1)
           ->  CTE Scan on absent absent_2  (cost=0.00..21.40 rows=1070 width=0) (actual time=0.000..0.001 rows=0 loops=1)
   InitPlan 9
     ->  Aggregate  (cost=25.90..25.91 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1)
           ->  CTE Scan on excess excess_2  (cost=0.00..23.02 rows=1151 width=0) (actual time=0.001..0.001 rows=0 loops=1)
 Planning Time: 22.980 ms
 Execution Time: 7280.800 ms
(91 rows)

Анализ планов выполнения

1. Способ получения максимального значения

  • MAX вариант: Использует (SELECT max(scheduled_departure) FROM flights)

  • ARRAY вариант: Использует array_agg(... ORDER BY ... DESC)[1]

2. Стоимость операций

  • MAX: Общая стоимость 321155.22

  • ARRAY: Общая стоимость 320322.22 (немного ниже)

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

  • MAX: 7108.159 ms

  • ARRAY: 7280.800 ms (немного выше)

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

1. Оптимизация планировщика PostgreSQL

Оба подхода эффективно оптимизируются:

  • Подзапрос с MAX() выполняется один раз как InitPlan и результат кэшируется

  • array_agg() также выполняется один раз и результат используется повторно

2. Основные затраты в других операциях

Основное время выполнения (~95%) тратится на:

  • Nested Loop Anti Join в CTE absent

  • Hash Anti Join в CTE excess

  • Обработка большого количества строк (108433 в loops=1)

3. Схожая структура планов

Оба запроса имеют идентичную структуру основных операций:

  • Такие же join-стратегии

  • Аналогичные методы доска к данным

  • Схожие фильтры и условия

4. Минимальное влияние на общую производительность

Разница в получении максимального значения составляет менее 1% от общего времени выполнения, поскольку:

  • Обе операции выполняются один раз

  • Результат кэшируется и используется повторно

  • Основная нагрузка - обработка сотен тысяч строк в соединениях

Рекомендации для оптимизации

Для реального улучшения производительности следует сосредоточиться на:

  1. Индексах для условий соединения в CTE dates_to_be

  2. Оптимизации Nested Loop в CTE absent

  3. Уменьшении количества обрабатываемых строк на ранних этапах

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

Результаты нагрузочного тестирования

Производительность СУБД

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

  • Операционная скорость: разница между проходами составила 0.08%

  • Статистическая достоверность: результаты подтверждены многократными прогонами

  • Вывод pg_expecto: в условиях параллельной нагрузки и большого объема данных статистически значимого преимущества не обнаружено

Анализ метрик инфраструктуры

Нагрузка на CPU (данные vmstat, агрегированные pg_expecto):

  • Оба теста: 97-98% пользовательского времени (us), 2-3% системного (sy)

  • Процессы в состоянии выполнения: MAX - рост в 2.7 раза, ARRAY - рост в 2.8 раза

Использование памяти (мониторинг средствами pg_expecto):

  • Свободная память: MAX показал рост +47%, ARRAY - снижение на 53%

  • Вывод pg_expecto: подход с ARRAY потребляет значительно больше оперативной памяти, что критично при параллельной нагрузке

Ввод-вывод (I/O) - корреляция данных pg_expecto и vmstat:

  • Блоки вывода: MAX - минимальный рост, ARRAY - нестабильная нагрузка с пиками

  • Вывод pg_expecto: ARRAY демонстрирует более волатильный профиль I/O операций, что негативно сказывается на стабильности системы при параллельной работе

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

  • Прерывания и переключения контекста: оба подхода показывают схожие значения

Критический анализ исходных рекомендаций

Гипотеза о «кратном ускорении» при отказе от агрегатных функций не подтвердилась в условиях параллельной нагрузки и больших объёмов данных.

Подход с ARRAY не показал преимуществ в производительности, но привёл к повышенному потреблению памяти и нестабильности операций ввода-вывода.

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

На основе данных, полученных с помощью pg_expecto, сформулированы следующие выводы:

  1. Контекстная зависимость: Эффективность оптимизаций сильно зависит от схемы данных, объёмов и характера нагрузки.

  2. Ресурсный баланс: Использование ARRAY увеличивает потребление памяти без значимого выигрыша в скорости.

  3. Тестирование в реалистичных условиях: Рекомендуется проводить нагрузочное тестирование в среде, максимально приближенной к production, с использованием специализированных инструментов.

  4. Критическое восприятие рекомендаций: Даже авторитетные источники могут предлагать решения, неэффективные в конкретных условиях.

Заключение

Эксперимент показал, что универсальных решений для оптимизации PostgreSQL не существует. Каждая система требует индивидуального анализа и валидации гипотез в условиях реальной нагрузки. Слепое следование рекомендациям, не подкреплённым комплексным тестированием, может привести к неоптимальным результатам.

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

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