Предисловие
Эксперимент по проверке гипотез, сгенерированных нейросетями для оптимизации СУБД, дал первый интригующий результат. Вопреки прогнозу, конструкция EXISTS показала на 3% меньшую производительность под параллельной нагрузкой, чем ее аналог IN. Это небольшое, но статистически значимое расхождение заставляет нас глубже изучить вопрос. Мы предполагаем, что причина может крыться в малом размере тестовых таблиц, и готовим новый виток испытаний на масштабных данных.
ℹ️ Новый инструмент с открытым исходным кодом для статистического анализа, нагрузочного тестирования и построения отчетов доступен в репозитории GitFlic и GitHub
Тестовые таблицы
CREATE TABLE customers (
id SERIAL PRIMARY KEY,
email VARCHAR(255) NOT NULL,
name VARCHAR(100) NOT NULL
);
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INTEGER NOT NULL,
order_date DATE NOT NULL,
amount DECIMAL(10,2) NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers(id)
);
INSERT INTO customers (email, name)
SELECT
'customer_' || seq || '@' ||
(CASE (seq % 4)
WHEN 0 THEN 'gmail.com'
WHEN 1 THEN 'yahoo.com'
WHEN 2 THEN 'hotmail.com'
ELSE 'company.com'
END) AS email,
'Customer_' || seq AS name
FROM generate_series(1, 100) AS seq;
INSERT INTO orders (customer_id, order_date, amount)
SELECT
(seq % 100) + 1 AS customer_id,
CURRENT_DATE - (random() * 730)::integer AS order_date,
ROUND((random() * 990 + 10)::numeric, 2) AS amount
FROM generate_series(1, 1000) AS seq;
ANALYZE customers ;
ANALYZE orders;
Для таблицы customers:
Генерируется 100 записей
Email в формате customer_X@domain.com с разными доменами
Имена в формате Customer_X
Для таблицы orders:
Генерируется 1000 записей
customer_id - случайный ID от 1 до 100
order_date - случайная дата за последние 2 года
amount - случайная сумма от 10.00 до 1010.00
Особенности:
У каждого клиента будет примерно одинаковое количество заказов
Тестовый запрос-1 : IN
SELECT DISTINCT c.*
INTO test_rec
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.amount IN (
10.50, 25.00, 50.00, 75.25, 100.00, 125.50, 150.00, 175.75, 200.00, 225.25,
250.50, 275.00, 300.00, 325.75, 350.00, 375.25, 400.50, 425.00, 450.00, 475.75,
500.00, 525.50, 550.00, 575.25, 600.00, 625.75, 650.00, 675.25, 700.50, 725.00,
750.00, 775.75, 800.00, 825.25, 850.50, 875.00, 900.00, 925.75, 950.00, 975.25,
1000.00, 1025.50, 1050.00, 1075.25, 1100.00, 1125.75, 1150.00, 1175.25, 1200.50, 1225.00, 1250.00, 1275.75, 1300.00, 1325.25, 1350.50, 1375.00, 1400.00, 1425.75, 1450.00, 1475.25, 1500.00, 1525.50, 1550.00, 1575.25, 1600.00, 1625.75, 1650.00, 1675.25, 1700.50, 1725.00, 1750.00, 1775.75, 1800.00, 1825.25, 1850.50, 1875.00, 1900.00, 1925.75, 1950.00, 1975.25, 2000.00, 2025.50, 2050.00, 2075.25, 2100.00, 2125.75, 2150.00, 2175.25, 2200.50, 2225.00, 2250.00, 2275.75, 2300.00, 2325.25, 2350.50, 2375.00, 2400.00, 2425.75, 2450.00, 2475.25);
План выполнения тестового запроса-1
HashAggregate (cost=26.52..27.52 rows=100 width=37) (actual time=0.529..0.532 rows=1 loops=1)
Group Key: c.id, c.email, c.name
Batches: 1 Memory Usage: 24kB
-> Hash Join (cost=3.50..25.77 rows=100 width=37) (actual time=0.372..0.515 rows=1 loops=1)
Hash Cond: (o.customer_id = c.id)
-> Seq Scan on orders o (cost=0.25..22.25 rows=100 width=4) (actual time=0.245..0.387 rows=1 loops=1)
Filter: (amount = ANY ('{10.50,25.00,50.00,75.25,100.00,125.50,150.00,175.75,200.00,225.25,250.50,275.00,300.00,325.75,350.00,375.25,400.50,425.00,450.00,475.75,500.00,525.50,550.00,575.25,600.00,625.75,650.00,675.25,700.50,725.00,750.00,775.75,800.00,825.25,850.50,875.00,900.00,925.75,950.00,975.25,1000.00,1025.50,1050.00,1075.25,1100.00,1125.75,1150.00,1175.25,1200.50,1225.00,1250.00,1275.75,1300.00,1325.25,1350.50,1375.00,1400.00,1425.75,1450.00,1475.25,1500.00,1525.50,1550.00,1575.25,1600.00,1625.75,1650.00,1675.25,1700.50,1725.00,1750.00,1775.75,1800.00,1825.25,1850.50,1875.00,1900.00,1925.75,1950.00,1975.25,2000.00,2025.50,2050.00,2075.25,2100.00,2125.75,2150.00,2175.25,2200.50,2225.00,2250.00,2275.75,2300.00,2325.25,2350.50,2375.00,2400.00,2425.75,2450.00,2475.25}'::numeric[]))
Rows Removed by Filter: 999
-> Hash (cost=2.00..2.00 rows=100 width=37) (actual time=0.082..0.083 rows=100 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 15kB
-> Seq Scan on customers c (cost=0.00..2.00 rows=100 width=37) (actual time=0.035..0.043 rows=100 loops=1)
Planning Time: 2.008 ms
Execution Time: 0.755 ms
(13 rows)
Тестовый запрос-2 : EXISTS
SELECT c.*
INTO test_rec
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.id
AND o.amount IN (10.50, 25.00, 50.00, 75.25, 100.00, 125.50, 150.00, 175.75, 200.00, 225.25,250.50, 275.00, 300.00, 325.75, 350.00, 375.25, 400.50, 425.00, 450.00, 475.75,500.00, 525.50, 550.00, 575.25, 600.00, 625.75, 650.00, 675.25, 700.50, 725.00,750.00, 775.75, 800.00, 825.25, 850.50, 875.00, 900.00, 925.75, 950.00, 975.25,1000.00, 1025.50, 1050.00, 1075.25, 1100.00, 1125.75, 1150.00, 1175.25, 1200.50, 1225.00,1250.00, 1275.75, 1300.00, 1325.25, 1350.50, 1375.00, 1400.00, 1425.75, 1450.00, 1475.25,1500.00, 1525.50, 1550.00, 1575.25, 1600.00, 1625.75, 1650.00, 1675.25, 1700.50, 1725.00,1750.00, 1775.75, 1800.00, 1825.25, 1850.50, 1875.00, 1900.00, 1925.75, 1950.00, 1975.25,2000.00, 2025.50, 2050.00, 2075.25, 2100.00, 2125.75, 2150.00, 2175.25, 2200.50, 2225.00,2250.00, 2275.75, 2300.00, 2325.25, 2350.50, 2375.00, 2400.00, 2425.75, 2450.00, 2475.25) );
План выполнения тестового запроса-2
Hash Join (cost=23.96..27.34 rows=100 width=37) (actual time=0.331..0.350 rows=1 loops=1)
Hash Cond: (c.id = o.customer_id)
-> Seq Scan on customers c (cost=0.00..2.00 rows=100 width=37) (actual time=0.021..0.030 rows=100 loops=1)
-> Hash (cost=23.15..23.15 rows=65 width=4) (actual time=0.294..0.294 rows=1 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> HashAggregate (cost=22.50..23.15 rows=65 width=4) (actual time=0.286..0.287 rows=1 loops=1)
Group Key: o.customer_id
Batches: 1 Memory Usage: 24kB
-> Seq Scan on orders o (cost=0.25..22.25 rows=100 width=4) (actual time=0.162..0.276 rows=1 loops=1)
Filter: (amount = ANY ('{10.50,25.00,50.00,75.25,100.00,125.50,150.00,175.75,200.00,225.25,250.50,275.00,300.00,325.75,350.00,375.25,400.50,425.00,450.00,475.75,500.00,525.50,550.00,575.25,600.00,625.75,650.00,675.25,700.50,725.00,750.00,775.75,800.00,825.25,850.50,875.00,900.00,925.75,950.00,975.25,1000.00,1025.50,1050.00,1075.25,1100.00,1125.75,1150.00,1175.25,1200.50,1225.00,1250.00,1275.75,1300.00,1325.25,1350.50,1375.00,1400.00,1425.75,1450.00,1475.25,1500.00,1525.50,1550.00,1575.25,1600.00,1625.75,1650.00,1675.25,1700.50,1725.00,1750.00,1775.75,1800.00,1825.25,1850.50,1875.00,1900.00,1925.75,1950.00,1975.25,2000.00,2025.50,2050.00,2075.25,2100.00,2125.75,2150.00,2175.25,2200.50,2225.00,2250.00,2275.75,2300.00,2325.25,2350.50,2375.00,2400.00,2425.75,2450.00,2475.25}'::numeric[]))
Rows Removed by Filter: 999
Planning Time: 0.484 ms
Execution Time: 0.443 ms
Вопрос нейросети
Проведи сравнительный анализ по запросам и планам выполнения: Запрос-1,
Запрос-2, План выполнения-1, План выполнения-2.
Сформулируй гипотезу - какой запрос покажет большую производительность при росте нагрузки с 10 до 44 параллельных сессий и размере таблицы customers = 100 строк и orders = 1000 строк ?
Ответ нейросети "Ask Postgres"
Для анализа производительности двух запросов при росте нагрузки (10–44 параллельных сессий) с фиксированными размерами таблиц (customers = 100 строк, orders = 1000 строк), проведём сравнительный анализ по ключевым аспектам планов выполнения, а затем сформулируем обоснованную гипотезу.
Сравнительный анализ планов выполнения
Запрос-1 (DISTINCT + JOIN)
SELECT DISTINCT c.*
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.amount IN (...)
План выполнения:
- Hash Join между customers и orders.
- orders сканируется секвентально, фильтруется по amount IN (...) → 1 подходящая строка из 1000.
- customers сканируется полностью (100 строк), хешируется.
- HashAggregate на выходе для DISTINCT — применяется к результату соединения (в данном случае 1 строка).
- Фактическое время выполнения: 0.755 мс.
Особенности:
- Соединение выполняется полностью — даже если в orders только 1 подходящая строка, PostgreSQL всё равно строит хеш-таблицу по customers и соединяет с отфильтрованными orders.
- DISTINCT требует дополнительной агрегации, даже если результат один — это избыточная операция.
- Потенциальная проблема: при росте числа параллельных сессий, хеш-таблица customers будет создаваться повторно в каждой сессии, что создаёт нагрузку на память и CPU.
Запрос-2 (EXISTS)
SELECT c.*
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.id
AND o.amount IN (...)
)
План выполнения:
- Hash Join — но структура иная:Сначала HashAggregate на orders — группировка по customer_id для выявления уникальных клиентов, у которых есть заказы с нужной суммой.
Затем хешируется только 1 уникальный customer_id (из 1 подходящей строки в orders).
Соединяется с customers по id. - HashAggregate здесь работает как фильтр — он сжимает 1000 строк orders до 1 уникального customer_id.
- Фактическое время выполнения: 0.350 мс — почти в 2 раза быстрее.
Особенности:
- EXISTS не дублирует строки customers — если у клиента 5 подходящих заказов, он вернётся только один раз.
- HashAggregate на orders предварительно фильтрует и сжимает данные — уменьшает объём данных для соединения.
- Хеш-таблица для customers создаётся один раз, но соединяется с уже сжатым набором (65 строк в плане — это оценка, фактически 1).
- Меньше памяти и CPU на этапе соединения.
Ключевые различия при масштабировании нагрузки (10–44 сессий)
Гипотеза
При увеличении нагрузки с 10 до 44 параллельных сессий, Запрос-2 (с EXISTS) покажет значительно лучшую производительность и масштабируемость, чем Запрос-1 (с DISTINCT JOIN).
Обоснование:
- Запрос-2 минимизирует объём данных, передаваемых между этапами:HashAggregate на orders сжимает 1000 строк до 1 уникального customer_id.
Это означает, что хеш-таблица customers соединяется с 1 значением, а не с 100 строками orders. - Запрос-1 неэффективно использует ресурсы:Каждая сессия полностью загружает таблицу customers в хеш-таблицу (100 строк × 44 сессии = 4400 строк в памяти одновременно).
DISTINCT — избыточная операция, так как JOIN с EXISTS-подобной логикой уже гарантирует уникальность. - Память и CPU:При 44 сессиях Запрос-1 может вызвать конкуренцию за память (хеш-таблицы customers), фрагментацию и частые GC-операции (если используется shared_buffers).
Запрос-2 — меньше памяти на хеш-таблицу, меньше CPU на агрегацию, меньше блокировок при параллельном доступе. - Практический вывод:EXISTS с подзапросом и HashAggregate на дочерней таблице — оптимальная модель для фильтрации по наличию связи.
DISTINCT JOIN — неоптимальна, когда цель — получить уникальные родительские записи, а не все комбинации.
Рекомендация
- Используйте Запрос-2 (EXISTS) — он не только быстрее на текущих данных, но и гораздо лучше масштабируется при росте параллелизма.
- Избегайте DISTINCT в JOIN, если не требуется именно дедупликация дублей — EXISTS или IN с подзапросом предпочтительнее.
- Для ещё большей производительности: Добавьте индекс на orders(customer_id, amount) — это превратит Seq Scan в Index Only Scan, снизив I/O.
✅ Гипотеза подтверждена: Запрос-2 (EXISTS) будет демонстрировать лучшую производительность при росте нагрузки.
Ответ нейросети DeepSeek
На основе анализа запросов и планов выполнения, я сформулирую следующую гипотезу:
Гипотеза
Запрос-2 (с EXISTS) покажет лучшую производительность при росте нагрузки до 44 параллельных сессий
Обоснование
1. Анализ планов выполнения
Запрос-1 (DISTINCT + JOIN):
- Использует HashAggregate для устранения дубликатов ПОСЛЕ соединения
- Обрабатывает все столбцы customers (width=37)
- Дополнительная операция агрегации увеличивает нагрузку на память
Запрос-2 (EXISTS):
- Использует HashAggregate только для customer_id (width=4)
- Более эффективное использование памяти (9kB vs 15kB в хэше)
- Агрегация выполняется на меньшем объеме данных
2. Факторы, влияющие на параллельную производительность
Память:
- Запрос-2 использует меньше памяти (9kB vs 15kB в хэш-таблице)
- При 44 сессиях экономия памяти будет значительной: ~396kB vs ~660kB
CPU и операции ввода-вывода:
- Запрос-2 выполняет агрегацию на меньшем наборе данных (только id)
- Меньше данных передается между операциями
Масштабируемость:
- Подход с EXISTS лучше масштабируется при росте:
Размера таблиц
Количества параллельных сессий
Объема данных в условии IN
3. Потенциальные узкие места при высокой нагрузке
Для Запроса-1:
- HashAggregate с широкими строками (37 байт)
- Конкуренция за память при множественных хэш-таблицах
- Более высокие накладные расходы на управление памятью
Для Запроса-2:
- Более эффективное использование ресурсов
- Меньший размер рабочих данных
- Лучшая локализация данных
4. Рекомендация для production
Для высоконагруженных систем с множеством параллельных сессий предпочтительнее использовать EXISTS-подход (Запрос-2), особенно при условии, что:
- Размер таблицы customers остается небольшим (100 строк)
- Таблица orders имеет умеренный размер (1000 строк)
- Количество параллельных запросов значительно возрастает
Экспериментальные результаты
Нагрузка на СУБД
Операционная скорость
Среднее снижение операционной скорости при использовании конструкции EXISTS по сравнению с IN составило 3.62%.
Ожидания Timeout/SpinDelay
Результат сравнительного анализа нагрузочного тестирования
Среднее снижение операционной скорости при использовании конструкции EXISTS по сравнению с IN составило 3.62%.
Анализ причин расхождения гипотезы и экспериментальных результатов
1. Накладные расходы на агрегацию в подзапросе
План-2 показывает:
HashAggregate (cost=22.50..23.15 rows=65 width=4)
-> Seq Scan on orders o
- Дополнительная операция HashAggregate выполняется ДО соединения
- При 44 сессиях эти "микро-затраты" суммируются
- В Запросе-1 агрегация выполняется ПОСЛЕ соединения, но на меньшем наборе данных
2. Конкуренция за доступ к таблице orders
Ключевая проблема:
- Оба запроса выполняют Seq Scan на таблице orders с одинаковым фильтром
- При 44 параллельных сессиях возникает "шторм" последовательных сканирований
- Таблица orders (1000 строк) становится узким местом
3. Особенности работы с маленькими таблицами
При размерах:
- customers: 100 строк
- orders: 1000 строк
Эффекты:
- Преимущества оптимизации памяти минимальны
- Время планирования и старта выполнения становится значимым
- EXISTS добавляет дополнительный уровень вложенности
4. Статистика и селективность
Из планов видно:
-- Оба запроса ожидают 100 строк, но получают 1
rows=100 (expected) vs actual rows=1
- Оба запроса имеют одинаковую селективность
- Оптимизатор не может эффективно использовать статистику для столь специфичного IN-списка
5. Параллельные доступы к хэш-таблицам
Для Запроса-2:
HashAggregate → Hash Join
- Две последовательные хэш-операции создают дополнительную конкуренцию
- При 44 сессиях возникают блокировки при построении хэш-таблиц
6. Особенности тестовой нагрузки
Возможные сценарии:
- "Горячая" таблица: частые обновления статистики
- Конкуренция за I/O: все сессии читают одни и те же данные
- Кэширование: при маленьких таблицах оба подхода одинаково эффективны
7. Архитектурные особенности СУБД
PostgreSQL-специфика:
- Оба запроса используют последовательное сканирование
- Отсутствие индексов нивелирует преимущества EXISTS
- Накладные расходы на управление подзапросами
Рекомендации для дальнейшего исследования
-- Добавить индекс для проверки гипотезы
CREATE INDEX idx_orders_amount ON orders(amount);
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
Вывод
При маленьких размерах таблиц и отсутствии индексов преимущества EXISTS нивелируются накладными расходами на дополнительную агрегацию и конкуренцией за доступ к маленькой таблице orders. Разница в 3% находится в пределах статистической погрешности и может быть обусловлена фоновой нагрузкой тестовой среды.