PostgreSQL分区表无分区键过滤查询性能差 如何优化？

• 修正索引配置
  你当前创建的索引是针对url列的，但查询过滤条件为info列，该索引完全无法命中，是性能差的核心原因之一。建议创建info列的分区索引，考虑到info是最大长度4096的字符串，可以创建前缀索引降低索引体积、提升检索效率：

  -- CONCURRENTLY参数避免建索引时锁表，不影响线上业务
  CREATE INDEX CONCURRENTLY idx_partitioned_table_info_prefix ON partitioned_table (substring(info FROM 1 FOR 256));
  

  若业务以等值查询为主，也可以额外存储info的哈希值作为冗余列，对哈希值建索引，查询时同时匹配哈希值和info原值，索引体积会更小、检索速度更快。无用的url列索引可以直接删除，降低写入开销。
• 优化分区修剪逻辑
  首先确认数据库参数constraint_exclusion设置为partition（默认值，建议不要修改为off）、enable_partition_pruning设置为on，确保数据库可以根据查询条件尽可能裁剪不需要扫描的分区。
  若经常需要执行不带时间范围的info查询，可将现有一级范围分区改造为二级分区：一级按scan_start_time做范围分区，二级按info的哈希值做哈希分区，查询时可自动裁剪掉大量哈希不匹配的二级分区，大幅减少需要扫描的分区数量。
• 降低锁竞争开销
  你观测到lock_manager消耗高，本质是每次查询需要扫描全部分区、获取每个分区的共享锁，分区数量大时锁开销和锁冲突都会急剧升高。可通过以下方式优化：
  1. 对冷分区做归档处理：将超过业务保留期的历史分区设置为只读，或直接迁移到离线存储，减少日常查询需要扫描的分区总数
  2. 封装默认时间范围视图：创建默认带近期时间过滤的视图，例如CREATE VIEW v_partitioned_table AS SELECT * FROM partitioned_table WHERE scan_start_time >= NOW() - INTERVAL '180 days'，业务侧默认查询视图，仅需要查历史数据时再自定义时间范围
  3. 大查询拆分执行：若必须查询全量历史数据，可将查询按时间范围拆分为多个小查询分批执行，每次仅扫描少量分区，降低锁持有数量和冲突概率
• 调整执行策略配置
  若使用PostgreSQL 12及以上版本，可适当调大max_parallel_workers_per_gather参数，允许分区表查询启动多个并行worker同时扫描不同分区，提升扫描效率。对只读类查询，可显式开启只读事务模式，降低锁的开销。

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