两列中的索引在同一表格中进行比较 [英] Index in two columns range comparisson within the same table
问题描述
在postgres或Mysql中是否可以创建一个索引来帮助同一个表中两列之间的范围比较？
Is it possible in postgres or Mysql to create an index for helping a range comparison between two columns within the same table?
例如，我有一个名为table的表测试，包含3列： id ， col1 和 col2 ，它有2000万行。查询如下所示：
For example, I have a table named Test with 3 columns: id, col1 and col2, and it has 20 million rows. The query looks like this:
SELECT id,col1,col2 from Test where col1 < col2;
查询计划员说它在整个表中使用顺序扫描。像这样的索引会起作用吗？：
The query planner is saying it's using a sequential scan in the whole table. Would an index like this work?:
CREATE INDEX idx_test on Test(col1,col2);
我已经创建了它，但查询规划器仍然进行顺序扫描。
I already created it but query planner still does sequential scans.
有没有办法增强查询以减少执行时间？
Is there a way to enhance that query to reduce the execution time?
推荐答案
是的。使用数学，Luis！
Yes. Use math, Luis!
重新调整查询，以便所有列出现在过滤器的一侧（我基本上做了等效的转换 -col2 ）：
Rephrase the query so that all columns appear on one side of the filter (I did basically do an equivalent transformation -col2):
SELECT id,col1,col2 from Test where col1 - col2 < 0;
在该表达式上添加索引：
Add index on that expression:
CREATE INDEX idx_test on Test (col1-col2);
该索引仅适用于PostgreSQL，但不适用于MySQL。根据您的整体系统负载，另一个索引stragety可能仍然会更好。此索引不能用于 col1 或 col2 上的查询。
The index will only work in PostgreSQL, but not MySQL. Depending on your overall system load, another indexing stragety might still be better. This index will not be useable for queries on col1 or col2 only.
参考文献：
- http://use-the-index-luke.com/sql/where-clause/obfuscation/math
这篇关于两列中的索引在同一表格中进行比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
