Привет, Хабр!
В SQL есть отдельный класс ошибок, неприятных именно тем, что они никак себя не проявляют: база не выдаёт исключения, не пишет предупреждения — запрос просто возвращает не то, что нужно. Чаще всего это пустой результат там, где вы ожидаете десятки строк. Один из самых распространённых случаев — NOT IN в сочетании с NULL.
Возьмём задачу: найти сотрудников, которые никем не руководят. В таблице employees есть поле manager_id — ссылка на начальника. Рассуждение простое: руководитель — это тот, кто хотя бы раз встречается в manager_id, а значит, не руководители — все, кого там нет. Запрос пишется почти сам собой:
SELECT name FROM employees WHERE id NOT IN (SELECT manager_id FROM employees);
Вы запускаете его и получаете ноль строк, хотя точно знаете: обычных сотрудников в компании большинство, и в выборку они обязаны попасть.
Логично заподозрить данные или сам запрос и для проверки заменить NOT IN на IN — посмотреть хотя бы на руководителей. IN отрабатывает корректно. А NOT IN на тех же данных упрямо возвращает пустоту. Исключения при этом нет, синтаксис безупречен, поэтому причину найти непросто.
Дальше разберём, почему NOT IN ломается из‑за одного‑единственного NULL, почему IN рядом продолжает работать корректно и почему NOT IN, вопреки названию, не противоположность IN.
Пример
Пять сотрудников, одна простая иерархия:
CREATE TABLE employees ( id int PRIMARY KEY, name text, manager_id int ); INSERT INTO employees VALUES (1, 'Алексей', NULL), -- директор, начальника нет (2, 'Борис', 1), (3, 'Вера', 1), (4, 'Глеб', 2), (5, 'Дина', 2);
Кто здесь руководитель? Тот, на кого ссылаются. В колонке manager_id встречаются 1 и 2 — значит, руководят Алексей и Борис. Остальные трое — Вера, Глеб и Дина — никем не командуют. Их мы и хотим получить.
SELECT name FROM employees WHERE id NOT IN (SELECT manager_id FROM employees);
name ------ (0 rows)
Ноль. Не Вера с Глебом и Диной, а пусто. Притом что IN на тех же данных отрабатывает безупречно:
SELECT name FROM employees WHERE id IN (SELECT manager_id FROM employees);
name --------- Алексей Борис (2 rows)
Разворачиваем NOT IN
Подзапрос SELECT manager_id FROM employees возвращает набор значений: NULL, 1, 1, 2, 2. Тот самый NULL — от Алексея, у которого начальника нет.
Теперь развернём NOT IN в то, чем он работает под капотом. x IN (a, b, c) — это сокращение для x = a OR x = b OR x = c. Соответственно, x NOT IN (a, b, c) — это NOT (x = a OR x = b OR x = c), по законам логики — x <> a AND x <> b AND x <> c.
Цепочка неравенств через AND. Подставим наш набор и проверим Веру, у неё id = 3:
3 <> NULL AND 3 <> 1 AND 3 <> 2
3 <> 1 — это TRUE. 3 <> 2 — тоже TRUE. А 3 <> NULL?
В SQL NULL — не значение, а неизвестно. Сравнить с ним нельзя ничего, и 3 <> NULL не вернёт ни TRUE, ни FALSE — только UNKNOWN, третий результат трёхзначной логики.
Складываем цепочку: TRUE AND TRUE AND UNKNOWN. И весь результат — UNKNOWN.
А WHERE пропускает дальше только строгое TRUE. UNKNOWN он выбрасывает наравне с FALSE. Вера вылетает. Глеб и Дина — по той же схеме: у них в цепочке такой же <> NULL, и каждая цепочка снова сваливается в UNKNOWN. Вылетают все.
Заметьте, настоящие руководители — Алексей и Борис — отсеялись бы и так, и это честно: у Алексея в цепочке есть 1 <> 1, это FALSE, а FALSE AND что угодно даёт FALSE. А вот не‑руководители, которые должны были пройти, спотыкаются именно об UNKNOWN. Один NULL в подзапросе сделал так, что NOT IN не способен вернуть TRUE ни для кого. Отсюда и ноль строк.
И подзапрос для этого не нужен — достаточно списка вручную:
SELECT name FROM employees WHERE id NOT IN (3, 4, NULL); -- 0 строк
Один NULL в скобках — и весь NOT IN отравлен.
Самое скверное тут даже не в логике, а в поведении со стороны.
База не падает, ошибку не кидает. С её точки зрения всё стерильно: трёхзначная логика отработала строго по стандарту. Запрос вернул результат — просто пустой или подозрительно куцый. А это глазами ловится в разы хуже, чем честный ERROR в консоли. И ведёт себя так не только PostgreSQL — это поведение стандарта SQL.
NULL в таких колонках — норма. У директора нет начальника. У товара может не быть категории, у события — связанного заказа. NULL в manager_id не баг, который кто‑то однажды вычистит. Это валидные данные, они там навсегда. Хватит одной такой строки на миллион — и любой NOT IN по этой колонке отравлен.
Чем лечить: NOT EXISTS
Надёжное лекарство — NOT EXISTS с коррелированным подзапросом:
SELECT e.name FROM employees e WHERE NOT EXISTS ( SELECT 1 FROM employees sub WHERE sub.manager_id = e.id );
name ------ Вера Глеб Дина (3 rows)
Те самые трое. EXISTS устроен иначе, в этом вся соль, он не сравнивает значения, а отвечает на один вопрос — нашлась хоть одна строка или нет. Ответ двузначный, да или нет, третьего не дано. NULL внутри подзапроса ему не страшен: строка Алексея с manager_id = NULL просто не совпадёт с e.id в условии sub.manager_id = e.id и не попадёт в подсчёт. Никакого UNKNOWN, который полез бы наружу.
Второй крепкий вариант — антиджойн через LEFT JOIN:
SELECT e.name FROM employees e LEFT JOIN employees sub ON sub.manager_id = e.id WHERE sub.id IS NULL;
Тоже отдаёт Веру, Глеба и Дину, тоже не боится NULL.
Можно ли спасти сам NOT IN? Можно — выкинуть NULL из подзапроса руками:
SELECT name FROM employees WHERE id NOT IN ( SELECT manager_id FROM employees WHERE manager_id IS NOT NULL );
Сработает, но это костыль на честном слове: держится на том, что вы помните про NULL и не забыли дописать фильтр. В следующий раз забудете и снова получите тихую пустоту. NOT EXISTS помнить ничего не просит.
Как поймать это у себя
Подозреваете NOT IN по конкретной колонке — спросите базу, есть ли там NULL:
SELECT count(*) FROM employees WHERE manager_id IS NULL;
Ноль — NOT IN по этой колонке пока безопасен. Больше нуля — он уже отравлен, и результат запроса вам врёт.
Также стоит пройтись поиском по NOT IN (SELECT — почти каждое такое место кандидат на разбор. Колонка в подзапросе объявлена как NOT NULL? Тогда NOT IN безопасен, его можно оставить. Колонка NULL — переписывайте на NOT EXISTS, не дожидаясь, пока туда заедет первый NULL.
NOT IN — это не «IN наоборот»
Остался вопрос с начала: почему IN выжил, а NOT IN рухнул на том же NULL?
Потому что они по‑разному дружат с UNKNOWN. IN раскрывается в цепочку OR. Для руководителя это TRUE OR ... OR UNKNOWN — а OR хватает одного TRUE, чтобы выдать TRUE, остальное не важно. UNKNOWN в цепочке OR просто тонет.
NOT IN раскрывается в цепочку AND. А AND к UNKNOWN беспощаден: одного UNKNOWN достаточно, чтобы увести весь результат из TRUE в UNKNOWN. Та же NULL‑строка, которую OR не заметил, для AND оказывается ядом.
Эта несимметрия и подводит. IN работает — и кажется само собой разумеющимся, что NOT IN просто его зеркало. Не зеркало.
Вернёмся к нулю строк из начала. Ваши не‑руководители никуда не пропадали, их отравил единственный NULL в manager_id, переведя условие из TRUE в UNKNOWN, и WHERE молча вынес всех за дверь.
В итоге NOT IN — не противоположность IN, как нашёптывает название. Под трёхзначной логикой это две разные операции с разным характером, и NULL ломает только одну. Рабочее правило короткое: исключаете что‑то по подзапросу — берите NOT EXISTS по умолчанию, не задумываясь. А NOT IN оставьте для захардкоженных списков, про которые точно знаете — NULL там нет и не будет.
В статье мы разобрали, как один NULL может превратить корректный на вид NOT IN в тихую логическую ловушку: запрос не падает, но возвращает не тот результат. Если хотите увереннее работать со сложной SQL‑логикой и лучше понимать, почему база ведёт себя именно так, обратите внимание на два бесплатных открытых урока OTUS:
21 мая, 20:00 — «SQL: Обобщенное табличное выражение (CTE) — как писать сложные запросы просто».
На уроке покажем, как CTE помогают раскладывать сложную SQL‑логику на понятные шаги, чтобы запросы было проще читать, проверять и сопровождать.2 июня, 20:00 — «Ты — индекс в Postgres, Я — индекс в ClickHouse. Мы — разные».
Поговорим о том, почему похожие на первый взгляд механики в разных СУБД работают по‑разному, и как это учитывать при работе с запросами и данными.
Больше бесплатных открытых уроков по аналитике, базам данных и не только можно найти в календаре мероприятий OTUS.
Комментарии (18)
daregod
20.05.2026 10:07Вместо WHERE можно оборачивать (но вообще полезно всегда помнить, что поле nullable).
SELECT name FROM employees WHERE id NOT IN (SELECT COALESCE(manager_id,0) FROM employees);
ptr128
20.05.2026 10:07Я бы начинал с того, что not manager_id=id тоже не инвертирование условия при наличии NULL. Хотя ошибку чаще допускают, ставя NOT перед скобками с более сложным логическим выражением.
SabMakc
20.05.2026 10:07Когда-то работал в компании, где приняли очень грамотное решение. А именно: отказаться от NULL в таблицах вообще. Вместо NULL было 0-е значение. Да, пришлось в таблицы добавлять нулевые строки, но зато и с подобными приколами не сталкивались. Работы с SQL было много, так что решение однозначно окупило себя.
ptr128
20.05.2026 10:07В некоторых случаях такой подход может приводить к увеличению таблицы в разы. К тому же он всё равно не решает проблемы, так как NULL в поле запроса может возникать не только из фактического значения поля в таблице.
SabMakc
20.05.2026 10:07Да, может возникать NULL - но как часто? Я навскидку не припомню, чтобы были проблемы из-за этого, хотя запросов я писал очень много.
Про увеличение таблиц в разы - можно подробнее? Может в каких-то особых случаях, но на вскидку даже индексы можно сделать с исключением 0-го значения, если понадобится.
Akina
20.05.2026 10:07Да, может возникать NULL - но как часто?
Аргументом NOT IN чаще является подзапрос, а не список значений. Этот подзапрос может иметь источником несколько таблиц с OUTER JOIN - вот вам и потенция для NULL.
SabMakc
20.05.2026 10:07Если у тебя все ссылки идут с 0 вместо NULL - то у тебя нет необходимости в чем-либо, кроме простого JOIN (он же INNER JOIN). LEFT/RIGHT JOIN еще встречались, но скорее в формате “JOIN для подзапроса”.
ptr128
20.05.2026 10:07Кроме явных случаев с OUTER JOIN, NULL могут возвращать функции. В том числе и агрегатные.
Что касается размеров кортежа, то в PostgreSQL NULL занимает в кортеже один бит, integer - 32 бита, 0::decimal - 3+2 байта = 40 бит. То есть, если в кортеже из восьми integer полей семь NULL, то его общая длина будет 23+1+4=28 байт. А если все восемь ноль - то 23+1+4*8=56 байт. В случае пары десятков decimal или varchar - можете сами посчитать, учитывая, что NULL-mask требует по байту на каждые восемь nullable полей.
SabMakc
20.05.2026 10:07Про JOIN ответил выше. Функции - аналогично, как часто результаты агрегатных функций попадают в условия? Да
ISNULL(..., 0)илиCOALESCE(...,0)в помощь при необходимости.Я не думаю, что “таблица из восьми полей, все могут быть NULL” - очень частая история. Обычно поля все-таки заполняются значениями, а NULL для ссылок идет. Да, места может потребоваться больше. Но не в разы (в разы - очень частная история). Но есть и выигрыш в производительности от фиксированного размера кортежа - не стоит про это забывать.
ptr128
20.05.2026 10:07Про JOIN ответил выше
Так себе ответ. OUTER JOIN применяются не редко. Просто потому, в таблице операций может не быть ссылок на все элементы справочника, который в запросе фильтруется по каким-то полям. А отсутствие записей - это тоже результат.
Для примера, в одном из моих текущих проектов по логистике вообще нет nullable PK. При этом там INNER встречается 38 тысяч раз, а OUTER - 18 тысяч раз. То есть, каждый третий JOIN - OUTER.
как часто результаты агрегатных функций попадают в условия?
Да повсеместно. Хоть SUM по нулевому количеству записей или LEAD/LAG на первой или последней записи в PARTITION.
Я не думаю, что “таблица из восьми полей, все могут быть NULL” - очень частая история.
На основании чего такие мысли? Предлагаю взять любую ERP на Ваш выбор и я в ней укажу сходу десятки таких таблиц.
Но есть и выигрыш в производительности от фиксированного размера кортежа - не стоит про это забывать.
Впервые такое слышу. Вот от уменьшения размера кортежа, благодаря которому на одной странице умещается больше кортежей - выигрыш в производительности показать могу легко. Только до компа добраться надо. А сможете ли Вы показать тут повторяемый пример, доказывающий Ваше утверждение?
SabMakc
20.05.2026 10:07Просто потому, в таблице операций может не быть ссылок на все элементы справочника, который в запросе фильтруется по каким-то полям. А отсутствие записей - это тоже результат.
Перепутал OUTER JOIN с CROSS JOIN. LEFT/RIGHT JOIN (OUTER опционален) - да, используются. Но заметно реже. “нет nullable PK” - а как иначе? Я про “нет nullable FK”. Сколько бы было у вас OUTER JOIN если бы не NULL в FK?
Да, если идет агрегация данных, например “справочник с количеством ссылающихся операций” - тогда да, LEFT JOIN нужен. Но так ли много подобных запросов?
На основании чего такие мысли? Предлагаю взять любую ERP на Ваш выбор и я в ней укажу сходу десятки таких таблиц.
На основании опыта. Таблица с одними числами, причем опциональными числами - не припомню. И тем более в разрезе “заметно экономим на nullable-полях”. Максиму кросс-таблицы приходят на ум, но там 3 числа (id, FK_1, FK_2, может еще какое поле) и все они по идее обязательны (а иначе и в самой записи нет смысла). А размер такой таблицы будет меньше с NOT NULL на FK-полях. Впрочем, с нулевыми ссылками (вместо NULL) таблица тоже мало имеет смысла из тех же соображений.
Впервые такое слышу. Вот от уменьшения размера кортежа, благодаря которому на одной странице умещается больше кортежей - выигрыш в производительности показать могу легко.
Предсказуемая адресация строк и колонок - чем не плюс? Найти искусственный пример, думаю, смогу. Другой вопрос, как это будет соотноситься с реальной жизнью. Думаю примерно также, как и “все в NULL” из соображений “в разы меньше хранить придется”.
P.S. я не спорю, что в большинстве своем nullable-поля экономичнее на диске будут. Я спорю именно с “может приводить к увеличению таблицы в разы” - если и будет такой сценарий, то крайне редко и в очень специфичных условиях. А минимизация ошибок в запросах лично для меня в приоритете. Именно из-за скрытого характера описанных в статье проблем.
ptr128
20.05.2026 10:07Сколько бы было у вас OUTER JOIN если бы не NULL в FK?
Столько же. Ведь выборка из таблиц операций идет почти всегда от справочников по соответствующим индексам. Что логично, так как в обратном случае мог работать разве что индекс по датам.
Да, если идет агрегация данных, например “справочник с количеством ссылающихся операций” - тогда да, LEFT JOIN нужен. Но так ли много подобных запросов?
Почти в каждом отчете.
Найти искусственный пример,
Зачем искать? Так сложно самому написать скрипт примера?
Я спорю именно с “может приводить к увеличению таблицы в разы”
Какой смысл спорить, если я уже предложил решение этого спора? Называйте ERP и там увидим. Хоть SAP, хоть OEBS, хоть Dynamics.
А минимизация ошибок в запросах лично для меня в приоритете.
Из этого лишь следует, что у Вас крошечные объёмы данных. Иначе бы производительность была бы существенно важнее.
SabMakc
20.05.2026 10:07Вот так и не понял, где в “Ведь выборка из таблиц операций идет почти всегда от справочников по соответствующим индексам” возникает LEFT JOIN. Если фильтр по справочнику - то там не “LEFT JOIN”, а просто условие в WHERE.
Если в таблице операций показываются значения из справочника (а не просто ID записи) - то “нет nullable FK” спасет от LEFT JOIN.
Искать затем, что “когда-то я работал в компании” - это 10+ лет. Сейчас я с SQL работаю сильно меньше и примера готового все равно не имею.
Попросил ИИ подобрать пример - и он справился, с 1й попытки. 2 таблицы, по 10 int-полей в каждой. Одна - nullable, другая с нулями. Фиксированная таблица на 30% быстрее оказалась в работе. Разница в размере - 30% при 10% заполнении данными в пользу nullable и менее 5% при 70% заполнении.
портянка кода с комментариями
ИИ немного напутал в размерах в своих выводах (сказал, что преимущество у fixed в размере), но все равно показательно, особенно в разрезе “разница в разы”:
Тестировал в PostgreSQL, что был под рукой. 15й, без какой-либо настройки (просто запущенный инстанс в docker).
-- Создадим две таблицы по 1 млн строк с десятью числовыми столбцами. -- В первой NULL разрешён и активно используется. Во второй — все столбцы NOT NULL, а вместо NULL записан 0. -- Таблица с NULL (переменная длина кортежа) CREATE TABLE metrics_nullable ( id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY, day date NOT NULL, value1 int, value2 int, value3 int, value4 int, value5 int, value6 int, value7 int, value8 int, value9 int, value10 int ); -- Таблица с 0 вместо NULL, все столбцы NOT NULL (фиксированная длина кортежа) CREATE TABLE metrics_fixed ( id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY, day date NOT NULL, value1 int NOT NULL DEFAULT 0, value2 int NOT NULL DEFAULT 0, value3 int NOT NULL DEFAULT 0, value4 int NOT NULL DEFAULT 0, value5 int NOT NULL DEFAULT 0, value6 int NOT NULL DEFAULT 0, value7 int NOT NULL DEFAULT 0, value8 int NOT NULL DEFAULT 0, value9 int NOT NULL DEFAULT 0, value10 int NOT NULL DEFAULT 0 ); -- Заполняем таблицу с NULL (70% шанс заполнения) INSERT INTO metrics_nullable (day, value1, value2, value3, value4, value5, value6, value7, value8, value9, value10) SELECT current_date - (i % 365), CASE WHEN random() < 0.7 THEN (random()*1000)::int END, CASE WHEN random() < 0.7 THEN (random()*1000)::int END, CASE WHEN random() < 0.7 THEN (random()*1000)::int END, CASE WHEN random() < 0.7 THEN (random()*1000)::int END, CASE WHEN random() < 0.7 THEN (random()*1000)::int END, CASE WHEN random() < 0.7 THEN (random()*1000)::int END, CASE WHEN random() < 0.7 THEN (random()*1000)::int END, CASE WHEN random() < 0.7 THEN (random()*1000)::int END, CASE WHEN random() < 0.7 THEN (random()*1000)::int END, CASE WHEN random() < 0.7 THEN (random()*1000)::int END FROM generate_series(1, 1000000) AS gs(i); -- Копируем во вторую таблицу, заменяя NULL на 0 INSERT INTO metrics_fixed (day, value1, value2, value3, value4, value5, value6, value7, value8, value9, value10) SELECT day, COALESCE(value1, 0), COALESCE(value2, 0), COALESCE(value3, 0), COALESCE(value4, 0), COALESCE(value5, 0), COALESCE(value6, 0), COALESCE(value7, 0), COALESCE(value8, 0), COALESCE(value9, 0), COALESCE(value10, 0) FROM metrics_nullable; -- Сравнение размера на диске SELECT 'nullable' AS table_type, pg_size_pretty(pg_total_relation_size('metrics_nullable')) AS total_size UNION ALL SELECT 'fixed', pg_size_pretty(pg_total_relation_size('metrics_fixed')); -- Сравнение скорости полного сканирования и агрегации -- Выполним суммирование всех значений по всем столбцам — операция, которая читает каждую строку полностью. EXPLAIN (ANALYZE, BUFFERS, TIMING) SELECT sum(value1), sum(value2), sum(value3), sum(value4), sum(value5), sum(value6), sum(value7), sum(value8), sum(value9), sum(value10) FROM metrics_nullable; EXPLAIN (ANALYZE, BUFFERS, TIMING) SELECT sum(value1), sum(value2), sum(value3), sum(value4), sum(value5), sum(value6), sum(value7), sum(value8), sum(value9), sum(value10) FROM metrics_fixed;При 70% заполнении разница в хранении nullable 92 MB vs fixed 95 MB - далеко не в разы, разница менее 5% (1млн записей). При 10% заполнении nullable 69 MB vs fixed 95 MB - ~30%, никак не “разы”.
Скорость же выполнения запроса к таблицам у меня примерно на 30% быстрее для варианта с fixed (и от заполнения 70%/10% не сильно изменилась). С ростом таблицы время растет линейно, пропорция не меняется.
Откуда пошли ERP-системы - вопрос интересный. Я не упоминал подобного. Я в другой сфере работал.
Объемы - сотни GB, под террабайт базы бывали. Может это и крошечные. Но как показал пример, разница там не сильно велика.
P.S. интересно, что если не использовать null в примере вообще (для nullable полей), то разницы и нет ни в размерах, ни в скорости работы. Уж не знаю, в чью сторону это аргумент )
ptr128
20.05.2026 10:07Вот так и не понял
Объясняю, очень сильно упрощая. Есть справочник вагонов:
CREATE TABLE cars ( id integer PRIMARY KEY, accure_date date NOT NULL, -- дата акта получения dismiss_date date NULL, -- дата акта выбытия max_mileage integer -- максимальный пробег до планового ремонта );Есть рейсы вагонов:
CREATE TABLE hauls ( car_id integer REFERENCES cars, out_date datetimetz NOT NULL, -- дата отправления haul_mileage integer NOT NULL -- пробег в рейсе );Задача - вывести для каждого вагона, доступного на текущую дату (между accure_date и dismiss_date) остаточный пробег до планового ремонта, как разницу между максимальным пробегом и пробегом во всех совершенных им рейсов с момента акта получения вагона. С учётом того, что в наличии есть вагоны, которые после покупки, взятия в аренду или получения их по доверенности ещё не совершали рейсов. И таких вагонов у крупных операторов ежедневно сотни.
Искать затем, что “когда-то я работал в компании” - это 10+ лет. Сейчас я с SQL работаю сильно меньше и примера готового все равно не имею.
Во-первых, сразу видим несоответствие между
Попросил ИИ подобрать пример
и
Объемы - сотни GB, под террабайт базы бывали.
Ну и, как следствие, у AI возникла галлюцинация и он вставил в таблицу metrics_nullable множество нулей, хотя их там быть не должно.
Для информации, у меня только одна таблица истории дислокаций вагонов больше двух терабайт. DWH же суммарно давно петабайт преодолел.
Во-вторых, добавлять сюда размер индексов некорректно, так как обсуждался размер кортежей.
Во-третьих, я писал "в некоторых случаях", из чего следует, что могу привести пример.
Пожалуйста
CREATE TABLE metrics_nullable ( id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY, value1 decimal(18,2), value2 decimal(18,2), value3 decimal(18,2), value4 decimal(18,2), value5 decimal(18,2), value6 decimal(18,2), value7 decimal(18,2), value8 decimal(18,2), value9 decimal(18,2), value10 decimal(18,2), value11 decimal(18,2), value12 decimal(18,2), value13 decimal(18,2), value14 decimal(18,2), value15 decimal(18,2), value16 decimal(18,2) ); CREATE TABLE metrics_fixed ( id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY, value1 decimal(18,2) NOT NULL DEFAULT 0, value2 decimal(18,2) NOT NULL DEFAULT 0, value3 decimal(18,2) NOT NULL DEFAULT 0, value4 decimal(18,2) NOT NULL DEFAULT 0, value5 decimal(18,2) NOT NULL DEFAULT 0, value6 decimal(18,2) NOT NULL DEFAULT 0, value7 decimal(18,2) NOT NULL DEFAULT 0, value8 decimal(18,2) NOT NULL DEFAULT 0, value9 decimal(18,2) NOT NULL DEFAULT 0, value10 decimal(18,2) NOT NULL DEFAULT 0, value11 decimal(18,2) NOT NULL DEFAULT 0, value12 decimal(18,2) NOT NULL DEFAULT 0, value13 decimal(18,2) NOT NULL DEFAULT 0, value14 decimal(18,2) NOT NULL DEFAULT 0, value15 decimal(18,2) NOT NULL DEFAULT 0, value16 decimal(18,2) NOT NULL DEFAULT 0 ); INSERT INTO metrics_nullable (value1, value2, value3, value4, value5, value6, value7, value8, value9, value10, value11, value12, value13, value14, value15, value16) SELECT CASE WHEN random() < 0.01 THEN (random()*1000+1)::int END, CASE WHEN random() < 0.01 THEN (random()*1000+1)::int END, CASE WHEN random() < 0.01 THEN (random()*1000+1)::int END, CASE WHEN random() < 0.01 THEN (random()*1000+1)::int END, CASE WHEN random() < 0.01 THEN (random()*1000+1)::int END, CASE WHEN random() < 0.01 THEN (random()*1000+1)::int END, CASE WHEN random() < 0.01 THEN (random()*1000+1)::int END, CASE WHEN random() < 0.01 THEN (random()*1000+1)::int END, CASE WHEN random() < 0.01 THEN (random()*1000+1)::int END, CASE WHEN random() < 0.01 THEN (random()*1000+1)::int END, CASE WHEN random() < 0.01 THEN (random()*1000+1)::int END, CASE WHEN random() < 0.01 THEN (random()*1000+1)::int END, CASE WHEN random() < 0.01 THEN (random()*1000+1)::int END, CASE WHEN random() < 0.01 THEN (random()*1000+1)::int END, CASE WHEN random() < 0.01 THEN (random()*1000+1)::int END, CASE WHEN random() < 0.01 THEN (random()*1000+1)::int END FROM generate_series(1, 1000000) AS gs(i); INSERT INTO metrics_fixed (value1, value2, value3, value4, value5, value6, value7, value8, value9, value10, value11, value12, value13, value14, value15, value16) SELECT COALESCE(value1, 0), COALESCE(value2, 0), COALESCE(value3, 0), COALESCE(value4, 0), COALESCE(value5, 0), COALESCE(value6, 0), COALESCE(value7, 0), COALESCE(value8, 0), COALESCE(value9, 0), COALESCE(value10, 0), COALESCE(value11, 0), COALESCE(value12, 0), COALESCE(value13, 0), COALESCE(value14, 0), COALESCE(value15, 0), COALESCE(value16, 0) FROM metrics_nullable; SELECT 'nullable' AS table_type, pg_size_pretty(pg_relation_size('metrics_nullable')) AS total_size UNION ALL SELECT 'fixed', pg_size_pretty(pg_relation_size('metrics_fixed')); EXPLAIN ANALYZE SELECT sum(value1), sum(value2), sum(value3), sum(value4), sum(value5), sum(value6), sum(value7), sum(value8), sum(value9), sum(value10), sum(value11), sum(value12), sum(value13), sum(value14), sum(value15), sum(value16) FROM metrics_nullable; Finalize Aggregate (cost=27381.80..27381.81 rows=1 width=512) (actual time=79.031..80.915 rows=1.00 loops=1) Buffers: shared hit=5548 -> Gather (cost=27381.39..27381.60 rows=2 width=512) (actual time=78.888..80.894 rows=3.00 loops=1) Workers Planned: 2 Workers Launched: 2 Buffers: shared hit=5548 -> Partial Aggregate (cost=26381.39..26381.40 rows=1 width=512) (actual time=75.819..75.820 rows=1.00 loops=3) Buffers: shared hit=5548 -> Parallel Seq Scan on metrics_nullable (cost=0.00..9714.67 rows=416667 width=80) (actual time=0.004..13.483 rows=333333.33 loops=3) Buffers: shared hit=5548 Planning Time: 0.091 ms Execution Time: 80.959 ms EXPLAIN ANALYZE SELECT sum(value1), sum(value2), sum(value3), sum(value4), sum(value5), sum(value6), sum(value7), sum(value8), sum(value9), sum(value10), sum(value11), sum(value12), sum(value13), sum(value14), sum(value15), sum(value16) FROM metrics_fixed; Finalize Aggregate (cost=27439.68..27439.69 rows=1 width=512) (actual time=133.084..135.022 rows=1.00 loops=1) Buffers: shared hit=10310 -> Gather (cost=27439.09..27439.40 rows=3 width=512) (actual time=132.909..134.984 rows=4.00 loops=1) Workers Planned: 3 Workers Launched: 3 Buffers: shared hit=10310 -> Partial Aggregate (cost=26439.09..26439.10 rows=1 width=512) (actual time=129.484..129.485 rows=1.00 loops=4) Buffers: shared hit=10310 -> Parallel Seq Scan on metrics_fixed (cost=0.00..13535.81 rows=322581 width=48) (actual time=0.005..10.629 rows=250000.00 loops=4) Buffers: shared hit=10310 Planning Time: 0.091 ms Execution Time: 135.067 msВ-четвертых, проблема не столько в тупой выборке с суммированием, сколько в том, что NULL поле не требует поиска в индексе справочника, тогда как 0 - требует.
Поэтому имеем в результате
CREATE TABLE stations ( id integer PRIMARY KEY, -- идентификатор станиции name varchar NOT NULL -- наименование станции ); INSERT INTO stations (id, name) SELECT G.i, G.i::text FROM generate_series(1,40000) G(i) UNION ALL SELECT 0, '-- отсутствует --'; DROP TABLE hauls_fixed; DROP TABLE hauls_nullable; CREATE TABLE hauls_nullable ( id serial PRIMARY KEY, from_staion_id integer NOT NULL REFERENCES stations, -- станция отправления to_staion_id integer NOT NULL REFERENCES stations, -- станция назначения border_station_id integer NULL REFERENCES stations, -- станция пересечения границы export_station_id integer NULL REFERENCES stations, -- станция экспортного досмотра import_station_id integer NULL REFERENCES stations -- станция импортного досмотра ); INSERT INTO hauls_nullable (from_staion_id, to_staion_id, border_station_id, export_station_id, import_station_id) SELECT random()*39999+1, random()*39999+1, CASE WHEN random()<0.1 THEN random()*39999+1 END, CASE WHEN random()<0.1 THEN random()*39999+1 END, CASE WHEN random()<0.1 THEN random()*39999+1 END FROM generate_series(1,1000000) G(i); CREATE TABLE hauls_fixed ( id serial PRIMARY KEY, from_staion_id integer NOT NULL REFERENCES stations, -- станция отправления to_staion_id integer NOT NULL REFERENCES stations, -- станция назначения border_station_id integer NOT NULL REFERENCES stations, -- станция пересечения границы export_station_id integer NOT NULL REFERENCES stations, -- станция экспортного досмотра import_station_id integer NOT NULL REFERENCES stations -- станция импортного досмотра ); INSERT INTO hauls_fixed (from_staion_id, to_staion_id, border_station_id, export_station_id, import_station_id) SELECT from_staion_id, to_staion_id, COALESCE(border_station_id,0), COALESCE(export_station_id,0), COALESCE(import_station_id,0) FROM hauls_nullable; EXPLAIN ANALYZE SELECT COALESCE(B.name,'-- отсутствует --'), COALESCE(E.name,'-- отсутствует --'), COALESCE(I.name,'-- отсутствует --') FROM hauls_nullable H LEFT JOIN stations B ON B.id=H.border_station_id LEFT JOIN stations E ON E.id=H.export_station_id LEFT JOIN stations I ON I.id=H.import_station_id; Hash Left Join (cost=3993.07..27297.13 rows=1000000 width=96) (actual time=19.255..387.291 rows=1000000.00 loops=1) Hash Cond: (h.import_station_id = i.id) Buffers: shared hit=6722 -> Hash Left Join (cost=2662.05..23341.09 rows=1000000 width=14) (actual time=12.366..271.725 rows=1000000.00 loops=1) Hash Cond: (h.export_station_id = e.id) Buffers: shared hit=6291 -> Hash Left Join (cost=1331.02..19385.04 rows=1000000 width=13) (actual time=6.237..173.591 rows=1000000.00 loops=1) Hash Cond: (h.border_station_id = b.id) Buffers: shared hit=5860 -> Seq Scan on hauls_nullable h (cost=0.00..15429.00 rows=1000000 width=12) (actual time=0.010..40.307 rows=1000000.00 loops=1) Buffers: shared hit=5429 -> Hash (cost=831.01..831.01 rows=40001 width=9) (actual time=6.181..6.182 rows=40001.00 loops=1) Buckets: 65536 Batches: 1 Memory Usage: 2227kB Buffers: shared hit=431 -> Seq Scan on stations b (cost=0.00..831.01 rows=40001 width=9) (actual time=0.013..2.531 rows=40001.00 loops=1) Buffers: shared hit=431 -> Hash (cost=831.01..831.01 rows=40001 width=9) (actual time=6.081..6.082 rows=40001.00 loops=1) Buckets: 65536 Batches: 1 Memory Usage: 2227kB Buffers: shared hit=431 -> Seq Scan on stations e (cost=0.00..831.01 rows=40001 width=9) (actual time=0.009..2.539 rows=40001.00 loops=1) Buffers: shared hit=431 -> Hash (cost=831.01..831.01 rows=40001 width=9) (actual time=6.839..6.840 rows=40001.00 loops=1) Buckets: 65536 Batches: 1 Memory Usage: 2227kB Buffers: shared hit=431 -> Seq Scan on stations i (cost=0.00..831.01 rows=40001 width=9) (actual time=0.008..2.281 rows=40001.00 loops=1) Buffers: shared hit=431 Planning: Buffers: shared hit=18 Planning Time: 0.266 ms Execution Time: 416.061 ms EXPLAIN ANALYZE SELECT B.name, E.name, I.name FROM hauls_fixed H JOIN stations B ON B.id=H.border_station_id JOIN stations E ON E.id=H.export_station_id JOIN stations I ON I.id=H.import_station_id; Hash Join (cost=3993.07..28238.90 rows=1000000 width=15) (actual time=18.645..459.774 rows=1000000.00 loops=1) Hash Cond: (h.import_station_id = i.id) Buffers: shared hit=7663 -> Hash Join (cost=2662.05..24282.60 rows=1000000 width=14) (actual time=12.302..324.342 rows=1000000.00 loops=1) Hash Cond: (h.export_station_id = e.id) Buffers: shared hit=7232 -> Hash Join (cost=1331.02..20326.30 rows=1000000 width=13) (actual time=6.391..196.050 rows=1000000.00 loops=1) Hash Cond: (h.border_station_id = b.id) Buffers: shared hit=6801 -> Seq Scan on hauls_fixed h (cost=0.00..16370.00 rows=1000000 width=12) (actual time=0.009..39.691 rows=1000000.00 loops=1) Buffers: shared hit=6370 -> Hash (cost=831.01..831.01 rows=40001 width=9) (actual time=6.336..6.337 rows=40001.00 loops=1) Buckets: 65536 Batches: 1 Memory Usage: 2227kB Buffers: shared hit=431 -> Seq Scan on stations b (cost=0.00..831.01 rows=40001 width=9) (actual time=0.007..2.269 rows=40001.00 loops=1) Buffers: shared hit=431 -> Hash (cost=831.01..831.01 rows=40001 width=9) (actual time=5.863..5.863 rows=40001.00 loops=1) Buckets: 65536 Batches: 1 Memory Usage: 2227kB Buffers: shared hit=431 -> Seq Scan on stations e (cost=0.00..831.01 rows=40001 width=9) (actual time=0.012..2.315 rows=40001.00 loops=1) Buffers: shared hit=431 -> Hash (cost=831.01..831.01 rows=40001 width=9) (actual time=6.298..6.299 rows=40001.00 loops=1) Buckets: 65536 Batches: 1 Memory Usage: 2227kB Buffers: shared hit=431 -> Seq Scan on stations i (cost=0.00..831.01 rows=40001 width=9) (actual time=0.015..2.631 rows=40001.00 loops=1) Buffers: shared hit=431 Planning: Buffers: shared hit=18 Planning Time: 0.261 ms Execution Time: 488.732 msПример приближен к реальности, так как действительно 90% грузоперевозок выполняются внутри РФ. Если же посмотреть на форматы данных АСОУП-3 или ЭТРАН, то там 99% полей nullable (minOccurs=“0” в xsd) и по факту, например в ж/д накладной заполняются в среднем 5% полей. Что, в принципе, логично. Так как ссылки на договора и акты охраны указывается для 0.01% грузоотправок, ссылки на договора и акты сопровождения - 0.1%, есть множество полей, заполняемых только для некоторых видов грузов, локомотивов или видов вагонов и т.п. Привожу в качестве примера, так как xsd обмена с АСОУП-3 и ЭТРАН общедоступны.
Откуда пошли ERP-системы - вопрос интересный.
Потому что это самый простой вариант реальной и достаточно сложной структуры БД, доступ к которой есть у любого желающего. Вы можете предложить иной вариант доступной Вам и мне реальной enterprise БД?
SabMakc
20.05.2026 10:07Если возвращаться к теме вопроса - “0 вместо NULL” (а то мне сложно вникать во взаимосвязь “10+ лет назад”, “БД на сотни гигов” и “в metrics_nullable не должно быть нулей” и комментировать “у всех под рукой ERP”).
Да, от LEFT JOIN не избавиться полностью. Отчеты/аналитика все равно будут требовать его использования.
Есть небольшой оверхед к размеру таблиц (к слову, меньше чем я ожидал). Но есть и некоторый профит в быстродействии.
Обычные индексы могут больше места занимать (partial-индексы в помощь).
В каких-то особых случаях не подойдет.
P.S. размер БД и отдельных таблиц в ней в петабайты и терабайты - это мерило успеха или криворукости? Лично я как-то не уверен, какой вариант правильнее. Вероятно, ответ где-то посредине )
P.P.S. может пример и приближен к реальности (хотя я и не уверен - но это отдельная тема, не относящаяся к основному вопросу), но, в любом случае, это явно не самая типовая ситуация.
Akina
20.05.2026 10:07Теперь развернём
NOT INв то, чем он работает под капотом.x IN (a, b, c)— это сокращение дляx = a OR x = b OR x = c. Соответственно,x NOT IN (a, b, c)— этоNOT (x = a OR x = b OR x = c), по законам логики —x <> a AND x <> b AND x <> c.Кстати.. вот сижу и думаю - а применимы ли тут законы бинарной логики?
ptr128
20.05.2026 10:07Не применимы, так как значением NULL=a и NULL<>a будет не false или true, а NULL
Akina
Предлагаю изменить форматирование и сделать вот это вот всё крупным, ярким и ну очень выделенным, чтобы сразу бросалось в глаза. Может быть, даже оформить это как отдельный раздел, а не как рядовой фрагмент внутри раздела. Потому что именно это - объяснение, а всё остальное - просто антураж.