Shulou(Shulou.com)05/31 Report--

This article mainly introduces "Analysis of New characteristics of PostgreSQL". In daily operation, I believe many people have doubts about the analysis of new features of PostgreSQL. The editor consulted all kinds of data and sorted out simple and easy-to-use methods of operation. I hope it will be helpful for you to answer the doubts of "Analysis of New characteristics of PostgreSQL"! Next, please follow the editor to study!

In PG 8.4 ~ PG 11 WITH PG will regard queries in WITH as "optimization fence" (optimized fencing, isolated from queries outside WITH, independently optimized), which means that optimization methods such as predicate pushdown can not be applied to WITH clauses, considering that CTE exists in most cases to enhance readability, so in PG 12, the following three conditions are met Instead of turning a blind eye to CTE, the optimizer will perform "positive" optimizations.

a. Recursive query

b. No side effects (side effect)

c. Referenced only once in the subsequent part of the query

Predicate pushdown

Test script:

Drop table if exists varchar 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 Magazine | | 'from generate_series (110000) as x insert into t_w2 select xchar2, (xUniver 2) |' from generate_series (110000) as x Insert into t_w3 select xpene x | |''from generate_series (1meme 10000) as x

Query statement:

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.idtestdb-# UNION ALLtestdb-# 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 (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)

Use the NOT MATERIALIZED option

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.idUNION ALLselect T1 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) so far The study on "Analysis of the New Features of PostgreSQL" is over. I hope I can solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.