SELECT и WHERE: фильтрация данных

SELECT и WHERE: фильтрация данных

Главная идея

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

1. Базовый SELECT с фильтром: SELECT id, email, full_name FROM users WHERE is_active = true AND created_at >= '2024-01-01'. Запрос возвращает только нужные столбцы (не *) и фильтрует по двум условиям. При наличии индекса на (is_active, created_at) или (created_at) PostgreSQL использует Index Scan.
2. Оператор IN — проверка списка значений: SELECT * FROM orders WHERE status IN ('pending', 'confirmed'). Эквивалентно status = 'pending' OR status = 'confirmed', но читабельнее. IN с индексированным столбцом эффективен. Максимальный список для IN: тысячи элементов работают нормально, но при очень больших списках используй JOIN с временной таблицей.
3. NOT IN с ловушкой NULL: SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM banned_users). Если в banned_users.user_id есть хотя бы одна NULL строка — запрос вернёт НОЛЬ пользователей! Потому что NOT IN это AND (id <> 1 AND id <> 2 AND id <> NULL) — последнее условие NULL даёт unknown, весь результат NULL. Безопасная альтернатива: NOT EXISTS.
4. BETWEEN для диапазонов: SELECT * FROM orders WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31 23:59:59'. BETWEEN включает оба края (>=, <=). Для дат удобнее: WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01' — исключает последний день явно.
5. LIKE для поиска по шаблону: WHERE name LIKE 'Иван%' (начинается с 'Иван') использует B-tree индекс при условии locale с правильным collation. WHERE name LIKE '%Иван%' (содержит 'Иван') — индекс не используется, seq scan. Для поиска подстроки нужен pg_trgm + GIN индекс.
6. ILIKE — регистронезависимый LIKE: WHERE email ILIKE '%@gmail.com'. Не использует обычный индекс на email. Для регистронезависимого поиска: CREATE INDEX ON users(LOWER(email)), WHERE LOWER(email) LIKE '%@gmail.com'.
7. IS NULL и IS NOT NULL: SELECT * FROM orders WHERE deleted_at IS NULL (soft delete паттерн). WHERE deleted_at = NULL — всегда 0 строк! NULL никогда не равен NULL через =. Partial index для soft delete: CREATE INDEX ON orders(id) WHERE deleted_at IS NULL — индекс только по активным записям.
8. Функции в WHERE убивают индекс: WHERE EXTRACT(YEAR FROM created_at) = 2024 — seq scan. Правильно: WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'. WHERE LOWER(email) = 'test@test.com' — seq scan. Правильно: функциональный индекс CREATE INDEX ON users(LOWER(email)) и тот же запрос.
9. EXISTS для проверки наличия связанных записей: SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.status = 'completed'). EXISTS останавливается при первом совпадении (short-circuit) — эффективнее IN для больших подзапросов. SELECT 1 — стандартная практика, конкретный столбец не важен.
10. Логические операторы и приоритет: AND имеет больший приоритет чем OR. WHERE status = 'pending' OR status = 'confirmed' AND user_id = 42 — это WHERE status = 'pending' OR (status = 'confirmed' AND user_id = 42)! Используй скобки: WHERE (status = 'pending' OR status = 'confirmed') AND user_id = 42.
11. Составные условия с индексами: индекс на (user_id, created_at) эффективен для WHERE user_id = 42 AND created_at > '2024-01-01'. Но не для WHERE created_at > '2024-01-01' без user_id — составной индекс используется слева направо по leading columns.
12. COALESCE в WHERE: WHERE COALESCE(discount_price, price) < 100. PostgreSQL не использует индекс на price при COALESCE. Альтернатива: WHERE (discount_price IS NOT NULL AND discount_price < 100) OR (discount_price IS NULL AND price < 100) — позволяет использовать индексы.

Примеры кода

Базовая фильтрация — правильно и неправильно

-- НЕПРАВИЛЬНО: SELECT * тянет все столбцы, включая JSONB metadata
SELECT * FROM users WHERE is_active = true;

-- ПРАВИЛЬНО: только нужные столбцы
SELECT id, email, full_name FROM users WHERE is_active = true;

-- НЕПРАВИЛЬНО: функция на индексированном столбце → Seq Scan
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';

-- ПРАВИЛЬНО: функциональный индекс + условие без функции
CREATE INDEX CONCURRENTLY idx_users_email_lower ON users(LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';
-- Теперь используется Index Scan on idx_users_email_lower

-- НЕПРАВИЛЬНО: диапазон через функцию
SELECT * FROM orders WHERE EXTRACT(YEAR FROM created_at) = 2024;

-- ПРАВИЛЬНО: диапазон напрямую (использует индекс на created_at)
SELECT * FROM orders
WHERE created_at >= '2024-01-01'
  AND created_at < '2025-01-01';

Ловушка NOT IN с NULL

-- Создаём тестовые данные
INSERT INTO users (email, full_name) VALUES
  ('alice@example.com', 'Alice'),
  ('bob@example.com', 'Bob'),
  ('charlie@example.com', 'Charlie');

CREATE TEMP TABLE excluded_users (user_id BIGINT);
INSERT INTO excluded_users VALUES (1), (NULL);  -- NULL здесь!

-- ОПАСНО: NOT IN с NULL в подзапросе → пустой результат!
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM excluded_users);
-- Результат: 0 строк! NOT IN (1, NULL) → NOT (id=1 OR id=NULL)
-- → id<>1 AND id<>NULL → id<>1 AND NULL → NULL

-- ПРАВИЛЬНО: NOT EXISTS безопасен с NULL
SELECT * FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM excluded_users e
  WHERE e.user_id = u.id
);
-- Результат: Bob, Charlie (Alice с id=1 исключена)

-- Альтернатива: LEFT JOIN + IS NULL (антисоединение)
SELECT u.*
FROM users u
LEFT JOIN excluded_users e ON u.id = e.user_id
WHERE e.user_id IS NULL;

Оптимизация поиска по строкам — LIKE и pg_trgm

-- Создаём расширение для триграммного поиска
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- GIN индекс для поиска подстрок
CREATE INDEX CONCURRENTLY idx_products_name_trgm
  ON products USING GIN (name gin_trgm_ops);

-- Теперь WHERE name LIKE '%ноутбук%' использует GIN индекс
SELECT id, name, price
FROM products
WHERE name ILIKE '%ноутбук%'
ORDER BY similarity(name, 'ноутбук') DESC
LIMIT 20;

-- similarity() возвращает число от 0 до 1
SELECT name, similarity(name, 'ноутбук') AS sim
FROM products
WHERE name % 'ноутбук'  -- оператор % : similarity > 0.3
ORDER BY sim DESC;

EXISTS vs IN — производительность на реальных данных

-- Найти пользователей с хотя бы одним выполненным заказом

-- Вариант 1: IN с подзапросом
-- Материализует весь список user_id из orders
SELECT id, email FROM users
WHERE id IN (
  SELECT DISTINCT user_id FROM orders WHERE status = 'completed'
);

-- Вариант 2: EXISTS (обычно быстрее для больших таблиц)
-- Останавливается при первом совпадении
SELECT id, email FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id
    AND o.status = 'completed'
);

-- Вариант 3: JOIN (для агрегации вместе с данными)
SELECT DISTINCT u.id, u.email
FROM users u
INNER JOIN orders o ON o.user_id = u.id
WHERE o.status = 'completed';

-- EXPLAIN ANALYZE покажет какой план выбрал PostgreSQL
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, email FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id AND o.status = 'completed'
);

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

• Логический порядок выполнения SQL: FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT/OFFSET. WHERE применяется ДО SELECT, поэтому псевдоним из SELECT недоступен в WHERE той же команды. Это объясняет, почему нужно писать WHERE created_at > X, а не WHERE year > X где year — псевдоним из SELECT.
• B-tree индекс работает для операторов =, <, >, <=, >= и BETWEEN. Для LIKE 'prefix%' (без ведущего %) B-tree тоже работает, но только если collation поддерживает это — в PostgreSQL нужно C locale или специальный оператор класс. LIKE '%suffix' и LIKE '%middle%' — только seq scan или GIN-индекс (pg_trgm).
• Функциональные индексы: CREATE INDEX ON users(LOWER(email)) создаёт индекс не на значение столбца, а на результат функции. PostgreSQL использует этот индекс когда в WHERE встречается точно такое же выражение: WHERE LOWER(email) = X. Функциональный индекс также может быть partial: CREATE INDEX ON orders(status) WHERE status != 'completed' — только для незакрытых заказов.
• Short-circuit evaluation в AND: если первое условие AND ложно — второе не вычисляется. PostgreSQL использует это при наличии нескольких условий: более селективное (убирающее больше строк) ставится первым в плане выполнения. Планировщик оценивает selectivity каждого условия из статистики и упорядочивает вычисления оптимально.
• IN (v1, v2, ...) PostgreSQL превращает в = v1 OR = v2 OR ... или Hash Join, в зависимости от размера списка. IN с небольшим списком буквальных значений превращается в array comparison и может использовать индекс. IN с подзапросом планировщик может переписать в Hash Semi Join — эффективная стратегия для больших подзапросов.
• NULL и три-значная логика: SQL использует three-valued logic (TRUE, FALSE, NULL/UNKNOWN). Любое сравнение с NULL возвращает NULL: NULL = 1 → NULL, NULL <> 1 → NULL, NULL > 0 → NULL. В WHERE фильтр убирает строки только для FALSE и NULL — строки с NULL-условием не попадают в результат, так же как строки с FALSE. Поэтому WHERE phone = NULL убирает все строки.
• Covering index: если индекс содержит все столбцы нужные запросу — PostgreSQL делает Index Only Scan (не обращается к heap страницам совсем). CREATE INDEX ON orders(user_id, status, created_at) покрывает SELECT created_at FROM orders WHERE user_id = X AND status = 'pending' — только операции с индексом, диск не читается.
• Partial index для soft delete: CREATE INDEX ON orders(user_id, created_at) WHERE deleted_at IS NULL. Этот индекс содержит только 'живые' строки. Запрос WHERE user_id = X AND deleted_at IS NULL использует этот partial index — он меньше полного индекса и эффективнее для фильтрации активных записей.
• OR условия сложнее для оптимизатора: WHERE user_id = 1 OR status = 'vip' не использует составной индекс. PostgreSQL может выполнить BitmapOr двух Index Scans: один по user_id, другой по status, объединить битовые маски и пройти по heap. Для OR c одним столбцом лучше использовать IN: WHERE status IN ('vip', 'premium').
• Recheck Condition в Bitmap Index Scan: при использовании GIN или когда индекс lossy (не хранит точные значения) PostgreSQL после индексного прохода перепроверяет условие на реальных строках. Это видно в EXPLAIN как 'Recheck Cond'. Для pg_trgm поиска это нормально: GIN находит кандидатов, Recheck отсеивает false positives.

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

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

E-commerce-платформа имеет поиск товаров по названию: WHERE name LIKE '%ноутбук%'. На таблице 200 000 товаров запрос занимает 800мс. С ростом каталога до 2 миллионов: 8 секунд. Команда думает о кешировании Redis. Перед этим senior-разработчик смотрит EXPLAIN: Seq Scan on products (cost=0..45000 rows=200000 width=120) Filter: (name ~~ '%ноутбук%') Rows Removed by Filter: 199847. Полный перебор 2 миллионов строк. Решение: CREATE EXTENSION pg_trgm; CREATE INDEX CONCURRENTLY idx_products_name_gin ON products USING GIN (name gin_trgm_ops). Результат через 3 минуты (индекс строится конкурентно, без блокировки): WHERE name ILIKE '%ноутбук%' → 12мс. Кеш Redis не понадобился. Урок: сначала EXPLAIN, потом архитектурные решения.