PG 12的新特性Inlined WITH queries (Common table expressions),描述如下:
>Common table expressions (aka WITH queries) can now be automatically inlined in a query if they a) are not recursive, b) do not have any side-effects and c) are only referenced once in a later part of a query. This removes an "optimization fence" that has existed since the introduction of the WITH clause in PostgreSQL 8.4.

在PG 8.4 ~ PG 11,PG會把WITH中的查詢視為"optimization fence"(最佳化圍欄,與WITH外的查詢隔離,獨立最佳化),也就意味著謂詞下推等最佳化手段無法應用到WITH子句中,考慮到CTE在大多數情況下是為了增強可讀性而存在,因此在PG 12中,滿足以下三個條件的,最佳化器將不會對CTE"視而不見"而是執行"積極的"最佳化.
A.遞迴查詢
B.沒有任何副作用(side effect)
C.僅在查詢的後續部分引用一次

**謂詞下推**
測試指令碼:
```
drop table  if exists t_w1;
drop table  if exists t_w2;
drop table  if exists t_w3;

create table t_w1(id int ,c1 varchar(20));
create table t_w2(id int ,c1 varchar(20));
create table t_w3(id int ,c1 varchar(20));

insert into t_w1 select x,x||'' from generate_series(1,10000) as x;
insert into t_w2 select x/2,(x/2)||'' from generate_series(1,10000) as x;
insert into t_w3 select x,x||'' from generate_series(1,10000) as x;
```
查詢語句:
```
WITH t1 AS ( SELECT * FROM t_w1 WHERE t_w1.id % 4 = 0 ) 
SELECT * FROM t1 
  JOIN t_w2 as t2 
  ON t2.id = t1.id
     AND t1.id < 100;
```

在PG 11中,其執行計劃如下:
```
                                                version                                                 
--------------------------------------------------------------------------------------------
 PostgreSQL 11.2 on x86_XX-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), XX-bit
(1 row)

testdb=# explain analyze WITH t1 AS ( SELECT * FROM t_w1 WHERE t_w1.id % 4 = 0 ) 
testdb-# SELECT * FROM t1 
testdb-#   JOIN t_w2 as t2 
testdb-#   ON t2.id = t1.id 
testdb-#      AND t1.id < 100;
                                                   QUERY PLAN                                                    
--------------------------------------------------------------------------------------------
 Hash Join  (cost=205.34..396.18 rows=34 width=70) (actual time=8.576..11.187 rows=48 loops=1)
   Hash Cond: (t2.id = t1.id)
   CTE t1
     ->  Seq Scan on t_w1  (cost=0.00..204.00 rows=50 width=8) (actual time=0.029..6.074 rows=2500 loops=1)
           Filter: ((id % 4) = 0)
           Rows Removed by Filter: 7500
   ->  Seq Scan on t_w2 t2  (cost=0.00..153.00 rows=10000 width=8) (actual time=0.030..1.166 rows=10000 loops=1)
   ->  Hash  (cost=1.12..1.12 rows=17 width=62) (actual time=8.536..8.536 rows=24 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         ->  CTE Scan on t1  (cost=0.00..1.12 rows=17 width=62) (actual time=0.033..8.521 rows=24 loops=1)
               Filter: (id < 100)
               Rows Removed by Filter: 2476
 Planning Time: 1.913 ms
 Execution Time: 11.357 ms
(14 rows)

```
在PG 12中,其執行計劃如下:
```
testdb=# select version();
                                                  version                                                   
--------------------------------------------------------------------------------------------
 PostgreSQL 12beta1 on x86_XX-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), XX-bit
(1 row)

testdb=# explain analyze WITH t1 AS ( SELECT * FROM t_w1 WHERE t_w1.id % 4 = 0 ) 
testdb-# SELECT * FROM t1 
testdb-#   JOIN t_w2 as t2 
testdb-#   ON t2.id = t1.id 
testdb-#      AND t1.id < 100;
                                                   QUERY PLAN                                                    
--------------------------------------------------------------------------------------------
 Hash Join  (cost=229.01..419.52 rows=1 width=16) (actual time=6.974..17.156 rows=48 loops=1)
   Hash Cond: (t2.id = t_w1.id)
   ->  Seq Scan on t_w2 t2  (cost=0.00..153.00 rows=10000 width=8) (actual time=0.076..5.205 rows=10000 loops=1)
   ->  Hash  (cost=229.00..229.00 rows=1 width=8) (actual time=6.882..6.882 rows=24 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         ->  Seq Scan on t_w1  (cost=0.00..229.00 rows=1 width=8) (actual time=0.077..6.842 rows=24 loops=1)
               Filter: ((id < 100) AND ((id % 4) = 0))
               Rows Removed by Filter: 9976
 Planning Time: 1.677 ms
 Execution Time: 17.244 ms
(10 rows)

```

可以看到,在PG 11中,謂詞(id < 100)不會下推CTE中,但在PG 12中,最佳化器則把謂詞下推到CTE中(Filter: ((*id < 100*) AND ((id % 4) = 0))).

**New Option**
如果希望12的最佳化器行為與先前的一樣,則加入Option : MATERIALIZED.
```
testdb=# explain analyze WITH t1 AS MATERIALIZED( SELECT * FROM t_w1 WHERE t_w1.id % 4 = 0 ) 
SELECT * FROM t1 
  JOIN t_w2 as t2 
  ON t2.id = t1.id 
     AND t1.id < 100;
                                                   QUERY PLAN                                                    
-------------------------------------------------------------------------------------------
 Hash Join  (cost=205.34..396.18 rows=34 width=70) (actual time=30.705..48.549 rows=48 loops=1)
   Hash Cond: (t2.id = t1.id)
   CTE t1
     ->  Seq Scan on t_w1  (cost=0.00..204.00 rows=50 width=8) (actual time=0.152..21.274 rows=2500 loops=1)
           Filter: ((id % 4) = 0)
           Rows Removed by Filter: 7500
   ->  Seq Scan on t_w2 t2  (cost=0.00..153.00 rows=10000 width=8) (actual time=0.154..8.582 rows=10000 loops=1)
   ->  Hash  (cost=1.12..1.12 rows=17 width=62) (actual time=30.502..30.502 rows=24 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         ->  CTE Scan on t1  (cost=0.00..1.12 rows=17 width=62) (actual time=0.168..30.445 rows=24 loops=1)
               Filter: (id < 100)
               Rows Removed by Filter: 2476
 Planning Time: 7.673 ms
 Execution Time: 49.284 ms
(14 rows)
```

如果希望最佳化器把儘可能的把CTE視為內聯查詢進行最佳化,則指定NOT MATERIALIZED Option:
下面的查詢,CTE被引用多次,最佳化器預設會進行MATERIALIZED,透過指定NOT MATERIALIZED則強制為內聯查詢.
```
testdb=# explain analyze WITH t1 AS ( SELECT * FROM t_w1 WHERE t_w1.id % 4 = 0 ) 
testdb-# SELECT * FROM t1 
testdb-#   JOIN t_w2 as t2 
testdb-#   ON t2.id = t1.id
testdb-# UNION ALL
testdb-# select t1.*,NULL,NULL from t1 where t1.id % 3 = 0;
                                                      QUERY PLAN                                                       
-----------------------------------------------------------------------------------------------------------------------
 Append  (cost=205.62..399.89 rows=101 width=70) (actual time=11.663..27.725 rows=3332 loops=1)
   CTE t1
     ->  Seq Scan on t_w1  (cost=0.00..204.00 rows=50 width=8) (actual time=0.032..7.300 rows=2500 loops=1)
           Filter: ((id % 4) = 0)
           Rows Removed by Filter: 7500
   ->  Hash Join  (cost=1.62..193.12 rows=100 width=70) (actual time=11.662..24.094 rows=2499 loops=1)
         Hash Cond: (t2.id = t1.id)
         ->  Seq Scan on t_w2 t2  (cost=0.00..153.00 rows=10000 width=8) (actual time=0.033..4.412 rows=10000 loops=1)
         ->  Hash  (cost=1.00..1.00 rows=50 width=62) (actual time=11.611..11.612 rows=2500 loops=1)
               Buckets: 4096 (originally 1024)  Batches: 1 (originally 1)  Memory Usage: 132kB
               ->  CTE Scan on t1  (cost=0.00..1.00 rows=50 width=62) (actual time=0.035..9.916 rows=2500 loops=1)
   ->  CTE Scan on t1 t1_1  (cost=0.00..1.25 rows=1 width=98) (actual time=0.008..2.824 rows=833 loops=1)
         Filter: ((id % 3) = 0)
         Rows Removed by Filter: 1667
 Planning Time: 2.358 ms
 Execution Time: 28.746 ms
(16 rows)
```
使用NOT MATERIALIZED選項
```
testdb=# explain analyze WITH t1 AS NOT MATERIALIZED( SELECT * FROM t_w1 WHERE t_w1.id % 4 = 0 ) 
SELECT * FROM t1 
  JOIN t_w2 as t2 
  ON t2.id = t1.id
UNION ALL
select t1.*,NULL,NULL from t1 where t1.id % 3 = 0;
                                                      QUERY PLAN                                                       
-------------------------------------------------------------------------------------------
 Append  (cost=204.62..650.39 rows=51 width=17) (actual time=27.894..57.453 rows=3332 loops=1)
   ->  Hash Join  (cost=204.62..395.62 rows=50 width=16) (actual time=27.892..48.911 rows=2499 loops=1)
         Hash Cond: (t2.id = t_w1.id)
         ->  Seq Scan on t_w2 t2  (cost=0.00..153.00 rows=10000 width=8) (actual time=0.149..7.606 rows=10000 loops=1)
         ->  Hash  (cost=204.00..204.00 rows=50 width=8) (actual time=27.699..27.699 rows=2500 loops=1)
               Buckets: 4096 (originally 1024)  Batches: 1 (originally 1)  Memory Usage: 132kB
               ->  Seq Scan on t_w1  (cost=0.00..204.00 rows=50 width=8) (actual time=0.151..22.446 rows=2500 loops=1)
                     Filter: ((id % 4) = 0)
                     Rows Removed by Filter: 7500
   ->  Seq Scan on t_w1 t_w1_1  (cost=0.00..254.00 rows=1 width=44) (actual time=0.038..7.400 rows=833 loops=1)
         Filter: (((id % 4) = 0) AND ((id % 3) = 0))
         Rows Removed by Filter: 9167
 Planning Time: 12.357 ms
 Execution Time: 58.490 ms
(14 rows)
```

**小結**
在大多數情況下,使用CTE的目的是為了簡化SQL語句的複雜性,但由於PG 12之前最佳化器把CTE視為Black Box而不進行整體最佳化,在某些情況下會出現效能問題(CTE返回大量資料需要MATERIALIZED時).在PG 12中,最佳化器則會對CTE納入"整體"進行最佳化,實現了既可以滿足SQL可讀性也能避免無法進行最佳化的問題.

PostgreSQL DBA(36) - PG 12 Inlined WITH queries

相關文章