为什么我的嵌套循环花费这么多时间？

Question

我有以下要求。简而言之，wallets_history代表钱包中发生的事务。目标是每一个in_interval从in_from到in_to得到1分。

它们最终将以直方图的形式显示给用户。(为了便于阅读，我简化了请求，这不会影响问题。)

CREATE OR REPLACE FUNCTION get_wallets_histogram(
  in_wallet_id TEXT, 
  in_code TEXT, 
  in_asset_id TEXT, 
  in_from TIMESTAMPTZ, 
  in_to TIMESTAMPTZ, 
  in_interval INTERVAL)
RETURNS TABLE(date TIMESTAMPTZ, balance_change BIGINT) AS $
  SELECT s AS time,
        change::bigint
  FROM generate_series(in_from,
                       in_to - in_interval,
                       in_interval) s
  LEFT JOIN LATERAL (
      SELECT s, COALESCE(SUM(balance_change),0) change FROM wallets_history
      WHERE  s <= seen_at AND seen_at < s + in_interval
  ) q USING (s)
$ LANGUAGE SQL STABLE;

然后我运行如下：(请注意，间隔为16天，因此在简化示例的横向请求中只有一行)

SELECT * FROM get_wallets_histogram(
  'WHALE', 
  'BTC',  
  '',  
  '2022-01-01'::timestamptz,  
  '2022-02-01'::timestamptz,  
  interval '16 day');

查询计划是

 Nested Loop Left Join  (cost=2526.62..2526662.88 rows=1000 width=16) (actual time=109.301..109.303 rows=1 loops=1)
   Output: s.s, (q.change)::bigint
   Inner Unique: true
   ->  Function Scan on pg_catalog.generate_series s  (cost=0.01..10.01 rows=1000 width=8) (actual time=104.854..104.855 rows=1 loops=1)
         Output: s.s
         Function Call: generate_series('2022-01-01 00:00:00+00'::timestamp with time zone, ('2022-02-01 00:00:00+00'::timestamp with time zone - '16 days'::interval), '16 days'::interval)
   ->  Subquery Scan on q  (cost=2526.62..2526.64 rows=1 width=40) (actual time=4.442..4.443 rows=1 loops=1)
         Output: q.s, q.change
         Filter: (s.s = q.s)
         ->  Aggregate  (cost=2526.62..2526.63 rows=1 width=40) (actual time=4.440..4.441 rows=1 loops=1)
               Output: s.s, COALESCE(sum(wallets_history.balance_change), '0'::numeric)
               ->  Bitmap Heap Scan on public.wallets_history  (cost=27.85..2523.83 rows=1115 width=5) (actual time=0.842..2.570 rows=23040 loops=1)
                     Output: wallets_history.wallet_id, wallets_history.code, wallets_history.asset_id, wallets_history.tx_id, wallets_history.seen_at, wallets_history.blk_height, wallets_history.blk_idx, wallets_history.balance_change,
wallets_history.balance_total, wallets_history.nth
                     Recheck Cond: ((s.s <= wallets_history.seen_at) AND (wallets_history.seen_at < (s.s + '16 days'::interval)))
                     Heap Blocks: exact=421
                     ->  Bitmap Index Scan on wallets_history_by_seen_at  (cost=0.00..27.57 rows=1115 width=0) (actual time=0.807..0.807 rows=23040 loops=1)
                           Index Cond: ((wallets_history.seen_at >= s.s) AND (wallets_history.seen_at < (s.s + '16 days'::interval)))
 Planning Time: 0.443 ms
 JIT:
   Functions: 13
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 2.408 ms, Inlining 9.035 ms, Optimization 59.083 ms, Emission 36.578 ms, Total 107.104 ms
 Execution Time: 111.823 ms

如果我正确地读取了查询计划，generate_series生成的第一行不会在104‘t进入请求之前发生！

但我不明白的是，为什么横向请求似乎在generate_series启动时间之前就有了启动时间？

我找不到这个请求中的瓶颈。在generate_series启动时间开始的时间和内部稍后查询完成的位图堆扫描之间存在巨大的差距。

最奇怪的是，当我尝试从函数外部手动执行横向请求时。它表现得很快。然而，一旦我离开它加入到generate_series (即使只有一个时间戳发出)，它需要超过100 is！

显然，没有任何节点会使事情变慢。

Answer 1

修复程序的Summary

我发现jit在查询中占用了大部分时间，然后我试图跟踪为什么我的简单查询需要JIT。

结果，时间戳的generate_series混淆了规划者:规划者总是估计它返回1000行。因此，我在下面创建了一个generate_series_fixed，它将一个LIMIT添加到generate_series中，然后将其用作替换项。

其余的答案是我的发现的历史。如果你需要的话，这是替换的下降。

-- Technically this LIMIT clause is useless. However, without it the query planner
-- is unable to correctly estimate the numbers of row in generate_series
-- which cause JIT compilation, slowing down the query considerably
-- See https://dba.stackexchange.com/questions/310235/why-is-my-nested-loop-taking-so-much-time/310242#310242
CREATE OR REPLACE FUNCTION generate_series_fixed(in_from TIMESTAMPTZ, in_to TIMESTAMPTZ, in_interval INTERVAL) RETURNS TABLE(s TIMESTAMPTZ) AS $
  SELECT generate_series(in_from, in_to, in_interval)
  LIMIT  (EXTRACT(EPOCH FROM (in_to - in_from))/EXTRACT(EPOCH FROM in_interval)) + 1; -- I am unsure about the exact formula, but over estimating 1 row is fine...
$ LANGUAGE SQL STABLE;

看来罪魁祸首是。JIT！使用set jit = off完全解决了这个问题。

与JIT一起计划

 WindowAgg  (cost=2543486.92..2543511.92 rows=1000 width=24) (actual time=305.248..305.252 rows=1 loops=1)
   Output: s.s, (q.change)::bigint, ((sum(q.change) OVER (?) + COALESCE($0, '0'::numeric)))::bigint
   InitPlan 1 (returns $0)
     ->  Limit  (cost=8414.21..8414.21 rows=1 width=23) (actual time=29.647..29.648 rows=1 loops=1)
           Output: wallets_history_1.balance_total, wallets_history_1.seen_at, wallets_history_1.blk_height, wallets_history_1.blk_idx
           ->  Sort  (cost=8414.21..8662.97 rows=99505 width=23) (actual time=29.646..29.646 rows=1 loops=1)
                 Output: wallets_history_1.balance_total, wallets_history_1.seen_at, wallets_history_1.blk_height, wallets_history_1.blk_idx
                 Sort Key: wallets_history_1.seen_at DESC, wallets_history_1.blk_height DESC, wallets_history_1.blk_idx DESC
                 Sort Method: top-N heapsort  Memory: 25kB
                 ->  Bitmap Heap Scan on public.wallets_history wallets_history_1  (cost=1871.58..7916.68 rows=99505 width=23) (actual time=3.425..19.254 rows=98750 loops=1)
                       Output: wallets_history_1.balance_total, wallets_history_1.seen_at, wallets_history_1.blk_height, wallets_history_1.blk_idx
                       Recheck Cond: (wallets_history_1.seen_at < '2022-01-01 00:00:00+00'::timestamp with time zone)
                       Filter: ((wallets_history_1.wallet_id = 'WHALE'::text) AND (wallets_history_1.code = 'BTC'::text) AND (wallets_history_1.asset_id = ''::text))
                       Heap Blocks: exact=1800
                       ->  Bitmap Index Scan on wallets_history_by_seen_at  (cost=0.00..1846.71 rows=99505 width=0) (actual time=3.272..3.272 rows=98750 loops=1)
                             Index Cond: (wallets_history_1.seen_at < '2022-01-01 00:00:00+00'::timestamp with time zone)
   ->  Sort  (cost=2535072.71..2535075.21 rows=1000 width=40) (actual time=275.590..275.592 rows=1 loops=1)
         Output: s.s, q.change
         Sort Key: s.s
         Sort Method: quicksort  Memory: 25kB
         ->  Nested Loop Left Join  (cost=2534.99..2535022.88 rows=1000 width=40) (actual time=275.557..275.559 rows=1 loops=1)
               Output: s.s, q.change
               Inner Unique: true
               ->  Function Scan on pg_catalog.generate_series s  (cost=0.01..10.01 rows=1000 width=8) (actual time=268.958..268.959 rows=1 loops=1)
                     Output: s.s
                     Function Call: generate_series('2022-01-01 00:00:00+00'::timestamp with time zone, ('2022-02-01 00:00:00+00'::timestamp with time zone - '16 days'::interval), '16 days'::interval)
               ->  Subquery Scan on q  (cost=2534.98..2535.00 rows=1 width=40) (actual time=6.586..6.587 rows=1 loops=1)
                     Output: q.s, q.change
                     Filter: (s.s = q.s)
                     ->  Aggregate  (cost=2534.98..2534.99 rows=1 width=40) (actual time=6.583..6.584 rows=1 loops=1)
                           Output: s.s, COALESCE(sum(wallets_history.balance_change), '0'::numeric)
                           ->  Bitmap Heap Scan on public.wallets_history  (cost=27.85..2532.19 rows=1115 width=5) (actual time=0.901..4.737 rows=23040 loops=1)
                                 Output: wallets_history.wallet_id, wallets_history.code, wallets_history.asset_id, wallets_history.tx_id, wallets_history.seen_at, wallets_history.blk_height, wallets_history.blk_idx, wallets_history.bala
nce_change, wallets_history.balance_total, wallets_history.nth
                                 Recheck Cond: ((s.s <= wallets_history.seen_at) AND (wallets_history.seen_at < (s.s + '16 days'::interval)))
                                 Filter: ((wallets_history.wallet_id = 'WHALE'::text) AND (wallets_history.code = 'BTC'::text) AND (wallets_history.asset_id = ''::text))
                                 Heap Blocks: exact=421
                                 ->  Bitmap Index Scan on wallets_history_by_seen_at  (cost=0.00..27.57 rows=1115 width=0) (actual time=0.864..0.864 rows=23040 loops=1)
                                       Index Cond: ((wallets_history.seen_at >= s.s) AND (wallets_history.seen_at < (s.s + '16 days'::interval)))
 Planning Time: 2.333 ms
 JIT:
   Functions: 27
   Options: Inlining true, Optimization true, Expressions true, Deforming true
   Timing: Generation 2.050 ms, Inlining 40.980 ms, Optimization 127.174 ms, Emission 100.448 ms, Total 270.652 ms
 Execution Time: 334.052 ms
(44 rows)

和JIT一起计划

 WindowAgg  (cost=2543486.92..2543511.92 rows=1000 width=24) (actual time=39.259..39.264 rows=1 loops=1)
   Output: s.s, (q.change)::bigint, ((sum(q.change) OVER (?) + COALESCE($0, '0'::numeric)))::bigint
   InitPlan 1 (returns $0)
     ->  Limit  (cost=8414.21..8414.21 rows=1 width=23) (actual time=32.375..32.376 rows=1 loops=1)
           Output: wallets_history_1.balance_total, wallets_history_1.seen_at, wallets_history_1.blk_height, wallets_history_1.blk_idx
           ->  Sort  (cost=8414.21..8662.97 rows=99505 width=23) (actual time=32.374..32.374 rows=1 loops=1)
                 Output: wallets_history_1.balance_total, wallets_history_1.seen_at, wallets_history_1.blk_height, wallets_history_1.blk_idx
                 Sort Key: wallets_history_1.seen_at DESC, wallets_history_1.blk_height DESC, wallets_history_1.blk_idx DESC
                 Sort Method: top-N heapsort  Memory: 25kB
                 ->  Bitmap Heap Scan on public.wallets_history wallets_history_1  (cost=1871.58..7916.68 rows=99505 width=23) (actual time=3.147..21.561 rows=98750 loops=1)
                       Output: wallets_history_1.balance_total, wallets_history_1.seen_at, wallets_history_1.blk_height, wallets_history_1.blk_idx
                       Recheck Cond: (wallets_history_1.seen_at < '2022-01-01 00:00:00+00'::timestamp with time zone)
                       Filter: ((wallets_history_1.wallet_id = 'WHALE'::text) AND (wallets_history_1.code = 'BTC'::text) AND (wallets_history_1.asset_id = ''::text))
                       Heap Blocks: exact=1800
                       ->  Bitmap Index Scan on wallets_history_by_seen_at  (cost=0.00..1846.71 rows=99505 width=0) (actual time=3.005..3.005 rows=98750 loops=1)
                             Index Cond: (wallets_history_1.seen_at < '2022-01-01 00:00:00+00'::timestamp with time zone)
   ->  Sort  (cost=2535072.71..2535075.21 rows=1000 width=40) (actual time=6.875..6.877 rows=1 loops=1)
         Output: s.s, q.change
         Sort Key: s.s
         Sort Method: quicksort  Memory: 25kB
         ->  Nested Loop Left Join  (cost=2534.99..2535022.88 rows=1000 width=40) (actual time=6.870..6.873 rows=1 loops=1)
               Output: s.s, q.change
               Inner Unique: true
               ->  Function Scan on pg_catalog.generate_series s  (cost=0.01..10.01 rows=1000 width=8) (actual time=0.023..0.023 rows=1 loops=1)
                     Output: s.s
                     Function Call: generate_series('2022-01-01 00:00:00+00'::timestamp with time zone, ('2022-02-01 00:00:00+00'::timestamp with time zone - '16 days'::interval), '16 days'::interval)
               ->  Subquery Scan on q  (cost=2534.98..2535.00 rows=1 width=40) (actual time=6.846..6.848 rows=1 loops=1)
                     Output: q.s, q.change
                     Filter: (s.s = q.s)
                     ->  Aggregate  (cost=2534.98..2534.99 rows=1 width=40) (actual time=6.845..6.846 rows=1 loops=1)
                           Output: s.s, COALESCE(sum(wallets_history.balance_change), '0'::numeric)
                           ->  Bitmap Heap Scan on public.wallets_history  (cost=27.85..2532.19 rows=1115 width=5) (actual time=1.324..4.837 rows=23040 loops=1)
                                 Output: wallets_history.wallet_id, wallets_history.code, wallets_history.asset_id, wallets_history.tx_id, wallets_history.seen_at, wallets_history.blk_height, wallets_history.blk_idx, wallets_history.bala
nce_change, wallets_history.balance_total, wallets_history.nth
                                 Recheck Cond: ((s.s <= wallets_history.seen_at) AND (wallets_history.seen_at < (s.s + '16 days'::interval)))
                                 Filter: ((wallets_history.wallet_id = 'WHALE'::text) AND (wallets_history.code = 'BTC'::text) AND (wallets_history.asset_id = ''::text))
                                 Heap Blocks: exact=421
                                 ->  Bitmap Index Scan on wallets_history_by_seen_at  (cost=0.00..27.57 rows=1115 width=0) (actual time=1.290..1.290 rows=23040 loops=1)
                                       Index Cond: ((wallets_history.seen_at >= s.s) AND (wallets_history.seen_at < (s.s + '16 days'::interval)))
 Planning Time: 0.451 ms
 Execution Time: 39.424 ms
(40 rows)

在Postgres v12.9上测试。这是个惊喜。由于wallet_history是一个物化视图，所以在我看来，查询并不太复杂，不需要JIT。

我再次尝试了Postgres 14.2，同样的问题。

如果我找到了JIT造成问题的根本原因，我会继续在这里发帖。我正在标记这个问题同时解决了。

编辑:如果查询的成本足够高(默认情况下为100000)，我学会了JIT的启动。

查看查询计划，看起来嵌套循环左Join成本为2500000，将其推到阈值以上。我还不知道为什么嵌套循环左联接的开销如此之大。这可能与generate_serie的计划行数实际上是错误的.挖啊。

以下是另一个似乎与左联接与生成_系列误导策划者相关的问题

编辑2：

我发现了一个有用的黑客..。我基本上是使用generate_serie +一个限制子句的子查询来帮助查询计划器。

例如，在我的非简化查询中：

CREATE OR REPLACE FUNCTION get_wallets_histogram(in_wallet_id TEXT, in_code TEXT, in_asset_id TEXT, in_from TIMESTAMPTZ, in_to TIMESTAMPTZ, in_interval INTERVAL)
RETURNS TABLE(date TIMESTAMPTZ, balance_change BIGINT, balance BIGINT) AS $
  SELECT s AS time,
        change::bigint,
        (SUM (q.change) OVER (ORDER BY s) + COALESCE((SELECT balance_total FROM wallets_history WHERE seen_at < in_from AND wallet_id=in_wallet_id AND code=in_code AND asset_id=in_asset_id ORDER BY seen_at DESC, blk_height DESC, blk_idx DESC LIMIT 1), 0))::BIGINT  AS balance
  FROM (-- Technically this LIMIT clause is useless. However, without it the query planner
        -- is unable to correctly estimate the numbers of row in generate_series
        -- which cause JIT compilation, slowing down the query considerably
        SELECT * FROM generate_series(in_from,
        in_to - in_interval,
        in_interval) s LIMIT (EXTRACT(EPOCH FROM (in_to - in_from))/EXTRACT(EPOCH FROM in_interval))) s
  LEFT JOIN LATERAL (
      SELECT s, COALESCE(SUM(balance_change),0) change FROM wallets_history
      WHERE  s <= seen_at AND seen_at < s + in_interval AND wallet_id=in_wallet_id AND code=in_code AND asset_id=in_asset_id
  ) q USING (s)
$ LANGUAGE SQL STABLE;

编辑3：

如果有人遇到同样的问题，这里有一个generate_series_fixed函数，您可以使用它来代替原始的generate_series。

-- Technically this LIMIT clause is useless. However, without it the query planner
-- is unable to correctly estimate the numbers of row in generate_series
-- which cause JIT compilation, slowing down the query considerably
-- See https://dba.stackexchange.com/questions/310235/why-is-my-nested-loop-taking-so-much-time/310242#310242
CREATE OR REPLACE FUNCTION generate_series_fixed(in_from TIMESTAMPTZ, in_to TIMESTAMPTZ, in_interval INTERVAL) RETURNS TABLE(s TIMESTAMPTZ) AS $
  SELECT generate_series(in_from, in_to, in_interval)
  LIMIT  (EXTRACT(EPOCH FROM (in_to - in_from))/EXTRACT(EPOCH FROM in_interval)) + 1; -- I am unsure about the exact formula, but over estimating 1 row is fine...
$ LANGUAGE SQL STABLE;