PostgreSQL 14的enable_memoize用于改善嵌套循环连接的性能

我最近发现了PostgreSQL 14的一个令人愉快的新功能，即新的enable_memoize标志，它提高了一些嵌套循环连接的性能，因为统计数据表明这样做是合适的。我的意思是，谁能抵制这种诱惑呢。

哇......PostgreSQL 14中的memoize使我的一个使用连接的查询快了1000倍🤩。

- Rasmus Porsager (@RPorsager)2021年11月2日

将查询速度提高1000倍，暗示着以前有一些非常不理想的事情发生，而像memoization这样的工具可以提供很大的帮助。但它对 "普通 "连接也有帮助吗？我想自己试试。

什么是记忆化？

在一个没有副作用的完美世界中（理论上，SQL就是这样一个完美的世界），记忆化意味着我们可以在任何计算中用y 来代替f(x) ，前提是y = f(x) 。例如，无论你计算多少次UPPER('x') ，你都会得到'X' 。如果计算这样一个函数的成本很高，而且只有少数可能的输入值，那么为什么不直接维护一个映射所有先前输入值的哈希图，用它来查找已知的（或至少是经常出现的）值，而不是再次计算它们？

正如我之前在这个博客上所展示的，Oracle 11g引入了一个叫做标量子查询缓存的功能，你可以在jOOQ中激活这个功能，以避免昂贵的PL/SQL上下文切换。

在PostgreSQL的enable_memoize ，这对SQL中的嵌套循环连接特别有用，参考上面的推文，横向连接经常通过嵌套循环连接执行。

打开和关闭该功能

我已经创建了一个这样的模式。

CREATE TABLE t AS
SELECT i, i % 5 AS j 
FROM generate_series(1, 100000) AS t(i);

CREATE TABLE u AS
SELECT i, i % 20000 as j 
FROM generate_series(1, 100000) AS t(i);

CREATE INDEX uj ON u(j);

综上所述。

• t 和u 两个表都有100000条记录。
• t.j 只有5个不同的值，每个值出现20000次。
• u.j 有20000个不同的值，每个值出现5次。

当在PostgreSQL 14上运行这个程序时。

SELECT current_setting('enable_memoize');

我得到。

|current_setting|
|---------------|
|on             |

所以，这个功能是激活的，我也可以在以下查询的EXPLAIN中看到。

EXPLAIN
SELECT *
FROM t JOIN u ON t.j = u.j;

该计划是。

|QUERY PLAN                                                            |
|----------------------------------------------------------------------|
|Nested Loop  (cost=0.30..8945.41 rows=496032 width=16)                |
|  ->  Seq Scan on t  (cost=0.00..1443.00 rows=100000 width=8)         |

如果没有记忆化，当像这样连接两个表时，对于t 中的100000条记录，我必须在u 中查找100000倍的5条匹配记录。但如果记忆化启动，那么我将只需要执行5次查找，因为只有5个不同的值t.j

我们可以通过打开或关闭该功能来玩弄执行计划。

SET enable_memoize = ON;
SET enable_memoize = OFF;

当关闭时，在我的机器上，PostgreSQL似乎会选择散列连接或合并连接（在多次执行之间，计划可能会切换）。

|QUERY PLAN                                                         |
|-------------------------------------------------------------------|
|Hash Join  (cost=3084.00..11568.51 rows=499351 width=16)           |
|  Hash Cond: (t.j = u.j)                                           |
|  ->  Seq Scan on t  (cost=0.00..1443.00 rows=100000 width=8)      |
|  ->  Hash  (cost=1443.00..1443.00 rows=100000 width=8)            |
|        ->  Seq Scan on u  (cost=0.00..1443.00 rows=100000 width=8)|


|QUERY PLAN                                                              |
|------------------------------------------------------------------------|
|Merge Join  (cost=9748.11..763846.11 rows=50000000 width=16)            |
|  Merge Cond: (u.j = t.j)                                               |
|  ->  Index Scan using uj on u  (cost=0.29..3848.29 rows=100000 width=8)|
|  ->  Sort  (cost=9747.82..9997.82 rows=100000 width=8)                 |
|        Sort Key: t.j                                                   |
|        ->  Seq Scan on t  (cost=0.00..1443.00 rows=100000 width=8)     |

我们来做基准测试

我们使用这里描述的通常的基准测试技术。

• 我们在模式A和模式B中重复一个操作25次，然后进行比较（如果是快速操作，则超过25次）。
• 我们重复上述操作5次，以减轻任何预热和其他缓存的影响

你可以自己在上述模式上运行以下基准，以进行验证。

DO $$
DECLARE
  v_ts TIMESTAMP;
  v_repeat CONSTANT INT := 25;
  rec RECORD;
BEGIN

  -- Repeat the whole benchmark several times to avoid warmup penalty
  FOR r IN 1..5 LOOP
    v_ts := clock_timestamp();
    SET enable_memoize = OFF;
  
    FOR i IN 1..v_repeat LOOP
      FOR rec IN (
        SELECT t.*
        FROM t JOIN u ON t.j = u.j
      ) LOOP
        NULL;
      END LOOP;
    END LOOP;

    RAISE INFO 'Run %, Statement 1: %', r, (clock_timestamp() - v_ts);
    v_ts := clock_timestamp();
    SET enable_memoize = ON;

    FOR i IN 1..v_repeat LOOP
      FOR rec IN (
        SELECT t.*
        FROM t JOIN u ON t.j = u.j
      ) LOOP
        NULL;
      END LOOP;
    END LOOP;

    RAISE INFO 'Run %, Statement 2: %', r, (clock_timestamp() - v_ts);
    RAISE INFO '';
  END LOOP;
END$$;

在我的机器上，结果是一致的，体面的，不是太令人印象深刻，但仍然很重要。

Run 1, Statement 1: 00:00:03.763426
Run 1, Statement 2: 00:00:03.401346

Run 2, Statement 1: 00:00:03.769419
Run 2, Statement 2: 00:00:03.375677

Run 3, Statement 1: 00:00:03.771465
Run 3, Statement 2: 00:00:03.374413

Run 4, Statement 1: 00:00:03.769136
Run 4, Statement 2: 00:00:03.398734

Run 5, Statement 1: 00:00:03.772544
Run 5, Statement 2: 00:00:03.375272

也就是说，速度提高了10%。在整个系统中，光是这一点就已经很值得了。

优化LATERAL

让我们试试优化LATERAL 。我们可以运行一个这样的查询。

SELECT *
FROM 
  t, 
  LATERAL (
    SELECT count(*) 
    FROM u 
    WHERE t.j = u.j
  ) AS u(j)

上面的EXPLAIN 是

|QUERY PLAN                                                                       |
|---------------------------------------------------------------------------------|
|Nested Loop  (cost=4.40..3969.47 rows=100000 width=16)                           |
|  ->  Seq Scan on t  (cost=0.00..1443.00 rows=100000 width=8)                    |

因此，我们可以再次对5个不同的t.j 输入值中的每一个的COUNT(*) 值进行缓存计算，而不是每次都重新计算。当然，这一定比以前更好？

基准测试时间!

DO $$
DECLARE
  v_ts TIMESTAMP;
  v_repeat CONSTANT INT := 25;
  rec RECORD;
BEGIN

  -- Repeat the whole benchmark several times to avoid warmup penalty
  FOR r IN 1..5 LOOP
    v_ts := clock_timestamp();
    SET enable_memoize = OFF;
  
    FOR i IN 1..v_repeat LOOP
      FOR rec IN (
        SELECT *
        FROM 
          t, 
          LATERAL (
            SELECT count(*) 
            FROM u 
            WHERE t.j = u.j
          ) AS u(j)
      ) LOOP
        NULL;
      END LOOP;
    END LOOP;

    RAISE INFO 'Run %, Statement 1: %', r, (clock_timestamp() - v_ts);
    v_ts := clock_timestamp();
    SET enable_memoize = ON;

    FOR i IN 1..v_repeat LOOP
      FOR rec IN (
        SELECT *
        FROM 
          t, 
          LATERAL (
            SELECT count(*) 
            FROM u 
            WHERE t.j = u.j
          ) AS u(j)
      ) LOOP
        NULL;
      END LOOP;
    END LOOP;

    RAISE INFO 'Run %, Statement 2: %', r, (clock_timestamp() - v_ts);
    RAISE INFO '';
  END LOOP;
END$$;

这一次，我们可以看到明显的速度提升!

Run 1, Statement 1: 00:00:03.419728
Run 1, Statement 2: 00:00:01.083941

Run 2, Statement 1: 00:00:03.404954
Run 2, Statement 2: 00:00:01.098404

Run 3, Statement 1: 00:00:03.425725
Run 3, Statement 2: 00:00:01.093883

Run 4, Statement 1: 00:00:03.441691
Run 4, Statement 2: 00:00:01.127837

Run 5, Statement 1: 00:00:03.420172
Run 5, Statement 2: 00:00:01.097943

这真是个好消息!等等，这对普通的相关子查询也有效吗？因为上面的LATERAL 相关子查询可以被改写为。

SELECT 
  t.*, 
  (
    SELECT count(*)
    FROM u
    WHERE t.j = u.j
  ) j
FROM t;

遗憾的是，这个计划并没有显示出记忆化。

|QUERY PLAN                                                                   |
|-----------------------------------------------------------------------------|
|Seq Scan on t  (cost=0.00..441693.00 rows=100000 width=16)                   |
|  SubPlan 1                                                                  |
|    ->  Aggregate  (cost=4.39..4.40 rows=1 width=8)                          |
|          ->  Index Only Scan using uj on u  (cost=0.29..4.38 rows=5 width=0)|
|                Index Cond: (j = t.j)                                        |

而基准测试（你可以自己把查询粘贴到基准逻辑中去重现）证实了没有记忆化的效果

Run 1, Statement 1: 00:00:03.617562
Run 1, Statement 2: 00:00:03.605765

Run 2, Statement 1: 00:00:03.610084
Run 2, Statement 2: 00:00:03.682064

Run 3, Statement 1: 00:00:03.725952
Run 3, Statement 2: 00:00:03.705622

Run 4, Statement 1: 00:00:03.672669
Run 4, Statement 2: 00:00:03.644612

Run 5, Statement 1: 00:00:03.645741
Run 5, Statement 2: 00:00:03.642717

似乎有了这个新功能，相关的子查询在未来可以改写成嵌套循环的外部连接？其他优化器已经做到了这一点，而我们在这里将有效地拥有与Oracle的标量子查询缓存相同的功能。

结论

该功能在PostgreSQL 14中被打开了。除了一些额外的内存消耗（如果优化器是错误的，并且统计是关闭的，这可能是一个小问题），我没有看到这个新功能的任何缺点。SQL是一个无副作用的（理论上）4GL，意味着优化器可以用一个只取决于计算的输入值的缓存值来代替任何计算。

一个相关的子查询是一个函数，其输入参数是谓词和其他对外部查询列的引用。因此，相关子查询的结果可以被缓存，或者被备忘。如上所示，这对你现在的SQL查询有很大的影响，你只要升级到PostgreSQL 14就可以从中获益。

• 点击分享到Facebook (在新窗口打开)

• 点击在LinkedIn上分享 (在新窗口打开)

• 点击在Twitter上分享 (在新窗口打开)

• 点击分享到Reddit (在新窗口打开)

• 更多

• 点击发送链接给朋友 (在新窗口打开)

• 点击打印 (在新窗口中打开)

喜欢这个

Like Loading...