多租户场景下关联查询执行计划差异导致SQL性能异常排查

环境配置 #

关联模型（多态）：

# Table name: util_participants
#
#  id                  :bigint           not null, primary key
#  assignable_type     :string           not null
#  assignable_id       :bigint           not null
#  contact_id          :bigint
#
# Indexes
#
#  assignable_index        (assignable_id,assignable_type)
#
# Foreign Keys
#
#  fk_rails_...  (contact_id => client_contacts.id)

该表用于将联系人分配到不同元素（如documentation这类assignable元素）。

慢查询详情 #

执行documentation.contacts时生成的SQL为：

SELECT COUNT(*) FROM "client_contacts" INNER JOIN "util_participants" ON "client_contacts"."id" = "util_participants"."contact_id" WHERE "client_contacts"."account_id" = $1 AND "util_participants"."assignable_id" = $2 AND "util_participants"."assignable_type" = $3 

account用于实现多租户，针对特定元素，某一account_id的查询耗时达7秒，但切换到其他account_id时查询速度很快。

示例：
慢查询：

SELECT count(*) 
 FROM client_contacts 
 INNER JOIN util_participants 
 ON client_contacts.id = util_participants.contact_id 
 WHERE client_contacts.account_id = 35 
    AND util_participants.assignable_id = 1;

快查询（account_id=27的联系人数据量更多）：

SELECT count(*) 
FROM client_contacts 
INNER JOIN util_participants 
ON client_contacts.id = util_participants.contact_id 
WHERE client_contacts.account_id = 27 
  AND util_participants.assignable_id = 1;

PostgreSQL执行计划分析 #

慢查询的EXPLAIN结果：

Aggregate  (cost=3688.79..3688.80 rows=1 width=8)
   ->  Nested Loop  (cost=0.28..3688.79 rows=1 width=0)
       Join Filter: (client_contacts.id = util_participants.contact_id)
       ->  Index Scan using index_client_contacts_on_account_id_and_client_id on client_contacts  (cost=0.28..8.30 rows=1 width=8)
             Index Cond: (account_id = 35)
       ->  Seq Scan on util_participants  (cost=0.00..3680.48 rows=1 width=8)
           Filter: (assignable_id = 1)
(7 rows)

总数据量：util_participants表13万行，client_contacts表6500行。

更新补充：带ANALYZE的执行计划 #

慢查询（account_id=35）的EXPLAIN(ANALYZE, VERBOSE, BUFFERS)结果 #

EXPLAIN(ANALYZE, VERBOSE, BUFFERS) SELECT count(*) FROM client_contacts INNER JOIN util_participants ON client_contacts.id = util_participants.contact_id WHERE client_contacts.account_id = 35 AND util_participants.assignable_id = 1;

 Aggregate  (cost=3688.79..3688.80 rows=1 width=8) (actual time=6991.704..6991.706 rows=1 loops=1)
   Output: count(*)
   Buffers: shared hit=893735
   ->  Nested Loop  (cost=0.28..3688.79 rows=1 width=0) (actual time=6991.699..6991.700 rows=0 loops=1)
         Join Filter: (client_contacts.id = util_participants.contact_id)
         Rows Removed by Join Filter: 428
         Buffers: shared hit=893735
         ->  Index Scan using index_client_contacts_on_account_id_and_client_id on public.client_contacts  (cost=0.28..8.30 rows=1 width=8) (actual time=0.015..1.160 rows=428 loops=1)
               Output: ....
               Index Cond: (client_contacts.account_id = 35)
               Buffers: shared hit=71
         ->  Seq Scan on public.util_participants (cost=0.00..3680.48 rows=1 width=8) (actual time=0.002..16.325 rows=1 loops=428)
               Output: ....
               Filter: (util_participants.assignable_id = 1)
               Rows Removed by Filter: 127268
               Buffers: shared hit=893664
 Planning Time: 0.183 ms
 Execution Time: 6991.741 ms

快查询（account_id=33）的EXPLAIN(ANALYZE, VERBOSE, BUFFERS)结果 #

EXPLAIN(ANALYZE, VERBOSE, BUFFERS) SELECT count(*) FROM client_contacts INNER JOIN util_participants ON client_contacts.id = util_participants.contact_id WHERE client_contacts.account_id = 33 AND util_participants.assignable_id = 1;

 Aggregate  (cost=3688.79..3688.80 rows=1 width=8) (actual time=16.882..16.884 rows=1 loops=1)
   Output: count(*)
   Buffers: shared hit=2088
   ->  Nested Loop  (cost=0.28..3688.78 rows=1 width=0) (actual time=16.876..16.878 rows=0 loops=1)
         Inner Unique: true
         Buffers: shared hit=2088
         ->  Seq Scan on public.util_participants  (cost=0.00..3680.48 rows=1 width=8) (actual time=0.007..16.873 rows=1 loops=1)
               Output: ...
               Filter: (util_participants.assignable_id = 1)
               Rows Removed by Filter: 127268
               Buffers: shared hit=2088
         ->  Index Scan using client_contacts_pkey on public.client_contacts  (cost=0.28..8.30 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=1)
               Output: ...
               Index Cond: (client_contacts.id = util_participants.contact_id)
               Filter: (client_contacts.account_id = 33)
 Planning Time: 0.176 ms
 Execution Time: 16.923 ms

问题 #

为何几乎相同的查询会产生不同的执行计划？

解答 #

核心原因：统计信息偏差导致执行计划选择错误 #

PostgreSQL查询优化器依赖表的统计信息估算执行成本，进而选择最优计划。从执行计划能看出：

1. 慢查询的行数估计严重失准：优化器认为account_id=35的client_contacts仅1行，但实际返回了428行。基于这个错误估计，优化器选择以client_contacts为驱动表的嵌套循环：先查428个联系人，再对每个联系人全表扫描util_participants（共428次全表扫描，每次扫描13万行），IO量暴增导致耗时7秒。
2. 快查询的执行计划更合理：优化器选择以util_participants为驱动表，先扫描出符合assignable_id=1的1行数据，再通过主键索引查询client_contacts是否匹配account_id=33，仅需1次全表扫描+1次索引查询，耗时仅16毫秒。

统计信息偏差的可能诱因：

• client_contacts表的统计信息过期，未反映account_id=35的实际行数。
• 数据分布不均：account_id=35的联系人数量远高于其他account，优化器默认统计采样未覆盖该分布。

解决方案 #

1. 更新表统计信息：执行ANALYZE client_contacts;，让优化器获取最新的行数和数据分布，重新计算执行计划。
2. 添加针对性索引：
   • 给util_participants添加(contact_id, assignable_id, assignable_type)复合索引，避免全表扫描。
   • 给client_contacts添加(account_id, id)复合索引，优化驱动表查询效率。
3. 临时强制执行计划：若统计信息更新后仍有问题，可使用/*+ Leading(util_participants client_contacts) */查询提示强制指定驱动表顺序，但不建议长期依赖，优先修复统计信息和索引。

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