postgresql - Postgres 在枚举类型上使用部分索引来表示相等条件但不用于不相等
问题描述
使用 PostgreSQL 9.6.9,我有:
- 在某个表 xy 的列中使用的具有 3 个级别的自定义 SQL 枚举类型。
- 表 xy 上此枚举的最高级别 (level3) 的复合部分索引。
- 大量虚拟数据(超过 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 没有正确优化这个……这似乎是一些简单的逻辑事情。
解决方案
一般来说
Postgres 仅在估计整体速度更快时才使用索引(或位图索引)扫描。不等式条件通常比等式条件返回(很多)行。因此,如果表统计信息和成本设置表明这可能会更快,查询计划器可能会切换到顺序扫描。使用索引会增加开销,并且通常仅在从表中获取一小部分行时才能提高性能。大约 5 % 或更少,随细节变化很大。
您确定表统计信息是最新的吗?你ANALYZE
在桌子上跑了吗?看:
要测试是否可以使用索引,请在测试会话中禁用顺序扫描(仅用于调试!):
SET enable_seqscan = OFF;
然后EXPLAIN ANALYZE
再次运行。
部分索引
WHERE
Postgres 仅在查询中几乎完全满足条件时才考虑部分索引。没有尝试分析表达式的高级逻辑(因为这可能会很快失控并增加大多数不从部分索引中获利的查询的开销)。
如果您有带有条件的部分索引,则甚至不会考虑带有该条件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
类型,查询可能会被破坏。
有关的:
索引和统计
注意两点:
对于涉及函数表达式的部分索引,有单独的每列统计信息,但不仅仅是普通列引用。
创建索引不会ANALYZE
自动触发基础表(或自身)上的索引。但是行计数等基本统计信息在pg_class
.
推荐阅读
- android - 当我想从 Firebase 获取数据时,为什么应用程序会关闭?
- tcp - 进程和 TCPSocket 在水晶中没有正确关闭
- pandas - 如何将ndarray转换为pandas DataFrame
- r - 使用 ibmdbR 包在 R 对象中追加和更新 DB2 表行
- sql-server - SQL Express - 我可以对现有表进行分区以解决 10GB 文件大小限制吗?
- wpf - 将对象绑定到组合框和文本框
- c - memcpy:GCC 或实现优化?
- javascript - 使用 JavaScript 创建文件并在单击按钮时下载
- unit-testing - 使用 github.com/jarcoal/httpmock 验证请求正文
- angular - Angular 7中ng-cloak(angularjs)的替代方法是布尔变量而不是对象