PostgreSQL 14.5：是否需创建与现有索引重叠的tenant_id单独索引？

背景与问题 #

我正在使用PostgreSQL 14.5，创建了如下多租户表及索引：

DROP TABLE IF EXISTS contract_detail_revenue;
CREATE TABLE contract_detail_revenue
(
    id                    smallint,
    contract_id           smallint       not null,
    cancel_revenue_date   date,
    revenue_type          smallint       not null,
    employee_id           smallint,
    organization_unit_id  smallint,
    inventory_item_id     smallint,
    rate                  numeric(16, 4) not null,
    revenue_amount        numeric(16, 4) not null,
    cancel_revenue_amount numeric(16, 4) not null,
    description           character varying(512),
    sort_order            smallint       not null,
    tenant_id             smallint,
    PRIMARY KEY (id, tenant_id)
);

CREATE INDEX contract_detail_revenue_idx ON contract_detail_revenue (id, tenant_id);
COMMIT;

由于是多租户系统，我会频繁执行类似如下的查询：

SELECT * FROM contract_detail_revenue WHERE tenant_id = 42;

请问是否需要额外创建如下单独索引？

CREATE INDEX contract_detail_revenue_idx2 ON contract_detail_revenue (tenant_id);

解答 #

• 清理冗余索引
  你手动创建的contract_detail_revenue_idx完全多余——PostgreSQL会自动为主键(id, tenant_id)创建唯一索引，这个手动索引和主键索引功能完全重复，建议直接删除它，避免浪费存储和维护资源。

• 单独tenant_id索引的必要性
  现有主键索引(id, tenant_id)的字段顺序是id在前、tenant_id在后，根据PostgreSQL复合索引的前缀匹配规则，这个索引只能高效支持包含id的查询（比如WHERE id = 1 AND tenant_id = 42），无法有效适配仅按tenant_id过滤的查询。当你执行WHERE tenant_id = 42时，数据库只能扫描整个主键索引，效率和全表扫描相差无几。

  因此，需要创建单独的tenant_id索引；如果你的业务中有更常见的组合查询（比如经常同时按tenant_id和contract_id过滤），也可以考虑创建以tenant_id为前缀的复合索引（如(tenant_id, contract_id)），这类索引既能支持单独的tenant_id查询，也能适配组合查询，实用性更强。

• 额外优化建议
  如果你的多租户业务中还有其他基于tenant_id的操作（比如关联查询、排序、分组），以tenant_id为核心的索引会大幅提升这类操作的性能，这也是多租户数据库优化的常规手段。

内容的提问来源于stack exchange，提问作者Đỗ Như Vỹ