复杂SQL查询耗时增加，是否需要创建索引？

首先，先把你的查询贴出来方便分析：

explain select m.* from conversation m where m.from_tel <>'12030000000' and m.to_tel = '12030000000' AND m.insert_time > NOW() - INTERVAL '2 days' AND m.insert_time = (select max(insert_time) from conversation where match_id = m.match_id group by match_id) AND insert_time < NOW() - INTERVAL '70 seconds' AND ((select count(*) from conversation as sl where sl.match_id = m.match_id and sl.reply_batch = true) < 8)

这条查询耗时增加的核心原因是相关子查询的重复执行以及缺少合适的索引来过滤和关联数据，下面给你具体的优化方案：

一、必须创建的索引建议 #

1. 主查询过滤+关联复合索引 #

创建这个索引来覆盖主查询的核心过滤条件和子查询关联逻辑：

CREATE INDEX idx_conversation_to_tel_match_id_insert_time ON conversation (to_tel, match_id, insert_time);

为什么选这个组合？

• to_tel = '12030000000'是等值过滤，放在最前面可以快速缩小扫描范围，直接定位目标用户的所有对话
• 紧接着match_id，可以让相同match_id的行聚在一起，配合后面的insert_time，子查询里的MAX(insert_time)无需全表扫描，直接取每个match_id组的最后一条记录即可
• insert_time放在最后，既可以覆盖主查询里的时间范围过滤，又能快速获取最大值

2. 统计子查询专用索引 #

针对统计reply_batch=true数量的子查询，创建这个索引：

CREATE INDEX idx_conversation_match_id_reply_batch ON conversation (match_id, reply_batch);

这个索引的作用是：

• 按match_id分组后，能快速筛选出reply_batch=true的行，统计COUNT(*)时不需要回表查询原数据，直接通过索引就能完成计数

二、查询语句改写（可选但推荐） #

你的查询里用了两个相关子查询，这意味着主查询每找到一行符合条件的数据，就要执行两次子查询，数据量越大重复计算越多。可以改成CTE（公共表表达式）的形式，把子查询的计算一次性完成：

WITH max_insert AS (
    SELECT match_id, MAX(insert_time) AS max_time
    FROM conversation
    GROUP BY match_id
),
reply_count AS (
    SELECT match_id, COUNT(*) AS cnt
    FROM conversation
    WHERE reply_batch = true
    GROUP BY match_id
)
SELECT m.*
FROM conversation m
JOIN max_insert mi ON m.match_id = mi.match_id AND m.insert_time = mi.max_time
LEFT JOIN reply_count rc ON m.match_id = rc.match_id
WHERE m.to_tel = '12030000000'
  AND m.from_tel <> '12030000000'
  AND m.insert_time > NOW() - INTERVAL '2 days'
  AND m.insert_time < NOW() - INTERVAL '70 seconds'
  AND COALESCE(rc.cnt, 0) < 8;

这种写法把两个子查询的计算提前完成，只执行一次，然后通过JOIN关联主查询，能大幅减少重复计算的开销。

三、验证优化效果 #

创建索引和改写查询后，记得用EXPLAIN ANALYZE来查看执行计划，确认索引是否被正确使用。如果发现统计信息过时，可以运行ANALYZE conversation;来更新表的统计信息，让优化器生成更准确的执行计划。

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