PostgreSQL：创建索引以快速区分NULL和非NULL值

考虑具有以下内容的 SQL 查询WHERE谓词：

...
WHERE name IS NOT NULL
...

Where name是 PostgreSQL 中的文本字段。

没有其他查询检查该值的任何文本属性，只是检查它是否是NULL或不。所以，完整的 btree 索引 http://www.postgresql.org/docs/9.3/static/indexes-types.html看起来有点矫枉过正，尽管它支持这种区别 http://www.postgresql.org/docs/9.3/static/indexes-types.html:

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

什么是正确的PostgreSQL索引可以快速区分NULLs 来自非NULLs?

我的意思是你声称它在两个方面“过度杀伤”：在复杂性方面（使用 B 树而不只是列表）和空间/性能。

对于复杂性来说，这并不过分。 B 树索引更可取，因为deletes它将比某种“无序”索引更快（因为缺乏更好的术语）。 （无序索引需要完整索引扫描才能删除。）鉴于这一事实，无序索引的任何收益通常都会被其损害所抵消，因此开发工作是不合理的。

但是，对于空间和性能，如果您想要一个高度选择性的索引以提高效率，您可以包括WHERE索引上的子句，如中所述精美手册 http://www.postgresql.org/docs/9.3/static/sql-createindex.html:

CREATE INDEX ON my_table (name) WHERE name IS NOT NULL;

请注意，只有当该索引允许 PostgreSQL 忽略某个索引时，您才会看到它的好处。large执行查询时的行数。例如，如果 99% 的行有name IS NOT NULL，索引不会给你带来任何好处，而只是让全表扫描发生；事实上，它的效率会较低（如@CraigRinger https://stackoverflow.com/users/398670/craig-ringer注），因为它需要额外的磁盘读取。然而，如果只有 1% 的行有name IS NOT NULL，那么这意味着巨大的节省，因为 PostgreSQL 可以忽略查询的大部分表。如果您的表非常大，即使删除 50% 的行也可能是值得的。这是一个调优问题，索引是否有价值将在很大程度上取决于数据的大小和分布。

此外，如果您仍然需要另一个索引来存储数据，那么在空间方面几乎没有什么好处。name IS NULL行。看克雷格·林格的回答 https://stackoverflow.com/a/31966621/1394393了解详情。