postgresql 如何减少Postgres查询中的排序时间

cczfrluj 于 2023-10-18 发布在 PostgreSQL

关注(0)|答案(2)|浏览(201)

我有一个名为conversationLog的表。Conversationlog具有属于消息表的messageId。当我运行查询时，成本增加主要是因为排序。如何缩短排序时间

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

这个结果是当我们有84k记录匹配条件时的结果。
如果我只删除Order By，查询成本将下降到1.26，实际时间为0.036。

不按顺序解释

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);

但这也不是查询所选择的。
为什么索引没有被排序？
我们怎样才能创建索引，让查询选择它呢？
我们如何降低排序成本并使查询更快？

postgresql

来源：https://stackoverflow.com/questions/77178782/how-to-reduce-sort-time-in-postgres-query

2条答案

按热度按时间

y53ybaqx1#

大部分时间都不去排序（只有大约500.322 - 444.964，或总时间的10%）。但是排序需要在返回任何行之前读取所有输入，而这种阅读所有输入的操作实际上花费了大部分时间。
它不能使用你的第一个索引来提供已经按顺序排列的行，因为索引中的“createdAt”列的位置在另外两个没有进行简单相等测试的列之后，IN列表和IS NOT TRUE。您可以通过将IN NOT TRUE重写为IS NULL来修复它，但这并不一定等同--它们在处理NULL的方式上有所不同。如果将“iscloud”声明为NOT NULL，那么它们将是等价的，但规划器不会注意到这一事实，因此您仍然需要重写查询。
正如Laurenz所建议的，您可以通过创建部分索引来修复这两个列的问题，但这只有在in-list参数匹配索引条件时才有效。另一种更灵活的可能性是将索引列重新排序为("teamId", "conversationId", "createdAt", "type", "isDeleted")，或者完全保留最后两列。这样，您仍然可以获得前两列的所有选择性（我假设这是绝大多数选择性的来源），同时避免排序，从而避免在排序之前读取所有行的需要。
“createdAt”上的单列索引没有使用（可能），因为计划器认为它会更慢。它可以避免排序，但是系统需要遍历“teamId”和“conversationId”不匹配的行，并逐个丢弃它们。它显然认为收益不值得付出代价。你可以通过执行set enable_sort=false来验证这一点，看看是否使用了索引，估计的成本是多少，以及真实的时间是多少。

赞(0）回复(0）举报 2023-10-18

kmbjn2e32#

索引不用于ORDER BY，因为type上的条件不是相等比较。您可以尝试部分索引，如

CREATE INDEX ON "conversationLog" ("teamId", "conversationId", "createdAt")
   WHERE "isDeleted" IS NOT TRUE AND type IN ('outgoing', 'incoming'));

看起来每个"conversationLog"最多只能有一个message，所以可以这样写：

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

赞(0）回复(0）举报 2023-10-18

我来回答

postgresql 如何减少Postgres查询中的排序时间

2条答案

相关问题

热门标签

最新问答