如何降低PostgreSQL查询conversationLog表的排序耗时？

我有一张名为conversationLog的表，其messageId字段关联message表的id字段。执行以下查询时，排序操作导致查询成本显著上升，希望降低排序耗时：

explain analyze SELECT
    *
FROM
    "conversationLog" AS l
    LEFT JOIN "message" AS "m" ON "m"."id" = l."messageId"
WHERE
    l."teamId" = 'uuid'
    AND l."conversationId" = 'uuid'
    AND l."type" IN( 'outgoing', 'incoming')
    AND l."isDeleted" IS NOT TRUE
ORDER BY
    l."createdAt" DESC
LIMIT 10 OFFSET 0;

我已为该查询创建索引：

CREATE INDEX conversationLog_idx ON "conversationLog" ("teamId", "conversationId", "type", "isDeleted", "createdAt" DESC);

执行计划结果 #

Limit  (cost=6579.86..6579.88 rows=10 width=2399) (actual time=500.322..500.326 rows=10 loops=1)
  ->  Sort  (cost=6579.86..6581.15 rows=519 width=2399) (actual time=500.320..500.322 rows=10 loops=1)
"        Sort Key: l.""createdAt"" DESC"
        Sort Method: top-N heapsort  Memory: 34kB
        ->  Nested Loop Left Join  (cost=1.26..6568.64 rows=519 width=2399) (actual time=0.039..444.964 rows=84488 loops=1)
"              ->  Index Scan using conversationlog_idx on ""conversationLog"" l  (cost=0.69..2113.03 rows=519 width=835) (actual time=0.027..94.446 rows=84488 loops=1)"
"                    Index Cond: ((""teamId"" = 'uuid'::uuid) AND (""conversationId"" = 'uuid'::uuid) AND ((type)::text = ANY ('{outgoing,incoming}'::text[])))"
"                    Filter: (""isDeleted"" IS NOT TRUE)"
              ->  Index Scan using message_pkey on message m  (cost=0.57..8.59 rows=1 width=1556) (actual time=0.004..0.004 rows=1 loops=84488)
"                    Index Cond: (id = l.""messageId"")"
Planning Time: 0.337 ms
Execution Time: 500.390 ms

该结果对应匹配条件的84000条记录。若移除Order By子句，查询成本降至1.26，实际耗时0.036ms：

移除Order By后的执行计划 #

Limit  (cost=1.26..127.80 rows=10 width=2391) (actual time=0.036..0.104 rows=10 loops=1)
  ->  Nested Loop Left Join  (cost=1.26..6568.64 rows=519 width=2391) (actual time=0.035..0.102 rows=10 loops=1)
"        ->  Index Scan using conversationlog_idx on ""conversationLog"" l  (cost=0.69..2113.03 rows=519 width=835) (actual time=0.024..0.035 rows=10 loops=1)"
"              Index Cond: ((""teamBotId"" = 'uuid'::uuid) AND (""conversationId"" = 'uuid'::uuid) AND ((type)::text = ANY ('{outgoing,incoming}'::text[])))"
"              Filter: (""isDeleted"" IS NOT TRUE)"
        ->  Index Scan using message_pkey on message m  (cost=0.57..8.59 rows=1 width=1556) (actual time=0.006..0.006 rows=1 loops=10)
"              Index Cond: (id = l.""messageId"")"
Planning Time: 0.262 ms
Execution Time: 0.139 ms

我还尝试创建了如下索引，但未被查询选用：

CREATE INDEX index_c ON "conversationLog" ("createdAt" DESC NULLS LAST);

提出的问题 #

1. 为何带Order By时索引未被选用？
2. 如何创建能被该查询选用的索引？
3. 如何降低排序成本，提升该查询的执行速度？

1. 为何带Order By时索引未被选用？ #

现有索引conversationLog_idx的字段顺序不合理：isDeleted是范围过滤条件（IS NOT TRUE属于非等值匹配），却放在了排序字段createdAt前面。PostgreSQL要利用索引排序，要求排序字段必须是等值过滤条件之后的索引前缀字段，而isDeleted只能通过Filter过滤，无法作为索引等值条件，导致createdAt无法被用来直接有序返回结果。数据库只能先取出所有符合过滤条件的8万多条记录，再进行排序。

单独的index_c索引只包含createdAt，没有覆盖查询的过滤条件，用它需要扫描整个索引找符合条件的行，成本远高于先过滤再排序，因此不会被优化器选用。

2. 如何创建能被该查询选用的索引？ #

调整索引字段顺序，将等值过滤字段放在最前，接着是排序字段，最后放范围过滤字段；或者直接创建包含过滤条件的部分索引，具体两种方案：

方案一：调整字段顺序的普通索引

CREATE INDEX conversationLog_opt_idx ON "conversationLog" 
("teamId", "conversationId", "type", "createdAt" DESC, "isDeleted");

方案二：最优的部分索引（只包含符合isDeleted IS NOT TRUE的行）

CREATE INDEX conversationLog_opt_idx ON "conversationLog" 
("teamId", "conversationId", "type", "createdAt" DESC)
WHERE "isDeleted" IS NOT TRUE;

这个部分索引直接缩小了索引范围，数据库可以直接按createdAt倒序取出前10条符合条件的记录，完全跳过排序步骤。

3. 如何降低排序成本，提升该查询的执行速度？ #

• 使用优化后的部分索引：这是最有效的方式，能让数据库直接从索引中有序取数，避免对8万多条记录排序，执行时间会接近移除Order By时的耗时。
• **避免SELECT ***：只查询需要的字段，减少数据传输和内存占用，进一步提升速度。
• 更新统计信息：执行ANALYZE "conversationLog";确保优化器能准确评估索引使用成本，避免选错执行计划。

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