PostgreSQL 原始碼解讀（50）- 查詢語句#35（Optimizer Review#1）

先前的章節介紹了query_planner中主計劃函式make_one_rel的實現邏輯，再繼續介紹之前有必要Review PG的Optimizer的機制，理論結合程式碼實現以便更好的理解程式碼。PG的Optimizer機制在原始碼中的README檔案(src/backend/optimizer/README)有相關說明。

一、Paths and Join Pairs

Paths and Join Pairs
訪問路徑和連線對

During the planning/optimizing process, we build "Path" trees representing
the different ways of doing a query. We select the cheapest Path that
generates the desired relation and turn it into a Plan to pass to the
executor. (There is pretty nearly a one-to-one correspondence between the
Path and Plan trees, but Path nodes omit info that won't be needed during
planning, and include info needed for planning that won't be needed by the
executor.)

在計劃/最佳化過程中,透過構建"Path"樹表示執行查詢的不同方法,在這些方法中,選擇生成所需Relation的最低成本路徑(Path結構體)並轉換為Plan結構體傳遞給執行器.(Path和Plan樹兩者之間幾乎存在一對一的對應關係,但Path節點省略了在計劃期間不需要但包含了在執行期間不需要而在計劃期間需要的資訊)

The optimizer builds a RelOptInfo structure for each base relation used in
the query. Base rels are either primitive tables, or subquery subselects
that are planned via a separate recursive invocation of the planner. A
RelOptInfo is also built for each join relation that is considered during
planning. A join rel is simply a combination of base rels. There is only
one join RelOptInfo for any given set of baserels --- for example, the join
{A B C} is represented by the same RelOptInfo no matter whether we build it
by joining A and B first and then adding C, or joining B and C first and
then adding A, etc. These different means of building the joinrel are represented as Paths. For each RelOptInfo we build a list of Paths that
represent plausible ways to implement the scan or join of that relation.
Once we've considered all the plausible Paths for a rel, we select the one
that is cheapest according to the planner's cost estimates. The final plan
is derived from the cheapest Path for the RelOptInfo that includes all the
base rels of the query.

最佳化器為查詢中使用到的每一個基礎關係(base relation)建立RelOptInfo結構體.基礎關係(base relation)包括原始表(primitive table),子查詢(透過獨立的計劃器遞迴呼叫生成計劃)以及參與連線的Relation.連線生成的關係(join rel)是基礎關係的組合(可以理解為透過連線運算生成的新關係).對於給定的基礎關係集合,只有一個連線的RelOptInfo結構體生成,而這個RelOptInfo如何生成(比如基礎關係集合{A B C},A和B可以先連線然後與C連線,當然也可以B和C先連線然後在與A連線)並不關心.這些構建join rel的方法透過Paths表示.對每一個RelOptInfo,最佳化器會構建Paths連結串列來表示這些"貌似有理的"實現掃描或者連線的方法.對於這些所有可能的路徑,最佳化器會根據成本估算選擇成本最低的訪問路徑.最後的執行計劃從RelOptInfo(最終生成的新關係)成本最低的訪問路徑產生.

Possible Paths for a primitive table relation include plain old sequential
scan, plus index scans for any indexes that exist on the table, plus bitmap
index scans using one or more indexes. Specialized RTE types, such as
function RTEs, may have only one possible Path.

訪問原始表的可能路徑包括普通的順序掃描/基於索引的索引掃描/點陣圖索引掃描.對於某些特殊的RTE型別,比如函式RTEs,可能只有一種可能的路徑.

Joins always occur using two RelOptInfos. One is outer, the other inner.
Outers drive lookups of values in the inner. In a nested loop, lookups of
values in the inner occur by scanning the inner path once per outer tuple
to find each matching inner row. In a mergejoin, inner and outer rows are
ordered, and are accessed in order, so only one scan is required to perform
the entire join: both inner and outer paths are scanned in-sync. (There's
not a lot of difference between inner and outer in a mergejoin...) In a
hashjoin, the inner is scanned first and all its rows are entered in a
hashtable, then the outer is scanned and for each row we lookup the join
key in the hashtable.

連線通常出現在兩個RelOptInfo之間,俗稱外部表和內部表,其中外部表又稱驅動表.在Nested Loop連線方式,對於外部的每一個元組,都會訪問內部表以掃描滿足條件的資料行.Merge Join連線方式,外部表和內部表的元組已排序,順序訪問外部表和內部表的每一個元組即可,這種方式只需要同步掃描一次.Hash Join連線方式,首先會掃描內部表並建立HashTable,然後掃描外部表,對於外部表的每一行掃描雜湊表找出匹配行.

A Path for a join relation is actually a tree structure, with the topmost
Path node representing the last-applied join method. It has left and right
subpaths that represent the scan or join methods used for the two input
relations.

join rel的訪問路徑實際上是一種樹狀結構,最頂層的路徑節點表示最好應用的連線方法.這顆樹有左右兩個子路徑(subpath)用以表示兩個relations的掃描或連線方法.

二、Join Tree Construction

Join Tree Construction
連線樹的構造

The optimizer generates optimal query plans by doing a more-or-less
exhaustive search through the ways of executing the query. The best Path
tree is found by a recursive process:

最佳化器儘可能的透過窮盡搜尋的方法生成最優的查詢執行計劃,最優的訪問路徑樹透過以下遞迴過程實現:

1).Take each base relation in the query, and make a RelOptInfo structure
for it. Find each potentially useful way of accessing the relation,
including sequential and index scans, and make Paths representing those
ways. All the Paths made for a given relation are placed in its
RelOptInfo.pathlist. (Actually, we discard Paths that are obviously
inferior alternatives before they ever get into the pathlist --- what
ends up in the pathlist is the cheapest way of generating each potentially
useful sort ordering and parameterization of the relation.) Also create a
RelOptInfo.joininfo list including all the join clauses that involve this
relation. For example, the WHERE clause "tab1.col1 = tab2.col1" generates
entries in both tab1 and tab2's joininfo lists.

1).為查詢中每個基礎關係生成RelOptInfo結構體.為每個基礎關係生成順序掃描或索引掃描訪問路徑.這些生成的訪問路徑儲存在RelOptInfo.pathlist連結串列中(實際上,在此過程中最佳化器已經拋棄了明顯不合理的訪問路徑,在pathlist中的路徑是生成排序路徑和引數化Relation的最可能路徑).在此期間,會生成RelOptInfo.joininfo連結串列,用於儲存與此Relation相關的索引的連線語句(join clauses).比如,WHERE語句"tab1.col1 = tab2.col1"在tab1和tab2的joininfo連結串列中會產生相應的資料結構(entries).

If we have only a single base relation in the query, we are done.
Otherwise we have to figure out how to join the base relations into a
single join relation.

如果查詢中只有一個基礎關係,最佳化器已完成所有工作,否則的話,最佳化器需要得出如何連線基礎關係,從而得到一個新關係(透過join連線而來).

2).Normally, any explicit JOIN clauses are "flattened" so that we just
have a list of relations to join. However, FULL OUTER JOIN clauses are
never flattened, and other kinds of JOIN might not be either, if the
flattening process is stopped by join_collapse_limit or from_collapse_limit
restrictions. Therefore, we end up with a planning problem that contains
lists of relations to be joined in any order, where any individual item
might be a sub-list that has to be joined together before we can consider
joining it to its siblings. We process these sub-problems recursively,
bottom up. Note that the join list structure constrains the possible join
orders, but it doesn't constrain the join implementation method at each
join (nestloop, merge, hash), nor does it say which rel is considered outer
or inner at each join. We consider all these possibilities in building
Paths. We generate a Path for each feasible join method, and select the
cheapest Path.

2)通常來說,顯式的JOIN語句已被扁平化(flattened)處理,最佳化器可以直接根據關係連結串列進行連線.但是,全外連線(FULL OUTER JOIN)以及某些型別的JOIN無法扁平化(比如由於join_collapse_limit或from_collapse_limit這些約束條件).這裡會遇到這麼一個問題:嘗試以任意順序進行連線的關係連結串列,連結串列中的子連結串列必須在兩兩連線之前進行連線.最佳化器會同自底向上的方式遞迴處理這些子問題.注意:連線連結串列限制了連線順序,但沒有限制連線的實現方法或者那個關係是內表或外表,這些問題在生成訪問路徑時解決.最佳化器會為每個可行的連線方式生成訪問路徑,並選擇其中成本最低的那個.

For each planning problem, therefore, we will have a list of relations
that are either base rels or joinrels constructed per sub-join-lists.
We can join these rels together in any order the planner sees fit.
The standard (non-GEQO) planner does this as follows:

對於每一個計劃過程中出現的問題,最佳化器把每一個構成子連線連結串列(sub-join-list)的
基礎關係或連線關係儲存在一個連結串列中.最佳化器會根據看起來合適的順序連線這些關係.標準的(非遺傳演算法,即動態規劃演算法)計劃器執行以下操作:

Consider joining each RelOptInfo to each other RelOptInfo for which there
is a usable joinclause, and generate a Path for each possible join method
for each such pair. (If we have a RelOptInfo with no join clauses, we have
no choice but to generate a clauseless Cartesian-product join; so we
consider joining that rel to each other available rel. But in the presence
of join clauses we will only consider joins that use available join
clauses. Note that join-order restrictions induced by outer joins and
IN/EXISTS clauses are also checked, to ensure that we find a workable join
order in cases where those restrictions force a clauseless join to be done.)

對於每一個可用的連線條件,考慮使用兩兩連線的方式,為每一對RelOptInfo的連線生成訪問路徑.如果沒有連線條件,那麼會使用笛卡爾連線,因此會優先考慮連線其他可用的Relation.

If we only had two relations in the list, we are done: we just pick
the cheapest path for the join RelOptInfo. If we had more than two, we now
need to consider ways of joining join RelOptInfos to each other to make
join RelOptInfos that represent more than two list items.

如果連結串列中只有2個Relations,最佳化器已完成所有工作,為參與連線的RelOptInfo挑選最優的訪問路徑即可.否則的話(>3個Relations),最佳化器需要考慮如何兩兩進行連線.

The join tree is constructed using a "dynamic programming" algorithm:
in the first pass (already described) we consider ways to create join rels
representing exactly two list items. The second pass considers ways
to make join rels that represent exactly three list items; the next pass,
four items, etc. The last pass considers how to make the final join
relation that includes all list items --- obviously there can be only one
join rel at this top level, whereas there can be more than one join rel
at lower levels. At each level we use joins that follow available join
clauses, if possible, just as described for the first level.

連線樹透過"動態規劃"演算法構造:
在第一輪(先前已描述)中,最佳化器已完成兩個Relations的連線方式;第二輪,最佳化器考慮如何建立三個Relations的join rels;下一輪,四個Relations,以此類推.最後一輪,考慮如何構造包含所有Relations的join rel.顯然,在最高層,只有一個join rel,而在低層則可能會有多個join rel.在每一個層次上,如前所述,如果可以的話,最佳化器會按照可用的連線條件進行連線.

For example:
SELECT *
FROM tab1, tab2, tab3, tab4
WHERE tab1.col = tab2.col AND
tab2.col = tab3.col AND
tab3.col = tab4.col

Tables 1, 2, 3, and 4 are joined as:
{1 2},{2 3},{3 4}
{1 2 3},{2 3 4}
{1 2 3 4}
(other possibilities will be excluded for lack of join clauses)

SELECT *
FROM tab1, tab2, tab3, tab4
WHERE tab1.col = tab2.col AND
tab1.col = tab3.col AND
tab1.col = tab4.col

Tables 1, 2, 3, and 4 are joined as:
{1 2},{1 3},{1 4}
{1 2 3},{1 3 4},{1 2 4}
{1 2 3 4}

We consider left-handed plans (the outer rel of an upper join is a joinrel,
but the inner is always a single list item); right-handed plans (outer rel
is always a single item); and bushy plans (both inner and outer can be
joins themselves). For example, when building {1 2 3 4} we consider
joining {1 2 3} to {4} (left-handed), {4} to {1 2 3} (right-handed), and
{1 2} to {3 4} (bushy), among other choices. Although the jointree
scanning code produces these potential join combinations one at a time,
all the ways to produce the same set of joined base rels will share the
same RelOptInfo, so the paths produced from different join combinations
that produce equivalent joinrels will compete in add_path().

下面來看看left-handed計劃,bushy計劃和right-handed計劃.比如,在構建{1 2 3 4}4個關係的連線時,在眾多的選擇中存在以下方式,left-handed:{1 2 3} 連線 {4},right-handed:{4}連線{1 2 3}和bushy:{1 2}連線{3 4}.雖然掃描連線樹時一次產生這些潛在的連線組合,但是所有產生相同連線base rels集合的方法會共享相同的RelOptInfo的資料結構,因此這些不同的連線組合在生成等價的join rel時會呼叫add_path方法時相互PK.

The dynamic-programming approach has an important property that's not
immediately obvious: we will finish constructing all paths for a given
relation before we construct any paths for relations containing that rel.
This means that we can reliably identify the "cheapest path" for each rel
before higher-level relations need to know that. Also, we can safely
discard a path when we find that another path for the same rel is better,
without worrying that maybe there is already a reference to that path in
some higher-level join path. Without this, memory management for paths
would be much more complicated.

動態規劃方法有一個重要的特性(自底向上):那就是在構建高層RelOptInfo的訪問路徑前,下層的RelOptInfo的訪問路徑已明確,而且最佳化器確保該訪問路徑是成本最低的.

Once we have built the final join rel, we use either the cheapest path
for it or the cheapest path with the desired ordering (if that's cheaper
than applying a sort to the cheapest other path).

一旦完成了最終結果join rel的構建,存在兩條路徑:成本最低或者按排序的要求最低

If the query contains one-sided outer joins (LEFT or RIGHT joins), or
IN or EXISTS WHERE clauses that were converted to semijoins or antijoins,
then some of the possible join orders may be illegal. These are excluded
by having join_is_legal consult a side list of such "special" joins to see
whether a proposed join is illegal. (The same consultation allows it to
see which join style should be applied for a valid join, ie, JOIN_INNER,
JOIN_LEFT, etc.)

如果查詢語句存在外連線或者轉換為半連線或反連線的IN或EXISTS語句,那麼有些可能的連線順序是非法的.最佳化器透過額外的方法進行處理.

三、參考資料

README