2 SET search_path TO public;
3 SET pg_hint_plan.debug_print TO on;
4 SET client_min_messages TO LOG;
5 SET pg_hint_plan.enable_hint TO on;
6 CREATE EXTENSION file_fdw;
7 CREATE SERVER file_server FOREIGN DATA WRAPPER file_fdw;
8 CREATE USER MAPPING FOR PUBLIC SERVER file_server;
9 CREATE FOREIGN TABLE ft1 (id int, val int) SERVER file_server OPTIONS (format 'csv', filename '@abs_srcdir@/data/data.csv');
27 EXPLAIN (COSTS false) SELECT * FROM s1.t1, ft1 ft_1, ft1 ft_2 WHERE t1.c1 = ft_1.id AND t1.c1 = ft_2.id;
29 Join Filter: (ft_1.id = t1.c1)
31 Join Filter: (ft_1.id = ft_2.id)
32 -> Foreign Scan on ft1 ft_1
33 Foreign File: @abs_srcdir@/data/data.csv
34 -> Foreign Scan on ft1 ft_2
35 Foreign File: @abs_srcdir@/data/data.csv
36 -> Index Scan using t1_i1 on t1
37 Index Cond: (c1 = ft_2.id)
40 ---- No. S-1-5 object type for the hint
43 /*+SeqScan(t1)SeqScan(ft_1)SeqScan(ft_2)*/
44 EXPLAIN (COSTS false) SELECT * FROM s1.t1, ft1 ft_1, ft1 ft_2 WHERE t1.c1 = ft_1.id AND t1.c1 = ft_2.id;
55 Join Filter: (t1.c1 = ft_2.id)
57 Hash Cond: (t1.c1 = ft_1.id)
60 -> Foreign Scan on ft1 ft_1
61 Foreign File: @abs_srcdir@/data/data.csv
62 -> Foreign Scan on ft1 ft_2
63 Foreign File: @abs_srcdir@/data/data.csv
66 ---- No. J-1-6 object type for the hint
69 /*+MergeJoin(ft_1 ft_2)Leading(ft_1 ft_2 t1)*/
70 EXPLAIN (COSTS false) SELECT * FROM s1.t1, ft1 ft_1, ft1 ft_2 WHERE t1.c1 = ft_1.id AND t1.c1 = ft_2.id;
80 Join Filter: (ft_1.id = t1.c1)
82 Merge Cond: (ft_1.id = ft_2.id)
85 -> Foreign Scan on ft1 ft_1
86 Foreign File: @abs_srcdir@/data/data.csv
89 -> Foreign Scan on ft1 ft_2
90 Foreign File: @abs_srcdir@/data/data.csv
91 -> Index Scan using t1_i1 on t1
92 Index Cond: (c1 = ft_2.id)
95 ---- No. L-1-6 object type for the hint
98 /*+Leading(ft_1 ft_2 t1)*/
99 EXPLAIN (COSTS false) SELECT * FROM s1.t1, ft1 ft_1, ft1 ft_2 WHERE t1.c1 = ft_1.id AND t1.c1 = ft_2.id;
102 Leading(ft_1 ft_2 t1)
108 Join Filter: (ft_1.id = t1.c1)
110 Join Filter: (ft_1.id = ft_2.id)
111 -> Foreign Scan on ft1 ft_1
112 Foreign File: @abs_srcdir@/data/data.csv
113 -> Foreign Scan on ft1 ft_2
114 Foreign File: @abs_srcdir@/data/data.csv
115 -> Index Scan using t1_i1 on t1
116 Index Cond: (c1 = ft_2.id)
119 ---- No. R-1-6 object type for the hint
122 \o results/ut-fdw.tmpout
123 /*+Rows(ft_1 ft_2 #1)Leading(ft_1 ft_2 t1)*/
124 EXPLAIN SELECT * FROM s1.t1, ft1 ft_1, ft1 ft_2 WHERE t1.c1 = ft_1.id AND t1.c1 = ft_2.id;
127 Leading(ft_1 ft_2 t1)
134 \! sql/maskout.sh results/ut-fdw.tmpout
135 Nested Loop (cost=xxx..xxx rows=1 width=xxx)
136 Join Filter: (ft_1.id = t1.c1)
137 -> Nested Loop (cost=xxx..xxx rows=1 width=xxx)
138 Join Filter: (ft_1.id = ft_2.id)
139 -> Foreign Scan on ft1 ft_1 (cost=xxx..xxx rows=1 width=xxx)
140 Foreign File: @abs_srcdir@/data/data.csv
141 Foreign File Size: 42 b
142 -> Foreign Scan on ft1 ft_2 (cost=xxx..xxx rows=1 width=xxx)
143 Foreign File: @abs_srcdir@/data/data.csv
144 Foreign File Size: 42 b
145 -> Index Scan using t1_i1 on t1 (cost=xxx..xxx rows=1 width=xxx)
146 Index Cond: (c1 = ft_2.id)
148 \! rm results/ut-fdw.tmpout