PostgreSQL может избежать полной сортировки таблицы при ORDER BY ... LIMIT!

Большинство думает, что ORDER BY всегда сортирует вообще всю таблицу. Но в PostgreSQL для LIMIT есть отдельная оптимизация — Top-N Heap Sort.

SELECT *
FROM orders
ORDER BY created_at DESC
LIMIT 10;

Если нужен только top-10 результат, PostgreSQL не обязан сортировать миллионы строк полностью. Вместо этого он держит в памяти только N лучших строк во время scan.

Это видно прямо в execution plan:

Sort Method: top-N heapsort

Особенно интересно становится на огромных таблицах, где полный sort мог бы уйти на диск:

SET work_mem = '4MB';

EXPLAIN ANALYZE
SELECT *
FROM events
ORDER BY created_at DESC
LIMIT 50;

Даже при маленьком work_mem PostgreSQL может избежать полной сортировки всех строк и работать заметно быстрее именно благодаря оптимизации Top-N.

А если добавить подходящий индекс:

CREATE INDEX idx_events_created_at
ON events (created_at DESC);

🔥 PostgreSQL вообще сможет обойтись без Sort и просто сделать Index Scan в нужном порядке.

➡️ SQL Ready | #совет

Почему индекс может не использоваться, даже если он есть!

Очень частая ситуация: индекс создан, запрос написан вроде нормально, но SQL всё равно делает Seq Scan или Full Table Scan.

Имеем такую таблицу:

users(
    id,
    email,
    created_at
)

Индекс:

CREATE INDEX idx_users_email
ON users(email);

Кажется, что такой запрос точно должен использовать индекс:

SELECT *
FROM users
WHERE email = 'test@example.com';

И обычно действительно будет Index Scan.

Но достаточно небольшой детали — и индекс может перестать использоваться. Например:

SELECT *
FROM users
WHERE LOWER(email) = 'test@example.com';

Проблема в том, что индекс построен по колонке: email. А в условии используется выражение:

LOWER(email)

Для оптимизатора это уже не то же самое условие. В итоге серверу часто приходится: применять LOWER() к строкам; сравнивать результат; читать гораздо больше данных, чем ожидалось.

Исправляется это expression/functional index:

CREATE INDEX idx_users_email_lower
ON users(LOWER(email));

То же самое часто происходит с датами:

SELECT *
FROM orders
WHERE DATE(created_at) = '2025-01-10';

Из-за:

DATE(created_at)

обычный индекс по created_at может не помочь, потому что функция применяется к колонке.

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

SELECT *
FROM orders
WHERE created_at >= '2025-01-10'
  AND created_at <  '2025-01-11';

Так условие остаётся sargable — то есть пригодным для эффективного использования индекса.

Ещё одна частая проблема — преобразования типов. Например, плохо:

WHERE user_id::text = '100'

Если user_id — integer, то здесь преобразование применяется к колонке. В такой ситуации обычный индекс по user_id может не использоваться.

Правильнее:

WHERE user_id = 100

Важно: простое условие вида:

WHERE user_id = '100'

в некоторых СУБД может нормально привести литерал к integer и всё равно использовать индекс. Проблема чаще начинается там, где преобразуется сама колонка или выражение становится сложнее.

Популярная ошибка с LIKE:

WHERE email LIKE '%gmail.com'

Здесь обычный B-Tree индекс обычно бесполезен. Так как поиск начинается не с начала строки; сервер не может эффективно использовать упорядоченность индекса.

Если таблица маленькая: 100–1000 строк, то Full Scan может быть дешевле, чем прыжки по индексу.

🔥 Наличие индекса ещё не гарантирует его использование. Функции над колонками, преобразования типов, неправильные LIKE, OR-условия и устаревшая статистика могут сделать индекс бесполезным или менее выгодным для оптимизатора.

➡️ SQL Ready | #практика

😎 SQL Cheatsheet — удобная шпаргалка для повседневной работы!

Этот репозиторий хорошо подойдёт тем, кто постоянно работает с базами данных и хочет быстро освежать в памяти нужные SQL-конструкции. Здесь всё подано компактно и по делу: запросы, JOIN’ы, агрегации, подзапросы и др. Особенно удобно использовать для подготовки к собеседованиям.

Оставляю ссылочку: GitHub 📱

➡️ SQL Ready | #репозиторий

🎓 PostgreSQL должен быть спроектирован до первого инцидента.

В enterprise-среде PostgreSQL — это уже не просто база данных, а часть критичной инфраструктуры с требованиями к доступности, восстановлению, безопасности и мониторингу.

На курсе «Архитектура PostgreSQL для корпоративных систем» разбираем, как строить устойчивую PostgreSQL-инфраструктуру под реальные корпоративные нагрузки.

📌 В фокусе курса:
• High Availability (высокая доступность)
• Disaster Recovery (аварийное восстановление)
• failover-сценарии
• RPO/RTO
• резервирование и мониторинг
• архитектурные ошибки и точки отказа

🐾 АИСыч напоминает: б бы «Backup — это не стратегия. Стратегия — это проверенное восстановление».

На курсе отдельно разбираем, как проектировать PostgreSQL-инфраструктуру так, чтобы система переживала сбои без потери критичных данных.

🎁 Для наших подписчиков действует скидка 20% по промокоду: SQLREADY20
Записаться на курс прямо сейчас

А ещё больше практики, ИИ и ИБ — в MAX Академии, где сейчас проходит розыгрыш курса за 1 рубль 🔥


Реклама АНО ДПО ЦПК "АИС", ИНН: 7720346012, erid: 2SDnjdvRDWz

PostgreSQL умеет обновлять только реально изменившиеся строки и это может сильно сократить WAL и нагрузку!

Многие приложения делают UPDATE даже тогда, когда данные вообще не изменились.

UPDATE users
SET name = 'Alex'
WHERE id = 1;

Даже если name уже равен 'Alex', PostgreSQL всё равно создаст новую версию строки (MVCC), запишет WAL, обновит индексы и увеличит нагрузку на autovacuum.

Проверить это можно через системную статистику:

SELECT n_tup_upd
FROM pg_stat_user_tables
WHERE relname = 'users';

Чтобы избежать “пустых” UPDATE, можно сравнивать старые и новые значения прямо в WHERE:

UPDATE users
SET
  name = $1,
  email = $2
WHERE id = $3
  AND (name, email) IS DISTINCT FROM ($1, $2);

IS DISTINCT FROM безопасно сравнивает даже NULL значения, в отличие от обычного !=:

SELECT
  NULL = NULL,
  NULL IS DISTINCT FROM NULL;

На highload-системах это может заметно уменьшить WAL, bloat, количество HOT/non-HOT update и нагрузку на autovacuum без изменения архитектуры.

➡️ SQL Ready | #совет

✍️ Информативная статья вышла на Хабре: «Обратная сторона массивов в PostgreSQL»!

В этой статье:
• Разбирается, почему массивы в PostgreSQL — это отдельная модель хранения со своими ограничениями и компромиссами;
• Показываются скрытые проблемы массивов: потеря ссылочной целостности, особенности GIN-индексов, TOAST, MVCC и дорогостоящие обновления;
• Объясняется, как правильно работать с массивами, когда использовать JSONB, intarray, pgvector и в каких случаях массивы действительно оправданы.

🔊 Продолжайте читать на Habr!

➡️ SQL Ready | #статья

❤️ PostgreSQL Tutorial — подробная документация и учебник по PostgreSQL!

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

📌 Оставляю ссылочку: postgresql.leopard.in.ua

➡️ SQL Ready | #ресурс