PostgreSQL的查询语句的连接方式与查询计划比较--多表连接（二）

来源：互联网发布：淘宝店铺转让靠谱吗编辑：程序博客网时间：2024/05/17 22:13

续：PostgreSQL的查询语句的连接方式与查询计划比较--多表连接（一）

--step5：对比step3，增加连接条件（连接条件变多但多余的条件可推导出相等故可合并）

test=# explain select * from A, B, C, D where A.c1=B.c1 and B.c1=C.c1 AND C.c1=D.c1 and A.c1=C.c1 and B.c1=D.c1;

QUERY PLAN

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

Merge Join (cost=541.37..27731.14 rows=1770590 width=48)

Merge Cond: (a.c1 = c.c1)

-> Merge Join (cost=270.68..562.65 rows=18818 width=24)

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

-> Sort (cost=135.34..140.19 rows=1940 width=12)

Sort Key: a.c1

-> Seq Scan on a (cost=0.00..29.40 rows=1940 width=12)

-> Sort (cost=135.34..140.19 rows=1940 width=12)

Sort Key: b.c1

-> Seq Scan on b (cost=0.00..29.40 rows=1940 width=12)

-> Materialize (cost=270.68..609.70 rows=18818 width=24)

-> Merge Join (cost=270.68..562.65 rows=18818 width=24)

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

-> Sort (cost=135.34..140.19 rows=1940 width=12)

Sort Key: c.c1

-> Seq Scan on c (cost=0.00..29.40 rows=1940 width=12)

-> Sort (cost=135.34..140.19 rows=1940 width=12)

Sort Key: d.c1

-> Seq Scan on d (cost=0.00..29.40 rows=1940 width=12)

(19 rows)

分析：

1) 连接条件与setp3比，查询计划没有特殊变化

2) 连接条件可以推导出：A.c1、B.c1、C.c1、D.c1是相等的，所以和step3的查询计划没有什么变化

--step6：对比step3，增加连接条件

test=# explain select * from A, B, C, D where A.c1=B.c1 and B.c1=C.c1 AND C.c1=D.c1 and A.c2=C.c2 and B.c2=D.c2;

QUERY PLAN

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

----------

Merge Join (cost=547.27..569.10 rows=44 width=48)

Merge Cond: ((a.c1 = b.c1) AND (d.c2 = b.c2))

-> Sort (cost=411.93..414.21 rows=912 width=36)

Sort Key: a.c1, d.c2

-> Hash Join (cost=301.90..367.09 rows=912 width=36)

Hash Cond: (d.c1 = a.c1)

-> Seq Scan on d (cost=0.00..29.40 rows=1940 width=12)

-> Hash (cost=300.72..300.72 rows=94 width=24)

-> Merge Join (cost=270.68..300.72 rows=94 width=24)

Merge Cond: ((a.c1 = c.c1) AND (a.c2 = c.c2))

-> Sort (cost=135.34..140.19 rows=1940 width=12)

Sort Key: a.c1, a.c2

-> Seq Scan on a (cost=0.00..29.40 rows=1940

width=12)

-> Sort (cost=135.34..140.19 rows=1940 width=12)

Sort Key: c.c1, c.c2

-> Seq Scan on c (cost=0.00..29.40 rows=1940

width=12)

-> Sort (cost=135.34..140.19 rows=1940 width=12)

Sort Key: b.c1, b.c2

-> Seq Scan on b (cost=0.00..29.40 rows=1940 width=12)

(19 rows)

分析：

1) 连接条件与setp3比，连接次序依旧是：AB->ABC->ABCD

--step6：对比step3、step4，增加新的连接条件，减少可推导出4个关系相等的连接条件

test=# explain select * from A, B, C, D where A.c1=B.c1 AND C.c1=D.c1 and A.c2=C.c2 and B.c2=D.c2;

QUERY PLAN

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

Merge Join (cost=3797.43..4168.23 rows=8853 width=48)

Merge Cond: ((a.c2 = c.c2) AND (b.c2 = d.c2))

-> Sort (cost=1898.72..1945.76 rows=18818 width=24)

Sort Key: a.c2, b.c2

-> Merge Join (cost=270.68..562.65 rows=18818 width=24)

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

-> Sort (cost=135.34..140.19 rows=1940 width=12)

Sort Key: a.c1

-> Seq Scan on a (cost=0.00..29.40 rows=1940 width=12)

-> Sort (cost=135.34..140.19 rows=1940 width=12)

Sort Key: b.c1

-> Seq Scan on b (cost=0.00..29.40 rows=1940 width=12)

-> Sort (cost=1898.72..1945.76 rows=18818 width=24)

Sort Key: c.c2, d.c2

-> Merge Join (cost=270.68..562.65 rows=18818 width=24)

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

-> Sort (cost=135.34..140.19 rows=1940 width=12)

Sort Key: c.c1

-> Seq Scan on c (cost=0.00..29.40 rows=1940 width=12)

-> Sort (cost=135.34..140.19 rows=1940 width=12)

Sort Key: d.c1

-> Seq Scan on d (cost=0.00..29.40 rows=1940 width=12)

(22 rows)

分析：

1) 与step3比，新增AC、BD之间的连接条件(A.c2=C.c2 and B.c2=D.c2)

2) 与step3比，减少可推导出AC之间相等的连接条件(B.c1=C.c1)

3) 连接条件与setp3比，连接次序是紧密树方式：AB、CD->ABCD

4) 连接算法采用的是归并连接（因为每2个关系，都依据连接条件排序）

--step7：对比step3，减少连接条件

test=# explain select * from A, B, C, D where A.c1=B.c1 and B.c1=C.c1;

QUERY PLAN

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

Nested Loop (cost=324.33..4433560.06 rows=354117900 width=48)

-> Hash Join (cost=324.33..7052.06 rows=182535 width=36)

Hash Cond: (a.c1 = c.c1)

-> Merge Join (cost=270.68..562.65 rows=18818 width=24)

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

-> Sort (cost=135.34..140.19 rows=1940 width=12)

Sort Key: a.c1

-> Seq Scan on a (cost=0.00..29.40 rows=1940 width=12)

-> Sort (cost=135.34..140.19 rows=1940 width=12)

Sort Key: b.c1

-> Seq Scan on b (cost=0.00..29.40 rows=1940 width=12)

-> Hash (cost=29.40..29.40 rows=1940 width=12)

-> Seq Scan on c (cost=0.00..29.40 rows=1940 width=12)

-> Materialize (cost=0.00..39.10 rows=1940 width=12)

-> Seq Scan on d (cost=0.00..29.40 rows=1940 width=12)

(15 rows)

分析：

1) 与setp3比，连接次序依旧是：AB->ABC->ABCD

2) 在关系D上，不存在连接条件，所以对于关系D和ABC这个新关系，做得是笛卡尔积

0 0