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
710 INSERT INTO hint_plan.hints VALUES (
711 'EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = ?;',
716 SET pg_hint_plan.enable_hint_table TO on;
717 SHOW pg_hint_plan.enable_hint_table;
718 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
721 SET pg_hint_plan.enable_hint_table TO off;
722 SHOW pg_hint_plan.enable_hint_table;
723 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
726 SET pg_hint_plan.enable_hint_table TO DEFAULT;
727 SHOW pg_hint_plan.enable_hint_table;
728 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
731 SET pg_hint_plan.enable_hint_table TO enable;
732 SHOW pg_hint_plan.enable_hint_table;
734 TRUNCATE hint_plan.hints;
737 ---- No. A-9-1 parse error message output
741 /*+"Set"(enable_indexscan on)*/SELECT 1;
742 /*+Set()(enable_indexscan on)*/SELECT 1;
743 /*+Set(enable_indexscan on*/SELECT 1;
746 ---- No. A-9-3 hint state output
749 SET pg_hint_plan.debug_print TO on;
750 SET client_min_messages TO LOG;
754 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
757 /*+SeqScan(no_table)*/
758 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
761 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
762 /*+TidScan(t1)BitmapScan(t1)*/
763 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
764 /*+TidScan(t1)BitmapScan(t1)IndexScan(t1)*/
765 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
766 /*+TidScan(t1)BitmapScan(t1)IndexScan(t1)SeqScan(t1)*/
767 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
770 /*+Set(enable_indexscan enable)*/
771 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
774 ---- No. A-10-1 hint state output
777 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
778 EXPLAIN (COSTS false) EXECUTE p1;
781 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
782 EXPLAIN (COSTS false) EXECUTE p1 (1000);
783 EXPLAIN (COSTS false) EXECUTE p1 (1000);
784 EXPLAIN (COSTS false) EXECUTE p1 (1000);
785 EXPLAIN (COSTS false) EXECUTE p1 (1000);
786 EXPLAIN (COSTS false) EXECUTE p1 (1000);
787 EXPLAIN (COSTS false) EXECUTE p1 (1000);
793 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
795 EXPLAIN (COSTS false) EXECUTE p1;
796 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
798 EXPLAIN (COSTS false) EXECUTE p1;
802 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
804 EXPLAIN (COSTS false) EXECUTE p1 (1000);
806 EXPLAIN (COSTS false) EXECUTE p1 (1000);
808 EXPLAIN (COSTS false) EXECUTE p1 (1000);
810 EXPLAIN (COSTS false) EXECUTE p1 (1000);
812 EXPLAIN (COSTS false) EXECUTE p1 (1000);
814 EXPLAIN (COSTS false) EXECUTE p1 (1000);
815 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
817 EXPLAIN (COSTS false) EXECUTE p1 (1000);
823 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
824 EXPLAIN (COSTS false) EXECUTE p1;
825 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
826 EXPLAIN (COSTS false) EXECUTE p1;
830 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
831 EXPLAIN (COSTS false) EXECUTE p1 (1000);
832 EXPLAIN (COSTS false) EXECUTE p1 (1000);
833 EXPLAIN (COSTS false) EXECUTE p1 (1000);
834 EXPLAIN (COSTS false) EXECUTE p1 (1000);
835 EXPLAIN (COSTS false) EXECUTE p1 (1000);
836 EXPLAIN (COSTS false) EXECUTE p1 (1000);
837 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
838 EXPLAIN (COSTS false) EXECUTE p1 (1000);
843 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
845 EXPLAIN (COSTS false) EXECUTE p1;
846 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
848 EXPLAIN (COSTS false) EXECUTE p1;
851 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
853 EXPLAIN (COSTS false) EXECUTE p1 (1000);
854 EXPLAIN (COSTS false) EXECUTE p1 (1000);
855 EXPLAIN (COSTS false) EXECUTE p1 (1000);
856 EXPLAIN (COSTS false) EXECUTE p1 (1000);
857 EXPLAIN (COSTS false) EXECUTE p1 (1000);
858 EXPLAIN (COSTS false) EXECUTE p1 (1000);
859 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
861 EXPLAIN (COSTS false) EXECUTE p1 (1000);
867 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
869 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
870 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
872 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
876 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
878 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
880 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
882 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
884 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
886 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
888 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
889 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
891 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
897 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
898 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
899 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
900 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
904 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
905 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
906 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
907 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
908 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
909 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
910 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
911 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
912 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
917 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
919 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
920 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
922 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
925 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
927 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
929 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
931 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
933 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
935 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
937 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
938 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
940 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
944 ---- No. A-10-4 EXECUTE statement name error
949 SHOW pg_hint_plan.debug_print;
952 ---- No. A-11-5 EXECUTE statement name error
956 SELECT pg_stat_statements_reset();
957 SELECT * FROM s1.t1 WHERE t1.c1 = 1;
958 /*+Set(enable_seqscan off)*/ SELECT * FROM s1.t1 WHERE t1.c1 = 1;
959 /*+SeqScan(t1)*/ SELECT * FROM s1.t1 WHERE t1.c1 = 1;
960 SELECT s.query, s.calls
961 FROM public.pg_stat_statements s
962 JOIN pg_catalog.pg_database d
967 ---- No. A-12-1 reset of global variable of core at the error
968 ---- No. A-12-2 reset of global variable of original at the error
971 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
972 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)NestLoop(t1 t1)*/
973 PREPARE p1 AS SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
974 EXPLAIN (COSTS false) EXECUTE p1;
978 SELECT name, setting FROM settings;
979 SET pg_hint_plan.parse_messages TO error;
980 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)NestLoop(t1 t1)*/
981 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
982 SELECT name, setting FROM settings;
983 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)*/
984 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
988 SELECT name, setting FROM settings;
989 SET pg_hint_plan.parse_messages TO error;
990 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)NestLoop(t1 t1)*/
991 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
992 SELECT name, setting FROM settings;
993 EXPLAIN (COSTS false) EXECUTE p1;
997 SELECT name, setting FROM settings;
998 SET pg_hint_plan.parse_messages TO error;
999 EXPLAIN (COSTS false) EXECUTE p2;
1000 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)*/
1001 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1002 EXPLAIN (COSTS false) EXECUTE p1;
1003 SELECT name, setting FROM settings;
1007 SELECT name, setting FROM settings;
1008 SET pg_hint_plan.parse_messages TO error;
1009 EXPLAIN (COSTS false) EXECUTE p2;
1010 EXPLAIN (COSTS false) EXECUTE p1;
1011 SELECT name, setting FROM settings;
1014 SET pg_hint_plan.parse_messages TO LOG;
1017 ---- No. A-12-3 effective range of the hint
1020 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1023 SET enable_indexscan TO off;
1024 SET enable_mergejoin TO off;
1025 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1026 SELECT name, setting FROM settings;
1027 /*+Set(enable_indexscan on)Set(geqo_threshold 100)IndexScan(t2)MergeJoin(t1 t2)Leading(t2 t1)*/
1028 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1029 SELECT name, setting FROM settings;
1030 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1033 SET enable_indexscan TO off;
1034 SET enable_mergejoin TO off;
1035 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1036 SELECT name, setting FROM settings;
1038 /*+Set(enable_indexscan on)Set(geqo_threshold 100)IndexScan(t2)MergeJoin(t1 t2)Leading(t2 t1)*/
1039 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1042 SELECT name, setting FROM settings;
1043 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1047 SET enable_indexscan TO off;
1048 SET enable_mergejoin TO off;
1049 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1050 SELECT name, setting FROM settings;
1051 /*+Set(enable_indexscan on)Set(geqo_threshold 100)IndexScan(t2)MergeJoin(t1 t2)Leading(t2 t1)*/
1052 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1054 SET enable_indexscan TO off;
1055 SET enable_mergejoin TO off;
1056 LOAD 'pg_hint_plan';
1057 SELECT name, setting FROM settings;
1058 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1060 SET pg_hint_plan.enable_hint TO on;
1061 SET pg_hint_plan.debug_print TO on;
1062 SET client_min_messages TO LOG;
1063 SET search_path TO public;
1064 RESET enable_indexscan;
1065 RESET enable_mergejoin;
1068 ---- No. A-13 call planner recursively
1071 CREATE OR REPLACE FUNCTION nested_planner(cnt int) RETURNS int AS $$
1075 RAISE NOTICE 'nested_planner(%)', cnt;
1082 EXECUTE '/*+ IndexScan(t_1) */'
1083 ' SELECT nested_planner($1) FROM s1.t1 t_1'
1084 ' JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)'
1085 ' ORDER BY t_1.c1 LIMIT 1'
1086 INTO new_cnt USING cnt - 1;
1090 $$ LANGUAGE plpgsql IMMUTABLE;
1093 ---- No. A-13-2 use hint of main query
1097 EXPLAIN (COSTS false) SELECT nested_planner(1) FROM s1.t1 t_1 ORDER BY t_1.c1;
1099 EXPLAIN (COSTS false) SELECT nested_planner(1) FROM s1.t1 t_1 ORDER BY t_1.c1;
1102 ---- No. A-13-3 output number of times of debugging log
1106 EXPLAIN (COSTS false) SELECT nested_planner(1) FROM s1.t1 t_1 ORDER BY t_1.c1;
1108 EXPLAIN (COSTS false) SELECT nested_planner(1) FROM s1.t1 t_1 ORDER BY t_1.c1;
1111 EXPLAIN (COSTS false) SELECT nested_planner(2) FROM s1.t1 t_1 ORDER BY t_1.c1;
1113 EXPLAIN (COSTS false) SELECT nested_planner(2) FROM s1.t1 t_1 ORDER BY t_1.c1;
1116 EXPLAIN (COSTS false) SELECT nested_planner(5) FROM s1.t1 t_1 ORDER BY t_1.c1;
1118 EXPLAIN (COSTS false) SELECT nested_planner(5) FROM s1.t1 t_1 ORDER BY t_1.c1;
1121 ---- No. A-13-4 output of debugging log on hint status
1125 /*+HashJoin(t_1 t_2)*/
1126 EXPLAIN (COSTS false)
1127 SELECT nested_planner(2) FROM s1.t1 t_1
1128 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
1132 /*+HashJoin(st_1 st_2)*/
1133 EXPLAIN (COSTS false)
1134 SELECT nested_planner(2) FROM s1.t1 st_1
1135 JOIN s1.t2 st_2 ON (st_1.c1 = st_2.c1)
1139 /*+HashJoin(t_1 t_2)*/
1140 EXPLAIN (COSTS false)
1141 SELECT nested_planner(2) FROM s1.t1 st_1
1142 JOIN s1.t2 st_2 ON (st_1.c1 = st_2.c1)
1146 /*+HashJoin(st_1 st_2)*/
1147 EXPLAIN (COSTS false)
1148 SELECT nested_planner(2) FROM s1.t1 t_1
1149 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
1153 /*+HashJoin(t_1 t_1)*/
1154 EXPLAIN (COSTS false)
1155 SELECT nested_planner(2) FROM s1.t1 t_1
1159 CREATE OR REPLACE FUNCTION nested_planner_one_t(cnt int) RETURNS int AS $$
1163 RAISE NOTICE 'nested_planner_one_t(%)', cnt;
1169 EXECUTE '/*+ IndexScan(t_1) */'
1170 ' SELECT nested_planner_one_t($1) FROM s1.t1 t_1'
1171 ' ORDER BY t_1.c1 LIMIT 1'
1172 INTO new_cnt USING cnt - 1;
1176 $$ LANGUAGE plpgsql IMMUTABLE;
1178 EXPLAIN (COSTS false)
1179 SELECT nested_planner_one_t(2) FROM s1.t1 t_1
1180 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
1182 /*+HashJoin(t_1 t_1)*/
1183 EXPLAIN (COSTS false)
1184 SELECT nested_planner_one_t(2) FROM s1.t1 t_1
1185 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
1188 DROP FUNCTION nested_planner_one_t(int);
1191 /*+HashJoin(t_1 t_1)*/
1192 EXPLAIN (COSTS false)
1193 SELECT nested_planner(2) FROM s1.t1 t_1
1194 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
1198 /*+MergeJoin(t_1 t_2)HashJoin(t_1 t_2)*/
1199 EXPLAIN (COSTS false)
1200 SELECT nested_planner(2) FROM s1.t1 t_1
1201 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)