PostgreSQL 14引入了memoize特性，通过参数enable_memoize控制，默认打开

**什么是memoize**
简单来说，memoize意思是把先前已经“计算”得到的结果缓存起来，不需要再计算了，在NL Join中特别有用。

**示例**
1、创建测试数据表
t表j列唯一值只有5个，u表j列唯一值有20000个
```
testdb=# CREATE TABLE t AS
testdb-# SELECT i, i % 5 AS j
testdb-# FROM generate_series(1, 100000) AS t(i);
SELECT 100000
testdb=#
testdb=# CREATE TABLE u AS
testdb-# SELECT i, i % 20000 as j
testdb-# FROM generate_series(1, 100000) AS t(i);
SELECT 100000
testdb=#
testdb=# CREATE INDEX uj ON u(j);
CREATE INDEX
testdb=# analyze t;
ANALYZE
testdb=# analyze u;
ANALYZE
```
2、enable_memoize参数默认开启，使用NL Join，只需从内存中读取5行即可
```
testdb=# EXPLAIN ANALYZE
SELECT *
FROM t JOIN u ON t.j = u.j;
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.30..8945.41 rows=502816 width=16) (actual time=0.037..217.732 rows=500000 loops=1)
   ->  Seq Scan on t  (cost=0.00..1443.00 rows=100000 width=8) (actual time=0.011..14.230 rows=100000 loops=1)
   ->  Memoize  (cost=0.30..0.41 rows=5 width=8) (actual time=0.000..0.001 rows=5 loops=100000)
         Cache Key: t.j
         Cache Mode: logical
         Hits: 99995  Misses: 5  Evictions: 0  Overflows: 0  Memory Usage: 2kB
         ->  Index Scan using uj on u  (cost=0.29..0.40 rows=5 width=8) (actual time=0.006..0.010 rows=5 loops=5)
               Index Cond: (j = t.j)
 Planning Time: 0.120 ms
 Execution Time: 248.187 ms
(10 rows)

testdb=# set enable_memoize=off;
SET
```
3、禁用enable_memoize，默认走HashJoin
```
3、
testdb=# EXPLAIN
SELECT *
FROM t JOIN u ON t.j = u.j;
                             QUERY PLAN
---------------------------------------------------------------------
 Hash Join  (cost=3084.00..11603.16 rows=502816 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)
(5 rows)
```
4、禁用Hash Join和Merge Join，走普通的NL Join，耗时是使用memoize的3倍多（接近4倍）
```
testdb=# set enable_hashjoin=off;
SET
testdb=# set enable_mergejoin=off;
SET
testdb=# EXPLAIN ANALYZE
SELECT *
FROM t JOIN u ON t.j = u.j;
                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.29..46791.00 rows=502816 width=16) (actual time=0.035..889.127 rows=500000 loops=1)
   ->  Seq Scan on t  (cost=0.00..1443.00 rows=100000 width=8) (actual time=0.011..19.893 rows=100000 loops=1)
   ->  Index Scan using uj on u  (cost=0.29..0.40 rows=5 width=8) (actual time=0.004..0.007 rows=5 loops=100000)
         Index Cond: (j = t.j)
 Planning Time: 0.117 ms
 Execution Time: 926.700 ms
(6 rows)
```

5、使用Lateral，性能优势更为明显
```
testdb=# explain analyze SELECT *
FROM
  t,
  LATERAL (
    SELECT count(*)
    FROM u
    WHERE t.j = u.j
  ) AS u(j)
;
                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=4.40..3969.47 rows=100000 width=16) (actual time=0.044..97.465 rows=100000 loops=1)
   ->  Seq Scan on t  (cost=0.00..1443.00 rows=100000 width=8) (actual time=0.011..13.739 rows=100000 loops=1)
   ->  Memoize  (cost=4.40..4.42 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=100000)
         Cache Key: t.j
         Cache Mode: binary
         Hits: 99995  Misses: 5  Evictions: 0  Overflows: 0  Memory Usage: 1kB
         ->  Aggregate  (cost=4.39..4.40 rows=1 width=8) (actual time=0.008..0.009 rows=1 loops=5)
               ->  Index Only Scan using uj on u  (cost=0.29..4.38 rows=5 width=0) (actual time=0.006..0.006 rows=5 loops=5)
                     Index Cond: (j = t.j)
                     Heap Fetches: 0
 Planning Time: 0.129 ms
 Execution Time: 102.161 ms
(12 rows)

testdb=# set enable_memoize=off;
SET
testdb=# explain analyze SELECT *
FROM
  t,
  LATERAL (
    SELECT count(*)
    FROM u
    WHERE t.j = u.j
  ) AS u(j)
;
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=4.39..443693.00 rows=100000 width=16) (actual time=0.043..632.747 rows=100000 loops=1)
   ->  Seq Scan on t  (cost=0.00..1443.00 rows=100000 width=8) (actual time=0.012..15.995 rows=100000 loops=1)
   ->  Aggregate  (cost=4.39..4.40 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=100000)
         ->  Index Only Scan using uj on u  (cost=0.29..4.38 rows=5 width=0) (actual time=0.003..0.004 rows=5 loops=100000)
               Index Cond: (j = t.j)
               Heap Fetches: 0
 Planning Time: 0.123 ms
 Execution Time: 640.749 ms
(8 rows)

```
**参考资料**
[PostgreSQL 14’s enable_memoize For Improved Performance of Nested Loop Joins](https://blog.jooq.org/postgresql-14s-enable_memoize-for-improved-performance-of-nested-loop-joins/)

ITPub博客

PostgreSQL DBA(188) - PG 14 enable_memoize

最新文章