sql - 为什么我们不能使用具有两个或多个独立范围条件的复合索引？

Question

use-the-index-luke.com说:

Nevertheless there are queries where a single index cannot do a perfect job, no matter how you define the index; e.g., queries with two or more independent range conditions as in the following example:

SELECT first_name, last_name, date_of_birth 
FROM employees 
WHERE UPPER(last_name) < ?
   AND date_of_birth < ?

It is impossible to define a B-tree index that would support this query without filter predicates.

特别是最后一句我不明白它的解释。有人可以帮忙吗？

Answer 1

那里的解释还不够好吗？

No matter how you twist and turn the index definition, the entries are always arranged along a chain. At one end, you have the small entries and at the other end the big ones. An index can therefore only support one range condition as an access predicate. Supporting two independent range conditions requires a second axis, for example like a chessboard. The query above would then match all entries from one corner of the chessboard, but an index is not like a chessboard—it is like a chain. There is no corner.