Итоги анализа вариантов оптимизации ресурсоёмкого SQL-запроса

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'

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;

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.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 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
WHERE EXISTS (
    SELECT 1 FROM random_period rp 
    WHERE b.book_date BETWEEN rp.start_date AND rp.end_date
)
ORDER BY b.book_date, t.ticket_no;

WITH random_period AS (
    SELECT 
        book_date AS start_date,
        book_date + INTERVAL '30 days' AS end_date
    FROM bookings 
    WHERE book_date <= (SELECT MAX(book_date) FROM bookings) - INTERVAL '30 days'
    ORDER BY RANDOM() 
    LIMIT 1
),
filtered_bookings AS (
    SELECT book_ref, book_date
    FROM bookings
    WHERE book_date BETWEEN (SELECT start_date FROM random_period) 
                        AND (SELECT end_date FROM random_period)
)
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 filtered_bookings b
LEFT JOIN LATERAL (
    SELECT ticket_no, passenger_name
    FROM tickets 
    WHERE book_ref = b.book_ref
) t ON true
LEFT JOIN LATERAL (
    SELECT flight_id, fare_conditions, price
    FROM segments 
    WHERE ticket_no = t.ticket_no
) s ON true
LEFT JOIN LATERAL (
    SELECT status, scheduled_departure, actual_departure, route_no
    FROM flights 
    WHERE flight_id = s.flight_id
) f ON true
LEFT JOIN LATERAL (
    SELECT seat_no, boarding_no
    FROM boarding_passes 
    WHERE ticket_no = t.ticket_no AND flight_id = s.flight_id
) bp ON true
ORDER BY b.book_date, t.ticket_no;

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
),
booking_summary AS (
    SELECT 
        b.book_ref,
        b.book_date,
        COUNT(t.ticket_no) as ticket_count,
        ARRAY_AGG(t.ticket_no) as ticket_numbers
    FROM bookings b
    LEFT JOIN tickets t ON b.book_ref = t.book_ref
    WHERE b.book_date BETWEEN (SELECT start_date FROM random_period) 
                        AND (SELECT end_date FROM random_period)
    GROUP BY b.book_ref, b.book_date
)
SELECT 
    bs.book_ref,
    bs.book_date,
    bs.ticket_count,
    tn.ticket_no,  -- Используем развернутое значение
    t.passenger_name,
    s.flight_id,
    f.status
FROM booking_summary bs
CROSS JOIN UNNEST(bs.ticket_numbers) AS tn(ticket_no)  -- Разворачиваем массив здесь
LEFT JOIN tickets t ON t.ticket_no = tn.ticket_no      -- Теперь соединяем по одиночному значению
LEFT JOIN segments s ON s.ticket_no = t.ticket_no
LEFT JOIN flights f ON f.flight_id = s.flight_id
ORDER BY bs.book_date, tn.ticket_no;  -- Сортируем по развернутому значению

-- Создаем временную таблицу для отфильтрованных бронирований
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);

--TEST-5 "TEMP TABLES"
EXPLAIN (ANALYZE , SUMMARY , COSTS)
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;

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.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 bookings b
CROSS JOIN random_period rp
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
WHERE b.book_date BETWEEN rp.start_date AND rp.end_date
  AND (f.status IS NULL OR f.status IN ('Scheduled', 'On Time', 'Departed')) -- Фильтр по статусу
ORDER BY b.book_date, t.ticket_no;