索引扫描以进行多列比较-索引列的非均匀排序 [英] Index scan for multicolumn comparison - non-uniform index column ordering
问题描述
该问题与强制进行索引扫描以进行多列比较
这里的解决方案是完美的，但是似乎只有在所有索引列都具有相同排序的情况下才有效。这个问题是不同的，因为这里的b列是 desc ，并且这个事实不再使用行语法来解决相同的问题。这就是为什么我正在寻找其他解决方案。
The solution there is perfect, but seems to works only if all index columns have same ordering. This question is different because column b is desc here, and this fact stops from using row-syntax to solve the same problem. This is why I'm looking for another solution.
假设索引是为3列（a asc，b DESC，c asc），我希望Postgres：
Suppose index is built for 3 columns (a asc, b DESC, c asc), I want Postgres to:
- 在以下位置找到键[a = 10，b = 20，c = 30]那个B树
- 扫描下10个条目并返回它们。
如果索引仅有一个列，解决方案是显而易见的：
If the index has only one column the solution is obvious:
select * from table1 where a >= 10 order by a limit 10
但是，如果有更多列，解决方案将变得更加复杂。对于3列：
But if there are more columns the solution becomes much more complex. For 3 columns:
select * from table1
where a > 10 or (a = 10 and (b < 20 or b = 20 and c <= 30))
order by a, b DESC, c
limit 10;
如何告诉Postgres我想要此操作？
我是否可以确定，即使对于2+列的那些复杂查询，优化器也会始终理解他应该执行范围扫描？为什么？
推荐答案
PostgreSQL非常彻底地实现了元组（不同于Oracle，DB2，SQL Server中的一半实现，等等。）。您可以使用元组不等式来写条件，如：
PostgreSQL implements tuples very thoroughly, (unlike half implementations found in Oracle, DB2, SQL Server, etc.). You can write your condition using "tuples inequality", as in:
select *
from table1
where (a, -b, c) >= (10, -20, 30)
order by a, -b, c
limit 10
请注意，由于第二列按降序排列，因此在比较期间必须反转其值。这就是为什么将它表示为 -b 以及 -20 的原因。对于非数字列，例如日期，varchars，LOB等，这可能会很棘手。
Please note that since the second column is in descending order, you must "invert" its value during the comparison. That's why it's expressed as -b and also, -20. This can be tricky for non-numeric columns such as dates, varchars, LOBs, etc.
最后，对于<$ c $，仍可以使用索引c> -b 列值（如果创建临时索引），例如：
Finally, the use of an index is still possible with the -b column value if you create an ad-hoc index, such as:
create index ix1 on table1 (a, (-b), c);
但是，您绝不能强迫PostgreSQL使用索引。 SQL是一种声明性语言，而不是命令性语言。您可以通过保持表状态为最新状态并选择少量行来吸引。如果您的 LIMIT 太大，则PostgreSQL可能会倾向于使用全表扫描。
However, you can never force PostgreSQL to use an index. SQL is a declarative language, not an imperative one. You can entice it to do it by keeping table stats up to date, and also by selecting a small number of rows. If your LIMIT is too big, PostgreSQL may be inclined to use a full table scan instead.
这篇关于索引扫描以进行多列比较-索引列的非均匀排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
