这篇文章主要介绍“PostgreSQL新特性分析”，在日常操作中，相信很多人在PostgreSQL新特性分析问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”PostgreSQL新特性分析”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

在PG 8.4 ~ PG 11,PG会把WITH中的查询视为”optimization fence”(优化围栏,与WITH外的查询隔离,独立优化),也就意味着谓词下推等优化手段无法应用到WITH子句中,考虑到CTE在大多数情况下是为了增强可读性而存在,因此在PG 12中,满足以下三个条件的,优化器将不会对CTE”视而不见”而是执行”积极的”优化.
A.递归查询
B.没有任何副作用(side effect)
C.仅在查询的后续部分引用一次

谓词下推
测试脚本:

droptableifexistst_w1;droptableifexistst_w2;droptableifexistst_w3;createtablet_w1(idint,c1varchar(20));createtablet_w2(idint,c1varchar(20));createtablet_w3(idint,c1varchar(20));insertintot_w1selectx,x||''fromgenerate_series(1,10000)asx;insertintot_w2selectx/2,(x/2)||''fromgenerate_series(1,10000)asx;insertintot_w3selectx,x||''fromgenerate_series(1,10000)asx;

查询语句:

WITHt1AS(SELECT*FROMt_w1WHEREt_w1.id%4=0)SELECT*FROMt1JOINt_w2ast2ONt2.id=t1.idANDt1.id<100;

在PG 11中,其执行计划如下:

version--------------------------------------------------------------------------------------------PostgreSQL11.2onx86_XX-pc-linux-gnu,compiledbygcc(GCC)4.8.520150623(RedHat4.8.5-16),XX-bit(1row)testdb=#explainanalyzeWITHt1AS(SELECT*FROMt_w1WHEREt_w1.id%4=0)testdb-#SELECT*FROMt1testdb-#JOINt_w2ast2testdb-#ONt2.id=t1.idtestdb-#ANDt1.id<100;QUERYPLAN--------------------------------------------------------------------------------------------HashJoin(cost=205.34..396.18rows=34width=70)(actualtime=8.576..11.187rows=48loops=1)HashCond:(t2.id=t1.id)CTEt1->SeqScanont_w1(cost=0.00..204.00rows=50width=8)(actualtime=0.029..6.074rows=2500loops=1)Filter:((id%4)=0)RowsRemovedbyFilter:7500->SeqScanont_w2t2(cost=0.00..153.00rows=10000width=8)(actualtime=0.030..1.166rows=10000loops=1)->Hash(cost=1.12..1.12rows=17width=62)(actualtime=8.536..8.536rows=24loops=1)Buckets:1024Batches:1MemoryUsage:9kB->CTEScanont1(cost=0.00..1.12rows=17width=62)(actualtime=0.033..8.521rows=24loops=1)Filter:(id<100)RowsRemovedbyFilter:2476PlanningTime:1.913msExecutionTime:11.357ms(14rows)

在PG 12中,其执行计划如下:

testdb=#selectversion();version--------------------------------------------------------------------------------------------PostgreSQL12beta1onx86_XX-pc-linux-gnu,compiledbygcc(GCC)4.8.520150623(RedHat4.8.5-16),XX-bit(1row)testdb=#explainanalyzeWITHt1AS(SELECT*FROMt_w1WHEREt_w1.id%4=0)testdb-#SELECT*FROMt1testdb-#JOINt_w2ast2testdb-#ONt2.id=t1.idtestdb-#ANDt1.id<100;QUERYPLAN--------------------------------------------------------------------------------------------HashJoin(cost=229.01..419.52rows=1width=16)(actualtime=6.974..17.156rows=48loops=1)HashCond:(t2.id=t_w1.id)->SeqScanont_w2t2(cost=0.00..153.00rows=10000width=8)(actualtime=0.076..5.205rows=10000loops=1)->Hash(cost=229.00..229.00rows=1width=8)(actualtime=6.882..6.882rows=24loops=1)Buckets:1024Batches:1MemoryUsage:9kB->SeqScanont_w1(cost=0.00..229.00rows=1width=8)(actualtime=0.077..6.842rows=24loops=1)Filter:((id<100)AND((id%4)=0))RowsRemovedbyFilter:9976PlanningTime:1.677msExecutionTime:17.244ms(10rows)

可以看到,在PG 11中,谓词(id < 100)不会下推CTE中,但在PG 12中,优化器则把谓词下推到CTE中(Filter: ((id < 100) AND ((id % 4) = 0))).

New Option
如果希望12的优化器行为与先前的一样,则加入Option : MATERIALIZED.

testdb=#explainanalyzeWITHt1ASMATERIALIZED(SELECT*FROMt_w1WHEREt_w1.id%4=0)SELECT*FROMt1JOINt_w2ast2ONt2.id=t1.idANDt1.id<100;QUERYPLAN-------------------------------------------------------------------------------------------HashJoin(cost=205.34..396.18rows=34width=70)(actualtime=30.705..48.549rows=48loops=1)HashCond:(t2.id=t1.id)CTEt1->SeqScanont_w1(cost=0.00..204.00rows=50width=8)(actualtime=0.152..21.274rows=2500loops=1)Filter:((id%4)=0)RowsRemovedbyFilter:7500->SeqScanont_w2t2(cost=0.00..153.00rows=10000width=8)(actualtime=0.154..8.582rows=10000loops=1)->Hash(cost=1.12..1.12rows=17width=62)(actualtime=30.502..30.502rows=24loops=1)Buckets:1024Batches:1MemoryUsage:9kB->CTEScanont1(cost=0.00..1.12rows=17width=62)(actualtime=0.168..30.445rows=24loops=1)Filter:(id<100)RowsRemovedbyFilter:2476PlanningTime:7.673msExecutionTime:49.284ms(14rows)

如果希望优化器把尽可能的把CTE视为内联查询进行优化,则指定NOT MATERIALIZED Option:
下面的查询,CTE被引用多次,优化器默认会进行MATERIALIZED,通过指定NOT MATERIALIZED则强制为内联查询.

testdb=#explainanalyzeWITHt1AS(SELECT*FROMt_w1WHEREt_w1.id%4=0)testdb-#SELECT*FROMt1testdb-#JOINt_w2ast2testdb-#ONt2.id=t1.idtestdb-#UNIONALLtestdb-#selectt1.*,NULL,NULLfromt1wheret1.id%3=0;QUERYPLAN-----------------------------------------------------------------------------------------------------------------------Append(cost=205.62..399.89rows=101width=70)(actualtime=11.663..27.725rows=3332loops=1)CTEt1->SeqScanont_w1(cost=0.00..204.00rows=50width=8)(actualtime=0.032..7.300rows=2500loops=1)Filter:((id%4)=0)RowsRemovedbyFilter:7500->HashJoin(cost=1.62..193.12rows=100width=70)(actualtime=11.662..24.094rows=2499loops=1)HashCond:(t2.id=t1.id)->SeqScanont_w2t2(cost=0.00..153.00rows=10000width=8)(actualtime=0.033..4.412rows=10000loops=1)->Hash(cost=1.00..1.00rows=50width=62)(actualtime=11.611..11.612rows=2500loops=1)Buckets:4096(originally1024)Batches:1(originally1)MemoryUsage:132kB->CTEScanont1(cost=0.00..1.00rows=50width=62)(actualtime=0.035..9.916rows=2500loops=1)->CTEScanont1t1_1(cost=0.00..1.25rows=1width=98)(actualtime=0.008..2.824rows=833loops=1)Filter:((id%3)=0)RowsRemovedbyFilter:1667PlanningTime:2.358msExecutionTime:28.746ms(16rows)

使用NOT MATERIALIZED选项

testdb=#explainanalyzeWITHt1ASNOTMATERIALIZED(SELECT*FROMt_w1WHEREt_w1.id%4=0)SELECT*FROMt1JOINt_w2ast2ONt2.id=t1.idUNIONALLselectt1.*,NULL,NULLfromt1wheret1.id%3=0;QUERYPLAN-------------------------------------------------------------------------------------------Append(cost=204.62..650.39rows=51width=17)(actualtime=27.894..57.453rows=3332loops=1)->HashJoin(cost=204.62..395.62rows=50width=16)(actualtime=27.892..48.911rows=2499loops=1)HashCond:(t2.id=t_w1.id)->SeqScanont_w2t2(cost=0.00..153.00rows=10000width=8)(actualtime=0.149..7.606rows=10000loops=1)->Hash(cost=204.00..204.00rows=50width=8)(actualtime=27.699..27.699rows=2500loops=1)Buckets:4096(originally1024)Batches:1(originally1)MemoryUsage:132kB->SeqScanont_w1(cost=0.00..204.00rows=50width=8)(actualtime=0.151..22.446rows=2500loops=1)Filter:((id%4)=0)RowsRemovedbyFilter:7500->SeqScanont_w1t_w1_1(cost=0.00..254.00rows=1width=44)(actualtime=0.038..7.400rows=833loops=1)Filter:(((id%4)=0)AND((id%3)=0))RowsRemovedbyFilter:9167PlanningTime:12.357msExecutionTime:58.490ms(14rows)

到此，关于“PostgreSQL新特性分析”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！