Промпт: ускорить медленный SQL-запрос по EXPLAIN

GigaChat 2 Max06.10.2026

Анализ текущего запроса и планового вывода

Проблема №1:
Основной источник медленной работы — последовательный сканирование (Seq Scan) таблиц orders и customers.

• В таблице orders: секвенциальный просмотр всех записей (~12 миллионов строк). Из-за фильтра по статусу (o.status = 'paid') и датам (o.created_at >= now() - interval '30 days'), большинство строк отбрасывается уже после прочтения.
• В таблице customers: также выполняется полное чтение строки по полному соответствию шаблону (email like '%@corp-example.ru'), которое тоже неэффективно, особенно учитывая отсутствие индекса по этому полю.

Проблема №2:
Сортировка порядка выполнения запроса происходит по дате заказа (o.created_at DESC), что требует полной сортировки найденных результатов перед ограничением LIMIT до первых 50 записей. Если бы была возможность отфильтровывать данные раньше и сразу получать отсортированные результаты, процесс мог бы стать значительно быстрее.

Решение проблемы: варианты оптимизации по эффективности

1️⃣ Создание композитного индекса с учетом всех условий WHERE и ORDER BY

Наиболее эффективный способ повысить производительность данного запроса — создание специального композитного индекса, который учитывает порядок фильтрации и сортировки одновременно.

CREATE INDEX idx_orders_status_created_email ON orders(status, created_at DESC);

Ожидаемый эффект: Этот индекс позволит быстро найти нужные заказы за последний месяц со статусом "paid". После быстрого поиска нужных записей дальнейшая сортировка будет происходить уже среди небольшого числа отобранных строк, что резко снизит затраты на обработку данных. Индекс включает поле created_at, необходимое для сортировки, что устраняет необходимость отдельной операции сортировки.

Цена решения:

• Увеличение объема базы данных примерно на размер нового индекса.
• Замедление операций вставки/обновления в таблицу orders.
• Блокировка таблицы во время создания индекса (см. ниже пункт про быстрое создание индекса).

2️⃣ Оптимизация условия поиска по email через функциональный индекс

Следующая проблема — условие LOWER(email) сильно замедляет работу запросов, поскольку PostgreSQL не может использовать обычный индекс при применении функции над колонкой. Можно решить эту проблему созданием дополнительного индекса на преобразованное значение поля.

CREATE INDEX idx_customers_lower_email ON customers(LOWER(email));

Ожидаемый эффект: Позволяет избежать полного просмотра таблицы customers при поиске нужного диапазона адресов электронной почты. Это приведет к быстрому поиску подходящих клиентов.

Цена решения:

• Увеличит объем базы данных дополнительным размером индекса.
• Медленнее будут выполняться обновления полей email и добавляться новые клиенты.
• Необходимость временной блокировки таблицы customers при создании индекса.

3️⃣ Рефакторинг запроса (если невозможно добавить индексы)

Если по каким-то причинам нельзя изменить структуру индексов, можно попробовать немного улучшить существующий запрос. Например, выборка из customers должна идти первой, чтобы уменьшить количество кандидатов на сопоставление в orders.

SELECT o.id, o.created_at, o.total, c.email
FROM (
   SELECT id, LOWER(email) AS lower_email
   FROM customers
   WHERE LOWER(email) LIKE '%@corp-example.ru'
 ) c
JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid'
AND o.created_at >= NOW() - INTERVAL '30 DAYS'
ORDER BY o.created_at DESC
LIMIT 50;

Ожидаемый эффект: Такой подход потенциально поможет несколько сократить объем обработки, но кардинального улучшения ожидать не стоит, так как база всё равно вынуждена делать полное сканирование обоих таблиц.

Цена решения: Отсутствует дополнительная нагрузка на базу данных, кроме минимальных накладных расходов на дополнительную вложенную операцию.

Быстрое создание индекса на production-сервере

Чтобы минимизировать блокировку таблицы во время создания большого индекса на продакшн-системе, рекомендуется использование команды CREATE INDEX CONCURRENTLY. Она позволяет создавать индекс асинхронно и практически не блокирует параллельные запросы на запись.

CREATE INDEX CONCURRENTLY idx_orders_status_created_email ON orders(status, created_at DESC);
CREATE INDEX CONCURRENTLY idx_customers_lower_email ON customers(LOWER(email));

Преимущества: Минимальное влияние на доступ пользователей и приложений. Нет длительной блокировки.

Недостаток: Создается чуть дольше обычного индекса, потребляя больше ресурсов системы.

Проверка результата

После внесения изменений нужно заново проанализировать план выполнения запроса командой EXPLAIN ANALYZE:

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.created_at, o.total, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid'
AND o.created_at >= now() - interval '30 days'
AND lower(c.email) LIKE '%@corp-example.ru'
ORDER BY o.created_at DESC
LIMIT 50;

Что должно измениться в выводе:

• Исчезнут полные просмотры таблиц, появится использование новых индексов.
• Значительно уменьшатся показатели I/O-ввода-вывода (количество прочитанных блоков памяти).
• Вместо полной сортировки (Sort) должен появиться быстрый обход индекса (например, Index Scan).
• Время исполнения запроса существенно сократится благодаря уменьшению количества обработанных строк.

Таким образом, оптимизированный запрос с новыми индексами обеспечит значительное повышение производительности и устранит основные узкие места текущей реализации.

ChatGPT (GPT-5.5)06.10.2026

1. Где тратится основное время и почему

Основная проблема — чтение почти всей таблицы orders.

Ключевые узлы плана:

Seq Scan on orders o  (actual time=0.03..4410.7 rows=412000 loops=1)
  Filter: ((status = 'paid') AND (created_at >= (now() - '30 days'::interval)))
  Rows Removed by Filter: 11588000

На это ушло примерно 4.4 секунды из 4.8 секунд. PostgreSQL прочитал 12 млн строк orders, отфильтровал 11.6 млн и оставил 412 тыс.

Причина: нет подходящего индекса под условие:

o.status = 'paid'
AND o.created_at >= now() - interval '30 days'

Существующий индекс:

orders(customer_id)

для такого фильтра бесполезен, потому что запрос сначала ограничивает заказы по status и created_at, а не по customer_id.

Вторая заметная, но гораздо меньшая проблема:

Seq Scan on customers c  (actual time=0.02..209.8 rows=3100 loops=1)
  Filter: (lower(email) ~~ '%@corp-example.ru'::text)
  Rows Removed by Filter: 896900

Таблица customers сканируется целиком — 900 тыс. строк. Это заняло около 210 мс, то есть заметно, но не главный тормоз.

Причины:

1. Используется lower(email), а индекса на выражение lower(email) нет.
2. Условие:

``sql
LIKE '%@corp-example.ru'
``

начинается с %, поэтому обычный btree-индекс по email или даже по lower(email) не поможет.

Далее:

Hash Join  (actual time=211.5..4806.9 rows=1840 loops=1)

PostgreSQL соединяет 412 тыс. найденных заказов с 3100 подходящими customer'ами. Сам join не выглядит основной проблемой — он в основном ждёт, пока orders будет просканирована.

Сортировка:

Sort  (actual time=4812.3..4812.3 rows=50 loops=1)
  Sort Method: top-N heapsort  Memory: 32kB

Сортировка дешёвая. Сортируется 1840 строк, берутся top-50. Это не узкое место.

2. Варианты ускорения по убыванию эффекта

Вариант 1. Индекс на orders под paid + created_at DESC

Самый важный индекс для текущего запроса:

CREATE INDEX CONCURRENTLY orders_paid_created_at_desc_idx
ON orders (created_at DESC)
INCLUDE (id, customer_id, total)
WHERE status = 'paid';

Почему именно так:

• WHERE status = 'paid' делает индекс частичным: в нём будут только оплаченные заказы.
• created_at DESC соответствует:

``sql
ORDER BY o.created_at DESC
``

• Условие:

``sql
o.created_at >= now() - interval '30 days'
``

сможет использовать этот индекс как range scan.

• INCLUDE (id, customer_id, total) позволяет потенциально получить данные из индекса без чтения самой таблицы, если visibility map позволяет index-only scan.

Ожидаемый эффект:

• Вместо полного Seq Scan on orders по 12 млн строк PostgreSQL сможет читать только свежие paid-заказы из индекса.
• Может исчезнуть отдельная сортировка, потому что индекс уже идёт по created_at DESC.
• Особенно полезно из-за LIMIT 50: PostgreSQL сможет идти от самых новых заказов вниз и остановиться, когда найдёт достаточно строк после join/filter.

Цена:

• Дополнительное место на диске. Индекс будет меньше полного, потому что только по status = 'paid', примерно по 30% таблицы.
• Замедление INSERT/UPDATE/DELETE для строк со статусом paid.
• Если заказ меняет статус на paid или с paid на другой — индекс тоже будет обновляться.
• При создании с CONCURRENTLY долгой эксклюзивной блокировки таблицы не будет, но создание займёт больше времени и будет создавать дополнительную I/O-нагрузку.

Вариант 2. Индекс для поиска customer'ов по email-домену через pg_trgm

Для текущего условия:

lower(c.email) LIKE '%@corp-example.ru'

лучше всего подходит trigram-индекс:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX CONCURRENTLY customers_lower_email_trgm_idx
ON customers
USING gin (lower(email) gin_trgm_ops);

Ожидаемый эффект:

• Вместо:

``text
Seq Scan on customers
``

должен появиться Bitmap Index Scan / Bitmap Heap Scan по customers_lower_email_trgm_idx.

• Сканирование customers с 900 тыс. строк должно стать заметно дешевле.
• В вашем текущем плане это экономит примерно ту часть, где сейчас тратится около 210 мс. То есть эффект полезный, но меньше, чем от индекса на orders.

Цена:

• GIN trigram-индекс может быть довольно крупным.
• Замедляет вставки и обновления email.
• Создание индекса требует дополнительного времени и I/O.
• CREATE EXTENSION обычно быстрая операция, но требует прав.

Вариант 3. Композитный частичный индекс на orders для плана от customers

Если email-фильтр очень селективный, можно дать PostgreSQL возможность идти от найденных customers к их заказам:

CREATE INDEX CONCURRENTLY orders_paid_customer_created_at_idx
ON orders (customer_id, created_at DESC)
INCLUDE (id, total)
WHERE status = 'paid';

Этот индекс полезен для плана вида:

1. Найти customers с корпоративным email.
2. Для каждого customer быстро найти его paid-заказы за последние 30 дней.
3. Отсортировать результат по created_at DESC.
4. Взять 50.

Ожидаемый эффект:

• Может появиться Nested Loop от customers к orders.
• Для каждого из примерно 3100 customers PostgreSQL сможет быстро искать заказы через индекс.
• Особенно хорошо, если у каждого customer немного заказов.

Цена:

• Ещё один индекс на orders.
• Дополнительное место на диске.
• Замедление записи в orders, особенно для строк paid.
• Если оставить и этот индекс, и orders_paid_created_at_desc_idx, стоимость записи увеличится сильнее.

Что выбрать между вариантами 1 и 3:

• Если обычно важен ORDER BY created_at DESC LIMIT 50, лучше начинать с:

``sql
orders_paid_created_at_desc_idx
``

• Если домен email очень селективный и таких customers мало, может быть выгоднее:

``sql
orders_paid_customer_created_at_idx
``

• На практике можно создать оба на staging/реплике и сравнить планы. На production лучше не плодить индексы без проверки.

Вариант 4. Специализированный индекс для suffix-поиска email через reverse

Так как условие ищет именно окончание строки:

LIKE '%@corp-example.ru'

можно сделать btree-индекс по перевёрнутой строке:

CREATE INDEX CONCURRENTLY customers_reverse_lower_email_idx
ON customers (reverse(lower(email)) text_pattern_ops);

И переписать условие:

reverse(lower(c.email)) LIKE reverse('@corp-example.ru') || '%'

Полный запрос:

SELECT o.id, o.created_at, o.total, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid'
  AND o.created_at >= now() - interval '30 days'
  AND reverse(lower(c.email)) LIKE reverse('@corp-example.ru') || '%'
ORDER BY o.created_at DESC
LIMIT 50;

Ожидаемый эффект:

• PostgreSQL сможет использовать btree-индекс для prefix-поиска по перевёрнутой строке.
• Для поиска именно suffix это может быть компактнее и дешевле, чем GIN trigram.

Цена:

• Запрос становится менее очевидным.
• Индекс полезен в основном для suffix-поиска, а не для произвольного LIKE '%abc%'.
• Нужно следить, чтобы выражение в запросе совпадало с выражением в индексе.
• Дополнительный индекс замедляет изменения email.

Для вашего случая, где фильтр всегда по домену, этот вариант может быть очень хорошим. Если же бывают разные substring-поиски, лучше pg_trgm.

Вариант 5. Изменение схемы: хранить домен email отдельно

Самый чистый вариант, если такие запросы частые:

ALTER TABLE customers
ADD COLUMN email_domain text
GENERATED ALWAYS AS (
  lower(substring(email from position('@' in email) + 1))
) STORED;

Индекс:

CREATE INDEX CONCURRENTLY customers_email_domain_idx
ON customers (email_domain);

Тогда запрос:

SELECT o.id, o.created_at, o.total, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid'
  AND o.created_at >= now() - interval '30 days'
  AND c.email_domain = 'corp-example.ru'
ORDER BY o.created_at DESC
LIMIT 50;

Ожидаемый эффект:

• Вместо LIKE '%@corp-example.ru' будет точное равенство по btree-индексу.
• Это быстрее, проще и стабильнее для planner'а.
• Хорошо масштабируется.

Цена:

• ALTER TABLE ... ADD COLUMN ... GENERATED ... STORED для большой таблицы может потребовать переписывания таблицы или существенной работы.
• Нужно планировать миграцию.
• Увеличится размер таблицы.
• Появится индекс, который надо поддерживать при изменении email.

Более контролируемая альтернатива:

ALTER TABLE customers ADD COLUMN email_domain text;

Потом батчами заполнить:

UPDATE customers
SET email_domain = lower(split_part(email, '@', 2))
WHERE email_domain IS NULL;

Затем добавить индекс:

CREATE INDEX CONCURRENTLY customers_email_domain_idx
ON customers (email_domain);

И поддерживать значение триггером или на уровне приложения.

3. Как создать индекс на production без долгой блокировки таблицы

Используйте CREATE INDEX CONCURRENTLY.

Например:

CREATE INDEX CONCURRENTLY orders_paid_created_at_desc_idx
ON orders (created_at DESC)
INCLUDE (id, customer_id, total)
WHERE status = 'paid';

И для customers:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX CONCURRENTLY customers_lower_email_trgm_idx
ON customers
USING gin (lower(email) gin_trgm_ops);

Важно:

1. CREATE INDEX CONCURRENTLY нельзя запускать внутри явной транзакции:

Нельзя:

``sql
BEGIN;
CREATE INDEX CONCURRENTLY ...;
COMMIT;
``

1. Он не блокирует обычные SELECT/INSERT/UPDATE/DELETE на длительное время.

1. Но краткие блокировки всё равно будут в начале и конце операции.

1. Создание может идти долго и сильно нагружать диск.

1. Если операция прервётся, может остаться невалидный индекс. Проверить:

``sql
SELECT
c.relname AS index_name,
i.indisvalid,
i.indisready
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE c.relname IN (
'orders_paid_created_at_desc_idx',
'customers_lower_email_trgm_idx'
);
``

Если индекс невалидный, его обычно удаляют:

``sql
DROP INDEX CONCURRENTLY index_name;
``

1. На время создания стоит следить за прогрессом:

``sql
SELECT *
FROM pg_stat_progress_create_index;
``

1. После создания индекса желательно обновить статистику:

``sql
ANALYZE orders;
ANALYZE customers;
``

4. Как проверить результат

Запустить:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT o.id, o.created_at, o.total, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid'
  AND o.created_at >= now() - interval '30 days'
  AND lower(c.email) LIKE '%@corp-example.ru'
ORDER BY o.created_at DESC
LIMIT 50;

Если добавили reverse-индекс, проверять надо переписанный запрос:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT o.id, o.created_at, o.total, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid'
  AND o.created_at >= now() - interval '30 days'
  AND reverse(lower(c.email)) LIKE reverse('@corp-example.ru') || '%'
ORDER BY o.created_at DESC
LIMIT 50;

Что должно измениться:

После индекса на orders

Вместо:

Seq Scan on orders o

желательно увидеть что-то вроде:

Index Scan using orders_paid_created_at_desc_idx on orders o

или:

Index Only Scan using orders_paid_created_at_desc_idx on orders o

Также хорошо, если исчезнет отдельный дорогой sort или он станет совсем маленьким. Возможный план:

Limit
  -> Nested Loop
       -> Index Scan using orders_paid_created_at_desc_idx on orders o
       -> Index Scan using customers_pkey on customers c

При этом PostgreSQL может идти по свежим paid-заказам в порядке created_at DESC, проверять customer'ов и останавливаться после 50 строк.

Смотреть нужно на:

actual time
Buffers: shared hit/read
Rows Removed by Filter

Особенно важно, чтобы больше не было чтения миллионов строк из orders.

После trigram-индекса на customers

Вместо:

Seq Scan on customers c

ожидаемо увидеть:

Bitmap Heap Scan on customers c
  -> Bitmap Index Scan on customers_lower_email_trgm_idx

Это будет означать, что фильтр:

lower(email) LIKE '%@corp-example.ru'

использует GIN trigram-индекс.

После индекса orders_paid_customer_created_at_idx

Возможный хороший план:

Nested Loop
  -> Bitmap Heap Scan on customers c
       -> Bitmap Index Scan on customers_lower_email_trgm_idx
  -> Index Scan using orders_paid_customer_created_at_idx on orders o
       Index Cond: (
         customer_id = c.id
         AND created_at >= now() - interval '30 days'
       )

Здесь PostgreSQL сначала находит customers с нужным доменом, потом для каждого быстро ищет заказы.

Рекомендованный порядок действий

Я бы начал так:

CREATE INDEX CONCURRENTLY orders_paid_created_at_desc_idx
ON orders (created_at DESC)
INCLUDE (id, customer_id, total)
WHERE status = 'paid';

Затем:

ANALYZE orders;

Проверить план через:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE) ...

Если customers всё ещё заметен в плане, добавить:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX CONCURRENTLY customers_lower_email_trgm_idx
ON customers
USING gin (lower(email) gin_trgm_ops);

ANALYZE customers;

Если такие запросы частые и домен email — бизнес-значимое поле, лучше в перспективе уйти от:

lower(email) LIKE '%@corp-example.ru'

к отдельному полю:

email_domain = 'corp-example.ru'

с обычным btree-индексом.

Claude Sonnet 506.10.2026

Анализ и ускорение запроса

1. Где тратится время

Главный виновник — Seq Scan on orders:

Seq Scan on orders o  (actual time=0.03..4410.7 rows=412000 loops=1)
  Filter: ((status = 'paid') AND (created_at >= ...))
  Rows Removed by Filter: 11588000

Из общих 4812.9 мс сюда уходит ~4410 мс (92% времени). Postgres читает все 12 млн строк таблицы, потому что нет индекса, покрывающего связку status + created_at. Есть только PK(id) и индекс по customer_id — они тут бесполезны. Об этом же говорит Buffers: read=402377 — это физическое чтение с диска/из ОС-кэша, а не из shared buffers, т.е. полное сканирование действительно «тяжёлое».

Второй источник — Seq Scan on customers:

Filter: (lower(email) ~~ '%@corp-example.ru'::text)
Rows Removed by Filter: 896900

~210 мс, меньше по абсолютному вкладу, но здесь проблема принципиальная: паттерн начинается с %, обычный B-tree индекс на email в принципе не может ускорить такой LIKE, даже если убрать lower().

Почему выбран Hash Join, а не Nested Loop: оптимизатор не видит дешёвого способа получить маленький набор customers и идти в orders по индексу — Seq Scan на orders и так уже выглядит "заложенным" в стоимость, а подходящего индекса (customer_id, status, created_at) нет. Поэтому оба входа читаются полностью и соединяются хэшем.

Sort/Limit — не проблема, top-N heapsort уже дешёвый (32 KB, доли мс).

2. Варианты ускорения (по убыванию эффекта)

Вариант 1 — индексы (наибольший эффект, рекомендую)

a) Частичный индекс на orders под самый горячий фильтр:

CREATE INDEX CONCURRENTLY idx_orders_paid_created_at
ON orders (created_at DESC)
WHERE status = 'paid';

• Эффект: убирает Seq Scan по 12 млн строк. Индекс содержит только ~30% строк (paid), сразу отсортирован по created_at DESC — для ORDER BY ... LIMIT 50 планировщик сможет читать его «сверху вниз» и рано остановиться.
• Цена: место — примерно как обычный btree на 3.6 млн строк (несколько сотен МБ, не 12 млн). Запись — слегка дороже при INSERT/UPDATE только для заказов со статусом paid (для new/shipped/cancelled индекс не трогается вообще — это и есть прелесть partial index).

b) Композитный индекс на orders под join по клиенту (если захотите развернуть Nested Loop):

CREATE INDEX CONCURRENTLY idx_orders_customer_status_created
ON orders (customer_id, status, created_at DESC);

• Эффект: если оптимизатор выберет путь "сначала customers → потом orders по customer_id", это даст быстрый Index Scan на orders для каждого найденного клиента.
• Цена: дороже по месту (полный индекс на 12 млн строк, 3 колонки) и чуть дороже на запись при любом статусе. Создавайте только если (a) не даст достаточного ускорения — обычно (a) само по себе уже решает проблему.

c) Индекс на customers под LIKE с любым расположением wildcard (универсальный):

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX CONCURRENTLY idx_customers_email_trgm
ON customers USING gin (lower(email) gin_trgm_ops);

• Эффект: lower(c.email) LIKE '%@corp-example.ru' сможет использовать GIN по триграммам вместо Seq Scan — отбор ~3100 строк из 900 тыс. почти мгновенно.
• Цена: GIN занимает больше места, чем btree (обычно в 1.5–3 раза больше объёма данных колонки), и дороже на запись/обновление — но email обновляется редко, так что это приемлемо.

d) Альтернатива (c), если паттерн всегда «хвостовой» (%@domain, без wildcard в конце) — дешевле по ресурсам:

CREATE INDEX CONCURRENTLY idx_customers_email_rev
ON customers (reverse(lower(email)));

Запрос переписывается:

AND reverse(lower(c.email)) LIKE reverse('%@corp-example.ru')

• Эффект: обычный B-tree, работает как LIKE 'xxx%' — очень дёшево.
• Цена: меньше места и дешевле на запись, чем GIN, но хрупко: если кто-то добавит LIKE '%corp%' (wildcard с двух сторон), этот индекс не поможет — тогда нужен именно pg_trgm.

Рекомендация: начать с (a) — он решает 90% проблемы в одиночку. Затем добавить (c) для email-фильтра. Если после этого Hash Join всё ещё не превращается в Nested Loop и время не устраивает — добавить (b).

Вариант 2 — переписать запрос (эффект средний, без него индексы работают не так красиво)

Явно избавиться от lower() на уровне данных, если возможно:

-- при вставке/обновлении всегда хранить email в нижнем регистре,
-- либо сделать email колонкой типа citext

Тогда можно обойтись обычным индексом без функционального выражения, что немного снижает сложность плана и объём индекса. Это изменение схемы, делать осторожно (см. ниже).

Также полезно явно подсказать планировщику порядок через EXISTS, если после индексов CBO всё равно упрямится:

SELECT o.id, o.created_at, o.total, c.email
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE lower(c.email) LIKE '%@corp-example.ru'
  AND o.status = 'paid'
  AND o.created_at >= now() - interval '30 days'
ORDER BY o.created_at DESC
LIMIT 50;

Семантически то же самое, но иногда помогает оптимизатору выбрать customers как driving-таблицу (на практике в PG 16 это обычно не нужно, CBO сам разберётся при наличии индексов — но проверить стоит).

Вариант 3 — изменения схемы (эффект высокий, но дороже в реализации)

• Партиционирование orders по created_at (например, помесячно). При фильтре created_at >= now() - 30 days partition pruning сразу отсекает 11+ месяцев данных. Эффективно на бóльших объёмах и долгосрочно, но требует миграции данных, обновления внешних ключей/логики приложения — это тяжёлая операция, делать только если объём orders продолжит расти (сейчас 12 млн, а индекс (a) уже решает задачу дешевле).
• citext для email — упрощает запросы с регистронезависимым поиском, убирает lower() из всех запросов разом. Миграция: ALTER TABLE customers ALTER COLUMN email TYPE citext; — перезапишет таблицу, требует ACCESS EXCLUSIVE LOCK на время операции (на 900 тыс. строк это быстро, но не "без блокировки"). Делать в окно обслуживания либо через pg_repack/логическую репликацию, если нужна нулевая блокировка.

3. Создание индекса на проде без долгой блокировки

CREATE INDEX CONCURRENTLY idx_orders_paid_created_at
ON orders (created_at DESC)
WHERE status = 'paid';

Важные нюансы:

• CONCURRENTLY не держит блокировку, препятствующую чтению/записи таблицы (но блокирует другие DDL на эту же таблицу, например другой CREATE INDEX CONCURRENTLY одновременно нельзя).
• Нельзя выполнять внутри транзакционного блока (BEGIN...COMMIT) — только отдельной командой.
• Делает два прохода по таблице, может занять больше времени, чем обычный CREATE INDEX.
• Если во время создания произойдёт сбой/отмена — индекс останется в состоянии INVALID. Проверить:

SELECT indexrelid::regclass, indisvalid
FROM pg_index
WHERE indisvalid = false;

Если такое найдено — дропнуть и пересоздать:

DROP INDEX CONCURRENTLY idx_orders_paid_created_at;

• Мониторить прогресс:

SELECT * FROM pg_stat_progress_create_index;

• Желательно выполнять в период низкой нагрузки на запись (хотя блокировок нет, build индекса создаёт дополнительную I/O и CPU нагрузку).

4. Как проверить результат

Выполнить тот же запрос с теми же параметрами:

EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT o.id, o.created_at, o.total, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid'
  AND o.created_at >= now() - interval '30 days'
  AND lower(c.email) LIKE '%@corp-example.ru'
ORDER BY o.created_at DESC
LIMIT 50;

Что должно измениться в плане:

1. Seq Scan on orders → должен смениться на Index Scan или Bitmap Heap Scan по idx_orders_paid_created_at, с полем Index Cond вместо Filter, и Rows Removed by Filter должен стать близким к нулю.
2. Seq Scan on customers → Bitmap Index Scan / Index Scan по idx_customers_email_trgm (или по reverse-индексу).
3. Возможно, поменяется сам тип join — ожидаемо увидеть Nested Loop вместо Hash Join, если customers стал маленьким и дешёвым входом.
4. Sort узел может исчезнуть полностью, если индекс на orders уже даёт нужный порядок (created_at DESC) — тогда LIMIT сработает прямо поверх Index Scan.
5. В Buffers: соотношение read к hit должно сильно упасть в сторону hit (меньше физического IO), а общее число buffers — упасть на порядки, не на проценты.
6. Execution Time — главный интегральный показатель, но в первую очередь смотрите на структуру плана, а не только на итоговое время: нужно убедиться, что ушли оба Seq Scan, а не просто ускорился один узел.

Дополнительно не забудьте после создания индексов:

ANALYZE orders;
ANALYZE customers;

чтобы статистика планировщика была актуальной и он действительно выбрал новые индексы, а не продолжил использовать старый план по старым оценкам.

Gemini 2.5 Flash06.10.2026

Привет! Как эксперт по производительности PostgreSQL 16, я готов помочь вам разобраться с этим запросом.

1. Где тратится основное время и почему

Основное время тратится в следующих узлах плана:

• Seq Scan on orders o (actual time=0.03..4410.7 rows=412000 loops=1)
• Почему: Этот узел выполняет последовательное сканирование всей таблицы orders. Несмотря на то, что у вас 12 млн строк, фильтр (status = 'paid') AND (created_at >= (now() - '30 days'::interval)) отбрасывает огромное количество строк (Rows Removed by Filter: 11588000). Сканирование 12 миллионов строк, даже если большинство из них отфильтровывается, является очень дорогой операцией, особенно с учетом чтения 402377 буферов. Это занимает 4410.7 мс, что составляет подавляющее большинство времени выполнения запроса.
• Hash Join (actual time=211.5..4806.9 rows=1840 loops=1)
• Почему: Хотя сам Hash Join не является самым медленным узлом, он вынужден обрабатывать большой объем данных, поступающих от Seq Scan on orders o (412000 строк). Это приводит к тому, что он наследует большую часть задержки от сканирования таблицы orders.
• Seq Scan on customers c (actual time=0.02..209.8 rows=3100 loops=1)
• Почему: Аналогично, здесь выполняется последовательное сканирование таблицы customers для применения фильтра lower(email) ~~ '%@corp-example.ru'::text. Несмотря на то, что таблица customers меньше (900 тыс. строк), сканирование всей таблицы для поиска по части строки (LIKE '%...') является неэффективным. Это занимает 209.8 мс.

Вывод: Главная проблема в том, что PostgreSQL вынужден сканировать очень большие таблицы (orders и customers) для применения фильтров, которые могли бы быть ускорены индексами.

2. Варианты ускорения по убыванию эффекта

Вариант 1: Индексы (наибольший эффект)

1.1. Индекс на orders для фильтрации и сортировки

• DDL:

``sql
CREATE INDEX CONCURRENTLY idx_orders_status_created_at_customer_id ON orders (status, created_at DESC, customer_id);
``

• Ожидаемый эффект:
• Значительное ускорение Seq Scan on orders o: PostgreSQL сможет использовать этот индекс для быстрого поиска строк, удовлетворяющих условиям status = 'paid' и created_at >= now() - interval '30 days'.
• Ускорение сортировки: Поскольку created_at включен в индекс в порядке DESC, PostgreSQL сможет избежать отдельной операции сортировки (Sort узел) или выполнить ее гораздо быстрее, используя индексный порядок.
• Ускорение Hash Join: Количество строк, поступающих на вход Hash Join, будет значительно меньше, так как индекс уже отфильтрует большую часть данных.
• Тип индекса: B-tree.
• Цена:
• Место на диске: Индекс будет занимать дополнительное место на диске, пропорционально количеству строк в таблице orders и размеру индексируемых столбцов. Для 12 млн строк это может быть несколько сотен мегабайт или даже гигабайт.
• Замедление записи: Вставка, обновление и удаление строк в таблице orders будут немного медленнее, так как PostgreSQL придется обновлять и этот индекс. Однако для большинства OLTP-систем это приемлемая плата за значительно более быстрые чтения.
• Блокировки при создании: При использовании CONCURRENTLY блокировки будут минимальными (см. пункт 3).

1.2. Индекс на customers для фильтрации по email (частичный индекс)

• Проблема: lower(c.email) LIKE '%@corp-example.ru' не может эффективно использовать обычный B-tree индекс из-за начального %.
• Решение: Использовать функциональный индекс или индекс на основе триграмм.
• Функциональный индекс (для точного совпадения или LIKE 'prefix%'):

Если бы запрос был lower(c.email) LIKE '@corp-example.ru%', то можно было бы создать:
``sql
CREATE INDEX CONCURRENTLY idx_customers_lower_email ON customers (lower(email));
`
Но для '%@corp-example.ru'` это не поможет.

• Индекс на основе триграмм (для LIKE '%substring%'):

Это лучший вариант для вашего случая. Сначала нужно установить расширение pg_trgm:
``sql
CREATE EXTENSION pg_trgm;
CREATE INDEX CONCURRENTLY idx_customers_email_trgm ON customers USING GIN (lower(email) gin_trgm_ops);
``

• Ожидаемый эффект:
• Значительное ускорение Seq Scan on customers c: PostgreSQL сможет использовать GIN-индекс для быстрого поиска строк, удовлетворяющих условию lower(email) LIKE '%@corp-example.ru'.
• Ускорение Hash узла: Количество строк, поступающих на вход Hash, будет значительно меньше.
• Цена:
• Место на диске: GIN-индексы на триграммах могут быть довольно большими, особенно для столбцов с длинными и разнообразными строками.
• Замедление записи: GIN-индексы обычно медленнее обновляются, чем B-tree индексы, но для таблицы customers (900 тыс. строк) это может быть приемлемо, если записи не происходят очень часто.
• Блокировки при создании: При использовании CONCURRENTLY блокировки будут минимальными (см. пункт 3).

Вариант 2: Переписанный запрос (незначительный эффект без индексов, но может быть полезен в комбинации)

В данном случае, ваш запрос уже достаточно оптимален с точки зрения структуры. Основные улучшения придут от индексов. Переписывание запроса без соответствующих индексов не даст существенного выигрыша.

Однако, если бы вы могли изменить условие на c.email LIKE '%@corp-example.ru' на c.email LIKE '@corp-example.ru%' (т.е. если бы домен всегда был в начале), то можно было бы использовать обычный B-tree индекс на lower(email). Но это, скорее всего, не соответствует вашей бизнес-логике.

Вариант 3: Изменения схемы (потенциально большой эффект, но высокая цена)

3.1. Денормализация (добавление email в таблицу orders)

• Идея: Если email клиента часто используется для фильтрации заказов, можно добавить столбец customer_email в таблицу orders.
• Ожидаемый эффект:
• Устранение JOIN: Запрос станет значительно проще, так как не потребуется соединение с таблицей customers.
• Устранение Seq Scan on customers c: Этот узел полностью исчезнет.
• Потенциальное ускорение: Если создать индекс на lower(customer_email) в таблице orders, это может быть очень быстро.
• Цена:
• Избыточность данных: Дублирование данных email в таблице orders.
• Проблемы с согласованностью: Если email клиента изменится, его нужно будет обновить во всех соответствующих заказах. Это требует триггеров или логики приложения, что усложняет систему.
• Место на диске: Увеличение размера таблицы orders.
• Сложность реализации: Изменение схемы большой таблицы, миграция данных.

3.2. Материализованное представление (для отчетов)

• Идея: Создать материализованное представление, которое предварительно вычисляет результат вашего запроса.
• Ожидаемый эффект:
• Мгновенное выполнение запроса: Запрос к материализованному представлению будет очень быстрым, так как данные уже подготовлены.
• Цена:
• Устаревание данных: Материализованное представление не обновляется автоматически. Его нужно периодически обновлять (REFRESH MATERIALIZED VIEW), что может быть ресурсоемкой операцией и приводить к временной недоступности актуальных данных.
• Место на диске: Занимает дополнительное место.
• Сложность: Требует планирования расписания обновлений.

Рекомендация: Начните с Варианта 1 (индексы). Это наиболее эффективный и наименее инвазивный способ решения вашей проблемы. Денормализация или материализованные представления являются более радикальными мерами, которые следует рассматривать только в том случае, если индексы не дают достаточного улучшения или если запрос является частью очень специфичного отчета.

3. Как создать индекс на продакшене без долгой блокировки таблицы

Используйте ключевое слово CONCURRENTLY при создании индекса.

CREATE INDEX CONCURRENTLY idx_orders_status_created_at_customer_id ON orders (status, created_at DESC, customer_id);

CREATE EXTENSION pg_trgm; -- Если еще не установлено
CREATE INDEX CONCURRENTLY idx_customers_email_trgm ON customers USING GIN (lower(email) gin_trgm_ops);

Как это работает:

1. PostgreSQL создает временный индекс.
2. Он сканирует таблицу, чтобы заполнить временный индекс, не блокируя операции записи на таблицу.
3. Затем он выполняет второе сканирование, чтобы поймать любые изменения, произошедшие во время первого сканирования.
4. На короткое время (обычно миллисекунды) он получает эксклюзивную блокировку таблицы, чтобы атомарно заменить старый индекс новым и зафиксировать изменения. Эта блокировка очень короткая и обычно не вызывает проблем в продакшене.

Важные моменты:

• CREATE INDEX CONCURRENTLY занимает больше времени, чем обычный CREATE INDEX.
• В случае ошибки (например, если транзакция, которая удерживает блокировку, не завершается), индекс может остаться в состоянии "invalid". В этом случае его нужно будет удалить (DROP INDEX) и попробовать создать заново.
• Нельзя использовать CONCURRENTLY внутри транзакции.

4. Как проверить результат: какой EXPLAIN запустить и что в нём должно измениться

Для проверки результата запускайте тот же EXPLAIN (ANALYZE, BUFFERS):

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.created_at, o.total, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid'
  AND o.created_at >= now() - interval '30 days'
  AND lower(c.email) LIKE '%@corp-example.ru'
ORDER BY o.created_at DESC
LIMIT 50;

Что должно измениться в плане:

1. Seq Scan on orders o должен исчезнуть или превратиться в Index Scan или Bitmap Index Scan:
2. Вместо Seq Scan on orders o вы увидите что-то вроде:

``
Index Scan Backward using idx_orders_status_created_at_customer_id on orders o
`
или
`
Bitmap Heap Scan on orders o
Recheck Cond: ((status = 'paid') AND (created_at >= (now() - '30 days'::interval)))
-> Bitmap Index Scan on idx_orders_status_created_at_customer_id
Index Cond: ((status = 'paid') AND (created_at >= (now() - '30 days'::interval)))
``

• actual time этого узла должно значительно уменьшиться (с 4410.7 мс до десятков или единиц миллисекунд).
• Rows Removed by Filter должно быть очень низким или отсутствовать.
• Buffers: shared hit и read для этого узла должны значительно уменьшиться.

1. Sort узел может исчезнуть или стать очень быстрым:
2. Если индекс idx_orders_status_created_at_customer_id используется для сортировки, узел Sort может исчезнуть, или его actual time будет очень низким, так как данные уже будут поступать в нужном порядке.

1. Seq Scan on customers c должен исчезнуть или превратиться в Index Scan:
2. Вместо Seq Scan on customers c вы увидите что-то вроде:

``
Index Scan using idx_customers_email_trgm on customers c
Index Cond: (lower(email) ~~ '%@corp-example.ru'::text)
`
или
`
Bitmap Heap Scan on customers c
Recheck Cond: (lower(email) ~~ '%@corp-example.ru'::text)
-> Bitmap Index Scan on idx_customers_email_trgm
Index Cond: (lower(email) ~~ '%@corp-example.ru'::text)
``

• actual time этого узла должно значительно уменьшиться (с 209.8 мс до десятков или единиц миллисекунд).
• Rows Removed by Filter должно быть очень низким или отсутствовать.
• Buffers: shared hit и read для этого узла должны значительно уменьшиться.

1. Общее Execution Time должно значительно уменьшиться:
2. Ожидайте сокращения с 4812.9 мс до сотен или десятков миллисекунд.
3. Общее количество Buffers: shared hit и read также должно существенно сократиться.

Удачи в оптимизации!