时间戳

首页 > 解决方案 > where 和 order by 子句的多列索引（postgres）

sql - where 和 order by 子句的多列索引（postgres）

问题描述

考虑这样的查询：

select ... from table where a = 1 and b > 2 order by c asc

这个查询的理想索引是什么？我应该使用一个索引（a，b，c）还是两个单独的（a，b）和（c）？

提前致谢。

标签： sqlpostgresqlindexing

解决方案

你可能不能同时做好这两件事。相等不是问题，但你不能干净地将不等与排序结合起来。您希望返回多少行？如果b>2非常有选择性地留下几行进行排序，那么显然你想要(a,b)获得选择性是有用的，而对剩余的几行进行排序不会花费很长时间。另一方面，如果b>2只排除几行，那么(a,c)（可能在末尾有更多列以允许仅索引扫描）避免大的慢速排序，同时删除少数失败的行b“硬方式”不会需要很长时间。

您可以同时构建两者并让计划者进行调用，尽管这样做远非完美。

可以在所有三列上使用 GiST 索引来同时实现不等式过滤和排序，但 GiST 索引的开销比 BTree 索引高得多，而且构建和维护速度较慢，因此不太可能值得一试，尽管如果您使用 LIMIT 可以使其更具吸引力。您还需要以扭曲的方式编写查询，作为 KNN 查询（​​由btree_gist扩展辅助）：

where a = 1 and b > 2 order by c <-> impossibly_low_value

还有其他高级可能性。如果你比较 b 的东西总是>2，那么你可以使用部分索引(a,c) where b>2或表达式索引(a,(b>2),c)。您也许还可以在 b 上进行有用的分区。如果 b 有少量不同的值，您可以对每个合格的不同值的查询结果进行 UNION ALL 并使用索引 on (a,b,c)，获得有效的合并追加：

(select * from foo where a=1 and b=3 order by c asc) 
    union all 
(select * from foo where a=1 and b=4 order by c asc) 
order by c asc;

推荐阅读