1 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
2 EXPLAIN SELECT * FROM t1, t2 WHERE t1.val = t2.val;
6 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
7 EXPLAIN SELECT * FROM t1, t2 WHERE t1.val = t2.val;
10 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
11 SET pg_hint_plan.enable TO off;
13 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
14 SET pg_hint_plan.enable TO on;
16 /*Set(enable_indexscan off)*/
17 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
18 /* Set(enable_indexscan off) Set(enable_hashjoin off) */
19 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
21 /* Set ( enable_indexscan off ) */
22 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
30 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
31 /* Set(enable_indexscan off)Set(enable_nestloop off)Set(enable_mergejoin off)
32 Set(enable_seqscan off)
34 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
35 /*Set(work_mem "1M")*/
36 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
37 /*Set(work_mem "1MB")*/
38 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
39 /*Set(work_mem TO "1MB")*/
40 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
42 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
44 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
46 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
47 /*SeqScan(t1)IndexScan(t2)*/
48 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
50 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
51 /*BitmapScan(t2)NoSeqScan(t1)*/
52 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
54 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
55 EXPLAIN SELECT * FROM t1, t4 WHERE t1.val < 10;
57 EXPLAIN SELECT * FROM t1, t4 WHERE t1.val < 10;
58 EXPLAIN SELECT * FROM t3, t4 WHERE t3.id = t4.id AND t4.ctid = '(1,1)';
60 EXPLAIN SELECT * FROM t3, t4 WHERE t3.id = t4.id AND t4.ctid = '(1,1)';
61 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)';
63 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t1.ctid = '(1,1)';
66 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
68 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
70 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
71 /*NoMergeJoin(t1 t2)*/
72 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
74 EXPLAIN SELECT * FROM t1, t3 WHERE t1.val = t3.val;
76 EXPLAIN SELECT * FROM t1, t3 WHERE t1.val = t3.val;
78 EXPLAIN SELECT * FROM t1, t3 WHERE t1.val = t3.val;
80 EXPLAIN SELECT * FROM t1, t3 WHERE t1.val = t3.val;
82 EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
83 /*MergeJoin(t4 t1 t2 t3)*/
84 EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
85 /*HashJoin(t3 t4 t1 t2)*/
86 EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
87 /*NoNestLoop(t4 t1 t3 t2)*/
88 EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
91 EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
93 EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
94 /*Leading(t3 t4 t1 t2)*/
95 EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
96 /*Leading(t3 t4 t1 t2 t1)*/
97 EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
99 EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t1.id = t3.id AND t1.id = t4.id;
102 SET pg_hint_plan.debug_print TO true;
103 /* NestLoop (t1 t2) */
104 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
105 /* NestLoop (t1 t2) */
106 EXPLAIN SELECT * FROM t1, t2 WHERE t1.val = t2.val;
108 /* HashJoin (t1 t2) */
109 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
110 /* HashJoin (t1 t2) */
111 EXPLAIN SELECT * FROM t1, t2 WHERE t1.val = t2.val;
113 /* MergeJoin (t1 t2) */
114 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
115 /* MergeJoin (t1 t2) */
116 EXPLAIN SELECT * FROM t1, t2 WHERE t1.val = t2.val;
118 /* NoMergeJoin (t1 t2) */
119 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
120 /* NoHashJoin (t1 t2) */
121 EXPLAIN SELECT * FROM t1, t2 WHERE t1.val = t2.val;
123 EXPLAIN SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.id = t3.id;
124 EXPLAIN SELECT * FROM t1, t2, t3 WHERE t1.val = t2.val AND t2.val = t3.val;
126 /* NestLoop (t1 t2 t3) NestLoop (t1 t3) */
127 EXPLAIN SELECT * FROM t1, t2, t3 WHERE t1.id = t2.id AND t2.id = t3.id;
128 /* NestLoop (t1 t2 t3) NestLoop (t1 t3) */
129 EXPLAIN SELECT * FROM t1, t2, t3 WHERE t1.val = t2.val AND t2.val = t3.val;
131 EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t2.id = t3.id AND t3.id = t4.id;
132 EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t2.id = t3.id AND t3.id = t4.id;
133 SET enable_mergejoin TO off;
134 EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t2.id = t3.id AND t3.id = t4.id;
135 EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t2.id = t3.id AND t3.id = t4.id;
136 EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t2.id = t3.id AND t3.id = t4.id;
137 SET enable_mergejoin TO on;
139 SET join_collapse_limit TO 10;
140 EXPLAIN SELECT * FROM t1 CROSS JOIN t2 CROSS JOIN t3 CROSS JOIN t4 WHERE t1.id = t2.id AND t2.id = t3.id AND t3.id = t4.id;
141 SET join_collapse_limit TO 1;
142 EXPLAIN SELECT * FROM t1 CROSS JOIN t2 CROSS JOIN t3 CROSS JOIN t4 WHERE t1.id = t2.id AND t2.id = t3.id AND t3.id = t4.id;
143 EXPLAIN SELECT * FROM t2 CROSS JOIN t3 CROSS JOIN t4 CROSS JOIN t1 WHERE t1.id = t2.id AND t2.id = t3.id AND t3.id = t4.id;
144 EXPLAIN SELECT * FROM t1 CROSS JOIN (t2 CROSS JOIN t3 CROSS JOIN t4) WHERE t1.id = t2.id AND t2.id = t3.id AND t3.id = t4.id;
145 EXPLAIN SELECT * FROM t1 JOIN t2 ON (t1.id = t2.id) JOIN t3 ON (t2.id = t3.id) JOIN t4 ON (t3.id = t4.id);
147 EXPLAIN SELECT * FROM v2;
148 EXPLAIN SELECT * FROM v3 v_3;
149 EXPLAIN SELECT * FROM v2 v_2, v3 v_3 WHERE v_2.t1_id = v_3.t1_id;
151 EXPLAIN SELECT * FROM t1 t_1, v2 v_2 WHERE t_1.id = v_2.t1_id;
152 SET from_collapse_limit TO 1;
153 EXPLAIN SELECT * FROM t1 t_1, v2 v_2 WHERE t_1.id = v_2.t1_id;
155 /* test */ EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
156 /* HashJoin(t1 t2) MergeJoin(t_1 t_2) NestLoop(t2 t1)*/ EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
157 SET join_collapse_limit TO 10;
158 /* Set(join_collapse_limit "1") */ EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
159 /* Set(join_collapse_limit "10") */ EXPLAIN SELECT * FROM t1 CROSS JOIN t2 CROSS JOIN t3 CROSS JOIN t4 WHERE t1.id = t2.id AND t2.id = t3.id AND t3.id = t4.id;
160 SHOW join_collapse_limit;
161 /* Set(join_collapse_limit "1") */ EXPLAIN SELECT * FROM t1 CROSS JOIN t2 CROSS JOIN t3 CROSS JOIN t4 WHERE t1.id = t2.id AND t2.id = t3.id AND t3.id = t4.id;
162 SHOW join_collapse_limit;
163 /* Set(join_collapse_limit "1") */ EXPLAIN SELECT * FROM t1 CROSS JOIN t2 CROSS JOIN t3 CROSS JOIN t4 WHERE t1.id = t2.id AND t2.id = t3.id AND t3.id = t4.id;
164 SHOW join_collapse_limit;
166 /* Set(cursor_tuple_fraction "1") */ EXPLAIN DECLARE c1 CURSOR FOR SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t2.val = 1 ORDER BY t1.id;
167 /* Set(cursor_tuple_fraction "0.5") */ EXPLAIN DECLARE c1 CURSOR FOR SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t2.val = 1 ORDER BY t1.id;
168 /* Set (cursor_tuple_fraction "0.4") */ EXPLAIN DECLARE c1 CURSOR FOR SELECT * FROM t1, t2 WHERE t1.id = t2.id AND t2.val = 1 ORDER BY t1.id;
170 /* Leading (t1 t2 t3 t4) */EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t2.id = t3.id AND t3.id = t4.id;
171 /* Leading (t4 t3 t2 t1) */EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t2.id = t3.id AND t3.id = t4.id;
173 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id < 1 AND t2.id < 1 AND t1.id = t2.id;
175 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id < 1 AND t2.id < 1 AND t1.id = t2.id;
177 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id < 1 AND t2.id < 1 AND t1.id = t2.id;
178 /* SeqScan (t1) SeqScan (t2) */
179 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id < 1 AND t2.id < 1 AND t1.id = t2.id;
181 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id < 1 AND t2.id < 1 AND t1.id = t2.id;
183 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id < 1 AND t2.id < 1 AND t1.id = t2.id;
184 /* BitmapScan (t1) BitmapScan (t2) */
185 EXPLAIN SELECT * FROM t1, t2 WHERE t1.id < 1 AND t2.id < 1 AND t1.id = t2.id;
187 EXPLAIN SELECT * FROM t1, t4 WHERE t1.id < 1 AND t4.id < 1 AND t1.id = t4.id AND t1.ctid = '(1, 1)' AND t4.ctid = '(1, 1)';
189 EXPLAIN SELECT * FROM t1, t4 WHERE t1.id < 1 AND t4.id < 1 AND t1.id = t4.id AND t1.ctid = '(1, 1)' AND t4.ctid = '(1, 1)';
190 /* IndexScan(t1) TidScan(t4)*/
191 EXPLAIN SELECT * FROM t1, t4 WHERE t1.id < 1 AND t4.id < 1 AND t1.id = t4.id AND t1.ctid = '(1, 1)' AND t4.ctid = '(1, 1)';
193 EXPLAIN SELECT * FROM t1, t4 WHERE t1.id < 1 AND t4.id < 1 AND t1.id = t4.id AND t1.ctid = '(1, 1)' AND t4.ctid = '(1, 1)';
195 EXPLAIN SELECT * FROM t1, t4 WHERE t1.id < 1 AND t4.id < 1 AND t1.id = t4.id AND t1.ctid = '(1, 1)' AND t4.ctid = '(1, 1)';
196 /* NoSeqScan(t4) NoTidScan(t1) */
197 EXPLAIN SELECT * FROM t1, t4 WHERE t1.id < 1 AND t4.id < 1 AND t1.id = t4.id AND t1.ctid = '(1, 1)' AND t4.ctid = '(1, 1)';
198 /* NoSeqScan(t4) NoTidScan(t1) NoNestLoop(t1 t4)*/
199 EXPLAIN SELECT * FROM t1, t4 WHERE t1.id < 1 AND t4.id < 1 AND t1.id = t4.id AND t1.ctid = '(1, 1)' AND t4.ctid = '(1, 1)';
201 EXPLAIN SELECT * FROM t1, t5 WHERE t1.id = t5.id AND t1.id < 1000 AND t5.val < 10;
202 /* NoIndexScan(t1) */
203 EXPLAIN SELECT * FROM t1, t5 WHERE t1.id = t5.id AND t1.id < 1000 AND t5.val < 10;
204 /* NoBitmapScan(t5) NoIndexScan(t1) */
205 EXPLAIN SELECT * FROM t1, t5 WHERE t1.id = t5.id AND t1.id < 1000 AND t5.val < 10;
207 --EXPLAIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
208 --EXPLAIN SELECT * FROM t1, t4 WHERE t1.id = t4.id;
210 EXPLAIN SELECT * FROM t1, t3 WHERE t1.id = t3.id;
212 EXPLAIN SELECT * FROM t1, t3 WHERE t1.id = t3.id;
214 EXPLAIN SELECT * FROM t1, t3 WHERE t1.id = t3.id;
215 /* SeqScan (t1) IndexScan (t3) */
216 EXPLAIN SELECT * FROM t1, t3 WHERE t1.id = t3.id;
217 /* SeqScan (t3) IndexScan (t1) */
218 EXPLAIN SELECT * FROM t1, t3 WHERE t1.id = t3.id;
219 /* IndexScan (t1) IndexScan (t3) */
220 EXPLAIN SELECT * FROM t1, t3 WHERE t1.id = t3.id;
222 EXPLAIN SELECT * FROM t1 WHERE id < 10000;
224 EXPLAIN SELECT * FROM t1 WHERE id < 10000;
226 EXPLAIN SELECT * FROM t1 WHERE id = 1;
228 EXPLAIN SELECT * FROM t1 WHERE id = 1;
230 EXPLAIN SELECT * FROM t1, t4 WHERE t1.id > 100;
232 EXPLAIN SELECT * FROM t1, t4 WHERE t1.id > 100;
234 EXPLAIN SELECT * FROM t1, t4 WHERE t1.id > 100;
235 /* Set (enable_seqscan off) Set (enable_indexscan off) */
236 EXPLAIN SELECT * FROM t1, t4 WHERE t1.id > 100;
238 CREATE INDEX t1_val ON t1 (val);
239 CREATE INDEX t1_val_id ON t1 (val, id);
240 EXPLAIN SELECT * FROM t1, t3 WHERE t1.val = t3.val;
241 /* IndexScan (t1 t1_val_id) */
242 EXPLAIN SELECT * FROM t1, t3 WHERE t1.val = t3.val;
243 /* IndexScan (t1 t1_pkey) */
244 EXPLAIN SELECT * FROM t1, t3 WHERE t1.val = t3.val;
245 /* IndexScan (t1 t1_val_id t1_val) */
246 EXPLAIN SELECT * FROM t1, t3 WHERE t1.val = t3.val;
247 /* IndexScan (t1 t2_val) */
248 EXPLAIN SELECT * FROM t1, t3 WHERE t1.val = t3.val;
250 SET pg_hint_plan.debug_print TO true;
251 /* HashJoin(a b c A B C z y x Z Y X) HashJoin (t1 t3) MergeJoin(a b c A B C z y x Z Y X) MergeJoin (t3 t1) NestLoop(a b c A B C z y x Z Y X) */
252 EXPLAIN SELECT * FROM t1, t3 WHERE t1.val = t3.val;
253 CREATE INDEX t3_val ON t3 (val);
254 /* Leading(t3) IndexScan(t4) Set (cursor_tuple_fraction "1.0") Leading(t3 t1 t4) HashJoin(t3 t1) BitmapScan(t3) HashJoin(a b c) IndexScan(t2) MergeJoin(A B C) MergeJoin (B C A) NestLoop(b c a) MergeJoin(c a b) IndexScan(t3) NestLoop(C A B) IndexScan(t1) SeqScan(t1) SeqScan(t1) SeqScan(t1) MergeJoin(t3 t1) Leading(t2 t1 t4)*/
255 EXPLAIN SELECT * FROM t1, t3 WHERE t1.val = t3.val ORDER BY t3.val;
257 EXPLAIN SELECT * FROM t1 WHERE t1.val < 10000;
258 /* Set(enable_seqscan "off") */
259 EXPLAIN SELECT * FROM t1 WHERE t1.val < 10000;
261 EXPLAIN SELECT * FROM t1 WHERE t1.val < 10000;
263 EXPLAIN SELECT * FROM v1 WHERE v1.val < 10000;
264 /* Set(enable_seqscan "off") */
265 EXPLAIN SELECT * FROM v1 WHERE v1.val < 10000;
267 EXPLAIN SELECT * FROM v1 WHERE v1.val < 10000;
269 EXPLAIN SELECT * FROM v4;
270 /* Set(enable_seqscan "off") */
271 EXPLAIN SELECT * FROM v4;
273 EXPLAIN SELECT * FROM v2;
274 /* BitmapScan(t_3) */
275 EXPLAIN SELECT * FROM v4;
277 EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t2.id = t3.id AND t3.id = t4.id;
278 /* NestLoop(t1 t2) NoNestLoop(t1 t2 t4) Leading(t1 t2 t3 t4) */
279 EXPLAIN SELECT * FROM t1, t2, t3, t4 WHERE t1.id = t2.id AND t2.id = t3.id AND t3.id = t4.id;