代码之家 › 专栏 › 技术社区 › Christophe Roussy

对枚举类型使用部分索引的Postgres用于等式条件,但不用于不等式

query-planner inequality database-indexes postgresql

1

Christophe Roussy · 技术社区 · 6 年前

使用PostgreSQL 9.6.9,我有:

使用 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没有正确地优化这个。。。这似乎是一些简单的逻辑问题。

1 回复 | 直到 6 年前

1

Erwin Brandstetter 6 年前

Postgres只使用索引(或位图索引)扫描,如果它估计它整体更快。不等式条件通常比等式条件返回(多)行。因此,如果表统计数据和成本设置表明这可能更快,那么查询计划器可能会切换到顺序扫描。使用索引会增加开销,通常只有在小的从表中提取行的百分比。大约5%或更少,随细节变化很大。

你确定表格统计是最新的吗?你跑了吗 ANALYZE 在桌子上?见:

要测试,索引是否可以在测试会话中禁用顺序扫描(仅用于调试!):

 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 类型,查询可能已断开。

注意两件事:

对于包含函数表达式的部分索引,有单独的每列统计信息,但不能只包含纯列引用。

创建索引不会触发 分析 在基础表上(或为其本身)自动执行。但是行计数之类的基本统计数据在 pg_class .