openGauss學(xué)習(xí)筆記-272 openGauss性能調(diào)優(yōu)-實(shí)際調(diào)優(yōu)案例01-調(diào)整查詢重寫GUC參數(shù)rewrite_rule272.1 目標(biāo)列子查詢提升參數(shù)intargetlist272.2 提升無agg的子查詢uniquecheck
openGauss學(xué)習(xí)筆記-272 openGauss性能調(diào)優(yōu)-實(shí)際調(diào)優(yōu)案例01-調(diào)整查詢重寫GUC參數(shù)rewrite_rule
rewrite_rule包含了多個查詢重寫規(guī)則：magicset、partialpush、uniquecheck、disablerep、intargetlist、predpush。下面簡要說明一下其中重要的幾個規(guī)則的使用場景：
272.1 目標(biāo)列子查詢提升參數(shù)intargetlist
通過將目標(biāo)列中子查詢提升，轉(zhuǎn)為JOIN，往往可以極大提升查詢性能。舉例如下查詢：
openGauss=# set rewrite_rule='none';
SET
openGauss=# create table t1(c1 int,c2 int);
CREATE TABLE
openGauss=# create table t2(c1 int,c2 int);
CREATE TABLE
openGauss=# explain (verbose on, costs off) select c1,(select avg(c2) from t2 where t2.c2=t1.c2) from t1 where t1.c1<100 order by t1.c2;
QUERY PLAN
-----------------------------------------------
Sort
Output: t1.c1, ((SubPlan 1)), t1.c2
Sort Key: t1.c2
-> Seq Scan on public.t1
Output: t1.c1, (SubPlan 1), t1.c2
Filter: (t1.c1 < 100)
SubPlan 1
-> Aggregate
Output: avg(t2.c2)
-> Seq Scan on public.t2
Output: t2.c1, t2.c2
Filter: (t2.c2 = t1.c2)
(12 rows)
由于目標(biāo)列中的相關(guān)子查詢(select avg(c2) from t2 where t2.c2=t1.c2)無法提升的緣故，導(dǎo)致每掃描t1的一行數(shù)據(jù)，就會觸發(fā)子查詢的一次執(zhí)行，效率低下。如果打開intargetlist參數(shù)會把子查詢提升轉(zhuǎn)為JOIN，來提升查詢的性能：
openGauss=# set rewrite_rule='intargetlist';
SET
openGauss=# explain (verbose on, costs off) select c1,(select avg(c2) from t2 where t2.c2=t1.c2) from t1 where t1.c1<100 order by t1.c2;
QUERY PLAN
-----------------------------------------------
Sort
Output: t1.c1, (avg(t2.c2)), t1.c2
Sort Key: t1.c2
-> Hash Left Join
Output: t1.c1, (avg(t2.c2)), t1.c2
Hash Cond: (t1.c2 = t2.c2)
-> Seq Scan on public.t1
Output: t1.c1, t1.c2
Filter: (t1.c1 < 100)
-> Hash
Output: (avg(t2.c2)), t2.c2
-> HashAggregate
Output: avg(t2.c2), t2.c2
Group By Key: t2.c2
-> Seq Scan on public.t2
Output: t2.c2
(16 rows)
272.2 提升無agg的子查詢uniquecheck
子鏈接提升需要保證對于每個條件只有一行輸出，對于有agg的子查詢可以自動提升，對于無agg的子查詢?nèi)纾?/p>
select t1.c1 from t1 where t1.c1 = (select t2.c1 from t2 where t1.c1=t2.c2) ;
重寫為：
select t1.c1 from t1 join (select t2.c1 from t2 where t2.c1 is not null group by t2.c1(unique check)) tt(c1) on tt.c1=t1.c1;
為了保證語義等價(jià)，子查詢tt必須保證對于每個group by t2.c1只能有一行輸出。打開uniquecheck查詢重寫參數(shù)保證可以提升并且等價(jià)，如果在運(yùn)行時(shí)輸出了多于一行的數(shù)據(jù)，就會報(bào)錯。
openGauss=# set rewrite_rule='uniquecheck';
SET
openGauss=# explain verbose select t1.c1 from t1 where t1.c1 = (select t2.c1 from t2 where t1.c1=t2.c1);
QUERY PLAN
-------------------------------------------------------------------------------------
Hash Join (cost=43.36..104.40 rows=2149 distinct=[200, 200] width=4)
Output: t1.c1
Hash Cond: (t1.c1 = subquery."?column?")
-> Seq Scan on public.t1 (cost=0.00..31.49 rows=2149 width=4)
Output: t1.c1, t1.c2
-> Hash (cost=40.86..40.86 rows=200 width=8)
Output: subquery."?column?", subquery.c1
-> Subquery Scan on subquery (cost=36.86..40.86 rows=200 width=8)
Output: subquery."?column?", subquery.c1
-> HashAggregate (cost=36.86..38.86 rows=200 width=4)
Output: t2.c1, t2.c1
Group By Key: t2.c1
Filter: (t2.c1 IS NOT NULL)
Unique Check Required
-> Seq Scan on public.t2 (cost=0.00..31.49 rows=2149 width=4)
Output: t2.c1
(16 rows)
注意：因?yàn)榉纸Mgroup by t2.c1 unique check發(fā)生在過濾條件tt.c1=t1.c1之前，可能導(dǎo)致原來不報(bào)錯的查詢重寫之后報(bào)錯。舉例：
有t1,t2表，其中的數(shù)據(jù)為：
openGauss=# select * from t1 order by c2;
c1 | c2
----+----
1 | 1
2 | 2
3 | 3
(3 rows)
openGauss=# select * from t2 order by c2;
c1 | c2
----+----
1 | 1
2 | 2
3 | 3
4 | 4
4 | 4
5 | 5
(6 rows)
分別關(guān)閉和打開uniquecheck參數(shù)對比，打開之后報(bào)錯。
openGauss=# select t1.c1 from t1 where t1.c1 = (select t2.c1 from t2 where t1.c1=t2.c2) ;
c1
----
1
2
3
(3 rows)
openGauss=# set rewrite_rule='uniquecheck';
SET
openGauss=# select t1.c1 from t1 where t1.c1 = (select t2.c1 from t2 where t1.c1=t2.c2) ;
ERROR: more than one row returned by a subquery used as an expression
?? 點(diǎn)贊，你的認(rèn)可是我創(chuàng)作的動力！
?? 收藏，你的青睞是我努力的方向！
?? 評論，你的意見是我進(jìn)步的財(cái)富！
