PG数据库中的表连接方式及应用场景

来源：白鳝的洞穴

关系型数据库的表连接最常用的有三种方式，NESTED LOOP，HASH JOIN和MERGE JOIN(SORT MERGE JOIN,SMJ)。在Oracle数据库中，DBA也经常被这三种JOIN给折腾的够呛，一旦优化器因为统计信息的不准确出现了JOIN类型选择错误，那么对系统性能来说，就没法看了。前阵子有朋友问我这三种JOIN在什么场合使用。实际上PG数据库对这三种JOIN的支持都还不错，今天早上事情比较多，我就写个简单点的文章，利用一个INNER JOIN的SQL来给大家解释一下这三种JOIN模式，并且大致分析一下其应用场景吧。

DROP TABLE JOIN1;

DROP TABLE JOIN2;

create table join1 (id integer,name varchar(300));

create table join2 (id integer,name varchar(300),score integer);

insert into join1 values

( generate_series(1,10000),

'aaaaaaaaaaaaaaaaAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAaaAAAAAAAAAAAAAAAAAAAAaaaaaaaAAAAAAAAAAAAAAAAAAAAA');

insert into join1 values

( generate_series(50001,51000),

'aaaaaaaaaaaaaaaaAAAAAAAAAAAAAAAAAAAAAASSSSSAAAAAAAAAAAAAAAAaaAAAAAAAAAAAAAAAAAAAAaaaaaaaAAAAAAAAAAAAAAAAAAAAA');

insert into join2 values

( generate_series(1,10000),'

aaaaaaaaaaaaaaaaAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAaaAAAAAAAAAAAAAAAAAAAAaaaaaaaAAAAAAAAAAAAAAAAAAAAA',1);

insert into join2 values

( generate_series(1,10000),

'aaaaaaaaaaaaaaaaAAAAAAABBAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAaaAAAAAAAAAAAAAAAAAAAAaaaaaaaAAAAAAAAAAAAAAAAAAAAA',2);

insert into join2 values

( generate_series(20001,22000),

'aaaaaaaaaaaaaaaaAACCCCAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAaaAAAAAAAAAAAAAAAAAAAAaaaaaaaAAAAAAAAAAAAAAAAAAAAA',3);

VACUUM ANALYZE JOIN1;

VACUUM ANALYZE JOIN2;