This repository has been archived by the owner on Sep 25, 2018. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 3
/
join.out
153 lines (140 loc) · 7.21 KB
/
join.out
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
/* We are comparing JOIN explanations (and verifying outputs)
* for tables with use_remote_estimate on and off */
/* Big joins are merge joins */
EXPLAIN (COSTS off) SELECT * FROM zips_re z1, zips_re z2 WHERE z1.city = z2.city;
QUERY PLAN
-----------------------------------------------------------------------
Merge Join
Merge Cond: ((z1.city)::text = (z2.city)::text)
-> Sort
Sort Key: z1.city
-> Foreign Scan on zips_re z1
Quasar query: SELECT `city`, `pop`, `state` FROM `zips`
-> Sort
Sort Key: z2.city
-> Foreign Scan on zips_re z2
Quasar query: SELECT `city`, `pop`, `state` FROM `zips`
(10 rows)
SELECT * FROM zips_re z1, zips_re z2 WHERE z1.city = z2.city ORDER BY z1.city, z1.pop LIMIT 10;
city | pop | state | city | pop | state
---------------+-------+-------+-------+-----+-------
AARON | 270 | KY | AARON | 270 | KY
AARONSBURG | 100 | PA | AARON | 270 | KY
ABAC | 27906 | GA | AARON | 270 | KY
ABBEVILLE | 1991 | GA | AARON | 270 | KY
ABBEVILLE | 4649 | MS | AARON | 270 | KY
ABBEVILLE | 5416 | AL | AARON | 270 | KY
ABBEVILLE | 11344 | SC | AARON | 270 | KY
ABBOT VILLAGE | 1193 | ME | AARON | 270 | KY
ABBOTSFORD | 2480 | WI | AARON | 270 | KY
ABBOTT | 577 | TX | AARON | 270 | KY
(10 rows)
/* Hash join with no sort on large table joining smaller one */
EXPLAIN (COSTS off) SELECT * FROM zips_re z1, zips_re z2 WHERE z1.pop > 60000 AND z1.city = z2.city;
QUERY PLAN
-----------------------------------------------------------------------------------------------
Hash Join
Hash Cond: ((z2.city)::text = (z1.city)::text)
-> Foreign Scan on zips_re z2
Quasar query: SELECT `city`, `pop`, `state` FROM `zips`
-> Hash
-> Foreign Scan on zips_re z1
Quasar query: SELECT `city`, `pop`, `state` FROM `zips` WHERE ((`pop` > 60000))
(7 rows)
SELECT * FROM zips_re z1, zips_re z2 WHERE z1.pop > 60000 AND z1.city = z2.city ORDER BY z1.city, z1.pop LIMIT 10;
city | pop | state | city | pop | state
---------+-------+-------+-------+-------+-------
ALTON | 67604 | TX | ALTON | 2939 | NH
ALTON | 67604 | TX | ALTON | 2557 | VA
ALTON | 67604 | TX | ALTON | 1371 | IA
ALTON | 67604 | TX | ALTON | 37541 | IL
ALTON | 67604 | TX | ALTON | 364 | KS
ALTON | 67604 | TX | ALTON | 67604 | TX
ALTON | 67604 | TX | ALTON | 159 | UT
ANAHEIM | 63622 | CA | ALTON | 2939 | NH
ANAHEIM | 63622 | CA | ALTON | 2557 | VA
ANAHEIM | 63622 | CA | ALTON | 1371 | IA
(10 rows)
/* Nested loop join when a tiny number of records */
EXPLAIN (COSTS off) SELECT * FROM zips_re z1, zips_re z2 WHERE z1.pop > 60000 AND z1.state = 'MA' AND z1.city = z2.city;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Nested Loop
-> Foreign Scan on zips_re z1
Quasar query: SELECT `city`, `pop`, `state` FROM `zips` WHERE ((`pop` > 60000)) AND ((`state` = "MA"))
-> Foreign Scan on zips_re z2
Quasar query: SELECT `city`, `pop`, `state` FROM `zips` WHERE ((:p1 = `city`))
(5 rows)
SELECT * FROM zips_re z1, zips_re z2 WHERE z1.pop > 60000 AND z1.state = 'MA' AND z1.city = z2.city ORDER BY z1.pop;
city | pop | state | city | pop | state
------------+-------+-------+------------+-------+-------
FRAMINGHAM | 65046 | MA | FRAMINGHAM | 65046 | MA
(1 row)
/* Cross Join */
EXPLAIN (COSTS off) SELECT * FROM smallzips CROSS JOIN nested;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop
-> Foreign Scan on smallzips
Quasar query: SELECT `city`, `pop`, `state` FROM `smallZips`
-> Materialize
-> Foreign Scan on nested
Quasar query: SELECT `topObj`.`midObj`.`botObj`.`a` AS `a`, `topObj`.`midObj`.`botObj`.`b` AS `b`, `topObj`.`midObj`.`botObj`.`c` AS `c` FROM `nested`
(6 rows)
SELECT * FROM smallzips CROSS JOIN nested ORDER BY city LIMIT 2;
city | pop | state | a | b | c
--------+-------+-------+---+---+---
ADAMS | 9901 | MA | m | n | o
AGAWAM | 15338 | MA | m | n | o
(2 rows)
/* Outer Join */
EXPLAIN (COSTS off) SELECT * FROM smallzips z1 LEFT OUTER JOIN zips_missing z2 ON z1.city = z2.missing;
QUERY PLAN
----------------------------------------------------------------------------
Merge Right Join
Merge Cond: ((z2.missing)::text = (z1.city)::text)
-> Sort
Sort Key: z2.missing
-> Foreign Scan on zips_missing z2
Quasar query: SELECT `city`, `missing` FROM `smallZips`
-> Sort
Sort Key: z1.city
-> Foreign Scan on smallzips z1
Quasar query: SELECT `city`, `pop`, `state` FROM `smallZips`
(10 rows)
SELECT * FROM smallzips z1 LEFT OUTER JOIN zips_missing z2 ON z1.city = z2.missing ORDER BY z1.city LIMIT 2;
city | pop | state | city | missing
--------+-------+-------+------+---------
ADAMS | 9901 | MA | |
AGAWAM | 15338 | MA | |
(2 rows)
EXPLAIN (COSTS off) SELECT * FROM smallzips z1 RIGHT OUTER JOIN zips_missing z2 ON z1.city = z2.missing;
QUERY PLAN
----------------------------------------------------------------------------
Merge Left Join
Merge Cond: ((z2.missing)::text = (z1.city)::text)
-> Sort
Sort Key: z2.missing
-> Foreign Scan on zips_missing z2
Quasar query: SELECT `city`, `missing` FROM `smallZips`
-> Sort
Sort Key: z1.city
-> Foreign Scan on smallzips z1
Quasar query: SELECT `city`, `pop`, `state` FROM `smallZips`
(10 rows)
SELECT * FROM smallzips z1 RIGHT OUTER JOIN zips_missing z2 ON z1.city = z2.missing ORDER BY z2.city LIMIT 2;
city | pop | state | city | missing
------+-----+-------+--------+---------
| | | ADAMS |
| | | AGAWAM |
(2 rows)
/* zips_re state field has a join_rowcount_estimate of 500 so it will use Hash join on some small joins */
EXPLAIN (COSTS off) SELECT * FROM zips_re z1, zips_re z2 WHERE z1.state = z2.state AND z1.city IN ('BARRE', 'AGAWAM');
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Nested Loop
-> Foreign Scan on zips_re z1
Quasar query: SELECT `city`, `pop`, `state` FROM `zips` WHERE ((`city` IN ("BARRE", "AGAWAM")))
-> Foreign Scan on zips_re z2
Quasar query: SELECT `city`, `pop`, `state` FROM `zips` WHERE ((:p1 = `state`))
(5 rows)