2 SET pg_hint_plan.enable_hint TO on;
3 SET pg_hint_plan.enable_hint_table TO on;
4 SET pg_hint_plan.debug_print TO on;
5 SET client_min_messages TO LOG;
6 SET search_path TO public;
10 ---- No.A-2-1 uninstall
14 CREATE EXTENSION pg_hint_plan;
17 DROP EXTENSION pg_hint_plan;
20 CREATE EXTENSION pg_hint_plan SCHEMA other_name;
22 CREATE EXTENSION pg_hint_plan;
25 ---- No. A-5-1 comment pattarn
30 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
34 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
38 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
42 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
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 /*+SeqScan(t1) /* nest comment */ */
54 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
57 /* +SeqScan(t1) /* nest comment */ */
58 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
61 /*SeqScan(t1) /* nest comment */ */
62 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
65 ---- No. A-5-2 hint position
69 EXPLAIN (COSTS false) SELECT c1 FROM s1.t1 WHERE t1.c1 = 1;
71 EXPLAIN (COSTS false) SELECT c1 FROM s1.t1 WHERE t1.c1 = 1;
74 EXPLAIN (COSTS false) SELECT c1 /*+SeqScan(t1)*/ FROM s1.t1 WHERE t1.c1 = 1;
77 EXPLAIN (COSTS false) SELECT c1 AS "c1"/*+SeqScan(t1)*/ FROM s1.t1 WHERE t1.c1 = 1;
80 EXPLAIN (COSTS false) SELECT * /*+SeqScan(t1)*/ FROM s1.t1 WHERE t1.c1 = 1;
83 ---- No. A-6-1 hint's table definition
89 ---- No. A-6-2 search condition
91 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
93 INSERT INTO hint_plan.hints VALUES (
94 'EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = ?;',
97 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
100 INSERT INTO hint_plan.hints VALUES (
101 'EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = ?;',
104 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
105 TRUNCATE hint_plan.hints;
108 INSERT INTO hint_plan.hints VALUES (
109 'EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = ?;',
110 'dummy_application_name',
113 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
114 TRUNCATE hint_plan.hints;
117 INSERT INTO hint_plan.hints VALUES (
118 'EXPLAIN (COSTS false) SELECT * FROM s1.t1;',
122 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
123 TRUNCATE hint_plan.hints;
126 ---- No. A-6-3 number of constant
130 INSERT INTO hint_plan.hints VALUES (
131 'EXPLAIN (COSTS false) SELECT c1 FROM s1.t1;',
135 EXPLAIN (COSTS false) SELECT c1 FROM s1.t1;
136 TRUNCATE hint_plan.hints;
139 INSERT INTO hint_plan.hints VALUES (
140 'EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = ?;',
144 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
145 TRUNCATE hint_plan.hints;
148 INSERT INTO hint_plan.hints VALUES (
149 'EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = ? OR t1.c1 = ?;',
153 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 OR t1.c1 = 0;
154 TRUNCATE hint_plan.hints;
157 ---- No. A-7-2 hint delimiter
160 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
168 /*+Set(enable_indexscan"off")Set(enable_bitmapscan"off")*/
169 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
172 /*+ Set(enable_indexscan"off")Set(enable_bitmapscan"off")*/
173 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
176 /*+Set(enable_indexscan"off")Set(enable_bitmapscan"off") */
177 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
180 /*+ Set (enable_indexscan"off") Set (enable_bitmapscan"off")*/
181 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
184 /*+Set ( enable_indexscan"off")Set ( enable_bitmapscan"off")*/
185 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
188 /*+Set(enable_indexscan"off" ) Set(enable_bitmapscan"off" ) */
189 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
192 /*+Set( enable_indexscan "off" )Set( enable_bitmapscan "off" )*/
193 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
196 /*+ Set ( enable_indexscan "off" ) Set ( enable_bitmapscan "off" ) */
197 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
200 /*+ Set(enable_indexscan"off")Set(enable_bitmapscan"off")*/
201 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
204 /*+Set(enable_indexscan"off")Set(enable_bitmapscan"off") */
205 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
208 /*+ Set (enable_indexscan"off") Set (enable_bitmapscan"off")*/
209 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
212 /*+Set ( enable_indexscan"off")Set ( enable_bitmapscan"off")*/
213 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
216 /*+Set(enable_indexscan"off" ) Set(enable_bitmapscan"off" ) */
217 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
220 /*+Set( enable_indexscan "off" )Set( enable_bitmapscan "off" )*/
221 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
224 /*+ Set ( enable_indexscan "off" ) Set ( enable_bitmapscan "off" ) */
225 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
229 Set(enable_indexscan"off")Set(enable_bitmapscan"off")*/
230 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
233 /*+Set(enable_indexscan"off")Set(enable_bitmapscan"off")
235 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
240 (enable_indexscan"off")
242 (enable_bitmapscan"off")*/
243 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
248 enable_indexscan"off")Set
250 enable_bitmapscan"off")*/
251 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
254 /*+Set(enable_indexscan"off"
256 Set(enable_bitmapscan"off"
259 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
269 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
284 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
288 Set(enable_indexscan"off")Set(enable_bitmapscan"off")*/
289 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
292 /*+Set(enable_indexscan"off")Set(enable_bitmapscan"off")
294 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
299 (enable_indexscan"off")
301 (enable_bitmapscan"off")*/
302 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
307 enable_indexscan"off")Set
309 enable_bitmapscan"off")*/
310 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
313 /*+Set(enable_indexscan"off"
315 Set(enable_bitmapscan"off"
318 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
328 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
343 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
346 ---- No. A-7-3 hint object pattern
347 ---- No. A-9-2 message object pattern
353 EXPLAIN (COSTS false) SELECT * FROM s1.t1 t WHERE t.c1 = 1;
355 EXPLAIN (COSTS false) SELECT * FROM s1.t1 ttt WHERE ttt.c1 = 1;
357 EXPLAIN (COSTS false) SELECT * FROM s1.t1 t WHERE t.c1 = 1;
359 EXPLAIN (COSTS false) SELECT * FROM s1.t1 ttt WHERE ttt.c1 = 1;
364 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "T" WHERE "T".c1 = 1;
366 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "TTT" WHERE "TTT".c1 = 1;
368 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "T" WHERE "T".c1 = 1;
370 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "TTT" WHERE "TTT".c1 = 1;
375 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "(" WHERE "(".c1 = 1;
377 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "(((" WHERE "(((".c1 = 1;
379 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "(" WHERE "(".c1 = 1;
381 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "(((" WHERE "(((".c1 = 1;
386 EXPLAIN (COSTS false) SELECT * FROM s1.t1 ")" WHERE ")".c1 = 1;
388 EXPLAIN (COSTS false) SELECT * FROM s1.t1 ")" WHERE ")".c1 = 1;
390 EXPLAIN (COSTS false) SELECT * FROM s1.t1 ")))" WHERE ")))".c1 = 1;
395 EXPLAIN (COSTS false) SELECT * FROM s1.t1 """" WHERE """".c1 = 1;
397 EXPLAIN (COSTS false) SELECT * FROM s1.t1 """" WHERE """".c1 = 1;
398 /*+SeqScan("""""""")*/
399 EXPLAIN (COSTS false) SELECT * FROM s1.t1 """""""" WHERE """""""".c1 = 1;
404 EXPLAIN (COSTS false) SELECT * FROM s1.t1 " " WHERE " ".c1 = 1;
406 EXPLAIN (COSTS false) SELECT * FROM s1.t1 " " WHERE " ".c1 = 1;
408 EXPLAIN (COSTS false) SELECT * FROM s1.t1 " " WHERE " ".c1 = 1;
413 EXPLAIN (COSTS false) SELECT * FROM s1.t1 " " WHERE " ".c1 = 1;
415 EXPLAIN (COSTS false) SELECT * FROM s1.t1 " " WHERE " ".c1 = 1;
417 EXPLAIN (COSTS false) SELECT * FROM s1.t1 " " WHERE " ".c1 = 1;
423 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "
428 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "
435 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "
446 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "Set" WHERE "Set".c1 = 1;
448 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "Set" WHERE "Set".c1 = 1;
449 /*+SeqScan("Set SeqScan Leading")*/
450 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "Set SeqScan Leading" WHERE "Set SeqScan Leading".c1 = 1;
455 EXPLAIN (COSTS false) SELECT * FROM s1.t1 あ WHERE あ.c1 = 1;
457 EXPLAIN (COSTS false) SELECT * FROM s1.t1 あいう WHERE あいう.c1 = 1;
459 EXPLAIN (COSTS false) SELECT * FROM s1.t1 あ WHERE あ.c1 = 1;
461 EXPLAIN (COSTS false) SELECT * FROM s1.t1 あいう WHERE あいう.c1 = 1;
466 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "/**/" WHERE "/**/".c1 = 1;
467 /*+SeqScan(/**//**//**/)*/
468 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "/**//**//**/" WHERE "/**//**//**/".c1 = 1;
474 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "tT()""
475 Set/**/あ" WHERE "tT()""
481 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "tT()""
486 ---- No. A-7-4 hint parse error
490 /*+Set(enable_indexscan off)Set enable_tidscan off)Set(enable_bitmapscan off)SeqScan(t1)*/
491 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
494 /*+Set(enable_indexscan off)Set(enable_tidscan off Set(enable_bitmapscan off)SeqScan(t1)*/
495 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
498 /*+Set(enable_indexscan off)Set(enable_tidscan "off)Set(enable_bitmapscan off)SeqScan(t1)*/
499 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
502 /*+Set(enable_indexscan off)SeqScan("")Set(enable_bitmapscan off)*/
503 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
506 /*+Set(enable_indexscan off)NoSet(enable_tidscan off)Set(enable_bitmapscan off)SeqScan(t1)*/
507 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
510 /*+Set(enable_indexscan off)"Set"(enable_tidscan off)Set(enable_bitmapscan off)SeqScan(t1)*/
511 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
514 /*+Set(enable_indexscan off)Set(enable_tidscan /* value */off)Set(enable_bitmapscan off)SeqScan(t1)*/
515 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
518 ---- No. A-8-1 original GUC parameter
523 SET pg_hint_plan.debug_print TO off;
524 SHOW pg_hint_plan.enable_hint;
525 SHOW pg_hint_plan.debug_print;
526 SHOW pg_hint_plan.parse_messages;
527 SET pg_hint_plan.enable_hint TO off;
528 SET pg_hint_plan.debug_print TO on;
529 SET pg_hint_plan.parse_messages TO error;
530 SHOW pg_hint_plan.enable_hint;
531 SHOW pg_hint_plan.debug_print;
532 SHOW pg_hint_plan.parse_messages;
533 RESET pg_hint_plan.enable_hint;
534 RESET pg_hint_plan.debug_print;
535 RESET pg_hint_plan.parse_messages;
536 SHOW pg_hint_plan.enable_hint;
537 SHOW pg_hint_plan.debug_print;
538 SHOW pg_hint_plan.parse_messages;
541 SET ROLE normal_user;
542 SHOW pg_hint_plan.enable_hint;
543 SHOW pg_hint_plan.debug_print;
544 SHOW pg_hint_plan.parse_messages;
545 SET pg_hint_plan.enable_hint TO off;
546 SET pg_hint_plan.debug_print TO on;
547 SET pg_hint_plan.parse_messages TO error;
548 SHOW pg_hint_plan.enable_hint;
549 SHOW pg_hint_plan.debug_print;
550 SHOW pg_hint_plan.parse_messages;
551 RESET pg_hint_plan.enable_hint;
552 RESET pg_hint_plan.debug_print;
553 RESET pg_hint_plan.parse_messages;
554 SHOW pg_hint_plan.enable_hint;
555 SHOW pg_hint_plan.debug_print;
556 SHOW pg_hint_plan.parse_messages;
561 ---- No. A-8-2 original GUC parameter pg_hint_plan.enable_hint
565 SET pg_hint_plan.enable_hint TO on;
566 SHOW pg_hint_plan.enable_hint;
567 /*+Set(enable_indexscan off)*/
568 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
571 SET pg_hint_plan.enable_hint TO off;
572 SHOW pg_hint_plan.enable_hint;
573 /*+Set(enable_indexscan off)*/
574 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
577 SET pg_hint_plan.enable_hint TO DEFAULT;
578 SHOW pg_hint_plan.enable_hint;
579 /*+Set(enable_indexscan off)*/
580 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
583 SET pg_hint_plan.enable_hint TO enable;
584 SHOW pg_hint_plan.enable_hint;
587 ---- No. A-8-3 original GUC parameter pg_hint_plan.debug_print
591 SET pg_hint_plan.debug_print TO on;
592 SHOW pg_hint_plan.debug_print;
593 /*+Set(enable_indexscan off)*/
594 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
597 SET pg_hint_plan.debug_print TO off;
598 SHOW pg_hint_plan.debug_print;
599 /*+Set(enable_indexscan off)*/
600 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
603 SET pg_hint_plan.debug_print TO DEFAULT;
604 SHOW pg_hint_plan.debug_print;
605 /*+Set(enable_indexscan off)*/
606 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
609 SET pg_hint_plan.debug_print TO enable;
610 SHOW pg_hint_plan.debug_print;
613 ---- No. A-8-4 original GUC parameter pg_hint_plan.parse_messages
616 SET client_min_messages TO debug5;
619 SET pg_hint_plan.parse_messages TO debug5;
620 SHOW pg_hint_plan.parse_messages;
622 SET client_min_messages TO debug4;
626 SET pg_hint_plan.parse_messages TO debug4;
627 SHOW pg_hint_plan.parse_messages;
629 SET client_min_messages TO debug3;
633 SET pg_hint_plan.parse_messages TO debug3;
634 SHOW pg_hint_plan.parse_messages;
636 SET client_min_messages TO debug2;
640 SET pg_hint_plan.parse_messages TO debug2;
641 SHOW pg_hint_plan.parse_messages;
643 SET client_min_messages TO debug1;
647 SET pg_hint_plan.parse_messages TO debug1;
648 SHOW pg_hint_plan.parse_messages;
650 SET client_min_messages TO log;
654 SET pg_hint_plan.parse_messages TO log;
655 SHOW pg_hint_plan.parse_messages;
657 SET client_min_messages TO info;
661 SET pg_hint_plan.parse_messages TO info;
662 SHOW pg_hint_plan.parse_messages;
664 SET client_min_messages TO notice;
668 SET pg_hint_plan.parse_messages TO notice;
669 SHOW pg_hint_plan.parse_messages;
671 SET client_min_messages TO warning;
675 SET pg_hint_plan.parse_messages TO warning;
676 SHOW pg_hint_plan.parse_messages;
678 SET client_min_messages TO error;
682 SET pg_hint_plan.parse_messages TO error;
683 SHOW pg_hint_plan.parse_messages;
685 SET client_min_messages TO fatal;
689 RESET client_min_messages;
690 SET pg_hint_plan.parse_messages TO DEFAULT;
691 SHOW pg_hint_plan.parse_messages;
695 SET pg_hint_plan.parse_messages TO fatal;
696 SHOW pg_hint_plan.parse_messages;
699 SET pg_hint_plan.parse_messages TO panic;
700 SHOW pg_hint_plan.parse_messages;
703 SET pg_hint_plan.parse_messages TO on;
704 SHOW pg_hint_plan.parse_messages;
707 ---- No. A-8-5 original GUC parameter pg_hint_plan.enable_hint_table
711 ---- No. A-9-1 parse error message output
715 /*+"Set"(enable_indexscan on)*/SELECT 1;
716 /*+Set()(enable_indexscan on)*/SELECT 1;
717 /*+Set(enable_indexscan on*/SELECT 1;
720 ---- No. A-9-3 hint state output
723 SET pg_hint_plan.debug_print TO on;
724 SET client_min_messages TO LOG;
728 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
731 /*+SeqScan(no_table)*/
732 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
735 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
736 /*+TidScan(t1)BitmapScan(t1)*/
737 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
738 /*+TidScan(t1)BitmapScan(t1)IndexScan(t1)*/
739 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
740 /*+TidScan(t1)BitmapScan(t1)IndexScan(t1)SeqScan(t1)*/
741 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
744 /*+Set(enable_indexscan enable)*/
745 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
748 ---- No. A-10-1 hint state output
751 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
752 EXPLAIN (COSTS false) EXECUTE p1;
755 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
756 EXPLAIN (COSTS false) EXECUTE p1 (1000);
757 EXPLAIN (COSTS false) EXECUTE p1 (1000);
758 EXPLAIN (COSTS false) EXECUTE p1 (1000);
759 EXPLAIN (COSTS false) EXECUTE p1 (1000);
760 EXPLAIN (COSTS false) EXECUTE p1 (1000);
761 EXPLAIN (COSTS false) EXECUTE p1 (1000);
767 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
769 EXPLAIN (COSTS false) EXECUTE p1;
770 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
772 EXPLAIN (COSTS false) EXECUTE p1;
776 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
778 EXPLAIN (COSTS false) EXECUTE p1 (1000);
780 EXPLAIN (COSTS false) EXECUTE p1 (1000);
782 EXPLAIN (COSTS false) EXECUTE p1 (1000);
784 EXPLAIN (COSTS false) EXECUTE p1 (1000);
786 EXPLAIN (COSTS false) EXECUTE p1 (1000);
788 EXPLAIN (COSTS false) EXECUTE p1 (1000);
789 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
791 EXPLAIN (COSTS false) EXECUTE p1 (1000);
797 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
798 EXPLAIN (COSTS false) EXECUTE p1;
799 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
800 EXPLAIN (COSTS false) EXECUTE p1;
804 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
805 EXPLAIN (COSTS false) EXECUTE p1 (1000);
806 EXPLAIN (COSTS false) EXECUTE p1 (1000);
807 EXPLAIN (COSTS false) EXECUTE p1 (1000);
808 EXPLAIN (COSTS false) EXECUTE p1 (1000);
809 EXPLAIN (COSTS false) EXECUTE p1 (1000);
810 EXPLAIN (COSTS false) EXECUTE p1 (1000);
811 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
812 EXPLAIN (COSTS false) EXECUTE p1 (1000);
817 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
819 EXPLAIN (COSTS false) EXECUTE p1;
820 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
822 EXPLAIN (COSTS false) EXECUTE p1;
825 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
827 EXPLAIN (COSTS false) EXECUTE p1 (1000);
828 EXPLAIN (COSTS false) EXECUTE p1 (1000);
829 EXPLAIN (COSTS false) EXECUTE p1 (1000);
830 EXPLAIN (COSTS false) EXECUTE p1 (1000);
831 EXPLAIN (COSTS false) EXECUTE p1 (1000);
832 EXPLAIN (COSTS false) EXECUTE p1 (1000);
833 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
835 EXPLAIN (COSTS false) EXECUTE p1 (1000);
841 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
843 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
844 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
846 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
850 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
852 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
854 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
856 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
858 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
860 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
862 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
863 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
865 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
871 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
872 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
873 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
874 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
878 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
879 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
880 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
881 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
882 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
883 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
884 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
885 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
886 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
891 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
893 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
894 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
896 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
899 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
901 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
903 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
905 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
907 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
909 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
911 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
912 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
914 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
918 ---- No. A-10-4 EXECUTE statement name error
923 SHOW pg_hint_plan.debug_print;
926 ---- No. A-11-5 EXECUTE statement name error
930 SELECT pg_stat_statements_reset();
931 SELECT * FROM s1.t1 WHERE t1.c1 = 1;
932 /*+Set(enable_seqscan off)*/ SELECT * FROM s1.t1 WHERE t1.c1 = 1;
933 /*+SeqScan(t1)*/ SELECT * FROM s1.t1 WHERE t1.c1 = 1;
934 SELECT s.query, s.calls
935 FROM public.pg_stat_statements s
936 JOIN pg_catalog.pg_database d
941 ---- No. A-12-1 reset of global variable of core at the error
942 ---- No. A-12-2 reset of global variable of original at the error
945 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
946 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)NestLoop(t1 t1)*/
947 PREPARE p1 AS SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
948 EXPLAIN (COSTS false) EXECUTE p1;
952 SELECT name, setting FROM settings;
953 SET pg_hint_plan.parse_messages TO error;
954 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)NestLoop(t1 t1)*/
955 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
956 SELECT name, setting FROM settings;
957 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)*/
958 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
962 SELECT name, setting FROM settings;
963 SET pg_hint_plan.parse_messages TO error;
964 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)NestLoop(t1 t1)*/
965 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
966 SELECT name, setting FROM settings;
967 EXPLAIN (COSTS false) EXECUTE p1;
971 SELECT name, setting FROM settings;
972 SET pg_hint_plan.parse_messages TO error;
973 EXPLAIN (COSTS false) EXECUTE p2;
974 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)*/
975 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
976 EXPLAIN (COSTS false) EXECUTE p1;
977 SELECT name, setting FROM settings;
981 SELECT name, setting FROM settings;
982 SET pg_hint_plan.parse_messages TO error;
983 EXPLAIN (COSTS false) EXECUTE p2;
984 EXPLAIN (COSTS false) EXECUTE p1;
985 SELECT name, setting FROM settings;
988 SET pg_hint_plan.parse_messages TO LOG;
991 ---- No. A-12-3 effective range of the hint
994 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
997 SET enable_indexscan TO off;
998 SET enable_mergejoin TO off;
999 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1000 SELECT name, setting FROM settings;
1001 /*+Set(enable_indexscan on)Set(geqo_threshold 100)IndexScan(t2)MergeJoin(t1 t2)Leading(t2 t1)*/
1002 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1003 SELECT name, setting FROM settings;
1004 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1007 SET enable_indexscan TO off;
1008 SET enable_mergejoin TO off;
1009 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1010 SELECT name, setting FROM settings;
1012 /*+Set(enable_indexscan on)Set(geqo_threshold 100)IndexScan(t2)MergeJoin(t1 t2)Leading(t2 t1)*/
1013 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1016 SELECT name, setting FROM settings;
1017 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1021 SET enable_indexscan TO off;
1022 SET enable_mergejoin TO off;
1023 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1024 SELECT name, setting FROM settings;
1025 /*+Set(enable_indexscan on)Set(geqo_threshold 100)IndexScan(t2)MergeJoin(t1 t2)Leading(t2 t1)*/
1026 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1028 SET enable_indexscan TO off;
1029 SET enable_mergejoin TO off;
1030 LOAD 'pg_hint_plan';
1031 SELECT name, setting FROM settings;
1032 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1034 SET pg_hint_plan.enable_hint TO on;
1035 SET pg_hint_plan.debug_print TO on;
1036 SET client_min_messages TO LOG;
1037 SET search_path TO public;
1038 RESET enable_indexscan;
1039 RESET enable_mergejoin;
1042 ---- No. A-13 call planner recursively
1045 CREATE OR REPLACE FUNCTION nested_planner(cnt int) RETURNS int AS $$
1049 RAISE NOTICE 'nested_planner(%)', cnt;
1056 EXECUTE '/*+ IndexScan(t_1) */'
1057 ' SELECT nested_planner($1) FROM s1.t1 t_1'
1058 ' JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)'
1059 ' ORDER BY t_1.c1 LIMIT 1'
1060 INTO new_cnt USING cnt - 1;
1064 $$ LANGUAGE plpgsql IMMUTABLE;
1067 ---- No. A-13-2 use hint of main query
1071 EXPLAIN (COSTS false) SELECT nested_planner(1) FROM s1.t1 t_1 ORDER BY t_1.c1;
1073 EXPLAIN (COSTS false) SELECT nested_planner(1) FROM s1.t1 t_1 ORDER BY t_1.c1;
1076 ---- No. A-13-3 output number of times of debugging log
1080 EXPLAIN (COSTS false) SELECT nested_planner(1) FROM s1.t1 t_1 ORDER BY t_1.c1;
1082 EXPLAIN (COSTS false) SELECT nested_planner(1) FROM s1.t1 t_1 ORDER BY t_1.c1;
1085 EXPLAIN (COSTS false) SELECT nested_planner(2) FROM s1.t1 t_1 ORDER BY t_1.c1;
1087 EXPLAIN (COSTS false) SELECT nested_planner(2) FROM s1.t1 t_1 ORDER BY t_1.c1;
1090 EXPLAIN (COSTS false) SELECT nested_planner(5) FROM s1.t1 t_1 ORDER BY t_1.c1;
1092 EXPLAIN (COSTS false) SELECT nested_planner(5) FROM s1.t1 t_1 ORDER BY t_1.c1;
1095 ---- No. A-13-4 output of debugging log on hint status
1099 /*+HashJoin(t_1 t_2)*/
1100 EXPLAIN (COSTS false)
1101 SELECT nested_planner(2) FROM s1.t1 t_1
1102 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
1106 /*+HashJoin(st_1 st_2)*/
1107 EXPLAIN (COSTS false)
1108 SELECT nested_planner(2) FROM s1.t1 st_1
1109 JOIN s1.t2 st_2 ON (st_1.c1 = st_2.c1)
1113 /*+HashJoin(t_1 t_2)*/
1114 EXPLAIN (COSTS false)
1115 SELECT nested_planner(2) FROM s1.t1 st_1
1116 JOIN s1.t2 st_2 ON (st_1.c1 = st_2.c1)
1120 /*+HashJoin(st_1 st_2)*/
1121 EXPLAIN (COSTS false)
1122 SELECT nested_planner(2) FROM s1.t1 t_1
1123 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
1127 /*+HashJoin(t_1 t_1)*/
1128 EXPLAIN (COSTS false)
1129 SELECT nested_planner(2) FROM s1.t1 t_1
1133 CREATE OR REPLACE FUNCTION nested_planner_one_t(cnt int) RETURNS int AS $$
1137 RAISE NOTICE 'nested_planner_one_t(%)', cnt;
1143 EXECUTE '/*+ IndexScan(t_1) */'
1144 ' SELECT nested_planner_one_t($1) FROM s1.t1 t_1'
1145 ' ORDER BY t_1.c1 LIMIT 1'
1146 INTO new_cnt USING cnt - 1;
1150 $$ LANGUAGE plpgsql IMMUTABLE;
1152 EXPLAIN (COSTS false)
1153 SELECT nested_planner_one_t(2) FROM s1.t1 t_1
1154 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
1156 /*+HashJoin(t_1 t_1)*/
1157 EXPLAIN (COSTS false)
1158 SELECT nested_planner_one_t(2) FROM s1.t1 t_1
1159 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
1162 DROP FUNCTION nested_planner_one_t(int);
1165 /*+HashJoin(t_1 t_1)*/
1166 EXPLAIN (COSTS false)
1167 SELECT nested_planner(2) FROM s1.t1 t_1
1168 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
1172 /*+MergeJoin(t_1 t_2)HashJoin(t_1 t_2)*/
1173 EXPLAIN (COSTS false)
1174 SELECT nested_planner(2) FROM s1.t1 t_1
1175 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)