PostgreSQL中ReviewPG的Optimizer機(jī)制如何優(yōu)化函數(shù)

小編給大家分享一下PostgreSQL中Review PG的Optimizer機(jī)制如何優(yōu)化函數(shù)，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

公司主營(yíng)業(yè)務(wù)：成都網(wǎng)站建設(shè)、做網(wǎng)站、移動(dòng)網(wǎng)站開(kāi)發(fā)等業(yè)務(wù)。幫助企業(yè)客戶(hù)真正實(shí)現(xiàn)互聯(lián)網(wǎng)宣傳，提高企業(yè)的競(jìng)爭(zhēng)能力。創(chuàng)新互聯(lián)公司是一支青春激揚(yáng)、勤奮敬業(yè)、活力青春激揚(yáng)、勤奮敬業(yè)、活力澎湃、和諧高效的團(tuán)隊(duì)。公司秉承以“開(kāi)放、自由、嚴(yán)謹(jǐn)、自律”為核心的企業(yè)文化，感謝他們對(duì)我們的高要求，感謝他們從不同領(lǐng)域給我們帶來(lái)的挑戰(zhàn)，讓我們激情的團(tuán)隊(duì)有機(jī)會(huì)用頭腦與智慧不斷的給客戶(hù)帶來(lái)驚喜。創(chuàng)新互聯(lián)公司推出西和免費(fèi)做網(wǎng)站回饋大家。

一、Optimizer Functions

Optimizer Functions-查詢(xún)優(yōu)化函數(shù)

The primary entry point is planner().
planner() //主入口
set up for recursive handling of subqueries
-subquery_planner()//planner->subquery_planner
pull up sublinks and subqueries from rangetable, if possible
canonicalize qual
Attempt to simplify WHERE clause to the most useful form; this includes
flattening nested AND/ORs and detecting clauses that are duplicated in
different branches of an OR.
simplify constant expressions
process sublinks
convert Vars of outer query levels into Params
--grouping_planner()//planner->subquery_planner->grouping_planner
preprocess target list for non-SELECT queries
handle UNION/INTERSECT/EXCEPT, GROUP BY, HAVING, aggregates,
ORDER BY, DISTINCT, LIMIT
---query_planner()//subquery_planner->grouping_planner->query_planner
make list of base relations used in query
split up the qual into restrictions (a=1) and joins (b=c)
find qual clauses that enable merge and hash joins
----make_one_rel()//...grouping_planner->query_planner->make_one_rel
set_base_rel_pathlists() //為每一個(gè)RelOptInfo生成訪問(wèn)路徑
find seqscan and all index paths for each base relation
find selectivity of columns used in joins
make_rel_from_joinlist() //使用遺傳算法或動(dòng)態(tài)規(guī)劃算法構(gòu)造連接路徑
hand off join subproblems to a plugin, GEQO, or standard_join_search()
-----standard_join_search()//這是動(dòng)態(tài)規(guī)劃算法
call join_search_one_level() for each level of join tree needed
join_search_one_level():
For each joinrel of the prior level, do make_rels_by_clause_joins()
if it has join clauses, or make_rels_by_clauseless_joins() if not.
Also generate "bushy plan" joins between joinrels of lower levels.
Back at standard_join_search(), generate gather paths if needed for
each newly constructed joinrel, then apply set_cheapest() to extract
the cheapest path for it.
Loop back if this wasn't the top join level.
Back at grouping_planner:
do grouping (GROUP BY) and aggregation//在最高層處理分組/聚集/唯一過(guò)濾/排序/控制輸出元組數(shù)目等
do window functions
make unique (DISTINCT)
do sorting (ORDER BY)
do limit (LIMIT/OFFSET)
Back at planner():
convert finished Path tree into a Plan tree
do final cleanup after planning

二、Optimizer Data Structures

Optimizer Data Structures
數(shù)據(jù)結(jié)構(gòu)

PlannerGlobal   - global information for a single planner invocation
PlannerInfo     - information for planning a particular Query (we make
a separate PlannerInfo node for each sub-Query)
RelOptInfo      - a relation or joined relations
RestrictInfo   - WHERE clauses, like "x = 3" or "y = z"
(note the same structure is used for restriction and
join clauses)
Path           - every way to generate a RelOptInfo(sequential,index,joins)
SeqScan       - represents a sequential scan plan //順序掃描
IndexPath     - index scan //索引掃描
BitmapHeapPath - top of a bitmapped index scan //位圖索引掃描
TidPath       - scan by CTID //CTID掃描
SubqueryScanPath - scan a subquery-in-FROM //FROM子句中的子查詢(xún)掃描
ForeignPath   - scan a foreign table, foreign join or foreign upper-relation //FDW
CustomPath    - for custom scan providers //定制化掃描
AppendPath    - append multiple subpaths together //多個(gè)子路徑APPEND,常見(jiàn)于集合操作
MergeAppendPath - merge multiple subpaths, preserving their common sort order //保持順序的APPEND
ResultPath    - a childless Result plan node (used for FROM-less SELECT)//結(jié)果路徑(如SELECT 2+2)
MaterialPath  - a Material plan node //物化路徑
UniquePath    - remove duplicate rows (either by hashing or sorting) //去除重復(fù)行路徑
GatherPath    - collect the results of parallel workers //并行
GatherMergePath - collect parallel results, preserving their common sort order //并行,保持順序
ProjectionPath - a Result plan node with child (used for projection) //投影
ProjectSetPath - a ProjectSet plan node applied to some sub-path //投影(應(yīng)用于子路徑上)
SortPath      - a Sort plan node applied to some sub-path //排序
GroupPath     - a Group plan node applied to some sub-path //分組
UpperUniquePath - a Unique plan node applied to some sub-path //應(yīng)用于子路徑的Unique Plan
AggPath       - an Agg plan node applied to some sub-path //應(yīng)用于子路徑的聚集
GroupingSetsPath - an Agg plan node used to implement GROUPING SETS //分組集合
MinMaxAggPath - a Result plan node with subplans performing MIN/MAX //最大最小
WindowAggPath - a WindowAgg plan node applied to some sub-path //應(yīng)用于子路徑的窗口函數(shù)
SetOpPath     - a SetOp plan node applied to some sub-path //應(yīng)用于子路徑的集合操作
RecursiveUnionPath - a RecursiveUnion plan node applied to two sub-paths //遞歸UNION
LockRowsPath  - a LockRows plan node applied to some sub-path //應(yīng)用于子路徑的的LockRows
ModifyTablePath - a ModifyTable plan node applied to some sub-path(s) //應(yīng)用于子路徑的數(shù)據(jù)表更新(如INSERT/UPDATE操作等)
LimitPath     - a Limit plan node applied to some sub-path//應(yīng)用于子路徑的LIMIT
NestPath      - nested-loop joins//嵌套循環(huán)連接
MergePath     - merge joins//Merge Join
HashPath      - hash joins//Hash Join
EquivalenceClass - a data structure representing a set of values known equal
PathKey        - a data structure representing the sort ordering of a path

The optimizer spends a good deal of its time worrying about the ordering
of the tuples returned by a path.  The reason this is useful is that by
knowing the sort ordering of a path, we may be able to use that path as
the left or right input of a mergejoin and avoid an explicit sort step.
Nestloops and hash joins don't really care what the order of their inputs
is, but mergejoin needs suitably ordered inputs.  Therefore, all paths
generated during the optimization process are marked with their sort order
(to the extent that it is known) for possible use by a higher-level merge.

優(yōu)化器在元組的排序上面花費(fèi)了不少時(shí)間,原因是為了在Merge Join時(shí)避免專(zhuān)門(mén)的排序步驟.

It is also possible to avoid an explicit sort step to implement a user's
ORDER BY clause if the final path has the right ordering already, so the
sort ordering is of interest even at the top level.  grouping_planner() will
look for the cheapest path with a sort order matching the desired order,
then compare its cost to the cost of using the cheapest-overall path and
doing an explicit sort on that.
When we are generating paths for a particular RelOptInfo, we discard a path
if it is more expensive than another known path that has the same or better
sort order.  We will never discard a path that is the only known way to
achieve a given sort order (without an explicit sort, that is).  In this
way, the next level up will have the maximum freedom to build mergejoins
without sorting, since it can pick from any of the paths retained for its
inputs.

以上是“PostgreSQL中Review PG的Optimizer機(jī)制如何優(yōu)化函數(shù)”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對(duì)大家有所幫助，如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道！

分享題目：PostgreSQL中ReviewPG的Optimizer機(jī)制如何優(yōu)化函數(shù)
網(wǎng)頁(yè)URL：http://www.muchs.cn/article30/iidipo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站收錄、虛擬主機(jī)、網(wǎng)站策劃、網(wǎng)站設(shè)計(jì)公司、Google、品牌網(wǎng)站設(shè)計(jì)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶(hù)投稿、用戶(hù)轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)