Введение
Когда запрос в PostgreSQL работает медленно, чаще всего помогают стандартные шаги: анализ плана, индексы, переписывание SQL, VACUUM/ANALYZE, иногда денормализация.
Но бывают ситуации, где «нестандартный» приём даёт заметный прирост — особенно в аналитике и BI, где много ad-hoc запросов и человеческих ошибок. Ниже — разбор идей из статьи “Unconventional PostgreSQL Optimizations” на русском (со слегка изменённой структурой).
Оригинал: https://hakibenita.com/postgresql-unconventional-optimizations
1) Как избежать бессмысленных Seq Scan с помощью CHECK-constraints
Иногда запрос гарантированно не может вернуть строки из-за ограничений (CHECK constraint), но PostgreSQL всё равно делает Seq Scan — тратит CPU и I/O, хотя результат заранее пустой. Это часто встречается в отчётах, где пользователь ошибся в значении фильтра (регистр, опечатка, неверный код).
Пример: значение колонки ограничено CHECK’ом, но запрос просит невозможное.
CREATE TABLE users (
id INT PRIMARY KEY,
username TEXT NOT NULL,
plan TEXT NOT NULL,
CONSTRAINT plan_check CHECK (plan IN ('free', 'pro'))
);Пользователь пишет:
SELECT * FROM users WHERE plan = 'Pro';Результат пустой — но без подсказок планировщику запрос может пройтись по таблице целиком.
Один из нетривиальных рычагов — параметр constraint_exclusion. При включении PostgreSQL способен понять, что условие заведомо ложное, и «срезать» выполнение до нуля работы.
SET constraint_exclusion = 'on';
EXPLAIN ANALYZE
SELECT * FROM users WHERE plan = 'Pro';Когда это полезно
- BI/аналитика: много произвольных запросов, больше ошибок — больше «пустых» тяжёлых сканов.
- Репортинг: выгодно на ролях/подключениях аналитиков, где важна экономия ресурсов.
- OLTP: может быть менее актуально из-за накладных расходов на планирование — проверяйте на практике.
2) Индексируем «смысл», а не точность: даты вместо timestamptz
Типичный отчёт: посчитать суммы по дням. Часто таблица хранит время с точностью до секунд/миллисекунд, но бизнесу нужны только «дневные бакеты».
Если индексировать исходный timestamptz, индекс может быть крупным и дорогим в обслуживании. Нестандартная идея: индексировать выражение с пониженной кардинальностью (например, только дату/день).
Пример запроса (агрегация по дням)
SELECT date_trunc('day', sold_at AT TIME ZONE 'UTC') AS day, SUM(charged) AS total FROM sale WHERE sold_at >= '2025-01-01 UTC' AND sold_at < '2025-02-01 UTC' GROUP BY 1;Function-based индекс по дате
CREATE INDEX sale_sold_at_date_ix ON sale (((date_trunc('day', sold_at AT TIME ZONE 'UTC'))::date));Чтобы индекс использовался, условие в WHERE должно совпадать с выражением индекса:
SELECT date_trunc('day', sold_at AT TIME ZONE 'UTC') AS day,
SUM(charged) AS total
FROM sale
WHERE (date_trunc('day', sold_at AT TIME ZONE 'UTC'))::date
BETWEEN '2025-01-01' AND '2025-01-31'
GROUP BY 1;Почему это может ускорить
- Меньше уникальных значений (низкая кардинальность) → индекс компактнее.
- Проще дедупликация и хранение повторов в индексе.
- Индекс ровно под типовые отчёты (по дням/неделям/месяцам), а не под «все возможные секунды».
3) “Discipline problem”: как не сломать function-based индекс руками
Главный минус function-based индекса — хрупкость. Чуть изменили выражение (другая функция, другой каст, другая TZ-логика) — и PostgreSQL может не распознать соответствие, уйдя в Seq Scan или другой план. Это называют “discipline problem”: все должны писать запросы «одинаково правильно».
Подход из статьи: использовать generated columns как единый «канонический» слой, чтобы в запросах фигурировала одна и та же колонка, а не десятки вариаций выражения.
Generated column как «точка истины»
Идея: добавить вычисляемую колонку, например, дату продажи.
ALTER TABLE sale
ADD COLUMN sold_at_date DATE
GENERATED ALWAYS AS ((date_trunc('day', sold_at AT TIME ZONE 'UTC'))::date) STORED;Теперь запросы становятся проще и стабильнее:
SELECT sold_at_date, SUM(charged) AS total
FROM sale
WHERE sold_at_date BETWEEN '2025-01-01' AND '2025-01-31'
GROUP BY 1;Плюсы подхода
- Меньше ошибок в запросах: люди используют готовую колонку, а не «изобретают» выражение заново.
- Понятнее семантика (особенно с таймзонами).
- Проще сопровождать и оптимизировать отчёты.
4) Уникальность для больших TEXT: «уникальный Hash» через exclusion constraint
Если у вас огромные текстовые значения (например, длинные URL) и почти нет повторов, btree-индекс для уникальности может занимать много места. Hash-индекс часто выходит компактнее, потому что оперирует хэшами.
Проблема: уникальные hash-индексы напрямую не поддерживаются. Нестандартный обход — использовать exclusion constraint, который в этом сценарии ведёт себя как «уникальность», но под капотом применяет hash-метод доступа.
Пример: уникальность URL через EXCLUDE USING HASH
ALTER TABLE urls ADD CONSTRAINT urls_url_unique_hash EXCLUDE USING HASH (url WITH =);Ограничения и подводные камни
- Foreign key может не получиться повесить так же, как на обычный UNIQUE (зависит от требований к уникальным ограничениям).
- UPSERT (INSERT … ON CONFLICT): чаще работает вариант с ON CONSTRAINT, но есть нюансы, особенно с DO UPDATE.
- Нужно внимательно проверить совместимость с вашим приложением и миграциями.
FAQ
Это можно применять в продакшене?
Можно, но это «нишевые» оптимизации. Всегда начинайте с EXPLAIN (ANALYZE, BUFFERS), измеряйте эффект на реальных данных и оценивайте влияние на запись/обслуживание индексов.
Когда стоит включать constraint_exclusion?
Чаще всего — для аналитических ролей/подключений, где много ad-hoc запросов и высок шанс «пустых» тяжёлых сканов из-за ошибок в фильтрах.
Function-based индекс — хороший выбор?
Да, если у вас стабильный паттерн запросов (например, «всегда по дням») и вы хотите уменьшить индекс/ускорить отчёты. Но помните про “discipline problem”.
Generated columns реально помогают?
Да: они упрощают запросы и стандартизируют логику. Это снижает вероятность «случайно не попасть в индекс» из-за другой формулы.
Hash через exclusion constraint — безопасно?
Технически это рабочий механизм, но с ограничениями (FK, ON CONFLICT). Перед внедрением проверьте, не ломает ли это вашу модель данных и типовые операции приложения.
Вывод
Нестандартные оптимизации полезны там, где стандартные подходы уже выжали максимум или где много «человеческого фактора».
Главные идеи:
- Использовать ограничения (CHECK) так, чтобы планировщик мог быстрее отсеивать невозможные условия.
- Индексировать не «сырой» timestamptz, а гранулярность, которая реально нужна бизнесу (дата/день/месяц).
- Снижать риск ошибок запросов через generated columns.
- Рассматривать альтернативные структуры (hash-подход) для больших редких TEXT, учитывая ограничения.
