Оптимизация SQL-запросов PostgreSQL : LIKE (пример)

Простой запрос - не всегда самый эффективный.

Начало

Текст SQL запроса

План выполнения запроса

Оптимизация SQL запроса

1. Создание индексов

-- Индекс для быстрого поиска с сортировкой по col1

CREATE INDEX CONCURRENTLY col1x_table1_supercol12 ON table1 (col4, col1 ASC);

-- Составной индекс для table2

CREATE INDEX CONCURRENTLY col1x_keycloak_user_roles_col12 ON table2 (col12, col14);

2. Оптимизация условия LIKE

Если структура `col14` позволяет, заменить поиск по подстроке:

-- Вариант 1: Если как массив

WHERE "table1"."col4"

AND 'xxx' = ANY(string_to_array("table2"."col14", ','))

-- Вариант 2: JSON

WHERE "table1"."col4"

AND "table2"."col14"::jsonb ? 'xxx'

-- Вариант 3: LIKE, но с триграммами

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX CONCURRENTLY col1x_col14_gin

ON table2 USING gin (col14 gin_trgm_ops);

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

начать с отфильтрованной таблицы `table2`:

4. Оптимизированный вариант с CTE

5. Дополнительные индексы (если нужно покрытие)

Основание для оптимизации

1. Индекс `col1x_table1_supercol12` позволит быстро найти поля в нужном порядке (ASC)

2. Индекс `col1x_keycloak_user_roles_col12` ускорит JOIN и фильтрацию по ролям

3. Изменение порядка JOIN может быть эффективнее, если записей с `col14 LIKE '%xxx%'` мало

Ожидаемый план после оптимизации