2 SET search_path TO public;
3 SET pg_hint_plan.enable TO on;
4 SET pg_hint_plan.debug_print TO on;
5 SET client_min_messages TO LOG;
6 EXPLAIN (COSTS false) SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id;
8 --------------------------------------------
11 Merge Cond: (t1.id = t2.id)
12 -> Index Scan using t1_pkey on t1
13 -> Index Scan using t2_pkey on t2
16 EXPLAIN (COSTS false) SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id AND t1.id > 10;
18 --------------------------------------------
21 Merge Cond: (t1.id = t2.id)
22 -> Index Scan using t1_pkey on t1
24 -> Index Scan using t2_pkey on t2
27 -- 9.1:PREPAREで指定したヒント句で実行計画が固定される
28 -- 9.2:PREPAREでヒント句を指定しても、実行計画は制御できない
29 /*+ NestLoop(t1 t2) */
30 PREPARE p1 AS SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id;
38 EXPLAIN (COSTS false) EXECUTE p1;
40 --------------------------------------------
44 -> Index Scan using t1_pkey on t1
45 Index Cond: (id = t2.id)
48 -- 9.1:PREPAREで指定したヒント句で実行計画が固定される
49 -- 9.2:パラメータがない場合は、1回目のEXPLAINで実行計画が決定する
50 /*+ NestLoop(t1 t2) */
51 PREPARE p2 AS SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id;
59 /*+ HashJoin(t1 t2) */
60 EXPLAIN (COSTS false) EXECUTE p2;
62 --------------------------------------------
66 -> Index Scan using t1_pkey on t1
67 Index Cond: (id = t2.id)
70 EXPLAIN (COSTS false) EXECUTE p2;
72 --------------------------------------------
76 -> Index Scan using t1_pkey on t1
77 Index Cond: (id = t2.id)
80 EXPLAIN (COSTS false) EXECUTE p2;
82 --------------------------------------------
86 -> Index Scan using t1_pkey on t1
87 Index Cond: (id = t2.id)
90 -- 9.1:PREPAREで指定したヒント句で実行計画が固定される
91 -- 9.2:5回目のEXPLAINまでヒント句を指定しても、6回目以降は本来の実行計画に戻る
92 /*+ NestLoop(t1 t2) */
93 PREPARE p3 AS SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id AND t1.id > $1;
101 /*+ HashJoin(t1 t2) */
102 EXPLAIN (COSTS false) EXECUTE p3 (10);
104 --------------------------------------------------------
108 -> Index Scan using t1_pkey on t1
109 Index Cond: ((id > $1) AND (id = t2.id))
112 /*+ HashJoin(t1 t2) */
113 EXPLAIN (COSTS false) EXECUTE p3 (10);
115 --------------------------------------------------------
119 -> Index Scan using t1_pkey on t1
120 Index Cond: ((id > $1) AND (id = t2.id))
123 /*+ HashJoin(t1 t2) */
124 EXPLAIN (COSTS false) EXECUTE p3 (10);
126 --------------------------------------------------------
130 -> Index Scan using t1_pkey on t1
131 Index Cond: ((id > $1) AND (id = t2.id))
134 /*+ HashJoin(t1 t2) */
135 EXPLAIN (COSTS false) EXECUTE p3 (10);
137 --------------------------------------------------------
141 -> Index Scan using t1_pkey on t1
142 Index Cond: ((id > $1) AND (id = t2.id))
145 /*+ HashJoin(t1 t2) */
146 EXPLAIN (COSTS false) EXECUTE p3 (10);
148 --------------------------------------------------------
152 -> Index Scan using t1_pkey on t1
153 Index Cond: ((id > $1) AND (id = t2.id))
156 EXPLAIN (COSTS false) EXECUTE p3 (10);
158 --------------------------------------------------------
162 -> Index Scan using t1_pkey on t1
163 Index Cond: ((id > $1) AND (id = t2.id))
166 EXPLAIN (COSTS false) EXECUTE p3 (10);
168 --------------------------------------------------------
172 -> Index Scan using t1_pkey on t1
173 Index Cond: ((id > $1) AND (id = t2.id))
176 EXPLAIN (COSTS false) EXECUTE p3 (10);
178 --------------------------------------------------------
182 -> Index Scan using t1_pkey on t1
183 Index Cond: ((id > $1) AND (id = t2.id))
186 -- 9.1:PREPAREで指定したヒント句で実行計画が固定される
187 -- 9.2:6回目のEXPLAINまでヒント句を指定すると、7回目以降も実行計画が固定される
188 /*+ NestLoop(t1 t2) */
189 PREPARE p4 AS SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id AND t1.id > $1;
197 /*+ HashJoin(t1 t2) */
198 EXPLAIN (COSTS false) EXECUTE p4 (10);
200 --------------------------------------------------------
204 -> Index Scan using t1_pkey on t1
205 Index Cond: ((id > $1) AND (id = t2.id))
208 /*+ HashJoin(t1 t2) */
209 EXPLAIN (COSTS false) EXECUTE p4 (10);
211 --------------------------------------------------------
215 -> Index Scan using t1_pkey on t1
216 Index Cond: ((id > $1) AND (id = t2.id))
219 /*+ HashJoin(t1 t2) */
220 EXPLAIN (COSTS false) EXECUTE p4 (10);
222 --------------------------------------------------------
226 -> Index Scan using t1_pkey on t1
227 Index Cond: ((id > $1) AND (id = t2.id))
230 /*+ HashJoin(t1 t2) */
231 EXPLAIN (COSTS false) EXECUTE p4 (10);
233 --------------------------------------------------------
237 -> Index Scan using t1_pkey on t1
238 Index Cond: ((id > $1) AND (id = t2.id))
241 /*+ HashJoin(t1 t2) */
242 EXPLAIN (COSTS false) EXECUTE p4 (10);
244 --------------------------------------------------------
248 -> Index Scan using t1_pkey on t1
249 Index Cond: ((id > $1) AND (id = t2.id))
252 /*+ HashJoin(t1 t2) */
253 EXPLAIN (COSTS false) EXECUTE p4 (10);
255 --------------------------------------------------------
259 -> Index Scan using t1_pkey on t1
260 Index Cond: ((id > $1) AND (id = t2.id))
263 EXPLAIN (COSTS false) EXECUTE p4 (10);
265 --------------------------------------------------------
269 -> Index Scan using t1_pkey on t1
270 Index Cond: ((id > $1) AND (id = t2.id))
273 EXPLAIN (COSTS false) EXECUTE p4 (10);
275 --------------------------------------------------------
279 -> Index Scan using t1_pkey on t1
280 Index Cond: ((id > $1) AND (id = t2.id))
283 -- 9.1:PREPAREで指定したヒント句で実行計画が固定される
284 -- 9.2:6回目のEXPLAINでヒント句を指定すると、7回目以降も実行計画を制御できる
285 /*+ NestLoop(t1 t2) */
286 PREPARE p5 AS SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id AND t1.id > $1;
294 EXPLAIN (COSTS false) EXECUTE p5 (10);
296 --------------------------------------------------------
300 -> Index Scan using t1_pkey on t1
301 Index Cond: ((id > $1) AND (id = t2.id))
304 EXPLAIN (COSTS false) EXECUTE p5 (10);
306 --------------------------------------------------------
310 -> Index Scan using t1_pkey on t1
311 Index Cond: ((id > $1) AND (id = t2.id))
314 EXPLAIN (COSTS false) EXECUTE p5 (10);
316 --------------------------------------------------------
320 -> Index Scan using t1_pkey on t1
321 Index Cond: ((id > $1) AND (id = t2.id))
324 EXPLAIN (COSTS false) EXECUTE p5 (10);
326 --------------------------------------------------------
330 -> Index Scan using t1_pkey on t1
331 Index Cond: ((id > $1) AND (id = t2.id))
334 EXPLAIN (COSTS false) EXECUTE p5 (10);
336 --------------------------------------------------------
340 -> Index Scan using t1_pkey on t1
341 Index Cond: ((id > $1) AND (id = t2.id))
344 /*+ HashJoin(t1 t2) */
345 EXPLAIN (COSTS false) EXECUTE p5 (10);
347 --------------------------------------------------------
351 -> Index Scan using t1_pkey on t1
352 Index Cond: ((id > $1) AND (id = t2.id))
355 /*+ HashJoin(t1 t2) */
356 EXPLAIN (COSTS false) EXECUTE p5 (10);
358 --------------------------------------------------------
362 -> Index Scan using t1_pkey on t1
363 Index Cond: ((id > $1) AND (id = t2.id))
366 /*+ HashJoin(t1 t2) */
367 EXPLAIN (COSTS false) EXECUTE p5 (10);
369 --------------------------------------------------------
373 -> Index Scan using t1_pkey on t1
374 Index Cond: ((id > $1) AND (id = t2.id))
377 -- 9.1:PREPAREで指定したヒント句で実行計画が固定される
378 -- 9.2:7回目以降のEXPLAINでヒント句を指定しても、以降も実行計画は制御できない
379 /*+ NestLoop(t1 t2) */
380 PREPARE p6 AS SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id AND t1.id > $1;
388 EXPLAIN (COSTS false) EXECUTE p6 (10);
390 --------------------------------------------------------
394 -> Index Scan using t1_pkey on t1
395 Index Cond: ((id > $1) AND (id = t2.id))
398 EXPLAIN (COSTS false) EXECUTE p6 (10);
400 --------------------------------------------------------
404 -> Index Scan using t1_pkey on t1
405 Index Cond: ((id > $1) AND (id = t2.id))
408 EXPLAIN (COSTS false) EXECUTE p6 (10);
410 --------------------------------------------------------
414 -> Index Scan using t1_pkey on t1
415 Index Cond: ((id > $1) AND (id = t2.id))
418 EXPLAIN (COSTS false) EXECUTE p6 (10);
420 --------------------------------------------------------
424 -> Index Scan using t1_pkey on t1
425 Index Cond: ((id > $1) AND (id = t2.id))
428 EXPLAIN (COSTS false) EXECUTE p6 (10);
430 --------------------------------------------------------
434 -> Index Scan using t1_pkey on t1
435 Index Cond: ((id > $1) AND (id = t2.id))
438 EXPLAIN (COSTS false) EXECUTE p6 (10);
440 --------------------------------------------------------
444 -> Index Scan using t1_pkey on t1
445 Index Cond: ((id > $1) AND (id = t2.id))
448 /*+ HashJoin(t1 t2) */
449 EXPLAIN (COSTS false) EXECUTE p6 (10);
451 --------------------------------------------------------
455 -> Index Scan using t1_pkey on t1
456 Index Cond: ((id > $1) AND (id = t2.id))
459 /*+ HashJoin(t1 t2) */
460 EXPLAIN (COSTS false) EXECUTE p6 (10);
462 --------------------------------------------------------
466 -> Index Scan using t1_pkey on t1
467 Index Cond: ((id > $1) AND (id = t2.id))
470 -- 9.1:実行計画が固定されたあと、ANALYZEをすると1回目のEXECUTEで実行計画が固定される
471 -- 9.2:実行計画が固定されたあと、ANALYZEをすると1回目のEXECUTEで実行計画が固定される
472 /*+ NestLoop(t1 t2) */
473 PREPARE p7 AS SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id AND t1.id > $1;
481 /*+ HashJoin(t1 t2) */
482 EXPLAIN (COSTS false) EXECUTE p7 (10);
484 --------------------------------------------------------
488 -> Index Scan using t1_pkey on t1
489 Index Cond: ((id > $1) AND (id = t2.id))
492 /*+ HashJoin(t1 t2) */
493 EXPLAIN (COSTS false) EXECUTE p7 (10);
495 --------------------------------------------------------
499 -> Index Scan using t1_pkey on t1
500 Index Cond: ((id > $1) AND (id = t2.id))
503 /*+ HashJoin(t1 t2) */
504 EXPLAIN (COSTS false) EXECUTE p7 (10);
506 --------------------------------------------------------
510 -> Index Scan using t1_pkey on t1
511 Index Cond: ((id > $1) AND (id = t2.id))
514 /*+ HashJoin(t1 t2) */
515 EXPLAIN (COSTS false) EXECUTE p7 (10);
517 --------------------------------------------------------
521 -> Index Scan using t1_pkey on t1
522 Index Cond: ((id > $1) AND (id = t2.id))
525 /*+ HashJoin(t1 t2) */
526 EXPLAIN (COSTS false) EXECUTE p7 (10);
528 --------------------------------------------------------
532 -> Index Scan using t1_pkey on t1
533 Index Cond: ((id > $1) AND (id = t2.id))
536 /*+ HashJoin(t1 t2) */
537 EXPLAIN (COSTS false) EXECUTE p7 (10);
539 --------------------------------------------------------
543 -> Index Scan using t1_pkey on t1
544 Index Cond: ((id > $1) AND (id = t2.id))
547 EXPLAIN (COSTS false) EXECUTE p7 (10);
549 --------------------------------------------------------
553 -> Index Scan using t1_pkey on t1
554 Index Cond: ((id > $1) AND (id = t2.id))
557 EXPLAIN (COSTS false) EXECUTE p7 (10);
559 --------------------------------------------------------
563 -> Index Scan using t1_pkey on t1
564 Index Cond: ((id > $1) AND (id = t2.id))
569 EXPLAIN (COSTS false) SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id AND t1.id > 10;
571 --------------------------------------------
574 Merge Cond: (t1.id = t2.id)
575 -> Index Scan using t1_pkey on t1
576 Index Cond: (id > 10)
577 -> Index Scan using t2_pkey on t2
580 EXPLAIN (COSTS false) EXECUTE p7 (10);
589 --------------------------------------------
592 -> Index Scan using t1_pkey on t1
593 Index Cond: (id > $1)
594 -> Index Scan using t2_pkey on t2
595 Index Cond: (id = t1.id)
598 /*+ HashJoin(t1 t2) */
599 EXPLAIN (COSTS false) EXECUTE p7 (10);
601 --------------------------------------------
604 -> Index Scan using t1_pkey on t1
605 Index Cond: (id > $1)
606 -> Index Scan using t2_pkey on t2
607 Index Cond: (id = t1.id)
610 INSERT INTO t1 SELECT i, i % 100 FROM (SELECT generate_series(1, 10000) i) t;
612 EXPLAIN (COSTS false) SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id AND t1.id > 10;
614 --------------------------------------------
617 Merge Cond: (t1.id = t2.id)
618 -> Index Scan using t1_pkey on t1
619 Index Cond: (id > 10)
620 -> Index Scan using t2_pkey on t2
623 /*+ HashJoin(t1 t2) */
624 EXPLAIN (COSTS false) EXECUTE p7 (10);
633 --------------------------------------------------------
637 -> Index Scan using t1_pkey on t1
638 Index Cond: ((id > $1) AND (id = t2.id))
641 /*+ NestLoop(t1 t2) */
642 EXPLAIN (COSTS false) EXECUTE p7 (10);
644 --------------------------------------------------------
648 -> Index Scan using t1_pkey on t1
649 Index Cond: ((id > $1) AND (id = t2.id))
653 /*+ NestLoop(t1 t2) */
654 EXPLAIN (COSTS false) EXECUTE p8 (10);
655 ERROR: prepared statement "p8" does not exist