PostgreSQL CTE中指定NOT MATERIALIZED

最新推荐文章于 2024-08-13 15:40:03 发布

foucus、

最新推荐文章于 2024-08-13 15:40:03 发布

阅读量2k

点赞数 2

分类专栏： PostgreSQL 文章标签： postgresql

本文链接：https://blog.csdn.net/weixin_39540651/article/details/103909736

版权

PostgreSQL 专栏收录该内容

282 篇文章 169 订阅

订阅专栏

pg12之前，WITH中的每一个CTE（common table express），都是直接进行物化的，也就是说外层的条件是不会影响到CTE语句里面的内容。但是对于select CTE来说，外面的条件如果可以推到CTE里面，那可能能够大幅降低扫描，尤其是在有索引的情况下。
从pg12开始，CTE语句支持了not materialized选项，即不使用物化，允许外面条件推进去。

例子：
–创建测试表

bill=# create table t3(c1 int,c2 int);
CREATE TABLE
bill=# create table t4 as select * from t3;
SELECT 0
bill=# insert into t3 select random()*1000,random()*1000 from generate_series(1,1000000);
INSERT 0 1000000
bill=# insert into t4 select random()*1000,random()*1000 from generate_series(1,1000000);
INSERT 0 1000000
bill=# create index idx_t3 on t3(c1);
CREATE INDEX
bill=# create index idx_t4 on t4(c1);
CREATE INDEX

–使用物化
可以发现性能异常的差，竟然耗时10几万秒！

bill=# explain (analyze ,verbose,timing,buffers,costs)WITH w AS (  
bill(#     SELECT * FROM t3  
bill(# )  
bill-# SELECT * FROM w AS w1 JOIN w AS w2 ON (w1.c1 = w2.c2)  
bill-# WHERE w2.c2 = 172;  

                                                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=14425.00..559375.00 rows=25000000 width=16) (actual time=0.157..108172.213 rows=975240 loops=1)
   Output: w1.c1, w1.c2, w2.c1, w2.c2
   Buffers: shared hit=4425, temp read=2268890 written=2198
   CTE w
     ->  Seq Scan on bill.t3  (cost=0.00..14425.00 rows=1000000 width=8) (actual time=0.012..112.465 rows=1000000 loops=1)
           Output: t3.c1, t3.c2
           Buffers: shared hit=4425
   ->  CTE Scan on w w1  (cost=0.00..22500.00 rows=5000 width=8) (actual time=0.100..101.660 rows=1032 loops=1)
         Output: w1.c1, w1.c2
         Filter: (w1.c1 = 172)
         Rows Removed by Filter: 998968
         Buffers: shared hit=2, temp read=2752 written=1
   ->  CTE Scan on w w2  (cost=0.00..22500.00 rows=5000 width=8) (actual time=0.027..104.555 rows=945 loops=1032)
         Output: w2.c1, w2.c2
         Filter: (w2.c2 = 172)
         Rows Removed by Filter: 999055
         Buffers: shared hit=4423, temp read=2266138 written=2197
 Planning Time: 0.100 ms
 Execution Time: 108241.389 ms
(19 rows)

–不使用物化
时间变成了200多秒，原因就是因为外部条件可以推到CTE语句里面，而且使用了索引。

bill=# explain (analyze ,verbose,timing,buffers,costs)WITH w AS NOT MATERIALIZED(  
bill(#     SELECT * FROM t3  
bill(# )  
bill-# SELECT * FROM w AS w1 JOIN w AS w2 ON (w1.c1 = w2.c2)  
bill-# WHERE w2.c2 = 172;  
                                                               QUERY PLAN                                                                
-----------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=1012.02..24125.71 rows=986049 width=16) (actual time=0.674..239.380 rows=975240 loops=1)
   Output: t3.c1, t3.c2, t3_1.c1, t3_1.c2
   Buffers: shared hit=5354 read=4
   ->  Bitmap Heap Scan on bill.t3  (cost=12.02..1064.98 rows=993 width=8) (actual time=0.389..2.133 rows=1032 loops=1)
         Output: t3.c1, t3.c2
         Recheck Cond: (t3.c1 = 172)
         Heap Blocks: exact=928
         Buffers: shared hit=929 read=4
         ->  Bitmap Index Scan on idx_t3  (cost=0.00..11.77 rows=993 width=0) (actual time=0.266..0.266 rows=1032 loops=1)
               Index Cond: (t3.c1 = 172)
               Buffers: shared hit=1 read=4
   ->  Materialize  (cost=1000.00..10737.60 rows=993 width=8) (actual time=0.000..0.091 rows=945 loops=1032)
         Output: t3_1.c1, t3_1.c2
         Buffers: shared hit=4425
         ->  Gather  (cost=1000.00..10732.63 rows=993 width=8) (actual time=0.280..36.914 rows=945 loops=1)
               Output: t3_1.c1, t3_1.c2
               Workers Planned: 2
               Workers Launched: 2
               Buffers: shared hit=4425
               ->  Parallel Seq Scan on bill.t3 t3_1  (cost=0.00..9633.33 rows=414 width=8) (actual time=0.047..33.761 rows=315 loops=3)
                     Output: t3_1.c1, t3_1.c2
                     Filter: (t3_1.c2 = 172)
                     Rows Removed by Filter: 333018
                     Buffers: shared hit=4425
                     Worker 0: actual time=0.020..32.610 rows=298 loops=1
                       Buffers: shared hit=1425
                     Worker 1: actual time=0.092..32.467 rows=239 loops=1
                       Buffers: shared hit=1061
 Planning Time: 0.167 ms
 Execution Time: 294.592 ms
(30 rows)