PostgreSQL Antipatterns: куда крутить NULLS

[bigtrot]

Дополнительно, раз уж пошла речь про особенности работы с NULL, то есть видео
https://www.youtube.com/watch?v=2qTczke77q4

[qw1]

Не имею postgre под рукой, но мне кажется будет оптимальнее

SELECT * FROM tstord WHERE val < 1000000 ORDER BY val DESC LIMIT 1000;

скан по индексу назад, без зачитывания и пропуска NULL-ов. Если сервер, конечно, до этого догадается.

[Kilor]

Смысл примерно одинаковый, поэтому я не стал отдельно в статье приводить:

Limit (actual time=0.013..0.775 rows=1000 loops=1)
  Buffers: shared hit=1007
  ->  Index Scan Backward using tstord_val_idx on tstord (actual time=0.011..0.709 rows=1000 loops=1)
        Index Cond: (val < 1000000)
        Buffers: shared hit=1007

[qw1]

В теории, оптимизатор должен был найти в дереве индекса значение, ближайшее в 1e6, и идти от него вперёд, т.е. по уменьшению val (сразу пропустив все null значения в начале).
Видимо, тут null-ов слишком мало, чтобы их пропуск как-то повлиял на количество прочитанных страниц.

[Kilor]

Фактически, он так и делает:

По умолчанию элементы B-дерева хранятся в порядке возрастания, при этом значения NULL идут в конце (для упорядочивания равных записей используется табличный столбец TID).

Ровно поэтому, ошибочно начитав кучу NULL'ов, мы увидели всего лишьshared hit=477, а, прочитав нужные -shared hit=1007.

То есть экземпляры одного NULL-значения за счет дедупликации хранятся в индексе более эффективно, чем различные значения val.

[qw1]

есть экземпляры одного NULL-значения за счет дедупликации хранятся в индексе более эффективно, чем различные значения val

Что-то как-то сомнительно. У каждого null-а в индексе должна быть ссылка на строку таблицы, откуда он взялся. Было бы интересно создать табличку из миллиона null-ов и табличку из миллиона единичек, построить на них индексы и посмотреть размеры этих индексов.

[Kilor]

SELECT version();
-- PostgreSQL 13beta2 ... - уже есть дедупликация
CREATE TABLE tst0 AS
SELECT
  i
, 0 z
, NULL n
FROM
  generate_series(1, 1e6) i;
CREATE INDEX ON tst0(i);
CREATE INDEX ON tst0(z);
CREATE INDEX ON tst0(n);

SELECT relname, pg_relation_size(oid) FROM pg_class WHERE relname LIKE 'tst0%';
--  tst0       | 44285952
--  tst0_i_idx | 22487040
--  tst0_n_idx |  6955008
--  tst0_z_idx |  6955008

[qw1]

Из этого делаю вывод, что куча ноликов в индексе весит ровно столько же, сколько такая же куча null-ов.
А если сделать всё то же самое, но

SELECT
  i
, 123456789 z
, NULL n

индекс по столбцу z будет того же размера? Если да, я поверю в дедупликацию, а не в кодирование переменной длины.

[Kilor]

ALTER TABLE tst0 ADD COLUMN v integer;
UPDATE tst0 SET v = 123456789;
CREATE INDEX ON tst0(v);
VACUUM FULL tst0;
-- tst0       | 44285952
-- tst0_i_idx | 22487040
-- tst0_n_idx |  6955008
-- tst0_v_idx |  6955008
-- tst0_z_idx |  6955008

[qw1]

Ровно поэтому, ошибочно начитав кучу NULL'ов, мы увидели всего лишьshared hit=477, а, прочитав нужные -shared hit=1007

Я бы это объяснил кодированием переменной длины. Число '1' занимает меньше бит в строке БД, чем число '123456789'. Хотя, по схеме данных, они оба int32, но известные мне движки БД (к сожалению, не postgre, а sqlite и firebird) меньшие числа кодируют меньшим числом бит. Также и null кодируется короче, чем типичный id из generate_series, но это не значит, что есть дедупликация null-ов.

[Kilor]

NULL в записи кодируется короче (битом в заголовке), но вот fixlen-значения всегда занимают все выделенное под них место. Вот тут чуть подробнее: https://habr.com/ru/post/542058/

[edo1h]

SELECT * FROM tstord WHERE val < 1000000 ORDER BY val DESC LIMIT 1000;

а в чём разница для постгреса?
он может настолько же эффективно исключить все null с ипользованием индекса

[qw1]

Логика моих рассуждений была в том, что в том индексе ключи лежат в отсортированном виде в последовательности:
null, null, null, ..., null, 999999, 999998, 999997, 999996,…
И неравенство в WHERE включит режим сканирования индекса по диапазону, сразу скипнет все null-ы и начнёт с числа. Но автор статьи меня убедил, что одинаковые ключи в индексе не повторяются (если подумать, то это даже и логично), поэтому скипнуть тут можно только 1 узел индекса.