Модуль 10 · Урок 39 із 58
JOIN, aggregation, cardinality, indexes і EXPLAIN
JOIN не просто додає columns: він змінює кількість rows. Перед оптимізацією треба довести cardinality й правильність результату; індекс і план виконання мають сенс лише після цього.
Безпечна PostgreSQL-практика модуля 10
Пакет містить synthetic schema/seed/query files, parameterized Python repository, contract tests і read-only verifier. Локальні тести не виконують PostgreSQL; integration run проводиться лише у власній disposable database без персональних чи production-даних, credentials і production endpoints.
Cardinality — перша перевірка JOIN
Для one-to-many relationship один project із трьома tickets дає три joined rows. Якщо додати ще одну many-side table без aggregation boundary, rows можуть перемножитися. Перед запитом запишіть grain: «один row на project», «один row на ticket» або «один row на project і day».
| JOIN | Результат | Ризик |
|---|---|---|
| INNER | Лише matched rows | Тихо втрачає parents без children |
| LEFT | Усі left rows + matches | WHERE по right column може перетворити його на inner |
| Many + many | Комбінації matches | Duplicate amplification |
LEFT JOIN і NULL-side
Щоб порахувати tickets для кожного project, включно з zero, використовують LEFT JOIN і COUNT(ticket.id), а не COUNT(*). Filter right-side rows часто треба помістити в ON, інакше WHERE ticket.status = 'open' відкине NULL-side та змінить semantics.
SELECT p.id, p.name, COUNT(t.id) AS open_count
FROM python_sql_lab.projects AS p
LEFT JOIN python_sql_lab.tickets AS t
ON t.project_id = p.id AND t.status = 'open'
GROUP BY p.id, p.name
ORDER BY p.id;Aggregation визначає новий grain
GROUP BY згортає rows до груп. Кожен selected expression має бути aggregate або належати group contract. WHERE фільтрує input rows до aggregation, а HAVING — готові groups. Не виправляйте неочікувані duplicates випадковим DISTINCT: спочатку знайдіть неправильний join path або grain.
Reconciliation перед performance
Порівняйте source row counts, distinct business keys, unmatched parents/children і очікувані totals. Швидкий query із подвоєною сумою — неправильний.
Індекс прискорює access path, але має ціну
B-tree index корисний для багатьох equality і range lookups, а multicolumn порядок залежить від query patterns. Кожен index займає disk і додає роботу на INSERT/UPDATE/DELETE. Foreign key не означає автоматичний index на referencing columns у всіх потрібних формах — workload треба перевірити.
Partial index може зберігати лише активні rows, але query predicate має узгоджуватися з його condition.
EXPLAIN — evidence, не магічний рейтинг
EXPLAIN показує planner estimate і обраний plan. EXPLAIN ANALYZE реально виконує statement, тому його не запускають бездумно на mutating або дорогому production query. Читайте estimated/actual rows, loops, scan/join nodes і filter removals у контексті статистики та workload.
- Спочатку доведіть result correctness і grain.
- Зафіксуйте representative synthetic або approved dataset.
- Порівняйте plan до/після одного обґрунтованого change.
- Перевірте write/storage cost і regression queries.
- Формулюйте висновок не ширше за зібраний evidence.
Методичні джерела
Урок, сценарії, пояснення й вправи створені SEOWORK. Посилання ведуть лише на офіційну документацію та Python Wiki.
Закріпіть матеріал уроку
Три сценарні питання. Для зарахування уроку потрібно дати щонайменше дві правильні відповіді.