Сортировка, DISTINCT, псевдонимы и пагинация

Сортировка, DISTINCT, псевдонимы и пагинация

Главная идея

Как это выглядит на практике

1. Базовая сортировка: SELECT id, email, created_at FROM users ORDER BY created_at DESC — пользователи от новых к старым. ASC (по умолчанию) — от меньшего к большему, DESC — от большего к меньшему.
2. Составная сортировка: SELECT * FROM orders ORDER BY status ASC, created_at DESC. Сначала сортируем по статусу алфавитно, при одинаковом статусе — от новых заказов к старым. Порядок столбцов в ORDER BY важен.
3. NULLS FIRST / NULLS LAST: ORDER BY score DESC NULLS LAST — пользователи без рейтинга (score IS NULL) окажутся в конце списка. По умолчанию в PostgreSQL: ASC → NULLS LAST, DESC → NULLS FIRST. Явное указание убирает неожиданности.
4. LIMIT для первых N записей: SELECT * FROM products ORDER BY rating DESC LIMIT 10 — топ-10 товаров по рейтингу. LIMIT без ORDER BY — непредсказуемый результат: PostgreSQL вернёт первые 10 строк в порядке файловой системы, который меняется.
5. Пагинация через OFFSET (традиционный способ): SELECT * FROM articles ORDER BY published_at DESC LIMIT 20 OFFSET 40 — третья страница (страницы 0-19 первая, 20-39 вторая, 40-59 третья). OFFSET = (page - 1) * page_size.
6. Проблема OFFSET на больших страницах: пользователь переходит на страницу 1000. OFFSET 20000 означает: PostgreSQL читает 20020 строк, сортирует их, отбрасывает первые 20000, возвращает 20. На таблице 10M строк это секунды. И чем дальше страница — тем медленнее.
7. Keyset pagination (cursor-based) — решение: вместо OFFSET используем WHERE created_at < :last_cursor. Первая страница: SELECT * FROM articles ORDER BY created_at DESC LIMIT 20. Следующая страница: SELECT * FROM articles WHERE created_at < '2024-03-15 10:30:00' ORDER BY created_at DESC LIMIT 20. Это Index Scan — одинаково быстро на любой странице.
8. DISTINCT для уникальных значений: SELECT DISTINCT country FROM users ORDER BY country — список стран. Альтернатива которая часто быстрее: SELECT country FROM users GROUP BY country ORDER BY country. GROUP BY позволяет добавить агрегаты.
9. DISTINCT как признак проблемы: SELECT DISTINCT u.id, u.name FROM users u JOIN orders o ON u.id = o.user_id. Этот DISTINCT убирает дубликаты от JOIN — пользователь с 5 заказами появлялся бы 5 раз. Вместо DISTINCT правильнее EXISTS: SELECT u.id, u.name FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id). Одна строка на пользователя без дубликатов.
10. Псевдонимы столбцов (AS): SELECT total_amount * 0.9 AS discounted_price, created_at AS order_date FROM orders. Псевдоним доступен в ORDER BY: ORDER BY discounted_price DESC. Но не в WHERE (WHERE discounted_price < 100 — ошибка).
11. Псевдонимы таблиц обязательны при JOIN: SELECT u.full_name, o.total_amount, p.name AS product_name FROM users u JOIN orders o ON u.id = o.user_id JOIN products p ON o.product_id = p.id. Без псевдонимов читать и писать JOIN-запросы крайне неудобно.
12. DISTINCT ON (PostgreSQL): SELECT DISTINCT ON (user_id) user_id, id AS last_order_id, created_at FROM orders ORDER BY user_id, created_at DESC. Возвращает первую строку из каждой группы — последний заказ каждого пользователя.

Примеры кода

OFFSET-пагинация vs keyset пагинация — сравнение производительности

-- OFFSET пагинация — медленная на глубоких страницах
-- Страница 1: быстро
SELECT id, title, published_at
FROM articles
WHERE is_published = true
ORDER BY published_at DESC
LIMIT 20 OFFSET 0;

-- Страница 500 (медленно! читает 10020 строк)
SELECT id, title, published_at
FROM articles
WHERE is_published = true
ORDER BY published_at DESC
LIMIT 20 OFFSET 9980;

-- KEYSET пагинация — Index Scan на любой странице
-- Страница 1
SELECT id, title, published_at
FROM articles
WHERE is_published = true
ORDER BY published_at DESC, id DESC
LIMIT 20;
-- Frontend сохраняет: last_date = '2024-01-15 08:30:00', last_id = 12345

-- Следующая страница (составной cursor — работает при одинаковых датах!)
SELECT id, title, published_at
FROM articles
WHERE is_published = true
  AND (published_at, id) < ('2024-01-15 08:30:00', 12345)
ORDER BY published_at DESC, id DESC
LIMIT 20;
-- Index Scan → всегда 1-5мс независимо от 'глубины страницы'

DISTINCT и GROUP BY — когда использовать каждый

-- DISTINCT: уникальные значения одного столбца
SELECT DISTINCT category FROM products ORDER BY category;

-- GROUP BY лучше если нужны агрегаты вместе с уникальностью
SELECT category, COUNT(*) AS cnt, ROUND(AVG(price),2) AS avg_price
FROM products
GROUP BY category
ORDER BY cnt DESC;

-- DISTINCT скрывающий проблему JOIN — плохой паттерн
SELECT DISTINCT u.id, u.full_name
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at > '2024-01-01';
-- дубли потому что у пользователя несколько заказов

-- Правильно: EXISTS вместо JOIN + DISTINCT
SELECT u.id, u.full_name
FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id
    AND o.created_at > '2024-01-01'
);

-- DISTINCT ON (PostgreSQL): последний заказ каждого пользователя
SELECT DISTINCT ON (user_id)
  user_id, id AS order_id, total_amount, created_at
FROM orders
ORDER BY user_id, created_at DESC;

Сортировка с NULL и составными ключами

-- NULLS LAST/FIRST — явное управление позицией NULL
SELECT full_name, score
FROM leaderboard
ORDER BY
  score  DESC NULLS LAST,   -- без очков в конце
  full_name ASC;            -- алфавит как тай-брейкер

-- Индекс для сортировки = Index Scan вместо Sort
CREATE INDEX ON articles(published_at DESC NULLS LAST, id DESC);
-- ORDER BY published_at DESC NULLS LAST, id DESC → Index Scan без Sort

-- Сортировка по выражению
SELECT id, full_name, COALESCE(updated_at, created_at) AS last_activity
FROM users
ORDER BY COALESCE(updated_at, created_at) DESC
LIMIT 50;
-- Оптимально: CREATE INDEX ON users(COALESCE(updated_at, created_at) DESC)

Что происходит под капотом

• OFFSET N не 'прыгает' к нужной позиции — PostgreSQL читает все строки до OFFSET и отбрасывает их. Для OFFSET 1000000 LIMIT 20: seq scan/index scan 1 миллиона строк → sort (если нужно) → skip 1M строк → return 20. Стоимость O(N + OFFSET), растёт линейно. Keyset: WHERE created_at < cursor → O(log n) через индекс.
• DISTINCT реализован через Sort (сортирует все строки, убирает соседние дубли) или HashAggregate (хеш-таблица уникальных в work_mem). Оба требуют обработки всего результата до первой строки. DISTINCT ON — Select первой строки для каждого уникального ключа; требует ORDER BY по ключу DISTINCT ON.
• ORDER BY без индекса добавляет Sort node в план. PostgreSQL сортирует в memory (work_mem) или в tmp-файлах на диске (спилл при нехватке work_mem). Disk sort — плохой сигнал в EXPLAIN. Индекс на ORDER BY столбце с совпадающим направлением = Index Scan без сортировки.
• LIMIT с ORDER BY и индексом: PostgreSQL делает 'top-N heapsort' или Index Scan с ранней остановкой. Для LIMIT 10 из 10M строк — берёт 10 строк из индекса и останавливается. Это O(LIMIT * log N) вместо O(N * log N) для полной сортировки.
• DISTINCT ON (key): SELECT DISTINCT ON (user_id) user_id, created_at, amount FROM orders ORDER BY user_id, created_at DESC. Группирует по user_id, берёт первую строку из каждой группы в ORDER BY порядке — последний заказ. Ключи DISTINCT ON должны быть первыми в ORDER BY.
• Псевдоним в ORDER BY: псевдоним из SELECT доступен в ORDER BY (PostgreSQL вычисляет SELECT до ORDER BY). Псевдоним НЕ доступен в WHERE и HAVING — они вычисляются до SELECT. Нельзя: WHERE discounted > 100 если discounted — псевдоним. Можно: ORDER BY discounted DESC.
• Tie-breaking в keyset pagination: если у нескольких статей одинаковая published_at — cursor только по дате пропустит некоторые или вернёт повторно. Составной cursor (date, id) решает это: WHERE (published_at, id) < (:last_date, :last_id). Уникальный id гарантирует детерминированность.
• NULL в ORDER BY по умолчанию: PostgreSQL ставит NULL в конце при ASC (NULLS LAST) и в начале при DESC (NULLS FIRST). Это часто неожиданно. Явное NULLS LAST при DESC убирает неопределённость. Индекс тоже может иметь NULLS LAST: CREATE INDEX ON t(col DESC NULLS LAST).
• Keyset с составной сортировкой: ORDER BY status ASC, created_at DESC, id DESC. Cursor должен содержать все поля сортировки: WHERE (status > :last_status) OR (status = :last_status AND (created_at, id) < (:last_date, :last_id)). Это сложнее, поэтому для API рекомендуют сортировку по одному монотонному полю (created_at или id).
• INDEX ONLY SCAN для ORDER BY: если индекс покрывает все SELECT и WHERE столбцы, PostgreSQL не обращается к heap-файлу совсем. CREATE INDEX ON articles(published_at DESC, id, title) для SELECT id, title FROM articles ORDER BY published_at DESC LIMIT 20 — Index Only Scan, минимальный I/O.

Термины урока

Мини-разбор реальной ситуации

Популярный новостной портал реализует бесконечный скролл через OFFSET. Работает отлично первые полгода. Когда статей становится 500 000, появляются жалобы: 'лента тормозит если долго листать'. EXPLAIN ANALYZE запроса страницы 200: Sort (cost=45000..) → Seq Scan on articles. OFFSET 4000 LIMIT 20 — читает 4020 строк, отбрасывает 4000. Страница 2000 занимает 8 секунд. Решение: cursor pagination. API возвращает next_cursor: {id: 12345, published_at: '2024-02-15T10:30:00Z'}. Следующий запрос: WHERE (published_at, id) < ('2024-02-15T10:30:00Z', 12345) ORDER BY published_at DESC, id DESC LIMIT 20. Добавлен индекс (published_at DESC, id DESC). Результат: любая 'страница' за 3-8мс. Бонус: cursor-based pagination не пропускает статьи при добавлении новых — offset-based смещает все страницы.