PostgreSQL大表分区后性能未提升，如何排查？

问题背景 #

我们在处理一张基于日期的超大交易表时，采用**partitioning（分区）**来提升性能，但未获得预期收益，反而性能更差。通过EXPLAIN ANALYZE分析执行计划后仍难以定位问题。

环境信息 #

• 系统：Ubuntu 64位，PostgreSQL 12.12
• 分区表详情：分区transactions_202210含40118032行，表大小9.9GB（含14GB索引）
• 未分区表详情：transactions_old预估含289618048行，表大小71GB（含91GB索引）

对比测试详情 #

分区表查询 #

查询语句：

explain analyze select * from transactions
where book_date >= '2022-10-01' and book_date < '2022-11-01'
and logical_account_id = 'some_uuid'
LIMIT 90000

耗时约9秒，执行计划：

Limit  (cost=0.56..350527.14 rows=90000 width=321) (actual time=117.259..8467.530 rows=90000 loops=1)
  ->  Append  (cost=0.56..380543.90 rows=97707 width=321) (actual time=4.469..8340.750 rows=90000 loops=1)
        ->  Index Scan using transactions_202209_logical_account_id_book_date_idx on transactions_202209  (cost=0.56..8.58 rows=1 width=319) (actual time=4.456..4.461 rows=1 loops=1)
              Index Cond: ((logical_account_id = 'some_uuid'::uuid) AND (book_date >= '2022-10-01 00:00:00+02'::timestamp with time zone) AND (book_date < '2022-11-01 00:00:00+01'::timestamp with time zone))
        ->  Index Scan using transactions_202210_logical_account_id_idx on transactions_202210  (cost=0.56..380046.78 rows=97706 width=321) (actual time=5.803..8322.708 rows=89999 loops=1)
              Index Cond: (logical_account_id = 'some_uuid'::uuid)
              Filter: ((book_date >= '2022-10-01 00:00:00+02'::timestamp with time zone) AND (book_date < '2022-11-01 00:00:00+01'::timestamp with time zone))
Planning Time: 46.393 ms
JIT:
  Functions: 7
  Options: Inlining false, Optimization false, Expressions true, Deforming true
  Timing: Generation 2.650 ms, Inlining 0.000 ms, Optimization 32.546 ms, Emission 77.547 ms, Total 112.743 ms
Execution Time: 8718.342 ms

未分区表查询 #

查询语句：

explain analyze select * from transactions_old
where book_date >= '2022-10-01' and book_date < '2022-11-01'
and logical_account_id = 'some_uuid'
LIMIT 90000

耗时约4秒，执行计划：

Limit  (cost=569.58..53494.83 rows=13569 width=315) (actual time=244.439..3720.348 rows=44038 loops=1)
  ->  Bitmap Heap Scan on transactions_old  (cost=569.58..53494.83 rows=13569 width=315) (actual time=244.437..3713.977 rows=44038 loops=1)
        Recheck Cond: ((logical_account_id = 'some_uuid'::uuid) AND (book_date >= '2022-10-01 00:00:00'::timestamp without time zone) AND (book_date < '2022-11-01 00:00:00'::timestamp without time zone))
        Heap Blocks: exact=5938
        ->  Bitmap Index Scan on ""IX_transactions_logical_account_id_book_date""  (cost=0.00..566.18 rows=13569 width=0) (actual time=241.483..241.484 rows=44038 loops=1)
              Index Cond: ((logical_account_id = 'some_uuid'::uuid) AND (book_date >= '2022-10-01 00:00:00'::timestamp without time zone) AND (book_date < '2022-11-01 00:00:00'::timestamp without time zone))
Planning Time: 18.052 ms
Execution Time: 3724.277 ms

排查思路 #

从执行计划和环境信息来看，分区表已正确路由到目标分区，但性能差距明显，可从以下方向逐一排查：

1. 索引结构差异

   • 未分区表使用(logical_account_id, book_date)复合索引，能直接通过索引过滤两个条件；而分区transactions_202210仅用单字段索引logical_account_id，需先筛选账号再回表过滤日期，产生大量额外IO。
   • 处理：为每个分区创建与未分区表一致的复合索引(logical_account_id, book_date)，避免回表过滤。

2. 数据类型不一致

   • 分区表book_date是带时区的timestamp with time zone，未分区表是无时区的timestamp without time zone，类型隐式转换会降低索引效率，还会干扰统计信息准确性。
   • 处理：统一book_date的数据类型，确保分区表与未分区表类型一致。

3. 统计信息准确性

   • 执行计划显示分区表预估行数与实际偏差较大，未分区表也存在类似问题，说明统计信息过时，导致优化器选择低效执行计划。
   • 处理：执行ANALYZE transactions_202210;更新分区统计信息，让优化器做出更精准决策。

4. 执行计划选择差异

   • 未分区表采用Bitmap Index Scan + Bitmap Heap Scan组合，能有效减少随机IO；分区表用Index Scan，返回大量行时随机IO开销更高。
   • 处理：临时开启enable_bitmapscan参数测试，或通过复合索引引导优化器选择位图扫描。

5. JIT编译影响

   • 分区表启用了JIT编译，虽单次耗时占比不高，但高并发场景下可能累积影响。
   • 处理：临时设置jit = off关闭JIT，测试性能变化。

6. 缓存命中率差异

   • 未分区表数据可能更多被缓存到内存，分区表缓存命中率低导致IO开销大。
   • 处理：查询pg_stat_user_tables和pg_buffercache查看缓存情况，验证缓存差异。

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