我有一个带有多列索引的表,并且我怀疑索引的正确排序以使查询获得最大性能。

方案:


PostgreSQL 8.4,具有大约一百万行的表
c1列中的值可以有大约100个不同的值。我们可以假设这些值是均匀分布的,因此每个可能的值都有大约10000行。
列c2可以有1000个不同的值。每个可能的值都有1000行。

搜索数据时,条件始终包含这两列的值,因此该表具有组合c1和c2的多列索引。如果您只使用一个列进行过滤的查询,我已经读过正确排序多列索引中的列的重要性。在我们的方案中情况并非如此。

我的问题是这个:

鉴于其中一个过滤器选择的数据集要小得多,我可以改善这一事实吗?如果第一个索引是最有选择性的索引(允许较小索引的索引),则性能如何?直到看到引用文章中的图形,我才考虑过这个问题:



引用文献中有关多列索引的图像。

查询使用两列中的值进行过滤。我没有只使用一列进行过滤的查询。它们都是:WHERE c1=@ParameterA AND c2=@ParameterB。也有类似这样的情况:WHERE c1 = "abc" AND c2 LIKE "ab%"

#1 楼

回答

由于您访问了use-the-index-luke.com网站,因此请考虑以下章节:

使用索引,卢克›子句在哪里›搜索范围›更大,更小且之间
/>
有一个示例可以完美地匹配您的情况(两列索引,一个用于相等性测试,另一个用于范围测试),解释(使用更多此类漂亮的索引图形)解释@ypercube的建议为何准确且总结一下:

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


对仅一列也很有效吗?

对仅一列的查询该怎么做似乎很清楚。在以下相关问题下,与此相关的更多详细信息和基准:



在PostgreSQL中使用索引
复合索引是否也适合在第一个字段上进行查询?

首先减少选择列?

除此之外,如果两个列都只有相等条件怎么办?

没关系。请将该列放在最有可能接收其自身条件的列中,该列实际上很重要。

请考虑此演示或自己复制它。我创建了一个简单的表,其中包含两列,每行包含10万行。一个具有很少的值,另一个具有许多不同的值:

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输出(十个最佳值,以排除缓存影响):

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


评论


索引中的3个(或更多)列是否也是这种情况?

– Hayd
19-09-19在7:52

@ hayd:不确定“ this”是指什么。您可能会问一个新问题。您可以随时参考此内容。 (并在此处添加评论以链接回。)

–欧文·布兰德斯特(Erwin Brandstetter)
19年9月19日在15:43

“这个”的意思是“如果索引定义中有两列以上,那么索引定义的排序是否重要”

– Hayd
19年9月19日在17:33

@hayd:最重要的一点:btree索引适用于在前导索引表达式上具有相等条件的查询。其中的顺序基本上无关紧要。许多其他细节不适合发表评论...

–欧文·布兰德斯特(Erwin Brandstetter)
19-09-19在23:04

谢谢,我将尝试写一个连贯的问题并链接到它。

– Hayd
19-09-20在4:30

#2 楼

如您所说,如果涉及这两个列的查询都是对两个列的相等性检查,例如:

WHERE c1=@ParameterA AND c2=@ParameterB


则不必为此烦恼。我怀疑会不会有任何差异,如果有差异,那可以忽略不计。当然,您始终可以使用数据和服务器设置进行测试。不同版本的DBMS在优化方面的行为可能略有不同。

索引内的顺序对于其他类型的查询将很重要,这些查询仅检查一列,或者检查不等式条件,或者检查一列的条件和

如果要选择两个顺序之一,则可以选择选择性较低的列。考虑具有yearmonth列的表。您更可能需要WHERE year = 2000条件或WHERE year BETWEEN 2000 AND 2013WHERE (year, month) BETWEEN (1999, 6) AND (2000, 5)

可能会希望查询类型为WHERE month = 7 GROUP BY year的查询(查找7月出生的人),但这种情况会减少。当然,这取决于表中存储的实际数据。现在选择一个订单,例如(c1, c2),您以后可以随时添加另一个索引(c2, c1)


在OP评论后进行更新:


还有类似这样的条件:WHERE c1 = 'abc' AND c2 LIKE 'ab%'


这种查询类型是否恰好在c2列上的范围条件,并且需要(c1, c2)索引。如果您还有反向类型的查询:

WHERE c2 = 'abc' AND c1 LIKE 'ab%'


,那么也可以使用(c2, c1)索引。