1 SET search_path TO public;
2 SET client_min_messages TO log;
3 \set SHOW_CONTEXT always
5 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
6 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.val = t2.val;
9 SET pg_hint_plan.debug_print TO on;
11 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
12 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.val = t2.val;
15 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
16 SET pg_hint_plan.enable_hint TO off;
18 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
19 SET pg_hint_plan.enable_hint TO on;
21 /*Set(enable_indexscan off)*/
22 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
23 --+Set(enable_indexscan off)
24 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
25 /*+Set(enable_indexscan off) /* nest comment */ */
26 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
27 /*+Set(enable_indexscan off)*/
28 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
29 EXPLAIN (COSTS false) /*+Set(enable_indexscan off)*/
30 SELECT * FROM t1, t2 WHERE t1.id = t2.id;
31 /*+ Set(enable_indexscan off) Set(enable_hashjoin off) */
32 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
34 /*+ Set ( enable_indexscan off ) */
35 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
43 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
44 /*+ Set(enable_indexscan off)Set(enable_nestloop off)Set(enable_mergejoin off)
45 Set(enable_seqscan off)
47 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
48 /*+Set(work_mem "1M")*/
49 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
50 /*+Set(work_mem "1MB")*/
51 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
52 /*+Set(work_mem TO "1MB")*/
53 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
56 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
58 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
59 /*+SeqScan(t1)IndexScan(t2)*/
60 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
62 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
63 /*+BitmapScan(t2)NoSeqScan(t1)*/
64 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
66 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
69 EXPLAIN (COSTS false) SELECT * FROM t1, t4 WHERE t1.val < 10;
71 EXPLAIN (COSTS false) SELECT * FROM t3, t4 WHERE t3.id = t4.id AND t4.ctid = '(1,1)';
73 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)';
76 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
78 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
79 /*+NoMergeJoin(t1 t2)*/
80 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
83 EXPLAIN (COSTS false) SELECT * FROM t1, t3 WHERE t1.val = t3.val;
85 EXPLAIN (COSTS false) SELECT * FROM t1, t3 WHERE t1.val = t3.val;
86 /*+NoHashJoin(t1 t3)*/
87 EXPLAIN (COSTS false) SELECT * FROM t1, t3 WHERE t1.val = t3.val;
89 /*+MergeJoin(t4 t1 t2 t3)*/
90 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
91 /*+HashJoin(t3 t4 t1 t2)*/
92 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
93 /*+NestLoop(t2 t3 t4 t1) IndexScan(t3)*/
94 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
95 /*+NoNestLoop(t4 t1 t3 t2)*/
96 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
99 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
101 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
103 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
104 /*+Leading( t3 t4 )*/
105 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
106 /*+Leading(t3 t4 t1)*/
107 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
108 /*+Leading(t3 t4 t1 t2)*/
109 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
110 /*+Leading(t3 t4 t1 t2 t1)*/
111 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
112 /*+Leading(t3 t4 t4)*/
113 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
115 EXPLAIN (COSTS false) SELECT * FROM t1, (VALUES(1,1),(2,2),(3,3)) AS t2(id,val) WHERE t1.id = t2.id;
117 EXPLAIN (COSTS false) SELECT * FROM t1, (VALUES(1,1),(2,2),(3,3)) AS t2(id,val) WHERE t1.id = t2.id;
118 /*+HashJoin(t1 *VALUES*)*/
119 EXPLAIN (COSTS false) SELECT * FROM t1, (VALUES(1,1),(2,2),(3,3)) AS t2(id,val) WHERE t1.id = t2.id;
120 /*+HashJoin(t1 *VALUES*) IndexScan(t1) IndexScan(*VALUES*)*/
121 EXPLAIN (COSTS false) SELECT * FROM t1, (VALUES(1,1),(2,2),(3,3)) AS t2(id,val) WHERE t1.id = t2.id;
123 -- single table scan hint test
124 EXPLAIN (COSTS false) SELECT (SELECT max(id) FROM t1 v_1 WHERE id < 10), id FROM v1 WHERE v1.id = (SELECT max(id) FROM t1 v_2 WHERE id < 10);
126 EXPLAIN (COSTS false) SELECT (SELECT max(id) FROM t1 v_1 WHERE id < 10), id FROM v1 WHERE v1.id = (SELECT max(id) FROM t1 v_2 WHERE id < 10);
128 EXPLAIN (COSTS false) SELECT (SELECT max(id) FROM t1 v_1 WHERE id < 10), id FROM v1 WHERE v1.id = (SELECT max(id) FROM t1 v_2 WHERE id < 10);
130 EXPLAIN (COSTS false) SELECT (SELECT max(id) FROM t1 v_1 WHERE id < 10), id FROM v1 WHERE v1.id = (SELECT max(id) FROM t1 v_2 WHERE id < 10);
131 /*+BitmapScan(v_1)BitmapScan(v_2)*/
132 EXPLAIN (COSTS false) SELECT (SELECT max(id) FROM t1 v_1 WHERE id < 10), id FROM v1 WHERE v1.id = (SELECT max(id) FROM t1 v_2 WHERE id < 10);
133 /*+BitmapScan(v_1)BitmapScan(t1)*/
134 EXPLAIN (COSTS false) SELECT (SELECT max(id) FROM t1 v_1 WHERE id < 10), id FROM v1 WHERE v1.id = (SELECT max(id) FROM t1 v_2 WHERE id < 10);
135 /*+BitmapScan(v_2)BitmapScan(t1)*/
136 EXPLAIN (COSTS false) SELECT (SELECT max(id) FROM t1 v_1 WHERE id < 10), id FROM v1 WHERE v1.id = (SELECT max(id) FROM t1 v_2 WHERE id < 10);
137 /*+BitmapScan(v_1)BitmapScan(v_2)BitmapScan(t1)*/
138 EXPLAIN (COSTS false) SELECT (SELECT max(id) FROM t1 v_1 WHERE id < 10), id FROM v1 WHERE v1.id = (SELECT max(id) FROM t1 v_2 WHERE id < 10);
140 -- full scan hint pattern test
141 EXPLAIN (COSTS false) SELECT * FROM t1 WHERE id < 10 AND ctid = '(1,1)';
143 EXPLAIN (COSTS false) SELECT * FROM t1 WHERE id < 10 AND ctid = '(1,1)';
145 EXPLAIN (COSTS false) SELECT * FROM t1 WHERE id < 10 AND ctid = '(1,1)';
147 EXPLAIN (COSTS false) SELECT * FROM t1 WHERE id < 10 AND ctid = '(1,1)';
149 EXPLAIN (COSTS false) SELECT * FROM t1 WHERE id < 10 AND ctid = '(1,1)';
151 EXPLAIN (COSTS false) SELECT * FROM t1 WHERE id < 10 AND ctid = '(1,1)';
153 EXPLAIN (COSTS false) SELECT * FROM t1 WHERE id < 10 AND ctid = '(1,1)';
154 /*+NoBitmapScan(t1)*/
155 EXPLAIN (COSTS false) SELECT * FROM t1 WHERE id < 10 AND ctid = '(1,1)';
157 EXPLAIN (COSTS false) SELECT * FROM t1 WHERE id < 10 AND ctid = '(1,1)';
159 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
161 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
163 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
164 /*+SeqScan(t1) SeqScan(t2)*/
165 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
166 /*+SeqScan(t1) IndexScan(t2)*/
167 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
168 /*+SeqScan(t1) BitmapScan(t2)*/
169 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
170 /*+SeqScan(t1) TidScan(t2)*/
171 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
172 /*+SeqScan(t1) NoSeqScan(t2)*/
173 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
174 /*+SeqScan(t1) NoIndexScan(t2)*/
175 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
176 /*+SeqScan(t1) NoBitmapScan(t2)*/
177 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
178 /*+SeqScan(t1) NoTidScan(t2)*/
179 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
182 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
184 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
185 /*+IndexScan(t1) SeqScan(t2)*/
186 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
187 /*+IndexScan(t1) IndexScan(t2)*/
188 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
189 /*+IndexScan(t1) BitmapScan(t2)*/
190 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
191 /*+IndexScan(t1) TidScan(t2)*/
192 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
193 /*+IndexScan(t1) NoSeqScan(t2)*/
194 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
195 /*+IndexScan(t1) NoIndexScan(t2)*/
196 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
197 /*+IndexScan(t1) NoBitmapScan(t2)*/
198 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
199 /*+IndexScan(t1) NoTidScan(t2)*/
200 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
203 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
205 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
207 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
208 /*+BitmapScan(t1) SeqScan(t2)*/
209 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
210 /*+BitmapScan(t1) IndexScan(t2)*/
211 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
212 /*+BitmapScan(t1) BitmapScan(t2)*/
213 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
214 /*+BitmapScan(t1) TidScan(t2)*/
215 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
216 /*+BitmapScan(t1) NoSeqScan(t2)*/
217 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
218 /*+BitmapScan(t1) NoIndexScan(t2)*/
219 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
220 /*+BitmapScan(t1) NoBitmapScan(t2)*/
221 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
222 /*+BitmapScan(t1) NoTidScan(t2)*/
223 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
226 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
228 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
229 /*+TidScan(t1) SeqScan(t2)*/
230 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
231 /*+TidScan(t1) IndexScan(t2)*/
232 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
233 /*+TidScan(t1) BitmapScan(t2)*/
234 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
235 /*+TidScan(t1) TidScan(t2)*/
236 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
237 /*+TidScan(t1) NoSeqScan(t2)*/
238 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
239 /*+TidScan(t1) NoIndexScan(t2)*/
240 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
241 /*+TidScan(t1) NoBitmapScan(t2)*/
242 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
243 /*+TidScan(t1) NoTidScan(t2)*/
244 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
247 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
249 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
250 /*+NoSeqScan(t1) SeqScan(t2)*/
251 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
252 /*+NoSeqScan(t1) IndexScan(t2)*/
253 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
254 /*+NoSeqScan(t1) BitmapScan(t2)*/
255 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
256 /*+NoSeqScan(t1) TidScan(t2)*/
257 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
258 /*+NoSeqScan(t1) NoSeqScan(t2)*/
259 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
260 /*+NoSeqScan(t1) NoIndexScan(t2)*/
261 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
262 /*+NoSeqScan(t1) NoBitmapScan(t2)*/
263 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
264 /*+NoSeqScan(t1) NoTidScan(t2)*/
265 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
268 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
270 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
271 /*+NoIndexScan(t1) SeqScan(t2)*/
272 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
273 /*+NoIndexScan(t1) IndexScan(t2)*/
274 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
275 /*+NoIndexScan(t1) BitmapScan(t2)*/
276 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
277 /*+NoIndexScan(t1) TidScan(t2)*/
278 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
279 /*+NoIndexScan(t1) NoSeqScan(t2)*/
280 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
281 /*+NoIndexScan(t1) NoIndexScan(t2)*/
282 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
283 /*+NoIndexScan(t1) NoBitmapScan(t2)*/
284 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
285 /*+NoIndexScan(t1) NoTidScan(t2)*/
286 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
288 /*+NoBitmapScan(t1)*/
289 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
290 /*+NoBitmapScan(t2)*/
291 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
292 /*+NoBitmapScan(t1) SeqScan(t2)*/
293 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
294 /*+NoBitmapScan(t1) IndexScan(t2)*/
295 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
296 /*+NoBitmapScan(t1) BitmapScan(t2)*/
297 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
298 /*+NoBitmapScan(t1) TidScan(t2)*/
299 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
300 /*+NoBitmapScan(t1) NoSeqScan(t2)*/
301 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
302 /*+NoBitmapScan(t1) NoIndexScan(t2)*/
303 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
304 /*+NoBitmapScan(t1) NoBitmapScan(t2)*/
305 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
306 /*+NoBitmapScan(t1) NoTidScan(t2)*/
307 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
310 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
312 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
313 /*+NoTidScan(t1) SeqScan(t2)*/
314 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
315 /*+NoTidScan(t1) IndexScan(t2)*/
316 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
317 /*+NoTidScan(t1) BitmapScan(t2)*/
318 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
319 /*+NoTidScan(t1) TidScan(t2)*/
320 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
321 /*+NoTidScan(t1) NoSeqScan(t2)*/
322 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
323 /*+NoTidScan(t1) NoIndexScan(t2)*/
324 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
325 /*+NoTidScan(t1) NoBitmapScan(t2)*/
326 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
327 /*+NoTidScan(t1) NoTidScan(t2)*/
328 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
331 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)' AND t1.id < 10 AND t2.id < 10;
332 /*+BitmapScan(t1) BitmapScan(t2)*/
333 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)' AND t1.id < 10 AND t2.id < 10;
336 EXPLAIN (COSTS false) SELECT * FROM t1 FULL OUTER JOIN t2 ON (t1.id = t2.id);
337 /*+MergeJoin(t1 t2)*/
338 EXPLAIN (COSTS false) SELECT * FROM t1 FULL OUTER JOIN t2 ON (t1.id = t2.id);
340 EXPLAIN (COSTS false) SELECT * FROM t1 FULL OUTER JOIN t2 ON (t1.id = t2.id);
342 -- inheritance tables test
343 SET constraint_exclusion TO off;
344 EXPLAIN (COSTS false) SELECT * FROM p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
345 SET constraint_exclusion TO on;
346 EXPLAIN (COSTS false) SELECT * FROM p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
347 SET constraint_exclusion TO off;
349 EXPLAIN (COSTS false) SELECT * FROM p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
351 EXPLAIN (COSTS false) SELECT * FROM p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
353 EXPLAIN (COSTS false) SELECT * FROM p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
355 EXPLAIN (COSTS false) SELECT * FROM p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
356 SET constraint_exclusion TO on;
358 EXPLAIN (COSTS false) SELECT * FROM p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
360 EXPLAIN (COSTS false) SELECT * FROM p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
362 EXPLAIN (COSTS false) SELECT * FROM p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
364 EXPLAIN (COSTS false) SELECT * FROM p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
366 SET constraint_exclusion TO off;
367 EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
368 SET constraint_exclusion TO on;
369 EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
370 SET constraint_exclusion TO off;
372 EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
374 EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
376 EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
378 EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
380 EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
381 /*+MergeJoin(p1 t1)*/
382 EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
384 EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
385 SET constraint_exclusion TO on;
387 EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
389 EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
391 EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
393 EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
395 EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
396 /*+MergeJoin(p1 t1)*/
397 EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
399 EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
401 SET constraint_exclusion TO off;
402 EXPLAIN (COSTS false) SELECT * FROM ONLY p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
403 SET constraint_exclusion TO on;
404 EXPLAIN (COSTS false) SELECT * FROM ONLY p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
405 SET constraint_exclusion TO off;
407 EXPLAIN (COSTS false) SELECT * FROM ONLY p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
409 EXPLAIN (COSTS false) SELECT * FROM ONLY p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
411 EXPLAIN (COSTS false) SELECT * FROM ONLY p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
413 EXPLAIN (COSTS false) SELECT * FROM ONLY p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
415 EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
416 /*+MergeJoin(p1 t1)*/
417 EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
419 EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
420 SET constraint_exclusion TO on;
422 EXPLAIN (COSTS false) SELECT * FROM ONLY p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
424 EXPLAIN (COSTS false) SELECT * FROM ONLY p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
426 EXPLAIN (COSTS false) SELECT * FROM ONLY p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
428 EXPLAIN (COSTS false) SELECT * FROM ONLY p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)';
430 EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
431 /*+MergeJoin(p1 t1)*/
432 EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
434 EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
436 SET constraint_exclusion TO off;
437 EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
438 SET constraint_exclusion TO on;
439 EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
440 SET constraint_exclusion TO off;
442 EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
444 EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
446 EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
448 EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
449 SET constraint_exclusion TO on;
451 EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
453 EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
455 EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
457 EXPLAIN (COSTS false) SELECT * FROM ONLY p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10;
460 /*+SeqScan("""t1 ) ")IndexScan("t 2 """)HashJoin("""t1 ) "T3"t 2 """)Leading("""t1 ) "T3"t 2 """)Set(application_name"a a a"" a A")*/
461 EXPLAIN (COSTS false) SELECT * FROM t1 """t1 ) ", t2 "t 2 """, t3 "T3" WHERE """t1 ) ".id = "t 2 """.id AND """t1 ) ".id = "T3".id;
463 -- duplicate hint test
464 /*+SeqScan(t1)SeqScan(t2)IndexScan(t1)IndexScan(t2)BitmapScan(t1)BitmapScan(t2)TidScan(t1)TidScan(t2)HashJoin(t1 t2)NestLoop(t2 t1)MergeJoin(t1 t2)Leading(t1 t2)Leading(t2 t1)Set(enable_seqscan off)Set(enable_mergejoin on)Set(enable_seqscan on)*/
465 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)' AND t2.ctid = '(1,1)';
467 -- sub query Leading hint test
468 SET from_collapse_limit TO 100;
469 SET geqo_threshold TO 100;
470 EXPLAIN (COSTS false)
472 SELECT max(t1_5.id) FROM t1 t1_5, t2 t2_5, t3 t3_5 WHERE t1_5.id = t2_5.id AND t2_5.id = t3_5.id
475 SELECT max(t1_2.id) FROM t1 t1_2, t2 t2_2, t3 t3_2 WHERE t1_2.id = t2_2.id AND t2_2.id = t3_2.id
476 ) FROM t1 t1_1, t2 t2_1, t3 t3_1, (
477 SELECT t1_3.id FROM t1 t1_3, t2 t2_3, t3 t3_3 WHERE t1_3.id = t2_3.id AND t2_3.id = t3_3.id
478 ) v1_1(id), c1_1 WHERE t1_1.id = t2_1.id AND t2_1.id = t3_1.id AND t2_1.id = v1_1.id AND v1_1.id = c1_1.id AND t1_1.id = (
479 SELECT max(t1_4.id) FROM t1 t1_4, t2 t2_4, t3 t3_4 WHERE t1_4.id = t2_4.id AND t2_4.id = t3_4.id
481 /*+HashJoin(t1_1 t3_1)MergeJoin(t1_3 t3_3)NestLoop(t1_2 t2_2)NestLoop(t1_4 t2_4)NestLoop(t1_5 t2_5)*/
482 EXPLAIN (COSTS false)
484 SELECT max(t1_5.id) FROM t1 t1_5, t2 t2_5, t3 t3_5 WHERE t1_5.id = t2_5.id AND t2_5.id = t3_5.id
487 SELECT max(t1_2.id) FROM t1 t1_2, t2 t2_2, t3 t3_2 WHERE t1_2.id = t2_2.id AND t2_2.id = t3_2.id
488 ) FROM t1 t1_1, t2 t2_1, t3 t3_1, (
489 SELECT t1_3.id FROM t1 t1_3, t2 t2_3, t3 t3_3 WHERE t1_3.id = t2_3.id AND t2_3.id = t3_3.id
490 ) v1_1(id), c1_1 WHERE t1_1.id = t2_1.id AND t2_1.id = t3_1.id AND t2_1.id = v1_1.id AND v1_1.id = c1_1.id AND t1_1.id = (
491 SELECT max(t1_4.id) FROM t1 t1_4, t2 t2_4, t3 t3_4 WHERE t1_4.id = t2_4.id AND t2_4.id = t3_4.id
493 /*+HashJoin(t1_1 t3_1)MergeJoin(t1_3 t3_3)NestLoop(t1_2 t2_2)NestLoop(t1_4 t2_4)NestLoop(t1_5 t2_5)Leading(a t1_1 t1_2 t1_4 t1_5)*/
494 EXPLAIN (COSTS false)
496 SELECT max(t1_5.id) FROM t1 t1_5, t2 t2_5, t3 t3_5 WHERE t1_5.id = t2_5.id AND t2_5.id = t3_5.id
499 SELECT max(t1_2.id) FROM t1 t1_2, t2 t2_2, t3 t3_2 WHERE t1_2.id = t2_2.id AND t2_2.id = t3_2.id
500 ) FROM t1 t1_1, t2 t2_1, t3 t3_1, (
501 SELECT t1_3.id FROM t1 t1_3, t2 t2_3, t3 t3_3 WHERE t1_3.id = t2_3.id AND t2_3.id = t3_3.id
502 ) v1_1(id), c1_1 WHERE t1_1.id = t2_1.id AND t2_1.id = t3_1.id AND t2_1.id = v1_1.id AND v1_1.id = c1_1.id AND t1_1.id = (
503 SELECT max(t1_4.id) FROM t1 t1_4, t2 t2_4, t3 t3_4 WHERE t1_4.id = t2_4.id AND t2_4.id = t3_4.id
505 /*+HashJoin(t1_1 t3_1)MergeJoin(t1_3 t3_3)NestLoop(t1_2 t2_2)NestLoop(t1_4 t2_4)NestLoop(t1_5 t2_5)Leading(a t3_2 t3_5 t2_2 c1_1 t3_4 t3_3 t2_3 t2_4 t1_3 t2_5 t1_2 t3_1 t1_4 t2_1 t1_5 t1_1)*/
506 EXPLAIN (COSTS false)
508 SELECT max(t1_5.id) FROM t1 t1_5, t2 t2_5, t3 t3_5 WHERE t1_5.id = t2_5.id AND t2_5.id = t3_5.id
511 SELECT max(t1_2.id) FROM t1 t1_2, t2 t2_2, t3 t3_2 WHERE t1_2.id = t2_2.id AND t2_2.id = t3_2.id
512 ) FROM t1 t1_1, t2 t2_1, t3 t3_1, (
513 SELECT t1_3.id FROM t1 t1_3, t2 t2_3, t3 t3_3 WHERE t1_3.id = t2_3.id AND t2_3.id = t3_3.id
514 ) v1_1(id), c1_1 WHERE t1_1.id = t2_1.id AND t2_1.id = t3_1.id AND t2_1.id = v1_1.id AND v1_1.id = c1_1.id AND t1_1.id = (
515 SELECT max(t1_4.id) FROM t1 t1_4, t2 t2_4, t3 t3_4 WHERE t1_4.id = t2_4.id AND t2_4.id = t3_4.id
517 /*+HashJoin(t1_1 t3_1)MergeJoin(t1_3 t3_3)NestLoop(t1_2 t2_2)NestLoop(t1_4 t2_4)NestLoop(t1_5 t2_5)Leading(t3_5 t2_5 t1_5)Leading(t3_2 t2_2 t1_2)Leading(t3_4 t2_4 t1_4)Leading(c1_1 t3_3 t2_3 t1_3 t3_1 t2_1 t1_1)*/
518 EXPLAIN (COSTS false)
520 SELECT max(t1_5.id) FROM t1 t1_5, t2 t2_5, t3 t3_5 WHERE t1_5.id = t2_5.id AND t2_5.id = t3_5.id
523 SELECT max(t1_2.id) FROM t1 t1_2, t2 t2_2, t3 t3_2 WHERE t1_2.id = t2_2.id AND t2_2.id = t3_2.id
524 ) FROM t1 t1_1, t2 t2_1, t3 t3_1, (
525 SELECT t1_3.id FROM t1 t1_3, t2 t2_3, t3 t3_3 WHERE t1_3.id = t2_3.id AND t2_3.id = t3_3.id
526 ) v1_1(id), c1_1 WHERE t1_1.id = t2_1.id AND t2_1.id = t3_1.id AND t2_1.id = v1_1.id AND v1_1.id = c1_1.id AND t1_1.id = (
527 SELECT max(t1_4.id) FROM t1 t1_4, t2 t2_4, t3 t3_4 WHERE t1_4.id = t2_4.id AND t2_4.id = t3_4.id
530 SET from_collapse_limit TO 1;
531 EXPLAIN (COSTS false)
533 SELECT max(t1_5.id) FROM t1 t1_5, t2 t2_5, t3 t3_5 WHERE t1_5.id = t2_5.id AND t2_5.id = t3_5.id
536 SELECT max(t1_2.id) FROM t1 t1_2, t2 t2_2, t3 t3_2 WHERE t1_2.id = t2_2.id AND t2_2.id = t3_2.id
537 ) FROM t1 t1_1, t2 t2_1, t3 t3_1, (
538 SELECT t1_3.id FROM t1 t1_3, t2 t2_3, t3 t3_3 WHERE t1_3.id = t2_3.id AND t2_3.id = t3_3.id
539 ) v1_1(id), c1_1 WHERE t1_1.id = t2_1.id AND t2_1.id = t3_1.id AND t2_1.id = v1_1.id AND v1_1.id = c1_1.id AND t1_1.id = (
540 SELECT max(t1_4.id) FROM t1 t1_4, t2 t2_4, t3 t3_4 WHERE t1_4.id = t2_4.id AND t2_4.id = t3_4.id
542 /*+HashJoin(t1_1 t3_1)MergeJoin(t1_3 t3_3)NestLoop(t1_2 t2_2)NestLoop(t1_4 t2_4)NestLoop(t1_5 t2_5)*/
543 EXPLAIN (COSTS false)
545 SELECT max(t1_5.id) FROM t1 t1_5, t2 t2_5, t3 t3_5 WHERE t1_5.id = t2_5.id AND t2_5.id = t3_5.id
548 SELECT max(t1_2.id) FROM t1 t1_2, t2 t2_2, t3 t3_2 WHERE t1_2.id = t2_2.id AND t2_2.id = t3_2.id
549 ) FROM t1 t1_1, t2 t2_1, t3 t3_1, (
550 SELECT t1_3.id FROM t1 t1_3, t2 t2_3, t3 t3_3 WHERE t1_3.id = t2_3.id AND t2_3.id = t3_3.id
551 ) v1_1(id), c1_1 WHERE t1_1.id = t2_1.id AND t2_1.id = t3_1.id AND t2_1.id = v1_1.id AND v1_1.id = c1_1.id AND t1_1.id = (
552 SELECT max(t1_4.id) FROM t1 t1_4, t2 t2_4, t3 t3_4 WHERE t1_4.id = t2_4.id AND t2_4.id = t3_4.id
554 /*+HashJoin(t1_1 t3_1)MergeJoin(t1_3 t3_3)NestLoop(t1_2 t2_2)NestLoop(t1_4 t2_4)NestLoop(t1_5 t2_5)Leading(a t1_1 t1_2 t1_4 t1_5)*/
555 EXPLAIN (COSTS false)
557 SELECT max(t1_5.id) FROM t1 t1_5, t2 t2_5, t3 t3_5 WHERE t1_5.id = t2_5.id AND t2_5.id = t3_5.id
560 SELECT max(t1_2.id) FROM t1 t1_2, t2 t2_2, t3 t3_2 WHERE t1_2.id = t2_2.id AND t2_2.id = t3_2.id
561 ) FROM t1 t1_1, t2 t2_1, t3 t3_1, (
562 SELECT t1_3.id FROM t1 t1_3, t2 t2_3, t3 t3_3 WHERE t1_3.id = t2_3.id AND t2_3.id = t3_3.id
563 ) v1_1(id), c1_1 WHERE t1_1.id = t2_1.id AND t2_1.id = t3_1.id AND t2_1.id = v1_1.id AND v1_1.id = c1_1.id AND t1_1.id = (
564 SELECT max(t1_4.id) FROM t1 t1_4, t2 t2_4, t3 t3_4 WHERE t1_4.id = t2_4.id AND t2_4.id = t3_4.id
566 /*+HashJoin(t1_1 t3_1)MergeJoin(t1_3 t3_3)NestLoop(t1_2 t2_2)NestLoop(t1_4 t2_4)NestLoop(t1_5 t2_5)Leading(a t3_2 t3_5 t2_2 c1_1 t3_4 t3_3 t2_3 t2_4 t1_3 t2_5 t1_2 t3_1 t1_4 t2_1 t1_5 t1_1)*/
567 EXPLAIN (COSTS false)
569 SELECT max(t1_5.id) FROM t1 t1_5, t2 t2_5, t3 t3_5 WHERE t1_5.id = t2_5.id AND t2_5.id = t3_5.id
572 SELECT max(t1_2.id) FROM t1 t1_2, t2 t2_2, t3 t3_2 WHERE t1_2.id = t2_2.id AND t2_2.id = t3_2.id
573 ) FROM t1 t1_1, t2 t2_1, t3 t3_1, (
574 SELECT t1_3.id FROM t1 t1_3, t2 t2_3, t3 t3_3 WHERE t1_3.id = t2_3.id AND t2_3.id = t3_3.id
575 ) v1_1(id), c1_1 WHERE t1_1.id = t2_1.id AND t2_1.id = t3_1.id AND t2_1.id = v1_1.id AND v1_1.id = c1_1.id AND t1_1.id = (
576 SELECT max(t1_4.id) FROM t1 t1_4, t2 t2_4, t3 t3_4 WHERE t1_4.id = t2_4.id AND t2_4.id = t3_4.id
578 /*+HashJoin(t1_1 t3_1)MergeJoin(t1_3 t3_3)NestLoop(t1_2 t2_2)NestLoop(t1_4 t2_4)NestLoop(t1_5 t2_5)Leading(t3_5 t2_5 t1_5)Leading(t3_2 t2_2 t1_2)Leading(t3_4 t2_4 t1_4)Leading(c1_1 t3_3 t2_3 t1_3 t3_1 t2_1 t1_1)*/
579 EXPLAIN (COSTS false)
581 SELECT max(t1_5.id) FROM t1 t1_5, t2 t2_5, t3 t3_5 WHERE t1_5.id = t2_5.id AND t2_5.id = t3_5.id
584 SELECT max(t1_2.id) FROM t1 t1_2, t2 t2_2, t3 t3_2 WHERE t1_2.id = t2_2.id AND t2_2.id = t3_2.id
585 ) FROM t1 t1_1, t2 t2_1, t3 t3_1, (
586 SELECT t1_3.id FROM t1 t1_3, t2 t2_3, t3 t3_3 WHERE t1_3.id = t2_3.id AND t2_3.id = t3_3.id
587 ) v1_1(id), c1_1 WHERE t1_1.id = t2_1.id AND t2_1.id = t3_1.id AND t2_1.id = v1_1.id AND v1_1.id = c1_1.id AND t1_1.id = (
588 SELECT max(t1_4.id) FROM t1 t1_4, t2 t2_4, t3 t3_4 WHERE t1_4.id = t2_4.id AND t2_4.id = t3_4.id
592 EXPLAIN (COSTS false) SELECT * FROM t1, s0.t1, t2 WHERE public.t1.id = s0.t1.id AND public.t1.id = t2.id;
594 EXPLAIN (COSTS false) SELECT * FROM t1, s0.t1, t2 WHERE public.t1.id = s0.t1.id AND public.t1.id = t2.id;
595 /*+Leading(t1 t2 t1)*/
596 EXPLAIN (COSTS false) SELECT * FROM t1, s0.t1, t2 WHERE public.t1.id = s0.t1.id AND public.t1.id = t2.id;
598 -- identifier length test
599 EXPLAIN (COSTS false) SELECT * FROM t1 "123456789012345678901234567890123456789012345678901234567890123" JOIN t2 ON ("123456789012345678901234567890123456789012345678901234567890123".id = t2.id) JOIN t3 ON (t2.id = t3.id);
601 Leading(123456789012345678901234567890123456789012345678901234567890123 t2 t3)
602 SeqScan(123456789012345678901234567890123456789012345678901234567890123)
603 MergeJoin(123456789012345678901234567890123456789012345678901234567890123 t2)
604 Set(123456789012345678901234567890123456789012345678901234567890123 1)
606 EXPLAIN (COSTS false) SELECT * FROM t1 "123456789012345678901234567890123456789012345678901234567890123" JOIN t2 ON ("123456789012345678901234567890123456789012345678901234567890123".id = t2.id) JOIN t3 ON (t2.id = t3.id);
608 Leading(1234567890123456789012345678901234567890123456789012345678901234 t2 t3)
609 SeqScan(1234567890123456789012345678901234567890123456789012345678901234)
610 MergeJoin(1234567890123456789012345678901234567890123456789012345678901234 t2)
611 Set(1234567890123456789012345678901234567890123456789012345678901234 1)
612 Set(cursor_tuple_fraction 0.1234567890123456789012345678901234567890123456789012345678901234)
614 EXPLAIN (COSTS false) SELECT * FROM t1 "1234567890123456789012345678901234567890123456789012345678901234" JOIN t2 ON ("1234567890123456789012345678901234567890123456789012345678901234".id = t2.id) JOIN t3 ON (t2.id = t3.id);
615 SET "123456789012345678901234567890123456789012345678901234567890123" TO 1;
616 SET "1234567890123456789012345678901234567890123456789012345678901234" TO 1;
617 SET cursor_tuple_fraction TO 1234567890123456789012345678901234567890123456789012345678901234;
620 /*+ Set(enable_seqscan 100)Set(seq_page_cost on)*/
621 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
623 -- debug log of candidate index to use IndexScan
624 EXPLAIN (COSTS false) SELECT * FROM t5 WHERE t5.id = 1;
625 /*+IndexScan(t5 t5_id2)*/
626 EXPLAIN (COSTS false) SELECT * FROM t5 WHERE t5.id = 1;
627 /*+IndexScan(t5 no_exist)*/
628 EXPLAIN (COSTS false) SELECT * FROM t5 WHERE t5.id = 1;
629 /*+IndexScan(t5 t5_id1 t5_id2)*/
630 EXPLAIN (COSTS false) SELECT * FROM t5 WHERE t5.id = 1;
631 /*+IndexScan(t5 no_exist t5_id2)*/
632 EXPLAIN (COSTS false) SELECT * FROM t5 WHERE t5.id = 1;
633 /*+IndexScan(t5 no_exist5 no_exist2)*/
634 EXPLAIN (COSTS false) SELECT * FROM t5 WHERE t5.id = 1;
637 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
640 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
641 /*+Leading((t1 t2))*/
642 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
643 /*+Leading((t1 t2 t3))*/
644 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
646 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.id < 10;
647 /*+Leading((t1 t2))*/
648 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.id < 10;
650 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
651 /*+Leading(((t1 t2) t3))*/
652 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
654 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t3.id = t4.id AND t1.val = t3.val AND t1.id < 10;
655 /*+Leading((((t1 t2) t3) t4))*/
656 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t3.id = t4.id AND t1.val = t3.val AND t1.id < 10;
658 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
659 /*+Leading(((t1 t2) t3))*/
660 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
661 /*+Leading((t1 (t2 t3)))*/
662 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
664 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t3.id = t4.id AND t1.val = t3.val AND t1.id < 10;
665 /*+Leading(((t1 t2) (t3 t4)))*/
666 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t3.id = t4.id AND t1.val = t3.val AND t1.id < 10;
668 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < ( SELECT t1_2.id FROM t1 t1_2, t2 t2_2 WHERE t1_2.id = t2_2.id AND t2_2.val > 100 ORDER BY t1_2.id LIMIT 1);
669 /*+Leading(((t1 t2) t3)) Leading(((t3 t1) t2))*/
670 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t1.val = t3.val AND t1.id < ( SELECT t1_2.id FROM t1 t1_2, t2 t2_2 WHERE t1_2.id = t2_2.id AND t2_2.val > 100 ORDER BY t1_2.id LIMIT 1);
671 /*+Leading(((t1 t2) t3)) Leading((t1_2 t2_2))*/
672 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < ( SELECT t1_2.id FROM t1 t1_2, t2 t2_2 WHERE t1_2.id = t2_2.id AND t2_2.val > 100 ORDER BY t1_2.id LIMIT 1);
673 /*+Leading(((((t1 t2) t3) t1_2) t2_2))*/
674 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < ( SELECT t1_2.id FROM t1 t1_2, t2 t2_2 WHERE t1_2.id = t2_2.id AND t2_2.val > 100 ORDER BY t1_2.id LIMIT 1);
676 -- Specified outer/inner leading hint and join method hint at the same time
677 /*+Leading(((t1 t2) t3))*/
678 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
679 /*+Leading(((t1 t2) t3)) MergeJoin(t1 t2)*/
680 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
681 /*+Leading(((t1 t2) t3)) MergeJoin(t1 t2 t3)*/
682 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
683 /*+Leading(((t1 t2) t3)) MergeJoin(t1 t3)*/
684 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
686 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t3.id = t4.id AND t1.val = t3.val AND t1.id < 10;
687 /*+Leading(((t1 t2) t3)) MergeJoin(t3 t4)*/
688 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t3.id = t4.id AND t1.val = t3.val AND t1.id < 10;
689 /*+Leading(((t1 t2) t3)) MergeJoin(t1 t2 t3 t4)*/
690 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t3.id = t4.id AND t1.val = t3.val AND t1.id < 10;
692 /*+ Leading ( ( t1 ( t2 t3 ) ) ) */
693 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
694 /*+Leading((t1(t2 t3)))*/
695 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
696 /*+Leading(("t1(t2" "t3)"))*/
697 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
698 /*+ Leading ( ( ( t1 t2 ) t3 ) ) */
699 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
700 /*+Leading(((t1 t2)t3))*/
701 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
702 /*+Leading(("(t1" "t2)t3"))*/
703 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.val = t3.val AND t1.id < 10;
705 /*+Leading((t1(t2(t3(t4 t5)))))*/
706 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4, t5 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id AND t1.id = t5.id;
707 /*+Leading((t5(t4(t3(t2 t1)))))*/
708 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4, t5 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id AND t1.id = t5.id;
709 /*+Leading(((((t1 t2)t3)t4)t5))*/
710 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4, t5 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id AND t1.id = t5.id;
711 /*+Leading(((((t5 t4)t3)t2)t1))*/
712 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4, t5 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id AND t1.id = t5.id;
713 /*+Leading(((t1 t2)(t3(t4 t5))))*/
714 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4, t5 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id AND t1.id = t5.id;
715 /*+Leading(((t5 t4)(t3(t2 t1))))*/
716 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4, t5 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id AND t1.id = t5.id;
717 /*+Leading((((t1 t2)t3)(t4 t5)))*/
718 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4, t5 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id AND t1.id = t5.id;
719 /*+Leading((((t5 t4)t3)(t2 t1)))*/
720 EXPLAIN (COSTS false) SELECT * FROM t1, t2, t3, t4, t5 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id AND t1.id = t5.id;
722 -- inherite table test to specify the index's name
723 EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
724 /*+IndexScan(p2 p2_pkey)*/
725 EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
726 /*+IndexScan(p2 p2_id_val_idx)*/
727 EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
728 /*+IndexScan(p2 p2_val_id_idx)*/
729 EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
731 EXPLAIN (COSTS false) SELECT val FROM p2 WHERE val >= '50' AND val <= '51' AND p2.ctid = '(1,1)';
732 /*+IndexScan(p2 p2_val)*/
733 EXPLAIN (COSTS false) SELECT val FROM p2 WHERE val >= '50' AND val <= '51' AND p2.ctid = '(1,1)';
734 /*+IndexScan(p2 p2_pkey)*/
735 EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
736 /*+IndexScan(p2 p2_id2_val)*/
737 EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
738 /*+IndexScan(p2 p2_val2_id)*/
739 EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
741 /*+IndexScan(p2 p2_pkey)*/
742 EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
743 /*+IndexScan(p2 p2_c1_id_val_idx)*/
744 EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
745 /*+IndexScan(p2 no_exist)*/
746 EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
747 /*+IndexScan(p2 p2_pkey p2_c1_id_val_idx)*/
748 EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
749 /*+IndexScan(p2 p2_pkey no_exist)*/
750 EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
751 /*+IndexScan(p2 p2_c1_id_val_idx no_exist)*/
752 EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
753 /*+IndexScan(p2 p2_pkey p2_c1_id_val_idx no_exist)*/
754 EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)';
756 /*+IndexScan(p2 p2_val_idx)*/
757 EXPLAIN (COSTS false) SELECT val FROM p2 WHERE val >= '50' AND val <= '51' AND p2.ctid = '(1,1)';
758 /*+IndexScan(p2 p2_expr)*/
759 EXPLAIN (COSTS false) SELECT val FROM p2 WHERE val >= '50' AND val <= '51' AND p2.ctid = '(1,1)';
760 /*+IndexScan(p2 p2_val_idx6)*/
761 EXPLAIN (COSTS false) SELECT val FROM p2 WHERE val >= '50' AND val <= '51' AND p2.ctid = '(1,1)';
762 /*+IndexScan(p2 p2_val_idx p2_val_idx6)*/
763 EXPLAIN (COSTS false) SELECT val FROM p2 WHERE val >= '50' AND val <= '51' AND p2.ctid = '(1,1)';
765 -- regular expression
767 EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
768 /*+ IndexScanRegexp(t5 t5_[^i].*)*/
769 EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
770 /*+ IndexScanRegexp(t5 t5_id[0-9].*)*/
771 EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
772 /*+ IndexScanRegexp(t5 t5[^_].*)*/
773 EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
774 /*+ IndexScanRegexp(t5 ^.*t5_idaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaab)*/
775 EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
776 /*+ IndexScan(t5 t5_id[0-9].*)*/
777 EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
778 /*+ IndexOnlyScanRegexp(t5 t5_[^i].*)*/
779 EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
780 /*+ IndexOnlyScanRegexp(t5 t5_id[0-9].*)*/
781 EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
782 /*+ IndexOnlyScanRegexp(t5 t5[^_].*)*/
783 EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
784 /*+ IndexOnlyScanRegexp(t5 ^.*t5_idaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaab)*/
785 EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
786 /*+ IndexOnlyScan(t5 t5_id[0-9].*)*/
787 EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
788 /*+ BitmapScanRegexp(t5 t5_[^i].*)*/
789 EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
790 /*+ BitmapScanRegexp(t5 t5_id[0-9].*)*/
791 EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
792 /*+ BitmapScanRegexp(t5 t5[^_].*)*/
793 EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
794 /*+ BitmapScanRegexp(t5 ^.*t5_idaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaab)*/
795 EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
796 /*+ BitmapScan(t5 t5_id[0-9].*)*/
797 EXPLAIN (COSTS false) SELECT id FROM t5 WHERE id = 1;
800 EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
801 /*+ IndexScanRegexp(p1 p1_.*[^0-9]$)*/
802 EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
803 /*+ IndexScanRegexp(p1 p1_.*val2.*)*/
804 EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
805 /*+ IndexScanRegexp(p1 p1[^_].*)*/
806 EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
807 /*+ IndexScan(p1 p1_.*val2.*)*/
808 EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
809 /*+ IndexOnlyScanRegexp(p1 p1_.*[^0-9]$)*/
810 EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
811 /*+ IndexOnlyScanRegexp(p1 p1_.*val2.*)*/
812 EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
813 /*+ IndexOnlyScanRegexp(p1 p1[^_].*)*/
814 EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
815 /*+ IndexOnlyScan(p1 p1_.*val2.*)*/
816 EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
817 /*+ BitmapScanRegexp(p1 p1_.*[^0-9]$)*/
818 EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
819 /*+ BitmapScanRegexp(p1 p1_.*val2.*)*/
820 EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
821 /*+ BitmapScanRegexp(p1 p1[^_].*)*/
822 EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
823 /*+ BitmapScan(p1 p1_.*val2.*)*/
824 EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
826 -- search from hint table
827 INSERT INTO hint_plan.hints (norm_query_string, application_name, hints) VALUES ('EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = ?;', '', 'SeqScan(t1)');
828 INSERT INTO hint_plan.hints (norm_query_string, application_name, hints) VALUES ('EXPLAIN (COSTS false) SELECT id FROM t1 WHERE t1.id = ?;', '', 'IndexScan(t1)');
829 INSERT INTO hint_plan.hints (norm_query_string, application_name, hints) VALUES ('EXPLAIN SELECT * FROM t1 WHERE t1.id = ?;', '', 'BitmapScan(t1)');
830 SELECT * FROM hint_plan.hints ORDER BY id;
831 SET pg_hint_plan.enable_hint_table = on;
832 EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = 1;
833 SET pg_hint_plan.enable_hint_table = off;
834 EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = 1;
835 TRUNCATE hint_plan.hints;
836 VACUUM ANALYZE hint_plan.hints;
839 EXPLAIN (COSTS false) SELECT id FROM t1 WHERE t1.id = 1;
842 CREATE FUNCTION testfunc() RETURNS RECORD AS $$
846 SELECT /*+ SeqScan(t1) */ * INTO ret FROM t1 LIMIT 1;
853 DROP FUNCTION testfunc();
854 CREATE FUNCTION testfunc() RETURNS void AS $$
856 EXECUTE format('/*+ SeqScan(t1) */ SELECT * FROM t1');
861 -- This should not use SeqScan(t1)
862 /*+ IndexScan(t1) */ SELECT * from t1 LIMIT 1;
865 DROP FUNCTION testfunc();
866 CREATE FUNCTION testfunc() RETURNS void AS $$
868 PERFORM 1, /*+ SeqScan(t1) */ * from t1;
874 DROP FUNCTION testfunc();
875 CREATE FUNCTION testfunc() RETURNS int AS $$
881 FOR v IN SELECT /*+ SeqScan(t1) */ v FROM t1 ORDER BY id LOOP
890 DROP FUNCTION testfunc();
891 CREATE FUNCTION testfunc() RETURNS int AS $$
898 FOR v IN EXECUTE 'SELECT /*+ SeqScan(t1) */ val FROM t1 ORDER BY id' LOOP
907 DROP FUNCTION testfunc();
908 CREATE FUNCTION testfunc() RETURNS int AS $$
910 ref CURSOR FOR SELECT /*+ SeqScan(t1) */ * FROM t1 ORDER BY id;
915 sum := sum + rec.val;
923 DROP FUNCTION testfunc();
924 CREATE FUNCTION testfunc() RETURNS SETOF t1 AS $$
926 RETURN QUERY SELECT /*+ SeqScan(t1) */ * FROM t1 ORDER BY id;
929 SELECT * FROM testfunc() LIMIT 1;
931 -- Test for error exit from inner SQL statement.
932 DROP FUNCTION testfunc();
933 CREATE FUNCTION testfunc() RETURNS SETOF t1 AS $$
935 RETURN QUERY SELECT /*+ SeqScan(t1) */ * FROM ttx ORDER BY id;
938 SELECT * FROM testfunc() LIMIT 1;
940 -- this should not use SeqScan(t1) hint.
941 /*+ IndexScan(t1) */ SELECT * from t1 LIMIT 1;
943 DROP FUNCTION testfunc();
944 DROP EXTENSION pg_hint_plan;
949 -- Explain result includes "Planning time" if COSTS is enabled, but
950 -- this test needs it enabled for get rows count. So do tests via psql
951 -- and grep -v the mutable line.
954 \o results/pg_hint_plan.tmpout
955 EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id);
957 \! sql/maskout.sh results/pg_hint_plan.tmpout
959 \o results/pg_hint_plan.tmpout
960 /*+ Rows(t1 t2 #99) */
961 EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id);
963 \! sql/maskout.sh results/pg_hint_plan.tmpout
965 \o results/pg_hint_plan.tmpout
966 /*+ Rows(t1 t2 +99) */
967 EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id);
969 \! sql/maskout.sh results/pg_hint_plan.tmpout
971 \o results/pg_hint_plan.tmpout
972 /*+ Rows(t1 t2 -99) */
973 EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id);
975 \! sql/maskout.sh results/pg_hint_plan.tmpout
977 \o results/pg_hint_plan.tmpout
978 /*+ Rows(t1 t2 *99) */
979 EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id);
981 \! sql/maskout.sh results/pg_hint_plan.tmpout
983 \o results/pg_hint_plan.tmpout
984 /*+ Rows(t1 t2 *0.01) */
985 EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id);
987 \! sql/maskout.sh results/pg_hint_plan.tmpout
989 \o results/pg_hint_plan.tmpout
990 /*+ Rows(t1 t2 #aa) */
991 EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id); -- ERROR
993 \! sql/maskout.sh results/pg_hint_plan.tmpout
995 \o results/pg_hint_plan.tmpout
996 /*+ Rows(t1 t2 /99) */
997 EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id); -- ERROR
999 \! sql/maskout.sh results/pg_hint_plan.tmpout
1002 \o results/pg_hint_plan.tmpout
1003 /*+ Rows(t1 t2 -99999) */
1004 EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id);
1006 \! sql/maskout.sh results/pg_hint_plan.tmpout
1008 -- complex join tree
1009 \o results/pg_hint_plan.tmpout
1010 EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id) JOIN t3 ON (t3.id = t2.id);
1012 \! sql/maskout.sh results/pg_hint_plan.tmpout
1014 \o results/pg_hint_plan.tmpout
1015 /*+ Rows(t1 t2 #22) */
1016 EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id) JOIN t3 ON (t3.id = t2.id);
1018 \! sql/maskout.sh results/pg_hint_plan.tmpout
1020 \o results/pg_hint_plan.tmpout
1021 /*+ Rows(t1 t3 *10) */
1022 EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id) JOIN t3 ON (t3.id = t2.id);
1024 \! sql/maskout.sh results/pg_hint_plan.tmpout
1025 \! rm results/pg_hint_plan.tmpout