PostgreSQL支持如下几种连接方式

PostgreSQL支持如下几种连接方式：

• Nested Loop Join
• Hash Join
• Merge Join

postgres=#  create table blogtable1(id1 int, id2 int);
CREATE TABLE
postgres=# create table blogtable2(id1 int, id2 int); 
CREATE TABLE
postgres=# insert into blogtable1 values(generate_series(1,10000),3);
INSERT 0 10000
postgres=# insert into blogtable2 values(generate_series(1,1000),3);
INSERT 0 1000
postgres=# analyze;
ANALYZE

Nested Loop Join

这个方式和mysql的SNLJ一样的原理

NLJ (Nested Loop Join)是一种最简单的连接算法，每条外部关系的记录与每条内部关系的记录相匹配。条件为A.ID < B.ID的A与B之间的连接可以表示为

For each tuple r in A
        For each tuple s in B
                If (r.ID < s.ID)
                    Emit output tuple (r,s)

Nested Loop Join(NLJ)是最常用的连接方法，它几乎可以用于任何类型、任何数据集的连接子句。由于该算法需要扫描外表和内表的所有元组，因此Nested Loop Join被认为是开销最大的连接操作。

根据上面的表和数据，下面的查询会使用Nested Loop Join，如下所示：

postgres=# explain select * from blogtable1 bt1, blogtable2 bt2 where bt1.id1 < bt2.id1;
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..150162.50 rows=3333333 width=16)
   Join Filter: (bt1.id1 < bt2.id1)
   ->  Seq Scan on blogtable1 bt1  (cost=0.00..145.00 rows=10000 width=8)
   ->  Materialize  (cost=0.00..20.00 rows=1000 width=8)
         ->  Seq Scan on blogtable2 bt2  (cost=0.00..15.00 rows=1000 width=8)
(5 rows)

因为连接子句是“<”，所以这里唯一可能的连接方法即是Nested Loop Join。注意这里出现了一个新类型的节点：Materialize，该节点作为中间结果缓存，用于替代多次获取一个表中的所有元组，Materialize会将第一次获取的结果集存储在内存中，下一次请求获取元组时将直接从内存中获取，而不是再次从表中数据页内获取。

有些查询可能没有连接子句，在这种情况下，连接的唯一选择就是Nested Loop Join(NLJ)。例如，根据之前的数据考虑以下查询：

postgres=#  explain select * from blogtable1, blogtable2;
                                QUERY PLAN                                
--------------------------------------------------------------------------
 Nested Loop  (cost=0.00..125162.50 rows=10000000 width=16)
   ->  Seq Scan on blogtable1  (cost=0.00..145.00 rows=10000 width=8)
   ->  Materialize  (cost=0.00..20.00 rows=1000 width=8)
         ->  Seq Scan on blogtable2  (cost=0.00..15.00 rows=1000 width=8)
(4 rows)

Hash Join

构建阶段：使用内表记录构建一个哈希表，散列键根据连接子句计算得到 （1）遍历内表B的每一条元组r

（2）将r.id插入到hashtab中

探测阶段：外表记录将基于连接子句键进行散列，以在散列中找到匹配的条目

（1）遍历外表A的每一条元组s，进行散列

（2）如果s.id=r.id,输出元组（r,s）

hash表取决于work_mem;

postgres=# explain select * from blogtable1 bt1, blogtable2 bt2 where bt1.id1 = bt2.id1;
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Hash Join  (cost=27.50..220.00 rows=1000 width=16)
   Hash Cond: (bt1.id1 = bt2.id1)
   ->  Seq Scan on blogtable1 bt1  (cost=0.00..145.00 rows=10000 width=8)
   ->  Hash  (cost=15.00..15.00 rows=1000 width=8)
         ->  Seq Scan on blogtable2 bt2  (cost=0.00..15.00 rows=1000 width=8)
(5 rows)

这里，基于表blogtable2创建了哈希表，因为它是一个较小的表，所以需要的内存也较少，这样整个哈希表页可以完全放入内存中。

Merge join

如果两个表上都创建了索引，那么走hash join 的示例查询可能会使用merge join这是因为由于索引，表中的数据可以按顺序检索。

Merje join是一种，每条外表的记录与每条内表的记录相匹配，直到有可能匹配到连接子句为止的算法。此连接算法仅在两个表都已排序并且连接子句操作符为“=”时才使用。连接条件为A.ID = B.ID，那么表A与B之间的连接可以表示为：

postgres=# 
postgres=# create index idx1 on blogtable1(id1);
CREATE INDEX
postgres=# create index idx2 on blogtable2(id1);
CREATE INDEX
postgres=# explain select * from blogtable1 bt1, blogtable2 bt2 where bt1.id1 = bt2.id1;
                                      QUERY PLAN                                       
---------------------------------------------------------------------------------------
 Merge Join  (cost=0.56..90.36 rows=1000 width=16)
   Merge Cond: (bt1.id1 = bt2.id1)
   ->  Index Scan using idx1 on blogtable1 bt1  (cost=0.29..318.29 rows=10000 width=8)
   ->  Index Scan using idx2 on blogtable2 bt2  (cost=0.28..43.27 rows=1000 width=8)
(4 rows)

两个表都使用索引扫描而不是顺序扫描，因为这两个表都将返回已排序的记录。

配置join方式

postgres=# select name,setting from pg_settings where name like '%enable%';
              name              | setting 
--------------------------------+---------
 enable_bitmapscan              | on
 enable_gathermerge             | on
 enable_hashagg                 | on
 enable_hashjoin                | on
 enable_indexonlyscan           | on
 enable_indexscan               | on
 enable_material                | on
 enable_mergejoin               | on
 enable_nestloop                | on
 enable_parallel_append         | on
 enable_parallel_hash           | on
 enable_partition_pruning       | on
 enable_partitionwise_aggregate | off
 enable_partitionwise_join      | off
 enable_seqscan                 | on
 enable_sort                    | on
 enable_tidscan                 | on
(17 rows)

PostgreSQL支持多种与规划器相关的配置，这些配置可用于提示查询优化器不要选择某些特定类型的连接方法。如果优化器选择的连接方法不是最优的，那么可以关闭这些配置参数，以强制查询优化器选择不同类型的连接方法。默认情况下，所有这些配置参数都是开启的。下面是对于特定连接方法的规划器配置参数：

• enable nestloop：对应Nested Loop Join
• enable hashjoin：对应Hash Join
• enable mergejoin：对应Merge Join

set enable_seqscan to off;
alter user postgres set enable_seqscan to off;