PostgreSQL查询性能优化：非主键查询场景下的分区策略选型

问题根源分析 #

• 你之前基于主键id_field做哈希分区，但实际查询几乎不涉及该字段，导致分区无法发挥**数据过滤（pruning）**的作用——查询时还是会扫描所有分区，和非分区表性能自然没区别。
• 10-12k的小数据量下，分区表的管理开销（比如元数据查询、分区扫描调度）会抵消分区带来的收益，只有当数据量达到百万级以上时，分区的优势才会显著体现。

适合的分区策略推荐 #

根据你的查询模式（依赖id_1、some_string_field、some_descriptor_field、some_enum_field的组合查询），推荐以下两种分区策略：

1. 列表分区（LIST Partitioning） #

如果你的查询中频繁用到枚举类字段（比如some_enum_field）或者有固定取值集合的字段（比如some_descriptor_field的取值是有限的分类），列表分区是最优选择：

• 按some_enum_field分区：每个枚举值对应一个分区，查询时PostgreSQL可以直接定位到目标分区，避免扫描无关数据。
• 示例SQL：

-- 创建分区表
CREATE TABLE your_table (
    id_field char PRIMARY KEY,
    id_1 char,
    id_2 char,
    some_string_field text,
    some_descriptor_field char,
    some_enum_field char
) PARTITION BY LIST (some_enum_field);

-- 创建分区
CREATE TABLE your_table_enum_val1 PARTITION OF your_table FOR VALUES IN ('VAL1');
CREATE TABLE your_table_enum_val2 PARTITION OF your_table FOR VALUES IN ('VAL2');
-- 其他枚举值分区以此类推

• 补充：如果常用查询是some_descriptor_field+some_enum_field的组合，可以考虑复合列表分区（PostgreSQL 11+支持），先按some_descriptor_field分区，再按some_enum_field子分区。

2. 范围分区（RANGE Partitioning） #

如果你的查询中用到的字段有有序取值范围（比如id_1是有规律的字符串，或者some_string_field可以按前缀/长度范围划分），可以用范围分区：

• 比如按id_1的字符串范围分区，或者对some_string_field按前缀截取后分区：

-- 按id_1的字符串范围分区示例
CREATE TABLE your_table (
    id_field char PRIMARY KEY,
    id_1 char,
    id_2 char,
    some_string_field text,
    some_descriptor_field char,
    some_enum_field char
) PARTITION BY RANGE (id_1);

-- 创建分区
CREATE TABLE your_table_id1_a_m PARTITION OF your_table FOR VALUES FROM ('A') TO ('N');
CREATE TABLE your_table_id1_n_z PARTITION OF your_table FOR VALUES FROM ('N') TO ('Z');

• 注意：范围分区对字符串的划分是基于字典序的，需要确保分区规则和你的查询范围匹配。

配套索引优化建议 #

分区策略要配合合适的索引才能最大化性能：

• 针对常用的查询组合创建联合索引，比如：
  • 对于id_1 + some_string_field的查询：CREATE INDEX idx_id1_string ON your_table (id_1, some_string_field);
  • 对于some_string_field + some_descriptor_field + some_enum_field的查询：CREATE INDEX idx_string_desc_enum ON your_table (some_string_field, some_descriptor_field, some_enum_field);
• 分区表上的索引默认是分区本地索引（每个分区有自己的索引），相比全局索引，它的维护成本更低，查询时也能配合分区过滤只扫描目标分区的索引。

额外注意事项 #

• 先确认数据量：当数据量在10k级别时，优先优化索引而非分区——合理的联合索引足以让查询性能达标，分区的收益要到数据量增长后才会显现。
• 测试分区过滤效果：可以用EXPLAIN ANALYZE查看查询计划，确认PostgreSQL是否正确执行了分区裁剪（Partition Pruning），如果没有，检查查询条件是否和分区键匹配。

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