database - 组合索引的Postgres顺序

Question

我有一个包含1000万行数据的表

CREATE TABLE log_info
(
  id serial NOT NULL,
  created_date date, # date in month - max 30-31 distinct value
  dept_id integer, # max 50 distinct value
  group_id integer, # 10000 distinct value
  .......
)

大多数查询都是基于created_date、dept_id和group_id，所以我想为3个字段创建组合索引

我知道组合索引的顺序会影响数据库性能，所以在我的例子中，哪个是最好的索引？

CREATE INDEX log_info_index1 ON log_info USING btree (created_date, dept_id, group_id);

或

CREATE INDEX log_info_index1 ON log_info USING btree (created_date, group_id, dept_id);

Answer 1

哪种顺序最好取决于您计划运行哪种查询。考虑以下示例:

WHERE created_date=? AND dept_id=?
WHERE created_date=? AND dept_id>=?
WHERE created_date=? AND dept_id=? AND group_id BETWEEN ? AND ?

对于所有这些，索引 (created_date, dept_id, group_id)可以使用，而索引(created_date, group_id, dept_id)不能。一般来说，如果你在 (a,b,c) 上有一个索引那么它可以用于以下情况

a=?
a=? AND b=?
a=? AND b=? AND c=?
a=? AND {comparison involving b}
a=? AND b=? AND {comparison involving c}

其中比较表示 < 之一, <= , between .

如果您知道您永远不会使用比较，那么您应该将具有最多值的列放在最前面。

因此，对于结合上述 2 条规则的示例，假设您有列 a , b , 和 c .此外，假设 b只能取 5 个不同的值(比如 1 到 5)，而 a可以取 5 个以上的值。 Funally，假设你只想用类似的东西运行查询

a=? AND b=? AND c>=?

那么你应该输入c最后(因为比较)和a之前b因为a有更多的值(value)。因此，您必须使用以下顺序:(a,b,c) .