Эффективная индексация разреженного столбца с нулевым значением в Postgres

Если большинство значений имеют значение NULL, наиболее эффективным способом поиска NULL является последовательное сканирование таблицы. Таким образом, индекс не поможет вам в таком поиске. Следовательно, вы могли бы также использовать частичный индекс; вы ничего не потеряете.

Начиная с PostgreSQL v13, индексы B-дерева «дедублируют» повторяющиеся значения, и от этого могут выиграть столбцы с большим количеством значений NULL. Таким образом, даже если вы не создадите частичный индекс, индекс будет довольно небольшим (но вы будете платить за изменения индекса всякий раз, когда вставляете NULL).

частичный индекс на IS NOT NULL позволяет мне эффективно искать только все ненулевые значения, а не конкретное ненулевое значение.

Это звучит как одно из этого:

create index on tbl(col is not null);
create index on tbl(col is not null, col);
create index on tbl(col, col is not null);

Но ничто из вышеперечисленного не является частичным индексом . Это было бы

create index on tbl(col)where(col is not null);

И он позволяет вам «эффективно искать определенные ненулевые значения», будучи в точности «индексом значения в этом столбце, который исключает из индекса только значения NULL».

демо на db<>fiddle

select setseed(.42);

create table tbl(col,r)as 
  select case when .5<random() then n::int end as col--50% null `col` values
        ,random() as r
  from generate_series(1,7e5)n 
  where .1<random()
  order by random();

create index idx1_normal_no_deduplication on tbl(col)
  with(deduplicate_items=false);
create index idx2_partial_no_deduplication on tbl(col)
  with(deduplicate_items=false)
  where(col is not null);
create index idx3_normal_deduplicated on tbl(col)
  with(deduplicate_items=true);
create index idx4_partial_deduplicated on tbl(col)
  with(deduplicate_items=true)
  where(col is not null);

Вы можете проверить, насколько меньше частичный индекс по сравнению с обычным:

select indexrelname
     , pg_relation_size(indexrelid)
     , pg_size_pretty(pg_relation_size(indexrelid))
from pg_stat_all_indexes i 
  join pg_class c 
  on i.relid=c.oid 
where i.relname='tbl'
order by 2;

Это имеет смысл, учитывая, что 50% col являются null, а все остальные значения уникальны. Это также показывает, что даже при включенной дедупликации частичный индекс все равно может занимать гораздо меньший объем, чем дедуплицированный, нечастичный. Если дедупликация отключена, вы получите индекс на 50 % меньше, и даже при ее включении еще можно сэкономить 25 %. В конце концов, равные значения не пропускаются полностью, они все еще присутствуют, но лучше уплотняются.
Само собой разумеется, что при меньших значениях null разница будет менее выражена.

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

explain analyze verboseselect from tbl where col<8e4;

explain analyze verbose select from tbl where col=8e4;

Учет col is null отключает использование индекса, поскольку именно эти случаи игнорируются индексом (а также потому, что может быть проще выполнить последовательное сканирование, если условия описывают достаточно большую часть набора):

explain analyze verbose select from tbl where col=8e4 or col is null;