Эксперимент над мифом: как коррелированные подзапросы обогнали JOIN по производительности.

Эксперимент над мифом: как коррелированные подзапросы обогнали JOIN по производительности.

Принято считать, что коррелированные подзапросы — это зло, ведущее к проблемам N+1, а JOIN — панацея для производительности. Статья описывает проверку догмы в ходе нагрузочного тестирования, будучи увереным в результатах еще до старта экспериментов.

Результат ошеломил: в некоторых сценариях коррелированный подзапрос показал кардинальное превышение производительности над классическим JOIN. Это наглядный пример того, как теоретическая стоимость запроса, которую мы видим в EXPLAIN, может быть совершенно не релевантна при оценке реальной производительности системы в целом.

Статья — это очередное напоминание всем разработчикам и DBA: в мире СУБД нет абсолютных истин, а любое, даже самое «логичное» правило, нужно проверять экспериментально.

ℹ️ Новый инструмент с открытым исходным кодом для статистического анализа, нагрузочного тестирования и построения отчетов доступен в репозитории GitFlic и GitHub

Задача

Провести экспериментальную проверку гипотезы о влиянии коррелированного запроса на производительность СУБД .

В этом тесте PostgreSQL 16 быстрее выполнил вариант с JOIN + GROUP BY: ~0.415 ms против ~0.803 ms для коррелированного подзапроса.

План JOIN: Hash Right Join + HashAggregate с одним проходом по таблицам — меньше итераций и накладных, чем у подзапроса.

План подзапроса: 25 запусков под-плана с Bitmap Scan по orders (классический N+1-эффект), поэтому медленнее.

Вывод: в PostgreSQL коррелированные подзапросы легко деградируют в N+1; предпочитайте set-based JOIN и проверяйте планы через EXPLAIN ANALYZE.

Источник:

Экспериментальная проверка гипотезы

Виртуальная машина

CPU = 8

RAM = 8GB

Postgres Pro (enterprise certified) 17.5.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.4.1 20230605 (Red Soft 11.4.0-1), 64-bit

postgresql.auto.conf

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'

Тестовые таблицы

before_start.sql

-- version 3.1

--Вариант по умолчанию - НИЧЕГО НЕ МЕНЯТЬ

--SELECT CURRENT_TIMESTAMP ;

--Вариант по умолчанию - НИЧЕГО НЕ МЕНЯТЬ

--------------------------------------------------------

-- Изменения/добавления тестовых таблиц

-- Таблица клиентов

-- Create the customers table

CREATE TABLE customers (

customer_id SERIAL PRIMARY KEY,

name VARCHAR(255) NOT NULL

);

-- Insert 25 random customer records

INSERT INTO customers (name) VALUES

('Alice Smith'), ('Bob Johnson'), ('Charlie Brown'), ('Diana Prince'), ('Ethan Hunt'),

('Fiona Gallagher'), ('George Miller'), ('Hannah Abbott'), ('Ian Fleming'), ('Jane Austen'),

('Kevin Spacey'), ('Laura Croft'), ('Michael Jordan'), ('Nancy Drew'), ('Oliver Twist'),

('Penelope Cruz'), ('Quentin Tarantino'), ('Rachel Green'), ('Steve Rogers'), ('Tina Turner'),

('Ursula K. Le Guin'), ('Victor Hugo'), ('Wendy Darling'), ('Xavier Roberts'), ('Yvonne Craig');

-- Create the orders table

CREATE TABLE orders (

order_id SERIAL PRIMARY KEY,

customer_id INT,

order_date TIMESTAMP,

FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE

);

INSERT INTO orders (customer_id)

WITH RECURSIVE data_rows (n) AS (

SELECT 1

UNION ALL

SELECT n + 1 FROM data_rows WHERE n < 1000

)

SELECT FLOOR(RANDOM() * 25) + 1

FROM data_rows;

-- Create index on orders table

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

-- Изменения/добавления тестовых таблиц

--------------------------------------------------------

Тестовый сценарий-1 ( JOIN )

-- version 4.0

-- JOIN

CREATE OR REPLACE FUNCTION scenario1() RETURNS integer AS $$

DECLARE

test_rec record ;

BEGIN

SET application_name = 'scenario1';

SELECT

c.customer_id, COUNT(o.order_id) AS orders_count

INTO test_rec

FROM customers c

LEFT JOIN orders o ON c.customer_id = o.customer_id

GROUP BY c.customer_id;

return 0 ;

END

$$ LANGUAGE plpgsql;

Тестовый сценарий-2 (SUBQUERY)

-- version 4.0

-- SUBQUERY

CREATE OR REPLACE FUNCTION scenario2() RETURNS integer AS $$

DECLARE

test_rec record ;

BEGIN

SET application_name = 'scenario2';

SELECT c.customer_id,

(SELECT COUNT(o.order_id)

FROM orders o

WHERE o.customer_id = c.customer_id) AS orders_count

INTO test_rec

FROM customers c;

return 0 ;

END

$$ LANGUAGE plpgsql;

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

# НАСТРОЙКИ НАГРУЗОЧНОГО ТЕСТИРОВАНИЯ

# Максимальная нагрузка

finish_load = 20

# Тестовая БД

testdb = default

# Веса сценариев

scenario1 = 1.0

scenario2 = 1.0

# Инициализировать тестовую БД

init_test_db = on

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

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

Нагрузка меняется от 5 до 22 одновременных соединений для тестового сценария

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

Операционная скорость в ходе нагрузочного тестирования для сценария-1(join) и сценария-2(subquery)

Относительная разницы операционной скорости в сценарии-2 по сравнению с сценарием-1

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

Для данной виртуальной машины , данной версии СУБД и данного характера нагрузки среднее снижение операционной скорости в ходе нагрузочного тестирования, для сценария использующего JOIN составило 188%.💥

Продолжение