PostgreSQL查询优化：避免Messages表的顺序扫描

你的查询存在两个核心问题：逻辑错误和索引未被有效利用，以下是针对性的解决思路：

一、先纠正查询逻辑错误 #

原查询先在子查询中无排序执行LIMIT 100，再对结果排序，这会返回随机100条符合条件的消息后排序，而非按CreatedAt排序的前100条消息。正确逻辑是先合并所有符合条件的记录，再排序取前100。

二、索引优化策略 #

针对OR条件，PostgreSQL优化器很难用单个索引覆盖两个分支，以下是可行的索引方案：

1. 创建覆盖式复合索引（针对每个OR分支） #

为每个条件分支创建包含所有查询列的复合索引，让数据库无需回表即可获取数据：

-- 匹配 SenderId=15 AND RecipientId=1 的分支
CREATE INDEX IX_Messages_Sender_Recipient_Covering 
ON "Messages" ("SenderId", "RecipientId", "CreatedAt")
INCLUDE ("Id", "Content", "ConversationId", "IsReadByRecipient", "ReadAt");

-- 匹配 RecipientId=15 AND SenderId=1 的分支
CREATE INDEX IX_Messages_Recipient_Sender_Covering 
ON "Messages" ("RecipientId", "SenderId", "CreatedAt")
INCLUDE ("Id", "Content", "ConversationId", "IsReadByRecipient", "ReadAt");

• 若使用PostgreSQL 11以下版本，不支持INCLUDE子句，直接将所有查询列加入索引键即可；
• 把CreatedAt放在索引末尾，方便后续排序时直接利用索引顺序。

2. 改写查询为UNION ALL（配合上述索引） #

将OR条件拆分为两个独立查询，用UNION ALL合并（无重复数据，无需用UNION），再排序取前100：

SELECT * FROM (
    SELECT "Id", "Content", "ConversationId", "CreatedAt", "IsReadByRecipient", "ReadAt", "RecipientId", "SenderId"
    FROM "Messages"
    WHERE "SenderId" = 15 AND "RecipientId" = 1
    UNION ALL
    SELECT "Id", "Content", "ConversationId", "CreatedAt", "IsReadByRecipient", "ReadAt", "RecipientId", "SenderId"
    FROM "Messages"
    WHERE "RecipientId" = 15 AND "SenderId" = 1
) AS combined
ORDER BY "CreatedAt"
LIMIT 100;

这样每个子查询可以单独利用对应的复合索引，优化器会自动选择索引扫描而非顺序扫描。

三、其他可能的性能影响因素 #

1. 统计信息过时：PostgreSQL依赖表统计信息选择执行计划，运行ANALYZE "Messages";更新统计数据，让优化器能准确判断索引成本。
2. 索引未被选中的原因：如果表中符合条件的行数占比过高（比如超过10%），优化器可能认为顺序扫描比索引扫描更快。但你的执行计划显示预估18868行，若表总数据量极大，这个占比应该不高，更新统计信息后索引应该会被选中。
3. work_mem设置过小：排序操作需要足够内存，若work_mem不足会使用磁盘临时文件，导致性能下降。可以临时调整测试：SET work_mem = '64MB';，再运行查询看效果（永久调整需修改postgresql.conf）。
4. 原有复合索引未生效的原因：你创建的(SenderId, RecipientId, CreatedAt)索引只能匹配OR的第一个分支，无法覆盖第二个分支，优化器不会用它来处理整个OR条件，所以还是会选择顺序扫描。

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