PostgreSQL数十亿行表双列索引与条件索引性能差异及选型咨询

前置说明 #

PostgreSQL 不存在nonclustered（非聚集）索引的语法，该术语属于SQL Server，PG 中普通二级索引创建无需加该关键字。以下基于你给出的两种索引逻辑做对比。 #

性能差异 #

存储开销 #

• 方案二（多条件部分索引）：每个索引仅存储对应type值的string_urn_id记录，单索引体积极小，总存储量仅为所有索引对应数据的string_urn_id字段占用空间之和。如果存在数据量极小的type，对应索引几乎不占用存储空间。
• 方案一（双列联合索引）：需要存储全表所有行的(type, string_urn_id)组合，总存储体积比方案二高30%~150%，额外开销来源于重复存储所有行的type字段值。

查询性能 #

• 固定带type = 固定值过滤的查询：方案二性能优于方案一15%~60%，因为部分索引体积更小，B树层级更少，相同缓存命中率下IO开销更低。
• 不带type过滤、跨多个type查询的场景：方案一可以直接走单次索引扫描，性能是方案二的3~10倍，方案二需要扫描多个独立的部分索引，开销随type数量上升线性增长。

维护开销 #

• 写入/更新/删除操作：方案一只需要维护1个索引，写入性能更稳定。方案二需要同时修改对应type的部分索引，当type数量超过10个时，写入吞吐量会比方案一低30%以上。
• 迭代成本：如果后续新增type枚举值，方案二需要手动创建新的部分索引才能覆盖新类型，方案一无须做任何变更。

适用场景 #

优先选择方案二的场景 #

• 所有业务查询100%都会携带type = 固定值的过滤条件，无跨type、无裸string_urn_id查询需求
• type的枚举值数量少（建议不超过10个），且不会频繁新增枚举值
• 不同type的数据量差异极大，比如部分type仅几千行、部分type过亿行，部分索引可以极大减少小数据量type的索引空间浪费

优先选择方案一的场景 #

• 存在不带type过滤、或者需要同时查询多个type的业务需求
• type的枚举值数量超过20个，或者会频繁新增type值，维护大量部分索引的运维成本过高
• 表的写入吞吐量要求极高，不想因为多索引维护拖慢写入性能

补充说明：你提到表已经通过type字段做了分区，这种分区表场景下，可以直接在每个分区上单独创建string_urn_id的单列索引，效果和方案二的部分索引完全一致，还不需要额外加WHERE过滤条件。

内容的提问来源于stack exchange，提问作者M4rk