PostgreSQL從小白到專家，是從入門逐漸能力提升的一個系列教程，內容包括對PG基礎的認知、包括安裝使用、包括角色權限、包括維護管理、、等內容，希望對熱愛PG、學習PG的同學們有幫助，歡迎持續關注CUUG PG技術大講堂。

第30講：多表連接方式

第30講預告：9月23日(周六)19:30-20:30，釘釘群直播，群號：35822460

內容1 : Nested Loop Join連接方式

內容2 : Merge Join連接方式

內容3 : Hash Join連接方式

多表連接方式

多表連接方式

三種連接方式：

nested loop join

merge join

hash join

支持所有join操作：

NATURAL INNER JOIN

INNER JOIN

LEFT/RIGHT OUTER JOIN

FULL OUTER JOIN

嵌套循環連接方式

Nested Loop Join

嵌套循環聯接是最基本的聯接操作，它可以用于任何聯接條件。

Nested Loop Join圖解

Materialized Nested Loop Join

我們使用下面的具體示例來探索執行器如何處理具體化嵌套循環連接的計劃樹，以及如何估計成本。

testdb=# EXPLAIN SELECT * FROM tbl_a AS a, tbl_b AS b WHERE a.id = b.id;

QUERY PLAN

-----------------------------------------------------------------------

Nested Loop (cost=0.00..750230.50 rows=5000 width=16)

Join Filter: (a.id = b.id)

-> Seq Scan on tbl_a a (cost=0.00..145.00 rows=10000 width=8)

-> Materialize (cost=0.00..98.00 rows=5000 width=8)

-> Seq Scan on tbl_b b (cost=0.00..73.00 rows=5000 width=8)

(5 rows)

Materialize成本估算

(Materialized) Nested Loop成本估算

Indexed Nested Loop Join

testdb=# EXPLAIN SELECT * FROM tbl_c AS c, tbl_b AS b WHERE c.id = b.id;

QUERY PLAN

--------------------------------------------------------------------------------

Nested Loop (cost=0.29..1935.50 rows=5000 width=16)

-> Seq Scan on tbl_b b (cost=0.00..73.00 rows=5000 width=8)

-> Index Scan using tbl_c_pkey on tbl_c c (cost=0.29..0.36 rows=1 width=8)

Index Cond：（id=b.id）

（4 rows）

具有外部索引掃描的嵌套循環聯接的三種變體

Merge Join連接方式

Merge Join

Merge Join成本估算

testdb=# EXPLAIN SELECT * FROM tbl_a AS a, tbl_b AS b WHERE a.id = b.id AND b.id < 1000;

QUERY PLAN

-------------------------------------------------------------------------

Merge Join (cost=944.71..984.71 rows=1000 width=16)

Merge Cond: (a.id = b.id)

-> Sort (cost=809.39..834.39 rows=10000 width=8)

Sort Key: a.id

-> Seq Scan on tbl_a a (cost=0.00..145.00 rows=10000 width=8)

-> Sort (cost=135.33..137.83 rows=1000 width=8)

Sort Key: b.id

-> Seq Scan on tbl_b b (cost=0.00..85.50 rows=1000 width=8)

Filter: (id < 1000)

(9 rows)

Materialized Merge Join

Other Variations

強制使用merge join

testdb=# SET enable_hashjoin TO off;

testdb=# SET enable_nestloop TO off;

testdb=# EXPLAIN SELECT * FROM tbl_c AS c, tbl_b AS b WHERE c.id = b.id AND b.id < 1000;

QUERY PLAN

--------------------------------------------------------------------------------------

Merge Join (cost=135.61..322.11 rows=1000 width=16)

Merge Cond: (c.id = b.id)

-> Index Scan using tbl_c_pkey on tbl_c c (cost=0.29..318.29 rows=10000 width=8)

-> Sort (cost=135.33..137.83 rows=1000 width=8)

Sort Key: b.id

-> Seq Scan on tbl_b b (cost=0.00..85.50 rows=1000 width=8)

Filter: (id < 1000)

(7 rows)

materialized merge join with outer index scan

testdb=# SET enable_hashjoin TO off;

testdb=# SET enable_nestloop TO off;

testdb=# EXPLAIN SELECT * FROM tbl_c AS c, tbl_b AS b WHERE c.id = b.id AND b.id < 4500;

QUERY PLAN

--------------------------------------------------------------------------------------

Merge Join (cost=421.84..672.09 rows=4500 width=16)

Merge Cond: (c.id = b.id)

-> Index Scan using tbl_c_pkey on tbl_c c (cost=0.29..318.29 rows=10000 width=8)

-> Materialize (cost=421.55..444.05 rows=4500 width=8)

-> Sort (cost=421.55..432.80 rows=4500 width=8)

Sort Key: b.id

-> Seq Scan on tbl_b b (cost=0.00..85.50 rows=4500 width=8)

Filter: (id < 4500)

(8 rows)

indexed merge join with outer index scan

testdb=# SET enable_hashjoin TO off;

testdb=# SET enable_nestloop TO off;

testdb=# EXPLAIN SELECT * FROM tbl_c AS c, tbl_d AS d WHERE c.id = d.id AND d.id < 1000;

QUERY PLAN

--------------------------------------------------------------------------------------

Merge Join (cost=0.57..226.07 rows=1000 width=16)

Merge Cond: (c.id = d.id)

-> Index Scan using tbl_c_pkey on tbl_c c (cost=0.29..318.29 rows=10000 width=8)

-> Index Scan using tbl_d_pkey on tbl_d d (cost=0.28..41.78 rows=1000 width=8)

Index Cond: (id < 1000)

(5 rows)

Hash Join連接方式

Hash Join

In-Memory Hash Join

構建階段：

將內部表的所有元組插入到一個批處理中

探測階段：

將外部表的每個元組與批處理中的內部元組進行比較，如果滿足連接條件，則進行連接

Hash Join

計劃器處理轉變

預處理

1、計劃和轉換CTE(如果查詢中帶有with列表，則計劃器通過SS_process_ctes()函數處理每個with查詢)

2、向上拉子查詢

根據子查詢的特點，改為自然連接查詢。

testdb=# SELECT * FROM tbl_a AS a, (SELECT * FROM tbl_b) as b WHERE a.id = b.id;

testdb=# SELECT * FROM tbl_a AS a, tbl_b as b WHERE a.id = b.id;

3、將外部聯接轉換為內部聯接

優化器可用規則

Getting the Cheapest Path

1、表數量小于12張，應用動態規劃得到最優的計劃

2、表數量大于12張，應用遺傳查詢優化器

參數 geqo_threshold指定的閾值(默認值為12)

3、分為不同的級別層次來處理

多表查詢連接順序選擇

SGetting the Cheapest Path of a Triple-Table Query

testdb=# SELECT * FROM tbl_a AS a, tbl_b AS b, tbl_c AS c

testdb=# WHERE a.id = b.id AND b.id = c.id AND a.data < 40;

考慮3種組合：

{tbl_a,tbl_b,tbl_c}=min({tbl_a,{tbl_b,tbl_c}},{tbl_b,{tbl_a,tbl_c}},{tbl_c,{tbl_a,tbl_b}}).

創建多表查詢的計劃樹· 此查詢的EXPLAIN命令的結果如下所示