sql - 优化使用索引进行聚合查询

Question

我的 Postgres 表看起来像这样:

tran_id SERIAL PRIMARY KEY,
acct_id int NOT NULL,
tran_type char(2) NOT NULL,
/* some performance irrelevant fields */

应用程序将运行的唯一多行查询如下:

SELECT acct_id, COUNT(tran_id) FROM table WHERE tran_type = 'X' GROUP BY acct_id;

所以我总是只想提取具有特定交易类型的帐户列表并获取该类型的交易数量。考虑到这一点，我最好的索引选择是什么:

1. acct_id 索引和 tran_type 索引
2. acct_id 和 tran_type 的综合索引
3. 1. 和 2. 组合(总共 3 个索引)
4. 还有别的吗？

Answer 1

第 2 点。但是列的顺序 是必不可少的。做到:

CREATE INDEX tbl_mult_idx ON tbl (tran_type, acct_id)

此外，使用 integer 列(或 enum )而不是 char(2) 作为类型会更有效。

如果 tran_id 定义为 NOT NULL，则使用 count(*) 而不是 count(tran_id)。速度有点快。

更多详情请参见 this related answer on dba.SE