postgresql - Postgres - 多列索引 - 前导(最左边)列

Question

我很好奇多列索引的前导列的概念。

我正在使用这个示例 dvdrental分贝。

这是查询:

SELECT
  title,
  length,
  rating,
  replacement_cost,
  rental_rate
FROM film
WHERE length BETWEEN 60 AND 70
  AND rating = 'G';

我有两个正在使用的索引:

#1

CREATE INDEX IF NOT EXISTS film_idx_length_rating
  ON film(length, rating);

#2

CREATE INDEX IF NOT EXISTS film_idx_rating_length
  ON film(rating, length);

在创建两个索引后，计划者选择索引进行计划:

QUERY PLAN
Bitmap Heap Scan on film  (cost=4.47..39.34 rows=15 width=34) (actual time=0.020..0.033 rows=18 loops=1)
  Recheck Cond: ((rating = 'G'::mpaa_rating) AND (length >= 60) AND (length <= 70))
  Heap Blocks: exact=14
  ->  Bitmap Index Scan on film_idx_rating_length  (cost=0.00..4.46 rows=15 width=0) (actual time=0.015..0.015 rows=18 loops=1)
        Index Cond: ((rating = 'G'::mpaa_rating) AND (length >= 60) AND (length <= 70))
Planning Time: 0.202 ms
Execution Time: 0.065 ms

执行了查询计划EXPLAIN ANALYZE，规划器选择了第二个查询，但是来自两个索引的查询计划实际上并没有任何显着差异，只有规划器选择的索引。这是为什么？为什么当 rating 用作前导列时，它被选中而不是 length 作为前导列？

来自docs有这个:

A multicolumn B-tree index can be used with query conditions that involve any subset of the index's columns, but the index is most efficient when there are constraints on the leading (leftmost) columns. The exact rule is that equality constraints on leading columns, plus any inequality constraints on the first column that does not have an equality constraint, will be used to limit the portion of the index that is scanned.

但是我不是很明白，也许有人可以给我举个例子？

谢谢!

Answer 1

在您开始看到有意义的差异之前，您可能需要使表格比该链接处的表格大几千倍。

对于以相等列开头的索引，可以跳转到索引内的'G'段，然后可以跳到60的长度，向前读，直到超过70，所有这些行都会满足两种资格。

但是对于另外一个索引，不能直接跳到60，然后跳到G段，因为没有单独的G段。 60 到 70 之间的每个不同值都有一个 G 部分。所以它最终要做的是扫描从 60 到 70 的所有行，单独过滤掉不是 G 的行。

事实证明差别并不大，因为大部分时间都花在了访问表堆以从表中获取所需的数据上，在这种情况下，需要为任一索引访问同一组行。