强制索引扫描以进行多列比较 [英] Enforcing index scan for multicolumn comparison
问题描述
我知道内部索引是B树或类似的树结构。
假设索引是为3列（a，b，c）构建的，我希望Postgres执行以下操作：
I know that index internally is B-tree or similar tree structure.
Suppose index is built for 3 columns (a,b,c), I want Postgres to:
- 在该B树中找到键[a = 10，b = 20，c = 30]，
- 扫描下10个条目并返回它们。 / li>
- find key [a=10, b=20, c=30] in that B-tree,
- scan next 10 entries and return them.
如果索引只有一列，则显而易见的解决方案是：
If the index has only one column the solution is obvious:
select * from table1
where a >= 10
order by a limit 10
但是，如果有更多的列，解决方案将变得更加复杂。对于2列：
But if there are more columns the solution becomes much more complex. For 2 columns:
select * from table1
where a > 10 or (a = 10 and b >= 20)
order by a, b limit 10
3列：
select * from table1
where a > 10 or (a = 10 and (b > 20 or b = 20 and c >= 30))
order by a, b, c limit 10
请注意查询：
select * from table1
where a >= 10 and b >= 20 and c >= 30
order by a, b, c limit 10
是不正确，因为它会过滤掉例如[a = 11，b = 10，c = 1]。
is incorrect, since it will filter out for example [a = 11, b = 10, c=1].
如何告诉Postgres我要执行此操作？
我可以确定即使对于2列以上的复杂查询，优化器也将始终了解他应该执行范围扫描？为什么？
推荐答案
使用 ROW值进行比较：
SELECT *
FROM table1
WHERE (a,b,c) >= (10, 20, 30)
ORDER BY a,b,c
LIMIT 10;
（使用> = 来匹配您的代码，尽管您的描述建议使用> 。都可以。）
(Using >= to match your code, though your description suggests >. Either works.)
（a， b，c）实际上是 ROW（a，b，c）的缩写。
是的，Postgres理解它可以使用匹配的多列B-树索引（与其他RDBMS不同-或我听说过）。
And yes, Postgres understands that it can use a matching multicolumn B-tree index for this (unlike some other RDBMS - or so I have heard).
匹配 表示所有索引表达式，它们的顺序和关联的顺序（ ASC | DESC ）是相同的-或整体的排序顺序索引行完全倒置，因此Postgres可以以几乎相同的速度向后扫描索引。
对于给定的示例，这些索引匹配：
"Matching" means that all index expressions, their sequence and the associated order (ASC|DESC) are the same - or the sort order of the whole index row is perfectly inverted, so that Postgres can scan the index backwards at almost the same speed.
For the given example these indexes match:
(a ASC, b ASC, c ASC)
(a DESC, b DESC, c DESC)
但是这些不是：
(a ASC, b DESC, c ASC)
(a ASC, c ASC, b ASC)
优化一系列时间戳（两列）上的查询
Optimizing queries on a range of timestamps (two columns)
相关，并提供更多说明：
Related, with more explanation:
- SQL syntax term for 'WHERE (col1, col2) < (val1, val2)'
- Optimizing queries on a range of timestamps (two columns)
这篇关于强制索引扫描以进行多列比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
