1 CREATE TABLE t1 (val1 int, val2 int);
2 CREATE TABLE t2 (val1 int, val2 int);
3 CREATE TABLE t3 (val1 int, val2 int);
4 CREATE TABLE t4 (val1 int, val2 int);
5 CREATE VIEW v1 AS SELECT val1, val2 FROM t1;
6 CREATE VIEW v2 AS SELECT t1.val1 t1_val1, t1.val2 t1_val2, t2.val1 t2_val1, t2.val2 t2_val2 FROM t1, t2 WHERE t1.val1 = t2.val1;
7 CREATE VIEW v3 AS SELECT t_1.val1 t1_val1, t_1.val2 t1_val2, t_2.val1 t2_val1, t_2.val2 t2_val2 FROM t1 t_1, t2 t_2 WHERE t_1.val1 = t_2.val1;
8 CREATE VIEW v4 AS SELECT v_2.t1_val1, t_3.val1 FROM v2 v_2, t3 t_3 WHERE v_2.t1_val1 = t_3.val1;
9 INSERT INTO t1 SELECT i, i FROM (SELECT generate_series(1, 10000) i) t;
10 INSERT INTO t2 SELECT i, i FROM (SELECT generate_series(1, 1000) i) t;
11 INSERT INTO t3 SELECT i, i FROM (SELECT generate_series(1, 100) i) t;
12 INSERT INTO t4 SELECT i, i FROM (SELECT generate_series(1, 10) i) t;
13 CREATE INDEX t1_val1 ON t1 (val1);
14 CREATE INDEX t2_val1 ON t2 (val1);
15 CREATE INDEX t3_val1 ON t3 (val1);
16 CREATE INDEX t4_val1 ON t4 (val1);
21 \set t1_oid `psql contrib_regression -tA -c "SELECT oid FROM pg_class WHERE relname = 't1'"`
22 \set t2_oid `psql contrib_regression -tA -c "SELECT oid FROM pg_class WHERE relname = 't2'"`
23 \set t3_oid `psql contrib_regression -tA -c "SELECT oid FROM pg_class WHERE relname = 't3'"`
24 \set t4_oid `psql contrib_regression -tA -c "SELECT oid FROM pg_class WHERE relname = 't4'"`
25 --SET enable_bitmapscan TO off;
26 --SET enable_hashagg TO off;
27 --SET enable_tidscan TO off;
28 --SET enable_sort TO off;
29 --SET enable_indexscan TO off;
30 --SET enable_seqscan TO off;
31 --SET enable_material TO off;
32 --SET enable_hashjoin TO off;
33 --SET enable_mergejoin TO off;
34 --SET enable_nestloop TO off;
35 EXPLAIN SELECT * FROM t1, t2 WHERE t1.val1 = t2.val1;
37 ------------------------------------------------------------------------------
38 Merge Join (cost=0.00..90.08 rows=1000 width=16)
39 Merge Cond: (t1.val1 = t2.val1)
40 -> Index Scan using t1_val1 on t1 (cost=0.00..318.26 rows=10000 width=8)
41 -> Index Scan using t2_val1 on t2 (cost=0.00..43.25 rows=1000 width=8)
44 EXPLAIN SELECT * FROM t1, t2 WHERE t1.val2 = t2.val2;
46 ------------------------------------------------------------------
47 Hash Join (cost=27.50..220.00 rows=1000 width=16)
48 Hash Cond: (t1.val2 = t2.val2)
49 -> Seq Scan on t1 (cost=0.00..145.00 rows=10000 width=8)
50 -> Hash (cost=15.00..15.00 rows=1000 width=8)
51 -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=8)
54 CREATE EXTENSION pg_hint_plan;
55 EXPLAIN SELECT * FROM t1, t2 WHERE t1.val1 = t2.val1;
57 ------------------------------------------------------------------------------
58 Merge Join (cost=0.00..90.08 rows=1000 width=16)
59 Merge Cond: (t1.val1 = t2.val1)
60 -> Index Scan using t1_val1 on t1 (cost=0.00..318.26 rows=10000 width=8)
61 -> Index Scan using t2_val1 on t2 (cost=0.00..43.25 rows=1000 width=8)
64 EXPLAIN SELECT * FROM t1, t2 WHERE t1.val2 = t2.val2;
66 ------------------------------------------------------------------
67 Hash Join (cost=27.50..220.00 rows=1000 width=16)
68 Hash Cond: (t1.val2 = t2.val2)
69 -> Seq Scan on t1 (cost=0.00..145.00 rows=10000 width=8)
70 -> Hash (cost=15.00..15.00 rows=1000 width=8)
71 -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=8)
74 SELECT pg_add_hint('nest(' || :t1_oid || ',' || :t2_oid || ')');
80 EXPLAIN SELECT * FROM t1, t2 WHERE t1.val1 = t2.val1;
82 ------------------------------------------------------------------------
83 Nested Loop (cost=0.00..607.00 rows=1000 width=16)
84 -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=8)
85 -> Index Scan using t1_val1 on t1 (cost=0.00..0.58 rows=1 width=8)
86 Index Cond: (val1 = t2.val1)
89 EXPLAIN SELECT * FROM t1, t2 WHERE t1.val2 = t2.val2;
91 ------------------------------------------------------------------
92 Nested Loop (cost=0.00..150162.50 rows=1000 width=16)
93 Join Filter: (t1.val2 = t2.val2)
94 -> Seq Scan on t1 (cost=0.00..145.00 rows=10000 width=8)
95 -> Materialize (cost=0.00..20.00 rows=1000 width=8)
96 -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=8)
99 SELECT pg_add_hint('hash(' || :t1_oid || ',' || :t2_oid || ')');
105 EXPLAIN SELECT * FROM t1, t2 WHERE t1.val1 = t2.val1;
107 ------------------------------------------------------------------
108 Hash Join (cost=27.50..220.00 rows=1000 width=16)
109 Hash Cond: (t1.val1 = t2.val1)
110 -> Seq Scan on t1 (cost=0.00..145.00 rows=10000 width=8)
111 -> Hash (cost=15.00..15.00 rows=1000 width=8)
112 -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=8)
115 EXPLAIN SELECT * FROM t1, t2 WHERE t1.val2 = t2.val2;
117 ------------------------------------------------------------------
118 Hash Join (cost=27.50..220.00 rows=1000 width=16)
119 Hash Cond: (t1.val2 = t2.val2)
120 -> Seq Scan on t1 (cost=0.00..145.00 rows=10000 width=8)
121 -> Hash (cost=15.00..15.00 rows=1000 width=8)
122 -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=8)
125 SELECT pg_add_hint('merge(' || :t1_oid || ',' || :t2_oid || ')');
131 EXPLAIN SELECT * FROM t1, t2 WHERE t1.val1 = t2.val1;
133 ------------------------------------------------------------------------------
134 Merge Join (cost=0.00..90.08 rows=1000 width=16)
135 Merge Cond: (t1.val1 = t2.val1)
136 -> Index Scan using t1_val1 on t1 (cost=0.00..318.26 rows=10000 width=8)
137 -> Index Scan using t2_val1 on t2 (cost=0.00..43.25 rows=1000 width=8)
140 EXPLAIN SELECT * FROM t1, t2 WHERE t1.val2 = t2.val2;
142 --------------------------------------------------------------------
143 Merge Join (cost=874.21..894.21 rows=1000 width=16)
144 Merge Cond: (t1.val2 = t2.val2)
145 -> Sort (cost=809.39..834.39 rows=10000 width=8)
147 -> Seq Scan on t1 (cost=0.00..145.00 rows=10000 width=8)
148 -> Sort (cost=64.83..67.33 rows=1000 width=8)
150 -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=8)
153 SELECT pg_clear_hint();
159 EXPLAIN SELECT * FROM t1, t2, t3 WHERE t1.val1 = t2.val1 AND t2.val1 = t3.val1;
161 ------------------------------------------------------------------------------------
162 Merge Join (cost=5.32..6.85 rows=10 width=24)
163 Merge Cond: (t1.val1 = t3.val1)
164 -> Merge Join (cost=0.00..90.08 rows=1000 width=16)
165 Merge Cond: (t1.val1 = t2.val1)
166 -> Index Scan using t1_val1 on t1 (cost=0.00..318.26 rows=10000 width=8)
167 -> Index Scan using t2_val1 on t2 (cost=0.00..43.25 rows=1000 width=8)
168 -> Sort (cost=5.32..5.57 rows=100 width=8)
170 -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=8)
173 EXPLAIN SELECT * FROM t1, t2, t3 WHERE t1.val2 = t2.val2 AND t2.val2 = t3.val2;
175 ----------------------------------------------------------------------------
176 Hash Join (cost=24.25..207.75 rows=10 width=24)
177 Hash Cond: (t1.val2 = t2.val2)
178 -> Seq Scan on t1 (cost=0.00..145.00 rows=10000 width=8)
179 -> Hash (cost=23.00..23.00 rows=100 width=16)
180 -> Hash Join (cost=3.25..23.00 rows=100 width=16)
181 Hash Cond: (t2.val2 = t3.val2)
182 -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=8)
183 -> Hash (cost=2.00..2.00 rows=100 width=8)
184 -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=8)
187 SELECT pg_add_hint('nest(' || :t1_oid || ',' || :t2_oid || ',' || :t3_oid || ')');
193 SELECT pg_add_hint('nest(' || :t1_oid || ',' || :t3_oid || ')');
199 EXPLAIN SELECT * FROM t1, t2, t3 WHERE t1.val1 = t2.val1 AND t2.val1 = t3.val1;
201 ----------------------------------------------------------------------------------
202 Nested Loop (cost=5.32..70.60 rows=10 width=24)
203 -> Merge Join (cost=5.32..11.40 rows=100 width=16)
204 Merge Cond: (t2.val1 = t3.val1)
205 -> Index Scan using t2_val1 on t2 (cost=0.00..43.25 rows=1000 width=8)
206 -> Sort (cost=5.32..5.57 rows=100 width=8)
208 -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=8)
209 -> Index Scan using t1_val1 on t1 (cost=0.00..0.58 rows=1 width=8)
210 Index Cond: (val1 = t2.val1)
213 EXPLAIN SELECT * FROM t1, t2, t3 WHERE t1.val2 = t2.val2 AND t2.val2 = t3.val2;
215 ------------------------------------------------------------------------
216 Nested Loop (cost=27.50..1722.25 rows=10 width=24)
217 Join Filter: (t1.val2 = t3.val2)
218 -> Hash Join (cost=27.50..220.00 rows=1000 width=16)
219 Hash Cond: (t1.val2 = t2.val2)
220 -> Seq Scan on t1 (cost=0.00..145.00 rows=10000 width=8)
221 -> Hash (cost=15.00..15.00 rows=1000 width=8)
222 -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=8)
223 -> Materialize (cost=0.00..2.50 rows=100 width=8)
224 -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=8)
227 SELECT pg_clear_hint();
233 EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.val1 = t2.val1 AND t2.val1 = t3.val1 AND t3.val1 = t4.val1;
235 ------------------------------------------------------------------------------------------
236 Nested Loop (cost=1.27..1.70 rows=1 width=32)
237 -> Merge Join (cost=1.27..1.42 rows=1 width=24)
238 Merge Cond: (t1.val1 = t4.val1)
239 -> Merge Join (cost=0.00..90.08 rows=1000 width=16)
240 Merge Cond: (t1.val1 = t2.val1)
241 -> Index Scan using t1_val1 on t1 (cost=0.00..318.26 rows=10000 width=8)
242 -> Index Scan using t2_val1 on t2 (cost=0.00..43.25 rows=1000 width=8)
243 -> Sort (cost=1.27..1.29 rows=10 width=8)
245 -> Seq Scan on t4 (cost=0.00..1.10 rows=10 width=8)
246 -> Index Scan using t3_val1 on t3 (cost=0.00..0.27 rows=1 width=8)
247 Index Cond: (val1 = t1.val1)
250 SELECT pg_add_hint('no_merge(' || :t1_oid || ',' || :t2_oid || ')');
256 EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.val1 = t2.val1 AND t2.val1 = t3.val1 AND t3.val1 = t4.val1;
258 ------------------------------------------------------------------------------------------
259 Nested Loop (cost=1.27..1.80 rows=1 width=32)
260 -> Merge Join (cost=1.27..1.51 rows=1 width=24)
261 Merge Cond: (t1.val1 = t4.val1)
262 -> Merge Join (cost=0.00..18.43 rows=100 width=16)
263 Merge Cond: (t1.val1 = t3.val1)
264 -> Index Scan using t1_val1 on t1 (cost=0.00..318.26 rows=10000 width=8)
265 -> Index Scan using t3_val1 on t3 (cost=0.00..13.75 rows=100 width=8)
266 -> Sort (cost=1.27..1.29 rows=10 width=8)
268 -> Seq Scan on t4 (cost=0.00..1.10 rows=10 width=8)
269 -> Index Scan using t2_val1 on t2 (cost=0.00..0.27 rows=1 width=8)
270 Index Cond: (val1 = t1.val1)
273 SET enable_mergejoin TO off;
274 EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.val1 = t2.val1 AND t2.val1 = t3.val1 AND t3.val1 = t4.val1;
276 ------------------------------------------------------------------------------
277 Nested Loop (cost=1.23..11.11 rows=1 width=32)
278 -> Nested Loop (cost=1.23..10.52 rows=1 width=24)
279 -> Hash Join (cost=1.23..3.70 rows=10 width=16)
280 Hash Cond: (t3.val1 = t4.val1)
281 -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=8)
282 -> Hash (cost=1.10..1.10 rows=10 width=8)
283 -> Seq Scan on t4 (cost=0.00..1.10 rows=10 width=8)
284 -> Index Scan using t2_val1 on t2 (cost=0.00..0.67 rows=1 width=8)
285 Index Cond: (val1 = t3.val1)
286 -> Index Scan using t1_val1 on t1 (cost=0.00..0.58 rows=1 width=8)
287 Index Cond: (val1 = t2.val1)
290 SELECT pg_add_hint('no_hash(' || :t3_oid || ',' || :t4_oid || ')');
296 EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.val1 = t2.val1 AND t2.val1 = t3.val1 AND t3.val1 = t4.val1;
298 ---------------------------------------------------------------------------------------
299 Nested Loop (cost=1.27..10.09 rows=1 width=32)
300 -> Nested Loop (cost=1.27..9.50 rows=1 width=24)
301 -> Merge Join (cost=1.27..2.68 rows=10 width=16)
302 Merge Cond: (t3.val1 = t4.val1)
303 -> Index Scan using t3_val1 on t3 (cost=0.00..13.75 rows=100 width=8)
304 -> Sort (cost=1.27..1.29 rows=10 width=8)
306 -> Seq Scan on t4 (cost=0.00..1.10 rows=10 width=8)
307 -> Index Scan using t2_val1 on t2 (cost=0.00..0.67 rows=1 width=8)
308 Index Cond: (val1 = t3.val1)
309 -> Index Scan using t1_val1 on t1 (cost=0.00..0.58 rows=1 width=8)
310 Index Cond: (val1 = t2.val1)
313 SELECT pg_add_hint('no_nest(' || :t2_oid || ',' || :t3_oid || ',' || :t4_oid || ')');
319 EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.val1 = t2.val1 AND t2.val1 = t3.val1 AND t3.val1 = t4.val1;
321 ---------------------------------------------------------------------------------------
322 Nested Loop (cost=1.27..2.74 rows=1 width=32)
323 -> Merge Join (cost=1.27..2.15 rows=1 width=24)
324 Merge Cond: (t2.val1 = t4.val1)
325 -> Nested Loop (cost=0.00..81.95 rows=100 width=16)
326 -> Index Scan using t3_val1 on t3 (cost=0.00..13.75 rows=100 width=8)
327 -> Index Scan using t2_val1 on t2 (cost=0.00..0.67 rows=1 width=8)
328 Index Cond: (val1 = t3.val1)
329 -> Sort (cost=1.27..1.29 rows=10 width=8)
331 -> Seq Scan on t4 (cost=0.00..1.10 rows=10 width=8)
332 -> Index Scan using t1_val1 on t1 (cost=0.00..0.58 rows=1 width=8)
333 Index Cond: (val1 = t2.val1)
336 SELECT pg_clear_hint();
342 SET join_collapse_limit TO 10;
343 EXPLAIN SELECT * FROM t1 CROSS JOIN t2 CROSS JOIN t3 CROSS JOIN t4 WHERE t1.val1 = t2.val1 AND t2.val1 = t3.val1 AND t3.val1 = t4.val1;
345 ------------------------------------------------------------------------------
346 Nested Loop (cost=1.23..11.11 rows=1 width=32)
347 -> Nested Loop (cost=1.23..10.52 rows=1 width=24)
348 -> Hash Join (cost=1.23..3.70 rows=10 width=16)
349 Hash Cond: (t3.val1 = t4.val1)
350 -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=8)
351 -> Hash (cost=1.10..1.10 rows=10 width=8)
352 -> Seq Scan on t4 (cost=0.00..1.10 rows=10 width=8)
353 -> Index Scan using t2_val1 on t2 (cost=0.00..0.67 rows=1 width=8)
354 Index Cond: (val1 = t3.val1)
355 -> Index Scan using t1_val1 on t1 (cost=0.00..0.58 rows=1 width=8)
356 Index Cond: (val1 = t2.val1)
359 SET join_collapse_limit TO 1;
360 EXPLAIN SELECT * FROM t1 CROSS JOIN t2 CROSS JOIN t3 CROSS JOIN t4 WHERE t1.val1 = t2.val1 AND t2.val1 = t3.val1 AND t3.val1 = t4.val1;
362 ------------------------------------------------------------------------------
363 Nested Loop (cost=30.75..229.72 rows=1 width=32)
364 Join Filter: (t1.val1 = t4.val1)
365 -> Hash Join (cost=30.75..227.10 rows=10 width=24)
366 Hash Cond: (t1.val1 = t3.val1)
367 -> Hash Join (cost=27.50..220.00 rows=1000 width=16)
368 Hash Cond: (t1.val1 = t2.val1)
369 -> Seq Scan on t1 (cost=0.00..145.00 rows=10000 width=8)
370 -> Hash (cost=15.00..15.00 rows=1000 width=8)
371 -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=8)
372 -> Hash (cost=2.00..2.00 rows=100 width=8)
373 -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=8)
374 -> Materialize (cost=0.00..1.15 rows=10 width=8)
375 -> Seq Scan on t4 (cost=0.00..1.10 rows=10 width=8)
378 EXPLAIN SELECT * FROM t2 CROSS JOIN t3 CROSS JOIN t4 CROSS JOIN t1 WHERE t1.val1 = t2.val1 AND t2.val1 = t3.val1 AND t3.val1 = t4.val1;
380 ----------------------------------------------------------------------------
381 Nested Loop (cost=4.47..25.20 rows=1 width=32)
382 -> Hash Join (cost=4.47..24.61 rows=1 width=24)
383 Hash Cond: (t2.val1 = t4.val1)
384 -> Hash Join (cost=3.25..23.00 rows=100 width=16)
385 Hash Cond: (t2.val1 = t3.val1)
386 -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=8)
387 -> Hash (cost=2.00..2.00 rows=100 width=8)
388 -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=8)
389 -> Hash (cost=1.10..1.10 rows=10 width=8)
390 -> Seq Scan on t4 (cost=0.00..1.10 rows=10 width=8)
391 -> Index Scan using t1_val1 on t1 (cost=0.00..0.58 rows=1 width=8)
392 Index Cond: (val1 = t2.val1)
395 EXPLAIN SELECT * FROM t1 CROSS JOIN (t2 CROSS JOIN t3 CROSS JOIN t4) WHERE t1.val1 = t2.val1 AND t2.val1 = t3.val1 AND t3.val1 = t4.val1;
397 ----------------------------------------------------------------------------
398 Nested Loop (cost=4.47..25.20 rows=1 width=32)
399 -> Hash Join (cost=4.47..24.61 rows=1 width=24)
400 Hash Cond: (t2.val1 = t4.val1)
401 -> Hash Join (cost=3.25..23.00 rows=100 width=16)
402 Hash Cond: (t2.val1 = t3.val1)
403 -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=8)
404 -> Hash (cost=2.00..2.00 rows=100 width=8)
405 -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=8)
406 -> Hash (cost=1.10..1.10 rows=10 width=8)
407 -> Seq Scan on t4 (cost=0.00..1.10 rows=10 width=8)
408 -> Index Scan using t1_val1 on t1 (cost=0.00..0.58 rows=1 width=8)
409 Index Cond: (val1 = t2.val1)
412 EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.val1 = t2.val1) JOIN t3 ON (t2.val1 = t3.val1) JOIN t4 ON (t3.val1 = t4.val1);
414 ------------------------------------------------------------------------------
415 Nested Loop (cost=30.75..229.72 rows=1 width=32)
416 Join Filter: (t1.val1 = t4.val1)
417 -> Hash Join (cost=30.75..227.10 rows=10 width=24)
418 Hash Cond: (t1.val1 = t3.val1)
419 -> Hash Join (cost=27.50..220.00 rows=1000 width=16)
420 Hash Cond: (t1.val1 = t2.val1)
421 -> Seq Scan on t1 (cost=0.00..145.00 rows=10000 width=8)
422 -> Hash (cost=15.00..15.00 rows=1000 width=8)
423 -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=8)
424 -> Hash (cost=2.00..2.00 rows=100 width=8)
425 -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=8)
426 -> Materialize (cost=0.00..1.15 rows=10 width=8)
427 -> Seq Scan on t4 (cost=0.00..1.10 rows=10 width=8)
430 EXPLAIN SELECT * FROM v2;
432 ------------------------------------------------------------------
433 Hash Join (cost=27.50..220.00 rows=1000 width=16)
434 Hash Cond: (t1.val1 = t2.val1)
435 -> Seq Scan on t1 (cost=0.00..145.00 rows=10000 width=8)
436 -> Hash (cost=15.00..15.00 rows=1000 width=8)
437 -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=8)
440 EXPLAIN SELECT * FROM v3 v_3;
442 ----------------------------------------------------------------------
443 Hash Join (cost=27.50..220.00 rows=1000 width=16)
444 Hash Cond: (t_1.val1 = t_2.val1)
445 -> Seq Scan on t1 t_1 (cost=0.00..145.00 rows=10000 width=8)
446 -> Hash (cost=15.00..15.00 rows=1000 width=8)
447 -> Seq Scan on t2 t_2 (cost=0.00..15.00 rows=1000 width=8)
450 EXPLAIN SELECT * FROM v2 v_2, v3 v_3 WHERE v_2.t1_val1 = v_3.t1_val1;
452 ------------------------------------------------------------------------------
453 Nested Loop (cost=55.00..284.45 rows=100 width=32)
454 -> Hash Join (cost=55.00..252.25 rows=100 width=24)
455 Hash Cond: (t1.val1 = t_2.val1)
456 -> Hash Join (cost=27.50..220.00 rows=1000 width=16)
457 Hash Cond: (t1.val1 = t2.val1)
458 -> Seq Scan on t1 (cost=0.00..145.00 rows=10000 width=8)
459 -> Hash (cost=15.00..15.00 rows=1000 width=8)
460 -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=8)
461 -> Hash (cost=15.00..15.00 rows=1000 width=8)
462 -> Seq Scan on t2 t_2 (cost=0.00..15.00 rows=1000 width=8)
463 -> Index Scan using t1_val1 on t1 t_1 (cost=0.00..0.31 rows=1 width=8)
464 Index Cond: (val1 = t1.val1)
467 --SELECT pg_enable_log(true);
468 EXPLAIN SELECT * FROM v4 v_4;
470 -----------------------------------------------------------------------------
471 Nested Loop (cost=3.25..82.20 rows=10 width=8)
472 -> Hash Join (cost=3.25..23.00 rows=100 width=8)
473 Hash Cond: (t2.val1 = t_3.val1)
474 -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=4)
475 -> Hash (cost=2.00..2.00 rows=100 width=4)
476 -> Seq Scan on t3 t_3 (cost=0.00..2.00 rows=100 width=4)
477 -> Index Only Scan using t1_val1 on t1 (cost=0.00..0.58 rows=1 width=4)
478 Index Cond: (val1 = t2.val1)
481 SET from_collapse_limit TO 1;
482 EXPLAIN SELECT * FROM v4 v_4;
484 ------------------------------------------------------------------------
485 Hash Join (cost=30.75..227.10 rows=10 width=8)
486 Hash Cond: (t1.val1 = t_3.val1)
487 -> Hash Join (cost=27.50..220.00 rows=1000 width=8)
488 Hash Cond: (t1.val1 = t2.val1)
489 -> Seq Scan on t1 (cost=0.00..145.00 rows=10000 width=4)
490 -> Hash (cost=15.00..15.00 rows=1000 width=4)
491 -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=4)
492 -> Hash (cost=2.00..2.00 rows=100 width=4)
493 -> Seq Scan on t3 t_3 (cost=0.00..2.00 rows=100 width=4)
496 --SELECT pg_enable_log(false);