Когда запрос ползёт по две-три секунды или в отчёте всплывают дубли, тянет выбрать один из двух привычных путей: докупить сервер помощнее или выгрузить строки в бэкенд и разгрести циклом. Оба варианта спасают лишь до следующего роста таблицы. Собрали три показательных кейса из SQL Portal, где проблему чинят прямо в запросе — пониманием индексов, кардинальности и окон строк.
Ускоряем поиск товаров с 2,5 секунд до 80 мс без нового железа
Авторы начинают с точной формулы: большинство «проблем с производительностью БД» — это плохо написанный SQL под маской проблем с базой. Каталог products на мощном сервере отвечает по 2,5 секунды:
-- Исходный запрос (~2,5 с): SELECT *, LOWER() и OFFSET
SELECT *
FROM products p
WHERE p.category_id = $1
AND p.active = TRUE
AND p.price_cents BETWEEN $2 AND $3
AND LOWER(p.name) LIKE LOWER('%' || $4 || '%')
ORDER BY p.created_at DESC
OFFSET $5 LIMIT $6;
Тут сошлись четыре ловушки. SELECT * тащит тяжёлое description. LOWER(p.name) LIKE '%...%' с ведущим процентом отключает обычный B-tree индекс. Сортировка по created_at идёт после фильтрации, а OFFSET заставляет сервер на каждой странице перечитывать и отбрасывать предыдущие строки.
Чинится всё без апгрейда. Сначала собирают покрывающий индекс по правилу «равенство → диапазон → сортировка» с INCLUDE (name) — в PostgreSQL (с 11-й версии) это даёт Index-Only Scan без похода в таблицу, пока VACUUM обновляет карту видимости. Затем подключают pg_trgm и вешают триграммный GIN-индекс на name под ILIKE (для 1–2 символов лучше поставить UX-ограничение). Наконец, меняют OFFSET на keyset-пагинацию (seek pagination) по координатам последней строки:
-- Покрывающий индекс (CONCURRENTLY не блокирует запись)
CREATE INDEX CONCURRENTLY idx_products_feed
ON products (category_id, active, price_cents,
created_at DESC, product_id DESC)
INCLUDE (name);
-- Триграммный GIN-индекс для поиска подстроки
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX CONCURRENTLY idx_products_name_trgm
ON products USING gin (name gin_trgm_ops);
-- Быстрый запрос с keyset-пагинацией (~80 мс)
SELECT p.product_id, p.name, p.price_cents, p.created_at
FROM products p
WHERE p.category_id = $1
AND p.active = TRUE
AND p.price_cents BETWEEN $2 AND $3
AND p.name ILIKE '%' || $4 || '%'
AND (p.created_at, p.product_id) < ($6, $7)
ORDER BY p.created_at DESC, p.product_id DESC
LIMIT $5;
Ответ падает с 2,5 секунды до 80 мс — быстрее в 30+ раз. Проверить план помогает EXPLAIN (ANALYZE, BUFFERS). Мощное железо лишь маскирует плохую архитектуру до следующего скачка нагрузки.
Кардинальность: почему индекс по полу пользователя работает вхолостую
На собеседованиях любят задачку с подвохом: «Что не так с этим скриптом?» Автор честно признаётся: сначала искал опечатку в синтаксисе, а дело оказалось в данных.
-- Синтаксис чистый, но один индекс работает вхолостую
CREATE TABLE Employees (
ID INT PRIMARY KEY,
Name VARCHAR(100),
Department VARCHAR(50),
Gender CHAR(1)
);
CREATE INDEX idx_name ON Employees(Name);
CREATE INDEX idx_gender ON Employees(Gender);
Всё решает кардинальность (cardinality) — доля уникальных значений в столбце. На таблице Employees из миллиона строк разница налицо:
| Столбец | Уникальных значений | Кардинальность | Польза от индекса |
|---|---|---|---|
ID | 1 000 000 | Высокая | Максимальная |
Name | Сотни тысяч | Высокая | Полезен |
Department | 10–20 | Средняя | Ограниченная |
Gender | 2–3 | Очень низкая | Почти нулевая |
Каждому значению Gender отвечает треть или половина таблицы, так что индекс не сужает поиск. Планировщик его игнорирует и запускает Full Table Scan — полное чтение таблицы. Но при каждом INSERT, UPDATE и DELETE база тратит диск и CPU на обновление idx_gender.
Низкокардинальные поля индексируют иначе: через составной индекс в паре с уникальным полем или частичный индекс под редкую долю строк (например, 5% открытых заказов):
-- Работает для Department и Department + ID,
-- но НЕ для поиска только по ID (левый префикс B-tree)
CREATE INDEX idx_dept_emp ON Employees (Department, ID);
-- Частичный индекс в PostgreSQL для редкого статуса
CREATE INDEX idx_orders_open ON orders (created_at)
WHERE active = TRUE;
Перед любым CREATE INDEX проверяй распределение значений: слабый индекс лишь тормозит запись.
Ищем повторные покупки за 5 минут через оконную функцию LAG
Классика платёжных систем: таблица CustomerPurchases (PurchaseId, CustomerId, StoreId, ProductId, PurchaseTimestamp). Нужно поймать случаи, когда один клиент купил тот же товар в том же магазине повторно за пять минут — признак дубля или двойного списания.
Тяжёлый self-JOIN здесь не нужен. Задачу за один проход закрывает CTE (WITH ... AS, именованный подзапрос) и оконная функция LAG — она подсматривает время прошлой покупки внутри группы (PARTITION BY):
-- PostgreSQL: вычитаем даты и сравниваем с INTERVAL
WITH PurchasesWithLag AS (
SELECT
PurchaseId, CustomerId, StoreId, ProductId,
PurchaseTimestamp,
LAG(PurchaseTimestamp) OVER (
PARTITION BY CustomerId, StoreId, ProductId
ORDER BY PurchaseTimestamp
) AS PreviousPurchaseTimestamp
FROM CustomerPurchases
)
SELECT
PurchaseId, CustomerId, StoreId, ProductId,
PreviousPurchaseTimestamp, PurchaseTimestamp
FROM PurchasesWithLag
WHERE PreviousPurchaseTimestamp IS NOT NULL
AND PurchaseTimestamp - PreviousPurchaseTimestamp
<= INTERVAL '5 minutes'
ORDER BY CustomerId, PurchaseTimestamp;
Важны два нюанса. На первой покупке в группе предыдущей строки нет, и LAG вернёт NULL — без IS NOT NULL отбор сломается. И учитывай диалект: в исходном примере под SQL Server стоит DATEDIFF (DATEDIFF(MINUTE, prev, curr) <= 5), но он считает пересечения границ минут (12:00:59 и 12:01:01 дадут 1), так что для точных 300 секунд в T-SQL надёжнее DATEDIFF(SECOND, ...) <= 300, а в PostgreSQL — INTERVAL '5 minutes'.


