Рассмотрим SQL-запрос со следующим предикатом WHERE:
...
WHERE name IS NOT NULL
...
Где name - текстовое поле в PostgreSQL.
Ни один другой запрос не проверяет текстовое свойство этого значения, просто ли оно NULL или нет. Поэтому полный индекс btree кажется излишним, хотя он поддерживает это различие:
Кроме того, условие IS NULL или IS NOT NULL в столбце индекса может использоваться с индексом B-дерева.
Какой правильный индекс PostgreSQL быстро отличает NULL от non- NULL?
Я интерпретирую, что вы утверждаете, что он "перекидывает" двумя способами: с точки зрения сложности (используя B-Tree вместо просто списка) и пространства/производительности.
Для сложности он не переполняет. Индекс B-Tree предпочтительнее, поскольку удаление из него будет быстрее, чем какой-либо "неупорядоченный" индекс (из-за отсутствия лучшего термина). (Для неупорядоченного индекса требуется полное сканирование индекса только для удаления.) В свете этого любой выигрыш от неупорядоченного индекса обычно будет перевешиваться в результате помех, поэтому усилия по разработке не оправданы.
Однако для пространства и производительности, если вам нужен высокоселективный индекс эффективности, вы можете включить предложение WHERE в индекс, как указано в прекрасном руководстве:
CREATE INDEX ON my_table (name) WHERE name IS NOT NULL;
Обратите внимание, что вы увидите только преимущества этого индекса, если он может позволить PostgreSQL игнорировать большое количество строк при выполнении вашего запроса. Например, если 99% строк имеют name IS NOT NULL, индекс не покупает вам что-либо, просто позволяя провести полное сканирование таблицы; Фактически, это было бы менее эффективно (как отмечает @CraigRinger), поскольку для этого потребуются дополнительные чтения дисков. Если, однако, только 1% строк имеют name IS NOT NULL, то это означает огромную экономию, поскольку PostgreSQL может игнорировать большую часть таблицы для вашего запроса. Если ваш стол очень большой, даже удаление 50% строк может стоить того. Это проблема настройки, и важно ли значение индекса, будет сильно зависеть от размера и распределения данных.
Кроме того, очень мало выгоды в плане пространства, если вам все еще нужен другой индекс для name IS NULL строк. См. Ответ Крейга Рингера.
Вы можете использовать индекс выражения, но не должны. Держите его простым и используйте простое дерево b.
Индекс выражения может быть создан на colname IS NOT NULL:
test=> CREATE TABLE blah(name text);
CREATE TABLE
test=> CREATE INDEX name_notnull ON blah((name IS NOT NULL));
CREATE INDEX
test=> INSERT INTO blah(name) VALUES ('a'),('b'),(NULL);
INSERT 0 3
test=> SET enable_seqscan = off;
SET
craig=> SELECT * FROM blah WHERE name IS NOT NULL;
name
------
a
b
(2 rows)
test=> EXPLAIN SELECT * FROM blah WHERE name IS NOT NULL;
QUERY PLAN
-----------------------------------------------------------------------------
Bitmap Heap Scan on blah (cost=9.39..25.94 rows=1303 width=32)
Filter: (name IS NOT NULL)
-> Bitmap Index Scan on name_notnull (cost=0.00..9.06 rows=655 width=0)
Index Cond: ((name IS NOT NULL) = true)
(4 rows)
test=> SET enable_bitmapscan = off;
SET
test=> EXPLAIN SELECT * FROM blah WHERE name IS NOT NULL;
QUERY PLAN
------------------------------------------------------------------------------
Index Scan using name_notnull on blah (cost=0.15..55.62 rows=1303 width=32)
Index Cond: ((name IS NOT NULL) = true)
Filter: (name IS NOT NULL)
(3 rows)
... но Pg не понимает, что он также применим для IS NULL:
test=> EXPLAIN SELECT * FROM blah WHERE name IS NULL;
QUERY PLAN
-------------------------------------------------------------------------
Seq Scan on blah (cost=10000000000.00..10000000023.10 rows=7 width=32)
Filter: (name IS NULL)
(2 rows)
и даже преобразует NOT (name IS NOT NULL) в name IS NULL, что обычно является тем, что вы хотите.
test=> EXPLAIN SELECT * FROM blah WHERE NOT (name IS NOT NULL);
QUERY PLAN
-------------------------------------------------------------------------
Seq Scan on blah (cost=10000000000.00..10000000023.10 rows=7 width=32)
Filter: (name IS NULL)
(2 rows)
так что вам действительно лучше с двумя непересекающимися индексами выражения, один на нулевом и один на ненулевом множестве.
test=> DROP INDEX name_notnull ;
DROP INDEX
test=> CREATE INDEX name_notnull ON blah((name IS NOT NULL)) WHERE (name IS NOT NULL);
CREATE INDEX
test=> EXPLAIN SELECT * FROM blah WHERE name IS NOT NULL;
QUERY PLAN
--------------------------------------------------------------------------
Index Scan using name_notnull on blah (cost=0.13..8.14 rows=3 width=32)
Index Cond: ((name IS NOT NULL) = true)
(2 rows)
test=> CREATE INDEX name_null ON blah((name IS NULL)) WHERE (name IS NULL);
CREATE INDEX
craig=> EXPLAIN SELECT * FROM blah WHERE name IS NULL;
QUERY PLAN
-----------------------------------------------------------------------
Index Scan using name_null on blah (cost=0.12..8.14 rows=1 width=32)
Index Cond: ((name IS NULL) = true)
(2 rows)
Это довольно ужасно. Для самых разумных применений я бы просто использовал простой индекс b-дерева. Улучшение размера индекса не слишком захватывающее, по крайней мере для небольших входов, например, манекен, который я создал с кучей значений md5:
test=> SELECT pg_size_pretty(pg_relation_size('blah'));
pg_size_pretty
----------------
9416 kB
(1 row)
test=> SELECT pg_size_pretty(pg_relation_size('blah_name'));
pg_size_pretty
----------------
7984 kB
(1 row)
test=> SELECT pg_size_pretty(pg_relation_size('name_notnull'));
pg_size_pretty
----------------
2208 kB
(1 row)
test=> SELECT pg_size_pretty(pg_relation_size('name_null'));
pg_size_pretty
----------------
2208 kB
(1 row)
Вы можете использовать выражение, подобное (title IS NULL), как индексированный столбец. Таким образом, это работает так, как ожидалось:
CREATE INDEX index_articles_on_title_null ON articles ( (title IS NULL) );
SELECT * FROM articles WHERE (title IS NULL)='t';
Это имеет большое преимущество перед использованием предиката, в этом случае значение, хранящееся в индексе, представляет собой только логическое значение yes/no, а не полное значение столбца. Поэтому, особенно если ваш столбец с проверкой NULL имеет тенденцию содержать большие значения (например, здесь текстовое поле заголовка), этот способ индексирования гораздо более экономичен по площади, чем использование предикатного индекса.