方案:
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
#2 楼
如您所说,如果涉及这两个列的查询都是对两个列的相等性检查,例如:WHERE c1=@ParameterA AND c2=@ParameterB
则不必为此烦恼。我怀疑会不会有任何差异,如果有差异,那可以忽略不计。当然,您始终可以使用数据和服务器设置进行测试。不同版本的DBMS在优化方面的行为可能略有不同。
索引内的顺序对于其他类型的查询将很重要,这些查询仅检查一列,或者检查不等式条件,或者检查一列的条件和
如果要选择两个顺序之一,则可以选择选择性较低的列。考虑具有
year
和month
列的表。您更可能需要WHERE year = 2000
条件或WHERE year BETWEEN 2000 AND 2013
或WHERE (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)
索引。
评论
索引中的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