Квиз: Погружение в Postgres. Часть 2

Вы ведь знали про CROSS JOIN, верно?

Возможно, JOIN ALL заставил вас задуматься о другой операции — UNION ALL?

Правильный ответ здесь — JOIN ALL ❌.

Корректные типы JOIN это:

• INNER JOIN (JOIN по умолчанию)
• LEFT JOIN (или LEFT OUTER JOIN)
• RIGHT JOIN (или RIGHT OUTER JOIN)
• FULL JOIN (или FULL OUTER JOIN)
• CROSS JOIN (декартово произведение, если только оно не фильтруется последующим предложением WHERE)

Хотя SERIAL все еще часто используется, способ, соответствующий стандарту SQL, выглядит так:

id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY

• id INT IDENTITY(1,1) — это синтаксис SQL Server.
• id INT AUTO_INCREMENT — это синтаксис MySQL.
• id INTEGER AUTO_INCREMENT PRIMARY KEY — это синтаксис MySQL.

У этого подхода есть несколько преимуществ перед SERIAL:

• Соответствие стандарту SQL
• Улучшенное управление владением последовательностью (sequence ownership)
• Более четкая семантика при ручных вставках
• Лучшая работа с репликацией

На самом деле SERIAL — это просто сокращение для создания последовательности и установки значения по умолчанию, что может привести к проблемам при создании дампов и восстановлении данных.

Примеры расширений PostgreSQL сверх стандарта ANSI SQL:

• Предложение RETURNING.
• Оператор ILIKE для регистронезависимого поиска.
• Типы массивов и операции с ними.
• Типы JSON/JSONB.

Оригинальный стандарт ANSI SQL включает:

• WITH (обобщенные табличные выражения — CTE).
• LIKE для сопоставления с шаблоном.
• Соединения LATERAL.
• Оконные функции.

Когда здесь возникает ошибка:

• Вся транзакция откатывается
• Никакие изменения не фиксируются
• Транзакция помечается как неудавшаяся

Для частичной фиксации:

BEGIN;
UPDATE ...;
SAVEPOINT my_save;
UPDATE ...; -- Error
ROLLBACK TO my_save;
COMMIT;

Hash join возможен только при наличии условия равенства (=) в предложении ON.

• = — это основной предикат для хеш-соединений
• > и >= не являются предикатами для hash join
• HASH JOIN не является валидным синтаксисом в PostgreSQL

Планировщик все равно может выбрать nested loop или merge join в зависимости от размера таблиц, статистики, индексов и настроек памяти.

Удивительно, но составной индекс может использоваться даже тогда, когда порядок колонок в тексте запроса не совпадает с определением индекса.

Примечание: В B-tree индексах порядок ведущих колонок имеет решающее значение. Поскольку этот запрос фильтрует по grade_level и last_name, индекс, начинающийся с grade_level, а затем содержащий last_name, отлично подходит.

Чем быстрее SQL сможет отсеять ненужные строки, тем выше будет производительность.

Например, если у вас есть таблица task и вы знаете, что колонка status обладает более высокой селективностью, чем created_at, стоит выбрать индекс, начинающийся со status для запроса вида status = 'done' AND created_at > '2024-01-01'. Важен не порядок условий в блоке WHERE, а порядок колонок в индексе и форма предикатов.

B+Tree индекс может использоваться для:

• Ведущей колонки или самого левого префикса колонок.
• Операторов =, >, <, BETWEEN и IN, в зависимости от структуры запроса.
• Сопоставления префиксов, например LIKE 'prefix%'.

Спасибо u/mwdb2 за исправление ошибки в предыдущей версии этого объяснения!

В PostgreSQL:

• Двойные кавычки (") используются для идентификаторов (имена таблиц, имена столбцов)
• Одинарные кавычки (') используются для строковых литералов

Лучшие практики:

• Избегайте смешанного регистра или пробелов в именах
• Используйте snake_case для идентификаторов
• Используйте кавычки для идентификаторов только в случае крайней необходимости

PostgreSQL использует двойные кавычки для экранирования идентификаторов:

• Двойные кавычки "..." для идентификаторов (имена столбцов, таблиц)
• Одинарные кавычки '...' для строковых литералов
• Запись first.name интерпретируется как имя_таблицы.имя_столбца

Без кавычек точка в имени столбца будет воспринята как разделитель схемы или таблицы! Кроме того, все идентификаторы без кавычек по умолчанию приводятся к нижнему регистру.

Чтобы избежать сюрпризов, лучше использовать snake_case и не применять специальные символы.

Этот вопрос с подвохом! Здесь расставлено около 6 ловушек, вот некоторые из них:

1. Описание высокого уровня + фрагменты синтаксиса. Проверяет, как вы адаптируете и собираете неполную информацию.
2. Требуется хорошее знание синтаксиса TABLESAMPLE, так как ни один из вариантов с TABLESAMPLE не использует правильный синтаксис! (Коварно, я знаю! Продолжайте, я в вас верю!!! ❤️)
3. Варианты ROW_NUMBER() OVER (ORDER BY RAND()) и ORDER BY RANDOM() > 10 — это отвлекающие маневры. Они выглядят похожими на правду, но это не так. Функции RAND() не существует. А RANDOM() > 10 — это логическое выражение, которое всегда будет ложным, так как RANDOM() всегда возвращает значение в диапазоне 0.0-1.0. Еще одна проблема: ORDER BY RANDOM() > 10 будет выполнять сортировку по логическому значению, а не по случайному числу. Но это > 10 выглядело так, будто это проценты, верно?
4. BERNOULLI — это валидный метод, однако SAMPLETABLE — нет.
5. WHERE RANDOM() >= 0.1 — это ловушка! RANDOM() возвращает значение в диапазоне 0.0 <= x < 1.0, поэтому >= 0.1 вернет около 90% строк, а не 10%.

Что касается метода TABLESAMPLE, PostgreSQL поддерживает два метода выборки:

• BERNOULLI: Каждая строка имеет равную вероятность попадания в выборку.
• SYSTEM: Выборка на уровне блоков (быстрее, но менее случайно).

Чтобы узнать больше о выборках, ознакомьтесь с этой статьей от Render.

Пример:

-- Sample 10% of rows
SELECT * FROM students TABLESAMPLE BERNOULLI (10);

-- For repeatable results, use REPEATABLE:
SELECT * FROM students
TABLESAMPLE BERNOULLI (10) REPEATABLE (42);

Также стоит упомянуть, что сообщалось, что TABLESAMPLE не всегда обеспечивает высокую степень случайности. Если вам нужна более случайная выборка фиксированного размера, рассмотрите использование ORDER BY RANDOM() LIMIT 10. Но имейте в виду, что это вернет 10 строк, а не 10%, и может работать медленно на больших таблицах.

Существуют также сторонние расширения, предоставляющие лучшие методы случайной выборки, но это выходит за рамки данного вопроса! 😅

Этот трюк называется Partial Index (частичный индекс).

Они полезны для экономии места на диске и ускорения запросов, когда обычно запрашивается только подмножество строк.

Postgres будет использовать этот индекс только в том случае, если в запросе присутствует условие status = 'active'.

Напоминание: Postgres не поддерживает «подсказки к запросам» (query hints), как другие СУБД. Планировщик запросов сам решает, когда использовать подходящие индексы.

Ладно, это было с подвохом. Ловушка в том, что x = NULL никогда не найдет совпадений ни в одной строке, и запрос ВСЕГДА вернет одну строку со значением 0.

Так как NULL — это неизвестное значение, его нельзя сравнивать ни с чем, даже с самим собой.

Правильный способ проверки на NULL — использовать оператор IS NULL.

Пример:

postgres=# SELECT count(*)
  FROM students
  WHERE age = null;
count
-------
    0
(1 row)

postgres=#
postgres=# SELECT count(*)
  FROM students
  WHERE age is null;
count
-------
    3
(1 row)

EXPLAIN ANALYZE на самом деле выполняет запрос! Будьте осторожны при анализе операций записи, а именно операторов UPDATE, DELETE и INSERT. Возможно, вам придется выполнять операции очистки после каждого EXPLAIN ANALYZE, чтобы получить повторяемые результаты.

Лучшие практики:

• Используйте EXPLAIN (без ANALYZE) для UPDATE/DELETE/INSERT
• Оборачивайте модификации в транзакции:

BEGIN;
EXPLAIN ANALYZE ...;
ROLLBACK;