PostgreSQL新特性分析
短信预约 -IT技能 免费直播动态提醒
这篇文章主要介绍“PostgreSQL新特性分析”，在日常操作中，相信很多人在PostgreSQL新特性分析问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”PostgreSQL新特性分析”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！
在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)到此，关于“PostgreSQL新特性分析”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/59.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
