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

简化Having语句
把Having中的约束条件,如满足可以提升到Where条件中的,则移动到Where子句中,否则仍保留在Having语句中.这样做的目的是因为Having过滤在Group by之后执行,如能把Having中的过滤提升到Where中,则可以提前执行"选择"运算,减少Group by的开销.
以下语句,条件dwbh='1002'提升到Where中执行:

testdb=#explainverboseselecta.dwbh,a.xb,count(*)testdb-#fromt_grxxatestdb-#groupbya.dwbh,a.xbtestdb-#havingcount(*)>=1anddwbh='1002';QUERYPLAN-----------------------------------------------------------------------------GroupAggregate(cost=15.01..15.06rows=1width=84)Output:dwbh,xb,count(*)GroupKey:a.dwbh,a.xbFilter:(count(*)>=1)--count(*)>=1仍保留在Having中->Sort(cost=15.01..15.02rows=2width=76)Output:dwbh,xbSortKey:a.xb->SeqScanonpublic.t_grxxa(cost=0.00..15.00rows=2width=76)Output:dwbh,xbFilter:((a.dwbh)::text='1002'::text)--提升到Where中,扫描时过滤Tuple(10rows)

如存在Group by & Grouping sets则不作处理:

testdb=#explainverbosetestdb-#selecta.dwbh,a.xb,count(*)testdb-#fromt_grxxatestdb-#groupbytestdb-#groupingsets((a.dwbh),(a.xb),())testdb-#havingcount(*)>=1anddwbh='1002'testdb-#orderbya.dwbh,a.xb;QUERYPLAN-------------------------------------------------------------------------------Sort(cost=28.04..28.05rows=3width=84)Output:dwbh,xb,(count(*))SortKey:a.dwbh,a.xb->MixedAggregate(cost=0.00..28.02rows=3width=84)Output:dwbh,xb,count(*)HashKey:a.dwbhHashKey:a.xbGroupKey:()Filter:((count(*)>=1)AND((a.dwbh)::text='1002'::text))--扫描数据表后再过滤->SeqScanonpublic.t_grxxa(cost=0.00..14.00rows=400width=76)Output:dwbh,grbh,xm,xb,nl(11rows)

简化Group by语句
如Group by中的字段列表已包含某个表主键的所有列,则该表在Group by语句中的其他列可以删除,这样的做法有利于提升在Group by过程中排序或Hash的性能,减少不必要的开销.

testdb=#explainverboseselecta.dwbh,a.dwmc,count(*)testdb-#fromt_dwxxatestdb-#groupbya.dwbh,a.dwmctestdb-#havingcount(*)>=1;QUERYPLAN--------------------------------------------------------------------------HashAggregate(cost=13.20..15.20rows=53width=264)Output:dwbh,dwmc,count(*)GroupKey:a.dwbh,a.dwmc--分组键为dwbh&dwmcFilter:(count(*)>=1)->SeqScanonpublic.t_dwxxa(cost=0.00..11.60rows=160width=256)Output:dwmc,dwbh,dwdz(6rows)testdb=#altertablet_dwxxaddprimarykey(dwbh);--添加主键ALTERTABLEtestdb=#explainverboseselecta.dwbh,a.dwmc,count(*)fromt_dwxxagroupbya.dwbh,a.dwmchavingcount(*)>=1;QUERYPLAN-----------------------------------------------------------------------HashAggregate(cost=1.05..1.09rows=1width=264)Output:dwbh,dwmc,count(*)GroupKey:a.dwbh--分组键只保留dwbhFilter:(count(*)>=1)->SeqScanonpublic.t_dwxxa(cost=0.00..1.03rows=3width=256)Output:dwmc,dwbh,dwdz(6rows)二、源码解读

相关处理的源码位于文件subquery_planner.c中,主函数为subquery_planner,代码片段如下:

/**InsomecaseswemaywanttotransferaHAVINGclauseintoWHERE.We*cannotdosoiftheHAVINGclausecontainsaggregates(obviously)or*volatilefunctions(sinceaHAVINGclauseissupposedtobeexecuted*onlyoncepergroup).Wealsocan'tdothisifthereareanynonempty*groupingsets;movingsuchaclauseintoWHEREwouldpotentiallychange*theresults,ifanyreferencedcolumnisn'tpresentinallthegrouping*sets.(Ifthereareonlyemptygroupingsets,thentheHAVINGclause*mustbedegenerateasdiscussedbelow.)**Also,itmaybethattheclauseissoexpensivetoexecutethatwe're*betteroffdoingitonlyoncepergroup,despitethelossof*selectivity.Thisishardtoestimateshortofdoingtheentire*planningprocesstwice,soweuseaheuristic:clausescontaining*subplansareleftinHAVING.Otherwise,wemoveorcopytheHAVING*clauseintoWHERE,inhopesofeliminatingtuplesbeforeaggregation*insteadofafter.**Ifthequeryhasexplicitgroupingthenwecansimplymovesucha*clauseintoWHERE;anygroupthatfailstheclausewillnotbeinthe*outputbecausenoneofitstupleswillreachthegroupingor*aggregationstage.Otherwisewemusthaveadegenerate(variable-free)*HAVINGclause,whichweputinWHEREsothatquery_planner()canuseit*inagatingResultnode,butalsokeepinHAVINGtoensurethatwe*don'temitabogusaggregatedrow.(Thiscouldbedonebetter,butit*seemsnotworthoptimizing.)**NotethatbothhavingQualandparse->jointree->qualsarein*implicitly-ANDed-listformatthispoint,eventhoughtheyaredeclared*asNode*.*/newHaving=NIL;foreach(l,(List*)parse->havingQual)//存在Having条件语句{Node*havingclause=(Node*)lfirst(l);//获取谓词if((parse->groupClause&&parse->groupingSets)||contain_agg_clause(havingclause)||contain_volatile_functions(havingclause)||contain_subplans(havingclause)){/*keepitinHAVING*///如果有Group&&GroupSets语句//保持不变newHaving=lappend(newHaving,havingclause);}elseif(parse->groupClause&&!parse->groupingSets){/*moveittoWHERE*///只有group语句,可以加入到jointree的条件中parse->jointree->quals=(Node*)lappend((List*)parse->jointree->quals,havingclause);}else//既没有group也没有groupingset,拷贝一份到jointree的条件中{/*putacopyinWHERE,keepitinHAVING*/parse->jointree->quals=(Node*)lappend((List*)parse->jointree->quals,copyObject(havingclause));newHaving=lappend(newHaving,havingclause);}}parse->havingQual=(Node*)newHaving;//调整having子句/*RemoveanyredundantGROUPBYcolumns*/remove_useless_groupby_columns(root);//去掉groupby中无用的数据列

remove_useless_groupby_columns

/**remove_useless_groupby_columns*RemoveanycolumnsintheGROUPBYclausethatareredundantdueto*beingfunctionallydependentonotherGROUPBYcolumns.**SincesomeotherDBMSesdonotallowreferencestoungroupedcolumns,it's*notunusualtofindallcolumnslistedinGROUPBYeventhoughlistingthe*primary-keycolumnswouldbesufficient.Deletingsuchexcesscolumns*avoidsredundantsortingwork,soit'sworthdoing.Whenwedothis,we*mustmarktheplanasdependentonthepkeyconstraint(comparethe*parser'scheck_ungrouped_columns()andcheck_functional_grouping()).**Inprinciple,wecouldtreatanyNOT-NULLcolumnsappearinginaUNIQUE*indexasthedeterminingcolumns.Butaswithcheck_functional_grouping(),*there'scurrentlynowaytorepresentdependencyonaNOTNULLconstraint,*soweconsideronlythepkeyfornow.*/staticvoidremove_useless_groupby_columns(PlannerInfo*root){Query*parse=root->parse;//查询树Bitmapset**groupbyattnos;//位图集合Bitmapset**surplusvars;//位图集合ListCell*lc;intrelid;/*NochancetodoanythingiftherearelessthantwoGROUPBYitems*/if(list_length(parse->groupClause)<2)//如果只有1个ITEMS,无需处理return;/*Don'tfiddlewiththeGROUPBYclauseifthequeryhasgroupingsets*/if(parse->groupingSets)//存在Groupingsets,不作处理return;/**ScantheGROUPBYclausetofindGROUPBYitemsthataresimpleVars.*Fillgroupbyattnos[k]withabitmapsetofthecolumnattnosofRTEk*thatareGROUPBYitems.*///用于分组的属性groupbyattnos=(Bitmapset**)palloc0(sizeof(Bitmapset*)*(list_length(parse->rtable)+1));foreach(lc,parse->groupClause){SortGroupClause*sgc=lfirst_node(SortGroupClause,lc);TargetEntry*tle=get_sortgroupclause_tle(sgc,parse->targetList);Var*var=(Var*)tle->expr;/**Ignorenon-VarsandVarsfromotherquerylevels.**XXXinprinciple,stableexpressionscontainingVarscouldalsobe*removed,ifalltheVarsarefunctionallydependentonotherGROUP*BYitems.Butit'snotclearthatsuchcasesoccuroftenenoughto*beworthtroublingover.*/if(!IsA(var,Var)||var->varlevelsup>0)continue;/*OK,rememberwehavethisVar*/relid=var->varno;Assert(relid<=list_length(parse->rtable));groupbyattnos[relid]=bms_add_member(groupbyattnos[relid],var->varattno-FirstLowInvalidHeapAttributeNumber);}/**Considereachrelationandseeifitispossibletoremovesomeofits*VarsfromGROUPBY.Forsimplicityandspeed,wedotheactualremoval*inaseparatepass.Here,wejustfillsurplusvars[k]withabitmapset*ofthecolumnattnosofRTEkthatareremovableGROUPBYitems.*/surplusvars=NULL;/*don'tallocatearrayunlessrequired*/relid=0;//如某个Relation的分组键中已含主键列,去掉其他列foreach(lc,parse->rtable){RangeTblEntry*rte=lfirst_node(RangeTblEntry,lc);Bitmapset*relattnos;Bitmapset*pkattnos;OidconstraintOid;relid++;/*Onlyplainrelationscouldhaveprimary-keyconstraints*/if(rte->rtekind!=RTE_RELATION)continue;/*NothingtodounlessthisrelhasmultipleVarsinGROUPBY*/relattnos=groupbyattnos[relid];if(bms_membership(relattnos)!=BMS_MULTIPLE)continue;/**Can'tremoveanycolumnsforthisrelifthereisnosuitable*(i.e.,nondeferrable)primarykeyconstraint.*/pkattnos=get_primary_key_attnos(rte->relid,false,&constraintOid);if(pkattnos==NULL)continue;/**Iftheprimarykeyisapropersubsetofrelattnosthenwehave*someitemsintheGROUPBYthatcanberemoved.*/if(bms_subset_compare(pkattnos,relattnos)==BMS_SUBSET1){/**Toeasilyrememberwhetherwe'vefoundanythingtodo,wedon't*allocatethesurplusvars[]arrayuntilwefindsomething.*/if(surplusvars==NULL)surplusvars=(Bitmapset**)palloc0(sizeof(Bitmapset*)*(list_length(parse->rtable)+1));/*Remembertheattnosoftheremovablecolumns*/surplusvars[relid]=bms_difference(relattnos,pkattnos);/*Also,marktheresultingplanasdependentonthisconstraint*/parse->constraintDeps=lappend_oid(parse->constraintDeps,constraintOid);}}/**IfwefoundanysurplusVars,buildanewGROUPBYclausewithoutthem.*(Note:thismayleavesomeTLEswithunreferencedressortgroupref*markings,butthat'sharmless.)*/if(surplusvars!=NULL){List*new_groupby=NIL;foreach(lc,parse->groupClause){SortGroupClause*sgc=lfirst_node(SortGroupClause,lc);TargetEntry*tle=get_sortgroupclause_tle(sgc,parse->targetList);Var*var=(Var*)tle->expr;/**Newlistmustincludenon-Vars,outerVars,andanythingnot*markedassurplus.*/if(!IsA(var,Var)||var->varlevelsup>0||!bms_is_member(var->varattno-FirstLowInvalidHeapAttributeNumber,surplusvars[var->varno]))new_groupby=lappend(new_groupby,sgc);}parse->groupClause=new_groupby;}}

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