同一PostgreSQL查询生成不同执行计划的原因排查

问题背景 #

将云端PostgreSQL 14.5数据库的SQL导出文件导入本地Docker Alpine PG 14.5环境后，同一查询在两地的执行计划与性能差异显著：本地执行耗时约15ms，云端（7.5GB内存、2核CPU）耗时约5000ms。users表数据量超100万条，websites列是带GIN索引的JSONB类型，两地数据与表结构完全一致。

执行查询语句 #

EXPLAIN SELECT "users".* FROM "users" WHERE "users"."group_id" = 1 AND (websites @> '[{"url":"test.com/route"}]' OR websites @> '[{"url":"site.com/place"}]' OR websites @> '[{"url":"other.com/location"}]') ORDER BY "users"."id" ASC LIMIT 1

本地执行计划 #

Limit  (cost=1314.34..1314.34 rows=1 width=1188)
  ->  Sort  (cost=1314.34..1315.10 rows=306 width=1188)
        Sort Key: id
        ->  Bitmap Heap Scan on users  (cost=122.53..1312.81 rows=306 width=1188)
              Recheck Cond: ((profiles @> '[{"url": "test.com/route"}]'::jsonb) OR (profiles @> '[{"url": "site.com/place"}]'::jsonb) OR (profiles @> '[{"url": "other.com/location"}]'::jsonb))
              Filter: (group_id = 1)
              ->  BitmapOr  (cost=122.53..122.53 rows=306 width=0)
                    ->  Bitmap Index Scan on index_users_on_profiles  (cost=0.00..40.77 rows=102 width=0)
                          Index Cond: (profiles @> '[{"url": "test.com/route"}]'::jsonb)
                    ->  Bitmap Index Scan on index_users_on_profiles  (cost=0.00..40.77 rows=102 width=0)
                          Index Cond: (profiles @> '[{"url": "site.com/place"}]'::jsonb)
                    ->  Bitmap Index Scan on index_users_on_profiles  (cost=0.00..40.77 rows=102 width=0)
                          Index Cond: (profiles @> '[{"url": "linkedin.com/in/haidar-khan-62a27251"}]'::jsonb)

云端执行计划 #

Limit  (cost=0.43..3009.22 rows=1 width=1082)
   ->  Index Scan using users_pkey on users  (cost=0.43..914673.14 rows=304 width=1082)
         Filter: ((group_id = '1'::bigint) AND ((websites @> '[{"url": "test.com/route"}]'::jsonb) OR (websites @> '[{"url": "site.com/place"}]'::jsonb) OR (websites @> '[{"url": "other.com/location"}]'::jsonb)))

导致执行策略差异的关键因素 #

• 统计信息偏差：PostgreSQL优化器依赖表和索引的统计信息估算执行成本。云端环境的统计信息可能未及时更新，或统计精度不足，导致优化器误判GIN索引的过滤效率，转而选择主键索引扫描+过滤的方案。执行ANALYZE users;更新统计信息后可重新测试。
• 内存参数配置差异：本地Docker环境可能分配了充足内存，work_mem、shared_buffers等参数设置更合理，能高效处理Bitmap Heap Scan的内存操作；云端内存参数可能偏低，优化器认为GIN索引+Bitmap扫描的内存成本过高，因此选择看似成本更低的主键索引扫描。
• 硬件性能与成本参数不匹配：云端CPU的主频、缓存性能可能弱于本地机器，或存储IO延迟较高。优化器依据cpu_tuple_cost、random_page_cost等参数估算代价，若云端IO成本设置过高，会倾向于避免需要大量随机IO的Bitmap Heap Scan，转而选择主键索引扫描（实际耗时更长）。
• 索引状态异常：云端的GIN索引可能存在碎片或未正确构建，导致索引扫描实际性能不佳，优化器评估时认为索引成本过高。执行REINDEX INDEX index_users_on_profiles;重建索引后可验证。
• 执行计划开关参数差异：云端可能禁用了Bitmap扫描（如enable_bitmapscan设为off），或effective_cache_size设置过低，优化器认为系统缓存不足以支撑GIN索引高效访问，从而选择保守的执行计划。

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