PostgreSQL: 创建一个索引来快速区分 NULL 和非 NULL 值

考虑使用以下 WHERE谓词的 SQL 查询:

...
WHERE name IS NOT NULL
...

其中 name是 PostgreSQL 中的文本字段。

没有其他查询检查此值的任何文本属性，只是检查它是否为 NULL。因此，尽管 它支持这种区别:

此外，索引列上的 ISNULL 或 ISNOTNULL 条件可以与 B 树索引一起使用。

什么是正确的 PostgreSQL 索引来快速区分 NULL和非 NULL？

43845 次浏览
小开
最佳答案

我从两个方面来解释您所说的“过度杀伤”: 复杂性(使用 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行索引，那么在空间方面的增益很小。详情请参阅 克雷格 · 林格的回答。

小开

可以使用表达式索引，但不应该使用表达式索引。

可以在 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)

所以实际上最好使用两个不相交的表达式索引，一个在 null 上，一个在非 null 集上。

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-tree 索引。索引大小的改进并不太令人兴奋，至少对于小型输入来说是这样，比如我用一堆 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';

这比使用谓词有很大的优势，在这种情况下，索引中存储的值只是一个是/否布尔值，而不是完整的列值。因此，特别是如果 NULL 检查的列往往包含较大的值(比如这里的标题文本字段) ，那么这种索引方式比使用谓词索引更加节省空间。


提交答案