PostgreSQL的查询语句的连接方式与查询计划比较--简单语句

--Step1：准备数据，创建表，插入数据，注意数据的设计，每个表是3种情况，目的是看字段为NULL时，各种连接的处理方式

test=# create table A (c1 int, c2 int);
CREATE TABLE
test=# create table B (c1 int, c2 int);
CREATE TABLE
test=#
test=# insert into A values(11,21);
INSERT 0 1
test=# insert into A values(NULL,22);
INSERT 0 1
test=# insert into A values(13,NULL);
INSERT 0 1
test=#
test=# insert into B values(11,21);
INSERT 0 1
test=# insert into B values(12,22);
INSERT 0 1
test=# insert into B values(NULL,23);
INSERT 0 1

 

--step2：各种查询，注意对比A、B表中不同数据在不同的连接方式下，其结果的不同之处
test=# select * from A join B on A.c1=B.c1;
 c1 | c2 | c1 | c2
----+----+----+----
 11 | 21 | 11 | 21
(1 row)

test=# select * from A cross join B ;
 c1 | c2 | c1 | c2
----+----+----+----
 11 | 21 | 11 | 21
 11 | 21 | 12 | 22
 11 | 21 |    | 23
    | 22 | 11 | 21
    | 22 | 12 | 22
    | 22 |    | 23
 13 |    | 11 | 21
 13 |    | 12 | 22
 13 |    |    | 23
(9 rows)

test=# select * from A left join B on A.c1=B.c1;
 c1 | c2 | c1 | c2
----+----+----+----
 11 | 21 | 11 | 21
 13 |    |    |
    | 22 |    |
(3 rows)

--说明：左连接，左表中为NULL得以保留

test=# select * from A right join B on A.c1=B.c1;
 c1 | c2 | c1 | c2
----+----+----+----
 11 | 21 | 11 | 21
    |    | 12 | 22
    |    |    | 23
(3 rows)
说明：右连接，右表中为NULL得以保留

test=# select * from A full join B on A.c1=B.c1;
 c1 | c2 | c1 | c2
----+----+----+----
 11 | 21 | 11 | 21
    |    | 12 | 22
    |    |    | 23
 13 |    |    |
    | 22 |    |
(5 rows)
--说明：全外连接，左、右表中为NULL得以保留

--step3：比较查询计划的不同（粉色字体，用以方便与step5得出的查询计划比较不同之处）
test=# explain select * from A join B on A.c1=B.c1;
                           QUERY PLAN
-----------------------------------------------------------------
 Merge Join  (cost=299.56..653.73 rows=22898 width=16)
   Merge Cond: (a.c1 = b.c1)
   ->  Sort  (cost=149.78..155.13 rows=2140 width=8)
         Sort Key: a.c1
         ->  Seq Scan on a  (cost=0.00..31.40 rows=2140 width=8)
   ->  Sort  (cost=149.78..155.13 rows=2140 width=8)
         Sort Key: b.c1
         ->  Seq Scan on b  (cost=0.00..31.40 rows=2140 width=8)
(8 rows)

test=# explain select * from A cross join B ;
                           QUERY PLAN
-----------------------------------------------------------------
 Nested Loop  (cost=0.00..57313.15 rows=4579600 width=16)
   ->  Seq Scan on a  (cost=0.00..31.40 rows=2140 width=8)
   ->  Materialize  (cost=0.00..42.10 rows=2140 width=8)
         ->  Seq Scan on b  (cost=0.00..31.40 rows=2140 width=8)
(4 rows)


test=# explain select * from A left join B on A.c1=B.c1;
                           QUERY PLAN
-----------------------------------------------------------------
 Merge Left Join  (cost=299.56..653.73 rows=22898 width=16)
   Merge Cond: (a.c1 = b.c1)
   ->  Sort  (cost=149.78..155.13 rows=2140 width=8)
         Sort Key: a.c1
         ->  Seq Scan on a  (cost=0.00..31.40 rows=2140 width=8)
   ->  Sort  (cost=149.78..155.13 rows=2140 width=8)
         Sort Key: b.c1
         ->  Seq Scan on b  (cost=0.00..31.40 rows=2140 width=8)
(8 rows)

test=# explain select * from A right join B on A.c1=B.c1;
                           QUERY PLAN
-----------------------------------------------------------------
 Merge Left Join  (cost=299.56..653.73 rows=22898 width=16)
   Merge Cond: (b.c1 = a.c1)
   ->  Sort  (cost=149.78..155.13 rows=2140 width=8)
         Sort Key: b.c1
         ->  Seq Scan on b  (cost=0.00..31.40 rows=2140 width=8)
   ->  Sort  (cost=149.78..155.13 rows=2140 width=8)
         Sort Key: a.c1
         ->  Seq Scan on a  (cost=0.00..31.40 rows=2140 width=8)
(8 rows)

test=# explain select * from A full join B on A.c1=B.c1;
                           QUERY PLAN
-----------------------------------------------------------------
 Merge Full Join  (cost=299.56..653.73 rows=22898 width=16)
   Merge Cond: (a.c1 = b.c1)
   ->  Sort  (cost=149.78..155.13 rows=2140 width=8)
         Sort Key: a.c1
         ->  Seq Scan on a  (cost=0.00..31.40 rows=2140 width=8)
   ->  Sort  (cost=149.78..155.13 rows=2140 width=8)
         Sort Key: b.c1
         ->  Seq Scan on b  (cost=0.00..31.40 rows=2140 width=8)
(8 rows)

--step4:对表做分析，目的是获得表属性的实际的数据（如表的真实行数），便于查询优化器获得真实数据，更好进行代价计算
test=# analyze;
ANALYZE

 

step5：查看analyze之后的查询计划，比较step3得到的查询计划，比较不同点（红色标识不同点，没有全部标出，可自行对照查看不同）
test=# explain select * from A join B on A.c1=B.c1;
                         QUERY PLAN
-------------------------------------------------------------
 Hash Join  (cost=1.07..2.12 rows=1 width=16)
   Hash Cond: (a.c1 = b.c1)
   ->  Seq Scan on a  (cost=0.00..1.03 rows=3 width=8)
   ->  Hash  (cost=1.03..1.03 rows=3 width=8)
         ->  Seq Scan on b  (cost=0.00..1.03 rows=3 width=8)
(5 rows)


test=# explain select * from A cross join B ;
                         QUERY PLAN
-------------------------------------------------------------
 Nested Loop  (cost=0.00..2.18 rows=9 width=16)
   ->  Seq Scan on a  (cost=0.00..1.03 rows=3 width=8)
   ->  Materialize  (cost=0.00..1.04 rows=3 width=8)
         ->  Seq Scan on b  (cost=0.00..1.03 rows=3 width=8)
(4 rows)

test=# explain select * from A left join B on A.c1=B.c1;
                         QUERY PLAN
-------------------------------------------------------------
 Hash Left Join  (cost=1.07..2.12 rows=3 width=16)
   Hash Cond: (a.c1 = b.c1)
   ->  Seq Scan on a  (cost=0.00..1.03 rows=3 width=8)
   ->  Hash  (cost=1.03..1.03 rows=3 width=8)
         ->  Seq Scan on b  (cost=0.00..1.03 rows=3 width=8)
(5 rows)

test=# explain select * from A right join B on A.c1=B.c1;
                         QUERY PLAN
-------------------------------------------------------------
 Hash Left Join  (cost=1.07..2.12 rows=3 width=16)
   Hash Cond: (b.c1 = a.c1)
   ->  Seq Scan on b  (cost=0.00..1.03 rows=3 width=8)
   ->  Hash  (cost=1.03..1.03 rows=3 width=8)
         ->  Seq Scan on a  (cost=0.00..1.03 rows=3 width=8)
(5 rows)


test=# explain select * from A full join B on A.c1=B.c1;
                         QUERY PLAN
-------------------------------------------------------------
 Merge Full Join  (cost=2.11..2.15 rows=3 width=16)
   Merge Cond: (a.c1 = b.c1)
   ->  Sort  (cost=1.05..1.06 rows=3 width=8)
         Sort Key: a.c1
         ->  Seq Scan on a  (cost=0.00..1.03 rows=3 width=8)
   ->  Sort  (cost=1.05..1.06 rows=3 width=8)
         Sort Key: b.c1
         ->  Seq Scan on b  (cost=0.00..1.03 rows=3 width=8)
(8 rows)

 

--step6：比较“explain analyze”和“explain”（step3）得出的查询计划的不同
test=# explain analyze select * from A join B on A.c1=B.c1;
                                              QUERY PLAN

--------------------------------------------------------------------------------
-----------------------
 Hash Join  (cost=1.07..2.12 rows=1 width=16) (actual time=0.094..0.109 rows=1 l
oops=1)
   Hash Cond: (a.c1 = b.c1)
   ->  Seq Scan on a  (cost=0.00..1.03 rows=3 width=8) (actual time=0.012..0.018
 rows=3 loops=1)
   ->  Hash  (cost=1.03..1.03 rows=3 width=8) (actual time=0.029..0.029 rows=2 l
oops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 1kB
         ->  Seq Scan on b  (cost=0.00..1.03 rows=3 width=8) (actual time=0.005.
.0.013 rows=3 loops=1)
 Total runtime: 0.220 ms
(7 rows)

 

--step7：比较join和普通where条件的不同
test=# select * from A, B where A.c1=B.c1;--与“select * from A join B on A.c1=B.c1;”的结果，没有什么不同
 c1 | c2 | c1 | c2
----+----+----+----
 11 | 21 | 11 | 21
(1 row)

test=# explain select * from A, B where A.c1=B.c1;--查询计划，也没有什么不同
                         QUERY PLAN
-------------------------------------------------------------
 Hash Join  (cost=1.07..2.12 rows=1 width=16)
   Hash Cond: (a.c1 = b.c1)
   ->  Seq Scan on a  (cost=0.00..1.03 rows=3 width=8)
   ->  Hash  (cost=1.03..1.03 rows=3 width=8)
         ->  Seq Scan on b  (cost=0.00..1.03 rows=3 width=8)
(5 rows)