Оптимизация SQL-запроса с фильтрами в PostgreSQL

При работе с большими объёмами данных - до 1 миллиона записей в таблице t и до 1 тысячи фильтров в таблице f - производительность запросов становится критичной. В исходном коде используется схема с четырьмя LEFT JOIN на одну и ту же таблицу фильтров, что при большом количестве строк может приводить к замедлению. Рассмотрим, как ускорить такой запрос без потери функциональности.

Почему исходный запрос медленный?

Исходный запрос:

SELECT t0.*
FROM t AS t0
LEFT JOIN f AS f_1 ON f_1.id = t0.param_1
LEFT JOIN f AS f_2 ON f_2.id = t0.param_2
LEFT JOIN f AS f_3 ON f_3.id = t0.param_3
LEFT JOIN f AS f_4 ON f_4.id = t0.param_4
WHERE f_1.is_use AND f_2.is_use AND f_3.is_use AND f_4.is_use

Основные проблемы:

• Множественные JOIN - каждый LEFT JOIN увеличивает сложность, особенно если таблица f большая (до 1 тыс. записей). Хотя индексы есть, PostgreSQL может выбирать неоптимальный план.
• Условия в WHERE - фильтрация по is_use после соединения может приводить к чтению лишних строк, так как LEFT JOIN сначала соединяет все строки, а потом отсекает не подходящие.
• Отсутствие индекса на is_use - если нет индекса по полю is_use, проверка условия будет выполняться последовательно.

Способы оптимизации запроса

1. Замена LEFT JOIN на INNER JOIN

Поскольку в условии WHERE требуется, чтобы все четыре фильтра были активны (is_use = true), LEFT JOIN избыточен. Используйте INNER JOIN:

SELECT t0.*
FROM t AS t0
INNER JOIN f AS f_1 ON f_1.id = t0.param_1 AND f_1.is_use
INNER JOIN f AS f_2 ON f_2.id = t0.param_2 AND f_2.is_use
INNER JOIN f AS f_3 ON f_3.id = t0.param_3 AND f_3.is_use
INNER JOIN f AS f_4 ON f_4.id = t0.param_4 AND f_4.is_use

Это уменьшит количество обрабатываемых строк, так как соединение происходит только по активным фильтрам.

2. Создание составных индексов

Убедитесь, что на таблице f есть индекс по полям id и is_use:

CREATE INDEX idx_f_id_is_use ON f (id, is_use);

Для таблицы t создайте индексы на столбцы param_1, param_2, param_3, param_4:

CREATE INDEX idx_t_param1 ON t (param_1);
CREATE INDEX idx_t_param2 ON t (param_2);
CREATE INDEX idx_t_param3 ON t (param_3);
CREATE INDEX idx_t_param4 ON t (param_4);

3. Использование EXISTS для проверки

Если нужно проверить только наличие активных фильтров, можно применить подзапросы с EXISTS:

SELECT t0.*
FROM t AS t0
WHERE EXISTS (SELECT 1 FROM f WHERE id = t0.param_1 AND is_use)
  AND EXISTS (SELECT 1 FROM f WHERE id = t0.param_2 AND is_use)
  AND EXISTS (SELECT 1 FROM f WHERE id = t0.param_3 AND is_use)
  AND EXISTS (SELECT 1 FROM f WHERE id = t0.param_4 AND is_use);

Такой подход часто быстрее, так как EXISTS прекращает поиск после первого совпадения.

4. Материализованные представления

Если данные в таблице f меняются редко, создайте материализованное представление, которое содержит только активные фильтры:

CREATE MATERIALIZED VIEW active_f AS
SELECT id FROM f WHERE is_use;

CREATE INDEX idx_active_f_id ON active_f (id);

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

SELECT t0.*
FROM t AS t0
WHERE t0.param_1 IN (SELECT id FROM active_f)
  AND t0.param_2 IN (SELECT id FROM active_f)
  AND t0.param_3 IN (SELECT id FROM active_f)
  AND t0.param_4 IN (SELECT id FROM active_f);

Это особенно эффективно при стабильном наборе фильтров.

Рекомендации по настройке PostgreSQL

• Проверьте план выполнения через EXPLAIN ANALYZE - это покажет узкие места.
• Увеличьте work_mem для ускорения сортировок и хэш-соединений.
• Используйте ANALYZE для обновления статистики после массовых изменений.

Выбор метода зависит от частоты обновления таблицы f и объёма данных. Для динамических фильтров лучше подойдёт EXISTS или INNER JOIN, а для статичных - материализованные представления.