Анализ вариантов оптимизации ресурсоёмкого SQL-запроса: Вариант-4 «Временная таблица» / Хабр

Не верь на слово, проверь под давлением.

Предисловие

Оптимизировать запрос в вакууме — просто. Но как он поведет себя, когда десятки таких же запросов одновременно борются за ресурсы?

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

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

Тестовая ВМ: CPU = 8 / RAM = 8GB
Инструмент нагрузочного тестирования и статистического анализа производительности СУБД: pg_expecto
Тестовая база данных: "Демобаза 2.0" (большой размер, сложная схема)
Условия тестирования: параллельная нагрузка, ресурсоемкий запрос
СУБД: PostgreSQL 17

Конфигурационные параметры СУБД

track_io_timing = 'on'
listen_addresses = '0.0.0.0'
logging_collector = 'on'
log_directory = '/log/pg_log'
log_destination = 'stderr'
log_rotation_size = '0'
log_rotation_age = '1d'
log_filename = 'postgresql-%u.log'
log_line_prefix = '%m| %d| %a| %u| %h| %p| %e| '
log_truncate_on_rotation = 'on'
log_checkpoints = 'on'
archive_mode = 'on'
archive_command = 'true'
archive_timeout = '30min'
checkpoint_timeout = '15min'
checkpoint_warning = '60'
checkpoint_completion_target = '0.9'
min_wal_size = '2GB'
max_wal_size = '8GB'
synchronous_commit = 'on'
wal_compression = 'on'
random_page_cost = '1.1'
effective_io_concurrency = '300'
wal_sender_timeout = '0'
autovacuum_naptime = '1s'
autovacuum_vacuum_scale_factor = '0.01'
autovacuum_analyze_scale_factor = '0.005'
autovacuum_vacuum_cost_delay = '2ms'
autovacuum_max_workers = '4'
autovacuum_work_mem = '256MB'
vacuum_cost_limit = '4000'
bgwriter_delay = '10ms'
bgwriter_lru_multiplier = '4'
bgwriter_lru_maxpages = '400'
max_locks_per_transaction = '256'
max_pred_locks_per_transaction = '256'
shared_buffers = '1919MB'
effective_cache_size = '5757MB'
work_mem = '14MB'
temp_buffers = '14MB'
maintenance_work_mem = '479MB'
max_worker_processes = '8'
max_parallel_workers = '8'
max_parallel_workers_per_gather = '4'
idle_in_transaction_session_timeout = '1h'
statement_timeout = '8h'
pg_stat_statements.track_utility = 'off'
max_parallel_maintenance_workers = '4'
hash_mem_multiplier = '2'
autovacuum_vacuum_insert_scale_factor = '0.01'
shared_preload_libraries = 'pg_stat_statements , pg_wait_sampling'
commit_delay = '1000'
log_autovacuum_min_duration = '0'
wipe_file_on_delete = 'on'
wipe_heaptuple_on_delete = 'on'
wipe_mem_on_free = 'on'
wipe_memctx_on_free = 'on'
wipe_xlog_on_free = 'on'
log_connections = 'on'
log_disconnections = 'on'
pg_stat_statements.track = 'all'
max_connections = '1000'

План нагрузочного тестирования (param.conf)

# НАСТРОЙКИ НАГРУЗОЧНОГО ТЕСТИРОВАНИЯ
# Максимальная нагрузка
finish_load = 20
# Тестовая БД
testdb = demo
# Веса сценариев
scenario1 = 1.0

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

Тестовый запрос, используемый в качестве baseline : LEFT JOIN

Запрос демонстрирует:

LEFT JOIN bookings → tickets
Все бронирования, даже без привязанных билетов
LEFT JOIN tickets → segments
Все билеты, даже без перелётов
LEFT JOIN segments → flights
Все перелёты, даже без информации о рейсах
LEFT JOIN segments → boarding_passes
Все перелёты, даже без посадочных талонов
Выбирает случайную дату из существующих бронирований
Гарантирует, что период полностью входит в доступный диапазон дат

Такой запрос может быть полезен для анализа:

Неполных бронирований
Билетов без привязки к рейсам
Рейсов без процедуры посадки
Статистики по незавершённым операциям

Таблицы, используемые в тестовом запросе

demo=# \d bookings
                    Unlogged table "bookings.bookings"
    Column    |           Type           | Collation | Nullable | Default
--------------+--------------------------+-----------+----------+---------
 book_ref     | character(6)             |           | not null |
 book_date    | timestamp with time zone |           | not null |
 total_amount | numeric(10,2)            |           | not null |
Indexes:
    "bookings_pkey" PRIMARY KEY, btree (book_ref)
    "idx_bookings_book_date" btree (book_date)
Referenced by:
    TABLE "tickets" CONSTRAINT "tickets_book_ref_fkey" FOREIGN KEY (book_ref) REFERENCES bookings(book_ref)

demo=# \d 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)
    "tickets_book_ref_passenger_id_outbound_key" UNIQUE CONSTRAINT, btree (book_ref, passenger_id, outbound)
Foreign-key constraints:
    "tickets_book_ref_fkey" FOREIGN KEY (book_ref) REFERENCES bookings(book_ref)
Referenced by:
    TABLE "segments" CONSTRAINT "segments_ticket_no_fkey" FOREIGN KEY (ticket_no) REFERENCES tickets(ticket_no)

demo=#  \d 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_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)

demo=# \d boarding_passes
                 Unlogged table "bookings.boarding_passes"
    Column     |           Type           | Collation | Nullable | Default
---------------+--------------------------+-----------+----------+---------
 ticket_no     | text                     |           | not null |
 flight_id     | integer                  |           | not null |
 seat_no       | text                     |           | not null |
 boarding_no   | integer                  |           |          |
 boarding_time | timestamp with time zone |           |          |
Indexes:
    "boarding_passes_pkey" PRIMARY KEY, btree (ticket_no, flight_id)
    "boarding_passes_flight_id_boarding_no_key" UNIQUE CONSTRAINT, btree (flight_id, boarding_no)
    "boarding_passes_flight_id_boarding_time" btree (flight_id, boarding_time)
    "boarding_passes_flight_id_seat_no_key" UNIQUE CONSTRAINT, btree (flight_id, seat_no)
Foreign-key constraints:
    "boarding_passes_ticket_no_flight_id_fkey" FOREIGN KEY (ticket_no, flight_id) REFERENCES segments(ticket_no, flight_id)

demo=# \d flights
                                  Unlogged table "bookings.flights"
       Column        |           Type           | Collation | Nullable |           Default
---------------------+--------------------------+-----------+----------+------------------------------
 flight_id           | integer                  |           | not null | generated always as identity
 route_no            | text                     |           | not null |
 status              | text                     |           | not null |
 scheduled_departure | timestamp with time zone |           | not null |
 scheduled_arrival   | timestamp with time zone |           | not null |
 actual_departure    | timestamp with time zone |           |          |
 actual_arrival      | timestamp with time zone |           |          |
Indexes:
    "flights_pkey" PRIMARY KEY, btree (flight_id)
    "flights_route_no_scheduled_departure_key" UNIQUE CONSTRAINT, btree (route_no, scheduled_departure)
    "idx_flights_route_no" btree (route_no)
    "idx_flights_scheduled_departure" btree (scheduled_departure DESC)
Check constraints:
    "flight_actual_check" CHECK (actual_arrival IS NULL OR actual_departure IS NOT NULL AND actual_arrival IS NOT NULL AND actual_arrival > actual_departure)
    "flight_scheduled_check" CHECK (scheduled_arrival > scheduled_departure)
    "flight_status_check" CHECK (status = ANY (ARRAY['Scheduled'::text, 'On Time'::text, 'Delayed'::text, 'Boarding'::text, 'Departed'::text, 'Arrived'::text, 'Cancelled'::text]))
Referenced by:
    TABLE "segments" CONSTRAINT "segments_flight_id_fkey" FOREIGN KEY (flight_id) REFERENCES flights(flight_id)

Тестовый запрос - 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;

План выполнения тестового запроса - LEFT JOIN

Sort  (cost=2544355.52..2550077.32 rows=2288717 width=93) (actual time=58778.563..59050.999 rows=1146771 loops=1)
   Sort Key: b.book_date, t.ticket_no
   Sort Method: external merge  Disk: 121832kB
   CTE random_period
     ->  Subquery Scan on random_date  (cost=66429.70..66429.71 rows=1 width=16) (actual time=2883.937..2883.945 rows=1 loops=1)
           ->  Limit  (cost=66429.70..66429.70 rows=1 width=16) (actual time=2883.919..2883.925 rows=1 loops=1)
                 InitPlan 2
                   ->  Result  (cost=0.45..0.46 rows=1 width=8) (actual time=1.994..1.996 rows=1 loops=1)
                         InitPlan 1
                           ->  Limit  (cost=0.43..0.45 rows=1 width=8) (actual time=1.989..1.991 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=1.988..1.989 rows=1 loops=1)
                                       Heap Fetches: 0
                 ->  Sort  (cost=66429.24..72356.90 rows=2371064 width=16) (actual time=2883.917..2883.918 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=2.183..1714.099 rows=6894590 loops=1)
                             Index Cond: (book_date <= ((InitPlan 2).col1 - '30 days'::interval))
                             Heap Fetches: 0
   ->  Hash Left Join  (cost=685482.92..2046557.85 rows=2288717 width=93) (actual time=49378.694..57307.629 rows=1146771 loops=1)
         Hash Cond: (s.flight_id = f.flight_id)
         ->  Merge Right Join  (cost=681880.13..2036946.95 rows=2288717 width=69) (actual time=49326.107..56627.660 rows=1146771 loops=1)
               Merge Cond: (s.ticket_no = t.ticket_no)
               ->  Merge Left Join  (cost=1.12..1264914.46 rows=20598448 width=25) (actual time=3.919..32375.016 rows=6564405 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=3.875..1950.364 rows=6564405 loops=1)
                           Heap Fetches: 0
                     ->  Index Scan using boarding_passes_pkey on boarding_passes bp  (cost=0.56..637941.24 rows=19870556 width=25) (actual time=0.033..25169.883 rows=6564405 loops=1)
               ->  Materialize  (cost=681879.01..690532.20 rows=1730639 width=58) (actual time=21308.253..22022.391 rows=1146771 loops=1)
                     ->  Sort  (cost=681879.01..686205.61 rows=1730639 width=58) (actual time=21308.245..21812.491 rows=862459 loops=1)
                           Sort Key: t.ticket_no
                           Sort Method: external merge  Disk: 58600kB
                           ->  Nested Loop Left Join  (cost=0.87..464448.40 rows=1730639 width=58) (actual time=2886.101..18909.459 rows=862459 loops=1)
                                 ->  Nested Loop  (cost=0.43..31711.08 rows=790355 width=31) (actual time=2884.013..3268.567 rows=397632 loops=1)
                                       ->  CTE Scan on random_period rp  (cost=0.00..0.02 rows=1 width=16) (actual time=2883.941..2883.946 rows=1 loops=1)
                                       ->  Index Scan using idx_bookings_book_date on bookings b  (cost=0.43..23807.51 rows=790355 width=15) (actual time=0.063..308.659 rows=397632 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.038 rows=2 loops=397632)
                                       Index Cond: (book_ref = b.book_ref)
         ->  Hash  (cost=2247.35..2247.35 rows=108435 width=28) (actual time=52.383..52.385 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.024..22.614 rows=108435 loops=1)
 Planning Time: 37.456 ms
 Execution Time: 59151.413 ms

Эксперимент-1 : вариант с использованием EXISTS

Эксперимент-2 : вариант с оптимизацией структуры запроса (TUNING)

Эксперимент-3 : Частичная агрегация

Эксперимент-4 «Временная таблица»

Временная таблица

-- Создаем временную таблицу для отфильтрованных бронирований
CREATE TEMPORARY TABLE temp_filtered_bookings AS
WITH random_period AS (
    SELECT 
        book_date AS start_date,
        book_date + INTERVAL '30 days' AS end_date
    FROM bookings 
    ORDER BY RANDOM() 
    LIMIT 1
)
SELECT b.*
FROM bookings b
CROSS JOIN random_period rp
WHERE b.book_date BETWEEN rp.start_date AND rp.end_date;

-- Создаем индексы на временной таблице
CREATE INDEX tmp_idx_book_ref ON temp_filtered_bookings (book_ref);
CREATE INDEX tmp_idx_book_date ON temp_filtered_bookings (book_date);

Тестовый запрос - TEMP TABLE

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
FROM temp_filtered_bookings 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;

План выполнения тестового запроса -TEMP TABLE

 Sort  (cost=2211998.73..2215844.96 rows=1538491 width=98) (actual time=34246.862..34542.140 rows=1211093 loops=1)
   Sort Key: b.book_date, t.ticket_no
   Sort Method: external merge  Disk: 109672kB
   ->  Hash Left Join  (cost=579129.28..1925563.26 rows=1538491 width=98) (actual time=23831.991..32643.918 rows=1211093 loops=1)
         Hash Cond: (s.flight_id = f.flight_id)
         ->  Merge Right Join  (cost=575526.49..1917921.78 rows=1538491 width=74) (actual time=23774.473..31882.934 rows=1211093 loops=1)
               Merge Cond: (s.ticket_no = t.ticket_no)
               ->  Merge Left Join  (cost=1.12..1264914.56 rows=20598448 width=25) (actual time=2.411..22735.924 rows=3430362 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.704..1121.887 rows=3430362 loops=1)
                           Heap Fetches: 0
                     ->  Index Scan using boarding_passes_pkey on boarding_passes bp  (cost=0.56..637941.32 rows=19870556 width=25) (actual time=0.698..18861.672 rows=3430362 loops=1)
               ->  Materialize  (cost=575525.37..581342.10 rows=1163347 width=63) (actual time=6939.314..7652.509 rows=1211093 loops=1)
                     ->  Sort  (cost=575525.37..578433.73 rows=1163347 width=63) (actual time=6939.289..7437.005 rows=902132 loops=1)
                           Sort Key: t.ticket_no
                           Sort Method: external merge  Disk: 47192kB
                           ->  Nested Loop Left Join  (cost=0.43..432700.18 rows=1163347 width=63) (actual time=0.175..4432.691 rows=902132 loops=1)
                                 ->  Seq Scan on temp_filtered_bookings b  (cost=0.00..6791.41 rows=410341 width=36) (actual time=0.096..71.373 rows=410341 loops=1)
                                 ->  Index Scan using idx_tickets_book_ref on tickets t  (cost=0.43..1.01 rows=3 width=34) (actual time=0.009..0.010 rows=2 loops=410341)
                                       Index Cond: (book_ref = b.book_ref)
         ->  Hash  (cost=2247.35..2247.35 rows=108435 width=28) (actual time=57.236..57.238 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.055..24.930 rows=108435 loops=1)
 Planning Time: 42.697 ms
 Execution Time: 34655.641 ms

Дополнение

Временная таблица temp_filtered_bookings пересоздается перед выполнением тестового запроса.

Тестовый сценарий scenario1.sql

-- scenario1.sql
-- TEMP TABLES
CREATE OR REPLACE FUNCTION scenario1() 
RETURNS integer AS $$
DECLARE 
    test_rec record;
    original_app_name text;
    temp_table_name text;
BEGIN 
    -- Сохраняем оригинальное application_name
    original_app_name := current_setting('application_name');
    
    -- Генерируем уникальное имя временной таблицы
    temp_table_name := 'temp_filtered_bookings_' || pg_backend_pid();
    
    -- Устанавливаем уникальное имя приложения для сессии
    PERFORM set_config('application_name', 'scenario1_' || pg_backend_pid(), false);
    
    -- Создаем временную таблицу с уникальным именем
    EXECUTE format('
        CREATE TEMP TABLE %I ON COMMIT DROP AS 
        WITH random_period AS (
            SELECT 
                book_date AS start_date,
                book_date + INTERVAL ''30 days'' AS end_date
            FROM bookings 
            ORDER BY RANDOM() 
            LIMIT 1
        )
        SELECT b.* 
        FROM bookings b 
        CROSS JOIN random_period rp
        WHERE b.book_date BETWEEN rp.start_date AND rp.end_date
    ', temp_table_name);
    
    -- Создаем индексы
    EXECUTE format('CREATE INDEX ON %I (book_ref)', temp_table_name);
    EXECUTE format('CREATE INDEX ON %I (book_date)', temp_table_name);
    
    EXECUTE format('
        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
        FROM %I 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
    ', temp_table_name) 
	INTO test_rec;
    
    -- Восстанавливаем оригинальное application_name
    PERFORM set_config('application_name', original_app_name, false);
    
    RETURN 0;
    
EXCEPTION
    WHEN OTHERS THEN
        -- Восстанавливаем application_name в случае ошибки
        PERFORM set_config('application_name', original_app_name, false);
        RAISE;
END;
$$ LANGUAGE plpgsql;

Сравнительный анализ планов выполнения запросов

Ключевые различия:

1.Подход к работе с данными:

TEMP TABLE: Использует временную таблицу с предварительной фильтрацией и индексами

2.Использование ресурсов

TEMP TABLE: Меньше дисковых операций сортировки (109,672 kB)

Оба запроса используют external merge сортировку из-за большого объема данных

3.Операции в плане

TEMP TABLE: Разделяет логику - сначала фильтрация во временную таблицу, затем JOIN

4.Чтение данных

TEMP TABLE: Работает с уже отфильтрованными строками во временной таблице

Итоговый вывод:

TEMP TABLE показывает лучшую производительность благодаря:

Разделению ответственности: Фильтрация данных выполняется один раз и сохраняется
Эффективному использованию индексов: Индексы на временной таблице ускоряют JOIN операции
Уменьшению сложности запроса: Основной запрос становится проще для оптимизатора
Снижению стоимости случайного выбора: ORDER BY RANDOM() выполняется один раз при создании временной таблицы

Подход TEMP TABLE более масштабируем и предсказуем по производительности, особенно при работе с большими объемами данных.

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

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

График изменения операционной скорости в ходе нагрузочного тестирования при использоваении тестового запроса "JOIN" и "TEMP TABLE"

График изменения относительной разницы операционной скорости для запроса "TEMP TABLE" по сравнению с тестовым запросом "JOIN"

Среднее увеличение операционной скорости при использовании запроса "TEMP TABLE" составило 14.62%

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

График изменения ожиданий СУБД в ходе нагрузочного тестирования при использоваении тестового запроса "JOIN" и "TEMP TABLE"

Сравнительный анализ wait_event_type

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

Ожидания ввода-вывода (IO):
В обоих типах запросов наблюдаются высокие и растущие значения IO.
Для TEMP TABLE максимальное значение IO достигает 22882, для LEFT JOIN — 15183.
Это указывает на значительную нагрузку на систему ввода-вывода в обоих случаях.
Межпроцессное взаимодействие (IPC):
В LEFT JOIN значения IPC значительно выше (до 24107) и демонстрируют устойчивый рост.
В TEMP TABLE - IPC ниже (до 5870) и растет медленнее.
Это говорит о том, что LEFT JOIN сильнее нагружает механизмы IPC.
Легковесные блокировки (LWLOCK):
В TEMP TABLE значения LWLOCK значительно выше (до 311) и растут быстрее.
В LEFT JOIN LWLOCK ниже (до 106) и увеличиваются умеренно.
Использование временных таблиц приводит к более активному использованию легковесных блокировок.
Обычные блокировки (LOCK):
Присутствуют только в TEMP TABLE, но после определенного момента становятся нулевыми.
Это может означать, что начальные этапы работы с временными таблицами требуют блокировок, которые затем отпускаются.
Таймауты (TIMEOUT):
В LEFT JOIN таймауты выше (до 19) и растут постепенно.
В TEMP TABLE таймауты ниже (до 8) и остаются стабильными.
Это может указывать на более предсказуемое выполнение временных таблиц.

Выводы:

Запросы LEFT JOIN создают высокую нагрузку на IPC и IO, с умеренным ростом легковесных блокировок и таймаутов.
Запросы TEMP TABLE вызывают значительный рост легковесных блокировок (LWLOCK), но при этом IPC и таймауты ниже. Наличие LOCK только на начальном этапе может свидетельствовать об особенностях аллокации временных объектов.

Метрики iostat для дискового устройства, используемого файловой системой /data

Дисковая подсистема не участвует в ограничении производительности данных запросов.

Метрики vmstat

1. Процессы:

"LEFT JOIN": Количество работающих процессов (procs_r) растёт до 9, есть процессы в ожидании (procs_b до 2).
"TEMP TABLE": procs_r доходит до 13, что указывает на более высокую конкуренцию за CPU.

2. Память:

Оба запроса работают в условиях достаточного объёма свободной памяти.
"LEFT JOIN" активнее использует кэш (выше memory_cache), что может быть связано с обработкой больших объёмов данных.

3. Ввод-вывод:

"LEFT JOIN" создаёт значительную нагрузку на диск (высокие io_bi и io_bo), что согласуется с высокой долей cpu_wa.
"TEMP TABLE" меньше нагружает диск, но при этом сильнее нагружает CPU.

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

"LEFT JOIN" вызывает больше прерываний и контекстных переключений, что может быть связано с активной работой с данными и блокировками.
"TEMP TABLE" демонстрирует более низкую системную активность, но выше нагрузку на CPU в пользовательском режиме.

5. Использование CPU:

"LEFT JOIN": Высокое ожидание ввода-вывода (cpu_wa), что указывает на узкое место в дисковой подсистеме.
"TEMP TABLE": Низкий cpu_id (простой CPU), высокий cpu_us — запрос активно использует процессор для вычислений.

Итог:

"LEFT JOIN" — I/O-зависимый запрос, создаёт высокую нагрузку на диск и вызывает ожидание ввода-вывода. Подходит для систем с быстрыми дисками и достаточным объёмом оперативной памяти для кэширования.
"TEMP TABLE" — CPU-зависимый запрос, активно использует процессор, но меньше нагружает диск. Может быть эффективнее в системах с быстрыми CPU и достаточным количеством ядер.

Общий итог : Часть-4 "TEMP TABLE"

Для условий высокой параллельной нагрузки и конкуренции за вычислительные ресурсы, с точки зрения производительности СУБД - использование временной таблицы оказывает существенное влияние на увеличение производительности СУБД .

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

Среднее увеличение операционной скорости при использовании запроса "TEMP TABLE" составило 14.62%💥

📊 Ключевые выводы о производительности

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

TEMP TABLE демонстрирует значительное преимущество - среднее увеличение операционной скорости составляет +14.62% по сравнению с LEFT JOIN
Подход с временными таблицами обеспечивает более предсказуемую и масштабируемую производительность

2. Характер нагрузки на систему

LEFT JOIN (I/O-зависимый запрос):

Высокая нагрузка на дисковую подсистему (io_bi до 87 тыс., io_bo до 7 тыс.)
Значительное межпроцессное взаимодействие (IPC до 24 107)
Высокое время ожидания ввода-вывода (cpu_wa 10-17%)
Больше прерываний и контекстных переключений

TEMP TABLE (CPU-зависимый запрос):

Интенсивное использование процессора (cpu_us до 81%)
Меньше дисковых операций (сортировка 109,672 kB против большего у JOIN)
Высокие легковесные блокировки (LWLOCK до 311)
Более стабильные таймауты (до 8)

🔧 Архитектурные преимущества TEMP TABLE

1. Разделение ответственности:

Фильтрация данных выполняется один раз и сохраняется
Основной запрос упрощается для оптимизатора СУБД

2. Эффективное использование индексов:

Индексы на временной таблице ускоряют JOIN операции
Снижение стоимости случайного выбора (ORDER BY RANDOM())

3. Оптимизация работы с данными:

Уменьшение сложности запроса
Предсказуемое использование памяти
Более эффективное кэширование

📈 Инфраструктурные выводы

1. Для LEFT JOIN требуется:

Быстрые диски (SSD/NVMe)
Достаточный объем оперативной памяти для кэширования
Оптимизация индексов и запросов

2. Для TEMP TABLE требуется:

Мощные многоядерные процессоры
Контроль легковесных блокировок
Оптимизация использования временных объектов

🎯 Рекомендации по выбору подхода

Выбирать TEMP TABLE когда:

Система имеет мощные процессоры
Требуется высокая параллельная обработка
Важна предсказуемость производительности
Работа с большими объемами данных

Выбирать LEFT JOIN когда:

Система имеет быстрые диски
Ограниченные вычислительные ресурсы
Простые запросы с хорошей индексацией
Достаточный объем RAM для кэширования

💡 Ключевой вывод

Для условий высокой параллельной нагрузки и конкуренции за вычислительные ресурсы использование временных таблиц (TEMP TABLE) обеспечивает существенное увеличение производительности СУБД (+14.62%) и является более масштабируемым решением.

Подход TEMP TABLE лучше подходит для современных систем с многоядерными процессорами, обеспечивая более эффективное распределение нагрузки и предсказуемую производительность при работе с большими объемами данных.

Продолжение цикла "Анализ вариантов оптимизации ресурсоёмкого SQL-запроса"

Вариант-5: Условие WHERE

Итог: сравнительный анализ вариантов оптимизации

Анализ вариантов оптимизации ресурсоёмкого SQL-запроса: Вариант-4 «Временная таблица»