小编给大家分享一下PostgreSQL中查询优化的示例分析，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！

下面是PG源码目录(/src/backend/optimizer)中的README文件对优化器相关函数和数据结构的总体说明:

OptimizerFunctions-------------------Theprimaryentrypointisplanner().planner()//优化器主入口函数setupforrecursivehandlingofsubqueries//为子查询配置处理器(递归方式)-subquery_planner()//调用(子)查询优化函数pullupsublinksandsubqueriesfromrangetable,ifpossible//可以的话,上拉子链接和子查询canonicalizequal//表达式规范化AttempttosimplifyWHEREclausetothemostusefulform;thisincludesflatteningnestedAND/ORsanddetectingclausesthatareduplicatedindifferentbranchesofanOR.//简化WHERE语句simplifyconstantexpressions//简化常量表达式processsublinks//处理子链接convertVarsofouterquerylevelsintoParams//转换外查询的Vars变量到Params中--grouping_planner()//preprocesstargetlistfornon-SELECTqueries//预处理非SELECT语句的投影列handleUNION/INTERSECT/EXCEPT,GROUPBY,HAVING,aggregates,//处理集合操作/聚集函数/排序等ORDERBY,DISTINCT,LIMIT--query_planner()//makelistofbaserelationsusedinquery//构造查询中的基表链表splitupthequalintorestrictions(a=1)andjoins(b=c)//拆分表达式为限制条件和连接findqualclausesthatenablemergeandhashjoins//查找可以让Merge和Hash连接生效的表达式----make_one_rel()//set_base_rel_pathlists()//设置基表路径链表findseqscanandallindexpathsforeachbaserelation//遍历每个基表,寻找顺序扫描和所有可能的索引扫描路径findselectivityofcolumnsusedinjoins//查找连接中使用的列的选择性make_rel_from_joinlist()//通过join链表构造Relationhandoffjoinsubproblemstoaplugin,GEQO,orstandard_join_search()//-----standard_join_search()//标准的连接搜索函数calljoin_search_one_level()foreachlevelofjointreeneeded//每一个jointree调用join_search_one_leveljoin_search_one_level():Foreachjoinrelofthepriorlevel,domake_rels_by_clause_joins()//对于上一层的每一个joinrel,执行make_rels_by_clause_joinsifithasjoinclauses,ormake_rels_by_clauseless_joins()ifnot.Alsogenerate"bushyplan"joinsbetweenjoinrelsoflowerlevels.Backatstandard_join_search(),generategatherpathsifneededfor//回到standard_join_search函数,需要的话,收集相关的路径并应用set_cheapest函数获取代价最小的路径eachnewlyconstructedjoinrel,thenapplyset_cheapest()toextractthecheapestpathforit.Loopbackifthiswasnotthetopjoinlevel.//如果不是最顶层连接,循环Backatgrouping_planner://回到grouping_planner函数dogrouping(GROUPBY)andaggregation//处理分组和聚集dowindowfunctions//处理窗口函数makeunique(DISTINCT)//处理唯一性dosorting(ORDERBY)//处理排序dolimit(LIMIT/OFFSET)//处理LimitBackatplanner()://回到planner函数convertfinishedPathtreeintoaPlantree//转换最终的路径树到计划树dofinalcleanupafterplanning//收尾工作OptimizerDataStructures-------------------------PlannerGlobal-globalinformationforasingleplannerinvocation//全局优化信息PlannerInfo-informationforplanningaparticularQuery(wemake//某个Planner的优化信息aseparatePlannerInfonodeforeachsub-Query)RelOptInfo-arelationorjoinedrelations//某个Relation(包括连接)的优化信息RestrictInfo-WHEREclauses,like"x=3"or"y=z"//限制条件(notethesamestructureisusedforrestrictionandjoinclauses)Path-everywaytogenerateaRelOptInfo(sequential,index,joins)//构造该关系(注意:中间结果也是关系的一种)的路径SeqScan-representsasequentialscanplanIndexPath-indexscanBitmapHeapPath-topofabitmappedindexscanTidPath-scanbyCTIDSubqueryScanPath-scanasubquery-in-FROMForeignPath-scanaforeigntable,foreignjoinorforeignupper-relationCustomPath-forcustomscanprovidersAppendPath-appendmultiplesubpathstogetherMergeAppendPath-mergemultiplesubpaths,preservingtheircommonsortorderResultPath-achildlessResultplannode(usedforFROM-lessSELECT)MaterialPath-aMaterialplannodeUniquePath-removeduplicaterows(eitherbyhashingorsorting)GatherPath-collecttheresultsofparallelworkersGatherMergePath-collectparallelresults,preservingtheircommonsortorderProjectionPath-aResultplannodewithchild(usedforprojection)ProjectSetPath-aProjectSetplannodeappliedtosomesub-pathSortPath-aSortplannodeappliedtosomesub-pathGroupPath-aGroupplannodeappliedtosomesub-pathUpperUniquePath-aUniqueplannodeappliedtosomesub-pathAggPath-anAggplannodeappliedtosomesub-pathGroupingSetsPath-anAggplannodeusedtoimplementGROUPINGSETSMinMaxAggPath-aResultplannodewithsubplansperformingMIN/MAXWindowAggPath-aWindowAggplannodeappliedtosomesub-pathSetOpPath-aSetOpplannodeappliedtosomesub-pathRecursiveUnionPath-aRecursiveUnionplannodeappliedtotwosub-pathsLockRowsPath-aLockRowsplannodeappliedtosomesub-pathModifyTablePath-aModifyTableplannodeappliedtosomesub-path(s)LimitPath-aLimitplannodeappliedtosomesub-pathNestPath-nested-loopjoinsMergePath-mergejoinsHashPath-hashjoinsEquivalenceClass-adatastructurerepresentingasetofvaluesknownequal//等价类PathKey-adatastructurerepresentingthesortorderingofapath//排序键

看完了这篇文章，相信你对“PostgreSQL中查询优化的示例分析”有了一定的了解，如果想了解更多相关知识，欢迎关注亿速云行业资讯频道，感谢各位的阅读！