PostgreSQL: создайте индекс, чтобы быстро отличать NULL от значений, отличных от NULL

Рассмотрим SQL-запрос со следующим предикатом WHERE:

...
WHERE name IS NOT NULL
...

Где name - текстовое поле в PostgreSQL.

Ни один другой запрос не проверяет текстовое свойство этого значения, просто ли оно NULL или нет. Поэтому полный индекс btree кажется излишним, хотя он поддерживает это различие:

Кроме того, условие IS NULL или IS NOT NULL в столбце индекса может использоваться с индексом B-дерева.

Какой правильный индекс PostgreSQL быстро отличает NULL от non- NULL?

Ответы

Ответ 1

Я интерпретирую, что вы утверждаете, что он "перекидывает" двумя способами: с точки зрения сложности (используя 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 строк. См. Ответ Крейга Рингера.

Ответ 2

Вы можете использовать индекс выражения, но не должны. Держите его простым и используйте простое дерево 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)

Ответ 3

Вы можете использовать выражение, подобное (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 имеет тенденцию содержать большие значения (например, здесь текстовое поле заголовка), этот способ индексирования гораздо более экономичен по площади, чем использование предикатного индекса.