sql - 在 Postgres 中创建索引的正确列顺序

Question

考虑表 Address，它有两个 text 列，City 和 State。我将执行以下查询:

select * from address where city = $1 and state = $2;

为了能够通过索引进行搜索，我将创建一个索引:

create index address_city_state on address(city,state);

问题是，在创建索引时设置列的顺序，首先是城市而不是州，对查询性能或任何其他方式的影响，考虑到城市值的变化大于州值？

Answer 1

您通常希望将限制性更强的列放在索引的第一位。也就是说，如果 city 的基数高于 state，那么您将使用 (city, state)。这样做的原因是，理论上，它会允许 Postgres 在触及索引中的第二列之前先过滤掉许多不相关的记录。

但是，您的查询有问题。您正在执行 select *，这意味着您的索引很可能必须覆盖 select 子句中的所有列，以便 Postgres 决定使用它。例如，如果您的 address 表只有另外两列，那么您可以使用此索引:

(city, state, col1, col2)

上面的索引据说覆盖第三和第四列col1和col2，因为这意味着索引本身包含所有完成原始查询所需的信息。如果您尝试 select * 并且您的表有许多其他列，那么 Postgres 可能不会使用您建议的索引。