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 Only Scan using t1_pkey on t1
13 -> Index Only 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 Only Scan using t1_pkey on t1
24 -> Index Only 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;
31 EXPLAIN (COSTS false) EXECUTE p1;
33 -------------------------------------------------
36 Merge Cond: (t1.id = t2.id)
37 -> Index Only Scan using t1_pkey on t1
38 -> Index Only Scan using t2_pkey on t2
41 -- 9.1:PREPAREで指定したヒント句で実行計画が固定される
42 -- 9.2:パラメータがない場合は、1回目のEXPLAINで実行計画が決定する
43 /*+ NestLoop(t1 t2) */
44 PREPARE p2 AS SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id;
45 /*+ HashJoin(t1 t2) */
46 EXPLAIN (COSTS false) EXECUTE p2;
55 ------------------------------------
58 Hash Cond: (t1.id = t2.id)
64 EXPLAIN (COSTS false) EXECUTE p2;
66 ------------------------------------
69 Hash Cond: (t1.id = t2.id)
75 EXPLAIN (COSTS false) EXECUTE p2;
77 ------------------------------------
80 Hash Cond: (t1.id = t2.id)
86 -- 9.1:PREPAREで指定したヒント句で実行計画が固定される
87 -- 9.2:5回目のEXPLAINまでヒント句を指定しても、6回目以降は本来の実行計画に戻る
88 /*+ NestLoop(t1 t2) */
89 PREPARE p3 AS SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id AND t1.id > $1;
90 /*+ HashJoin(t1 t2) */
91 EXPLAIN (COSTS false) EXECUTE p3 (10);
100 ------------------------------------
103 Hash Cond: (t1.id = t2.id)
110 /*+ HashJoin(t1 t2) */
111 EXPLAIN (COSTS false) EXECUTE p3 (10);
120 ------------------------------------
123 Hash Cond: (t1.id = t2.id)
130 /*+ HashJoin(t1 t2) */
131 EXPLAIN (COSTS false) EXECUTE p3 (10);
140 ------------------------------------
143 Hash Cond: (t1.id = t2.id)
150 /*+ HashJoin(t1 t2) */
151 EXPLAIN (COSTS false) EXECUTE p3 (10);
160 ------------------------------------
163 Hash Cond: (t1.id = t2.id)
170 /*+ HashJoin(t1 t2) */
171 EXPLAIN (COSTS false) EXECUTE p3 (10);
180 ------------------------------------
183 Hash Cond: (t1.id = t2.id)
190 EXPLAIN (COSTS false) EXECUTE p3 (10);
192 -------------------------------------------------
195 Merge Cond: (t1.id = t2.id)
196 -> Index Only Scan using t1_pkey on t1
197 Index Cond: (id > $1)
198 -> Index Only Scan using t2_pkey on t2
201 EXPLAIN (COSTS false) EXECUTE p3 (10);
203 -------------------------------------------------
206 Merge Cond: (t1.id = t2.id)
207 -> Index Only Scan using t1_pkey on t1
208 Index Cond: (id > $1)
209 -> Index Only Scan using t2_pkey on t2
212 EXPLAIN (COSTS false) EXECUTE p3 (10);
214 -------------------------------------------------
217 Merge Cond: (t1.id = t2.id)
218 -> Index Only Scan using t1_pkey on t1
219 Index Cond: (id > $1)
220 -> Index Only Scan using t2_pkey on t2
223 -- 9.1:PREPAREで指定したヒント句で実行計画が固定される
224 -- 9.2:6回目のEXPLAINまでヒント句を指定すると、7回目以降も実行計画が固定される
225 /*+ NestLoop(t1 t2) */
226 PREPARE p4 AS SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id AND t1.id > $1;
227 /*+ HashJoin(t1 t2) */
228 EXPLAIN (COSTS false) EXECUTE p4 (10);
237 ------------------------------------
240 Hash Cond: (t1.id = t2.id)
247 /*+ HashJoin(t1 t2) */
248 EXPLAIN (COSTS false) EXECUTE p4 (10);
257 ------------------------------------
260 Hash Cond: (t1.id = t2.id)
267 /*+ HashJoin(t1 t2) */
268 EXPLAIN (COSTS false) EXECUTE p4 (10);
277 ------------------------------------
280 Hash Cond: (t1.id = t2.id)
287 /*+ HashJoin(t1 t2) */
288 EXPLAIN (COSTS false) EXECUTE p4 (10);
297 ------------------------------------
300 Hash Cond: (t1.id = t2.id)
307 /*+ HashJoin(t1 t2) */
308 EXPLAIN (COSTS false) EXECUTE p4 (10);
317 ------------------------------------
320 Hash Cond: (t1.id = t2.id)
327 /*+ HashJoin(t1 t2) */
328 EXPLAIN (COSTS false) EXECUTE p4 (10);
337 -------------------------------------------------
340 Hash Cond: (t1.id = t2.id)
341 -> Index Only Scan using t1_pkey on t1
342 Index Cond: (id > $1)
347 EXPLAIN (COSTS false) EXECUTE p4 (10);
349 -------------------------------------------------
352 Hash Cond: (t1.id = t2.id)
353 -> Index Only Scan using t1_pkey on t1
354 Index Cond: (id > $1)
359 EXPLAIN (COSTS false) EXECUTE p4 (10);
361 -------------------------------------------------
364 Hash Cond: (t1.id = t2.id)
365 -> Index Only Scan using t1_pkey on t1
366 Index Cond: (id > $1)
371 -- 9.1:PREPAREで指定したヒント句で実行計画が固定される
372 -- 9.2:6回目のEXPLAINでヒント句を指定すると、7回目以降も実行計画を制御できる
373 /*+ NestLoop(t1 t2) */
374 PREPARE p5 AS SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id AND t1.id > $1;
375 EXPLAIN (COSTS false) EXECUTE p5 (10);
377 -------------------------------------------------
380 Merge Cond: (t1.id = t2.id)
381 -> Index Only Scan using t1_pkey on t1
382 Index Cond: (id > 10)
383 -> Index Only Scan using t2_pkey on t2
386 EXPLAIN (COSTS false) EXECUTE p5 (10);
388 -------------------------------------------------
391 Merge Cond: (t1.id = t2.id)
392 -> Index Only Scan using t1_pkey on t1
393 Index Cond: (id > 10)
394 -> Index Only Scan using t2_pkey on t2
397 EXPLAIN (COSTS false) EXECUTE p5 (10);
399 -------------------------------------------------
402 Merge Cond: (t1.id = t2.id)
403 -> Index Only Scan using t1_pkey on t1
404 Index Cond: (id > 10)
405 -> Index Only Scan using t2_pkey on t2
408 EXPLAIN (COSTS false) EXECUTE p5 (10);
410 -------------------------------------------------
413 Merge Cond: (t1.id = t2.id)
414 -> Index Only Scan using t1_pkey on t1
415 Index Cond: (id > 10)
416 -> Index Only Scan using t2_pkey on t2
419 EXPLAIN (COSTS false) EXECUTE p5 (10);
421 -------------------------------------------------
424 Merge Cond: (t1.id = t2.id)
425 -> Index Only Scan using t1_pkey on t1
426 Index Cond: (id > 10)
427 -> Index Only Scan using t2_pkey on t2
430 /*+ HashJoin(t1 t2) */
431 EXPLAIN (COSTS false) EXECUTE p5 (10);
447 ------------------------------------
450 Hash Cond: (t1.id = t2.id)
457 /*+ HashJoin(t1 t2) */
458 EXPLAIN (COSTS false) EXECUTE p5 (10);
467 ------------------------------------
470 Hash Cond: (t1.id = t2.id)
477 /*+ HashJoin(t1 t2) */
478 EXPLAIN (COSTS false) EXECUTE p5 (10);
487 ------------------------------------
490 Hash Cond: (t1.id = t2.id)
497 -- 9.1:PREPAREで指定したヒント句で実行計画が固定される
498 -- 9.2:7回目以降のEXPLAINでヒント句を指定しても、以降も実行計画は制御できない
499 /*+ NestLoop(t1 t2) */
500 PREPARE p6 AS SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id AND t1.id > $1;
501 EXPLAIN (COSTS false) EXECUTE p6 (10);
503 -------------------------------------------------
506 Merge Cond: (t1.id = t2.id)
507 -> Index Only Scan using t1_pkey on t1
508 Index Cond: (id > 10)
509 -> Index Only Scan using t2_pkey on t2
512 EXPLAIN (COSTS false) EXECUTE p6 (10);
514 -------------------------------------------------
517 Merge Cond: (t1.id = t2.id)
518 -> Index Only Scan using t1_pkey on t1
519 Index Cond: (id > 10)
520 -> Index Only Scan using t2_pkey on t2
523 EXPLAIN (COSTS false) EXECUTE p6 (10);
525 -------------------------------------------------
528 Merge Cond: (t1.id = t2.id)
529 -> Index Only Scan using t1_pkey on t1
530 Index Cond: (id > 10)
531 -> Index Only Scan using t2_pkey on t2
534 EXPLAIN (COSTS false) EXECUTE p6 (10);
536 -------------------------------------------------
539 Merge Cond: (t1.id = t2.id)
540 -> Index Only Scan using t1_pkey on t1
541 Index Cond: (id > 10)
542 -> Index Only Scan using t2_pkey on t2
545 EXPLAIN (COSTS false) EXECUTE p6 (10);
547 -------------------------------------------------
550 Merge Cond: (t1.id = t2.id)
551 -> Index Only Scan using t1_pkey on t1
552 Index Cond: (id > 10)
553 -> Index Only Scan using t2_pkey on t2
556 EXPLAIN (COSTS false) EXECUTE p6 (10);
558 -------------------------------------------------
561 Merge Cond: (t1.id = t2.id)
562 -> Index Only Scan using t1_pkey on t1
563 Index Cond: (id > $1)
564 -> Index Only Scan using t2_pkey on t2
567 /*+ HashJoin(t1 t2) */
568 EXPLAIN (COSTS false) EXECUTE p6 (10);
570 -------------------------------------------------
573 Merge Cond: (t1.id = t2.id)
574 -> Index Only Scan using t1_pkey on t1
575 Index Cond: (id > $1)
576 -> Index Only Scan using t2_pkey on t2
579 /*+ HashJoin(t1 t2) */
580 EXPLAIN (COSTS false) EXECUTE p6 (10);
582 -------------------------------------------------
585 Merge Cond: (t1.id = t2.id)
586 -> Index Only Scan using t1_pkey on t1
587 Index Cond: (id > $1)
588 -> Index Only Scan using t2_pkey on t2
591 -- 9.1:実行計画が固定されたあと、ANALYZEをすると1回目のEXECUTEで実行計画が固定される
592 -- 9.2:実行計画が固定されたあと、ANALYZEをすると1回目のEXECUTEで実行計画が固定される
593 /*+ NestLoop(t1 t2) */
594 PREPARE p7 AS SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id AND t1.id > $1;
595 /*+ HashJoin(t1 t2) */
596 EXPLAIN (COSTS false) EXECUTE p7 (10);
605 ------------------------------------
608 Hash Cond: (t1.id = t2.id)
615 /*+ HashJoin(t1 t2) */
616 EXPLAIN (COSTS false) EXECUTE p7 (10);
625 ------------------------------------
628 Hash Cond: (t1.id = t2.id)
635 /*+ HashJoin(t1 t2) */
636 EXPLAIN (COSTS false) EXECUTE p7 (10);
645 ------------------------------------
648 Hash Cond: (t1.id = t2.id)
655 /*+ HashJoin(t1 t2) */
656 EXPLAIN (COSTS false) EXECUTE p7 (10);
665 ------------------------------------
668 Hash Cond: (t1.id = t2.id)
675 /*+ HashJoin(t1 t2) */
676 EXPLAIN (COSTS false) EXECUTE p7 (10);
685 ------------------------------------
688 Hash Cond: (t1.id = t2.id)
695 /*+ HashJoin(t1 t2) */
696 EXPLAIN (COSTS false) EXECUTE p7 (10);
705 -------------------------------------------------
708 Hash Cond: (t1.id = t2.id)
709 -> Index Only Scan using t1_pkey on t1
710 Index Cond: (id > $1)
715 EXPLAIN (COSTS false) EXECUTE p7 (10);
717 -------------------------------------------------
720 Hash Cond: (t1.id = t2.id)
721 -> Index Only Scan using t1_pkey on t1
722 Index Cond: (id > $1)
727 EXPLAIN (COSTS false) EXECUTE p7 (10);
729 -------------------------------------------------
732 Hash Cond: (t1.id = t2.id)
733 -> Index Only Scan using t1_pkey on t1
734 Index Cond: (id > $1)
741 EXPLAIN (COSTS false) SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id AND t1.id > 10;
743 -------------------------------------------------
746 Merge Cond: (t1.id = t2.id)
747 -> Index Only Scan using t1_pkey on t1
748 Index Cond: (id > 10)
749 -> Index Only Scan using t2_pkey on t2
752 EXPLAIN (COSTS false) EXECUTE p7 (10);
754 -------------------------------------------------
757 Merge Cond: (t1.id = t2.id)
758 -> Index Only Scan using t1_pkey on t1
759 Index Cond: (id > $1)
760 -> Index Only Scan using t2_pkey on t2
763 /*+ HashJoin(t1 t2) */
764 EXPLAIN (COSTS false) EXECUTE p7 (10);
766 -------------------------------------------------
769 Merge Cond: (t1.id = t2.id)
770 -> Index Only Scan using t1_pkey on t1
771 Index Cond: (id > $1)
772 -> Index Only Scan using t2_pkey on t2
775 INSERT INTO t1 SELECT i, i % 100 FROM (SELECT generate_series(1, 10000) i) t;
777 EXPLAIN (COSTS false) SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id AND t1.id > 10;
779 -------------------------------------------------
782 Merge Cond: (t1.id = t2.id)
783 -> Index Only Scan using t1_pkey on t1
784 Index Cond: (id > 10)
785 -> Index Only Scan using t2_pkey on t2
788 /*+ HashJoin(t1 t2) */
789 EXPLAIN (COSTS false) EXECUTE p7 (10);
794 -------------------------------------------------
797 Hash Cond: (t1.id = t2.id)
798 -> Index Only Scan using t1_pkey on t1
799 Index Cond: (id > $1)
804 /*+ NestLoop(t1 t2) */
805 EXPLAIN (COSTS false) EXECUTE p7 (10);
807 -------------------------------------------------
810 Hash Cond: (t1.id = t2.id)
811 -> Index Only Scan using t1_pkey on t1
812 Index Cond: (id > $1)
818 /*+ NestLoop(t1 t2) */
819 EXPLAIN (COSTS false) EXECUTE p8 (10);
820 ERROR: prepared statement "p8" does not exist
821 /*+ NestLoop(t1 t2) */
822 EXPLAIN (COSTS false) SELECT count(*) FROM t1, t2 WHERE t1.id = t2.id AND t1.id > 10;
831 --------------------------------------------------------
835 -> Index Scan using t1_pkey on t1
836 Index Cond: ((id > 10) AND (id = t2.id))