PostgreSQL关联子查询执行异常缓慢的原因排查与执行计划分析

先帮你拆解下当前查询的性能瓶颈，再给出具体的优化方案：

问题根源分析 #

先看你的原始查询和核心执行计划细节：

原始查询 #

SELECT *, 
(
    SELECT COUNT(messaging_channelmembership.id) 
    FROM messaging_channelmembership 
    INNER JOIN messaging_member ON (messaging_channelmembership.member_id = messaging_member.id) 
    INNER JOIN messaging_user ON (messaging_member.user_id = messaging_user.id) 
    WHERE (
        messaging_channelmembership.channel_id = messaging_message.channel_id 
        AND messaging_channelmembership.last_read_message < messaging_message.id 
        AND messaging_user.is_bot = false 
        AND messaging_user.username != 'puddlrbot' 
        AND messaging_channelmembership.member_id != messaging_message.sender_id
    ) 
) as unread_count 
FROM "messaging_message" 
WHERE ("messaging_message"."channel_id" = $1) AND (messaging_message.id >= $2) 
ORDER BY messaging_message.created LIMIT 30;

关键执行计划问题点 #

Limit  (cost=169.00..169.00 rows=1 width=101) (actual time=218.212..218.218 rows=30 loops=1)
  ...
SubPlan 1
  ->  Aggregate  (cost=59.81..59.82 rows=1 width=4) (actual time=0.588..0.588 rows=1 loops=369)
        ->  Nested Loop  (cost=1.27..59.80 rows=3 width=4) (actual time=0.008..0.578 rows=87 loops=369)
              ...

从执行计划能一眼看到核心问题：

• 主查询返回了369条messaging_message记录，这个子查询会对每条消息单独执行一次（loops=369），相当于重复做了369次关联统计操作。单次子查询耗时0.588ms，累计起来就占了几乎全部的218ms总执行时间。
• 虽然子查询用到了索引，但多次执行的叠加开销还是拉垮了整体性能。

优化方案 #

方案1：把相关子查询改写为JOIN + GROUP BY，避免逐行循环 #

最有效的优化是将原来的列相关子查询改成一次性的JOIN统计，让数据库只做一次关联计算，而不是369次重复劳动。改写后的查询如下：

SELECT 
    mm.*,
    COALESCE(COUNT(mcm.id), 0) AS unread_count
FROM messaging_message mm
LEFT JOIN messaging_channelmembership mcm 
    ON mcm.channel_id = mm.channel_id 
    AND mcm.last_read_message < mm.id 
    AND mcm.member_id != mm.sender_id
INNER JOIN messaging_member mmb 
    ON mcm.member_id = mmb.id
INNER JOIN messaging_user mu 
    ON mmb.user_id = mu.id
    AND mu.is_bot = false 
    AND mu.username != 'puddlrbot'
WHERE mm.channel_id = $1 
  AND mm.id >= $2
GROUP BY mm.id, mm.created /* PostgreSQL 10+版本可直接用GROUP BY mm.id（假设id是主键），自动包含其他字段 */
ORDER BY mm.created 
LIMIT 30;

方案2：添加针对性复合索引，加速关联查询 #

为子查询的过滤和关联路径创建覆盖索引，进一步降低查询开销：

1. 给messaging_channelmembership创建覆盖索引，直接满足过滤和关联需求：

CREATE INDEX idx_mcm_channel_lastread_member ON messaging_channelmembership (channel_id, last_read_message, member_id);

2. 给messaging_user创建针对bot和用户名过滤的索引，避免回表：

CREATE INDEX idx_mu_isbot_username_id ON messaging_user (is_bot, username, id);

方案3：优化主查询的排序开销 #

执行计划里用到了top-N heapsort处理排序，虽然当前内存开销不大，但可以通过复合索引彻底避免排序操作：

CREATE INDEX idx_mm_channel_id_created ON messaging_message (channel_id, id, created);

这个索引能让数据库直接按created顺序获取符合条件的数据，不需要额外排序步骤。

优化预期 #

改写为JOIN后，原来369次的子查询循环会变成一次批量统计，预计总执行时间会降到几毫秒级别，配合索引优化能进一步稳定查询性能。

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