#### 3.4執行器如何工作

單表查詢的例子，執行器從計劃樹取出計劃節點，按照自底向上方的順序進行處理，並調取節點相應的函式。這些函式在src/backend/executor目錄中。如執行順序掃描的函式(seqscan)定義在nodeSeqscan.c中，索引掃描(IndexScanNode)在nodeIndexScan.cSortNode節點對應的排序函式在nodeSort.c中。

mydb=# explain select * from tb1_1 where id <300 order by data;
                          QUERY PLAN                           
---------------------------------------------------------------
 4.Sort  (cost=182.34..183.09 rows=300 width=8)
 5.  Sort Key: data
 6.  ->  Seq Scan on tb1_1  (cost=0.00..170.00 rows=300 width=8)
 7.        Filter: (id < 300)
 8. (4 rows)

--第六行,執行器透過函式執行順序掃描
--第四行，透過nodeSort.c中定義的函式對順序掃描的結果進行拍照
```

> 執行器處理查詢時會使用工作記憶體和臨時緩衝區，都在記憶體中分配。如果無法在記憶體完成，就會用臨時檔案，走磁碟

```
--可以透過以下命令實際執行，檢視
explain analyze select id,data from tb1_25m order by id;

--如果因記憶體不足，生成臨時檔案，在base/pg_tmp子目錄中。命名規則：
--"pgsql_tmp"+建立文字檔案的Postgres程式的PID.{從0開始的序列號}
```

#### 3.5連線
pg支援三種連線操作，分別是巢狀巡迴連線、並歸連線和雜湊連線

環境準備：

```
mydb=# select version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 10.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-18), 64-bit
(1 row)

--建立表
create table tb1_a(id int primary key,data int);
create table tb1_b(id int primary key,data int);
--插入資料
insert into tb1_a select generate_series(1,10000),generate_series(1,10000);
insert into tb1_b select generate_series(1,10000),generate_series(1,10000);
```

##### 巢狀迴圈(Nested Loop)

```
--下述語句預設先走hash，再走merge，所以先關閉
set enable_hashjoin=off;
set enable_mergejoin=off;

--檢視執行計劃,物化操作沒有啟動成本，其他有
mydb=# explain select * from tb1_a as a, tb1_b as b where a.data=b.data;
                              QUERY PLAN                               
-----------------------------------------------------------------------
4. Nested Loop  (cost=0.00..375158.50 rows=5000 width=16)
5.   Join Filter: (a.data = b.data)
6.   ->  Seq Scan on tb1_a a  (cost=0.00..73.00 rows=5000 width=8)
7.   ->  Materialize  (cost=0.00..98.00 rows=5000 width=8)
8.         ->  Seq Scan on tb1_b b  (cost=0.00..73.00 rows=5000 width=8)
(5 rows)
```

第七行 執行器使用順序掃描，物化內部表tb1_b；

第四行，執行執行巢狀順序連線操作，外表是tb1_a,內表是物化的tb1_b.

> 物化節點意味著在執行上層節點之前，將樹中其下方任何內容的輸出（可以是掃描，或者是完整的聯接之類的東西）儲存到記憶體中。通常，當外部節點需要出於某種原因可以重新掃描的源時，便可以執行此操作。

其他，如果再外表上存在一個與連線條件相關的索引，那麼在外表上也可以以索引掃描代替順序掃描。特別是當where子句中的訪問謂詞可以使用該索引時，能縮小外表上的搜尋範圍，巢狀迴圈連線的代價可能會急劇減少。如：

```
mydb=# explain select * from tb1_a as a, tb1_b as b where a.id=b.data and a.id=100;
                                   QUERY PLAN                                   
--------------------------------------------------------------------------------
 Nested Loop  (cost=0.28..93.81 rows=1 width=16)
   ->  Index Scan using tb1_a_pkey on tb1_a a  (cost=0.28..8.30 rows=1 width=8)
         Index Cond: (id = 100)
   ->  Seq Scan on tb1_b b  (cost=0.00..85.50 rows=1 width=8)
         Filter: (data = 100)
(5 rows)

```

##### 歸併連線(merge join)

與巢狀連線不同， 歸併連線只能用於自然連線和等值連線
```
set enable_mergejoin=on;

mydb=# explain select * from tb1_a as a,tb1_b as b where a.data=b.data and b.data<1000;
                              QUERY PLAN                               
-----------------------------------------------------------------------
4. Merge Join  (cost=515.52..555.52 rows=1000 width=16)
5.   Merge Cond: (a.data = b.data)
6.   ->  Sort  (cost=380.19..392.69 rows=5000 width=8)
7.        Sort Key: a.data
8.         ->  Seq Scan on tb1_a a  (cost=0.00..73.00 rows=5000 width=8)
9.   ->  Sort  (cost=135.33..137.83 rows=1000 width=8)
10         Sort Key: b.data
         ->  Seq Scan on tb1_b b  (cost=0.00..85.50 rows=1000 width=8)
               Filter: (data < 1000)
(9 rows)

--9行 執行器對內表tb1_b進行順序掃描
--6行，對外表tb1_a進行順序掃描
--4行，執行器歸併連線操作，內外表都是排好序的
```

其他如外表相關列建立索引，內表相關列建立索引等。物化歸併連線與普通歸併連線基本無差異，區別在Materialize，對tb1_b排好序的結果進行物化。

##### 雜湊連線（Hash join）
  只能用於自然連線和等值連線
  
PostgreSQL中的雜湊連線的行為因表的大小而異。如果目標表足夠小（確切地說，內表大小不超過工作記憶體的25%），那麼雜湊連線就是簡單的兩階段記憶體雜湊連線，否則將會使用帶傾斜批次的混合雜湊連線。

###### 記憶體雜湊連線
記憶體中的雜湊連線是在work_mem中處理的，在PostgreSQL中，雜湊表區域被稱作處理批次。一個處理批次會有多個雜湊槽，內部稱其為桶，桶的數量由nodeHash.c 中定義的ExecChooseHashTableSize()函式所確定。桶的數量總是2的整數次冪。

記憶體雜湊連線有兩個階段，分別是構建階段和探測階段。在構建階段，內表中的所有元組都會被插入到處理批次中；在探測階段，每條外表元組都會與處理批次中的內表元組比較，如果滿足連線條件，則將兩條元組連線起來。

###### 帶傾斜的混合雜湊連線
當內表的元組無法全部儲存在工作記憶體中的單個處理批次時，PostgreSQL使用帶傾斜批次的混合雜湊連線演算法，該演算法是混合雜湊連線的一種變體。

在第一個構建和探測階段，PostgreSQL準備多個批次。與桶的數目類似，處理批次的數目由函式ExecChooseHashTableSize()決定，也就是2的整數次冪。工作記憶體中只會分配一個處理批次，而其他批次都以臨時檔案的形式建立。屬於這些批次的元組將透過臨時元組儲存功能被寫入到相應的檔案中.

##### 連線訪問路徑與連線節點

巢狀迴圈連線的訪問路徑由 JoinPath 結構表示，其他連線訪問路徑，諸如MergePath與HashPath都基於其實現。

如圖3.30（page73)

###### 連線節點
本節列出了三種連線節點，分別是NestedLoopNode、MergeJoinNode和HashJoinNode，它們都基於JoinNode實現.

```
#/pgdata/pgsql/include/postgresql/server/nodes/plannodes.h

/* ----------------
 *              Join node
 *
 * jointype:    rule for joining tuples from left and right subtrees
 * inner_unique each outer tuple can match to no more than one inner tuple
 * joinqual:    qual conditions that came from JOIN/ON or JOIN/USING
 *                              (plan.qual contains conditions that came from WHERE)
 *
 * When jointype is INNER, joinqual and plan.qual are semantically
 * interchangeable.  For OUTER jointypes, the two are *not* interchangeable;
 * only joinqual is used to determine whether a match has been found for
 * the purpose of deciding whether to generate null-extended tuples.
 * (But plan.qual is still applied before actually returning a tuple.)
 * For an outer join, only joinquals are allowed to be used as the merge
 * or hash condition of a merge or hash join.
 *
 * inner_unique is set if the joinquals are such that no more than one inner
 * tuple could match any given outer tuple.  This allows the executor to
 * skip searching for additional matches.  (This must be provable from just
 * the joinquals, ignoring plan.qual, due to where the executor tests it.)
 * ----------------
 */
typedef struct Join
{
        Plan            plan;
        JoinType        jointype;
        bool            inner_unique;
        List       *joinqual;           /* JOIN quals (in addition to plan.qual) */
} Join;

/* ----------------
 *              nest loop join node
 *
 * The nestParams list identifies any executor Params that must be passed
 * into execution of the inner subplan carrying values from the current row
 * of the outer subplan.  Currently we restrict these values to be simple
 * Vars, but perhaps someday that'd be worth relaxing.  (Note: during plan
 * creation, the paramval can actually be a PlaceHolderVar expression; but it
 * must be a Var with varno OUTER_VAR by the time it gets to the executor.)
 * ----------------
 */
typedef struct NestLoop
{
        Join            join;
        List       *nestParams;         /* list of NestLoopParam nodes */
} NestLoop;

typedef struct NestLoopParam
{
        NodeTag         type;
        int                     paramno;                /* number of the PARAM_EXEC Param to set */
        Var                *paramval;           /* outer-relation Var to assign to Param */
} NestLoopParam;

/* ----------------
 *              merge join node
 *
 * The expected ordering of each mergeable column is described by a btree
 * opfamily OID, a collation OID, a direction (BTLessStrategyNumber or
 * BTGreaterStrategyNumber) and a nulls-first flag.  Note that the two sides
 * of each mergeclause may be of different datatypes, but they are ordered the
 * same way according to the common opfamily and collation.  The operator in
 * each mergeclause must be an equality operator of the indicated opfamily.
 * ----------------
 */
typedef struct MergeJoin
{
        Join            join;
        bool            skip_mark_restore;      /* Can we skip mark/restore calls? */
        List       *mergeclauses;       /* mergeclauses as expression trees */
        /* these are arrays, but have the same length as the mergeclauses list: */
        Oid                *mergeFamilies;      /* per-clause OIDs of btree opfamilies */
        Oid                *mergeCollations;    /* per-clause OIDs of collations */
        int                *mergeStrategies;    /* per-clause ordering (ASC or DESC) */
        bool       *mergeNullsFirst;    /* per-clause nulls ordering */
} MergeJoin;

/* ----------------
 *              hash join node
 * ----------------
 */
typedef struct HashJoin
{
        Join            join;
        List       *hashclauses;
} HashJoin;
```

- 如果聯接的一側只有很少的行，則首選巢狀迴圈聯接。如果聯接條件不使用相等運算子，則巢狀迴圈聯接也將用作唯一選項。
- 雜湊聯接如果聯接條件使用相等運算子並且聯接的兩端都很大並且雜湊適合work_mem，則首選雜湊聯接。
- 合併聯接如果聯接條件使用相等運算子並且聯接的兩端都很大，則首選，但可以有效地對聯接條件進行排序(例如，如果聯接列中使用的表示式上有索引) )。

#### 3.6建立多表查詢計劃樹

預處理由planner.c中定義的subquery_planner()函式執行。

【上拉查詢】如果FROM子句帶有一個子查詢，且該子查詢沒有用到GROUP BY、HAVING、ORDER BY、LIMIT和DISTINCT、INTERSECT或EXCEPT，那麼計劃器就會使用pull_up_subqueries()函式將其轉換為連線形式。例如下面一個 FROM 子句含子查詢的查詢就可以被轉換為自然連線查詢。自不必說，這種轉換是在查詢樹上進行的。

【獲取代價最小的路徑】

為了獲取最佳計劃樹，計劃器必須考慮各個索引與各種連線方法之間的所有可能組合。如果表的數量小於12張，計劃器可以使用動態規劃來獲取最佳計劃，否則計劃器會使用遺傳演算法。

以下是使用動態規劃確定最佳計劃樹的過程：
- 第一層：獲得每張表上代價最小的路徑，其儲存在表相應的RelOptInfo結構中
- 第二層：所有表中選擇兩種表，為每種組合找出代價最低的路徑
- 第三層及其後：繼續進行同樣處理，直到層級等於表數量

**本節英文版**

https://www.interdb.jp/pg/pgsql03.html

【讀書筆記】Postgresql連線方法及執行器

相關文章