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;
9 ---- No.A-2-1 uninstall
13 CREATE EXTENSION pg_hint_plan;
16 DROP EXTENSION pg_hint_plan;
19 CREATE EXTENSION pg_hint_plan SCHEMA other_name;
21 CREATE EXTENSION pg_hint_plan;
24 ---- No. A-5-1 comment pattern
29 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
33 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
37 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
41 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
45 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
49 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
52 /*+SeqScan(t1) /* nest comment */ */
53 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
56 /* +SeqScan(t1) /* nest comment */ */
57 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
60 /*SeqScan(t1) /* nest comment */ */
61 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
64 ---- No. A-5-2 hint position
68 EXPLAIN (COSTS false) SELECT c1 FROM s1.t1 WHERE t1.c1 = 1;
70 EXPLAIN (COSTS false) SELECT c1 FROM s1.t1 WHERE t1.c1 = 1;
73 EXPLAIN (COSTS false) SELECT c1, c2 AS c_2 /*+SeqScan(t1)*/ FROM s1.t1 WHERE t1.c1 = 1;
76 EXPLAIN (COSTS false) SELECT c1 AS "c1"/*+SeqScan(t1)*/ FROM s1.t1 WHERE t1.c1 = 1;
79 EXPLAIN (COSTS false) SELECT * /*+SeqScan(t1)*/ FROM s1.t1 WHERE t1.c1 = 1;
82 ---- No. A-6-1 hint's table definition
85 SET pg_hint_plan.enable_hint_table TO on;
90 ---- No. A-6-2 search condition
92 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
94 INSERT INTO hint_plan.hints VALUES (
95 'EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = ?;',
98 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
101 INSERT INTO hint_plan.hints VALUES (
102 'EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = ?;',
105 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
106 TRUNCATE hint_plan.hints;
109 INSERT INTO hint_plan.hints VALUES (
110 'EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = ?;',
111 'dummy_application_name',
114 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
115 TRUNCATE hint_plan.hints;
118 INSERT INTO hint_plan.hints VALUES (
119 'EXPLAIN (COSTS false) SELECT * FROM s1.t1;',
123 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
124 TRUNCATE hint_plan.hints;
127 ---- No. A-6-3 number of constant
131 INSERT INTO hint_plan.hints VALUES (
132 'EXPLAIN (COSTS false) SELECT c1 FROM s1.t1;',
136 EXPLAIN (COSTS false) SELECT c1 FROM s1.t1;
137 TRUNCATE hint_plan.hints;
140 INSERT INTO hint_plan.hints VALUES (
141 'EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = ?;',
145 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
146 TRUNCATE hint_plan.hints;
149 INSERT INTO hint_plan.hints VALUES (
150 'EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = ? OR t1.c1 = ?;',
154 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 OR t1.c1 = 0;
155 TRUNCATE hint_plan.hints;
156 SET pg_hint_plan.enable_hint_table TO off;
159 ---- No. A-7-2 hint delimiter
162 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
170 /*+Set(enable_indexscan"off")Set(enable_bitmapscan"off")*/
171 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
174 /*+ Set(enable_indexscan"off")Set(enable_bitmapscan"off")*/
175 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
178 /*+Set(enable_indexscan"off")Set(enable_bitmapscan"off") */
179 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
182 /*+ Set (enable_indexscan"off") Set (enable_bitmapscan"off")*/
183 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
186 /*+Set ( enable_indexscan"off")Set ( enable_bitmapscan"off")*/
187 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
190 /*+Set(enable_indexscan"off" ) Set(enable_bitmapscan"off" ) */
191 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
194 /*+Set( enable_indexscan "off" )Set( enable_bitmapscan "off" )*/
195 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
198 /*+ Set ( enable_indexscan "off" ) Set ( enable_bitmapscan "off" ) */
199 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
202 /*+ Set(enable_indexscan"off")Set(enable_bitmapscan"off")*/
203 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
206 /*+Set(enable_indexscan"off")Set(enable_bitmapscan"off") */
207 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
210 /*+ Set (enable_indexscan"off") Set (enable_bitmapscan"off")*/
211 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
214 /*+Set ( enable_indexscan"off")Set ( enable_bitmapscan"off")*/
215 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
218 /*+Set(enable_indexscan"off" ) Set(enable_bitmapscan"off" ) */
219 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
222 /*+Set( enable_indexscan "off" )Set( enable_bitmapscan "off" )*/
223 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
226 /*+ Set ( enable_indexscan "off" ) Set ( enable_bitmapscan "off" ) */
227 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
231 Set(enable_indexscan"off")Set(enable_bitmapscan"off")*/
232 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
235 /*+Set(enable_indexscan"off")Set(enable_bitmapscan"off")
237 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
242 (enable_indexscan"off")
244 (enable_bitmapscan"off")*/
245 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
250 enable_indexscan"off")Set
252 enable_bitmapscan"off")*/
253 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
256 /*+Set(enable_indexscan"off"
258 Set(enable_bitmapscan"off"
261 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
271 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
286 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
290 Set(enable_indexscan"off")Set(enable_bitmapscan"off")*/
291 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
294 /*+Set(enable_indexscan"off")Set(enable_bitmapscan"off")
296 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
301 (enable_indexscan"off")
303 (enable_bitmapscan"off")*/
304 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
309 enable_indexscan"off")Set
311 enable_bitmapscan"off")*/
312 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
315 /*+Set(enable_indexscan"off"
317 Set(enable_bitmapscan"off"
320 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
330 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
345 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
348 ---- No. A-7-3 hint object pattern
349 ---- No. A-9-2 message object pattern
355 EXPLAIN (COSTS false) SELECT * FROM s1.t1 t WHERE t.c1 = 1;
357 EXPLAIN (COSTS false) SELECT * FROM s1.t1 ttt WHERE ttt.c1 = 1;
359 EXPLAIN (COSTS false) SELECT * FROM s1.t1 t WHERE t.c1 = 1;
361 EXPLAIN (COSTS false) SELECT * FROM s1.t1 ttt WHERE ttt.c1 = 1;
366 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "T" WHERE "T".c1 = 1;
368 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "TTT" WHERE "TTT".c1 = 1;
370 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "T" WHERE "T".c1 = 1;
372 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "TTT" WHERE "TTT".c1 = 1;
377 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "(" WHERE "(".c1 = 1;
379 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "(" WHERE "(".c1 = 1;
384 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;
393 EXPLAIN (COSTS false) SELECT * FROM s1.t1 """" WHERE """".c1 = 1;
395 EXPLAIN (COSTS false) SELECT * FROM s1.t1 """" WHERE """".c1 = 1;
396 /*+SeqScan("""""""")*/
397 EXPLAIN (COSTS false) SELECT * FROM s1.t1 """""""" WHERE """""""".c1 = 1;
402 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;
411 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;
421 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "
426 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "
433 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "
444 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "Set" WHERE "Set".c1 = 1;
446 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "Set" WHERE "Set".c1 = 1;
447 /*+SeqScan("Set SeqScan Leading")*/
448 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "Set SeqScan Leading" WHERE "Set SeqScan Leading".c1 = 1;
453 EXPLAIN (COSTS false) SELECT * FROM s1.t1 あ WHERE あ.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;
464 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "/**/" WHERE "/**/".c1 = 1;
465 /*+SeqScan(/**//**//**/)*/
466 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "/**//**//**/" WHERE "/**//**//**/".c1 = 1;
472 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "tT()""
473 Set/**/あ" WHERE "tT()""
479 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "tT()""
484 ---- No. A-7-4 hint parse error
488 /*+Set(enable_indexscan off)Set enable_tidscan off)Set(enable_bitmapscan off)SeqScan(t1)*/
489 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
492 /*+Set(enable_indexscan off)Set(enable_tidscan off Set(enable_bitmapscan off)SeqScan(t1)*/
493 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
496 /*+Set(enable_indexscan off)Set(enable_tidscan "off)Set(enable_bitmapscan off)SeqScan(t1)*/
497 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
500 /*+Set(enable_indexscan off)SeqScan("")Set(enable_bitmapscan off)*/
501 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
504 /*+Set(enable_indexscan off)NoSet(enable_tidscan off)Set(enable_bitmapscan off)SeqScan(t1)*/
505 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
508 /*+Set(enable_indexscan off)"Set"(enable_tidscan off)Set(enable_bitmapscan off)SeqScan(t1)*/
509 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
512 /*+Set(enable_indexscan off)Set(enable_tidscan /* value */off)Set(enable_bitmapscan off)SeqScan(t1)*/
513 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
516 ---- No. A-8-1 original GUC parameter
521 SET pg_hint_plan.debug_print TO off;
522 SHOW pg_hint_plan.enable_hint;
523 SHOW pg_hint_plan.debug_print;
524 SHOW pg_hint_plan.parse_messages;
525 SET pg_hint_plan.enable_hint TO off;
526 SET pg_hint_plan.debug_print TO on;
527 SET pg_hint_plan.parse_messages TO error;
528 SHOW pg_hint_plan.enable_hint;
529 SHOW pg_hint_plan.debug_print;
530 SHOW pg_hint_plan.parse_messages;
531 RESET pg_hint_plan.enable_hint;
532 RESET pg_hint_plan.debug_print;
533 RESET pg_hint_plan.parse_messages;
534 SHOW pg_hint_plan.enable_hint;
535 SHOW pg_hint_plan.debug_print;
536 SHOW pg_hint_plan.parse_messages;
539 SET ROLE normal_user;
540 SHOW pg_hint_plan.enable_hint;
541 SHOW pg_hint_plan.debug_print;
542 SHOW pg_hint_plan.parse_messages;
543 SET pg_hint_plan.enable_hint TO off;
544 SET pg_hint_plan.debug_print TO on;
545 SET pg_hint_plan.parse_messages TO error;
546 SHOW pg_hint_plan.enable_hint;
547 SHOW pg_hint_plan.debug_print;
548 SHOW pg_hint_plan.parse_messages;
549 RESET pg_hint_plan.enable_hint;
550 RESET pg_hint_plan.debug_print;
551 RESET pg_hint_plan.parse_messages;
552 SHOW pg_hint_plan.enable_hint;
553 SHOW pg_hint_plan.debug_print;
554 SHOW pg_hint_plan.parse_messages;
559 ---- No. A-8-2 original GUC parameter pg_hint_plan.enable_hint
563 SET pg_hint_plan.enable_hint TO on;
564 SHOW pg_hint_plan.enable_hint;
565 /*+Set(enable_indexscan off)*/
566 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
569 SET pg_hint_plan.enable_hint TO off;
570 SHOW pg_hint_plan.enable_hint;
571 /*+Set(enable_indexscan off)*/
572 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
575 SET pg_hint_plan.enable_hint TO DEFAULT;
576 SHOW pg_hint_plan.enable_hint;
577 /*+Set(enable_indexscan off)*/
578 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
581 SET pg_hint_plan.enable_hint TO enable;
582 SHOW pg_hint_plan.enable_hint;
585 ---- No. A-8-3 original GUC parameter pg_hint_plan.debug_print
589 SET pg_hint_plan.debug_print TO on;
590 SHOW pg_hint_plan.debug_print;
591 /*+Set(enable_indexscan off)*/
592 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
595 SET pg_hint_plan.debug_print TO off;
596 SHOW pg_hint_plan.debug_print;
597 /*+Set(enable_indexscan off)*/
598 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
601 SET pg_hint_plan.debug_print TO DEFAULT;
602 SHOW pg_hint_plan.debug_print;
603 /*+Set(enable_indexscan off)*/
604 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
607 SET pg_hint_plan.debug_print TO enable;
608 SHOW pg_hint_plan.debug_print;
611 ---- No. A-8-4 original GUC parameter pg_hint_plan.parse_messages
614 SET client_min_messages TO debug5;
617 SET pg_hint_plan.parse_messages TO debug5;
618 SHOW pg_hint_plan.parse_messages;
620 SET client_min_messages TO debug4;
624 SET pg_hint_plan.parse_messages TO debug4;
625 SHOW pg_hint_plan.parse_messages;
627 SET client_min_messages TO debug3;
631 SET pg_hint_plan.parse_messages TO debug3;
632 SHOW pg_hint_plan.parse_messages;
634 SET client_min_messages TO debug2;
638 SET pg_hint_plan.parse_messages TO debug2;
639 SHOW pg_hint_plan.parse_messages;
641 SET client_min_messages TO debug1;
645 SET pg_hint_plan.parse_messages TO debug1;
646 SHOW pg_hint_plan.parse_messages;
648 SET client_min_messages TO log;
652 SET pg_hint_plan.parse_messages TO log;
653 SHOW pg_hint_plan.parse_messages;
655 SET client_min_messages TO info;
659 SET pg_hint_plan.parse_messages TO info;
660 SHOW pg_hint_plan.parse_messages;
662 SET client_min_messages TO notice;
666 SET pg_hint_plan.parse_messages TO notice;
667 SHOW pg_hint_plan.parse_messages;
669 SET client_min_messages TO warning;
673 SET pg_hint_plan.parse_messages TO warning;
674 SHOW pg_hint_plan.parse_messages;
676 SET client_min_messages TO error;
680 SET pg_hint_plan.parse_messages TO error;
681 SHOW pg_hint_plan.parse_messages;
683 SET client_min_messages TO fatal;
687 RESET client_min_messages;
688 SET pg_hint_plan.parse_messages TO DEFAULT;
689 SHOW pg_hint_plan.parse_messages;
693 SET pg_hint_plan.parse_messages TO fatal;
694 SHOW pg_hint_plan.parse_messages;
697 SET pg_hint_plan.parse_messages TO panic;
698 SHOW pg_hint_plan.parse_messages;
701 SET pg_hint_plan.parse_messages TO on;
702 SHOW pg_hint_plan.parse_messages;
705 ---- No. A-8-5 original GUC parameter pg_hint_plan.enable_hint_table
708 INSERT INTO hint_plan.hints VALUES (
709 'EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = ?;',
714 SET pg_hint_plan.enable_hint_table TO on;
715 SHOW pg_hint_plan.enable_hint_table;
716 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
719 SET pg_hint_plan.enable_hint_table TO off;
720 SHOW pg_hint_plan.enable_hint_table;
721 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
724 SET pg_hint_plan.enable_hint_table TO DEFAULT;
725 SHOW pg_hint_plan.enable_hint_table;
726 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
729 SET pg_hint_plan.enable_hint_table TO enable;
730 SHOW pg_hint_plan.enable_hint_table;
732 TRUNCATE hint_plan.hints;
735 ---- No. A-9-1 parse error message output
739 /*+"Set"(enable_indexscan on)*/SELECT 1;
740 /*+Set()(enable_indexscan on)*/SELECT 1;
741 /*+Set(enable_indexscan on*/SELECT 1;
744 ---- No. A-9-3 hint state output
747 SET pg_hint_plan.debug_print TO on;
748 SET client_min_messages TO LOG;
752 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
755 /*+SeqScan(no_table)*/
756 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
759 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
760 /*+TidScan(t1)BitmapScan(t1)*/
761 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
762 /*+TidScan(t1)BitmapScan(t1)IndexScan(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)SeqScan(t1)*/
765 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
768 /*+Set(enable_indexscan enable)*/
769 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
772 ---- No. A-10-1 hint state output
775 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
776 EXPLAIN (COSTS false) EXECUTE p1;
779 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
780 EXPLAIN (COSTS false) EXECUTE p1 (1000);
781 EXPLAIN (COSTS false) EXECUTE p1 (1000);
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);
791 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
793 EXPLAIN (COSTS false) EXECUTE p1;
794 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
796 EXPLAIN (COSTS false) EXECUTE p1;
800 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
802 EXPLAIN (COSTS false) EXECUTE p1 (1000);
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);
813 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
815 EXPLAIN (COSTS false) EXECUTE p1 (1000);
821 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
822 EXPLAIN (COSTS false) EXECUTE p1;
823 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
824 EXPLAIN (COSTS false) EXECUTE p1;
828 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
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 EXPLAIN (COSTS false) EXECUTE p1 (1000);
834 EXPLAIN (COSTS false) EXECUTE p1 (1000);
835 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
836 EXPLAIN (COSTS false) EXECUTE p1 (1000);
841 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
843 EXPLAIN (COSTS false) EXECUTE p1;
844 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
846 EXPLAIN (COSTS false) EXECUTE p1;
849 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
851 EXPLAIN (COSTS false) EXECUTE p1 (1000);
852 EXPLAIN (COSTS false) EXECUTE p1 (1000);
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 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
859 EXPLAIN (COSTS false) EXECUTE p1 (1000);
865 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
867 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
868 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
870 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
874 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
876 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
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);
887 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
889 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
895 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
896 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
897 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
898 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
902 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
903 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
904 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
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 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
910 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
915 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
917 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
918 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
920 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
923 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
925 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
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);
936 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
938 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
942 ---- No. A-10-4 EXECUTE statement name error
947 SHOW pg_hint_plan.debug_print;
950 ---- No. A-11-5 EXECUTE statement name error
954 SELECT pg_stat_statements_reset();
955 SELECT * FROM s1.t1 WHERE t1.c1 = 1;
956 /*+Set(enable_seqscan off)*/ SELECT * FROM s1.t1 WHERE t1.c1 = 1;
957 /*+SeqScan(t1)*/ SELECT * FROM s1.t1 WHERE t1.c1 = 1;
958 SELECT s.query, s.calls
959 FROM public.pg_stat_statements s
960 JOIN pg_catalog.pg_database d
965 ---- No. A-12-1 reset of global variable of core at the error
966 ---- No. A-12-2 reset of global variable of original at the error
969 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
970 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)NestLoop(t1 t1)*/
971 PREPARE p1 AS SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
972 EXPLAIN (COSTS false) EXECUTE p1;
976 SELECT name, setting FROM settings;
977 SET pg_hint_plan.parse_messages TO error;
978 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)NestLoop(t1 t1)*/
979 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
980 SELECT name, setting FROM settings;
981 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)*/
982 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
986 SELECT name, setting FROM settings;
987 SET pg_hint_plan.parse_messages TO error;
988 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)NestLoop(t1 t1)*/
989 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
990 SELECT name, setting FROM settings;
991 EXPLAIN (COSTS false) EXECUTE p1;
995 SELECT name, setting FROM settings;
996 SET pg_hint_plan.parse_messages TO error;
997 EXPLAIN (COSTS false) EXECUTE p2;
998 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)*/
999 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1000 EXPLAIN (COSTS false) EXECUTE p1;
1001 SELECT name, setting FROM settings;
1005 SELECT name, setting FROM settings;
1006 SET pg_hint_plan.parse_messages TO error;
1007 EXPLAIN (COSTS false) EXECUTE p2;
1008 EXPLAIN (COSTS false) EXECUTE p1;
1009 SELECT name, setting FROM settings;
1012 SET pg_hint_plan.parse_messages TO LOG;
1015 ---- No. A-12-3 effective range of the hint
1018 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;
1027 SELECT name, setting FROM settings;
1028 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1031 SET enable_indexscan TO off;
1032 SET enable_mergejoin TO off;
1033 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1034 SELECT name, setting FROM settings;
1036 /*+Set(enable_indexscan on)Set(geqo_threshold 100)IndexScan(t2)MergeJoin(t1 t2)Leading(t2 t1)*/
1037 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1040 SELECT name, setting FROM settings;
1041 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1045 SET enable_indexscan TO off;
1046 SET enable_mergejoin TO off;
1047 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1048 SELECT name, setting FROM settings;
1049 /*+Set(enable_indexscan on)Set(geqo_threshold 100)IndexScan(t2)MergeJoin(t1 t2)Leading(t2 t1)*/
1050 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1052 SET enable_indexscan TO off;
1053 SET enable_mergejoin TO off;
1054 LOAD 'pg_hint_plan';
1055 SELECT name, setting FROM settings;
1056 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1058 SET pg_hint_plan.enable_hint TO on;
1059 SET pg_hint_plan.debug_print TO on;
1060 SET client_min_messages TO LOG;
1061 SET search_path TO public;
1062 RESET enable_indexscan;
1063 RESET enable_mergejoin;
1066 ---- No. A-13 call planner recursively
1069 CREATE OR REPLACE FUNCTION nested_planner(cnt int) RETURNS int AS $$
1073 RAISE NOTICE 'nested_planner(%)', cnt;
1080 EXECUTE '/*+ IndexScan(t_1) */'
1081 ' SELECT nested_planner($1) FROM s1.t1 t_1'
1082 ' JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)'
1083 ' ORDER BY t_1.c1 LIMIT 1'
1084 INTO new_cnt USING cnt - 1;
1088 $$ LANGUAGE plpgsql IMMUTABLE;
1091 ---- No. A-13-2 use hint of main query
1095 EXPLAIN (COSTS false) SELECT nested_planner(1) FROM s1.t1 t_1 ORDER BY t_1.c1;
1097 EXPLAIN (COSTS false) SELECT nested_planner(1) FROM s1.t1 t_1 ORDER BY t_1.c1;
1100 ---- No. A-13-3 output number of times of debugging log
1104 EXPLAIN (COSTS false) SELECT nested_planner(1) FROM s1.t1 t_1 ORDER BY t_1.c1;
1106 EXPLAIN (COSTS false) SELECT nested_planner(1) FROM s1.t1 t_1 ORDER BY t_1.c1;
1109 EXPLAIN (COSTS false) SELECT nested_planner(2) 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;
1114 EXPLAIN (COSTS false) SELECT nested_planner(5) 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;
1119 ---- No. A-13-4 output of debugging log on hint status
1123 /*+HashJoin(t_1 t_2)*/
1124 EXPLAIN (COSTS false)
1125 SELECT nested_planner(2) FROM s1.t1 t_1
1126 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
1130 /*+HashJoin(st_1 st_2)*/
1131 EXPLAIN (COSTS false)
1132 SELECT nested_planner(2) FROM s1.t1 st_1
1133 JOIN s1.t2 st_2 ON (st_1.c1 = st_2.c1)
1137 /*+HashJoin(t_1 t_2)*/
1138 EXPLAIN (COSTS false)
1139 SELECT nested_planner(2) FROM s1.t1 st_1
1140 JOIN s1.t2 st_2 ON (st_1.c1 = st_2.c1)
1144 /*+HashJoin(st_1 st_2)*/
1145 EXPLAIN (COSTS false)
1146 SELECT nested_planner(2) FROM s1.t1 t_1
1147 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
1151 /*+HashJoin(t_1 t_1)*/
1152 EXPLAIN (COSTS false)
1153 SELECT nested_planner(2) FROM s1.t1 t_1
1157 CREATE OR REPLACE FUNCTION nested_planner_one_t(cnt int) RETURNS int AS $$
1161 RAISE NOTICE 'nested_planner_one_t(%)', cnt;
1167 EXECUTE '/*+ IndexScan(t_1) */'
1168 ' SELECT nested_planner_one_t($1) FROM s1.t1 t_1'
1169 ' ORDER BY t_1.c1 LIMIT 1'
1170 INTO new_cnt USING cnt - 1;
1174 $$ LANGUAGE plpgsql IMMUTABLE;
1176 EXPLAIN (COSTS false)
1177 SELECT nested_planner_one_t(2) FROM s1.t1 t_1
1178 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
1180 /*+HashJoin(t_1 t_1)*/
1181 EXPLAIN (COSTS false)
1182 SELECT nested_planner_one_t(2) FROM s1.t1 t_1
1183 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
1186 DROP FUNCTION nested_planner_one_t(int);
1189 /*+HashJoin(t_1 t_1)*/
1190 EXPLAIN (COSTS false)
1191 SELECT nested_planner(2) FROM s1.t1 t_1
1192 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
1196 /*+MergeJoin(t_1 t_2)HashJoin(t_1 t_2)*/
1197 EXPLAIN (COSTS false)
1198 SELECT nested_planner(2) FROM s1.t1 t_1
1199 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)