1 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
3 ------------------------------------------------------------------------------
4 Merge Join (cost=0.00..90.08 rows=1000 width=16)
5 Merge Cond: (t1.id = t2.id)
6 -> Index Scan using t1_pkey on t1 (cost=0.00..318.26 rows=10000 width=8)
7 -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8)
10 EXPLAIN SELECT * FROM t1, t2 WHERE t1.val = t2.val;
12 -----------------------------------------------------------------------------------
13 Merge Join (cost=0.00..1377.64 rows=100000 width=16)
14 Merge Cond: (t2.val = t1.val)
15 -> Index Scan using t2_val on t2 (cost=0.00..55.12 rows=1000 width=8)
16 -> Materialize (cost=0.00..475.21 rows=10000 width=8)
17 -> Index Scan using t1_val on t1 (cost=0.00..450.21 rows=10000 width=8)
21 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
23 ------------------------------------------------------------------------------
24 Merge Join (cost=0.00..90.08 rows=1000 width=16)
25 Merge Cond: (t1.id = t2.id)
26 -> Index Scan using t1_pkey on t1 (cost=0.00..318.26 rows=10000 width=8)
27 -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8)
30 EXPLAIN SELECT * FROM t1, t2 WHERE t1.val = t2.val;
32 -----------------------------------------------------------------------------------
33 Merge Join (cost=0.00..1377.64 rows=100000 width=16)
34 Merge Cond: (t2.val = t1.val)
35 -> Index Scan using t2_val on t2 (cost=0.00..55.12 rows=1000 width=8)
36 -> Materialize (cost=0.00..475.21 rows=10000 width=8)
37 -> Index Scan using t1_val on t1 (cost=0.00..450.21 rows=10000 width=8)
41 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
42 INFO: hint syntax error at or near "Test (t1 t2) "
43 DETAIL: Keyword "Test" does not exist.
45 ------------------------------------------------------------------------------
46 Merge Join (cost=0.00..90.08 rows=1000 width=16)
47 Merge Cond: (t1.id = t2.id)
48 -> Index Scan using t1_pkey on t1 (cost=0.00..318.26 rows=10000 width=8)
49 -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8)
52 SET pg_hint_plan.enable TO off;
54 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
56 ------------------------------------------------------------------------------
57 Merge Join (cost=0.00..90.08 rows=1000 width=16)
58 Merge Cond: (t1.id = t2.id)
59 -> Index Scan using t1_pkey on t1 (cost=0.00..318.26 rows=10000 width=8)
60 -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8)
63 SET pg_hint_plan.enable TO on;
64 /*Set(enable_indexscan off)*/
65 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
67 ------------------------------------------------------------------
68 Hash Join (cost=27.50..220.00 rows=1000 width=16)
69 Hash Cond: (t1.id = t2.id)
70 -> Seq Scan on t1 (cost=0.00..145.00 rows=10000 width=8)
71 -> Hash (cost=15.00..15.00 rows=1000 width=8)
72 -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=8)
75 /* Set(enable_indexscan off) Set(enable_hashjoin off) */
76 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
78 --------------------------------------------------------------------
79 Merge Join (cost=874.21..894.21 rows=1000 width=16)
80 Merge Cond: (t1.id = t2.id)
81 -> Sort (cost=809.39..834.39 rows=10000 width=8)
83 -> Seq Scan on t1 (cost=0.00..145.00 rows=10000 width=8)
84 -> Sort (cost=64.83..67.33 rows=1000 width=8)
86 -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=8)
89 /* Set ( enable_indexscan off ) */
90 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
92 ------------------------------------------------------------------
93 Hash Join (cost=27.50..220.00 rows=1000 width=16)
94 Hash Cond: (t1.id = t2.id)
95 -> Seq Scan on t1 (cost=0.00..145.00 rows=10000 width=8)
96 -> Hash (cost=15.00..15.00 rows=1000 width=8)
97 -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=8)
107 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
109 ------------------------------------------------------------------
110 Hash Join (cost=27.50..220.00 rows=1000 width=16)
111 Hash Cond: (t1.id = t2.id)
112 -> Seq Scan on t1 (cost=0.00..145.00 rows=10000 width=8)
113 -> Hash (cost=15.00..15.00 rows=1000 width=8)
114 -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=8)
117 /* Set(enable_indexscan off)Set(enable_nestloop off)Set(enable_mergejoin off)
118 Set(enable_seqscan off)
120 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
122 -----------------------------------------------------------------------------------------------------
123 Hash Join (cost=20000000055.75..20000000421.51 rows=1000 width=16)
124 Hash Cond: (t1.id = t2.id)
125 -> Index Scan using t1_pkey on t1 (cost=10000000000.00..10000000318.26 rows=10000 width=8)
126 -> Hash (cost=10000000043.25..10000000043.25 rows=1000 width=8)
127 -> Index Scan using t2_pkey on t2 (cost=10000000000.00..10000000043.25 rows=1000 width=8)
130 /*Set(work_mem "1M")*/
131 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
132 INFO: invalid value for parameter "work_mem": "1M"
133 HINT: Valid units for this parameter are "kB", "MB", and "GB".
135 ------------------------------------------------------------------------------
136 Merge Join (cost=0.00..90.08 rows=1000 width=16)
137 Merge Cond: (t1.id = t2.id)
138 -> Index Scan using t1_pkey on t1 (cost=0.00..318.26 rows=10000 width=8)
139 -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8)
142 /*Set(work_mem "1MB")*/
143 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
145 ------------------------------------------------------------------------------
146 Merge Join (cost=0.00..90.08 rows=1000 width=16)
147 Merge Cond: (t1.id = t2.id)
148 -> Index Scan using t1_pkey on t1 (cost=0.00..318.26 rows=10000 width=8)
149 -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8)
152 /*Set(work_mem TO "1MB")*/
153 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
154 INFO: hint syntax error at or near ""1MB")"
155 DETAIL: Closed parenthesis is necessary.
157 ------------------------------------------------------------------------------
158 Merge Join (cost=0.00..90.08 rows=1000 width=16)
159 Merge Cond: (t1.id = t2.id)
160 -> Index Scan using t1_pkey on t1 (cost=0.00..318.26 rows=10000 width=8)
161 -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8)
164 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
166 ------------------------------------------------------------------------------
167 Merge Join (cost=0.00..90.08 rows=1000 width=16)
168 Merge Cond: (t1.id = t2.id)
169 -> Index Scan using t1_pkey on t1 (cost=0.00..318.26 rows=10000 width=8)
170 -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8)
174 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
175 INFO: hint syntax error at or near "t2)"
176 DETAIL: Closed parenthesis is necessary.
178 ------------------------------------------------------------------------------
179 Merge Join (cost=0.00..90.08 rows=1000 width=16)
180 Merge Cond: (t1.id = t2.id)
181 -> Index Scan using t1_pkey on t1 (cost=0.00..318.26 rows=10000 width=8)
182 -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8)
186 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
188 ------------------------------------------------------------------
189 Hash Join (cost=27.50..220.00 rows=1000 width=16)
190 Hash Cond: (t1.id = t2.id)
191 -> Seq Scan on t1 (cost=0.00..145.00 rows=10000 width=8)
192 -> Hash (cost=15.00..15.00 rows=1000 width=8)
193 -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=8)
196 /*SeqScan(t1)IndexScan(t2)*/
197 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
199 ----------------------------------------------------------------------------------
200 Hash Join (cost=55.75..248.25 rows=1000 width=16)
201 Hash Cond: (t1.id = t2.id)
202 -> Seq Scan on t1 (cost=0.00..145.00 rows=10000 width=8)
203 -> Hash (cost=43.25..43.25 rows=1000 width=8)
204 -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8)
208 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
210 ----------------------------------------------------------------------------
211 Nested Loop (cost=0.26..42987.50 rows=1000 width=16)
212 -> Seq Scan on t1 (cost=0.00..145.00 rows=10000 width=8)
213 -> Bitmap Heap Scan on t2 (cost=0.26..4.27 rows=1 width=8)
214 Recheck Cond: (id = t1.id)
215 -> Bitmap Index Scan on t2_pkey (cost=0.00..0.26 rows=1 width=0)
216 Index Cond: (id = t1.id)
219 /*BitmapScan(t2)NoSeqScan(t1)*/
220 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
222 ------------------------------------------------------------------------------
223 Nested Loop (cost=0.26..43160.76 rows=1000 width=16)
224 -> Index Scan using t1_pkey on t1 (cost=0.00..318.26 rows=10000 width=8)
225 -> Bitmap Heap Scan on t2 (cost=0.26..4.27 rows=1 width=8)
226 Recheck Cond: (id = t1.id)
227 -> Bitmap Index Scan on t2_pkey (cost=0.00..0.26 rows=1 width=0)
228 Index Cond: (id = t1.id)
232 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
234 ------------------------------------------------------------------
235 Hash Join (cost=27.50..220.00 rows=1000 width=16)
236 Hash Cond: (t1.id = t2.id)
237 -> Seq Scan on t1 (cost=0.00..145.00 rows=10000 width=8)
238 -> Hash (cost=15.00..15.00 rows=1000 width=8)
239 -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=8)
242 EXPLAIN SELECT * FROM t1, t4 WHERE t1.val < 10;
244 -------------------------------------------------------------------------------
245 Nested Loop (cost=20.01..203.64 rows=10000 width=16)
246 -> Bitmap Heap Scan on t1 (cost=20.01..77.51 rows=1000 width=8)
247 Recheck Cond: (val < 10)
248 -> Bitmap Index Scan on t1_val (cost=0.00..19.76 rows=1000 width=0)
249 Index Cond: (val < 10)
250 -> Materialize (cost=0.00..1.15 rows=10 width=8)
251 -> Seq Scan on t4 (cost=0.00..1.10 rows=10 width=8)
255 EXPLAIN SELECT * FROM t1, t4 WHERE t1.val < 10;
257 ---------------------------------------------------------------
258 Nested Loop (cost=0.00..296.12 rows=10000 width=16)
259 -> Seq Scan on t1 (cost=0.00..170.00 rows=1000 width=8)
261 -> Materialize (cost=0.00..1.15 rows=10 width=8)
262 -> Seq Scan on t4 (cost=0.00..1.10 rows=10 width=8)
265 EXPLAIN SELECT * FROM t3, t4 WHERE t3.id = t4.id AND t4.ctid = '(1,1)';
267 ---------------------------------------------------------------------------
268 Merge Join (cost=1.14..2.41 rows=1 width=16)
269 Merge Cond: (t3.id = t4.id)
270 -> Index Scan using t3_pkey on t3 (cost=0.00..13.75 rows=100 width=8)
271 -> Sort (cost=1.14..1.14 rows=1 width=8)
273 -> Seq Scan on t4 (cost=0.00..1.12 rows=1 width=8)
274 Filter: (ctid = '(1,1)'::tid)
278 EXPLAIN SELECT * FROM t3, t4 WHERE t3.id = t4.id AND t4.ctid = '(1,1)';
280 ---------------------------------------------------------------------------
281 Merge Join (cost=4.02..5.30 rows=1 width=16)
282 Merge Cond: (t3.id = t4.id)
283 -> Index Scan using t3_pkey on t3 (cost=0.00..13.75 rows=100 width=8)
284 -> Sort (cost=4.02..4.03 rows=1 width=8)
286 -> Tid Scan on t4 (cost=0.00..4.01 rows=1 width=8)
287 TID Cond: (ctid = '(1,1)'::tid)
290 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)';
292 ------------------------------------------------------------------------
293 Nested Loop (cost=0.00..12.29 rows=1 width=16)
294 -> Tid Scan on t1 (cost=0.00..4.01 rows=1 width=8)
295 TID Cond: (ctid = '(1,1)'::tid)
296 -> Index Scan using t2_pkey on t2 (cost=0.00..8.27 rows=1 width=8)
297 Index Cond: (id = t1.id)
301 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)';
303 ------------------------------------------------------------------------
304 Nested Loop (cost=0.00..178.28 rows=1 width=16)
305 -> Seq Scan on t1 (cost=0.00..170.00 rows=1 width=8)
306 Filter: (ctid = '(1,1)'::tid)
307 -> Index Scan using t2_pkey on t2 (cost=0.00..8.27 rows=1 width=8)
308 Index Cond: (id = t1.id)