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 || ')');
252 EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.val1 = t2.val1 AND t2.val1 = t3.val1 AND t3.val1 = t4.val1;
254 ------------------------------------------------------------------------------------------
255 Nested Loop (cost=1.27..1.70 rows=1 width=32)
256 -> Merge Join (cost=1.27..1.42 rows=1 width=24)
257 Merge Cond: (t1.val1 = t4.val1)
258 -> Merge Join (cost=0.00..90.08 rows=1000 width=16)
259 Merge Cond: (t1.val1 = t2.val1)
260 -> Index Scan using t1_val1 on t1 (cost=0.00..318.26 rows=10000 width=8)
261 -> Index Scan using t2_val1 on t2 (cost=0.00..43.25 rows=1000 width=8)
262 -> Sort (cost=1.27..1.29 rows=10 width=8)
264 -> Seq Scan on t4 (cost=0.00..1.10 rows=10 width=8)
265 -> Index Scan using t3_val1 on t3 (cost=0.00..0.27 rows=1 width=8)
266 Index Cond: (val1 = t1.val1)
269 SET enable_mergejoin TO off;
270 EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.val1 = t2.val1 AND t2.val1 = t3.val1 AND t3.val1 = t4.val1;
272 ------------------------------------------------------------------------------
273 Nested Loop (cost=1.23..11.11 rows=1 width=32)
274 -> Nested Loop (cost=1.23..10.52 rows=1 width=24)
275 -> Hash Join (cost=1.23..3.70 rows=10 width=16)
276 Hash Cond: (t3.val1 = t4.val1)
277 -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=8)
278 -> Hash (cost=1.10..1.10 rows=10 width=8)
279 -> Seq Scan on t4 (cost=0.00..1.10 rows=10 width=8)
280 -> Index Scan using t2_val1 on t2 (cost=0.00..0.67 rows=1 width=8)
281 Index Cond: (val1 = t3.val1)
282 -> Index Scan using t1_val1 on t1 (cost=0.00..0.58 rows=1 width=8)
283 Index Cond: (val1 = t2.val1)
286 SELECT pg_add_hint('no_hash(' || :t3_oid || ',' || :t4_oid || ')');
288 EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.val1 = t2.val1 AND t2.val1 = t3.val1 AND t3.val1 = t4.val1;
290 ------------------------------------------------------------------------------
291 Nested Loop (cost=1.23..11.11 rows=1 width=32)
292 -> Nested Loop (cost=1.23..10.52 rows=1 width=24)
293 -> Hash Join (cost=1.23..3.70 rows=10 width=16)
294 Hash Cond: (t3.val1 = t4.val1)
295 -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=8)
296 -> Hash (cost=1.10..1.10 rows=10 width=8)
297 -> Seq Scan on t4 (cost=0.00..1.10 rows=10 width=8)
298 -> Index Scan using t2_val1 on t2 (cost=0.00..0.67 rows=1 width=8)
299 Index Cond: (val1 = t3.val1)
300 -> Index Scan using t1_val1 on t1 (cost=0.00..0.58 rows=1 width=8)
301 Index Cond: (val1 = t2.val1)
304 SELECT pg_add_hint('no_nest(' || :t2_oid || ',' || :t3_oid || ',' || :t4_oid || ')');
306 EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.val1 = t2.val1 AND t2.val1 = t3.val1 AND t3.val1 = t4.val1;
308 ------------------------------------------------------------------------------
309 Nested Loop (cost=1.23..11.11 rows=1 width=32)
310 -> Nested Loop (cost=1.23..10.52 rows=1 width=24)
311 -> Hash Join (cost=1.23..3.70 rows=10 width=16)
312 Hash Cond: (t3.val1 = t4.val1)
313 -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=8)
314 -> Hash (cost=1.10..1.10 rows=10 width=8)
315 -> Seq Scan on t4 (cost=0.00..1.10 rows=10 width=8)
316 -> Index Scan using t2_val1 on t2 (cost=0.00..0.67 rows=1 width=8)
317 Index Cond: (val1 = t3.val1)
318 -> Index Scan using t1_val1 on t1 (cost=0.00..0.58 rows=1 width=8)
319 Index Cond: (val1 = t2.val1)
322 SELECT pg_clear_hint();
328 SET join_collapse_limit TO 10;
329 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;
331 ------------------------------------------------------------------------------
332 Nested Loop (cost=1.23..11.11 rows=1 width=32)
333 -> Nested Loop (cost=1.23..10.52 rows=1 width=24)
334 -> Hash Join (cost=1.23..3.70 rows=10 width=16)
335 Hash Cond: (t3.val1 = t4.val1)
336 -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=8)
337 -> Hash (cost=1.10..1.10 rows=10 width=8)
338 -> Seq Scan on t4 (cost=0.00..1.10 rows=10 width=8)
339 -> Index Scan using t2_val1 on t2 (cost=0.00..0.67 rows=1 width=8)
340 Index Cond: (val1 = t3.val1)
341 -> Index Scan using t1_val1 on t1 (cost=0.00..0.58 rows=1 width=8)
342 Index Cond: (val1 = t2.val1)
345 SET join_collapse_limit TO 1;
346 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;
348 ------------------------------------------------------------------------------
349 Nested Loop (cost=30.75..229.72 rows=1 width=32)
350 Join Filter: (t1.val1 = t4.val1)
351 -> Hash Join (cost=30.75..227.10 rows=10 width=24)
352 Hash Cond: (t1.val1 = t3.val1)
353 -> Hash Join (cost=27.50..220.00 rows=1000 width=16)
354 Hash Cond: (t1.val1 = t2.val1)
355 -> Seq Scan on t1 (cost=0.00..145.00 rows=10000 width=8)
356 -> Hash (cost=15.00..15.00 rows=1000 width=8)
357 -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 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)
360 -> Materialize (cost=0.00..1.15 rows=10 width=8)
361 -> Seq Scan on t4 (cost=0.00..1.10 rows=10 width=8)
364 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;
366 ----------------------------------------------------------------------------
367 Nested Loop (cost=4.47..25.20 rows=1 width=32)
368 -> Hash Join (cost=4.47..24.61 rows=1 width=24)
369 Hash Cond: (t2.val1 = t4.val1)
370 -> Hash Join (cost=3.25..23.00 rows=100 width=16)
371 Hash Cond: (t2.val1 = t3.val1)
372 -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=8)
373 -> Hash (cost=2.00..2.00 rows=100 width=8)
374 -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=8)
375 -> Hash (cost=1.10..1.10 rows=10 width=8)
376 -> Seq Scan on t4 (cost=0.00..1.10 rows=10 width=8)
377 -> Index Scan using t1_val1 on t1 (cost=0.00..0.58 rows=1 width=8)
378 Index Cond: (val1 = t2.val1)
381 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;
383 ----------------------------------------------------------------------------
384 Nested Loop (cost=4.47..25.20 rows=1 width=32)
385 -> Hash Join (cost=4.47..24.61 rows=1 width=24)
386 Hash Cond: (t2.val1 = t4.val1)
387 -> Hash Join (cost=3.25..23.00 rows=100 width=16)
388 Hash Cond: (t2.val1 = t3.val1)
389 -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=8)
390 -> Hash (cost=2.00..2.00 rows=100 width=8)
391 -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=8)
392 -> Hash (cost=1.10..1.10 rows=10 width=8)
393 -> Seq Scan on t4 (cost=0.00..1.10 rows=10 width=8)
394 -> Index Scan using t1_val1 on t1 (cost=0.00..0.58 rows=1 width=8)
395 Index Cond: (val1 = t2.val1)
398 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);
400 ------------------------------------------------------------------------------
401 Nested Loop (cost=30.75..229.72 rows=1 width=32)
402 Join Filter: (t1.val1 = t4.val1)
403 -> Hash Join (cost=30.75..227.10 rows=10 width=24)
404 Hash Cond: (t1.val1 = t3.val1)
405 -> Hash Join (cost=27.50..220.00 rows=1000 width=16)
406 Hash Cond: (t1.val1 = t2.val1)
407 -> Seq Scan on t1 (cost=0.00..145.00 rows=10000 width=8)
408 -> Hash (cost=15.00..15.00 rows=1000 width=8)
409 -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=8)
410 -> Hash (cost=2.00..2.00 rows=100 width=8)
411 -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=8)
412 -> Materialize (cost=0.00..1.15 rows=10 width=8)
413 -> Seq Scan on t4 (cost=0.00..1.10 rows=10 width=8)
416 EXPLAIN SELECT * FROM v2;
418 ------------------------------------------------------------------
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)
426 EXPLAIN SELECT * FROM v3 v_3;
428 ----------------------------------------------------------------------
429 Hash Join (cost=27.50..220.00 rows=1000 width=16)
430 Hash Cond: (t_1.val1 = t_2.val1)
431 -> Seq Scan on t1 t_1 (cost=0.00..145.00 rows=10000 width=8)
432 -> Hash (cost=15.00..15.00 rows=1000 width=8)
433 -> Seq Scan on t2 t_2 (cost=0.00..15.00 rows=1000 width=8)
436 EXPLAIN SELECT * FROM v2 v_2, v3 v_3 WHERE v_2.t1_val1 = v_3.t1_val1;
438 ------------------------------------------------------------------------------
439 Nested Loop (cost=55.00..284.45 rows=100 width=32)
440 -> Hash Join (cost=55.00..252.25 rows=100 width=24)
441 Hash Cond: (t1.val1 = t_2.val1)
442 -> Hash Join (cost=27.50..220.00 rows=1000 width=16)
443 Hash Cond: (t1.val1 = t2.val1)
444 -> Seq Scan on t1 (cost=0.00..145.00 rows=10000 width=8)
445 -> Hash (cost=15.00..15.00 rows=1000 width=8)
446 -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=8)
447 -> Hash (cost=15.00..15.00 rows=1000 width=8)
448 -> Seq Scan on t2 t_2 (cost=0.00..15.00 rows=1000 width=8)
449 -> Index Scan using t1_val1 on t1 t_1 (cost=0.00..0.31 rows=1 width=8)
450 Index Cond: (val1 = t1.val1)
453 --SELECT pg_enable_log(true);
454 EXPLAIN SELECT * FROM v4 v_4;
456 -----------------------------------------------------------------------------
457 Nested Loop (cost=3.25..82.20 rows=10 width=8)
458 -> Hash Join (cost=3.25..23.00 rows=100 width=8)
459 Hash Cond: (t2.val1 = t_3.val1)
460 -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=4)
461 -> Hash (cost=2.00..2.00 rows=100 width=4)
462 -> Seq Scan on t3 t_3 (cost=0.00..2.00 rows=100 width=4)
463 -> Index Only Scan using t1_val1 on t1 (cost=0.00..0.58 rows=1 width=4)
464 Index Cond: (val1 = t2.val1)
467 SET from_collapse_limit TO 1;
468 EXPLAIN SELECT * FROM v4 v_4;
470 ------------------------------------------------------------------------
471 Hash Join (cost=30.75..227.10 rows=10 width=8)
472 Hash Cond: (t1.val1 = t_3.val1)
473 -> Hash Join (cost=27.50..220.00 rows=1000 width=8)
474 Hash Cond: (t1.val1 = t2.val1)
475 -> Seq Scan on t1 (cost=0.00..145.00 rows=10000 width=4)
476 -> Hash (cost=15.00..15.00 rows=1000 width=4)
477 -> Seq Scan on t2 (cost=0.00..15.00 rows=1000 width=4)
478 -> Hash (cost=2.00..2.00 rows=100 width=4)
479 -> Seq Scan on t3 t_3 (cost=0.00..2.00 rows=100 width=4)
482 --SELECT pg_enable_log(false);