postgresql – 多列索引和性能

由于您参考网站use-the-index-luke.com,请考虑以下章节：

Use The Index,Luke ? The Where Clause ? Searching For Ranges ? Greater,Less and BETWEEN

它有一个完美匹配你的情况的例子(双列索引,一个测试相等,另一个测试范围),解释(更多那些漂亮的索引图形)为什么@ypercube’s advice是准确的并总结起来：

Rule of thumb: index for equality first — then for ranges.

只有一列好吗？

对于只有一列的查询,该做什么似乎很清楚.关于这些相关问题的更多细节和基准：

> Working of indexes in PostgreSQL
> Is a composite index also good for queries on the first field?

选择性较低的列首先？

除此之外,如果两个列只有相同的条件怎么办？

没关系.把列放在第一位,更有可能获得自己的条件,这实际上很重要.

考虑这个演示,或自己重现.我创建了一个包含100k行的两列简单表.一个很少,另一个有很多不同的值：

CREATE TEMP TABLE t AS
SELECT (random() * 10000)::int AS lots,(random() * 4)::int     AS few
FROM generate_series (1,100000);

DELETE FROM t WHERE random() > 0.9;  -- create some dead tuples,more "real-life"

ANALYZE t;

SELECT count(distinct lots)   -- 9999,count(distinct few)    --    5
FROM   t;

查询：

SELECT *
FROM   t
WHERE  lots = 2345
AND    few = 2;

EXPLAIN ANALYZE输出(排除缓存效果的最佳值为10)：

Seq Scan on t  (cost=0.00..5840.84 rows=2 width=8)
               (actual time=5.646..15.535 rows=2 loops=1)
  Filter: ((lots = 2345) AND (few = 2))
  Buffers: local hit=443
Total runtime: 15.557 ms

添加索引,重新测试：

CREATE INDEX t_lf_idx ON t(lots,few);

Index Scan using t_lf_idx on t  (cost=0.00..3.76 rows=2 width=8)
                                (actual time=0.008..0.011 rows=2 loops=1)
  Index Cond: ((lots = 2345) AND (few = 2))
  Buffers: local hit=4
Total runtime: 0.027 ms

添加其他索引,重新测试：

DROP INDEX t_lf_idx;
CREATE INDEX t_fl_idx  ON t(few,lots);

Index Scan using t_fl_idx on t  (cost=0.00..3.74 rows=2 width=8)
                                (actual time=0.007..0.011 rows=2 loops=1)
  Index Cond: ((few = 2) AND (lots = 2345))
  Buffers: local hit=4
Total runtime: 0.027 ms