postgresql - Postgres 在枚举类型上使用部分索引来满足相等条件而不是不相等条件

Question

使用 PostgreSQL 9.6.9，我有:

• 一个自定义 SQL 枚举类型，具有 3 个级别，用于某些列表 xy。
• 表 xy 上此枚举的最高级别(级别 3)的复合部分索引。
• 大量虚拟数据(超过 200000 行)

然后在控制台中使用 EXPLAIN ANALYSE，我看到:

SELECT ... FROM xy WHERE ... AND custom_type = 'level3' <--- Index Scan used

但是:

SELECT ... FROM xy WHERE ... AND custom_type > 'level2' <--- Index Scan NOT used

查询完全相同，除了不等式/相等条件。

难道数据库规划者看不到level2以上只能有level3，所以可以使用部分索引吗？

为什么 Postgres 没有适本地优化它......这看起来像是一些简单的逻辑问题。

Answer 1

一般

Postgres 仅在估计整体速度更快时才使用索引(或位图索引)扫描。不等式条件通常返回(许多)比等式条件更多的行。因此，如果表统计信息和成本设置表明这可能更快，查询规划器可能会切换到顺序扫描。使用索引会增加开销，并且通常只会在从表中获取小 百分比的行时提高性能。大约 5% 或更少，因细节而异。

您确定表格统计信息是最新的吗？你在 table 上运行了 ANALYZE 了吗？见:

• Keep PostgreSQL from sometimes choosing a bad query plan

要测试是否可以使用索引，请在测试 session 中禁用顺序扫描(仅用于调试!):

 SET enable_seqscan = OFF;

然后再次运行EXPLAIN ANALYZE。

部分索引

Postgres 仅在查询中完全满足WHERE 条件时才考虑部分索引。没有尝试分析表达式的高级逻辑(因为这可能很快就会失控并增加大多数不从部分索引中获利的查询的开销)。

如果您有条件为 WHERE custom_type = 'level3' 的部分索引，则甚至不会考虑条件为 WHERE custom_type > 'level2' 的查询。简单的解决方案是将部分索引的条件添加到您的查询中(冗余)。喜欢:

SELECT ... FROM xy WHERE ... AND custom_type > 'level2'
AND custom_type = 'level3';  -- redundant, but makes Postgres consider partial index

自作聪明时要小心:如果您稍后扩展您的 enum 类型，查询可能会被破坏。

相关:

• PostgreSQL does not use a partial index
• Optimize performance for queries on recent rows of a large table

索引和统计

注意两点:

对于涉及函数表达式的部分索引，有单独的每列统计信息，但不仅仅是普通列引用。

创建索引不会自动触发基础表(或自身)的ANALYZE。但是像行数这样的基本统计数据在 pg_class 中更新。

• Kaminari is slow with COUNT(*) on a huge table in Postgres
• PostgreSQL partial index unused when created on a table with existing data
• Inconsistent statistics on jsonb column with btree index
• GIN index not used when adding order clause
• Optimizing slow performance of simple SELECT query