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)
311 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
313 ------------------------------------------------------------------------------
314 Merge Join (cost=0.00..90.08 rows=1000 width=16)
315 Merge Cond: (t1.id = t2.id)
316 -> Index Scan using t1_pkey on t1 (cost=0.00..318.26 rows=10000 width=8)
317 -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8)
321 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
323 ------------------------------------------------------------------
324 Hash Join (cost=27.50..220.00 rows=1000 width=16)
325 Hash Cond: (t1.id = t2.id)
326 -> Seq Scan on t1 (cost=0.00..145.00 rows=10000 width=8)
327 -> Hash (cost=15.00..15.00 rows=1000 width=8)
328 -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=8)
332 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
334 ------------------------------------------------------------------------
335 Nested Loop (cost=0.00..607.00 rows=1000 width=16)
336 -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=8)
337 -> Index Scan using t1_pkey on t1 (cost=0.00..0.58 rows=1 width=8)
338 Index Cond: (id = t2.id)
341 /*NoMergeJoin(t1 t2)*/
342 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
344 ------------------------------------------------------------------
345 Hash Join (cost=27.50..220.00 rows=1000 width=16)
346 Hash Cond: (t1.id = t2.id)
347 -> Seq Scan on t1 (cost=0.00..145.00 rows=10000 width=8)
348 -> Hash (cost=15.00..15.00 rows=1000 width=8)
349 -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=8)
352 EXPLAIN SELECT * FROM t1, t3 WHERE t1.val = t3.val;
354 ----------------------------------------------------------------
355 Hash Join (cost=3.25..285.75 rows=10000 width=16)
356 Hash Cond: (t1.val = t3.val)
357 -> Seq Scan on t1 (cost=0.00..145.00 rows=10000 width=8)
358 -> Hash (cost=2.00..2.00 rows=100 width=8)
359 -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=8)
363 EXPLAIN SELECT * FROM t1, t3 WHERE t1.val = t3.val;
365 -----------------------------------------------------------------------------
366 Merge Join (cost=10.07..630.03 rows=10000 width=16)
367 Merge Cond: (t1.val = t3.val)
368 -> Index Scan using t1_val on t1 (cost=0.00..450.21 rows=10000 width=8)
369 -> Sort (cost=5.32..5.57 rows=100 width=8)
371 -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=8)
375 EXPLAIN SELECT * FROM t1, t3 WHERE t1.val = t3.val;
377 -------------------------------------------------------------------------
378 Nested Loop (cost=0.00..628.20 rows=10000 width=16)
379 -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=8)
380 -> Index Scan using t1_val on t1 (cost=0.00..5.01 rows=100 width=8)
381 Index Cond: (val = t3.val)
384 /*NoHashJoin(t1 t3)*/
385 EXPLAIN SELECT * FROM t1, t3 WHERE t1.val = t3.val;
387 -------------------------------------------------------------------------
388 Nested Loop (cost=0.00..628.20 rows=10000 width=16)
389 -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=8)
390 -> Index Scan using t1_val on t1 (cost=0.00..5.01 rows=100 width=8)
391 Index Cond: (val = t3.val)
394 EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
396 ------------------------------------------------------------------------------------------
397 Nested Loop (cost=1.27..1.70 rows=1 width=32)
398 -> Merge Join (cost=1.27..1.42 rows=1 width=24)
399 Merge Cond: (t1.id = t4.id)
400 -> Merge Join (cost=0.00..90.08 rows=1000 width=16)
401 Merge Cond: (t1.id = t2.id)
402 -> Index Scan using t1_pkey on t1 (cost=0.00..318.26 rows=10000 width=8)
403 -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8)
404 -> Sort (cost=1.27..1.29 rows=10 width=8)
406 -> Seq Scan on t4 (cost=0.00..1.10 rows=10 width=8)
407 -> Index Scan using t3_pkey on t3 (cost=0.00..0.27 rows=1 width=8)
408 Index Cond: (id = t1.id)
411 /*MergeJoin(t4 t1 t2 t3)*/
412 EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
414 ------------------------------------------------------------------------------------------
415 Merge Join (cost=1.27..2.83 rows=1 width=32)
416 Merge Cond: (t1.id = t4.id)
417 -> Merge Join (cost=0.00..15.03 rows=10 width=24)
418 Merge Cond: (t1.id = t3.id)
419 -> Merge Join (cost=0.00..90.08 rows=1000 width=16)
420 Merge Cond: (t1.id = t2.id)
421 -> Index Scan using t1_pkey on t1 (cost=0.00..318.26 rows=10000 width=8)
422 -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8)
423 -> Index Scan using t3_pkey on t3 (cost=0.00..13.75 rows=100 width=8)
424 -> Sort (cost=1.27..1.29 rows=10 width=8)
426 -> Seq Scan on t4 (cost=0.00..1.10 rows=10 width=8)
429 /*HashJoin(t3 t4 t1 t2)*/
430 EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
432 ------------------------------------------------------------------------------------------------
433 Hash Join (cost=1.43..3.82 rows=1 width=32)
434 Hash Cond: (t3.id = t1.id)
435 -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=8)
436 -> Hash (cost=1.42..1.42 rows=1 width=24)
437 -> Merge Join (cost=1.27..1.42 rows=1 width=24)
438 Merge Cond: (t1.id = t4.id)
439 -> Merge Join (cost=0.00..90.08 rows=1000 width=16)
440 Merge Cond: (t1.id = t2.id)
441 -> Index Scan using t1_pkey on t1 (cost=0.00..318.26 rows=10000 width=8)
442 -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8)
443 -> Sort (cost=1.27..1.29 rows=10 width=8)
445 -> Seq Scan on t4 (cost=0.00..1.10 rows=10 width=8)
448 /*NoNestLoop(t4 t1 t3 t2)*/
449 EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
451 ------------------------------------------------------------------------------------------
452 Merge Join (cost=1.27..2.83 rows=1 width=32)
453 Merge Cond: (t1.id = t4.id)
454 -> Merge Join (cost=0.00..15.03 rows=10 width=24)
455 Merge Cond: (t1.id = t3.id)
456 -> Merge Join (cost=0.00..90.08 rows=1000 width=16)
457 Merge Cond: (t1.id = t2.id)
458 -> Index Scan using t1_pkey on t1 (cost=0.00..318.26 rows=10000 width=8)
459 -> Index Scan using t2_pkey on t2 (cost=0.00..43.25 rows=1000 width=8)
460 -> Index Scan using t3_pkey on t3 (cost=0.00..13.75 rows=100 width=8)
461 -> Sort (cost=1.27..1.29 rows=10 width=8)
463 -> Seq Scan on t4 (cost=0.00..1.10 rows=10 width=8)