时间戳

首页 > 解决方案 > 嵌套循环左连接花费太多时间？

postgresql - 嵌套循环左连接花费太多时间？

问题描述

这是query：

EXPLAIN (analyze, BUFFERS, SETTINGS)
SELECT
    operation.id
FROM
    operation
RIGHT JOIN(
    SELECT uid, did FROM (
            SELECT uid, did FROM operation where id = 993754
        ) t
    ) parts ON (operation.uid = parts.uid AND operation.did = parts.did)

和EXPLAIN信息：

Nested Loop Left Join  (cost=0.85..29695.77 rows=100 width=8) (actual time=13.709..13.711 rows=1 loops=1)
  Buffers: shared hit=4905
  ->  Unique  (cost=0.42..8.45 rows=1 width=16) (actual time=0.011..0.013 rows=1 loops=1)
        Buffers: shared hit=5
        ->  Index Only Scan using oi on operation operation_1  (cost=0.42..8.44 rows=1 width=16) (actual time=0.011..0.011 rows=1 loops=1)
              Index Cond: (id = 993754)
              Heap Fetches: 1
              Buffers: shared hit=5
  ->  Index Only Scan using oi on operation  (cost=0.42..29686.32 rows=100 width=24) (actual time=13.695..13.696 rows=1 loops=1)
        Index Cond: ((uid = operation_1.uid) AND (did = operation_1.did))
        Heap Fetches: 1
        Buffers: shared hit=4900
Settings: max_parallel_workers_per_gather = '4', min_parallel_index_scan_size = '0', min_parallel_table_scan_size = '0', parallel_setup_cost = '0', parallel_tuple_cost = '0', work_mem = '256MB'
Planning Time: 0.084 ms
Execution Time: 13.728 ms

为什么Nested Loop花费的时间比孩子花费的总和越来越多？我能为此做些什么？Execution Time应该小于1毫秒吧？

更新：

Nested Loop Left Join  (cost=5.88..400.63 rows=101 width=8) (actual time=0.012..0.012 rows=1 loops=1)
  Buffers: shared hit=8
  ->  Index Scan using oi on operation operation_1  (cost=0.42..8.44 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=1)
        Index Cond: (id = 993754)
        Buffers: shared hit=4
  ->  Bitmap Heap Scan on operation  (cost=5.45..391.19 rows=100 width=24) (actual time=0.004..0.005 rows=1 loops=1)
        Recheck Cond: ((uid = operation_1.uid) AND (did = operation_1.did))
        Heap Blocks: exact=1
        Buffers: shared hit=4
        ->  Bitmap Index Scan on ou  (cost=0.00..5.42 rows=100 width=0) (actual time=0.003..0.003 rows=1 loops=1)
              Index Cond: ((uid = operation_1.uid) AND (did = operation_1.did))
              Buffers: shared hit=3
Settings: max_parallel_workers_per_gather = '4', min_parallel_index_scan_size = '0', min_parallel_table_scan_size = '0', parallel_setup_cost = '0', parallel_tuple_cost = '0', work_mem = '256MB'
Planning Time: 0.127 ms
Execution Time: 0.028 ms

谢谢大家，当我将索引拆分为btree(id)andbtree(uid, did)时，一切都很完美，但是是什么导致它们不能一起使用？有什么细节或规则吗？

顺便说一句，该 sql 用于实时计算，这里没有显示一些窗口函数代码。

标签： postgresqlperformanceexplain

解决方案

Nested Loop实际上并不需要太多时间。的实际时间13.709..13.711意味着13.709第一行准备好从该节点发出之前花费了0.002ms，并且直到完成为止花费了 ms。

请注意，13.709ms 的启动成本包括其两个子节点的成本。在嵌套循环开始之前，两个子节点都需要发出至少一行。

孩子在msUnique之后开始发射它的第一行（也是唯一的）。0.011然而，孩子只是在msIndex Only Scan之后才开始发出它的第一（也是唯一）行。13.695这意味着您实际花费的大部分时间都在这个Index Only Scan.

这里有一个很好的答案，它深入解释了成本和实际时间。

https://explain.depesz.com上还有一个不错的工具，它计算每个节点的包含和独占时间。在这里，它用于您的查询计划，清楚地表明大部分时间都花在Index Only Scan.

由于查询几乎所有时间都花在仅索引扫描中，因此优化将有最大的好处。uid为列和表创建单独的索引did应该operation可以大大缩短查询时间。

CREATE INDEX operation_uid_did ON operation(uid, did);

当前执行计划包含 2 个仅索引扫描。

一个慢的：

  ->  Index Only Scan using oi on operation  (cost=0.42..29686.32 rows=100 width=24) (actual time=13.695..13.696 rows=1 loops=1)
        Index Cond: ((uid = operation_1.uid) AND (did = operation_1.did))
        Heap Fetches: 1
        Buffers: shared hit=4900

还有一个快速的：

  ->  Index Only Scan using oi on operation operation_1  (cost=0.42..8.44 rows=1 width=16) (actual time=0.011..0.011 rows=1 loops=1)
        Index Cond: (id = 993754)
        Heap Fetches: 1
        Buffers: shared hit=5

两者都使用索引oi，但索引条件不同。请注意，使用idas 索引条件的速度非常快，只需要加载 5 页数据 ( Buffers: shared hit=5)。慢的需要加载 4900 页而不是（Buffers: shared hit=4900）。这表明该索引已针对查询进行了优化，但对于and 并id没有那么多。索引可能按此顺序涵盖所有 3 列。uiddidoiid, uid, did

多列 btree 索引只有在查询中对最左边的列有约束时才能有效使用。关于多列索引的官方文档非常深入地解释了这一点。

推荐阅读