Re: union all vs. left outer join
Date: Mon, 07 Nov 2005 22:22:53 +0100
Message-ID: <3t9uteFotv4eU1_at_individual.net>
henrik.sorensen_at_balcab.ch wrote:
> ... > > Any ideas, or hints would be much appreciated. > > Henrik > > >
Tested on postgreSQL 8
QUERY1:
SELECT *
from A
left outer join B on A.id = B.aid
left outer join C on A.id = C.aid
inner join D on
d.bid = B.id
or d.bid = C.bid
inner join E on E.id = D.eid
where E.m1 = 'A'
Query Time: 51ms + 140ms
Query Plan:
Nested Loop (cost=108.65..451.01 rows=14 width=161)
Join Filter: (("inner".bid = "outer".id) OR ("inner".bid = "outer".bid)) -> Hash Left Join (cost=58.25..123.87 rows=1230 width=71)
Hash Cond: ("outer".id = "inner".aid)
-> Hash Left Join (cost=30.75..74.84 rows=1230 width=47)
Hash Cond: ("outer".id = "inner".aid)
-> Seq Scan on a (cost=0.00..22.30 rows=1230 width=32)
-> Hash (cost=26.60..26.60 rows=1660 width=15)
-> Seq Scan on b (cost=0.00..26.60 rows=1660 width=15)
-> Hash (cost=24.00..24.00 rows=1400 width=24)
-> Seq Scan on c (cost=0.00..24.00 rows=1400 width=24)
-> Materialize (cost=50.40..50.49 rows=9 width=90)
-> Hash Join (cost=15.40..50.39 rows=9 width=90)
Hash Cond: ("outer".eid = "inner".id)
-> Seq Scan on d (cost=0.00..26.60 rows=1660 width=15)
-> Hash (cost=15.39..15.39 rows=4 width=75)
-> Index Scan using xem1 on e (cost=0.00..15.39 rows=4 width=75)
Index Cond: (m1 = 'A'::bpchar)
QUERY2:
SELECT a.*,d.*,e.*
from A
left outer join B on A.id = B.aid
inner join D on
d.bid = B.id
inner join E on E.id = D.eid
where E.m1 = 'A'
union all
SELECT a.*,d.*,e.*
from A
left outer join C on A.id = C.aid
inner join D on
d.bid = C.bid
inner join E on E.id = D.eid
where E.m1 = 'A'
Query Time: 31ms + 110ms
Append (cost=85.42..224.12 rows=14 width=122)
-> Subquery Scan "*SELECT* 1" (cost=85.42..114.01 rows=7 width=122)
-> Hash Join (cost=85.42..113.94 rows=7 width=122)
Hash Cond: ("outer".id = "inner".aid)
-> Seq Scan on a (cost=0.00..22.30 rows=1230 width=32)
-> Hash (cost=85.40..85.40 rows=9 width=94)
-> Hash Join (cost=50.41..85.40 rows=9 width=94)
Hash Cond: ("outer".id = "inner".bid)
-> Seq Scan on b (cost=0.00..26.60 rows=1660 width=8)
-> Hash (cost=50.39..50.39 rows=9 width=90)
-> Hash Join (cost=15.40..50.39 rows=9 width=90)
Hash Cond: ("outer".eid = "inner".id)
-> Seq Scan on d (cost=0.00..26.60 rows=1660
width=15)
-> Hash (cost=15.39..15.39 rows=4 width=75)
-> Index Scan using xem1 on e
(cost=0.00..15.39 rows=4 width=75)
Index Cond: (m1 = 'A'::bpchar)
-> Subquery Scan "*SELECT* 2" (cost=81.51..110.11 rows=7 width=122)
-> Hash Join (cost=81.51..110.04 rows=7 width=122)
Hash Cond: ("outer".id = "inner".aid)
-> Seq Scan on a (cost=0.00..22.30 rows=1230 width=32)
-> Hash (cost=81.49..81.49 rows=8 width=94)
-> Hash Join (cost=50.41..81.49 rows=8 width=94)
Hash Cond: ("outer".bid = "inner".bid)
-> Seq Scan on c (cost=0.00..24.00 rows=1400 width=8)
-> Hash (cost=50.39..50.39 rows=9 width=90)
-> Hash Join (cost=15.40..50.39 rows=9 width=90)
Hash Cond: ("outer".eid = "inner".id)
-> Seq Scan on d (cost=0.00..26.60 rows=1660
width=15)
-> Hash (cost=15.39..15.39 rows=4 width=75)
-> Index Scan using xem1 on e
(cost=0.00..15.39 rows=4 width=75)
Index Cond: (m1 = 'A'::bpchar)
Test on MySQL 4.1
QUERY1:
Query Time: 0.03 sec
Explain:
+--------+-------------+--------+--------+---------------+--------+---------+--------+--------+--------+
| id | select_type | table | type | possible_keys | key | key_len | ref |
rows | Extra |
+--------+-------------+--------+--------+---------------+--------+---------+--------+--------+--------+
| 1 | SIMPLE | E | ref | PRIMARY,XE,XEM1| XEM1 | 2 | const | 1
| Using where|
| 1 | SIMPLE | A | ALL | [NULL] | [NULL] | [NULL] | [NULL] | 16
| |
| 1 | SIMPLE | B | ref | XBA | XBA | 5 | test.A.id| 2
| |
| 1 | SIMPLE | C | ref | XCA | XCA | 5 | test.A.id| 2
| |
| 1 | SIMPLE | D | ALL | PRIMARY,XD,XDE| [NULL] | [NULL] | [NULL] | 3
| Range checked for each record (index map: 0x7)|
+--------+-------------+--------+--------+---------------+--------+---------+--------+--------+--------+
QUERY2:
Query Time: 0.02 sec
Explain:
+--------+-------------+--------+--------+---------------+--------+---------+--------+--------+--------+
| id | select_type | table | type | possible_keys | key | key_len | ref |
rows | Extra |
+--------+-------------+--------+--------+---------------+--------+---------+--------+--------+--------+
| 1 | PRIMARY | A | ALL | [NULL] | [NULL] | [NULL] | [NULL] | 16
| |
| 1 | PRIMARY | B | ref | XBA | XBA | 5 | test.A.id| 2
| |
| 1 | PRIMARY | D | eq_ref | PRIMARY,XD,XDE| PRIMARY| 4 | test.B.id| 1
| |
| 1 | PRIMARY | E | eq_ref | PRIMARY,XE,XEM1| PRIMARY| 4 | test.D.eid|
1 | Using where|
| 2 | UNION | A | ALL | [NULL] | [NULL] | [NULL] | [NULL] | 16
| |
| 2 | UNION | C | ref | XCA | XCA | 5 | test.A.id| 2
| |
| 2 | UNION | D | eq_ref | PRIMARY,XD,XDE| PRIMARY| 4 | test.C.bid|
1 | |
| 2 | UNION | E | eq_ref | PRIMARY,XE,XEM1| PRIMARY| 4 | test.D.eid|
1 | Using where|
| [NULL] | UNION RESULT| <union1,2>| ALL | [NULL] | [NULL] | [NULL] | [NULL] |
[NULL] | |
+--------+-------------+--------+--------+---------------+--------+---------+--------+--------+--------+
Regards
Stefan
Received on Mon Nov 07 2005 - 22:22:53 CET