2 SET pg_hint_plan.enable_hint TO on;
3 SET pg_hint_plan.debug_print TO on;
4 SET client_min_messages TO LOG;
5 SET search_path TO public;
7 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1;
8 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
9 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c3 < 10;
10 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
13 ---- No. S-1-1 specified pattern of the object name
18 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
22 EXPLAIN (COSTS false) SELECT * FROM s1.t1 t_1 WHERE t_1.c1 = 1;
26 EXPLAIN (COSTS false) SELECT * FROM s1.t1 t_1 WHERE t_1.c1 = 1;
29 ---- No. S-1-2 specified schema name in the hint option
34 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
38 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
41 ---- No. S-1-3 table doesn't exist in the hint option
46 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
50 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
53 ---- No. S-1-4 conflict table name
57 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = 1 AND t1.c1 = t2.c1;
59 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = 1 AND t1.c1 = t2.c1;
62 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s2.t1 WHERE s1.t1.c1 = 1 AND s1.t1.c1 = s2.t1.c1;
64 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s2.t1 WHERE s1.t1.c1 = 1 AND s1.t1.c1 = s2.t1.c1;
67 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s2.t1 s2t1 WHERE s1.t1.c1 = 1 AND s1.t1.c1 = s2t1.c1;
69 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s2.t1 s2t1 WHERE s1.t1.c1 = 1 AND s1.t1.c1 = s2t1.c1;
72 EXPLAIN (COSTS false) SELECT (SELECT max(c1) FROM s1.t1 WHERE s1.t1.c1 = 1) FROM s1.t1 WHERE s1.t1.c1 = 1;
74 EXPLAIN (COSTS false) SELECT (SELECT max(c1) FROM s1.t1 WHERE s1.t1.c1 = 1) FROM s1.t1 WHERE s1.t1.c1 = 1;
77 EXPLAIN (COSTS false) SELECT (SELECT max(c1) FROM s1.t1 t11 WHERE t11.c1 = 1) FROM s1.t1 t12 WHERE t12.c1 = 1;
79 EXPLAIN (COSTS false) SELECT (SELECT max(c1) FROM s1.t1 t11 WHERE t11.c1 = 1) FROM s1.t1 t12 WHERE t12.c1 = 1;
82 ---- No. S-1-5 object type for the hint
87 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
90 EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE p1.c1 = 1;
92 EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE p1.c1 = 1;
95 EXPLAIN (COSTS false) SELECT * FROM s1.ul1 WHERE ul1.c1 = 1;
97 EXPLAIN (COSTS false) SELECT * FROM s1.ul1 WHERE ul1.c1 = 1;
100 CREATE TEMP TABLE tm1 (LIKE s1.t1 INCLUDING ALL);
101 EXPLAIN (COSTS false) SELECT * FROM tm1 WHERE tm1.c1 = 1;
103 EXPLAIN (COSTS false) SELECT * FROM tm1 WHERE tm1.c1 = 1;
106 EXPLAIN (COSTS false) SELECT * FROM pg_catalog.pg_class WHERE oid = 1;
107 /*+SeqScan(pg_class)*/
108 EXPLAIN (COSTS false) SELECT * FROM pg_catalog.pg_class WHERE oid = 1;
114 EXPLAIN (COSTS false) SELECT * FROM s1.f1() AS ft1 WHERE ft1.c1 = 1;
116 EXPLAIN (COSTS false) SELECT * FROM s1.f1() AS ft1 WHERE ft1.c1 = 1;
119 EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1'), (2,2,2,'2'), (3,3,3,'3')) AS val1 (c1, c2, c3, c4) WHERE val1.c1 = 1;
121 EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1'), (2,2,2,'2'), (3,3,3,'3')) AS val1 (c1, c2, c3, c4) WHERE val1.c1 = 1;
122 /*+SeqScan(*VALUES*)*/
123 EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1'), (2,2,2,'2'), (3,3,3,'3')) AS val1 (c1, c2, c3, c4) WHERE val1.c1 = 1;
126 EXPLAIN (COSTS false) WITH c1(c1) AS (SELECT max(c1) FROM s1.t1 WHERE t1.c1 = 1)
127 SELECT * FROM s1.t1, c1 WHERE t1.c1 = 1 AND t1.c1 = c1.c1;
129 EXPLAIN (COSTS false) WITH c1(c1) AS (SELECT max(c1) FROM s1.t1 WHERE t1.c1 = 1)
130 SELECT * FROM s1.t1, c1 WHERE t1.c1 = 1 AND t1.c1 = c1.c1;
133 EXPLAIN (COSTS false) SELECT * FROM s1.v1 WHERE v1.c1 = 1;
135 EXPLAIN (COSTS false) SELECT * FROM s1.v1 WHERE v1.c1 = 1;
138 EXPLAIN (COSTS false) SELECT * FROM (SELECT * FROM s1.t1 WHERE t1.c1 = 1) AS s1 WHERE s1.c1 = 1;
140 EXPLAIN (COSTS false) SELECT * FROM (SELECT * FROM s1.t1 WHERE t1.c1 = 1) AS s1 WHERE s1.c1 = 1;
143 ---- No. S-2-1 some complexity query blocks
147 EXPLAIN (COSTS false)
148 SELECT max(bmt1.c1), (
149 SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1
151 FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1
153 /*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4)
154 TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey)
156 EXPLAIN (COSTS false)
157 SELECT max(bmt1.c1), (
158 SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1
160 FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1
164 EXPLAIN (COSTS false)
165 SELECT max(bmt1.c1), (
166 SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1
168 SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1
170 FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1
172 /*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4)
173 TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey)
174 BitmapScan(b2t1 t1_pkey)TidScan(b2t2)SeqScan(b2t3)IndexScan(b2t4 t4_pkey)
176 EXPLAIN (COSTS false)
177 SELECT max(bmt1.c1), (
178 SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1
180 SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1
182 FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1
186 EXPLAIN (COSTS false) SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, (SELECT * FROM s1.t4 bmt4) sbmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = sbmt4.c1;
187 /*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4)
189 EXPLAIN (COSTS false) SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, (SELECT * FROM s1.t4 bmt4) sbmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = sbmt4.c1;
192 EXPLAIN (COSTS false) SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, (SELECT * FROM s1.t3 bmt3) sbmt3, (SELECT * FROM s1.t4 bmt4) sbmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = sbmt3.c1 AND bmt1.c1 = sbmt4.c1;
193 /*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4)
195 EXPLAIN (COSTS false) SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, (SELECT * FROM s1.t3 bmt3) sbmt3, (SELECT * FROM s1.t4 bmt4) sbmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = sbmt3.c1 AND bmt1.c1 = sbmt4.c1;
198 EXPLAIN (COSTS false)
199 SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1
201 SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1
204 /*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4)
205 TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey)
207 EXPLAIN (COSTS false)
208 SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1
210 SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1
215 EXPLAIN (COSTS false)
216 SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1
218 SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1
220 SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1
223 /*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4)
224 TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey)
225 BitmapScan(b2t1 t1_pkey)TidScan(b2t2)SeqScan(b2t3)IndexScan(b2t4 t4_pkey)
227 EXPLAIN (COSTS false)
228 SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1
230 SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1
232 SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1
237 EXPLAIN (COSTS false)
239 SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1
241 SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4
243 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1
246 /*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4)
247 TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey)
249 EXPLAIN (COSTS false)
251 SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1
253 SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4
255 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1
260 EXPLAIN (COSTS false)
262 SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1
265 SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1
267 SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4
269 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1
273 /*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4)
274 TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey)
275 BitmapScan(b2t1 t1_pkey)TidScan(b2t2)SeqScan(b2t3)IndexScan(b2t4 t4_pkey)
277 EXPLAIN (COSTS false)
279 SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.c1 = b1t2.c1 AND b1t1.c1 = b1t3.c1 AND b1t1.c1 = b1t4.c1
282 SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.c1 = b2t2.c1 AND b2t1.c1 = b2t3.c1 AND b2t1.c1 = b2t4.c1
284 SELECT max(bmt1.c1) FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4
286 WHERE bmt1.c1 = bmt2.c1 AND bmt1.c1 = bmt3.c1 AND bmt1.c1 = bmt4.c1
292 ---- No. S-2-2 the number of the tables per quiry block
296 EXPLAIN (COSTS false)
298 SELECT max(b1t1.c1) FROM s1.t1 b1t1 WHERE b1t1.ctid = '(1,1)' AND b1t1.c1 = 1
300 SELECT max(bmt1.c1), (
301 SELECT max(b2t1.c1) FROM s1.t1 b2t1 WHERE b2t1.ctid = '(1,1)' AND b2t1.c1 = 1
303 FROM s1.t1 bmt1, c1 WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = 1
305 SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)' AND b3t1.c1 = 1
310 BitmapScan(b2t1 t1_pkey)
311 IndexScan(b3t1 t1_pkey)
313 EXPLAIN (COSTS false)
315 SELECT max(b1t1.c1) FROM s1.t1 b1t1 WHERE b1t1.ctid = '(1,1)' AND b1t1.c1 = 1
317 SELECT max(bmt1.c1), (
318 SELECT max(b2t1.c1) FROM s1.t1 b2t1 WHERE b2t1.ctid = '(1,1)' AND b2t1.c1 = 1
320 FROM s1.t1 bmt1, c1 WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = 1
322 SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)' AND b3t1.c1 = 1
327 EXPLAIN (COSTS false)
329 SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2 WHERE b1t1.ctid = '(1,1)' AND b1t1.c1 = b1t2.c1 AND b1t2.ctid = '(1,1)'
331 SELECT max(bmt1.c1), (
332 SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2 WHERE b2t1.ctid = '(1,1)' AND b2t1.c1 = b2t2.c1 AND b2t2.ctid = '(1,1)'
334 FROM s1.t1 bmt1, s1.t2 bmt2, c1 WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = bmt2.c1 AND bmt2.ctid = '(1,1)'
336 SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2 WHERE b3t1.ctid = '(1,1)' AND b3t1.c1 = b3t2.c1 AND b3t2.ctid = '(1,1)'
339 /*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)
340 TidScan(b1t1)SeqScan(b1t2)
341 BitmapScan(b2t1 t1_pkey)TidScan(b2t2)
342 IndexScan(b3t1 t1_pkey)BitmapScan(b3t2 t2_pkey)
344 EXPLAIN (COSTS false)
346 SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2 WHERE b1t1.ctid = '(1,1)' AND b1t1.c1 = b1t2.c1 AND b1t2.ctid = '(1,1)'
348 SELECT max(bmt1.c1), (
349 SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2 WHERE b2t1.ctid = '(1,1)' AND b2t1.c1 = b2t2.c1 AND b2t2.ctid = '(1,1)'
351 FROM s1.t1 bmt1, s1.t2 bmt2, c1 WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = bmt2.c1 AND bmt2.ctid = '(1,1)'
353 SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2 WHERE b3t1.ctid = '(1,1)' AND b3t1.c1 = b3t2.c1 AND b3t2.ctid = '(1,1)'
358 EXPLAIN (COSTS false)
360 SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.ctid = '(1,1)' AND b1t1.c1 = b1t2.c1 AND b1t2.ctid = '(1,1)' AND b1t1.c1 = b1t3.c1 AND b1t3.ctid = '(1,1)' AND b1t1.c1 = b1t4.c1 AND b1t4.ctid = '(1,1)'
362 SELECT max(bmt1.c1), (
363 SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.ctid = '(1,1)' AND b2t1.c1 = b2t2.c1 AND b2t2.ctid = '(1,1)' AND b2t1.c1 = b2t3.c1 AND b2t3.ctid = '(1,1)' AND b2t1.c1 = b2t4.c1 AND b2t4.ctid = '(1,1)'
365 FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4, c1 WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = bmt2.c1 AND bmt2.ctid = '(1,1)' AND bmt1.c1 = bmt3.c1 AND bmt3.ctid = '(1,1)' AND bmt1.c1 = bmt4.c1 AND bmt4.ctid = '(1,1)' AND bmt1.c1 = c1.c1
367 SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3t1.ctid = '(1,1)' AND b3t1.c1 = b3t2.c1 AND b3t2.ctid = '(1,1)' AND b3t1.c1 = b3t3.c1 AND b3t3.ctid = '(1,1)' AND b3t1.c1 = b3t4.c1 AND b3t4.ctid = '(1,1)'
370 /*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4)
371 TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey)
372 BitmapScan(b2t1 t1_pkey)TidScan(b2t2)SeqScan(b2t3)IndexScan(b2t4 t4_pkey)
373 IndexScan(b3t1 t1_pkey)BitmapScan(b3t2 t2_pkey)TidScan(b3t3)SeqScan(b3t4)
375 EXPLAIN (COSTS false)
377 SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.ctid = '(1,1)' AND b1t1.c1 = b1t2.c1 AND b1t2.ctid = '(1,1)' AND b1t1.c1 = b1t3.c1 AND b1t3.ctid = '(1,1)' AND b1t1.c1 = b1t4.c1 AND b1t4.ctid = '(1,1)'
379 SELECT max(bmt1.c1), (
380 SELECT max(b2t1.c1) FROM s1.t1 b2t1, s1.t2 b2t2, s1.t3 b2t3, s1.t4 b2t4 WHERE b2t1.ctid = '(1,1)' AND b2t1.c1 = b2t2.c1 AND b2t2.ctid = '(1,1)' AND b2t1.c1 = b2t3.c1 AND b2t3.ctid = '(1,1)' AND b2t1.c1 = b2t4.c1 AND b2t4.ctid = '(1,1)'
382 FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4, c1 WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = bmt2.c1 AND bmt2.ctid = '(1,1)' AND bmt1.c1 = bmt3.c1 AND bmt3.ctid = '(1,1)' AND bmt1.c1 = bmt4.c1 AND bmt4.ctid = '(1,1)' AND bmt1.c1 = c1.c1
384 SELECT max(b3t1.c1) FROM s1.t1 b3t1, s1.t2 b3t2, s1.t3 b3t3, s1.t4 b3t4 WHERE b3t1.ctid = '(1,1)' AND b3t1.c1 = b3t2.c1 AND b3t2.ctid = '(1,1)' AND b3t1.c1 = b3t3.c1 AND b3t3.ctid = '(1,1)' AND b3t1.c1 = b3t4.c1 AND b3t4.ctid = '(1,1)'
389 EXPLAIN (COSTS false)
391 SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.ctid = '(1,1)' AND b1t1.c1 = b1t2.c1 AND b1t2.ctid = '(1,1)' AND b1t1.c1 = b1t3.c1 AND b1t3.ctid = '(1,1)' AND b1t1.c1 = b1t4.c1 AND b1t4.ctid = '(1,1)'
393 SELECT max(bmt1.c1), (
394 SELECT max(b2t1.c1) FROM s1.t1 b2t1 WHERE b2t1.ctid = '(1,1)' AND b2t1.c1 = 1
396 FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4, c1 WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = bmt2.c1 AND bmt2.ctid = '(1,1)' AND bmt1.c1 = bmt3.c1 AND bmt3.ctid = '(1,1)' AND bmt1.c1 = bmt4.c1 AND bmt4.ctid = '(1,1)' AND bmt1.c1 = c1.c1
398 SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)'
401 /*+SeqScan(bmt1)IndexScan(bmt2 t2_pkey)BitmapScan(bmt3 t3_pkey)TidScan(bmt4)
402 TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey)
403 BitmapScan(b2t1 t1_pkey)
404 IndexScan(b3t1 t1_pkey)
406 EXPLAIN (COSTS false)
408 SELECT max(b1t1.c1) FROM s1.t1 b1t1, s1.t2 b1t2, s1.t3 b1t3, s1.t4 b1t4 WHERE b1t1.ctid = '(1,1)' AND b1t1.c1 = b1t2.c1 AND b1t2.ctid = '(1,1)' AND b1t1.c1 = b1t3.c1 AND b1t3.ctid = '(1,1)' AND b1t1.c1 = b1t4.c1 AND b1t4.ctid = '(1,1)'
410 SELECT max(bmt1.c1), (
411 SELECT max(b2t1.c1) FROM s1.t1 b2t1 WHERE b2t1.ctid = '(1,1)' AND b2t1.c1 = 1
413 FROM s1.t1 bmt1, s1.t2 bmt2, s1.t3 bmt3, s1.t4 bmt4, c1 WHERE bmt1.ctid = '(1,1)' AND bmt1.c1 = bmt2.c1 AND bmt2.ctid = '(1,1)' AND bmt1.c1 = bmt3.c1 AND bmt3.ctid = '(1,1)' AND bmt1.c1 = bmt4.c1 AND bmt4.ctid = '(1,1)' AND bmt1.c1 = c1.c1
415 SELECT max(b3t1.c1) FROM s1.t1 b3t1 WHERE b3t1.ctid = '(1,1)'
420 ---- No. S-2-3 RULE or VIEW
424 EXPLAIN (COSTS false) UPDATE s1.r1 SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
425 /*+TidScan(t1)SeqScan(t2)IndexScan(t3 t3_pkey)BitmapScan(t4 t4_pkey)
427 EXPLAIN (COSTS false) UPDATE s1.r1 SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
428 EXPLAIN (COSTS false) UPDATE s1.r1_ SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
429 /*+TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey)
431 EXPLAIN (COSTS false) UPDATE s1.r1_ SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
434 EXPLAIN (COSTS false) UPDATE s1.r2 SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
435 /*+TidScan(t1)SeqScan(t2)IndexScan(t3 t3_pkey)BitmapScan(t4 t4_pkey)
437 EXPLAIN (COSTS false) UPDATE s1.r2 SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
438 EXPLAIN (COSTS false) UPDATE s1.r2_ SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
439 /*+TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey)
440 BitmapScan(b2t1 t1_pkey)TidScan(b2t2)SeqScan(b2t3)IndexScan(b2t4 t4_pkey)
442 EXPLAIN (COSTS false) UPDATE s1.r2_ SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
445 EXPLAIN (COSTS false) UPDATE s1.r3 SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
446 /*+TidScan(t1)SeqScan(t2)IndexScan(t3 t3_pkey)BitmapScan(t4 t4_pkey)
448 EXPLAIN (COSTS false) UPDATE s1.r3 SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
449 EXPLAIN (COSTS false) UPDATE s1.r3_ SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
450 /*+TidScan(b1t1)SeqScan(b1t2)IndexScan(b1t3 t3_pkey)BitmapScan(b1t4 t4_pkey)
451 BitmapScan(b2t1 t1_pkey)TidScan(b2t2)SeqScan(b2t3)IndexScan(b2t4 t4_pkey)
452 IndexScan(b3t1 t1_pkey)BitmapScan(b3t2 t2_pkey)TidScan(b3t3)SeqScan(b3t4)
454 EXPLAIN (COSTS false) UPDATE s1.r3_ SET c1 = c1 WHERE c1 = 1 AND ctid = '(1,1)';
457 EXPLAIN (COSTS false) SELECT * FROM s1.v1 v1, s1.v1 v2 WHERE v1.c1 = v2.c1;
458 /*+BitmapScan(v1t1)*/
459 EXPLAIN (COSTS false) SELECT * FROM s1.v1 v1, s1.v1 v2 WHERE v1.c1 = v2.c1;
462 EXPLAIN (COSTS false) SELECT * FROM s1.v1 v1, s1.v1_ v2 WHERE v1.c1 = v2.c1;
463 /*+SeqScan(v1t1)BitmapScan(v1t1_)*/
464 EXPLAIN (COSTS false) SELECT * FROM s1.v1 v1, s1.v1_ v2 WHERE v1.c1 = v2.c1;
467 EXPLAIN (COSTS false) SELECT * FROM s1.r4 t1, s1.r4 t2 WHERE t1.c1 = t2.c1;
468 /*+BitmapScan(r4t1)*/
469 EXPLAIN (COSTS false) SELECT * FROM s1.r4 t1, s1.r4 t2 WHERE t1.c1 = t2.c1;
472 EXPLAIN (COSTS false) SELECT * FROM s1.r4 t1, s1.r5 t2 WHERE t1.c1 = t2.c1;
473 /*+SeqScan(r4t1)BitmapScan(r5t1)*/
474 EXPLAIN (COSTS false) SELECT * FROM s1.r4 t1, s1.r5 t2 WHERE t1.c1 = t2.c1;
477 ---- No. S-2-4 VALUES clause
481 EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1')) AS t1 (c1) WHERE t1.c1 = 1;
483 EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1')) AS t1 (c1) WHERE t1.c1 = 1;
484 /*+SeqScan(*VALUES*)*/
485 EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1')) AS t1 (c1) WHERE t1.c1 = 1;
488 EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1')) AS t1 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t2 (c1, c2) WHERE t1.c1 = t2.c1;
490 EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1')) AS t1 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t2 (c1, c2) WHERE t1.c1 = t2.c1;
491 /*+SeqScan(*VALUES*)*/
492 EXPLAIN (COSTS false) SELECT * FROM (VALUES(1,1,1,'1')) AS t1 (c1, c2, c3, c4), (VALUES(1,1,1,'1'), (2,2,2,'2')) AS t2 (c1, c2) WHERE t1.c1 = t2.c1;
495 ---- No. S-3-1 scan method hint
499 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1;
501 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1;
504 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
506 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
509 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
511 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
514 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1;
516 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1;
519 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c3 < 10;
521 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c3 < 10;
524 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
526 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
529 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
531 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
534 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid IN ('(1,1)', '(2,2)', '(3,3)');
536 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid IN ('(1,1)', '(2,2)', '(3,3)');
539 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1;
541 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1;
544 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
546 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
549 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
551 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
554 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1;
556 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 >= 1;
559 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c3 < 10;
560 /*+NoBitmapScan(t1)*/
561 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c3 < 10;
564 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
565 /*+NoBitmapScan(t1)*/
566 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
569 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
571 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
574 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
576 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
579 EXPLAIN (COSTS false) SELECT c1 FROM s1.t1 WHERE t1.c1 = 1;
580 /*+IndexOnlyScan(t1)*/
581 EXPLAIN (COSTS false) SELECT c1 FROM s1.t1 WHERE t1.c1 = 1;
584 EXPLAIN (COSTS false) SELECT c1 FROM s1.t1 WHERE t1.c1 >= 1;
585 /*+IndexOnlyScan(t1)*/
586 EXPLAIN (COSTS false) SELECT c1 FROM s1.t1 WHERE t1.c1 >= 1;
589 EXPLAIN (COSTS false) SELECT c1 FROM s1.t1 WHERE t1.c1 = 1;
590 /*+NoIndexOnlyScan(t1)*/
591 EXPLAIN (COSTS false) SELECT c1 FROM s1.t1 WHERE t1.c1 = 1;
594 EXPLAIN (COSTS false) SELECT c1 FROM s1.t1 WHERE t1.c1 >= 1;
595 /*+NoIndexOnlyScan(t1)*/
596 EXPLAIN (COSTS false) SELECT c1 FROM s1.t1 WHERE t1.c1 >= 1;
599 ---- No. S-3-3 index name specified
602 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE ti1.c2 = 1 AND ctid = '(1,1)';
603 SET enable_tidscan TO off;
604 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE ti1.c2 = 1 AND ctid = '(1,1)';
605 SET enable_indexscan TO off;
606 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE ti1.c2 = 1 AND ctid = '(1,1)';
607 RESET enable_tidscan;
608 RESET enable_indexscan;
610 EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 WHERE ti1.c2 >= 1;
613 /*+IndexScan(ti1 ti1_i3)*/
614 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE ti1.c2 = 1 AND ctid = '(1,1)';
617 /*+IndexScan(ti1 ti1_i3 ti1_i2)*/
618 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE ti1.c2 = 1 AND ctid = '(1,1)';
621 /*+IndexScan(ti1 ti1_i4 ti1_i3 ti1_i2 ti1_i1)*/
622 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE ti1.c2 = 1 AND ctid = '(1,1)';
625 /*+BitmapScan(ti1 ti1_i3)*/
626 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE ti1.c2 = 1 AND ctid = '(1,1)';
629 /*+BitmapScan(ti1 ti1_i3 ti1_i2)*/
630 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE ti1.c2 = 1 AND ctid = '(1,1)';
633 /*+BitmapScan(ti1 ti1_i4 ti1_i3 ti1_i2 ti1_i1)*/
634 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE ti1.c2 = 1 AND ctid = '(1,1)';
637 /*+IndexOnlyScan(ti1 ti1_i3)*/
638 EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 WHERE ti1.c2 >= 1;
641 /*+IndexOnlyScan(ti1 ti1_i3 ti1_i2)*/
642 EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 WHERE ti1.c2 >= 1;
645 /*+IndexOnlyScan(ti1 ti1_i4 ti1_i3 ti1_i2 ti1_i1)*/
646 EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 WHERE ti1.c2 >= 1;
649 ---- No. S-3-4 index type
653 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
656 /*+IndexScan(ti1 ti1_btree)*/
657 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
660 /*+IndexScan(ti1 ti1_hash)*/
661 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
664 /*+IndexScan(ti1 ti1_gist)*/
665 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
668 /*+IndexScan(ti1 ti1_gin)*/
669 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
672 /*+IndexScan(ti1 ti1_expr)*/
673 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
676 /*+IndexScan(ti1 ti1_pred)*/
677 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
680 /*+IndexScan(ti1 ti1_uniq)*/
681 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
684 /*+IndexScan(ti1 ti1_multi)*/
685 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
688 /*+IndexScan(ti1 ti1_ts)*/
689 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
692 /*+IndexScan(ti1 ti1_pkey)*/
693 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
696 /*+IndexScan(ti1 ti1_c2_key)*/
697 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
700 /*+BitmapScan(ti1 ti1_btree)*/
701 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
704 /*+BitmapScan(ti1 ti1_hash)*/
705 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
708 /*+BitmapScan(ti1 ti1_gist)*/
709 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
712 /*+BitmapScan(ti1 ti1_gin)*/
713 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
716 /*+BitmapScan(ti1 ti1_expr)*/
717 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
720 /*+BitmapScan(ti1 ti1_pred)*/
721 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
724 /*+BitmapScan(ti1 ti1_uniq)*/
725 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
728 /*+BitmapScan(ti1 ti1_multi)*/
729 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
732 /*+BitmapScan(ti1 ti1_ts)*/
733 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
736 /*+BitmapScan(ti1 ti1_pkey)*/
737 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
740 /*+BitmapScan(ti1 ti1_c2_key)*/
741 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 < 100 AND c2 = 1 AND lower(c4) = '1' AND to_tsvector('english', c4) @@ 'a & b' AND ctid = '(1,1)';
744 EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 >= 1;
745 /*+IndexOnlyScan(ti1 ti1_btree)*/
746 EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 >= 1;
749 EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 = 1;
750 /*+IndexOnlyScan(ti1 ti1_hash)*/
751 EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 = 1;
754 EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 < 1;
755 /*+IndexOnlyScan(ti1 ti1_gist)*/
756 EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 < 1;
759 EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 = 1;
760 /*+IndexOnlyScan(ti1 ti1_gin)*/
761 EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 = 1;
764 EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 < 100;
765 /*+IndexOnlyScan(ti1 ti1_expr)*/
766 EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 < 100;
769 EXPLAIN (COSTS false) SELECT c4 FROM s1.ti1 WHERE lower(c4) >= '1';
770 /*+IndexOnlyScan(ti1 ti1_pred)*/
771 EXPLAIN (COSTS false) SELECT c4 FROM s1.ti1 WHERE lower(c4) >= '1';
774 EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 >= 1;
775 /*+IndexOnlyScan(ti1 ti1_uniq)*/
776 EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 >= 1;
779 EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 >= 1;
780 /*+IndexOnlyScan(ti1 ti1_multi)*/
781 EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 >= 1;
784 EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE to_tsvector('english', c4) @@ 'a & b';
785 /*+IndexOnlyScan(ti1 ti1_ts)*/
786 EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE to_tsvector('english', c4) @@ 'a & b';
789 EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 >= 1;
790 /*+IndexOnlyScan(ti1 ti1_pkey)*/
791 EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 >= 1;
794 EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 WHERE c2 >= 1;
795 /*+IndexOnlyScan(ti1 ti1_c2_key)*/
796 EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 WHERE c2 >= 1;
799 ---- No. S-3-5 not used index
803 /*+IndexScan(ti1 ti1_pred)*/
804 EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100;
807 /*+BitmapScan(ti1 ti1_pred)*/
808 EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100;
811 /*+IndexOnlyScan(ti1 ti1_pred)*/
812 EXPLAIN (COSTS true) SELECT c1 FROM s1.ti1 WHERE c1 = 100;
815 /*+IndexScan(ti1 not_exist)*/
816 EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100;
819 /*+BitmapScan(ti1 not_exist)*/
820 EXPLAIN (COSTS true) SELECT * FROM s1.ti1 WHERE c1 = 100;
823 /*+IndexOnlyScan(ti1 not_exist)*/
824 EXPLAIN (COSTS true) SELECT c1 FROM s1.ti1 WHERE c1 = 100;
827 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
829 EXPLAIN (COSTS true) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
832 ---- No. S-3-6 query structure
835 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1 AND t1.ctid = '(1,1)';
839 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE c1 = 100;
842 /*+SeqScan(t1)BitmapScan(t2)*/
843 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1 AND t1.ctid = '(1,1)';
847 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1 AND t1.ctid = '(1,1)';
850 ---- No. S-3-7 number of tables in a query block
854 EXPLAIN (COSTS false)
856 SELECT max(b1t1.c1) FROM s1.t1 b1t1 WHERE b1t1.c1 = 1)
857 SELECT max(b3t1.c1), (
858 SELECT max(b2t1.c1) FROM s1.t1 b2t1 WHERE b2t1.c1 = 1
859 ) FROM s1.t1 b3t1 WHERE b3t1.c1 = (
860 SELECT max(b4t1.c1) FROM s1.t1 b4t1 WHERE b4t1.c1 = 1);
861 /*+SeqScan(b1t1)IndexScan(b2t1 t1_pkey)BitmapScan(b3t1 t1_pkey)TidScan(b4t1)
863 EXPLAIN (COSTS false)
865 SELECT max(b1t1.c1) FROM s1.t1 b1t1 WHERE b1t1.c1 = 1)
866 SELECT max(b3t1.c1), (
867 SELECT max(b2t1.c1) FROM s1.t1 b2t1 WHERE b2t1.c1 = 1
868 ) FROM s1.t1 b3t1 WHERE b3t1.c1 = (
869 SELECT max(b4t1.c1) FROM s1.t1 b4t1 WHERE b4t1.c1 = 1);
872 EXPLAIN (COSTS false)
874 SELECT max(b1t1.c1) FROM s1.t1 b1t1 JOIN s1.t2 b1t2 ON(b1t1.c1 = b1t2.c1) WHERE b1t1.c1 = 1)
875 SELECT max(b3t1.c1), (
876 SELECT max(b2t1.c1) FROM s1.t1 b2t1 JOIN s1.t2 b2t2 ON(b2t1.c1 = b2t2.c1) WHERE b2t1.c1 = 1
877 ) FROM s1.t1 b3t1 JOIN s1.t2 b3t2 ON(b3t1.c1 = b3t2.c1) JOIN cte1 ON(b3t1.c1 = cte1.c1) WHERE b3t1.c1 = (
878 SELECT max(b4t1.c1) FROM s1.t1 b4t1 JOIN s1.t2 b4t2 ON(b4t1.c1 = b4t2.c1) WHERE b4t1.c1 = 1);
879 /*+SeqScan(b1t1)IndexScan(b2t1 t1_pkey)BitmapScan(b3t1 t1_pkey)TidScan(b4t1)
880 TidScan(b1t2)SeqScan(b2t2)IndexScan(b3t2 t2_pkey)BitmapScan(b4t2 t2_pkey)
882 EXPLAIN (COSTS false)
884 SELECT max(b1t1.c1) FROM s1.t1 b1t1 JOIN s1.t2 b1t2 ON(b1t1.c1 = b1t2.c1) WHERE b1t1.c1 = 1)
885 SELECT max(b3t1.c1), (
886 SELECT max(b2t1.c1) FROM s1.t1 b2t1 JOIN s1.t2 b2t2 ON(b2t1.c1 = b2t2.c1) WHERE b2t1.c1 = 1
887 ) FROM s1.t1 b3t1 JOIN s1.t2 b3t2 ON(b3t1.c1 = b3t2.c1) JOIN cte1 ON(b3t1.c1 = cte1.c1) WHERE b3t1.c1 = (
888 SELECT max(b4t1.c1) FROM s1.t1 b4t1 JOIN s1.t2 b4t2 ON(b4t1.c1 = b4t2.c1) WHERE b4t1.c1 = 1);
891 EXPLAIN (COSTS false)
893 SELECT max(b1t1.c1) FROM s1.t1 b1t1 JOIN s1.t2 b1t2 ON(b1t1.c1 = b1t2.c1) WHERE b1t1.c1 = 1)
894 SELECT max(b3t1.c1), (
895 SELECT max(b2t1.c1) FROM s1.t1 b2t1 WHERE b2t1.c1 = 1
896 ) FROM s1.t1 b3t1 JOIN s1.t2 b3t2 ON(b3t1.c1 = b3t2.c1) JOIN cte1 ON(b3t1.c1 = cte1.c1) WHERE b3t1.c1 = (
897 SELECT max(b4t1.c1) FROM s1.t1 b4t1 WHERE b4t1.c1 = 1);
898 /*+SeqScan(b1t1)IndexScan(b2t1 t1_pkey)BitmapScan(b3t1 t1_pkey)TidScan(b4t1)
899 TidScan(b1t2)IndexScan(b3t2 t2_pkey)
901 EXPLAIN (COSTS false)
903 SELECT max(b1t1.c1) FROM s1.t1 b1t1 JOIN s1.t2 b1t2 ON(b1t1.c1 = b1t2.c1) WHERE b1t1.c1 = 1)
904 SELECT max(b3t1.c1), (
905 SELECT max(b2t1.c1) FROM s1.t1 b2t1 WHERE b2t1.c1 = 1
906 ) FROM s1.t1 b3t1 JOIN s1.t2 b3t2 ON(b3t1.c1 = b3t2.c1) JOIN cte1 ON(b3t1.c1 = cte1.c1) WHERE b3t1.c1 = (
907 SELECT max(b4t1.c1) FROM s1.t1 b4t1 WHERE b4t1.c1 = 1);
910 ---- No. S-3-8 inheritance table select type
914 EXPLAIN (COSTS false) SELECT * FROM ONLY s1.p1 WHERE c1 = 1;
916 EXPLAIN (COSTS false) SELECT * FROM ONLY s1.p1 WHERE c1 = 1;
919 EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1;
921 EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1;
924 ---- No. S-3-9 inheritance table number
928 EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1;
930 EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1;
933 EXPLAIN (COSTS false) SELECT * FROM s1.p2 WHERE c1 = 1;
935 EXPLAIN (COSTS false) SELECT * FROM s1.p2 WHERE c1 = 1;
938 ---- No. S-3-10 inheritance table specified table
941 EXPLAIN (COSTS false) SELECT * FROM s1.p2 WHERE c1 = 1;
945 EXPLAIN (COSTS false) SELECT * FROM s1.p2 WHERE c1 = 1;
949 EXPLAIN (COSTS false) SELECT * FROM s1.p2 WHERE c1 = 1;
952 EXPLAIN SELECT c4 FROM s1.p1 WHERE c2 * 2 < 100 AND c1 < 10;
953 /*+IndexScan(p1 p1_parent)*/
954 EXPLAIN SELECT c4 FROM s1.p1 WHERE c2 * 2 < 100 AND c1 < 10;
957 /*+IndexScan(p1 p1_i2)*/
958 EXPLAIN SELECT c2 FROM s1.p1 WHERE c2 = 1;
961 /*+IndexScan(p2 p2c1_pkey)*/
962 EXPLAIN (COSTS true) SELECT * FROM s1.p2 WHERE c1 = 1;
965 ---- No. S-3-12 specified same table
969 /*+IndexScan(ti1) BitmapScan(ti1)*/
970 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
973 /*+IndexScan(ti1 ti1_pkey) BitmapScan(ti1 ti1_btree)*/
974 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
977 /*+BitmapScan(ti1) IndexScan(ti1) BitmapScan(ti1)*/
978 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
981 /*+BitmapScan(ti1 ti1_hash) IndexScan(ti1 ti1_pkey) BitmapScan(ti1 ti1_btree)*/
982 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
985 ---- No. S-3-13 message output of hint
990 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
993 /*+SeqScan(ti1 ti1_pkey)*/
994 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
997 /*+SeqScan(ti1 ti1_pkey ti1_btree)*/
998 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
1002 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
1005 /*+IndexScan(ti1 ti1_pkey)*/
1006 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
1009 /*+IndexScan(ti1 ti1_pkey ti1_btree)*/
1010 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
1013 /*+BitmapScan(ti1)*/
1014 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
1017 /*+BitmapScan(ti1 ti1_pkey)*/
1018 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
1021 /*+BitmapScan(ti1 ti1_pkey ti1_btree)*/
1022 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
1026 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
1029 /*+TidScan(ti1 ti1_pkey)*/
1030 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
1033 /*+TidScan(ti1 ti1_pkey ti1_btree)*/
1034 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
1038 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
1041 /*+NoSeqScan(ti1 ti1_pkey)*/
1042 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
1045 /*+NoSeqScan(ti1 ti1_pkey ti1_btree)*/
1046 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
1049 /*+NoIndexScan(ti1)*/
1050 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
1053 /*+NoIndexScan(ti1 ti1_pkey)*/
1054 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
1057 /*+NoIndexScan(ti1 ti1_pkey ti1_btree)*/
1058 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
1061 /*+NoBitmapScan(ti1)*/
1062 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
1065 /*+NoBitmapScan(ti1 ti1_pkey)*/
1066 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
1069 /*+NoBitmapScan(ti1 ti1_pkey ti1_btree)*/
1070 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
1074 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
1077 /*+NoTidScan(ti1 ti1_pkey)*/
1078 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
1081 /*+NoTidScan(ti1 ti1_pkey ti1_btree)*/
1082 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c1 = 1 AND ctid = '(1,1)';
1085 /*+IndexOnlyScan(ti1)*/
1086 EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 >= 1;
1089 /*+IndexOnlyScan(ti1 ti1_pkey)*/
1090 EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 >= 1;
1093 /*+IndexOnlyScan(ti1 ti1_pkey ti1_btree)*/
1094 EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 >= 1;
1097 /*+NoIndexOnlyScan(ti1)*/
1098 EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 = 1;
1101 /*+NoIndexOnlyScan(ti1 ti1_pkey)*/
1102 EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 = 1;
1105 /*+NoIndexOnlyScan(ti1 ti1_pkey ti1_btree)*/
1106 EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 = 1;
1110 ---- No. S-3-14 regular expression
1112 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
1115 /*+IndexScanRegexp(ti1 ti1_.*_key)*/
1116 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
1119 /*+IndexScanRegexp(ti1 ti1_i.)*/
1120 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
1123 /*+IndexScanRegexp(ti1 no.*_exist)*/
1124 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
1127 /*+IndexScanRegexp(p1 .*pkey)*/
1128 EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1;
1131 /*+IndexScanRegexp(p1 p1.*i)*/
1132 EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1;
1135 /*+IndexScanRegexp(p1 no.*_exist)*/
1136 EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1;
1139 ---- No. S-3-15 message output of index candidate
1143 /*+IndexScan(ti1 ti1_i1)*/
1144 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
1146 /*+IndexScan(ti1 not_exist)*/
1147 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
1149 /*+IndexScan(ti1 ti1_i1 ti1_i2)*/
1150 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
1152 /*+IndexScan(ti1 ti1_i1 not_exist)*/
1153 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
1155 /*+IndexScan(ti1 not_exist1 not_exist2)*/
1156 EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;