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;
381 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "(" WHERE "(".c1 = 1;
383 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;
392 EXPLAIN (COSTS false) SELECT * FROM s1.t1 ")))" WHERE ")))".c1 = 1;
397 EXPLAIN (COSTS false) SELECT * FROM s1.t1 """" WHERE """".c1 = 1;
399 EXPLAIN (COSTS false) SELECT * FROM s1.t1 """" WHERE """".c1 = 1;
400 /*+SeqScan("""""""")*/
401 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;
410 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;
419 EXPLAIN (COSTS false) SELECT * FROM s1.t1 " " WHERE " ".c1 = 1;
425 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "
430 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "
437 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "
448 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "Set" WHERE "Set".c1 = 1;
450 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "Set" WHERE "Set".c1 = 1;
451 /*+SeqScan("Set SeqScan Leading")*/
452 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "Set SeqScan Leading" WHERE "Set SeqScan Leading".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;
463 EXPLAIN (COSTS false) SELECT * FROM s1.t1 あいう WHERE あいう.c1 = 1;
468 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "/**/" WHERE "/**/".c1 = 1;
469 /*+SeqScan(/**//**//**/)*/
470 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "/**//**//**/" WHERE "/**//**//**/".c1 = 1;
476 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "tT()""
477 Set/**/あ" WHERE "tT()""
483 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "tT()""
488 ---- No. A-7-4 hint parse error
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)Set(enable_tidscan "off)Set(enable_bitmapscan off)SeqScan(t1)*/
501 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
504 /*+Set(enable_indexscan off)SeqScan("")Set(enable_bitmapscan off)*/
505 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
508 /*+Set(enable_indexscan off)NoSet(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 off)Set(enable_bitmapscan off)SeqScan(t1)*/
513 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
516 /*+Set(enable_indexscan off)Set(enable_tidscan /* value */off)Set(enable_bitmapscan off)SeqScan(t1)*/
517 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
520 ---- No. A-8-1 original GUC parameter
525 SET pg_hint_plan.debug_print TO off;
526 SHOW pg_hint_plan.enable_hint;
527 SHOW pg_hint_plan.debug_print;
528 SHOW pg_hint_plan.parse_messages;
529 SET pg_hint_plan.enable_hint TO off;
530 SET pg_hint_plan.debug_print TO on;
531 SET pg_hint_plan.parse_messages TO error;
532 SHOW pg_hint_plan.enable_hint;
533 SHOW pg_hint_plan.debug_print;
534 SHOW pg_hint_plan.parse_messages;
535 RESET pg_hint_plan.enable_hint;
536 RESET pg_hint_plan.debug_print;
537 RESET pg_hint_plan.parse_messages;
538 SHOW pg_hint_plan.enable_hint;
539 SHOW pg_hint_plan.debug_print;
540 SHOW pg_hint_plan.parse_messages;
543 SET ROLE normal_user;
544 SHOW pg_hint_plan.enable_hint;
545 SHOW pg_hint_plan.debug_print;
546 SHOW pg_hint_plan.parse_messages;
547 SET pg_hint_plan.enable_hint TO off;
548 SET pg_hint_plan.debug_print TO on;
549 SET pg_hint_plan.parse_messages TO error;
550 SHOW pg_hint_plan.enable_hint;
551 SHOW pg_hint_plan.debug_print;
552 SHOW pg_hint_plan.parse_messages;
553 RESET pg_hint_plan.enable_hint;
554 RESET pg_hint_plan.debug_print;
555 RESET pg_hint_plan.parse_messages;
556 SHOW pg_hint_plan.enable_hint;
557 SHOW pg_hint_plan.debug_print;
558 SHOW pg_hint_plan.parse_messages;
563 ---- No. A-8-2 original GUC parameter pg_hint_plan.enable_hint
567 SET pg_hint_plan.enable_hint TO on;
568 SHOW pg_hint_plan.enable_hint;
569 /*+Set(enable_indexscan off)*/
570 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
573 SET pg_hint_plan.enable_hint TO off;
574 SHOW pg_hint_plan.enable_hint;
575 /*+Set(enable_indexscan off)*/
576 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
579 SET pg_hint_plan.enable_hint TO DEFAULT;
580 SHOW pg_hint_plan.enable_hint;
581 /*+Set(enable_indexscan off)*/
582 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
585 SET pg_hint_plan.enable_hint TO enable;
586 SHOW pg_hint_plan.enable_hint;
589 ---- No. A-8-3 original GUC parameter pg_hint_plan.debug_print
593 SET pg_hint_plan.debug_print TO on;
594 SHOW pg_hint_plan.debug_print;
595 /*+Set(enable_indexscan off)*/
596 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
599 SET pg_hint_plan.debug_print TO off;
600 SHOW pg_hint_plan.debug_print;
601 /*+Set(enable_indexscan off)*/
602 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
605 SET pg_hint_plan.debug_print TO DEFAULT;
606 SHOW pg_hint_plan.debug_print;
607 /*+Set(enable_indexscan off)*/
608 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
611 SET pg_hint_plan.debug_print TO enable;
612 SHOW pg_hint_plan.debug_print;
615 ---- No. A-8-4 original GUC parameter pg_hint_plan.parse_messages
618 SET client_min_messages TO debug5;
621 SET pg_hint_plan.parse_messages TO debug5;
622 SHOW pg_hint_plan.parse_messages;
624 SET client_min_messages TO debug4;
628 SET pg_hint_plan.parse_messages TO debug4;
629 SHOW pg_hint_plan.parse_messages;
631 SET client_min_messages TO debug3;
635 SET pg_hint_plan.parse_messages TO debug3;
636 SHOW pg_hint_plan.parse_messages;
638 SET client_min_messages TO debug2;
642 SET pg_hint_plan.parse_messages TO debug2;
643 SHOW pg_hint_plan.parse_messages;
645 SET client_min_messages TO debug1;
649 SET pg_hint_plan.parse_messages TO debug1;
650 SHOW pg_hint_plan.parse_messages;
652 SET client_min_messages TO log;
656 SET pg_hint_plan.parse_messages TO log;
657 SHOW pg_hint_plan.parse_messages;
659 SET client_min_messages TO info;
663 SET pg_hint_plan.parse_messages TO info;
664 SHOW pg_hint_plan.parse_messages;
666 SET client_min_messages TO notice;
670 SET pg_hint_plan.parse_messages TO notice;
671 SHOW pg_hint_plan.parse_messages;
673 SET client_min_messages TO warning;
677 SET pg_hint_plan.parse_messages TO warning;
678 SHOW pg_hint_plan.parse_messages;
680 SET client_min_messages TO error;
684 SET pg_hint_plan.parse_messages TO error;
685 SHOW pg_hint_plan.parse_messages;
687 SET client_min_messages TO fatal;
691 RESET client_min_messages;
692 SET pg_hint_plan.parse_messages TO DEFAULT;
693 SHOW pg_hint_plan.parse_messages;
697 SET pg_hint_plan.parse_messages TO fatal;
698 SHOW pg_hint_plan.parse_messages;
701 SET pg_hint_plan.parse_messages TO panic;
702 SHOW pg_hint_plan.parse_messages;
705 SET pg_hint_plan.parse_messages TO on;
706 SHOW pg_hint_plan.parse_messages;
709 ---- No. A-8-5 original GUC parameter pg_hint_plan.enable_hint_table
712 INSERT INTO hint_plan.hints VALUES (
713 'EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = ?;',
718 SET pg_hint_plan.enable_hint_table TO on;
719 SHOW pg_hint_plan.enable_hint_table;
720 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
723 SET pg_hint_plan.enable_hint_table TO off;
724 SHOW pg_hint_plan.enable_hint_table;
725 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
728 SET pg_hint_plan.enable_hint_table TO DEFAULT;
729 SHOW pg_hint_plan.enable_hint_table;
730 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
733 SET pg_hint_plan.enable_hint_table TO enable;
734 SHOW pg_hint_plan.enable_hint_table;
736 TRUNCATE hint_plan.hints;
739 ---- No. A-9-1 parse error message output
743 /*+"Set"(enable_indexscan on)*/SELECT 1;
744 /*+Set()(enable_indexscan on)*/SELECT 1;
745 /*+Set(enable_indexscan on*/SELECT 1;
748 ---- No. A-9-3 hint state output
751 SET pg_hint_plan.debug_print TO on;
752 SET client_min_messages TO LOG;
756 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
759 /*+SeqScan(no_table)*/
760 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
763 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
764 /*+TidScan(t1)BitmapScan(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)*/
767 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
768 /*+TidScan(t1)BitmapScan(t1)IndexScan(t1)SeqScan(t1)*/
769 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
772 /*+Set(enable_indexscan enable)*/
773 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
776 ---- No. A-10-1 hint state output
779 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
780 EXPLAIN (COSTS false) EXECUTE p1;
783 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
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);
788 EXPLAIN (COSTS false) EXECUTE p1 (1000);
789 EXPLAIN (COSTS false) EXECUTE p1 (1000);
795 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
797 EXPLAIN (COSTS false) EXECUTE p1;
798 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;
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);
816 EXPLAIN (COSTS false) EXECUTE p1 (1000);
817 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
819 EXPLAIN (COSTS false) EXECUTE p1 (1000);
825 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
826 EXPLAIN (COSTS false) EXECUTE p1;
827 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
828 EXPLAIN (COSTS false) EXECUTE p1;
832 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
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 EXPLAIN (COSTS false) EXECUTE p1 (1000);
838 EXPLAIN (COSTS false) EXECUTE p1 (1000);
839 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
840 EXPLAIN (COSTS false) EXECUTE p1 (1000);
845 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
847 EXPLAIN (COSTS false) EXECUTE p1;
848 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
850 EXPLAIN (COSTS false) EXECUTE p1;
853 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
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 EXPLAIN (COSTS false) EXECUTE p1 (1000);
860 EXPLAIN (COSTS false) EXECUTE p1 (1000);
861 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
863 EXPLAIN (COSTS false) EXECUTE p1 (1000);
869 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
871 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
872 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;
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);
890 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
891 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
893 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
899 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
900 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
901 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
902 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
906 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
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 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
912 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
913 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
914 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
919 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
921 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
922 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
924 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
927 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
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);
939 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
940 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
942 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
946 ---- No. A-10-4 EXECUTE statement name error
951 SHOW pg_hint_plan.debug_print;
954 ---- No. A-11-5 EXECUTE statement name error
958 SELECT pg_stat_statements_reset();
959 SELECT * FROM s1.t1 WHERE t1.c1 = 1;
960 /*+Set(enable_seqscan off)*/ SELECT * FROM s1.t1 WHERE t1.c1 = 1;
961 /*+SeqScan(t1)*/ SELECT * FROM s1.t1 WHERE t1.c1 = 1;
962 SELECT s.query, s.calls
963 FROM public.pg_stat_statements s
964 JOIN pg_catalog.pg_database d
969 ---- No. A-12-1 reset of global variable of core at the error
970 ---- No. A-12-2 reset of global variable of original at the error
973 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
974 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)NestLoop(t1 t1)*/
975 PREPARE p1 AS SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
976 EXPLAIN (COSTS false) EXECUTE p1;
980 SELECT name, setting FROM settings;
981 SET pg_hint_plan.parse_messages TO error;
982 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)NestLoop(t1 t1)*/
983 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
984 SELECT name, setting FROM settings;
985 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)*/
986 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
990 SELECT name, setting FROM settings;
991 SET pg_hint_plan.parse_messages TO error;
992 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)NestLoop(t1 t1)*/
993 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
994 SELECT name, setting FROM settings;
995 EXPLAIN (COSTS false) EXECUTE p1;
999 SELECT name, setting FROM settings;
1000 SET pg_hint_plan.parse_messages TO error;
1001 EXPLAIN (COSTS false) EXECUTE p2;
1002 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)*/
1003 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1004 EXPLAIN (COSTS false) EXECUTE p1;
1005 SELECT name, setting FROM settings;
1009 SELECT name, setting FROM settings;
1010 SET pg_hint_plan.parse_messages TO error;
1011 EXPLAIN (COSTS false) EXECUTE p2;
1012 EXPLAIN (COSTS false) EXECUTE p1;
1013 SELECT name, setting FROM settings;
1016 SET pg_hint_plan.parse_messages TO LOG;
1019 ---- No. A-12-3 effective range of the hint
1022 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1025 SET enable_indexscan TO off;
1026 SET enable_mergejoin TO off;
1027 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1028 SELECT name, setting FROM settings;
1029 /*+Set(enable_indexscan on)Set(geqo_threshold 100)IndexScan(t2)MergeJoin(t1 t2)Leading(t2 t1)*/
1030 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1031 SELECT name, setting FROM settings;
1032 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1035 SET enable_indexscan TO off;
1036 SET enable_mergejoin TO off;
1037 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1038 SELECT name, setting FROM settings;
1040 /*+Set(enable_indexscan on)Set(geqo_threshold 100)IndexScan(t2)MergeJoin(t1 t2)Leading(t2 t1)*/
1041 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1044 SELECT name, setting FROM settings;
1045 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1049 SET enable_indexscan TO off;
1050 SET enable_mergejoin TO off;
1051 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1052 SELECT name, setting FROM settings;
1053 /*+Set(enable_indexscan on)Set(geqo_threshold 100)IndexScan(t2)MergeJoin(t1 t2)Leading(t2 t1)*/
1054 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1056 SET enable_indexscan TO off;
1057 SET enable_mergejoin TO off;
1058 LOAD 'pg_hint_plan';
1059 SELECT name, setting FROM settings;
1060 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1062 SET pg_hint_plan.enable_hint TO on;
1063 SET pg_hint_plan.debug_print TO on;
1064 SET client_min_messages TO LOG;
1065 SET search_path TO public;
1066 RESET enable_indexscan;
1067 RESET enable_mergejoin;
1070 ---- No. A-13 call planner recursively
1073 CREATE OR REPLACE FUNCTION nested_planner(cnt int) RETURNS int AS $$
1077 RAISE NOTICE 'nested_planner(%)', cnt;
1084 EXECUTE '/*+ IndexScan(t_1) */'
1085 ' SELECT nested_planner($1) FROM s1.t1 t_1'
1086 ' JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)'
1087 ' ORDER BY t_1.c1 LIMIT 1'
1088 INTO new_cnt USING cnt - 1;
1092 $$ LANGUAGE plpgsql IMMUTABLE;
1095 ---- No. A-13-2 use hint of main query
1099 EXPLAIN (COSTS false) SELECT nested_planner(1) FROM s1.t1 t_1 ORDER BY t_1.c1;
1101 EXPLAIN (COSTS false) SELECT nested_planner(1) FROM s1.t1 t_1 ORDER BY t_1.c1;
1104 ---- No. A-13-3 output number of times of debugging log
1108 EXPLAIN (COSTS false) SELECT nested_planner(1) FROM s1.t1 t_1 ORDER BY t_1.c1;
1110 EXPLAIN (COSTS false) SELECT nested_planner(1) 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;
1115 EXPLAIN (COSTS false) SELECT nested_planner(2) 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;
1120 EXPLAIN (COSTS false) SELECT nested_planner(5) FROM s1.t1 t_1 ORDER BY t_1.c1;
1123 ---- No. A-13-4 output of debugging log on hint status
1127 /*+HashJoin(t_1 t_2)*/
1128 EXPLAIN (COSTS false)
1129 SELECT nested_planner(2) FROM s1.t1 t_1
1130 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
1134 /*+HashJoin(st_1 st_2)*/
1135 EXPLAIN (COSTS false)
1136 SELECT nested_planner(2) FROM s1.t1 st_1
1137 JOIN s1.t2 st_2 ON (st_1.c1 = st_2.c1)
1141 /*+HashJoin(t_1 t_2)*/
1142 EXPLAIN (COSTS false)
1143 SELECT nested_planner(2) FROM s1.t1 st_1
1144 JOIN s1.t2 st_2 ON (st_1.c1 = st_2.c1)
1148 /*+HashJoin(st_1 st_2)*/
1149 EXPLAIN (COSTS false)
1150 SELECT nested_planner(2) FROM s1.t1 t_1
1151 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
1155 /*+HashJoin(t_1 t_1)*/
1156 EXPLAIN (COSTS false)
1157 SELECT nested_planner(2) FROM s1.t1 t_1
1161 CREATE OR REPLACE FUNCTION nested_planner_one_t(cnt int) RETURNS int AS $$
1165 RAISE NOTICE 'nested_planner_one_t(%)', cnt;
1171 EXECUTE '/*+ IndexScan(t_1) */'
1172 ' SELECT nested_planner_one_t($1) FROM s1.t1 t_1'
1173 ' ORDER BY t_1.c1 LIMIT 1'
1174 INTO new_cnt USING cnt - 1;
1178 $$ LANGUAGE plpgsql IMMUTABLE;
1180 EXPLAIN (COSTS false)
1181 SELECT nested_planner_one_t(2) FROM s1.t1 t_1
1182 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
1184 /*+HashJoin(t_1 t_1)*/
1185 EXPLAIN (COSTS false)
1186 SELECT nested_planner_one_t(2) FROM s1.t1 t_1
1187 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
1190 DROP FUNCTION nested_planner_one_t(int);
1193 /*+HashJoin(t_1 t_1)*/
1194 EXPLAIN (COSTS false)
1195 SELECT nested_planner(2) FROM s1.t1 t_1
1196 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
1200 /*+MergeJoin(t_1 t_2)HashJoin(t_1 t_2)*/
1201 EXPLAIN (COSTS false)
1202 SELECT nested_planner(2) FROM s1.t1 t_1
1203 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)