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;
44 /* /*+SeqScan(t1)*/ */
45 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
48 ---- No. A-5-2 hint position
52 EXPLAIN (COSTS false) SELECT c1 FROM s1.t1 WHERE t1.c1 = 1;
54 EXPLAIN (COSTS false) SELECT c1 FROM s1.t1 WHERE t1.c1 = 1;
57 EXPLAIN (COSTS false) SELECT c1, c2 AS c_2 /*+SeqScan(t1)*/ FROM s1.t1 WHERE t1.c1 = 1;
60 EXPLAIN (COSTS false) SELECT c1 AS "c1"/*+SeqScan(t1)*/ FROM s1.t1 WHERE t1.c1 = 1;
63 EXPLAIN (COSTS false) SELECT * /*+SeqScan(t1)*/ FROM s1.t1 WHERE t1.c1 = 1;
66 ---- No. A-6-1 hint's table definition
69 SET pg_hint_plan.enable_hint_table TO on;
74 ---- No. A-6-2 search condition
76 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
78 INSERT INTO hint_plan.hints (norm_query_string, application_name, hints)
80 'EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = ?;',
83 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
86 INSERT INTO hint_plan.hints (norm_query_string, application_name, hints)
88 'EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = ?;',
91 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
92 TRUNCATE hint_plan.hints;
95 INSERT INTO hint_plan.hints (norm_query_string, application_name, hints)
97 'EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = ?;',
98 'dummy_application_name',
101 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
102 TRUNCATE hint_plan.hints;
105 INSERT INTO hint_plan.hints (norm_query_string, application_name, hints)
107 'EXPLAIN (COSTS false) SELECT * FROM s1.t1;',
111 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
112 TRUNCATE hint_plan.hints;
115 ---- No. A-6-3 number of constant
119 INSERT INTO hint_plan.hints (norm_query_string, application_name, hints)
121 'EXPLAIN (COSTS false) SELECT c1 FROM s1.t1;',
125 EXPLAIN (COSTS false) SELECT c1 FROM s1.t1;
126 TRUNCATE hint_plan.hints;
129 INSERT INTO hint_plan.hints (norm_query_string, application_name, hints)
131 'EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = ?;',
135 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
136 TRUNCATE hint_plan.hints;
139 INSERT INTO hint_plan.hints (norm_query_string, application_name, hints)
141 'EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = ? OR t1.c1 = ?;',
145 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 OR t1.c1 = 0;
146 TRUNCATE hint_plan.hints;
147 SET pg_hint_plan.enable_hint_table TO off;
150 ---- No. A-7-2 hint delimiter
153 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
161 /*+Set(enable_indexscan"off")Set(enable_bitmapscan"off")*/
162 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
165 /*+ Set(enable_indexscan"off")Set(enable_bitmapscan"off")*/
166 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
169 /*+Set(enable_indexscan"off")Set(enable_bitmapscan"off") */
170 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
173 /*+ Set (enable_indexscan"off") Set (enable_bitmapscan"off")*/
174 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
177 /*+Set ( enable_indexscan"off")Set ( enable_bitmapscan"off")*/
178 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
181 /*+Set(enable_indexscan"off" ) Set(enable_bitmapscan"off" ) */
182 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
185 /*+Set( enable_indexscan "off" )Set( enable_bitmapscan "off" )*/
186 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
189 /*+ Set ( enable_indexscan "off" ) Set ( enable_bitmapscan "off" ) */
190 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
193 /*+ Set(enable_indexscan"off")Set(enable_bitmapscan"off")*/
194 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
197 /*+Set(enable_indexscan"off")Set(enable_bitmapscan"off") */
198 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
201 /*+ Set (enable_indexscan"off") Set (enable_bitmapscan"off")*/
202 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
205 /*+Set ( enable_indexscan"off")Set ( enable_bitmapscan"off")*/
206 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
209 /*+Set(enable_indexscan"off" ) Set(enable_bitmapscan"off" ) */
210 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
213 /*+Set( enable_indexscan "off" )Set( enable_bitmapscan "off" )*/
214 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
217 /*+ Set ( enable_indexscan "off" ) Set ( enable_bitmapscan "off" ) */
218 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")
228 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
233 (enable_indexscan"off")
235 (enable_bitmapscan"off")*/
236 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
241 enable_indexscan"off")Set
243 enable_bitmapscan"off")*/
244 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
247 /*+Set(enable_indexscan"off"
249 Set(enable_bitmapscan"off"
252 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
262 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
277 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
281 Set(enable_indexscan"off")Set(enable_bitmapscan"off")*/
282 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
285 /*+Set(enable_indexscan"off")Set(enable_bitmapscan"off")
287 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
292 (enable_indexscan"off")
294 (enable_bitmapscan"off")*/
295 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
300 enable_indexscan"off")Set
302 enable_bitmapscan"off")*/
303 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
306 /*+Set(enable_indexscan"off"
308 Set(enable_bitmapscan"off"
311 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
321 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
336 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
339 ---- No. A-7-3 hint object pattern
340 ---- No. A-9-2 message object pattern
346 EXPLAIN (COSTS false) SELECT * FROM s1.t1 t WHERE t.c1 = 1;
348 EXPLAIN (COSTS false) SELECT * FROM s1.t1 ttt WHERE ttt.c1 = 1;
350 EXPLAIN (COSTS false) SELECT * FROM s1.t1 t WHERE t.c1 = 1;
352 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;
361 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "T" WHERE "T".c1 = 1;
363 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "TTT" WHERE "TTT".c1 = 1;
368 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "(" WHERE "(".c1 = 1;
370 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "(" WHERE "(".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;
384 EXPLAIN (COSTS false) SELECT * FROM s1.t1 """" WHERE """".c1 = 1;
386 EXPLAIN (COSTS false) SELECT * FROM s1.t1 """" WHERE """".c1 = 1;
387 /*+SeqScan("""""""")*/
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;
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;
412 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "
417 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "
424 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "
435 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "Set" WHERE "Set".c1 = 1;
437 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "Set" WHERE "Set".c1 = 1;
438 /*+SeqScan("Set SeqScan Leading")*/
439 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "Set SeqScan Leading" WHERE "Set SeqScan Leading".c1 = 1;
444 EXPLAIN (COSTS false) SELECT * FROM s1.t1 あ WHERE あ.c1 = 1;
446 EXPLAIN (COSTS false) SELECT * FROM s1.t1 あいう WHERE あいう.c1 = 1;
448 EXPLAIN (COSTS false) SELECT * FROM s1.t1 あ WHERE あ.c1 = 1;
450 EXPLAIN (COSTS false) SELECT * FROM s1.t1 あいう WHERE あいう.c1 = 1;
455 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "/**/" WHERE "/**/".c1 = 1;
456 /*+SeqScan(/**//**//**/)*/
457 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "/**//**//**/" WHERE "/**//**//**/".c1 = 1;
463 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "tT()""
464 Set/**/あ" WHERE "tT()""
470 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "tT()""
476 /*+SeqScan(a123456789b123456789c123456789d123456789e123456789f123)*/
477 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "123456789012345678901234567890123456789012345678901234" WHERE "123456789012345678901234567890123456789012345678901234".c1 = 1;
480 ---- No. A-7-4 hint parse error
484 /*+Set(enable_indexscan off)Set enable_tidscan off)Set(enable_bitmapscan off)SeqScan(t1)*/
485 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
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)SeqScan("")Set(enable_bitmapscan off)*/
497 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
500 /*+Set(enable_indexscan off)NoSet(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)"Set"(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 /* value */off)Set(enable_bitmapscan off)SeqScan(t1)*/
509 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
512 ---- No. A-8-1 original GUC parameter
517 SET pg_hint_plan.debug_print TO off;
518 SHOW pg_hint_plan.enable_hint;
519 SHOW pg_hint_plan.debug_print;
520 SHOW pg_hint_plan.parse_messages;
521 SET pg_hint_plan.enable_hint TO off;
522 SET pg_hint_plan.debug_print TO on;
523 SET pg_hint_plan.parse_messages TO error;
524 SHOW pg_hint_plan.enable_hint;
525 SHOW pg_hint_plan.debug_print;
526 SHOW pg_hint_plan.parse_messages;
527 RESET pg_hint_plan.enable_hint;
528 RESET pg_hint_plan.debug_print;
529 RESET pg_hint_plan.parse_messages;
530 SHOW pg_hint_plan.enable_hint;
531 SHOW pg_hint_plan.debug_print;
532 SHOW pg_hint_plan.parse_messages;
535 SET ROLE normal_user;
536 SHOW pg_hint_plan.enable_hint;
537 SHOW pg_hint_plan.debug_print;
538 SHOW pg_hint_plan.parse_messages;
539 SET pg_hint_plan.enable_hint TO off;
540 SET pg_hint_plan.debug_print TO on;
541 SET pg_hint_plan.parse_messages TO error;
542 SHOW pg_hint_plan.enable_hint;
543 SHOW pg_hint_plan.debug_print;
544 SHOW pg_hint_plan.parse_messages;
545 RESET pg_hint_plan.enable_hint;
546 RESET pg_hint_plan.debug_print;
547 RESET pg_hint_plan.parse_messages;
548 SHOW pg_hint_plan.enable_hint;
549 SHOW pg_hint_plan.debug_print;
550 SHOW pg_hint_plan.parse_messages;
555 ---- No. A-8-2 original GUC parameter pg_hint_plan.enable_hint
559 SET pg_hint_plan.enable_hint TO on;
560 SHOW pg_hint_plan.enable_hint;
561 /*+Set(enable_indexscan off)*/
562 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
565 SET pg_hint_plan.enable_hint TO off;
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 DEFAULT;
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 enable;
578 SHOW pg_hint_plan.enable_hint;
581 ---- No. A-8-3 original GUC parameter pg_hint_plan.debug_print
585 SET pg_hint_plan.debug_print TO on;
586 SHOW pg_hint_plan.debug_print;
587 /*+Set(enable_indexscan off)*/
588 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
591 SET pg_hint_plan.debug_print TO off;
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 DEFAULT;
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 enable;
604 SHOW pg_hint_plan.debug_print;
607 ---- No. A-8-4 original GUC parameter pg_hint_plan.parse_messages
610 SET client_min_messages TO debug5;
613 SET pg_hint_plan.parse_messages TO debug5;
614 SHOW pg_hint_plan.parse_messages;
616 SET client_min_messages TO debug4;
620 SET pg_hint_plan.parse_messages TO debug4;
621 SHOW pg_hint_plan.parse_messages;
623 SET client_min_messages TO debug3;
627 SET pg_hint_plan.parse_messages TO debug3;
628 SHOW pg_hint_plan.parse_messages;
630 SET client_min_messages TO debug2;
634 SET pg_hint_plan.parse_messages TO debug2;
635 SHOW pg_hint_plan.parse_messages;
637 SET client_min_messages TO debug1;
641 SET pg_hint_plan.parse_messages TO debug1;
642 SHOW pg_hint_plan.parse_messages;
644 SET client_min_messages TO log;
648 SET pg_hint_plan.parse_messages TO log;
649 SHOW pg_hint_plan.parse_messages;
651 SET client_min_messages TO info;
655 SET pg_hint_plan.parse_messages TO info;
656 SHOW pg_hint_plan.parse_messages;
658 SET client_min_messages TO notice;
662 SET pg_hint_plan.parse_messages TO notice;
663 SHOW pg_hint_plan.parse_messages;
665 SET client_min_messages TO warning;
669 SET pg_hint_plan.parse_messages TO warning;
670 SHOW pg_hint_plan.parse_messages;
672 SET client_min_messages TO error;
676 SET pg_hint_plan.parse_messages TO error;
677 SHOW pg_hint_plan.parse_messages;
679 SET client_min_messages TO fatal;
683 RESET client_min_messages;
684 SET pg_hint_plan.parse_messages TO DEFAULT;
685 SHOW pg_hint_plan.parse_messages;
689 SET pg_hint_plan.parse_messages TO fatal;
690 SHOW pg_hint_plan.parse_messages;
693 SET pg_hint_plan.parse_messages TO panic;
694 SHOW pg_hint_plan.parse_messages;
697 SET pg_hint_plan.parse_messages TO on;
698 SHOW pg_hint_plan.parse_messages;
701 ---- No. A-8-5 original GUC parameter pg_hint_plan.enable_hint_table
704 INSERT INTO hint_plan.hints (norm_query_string, application_name, hints)
706 'EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = ?;',
711 SET pg_hint_plan.enable_hint_table TO on;
712 SHOW pg_hint_plan.enable_hint_table;
713 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
716 SET pg_hint_plan.enable_hint_table TO off;
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 DEFAULT;
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 enable;
727 SHOW pg_hint_plan.enable_hint_table;
729 TRUNCATE hint_plan.hints;
732 ---- No. A-9-1 parse error message output
736 /*+"Set"(enable_indexscan on)*/SELECT 1;
737 /*+Set()(enable_indexscan on)*/SELECT 1;
738 /*+Set(enable_indexscan on*/SELECT 1;
741 ---- No. A-9-3 hint state output
744 SET pg_hint_plan.debug_print TO on;
745 SET client_min_messages TO LOG;
749 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
752 /*+SeqScan(no_table)*/
753 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
756 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
757 /*+TidScan(t1)BitmapScan(t1)*/
758 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
759 /*+TidScan(t1)BitmapScan(t1)IndexScan(t1)*/
760 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
761 /*+TidScan(t1)BitmapScan(t1)IndexScan(t1)SeqScan(t1)*/
762 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
765 /*+Set(enable_indexscan enable)*/
766 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
769 ---- No. A-10-1 hint state output
772 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
773 EXPLAIN (COSTS false) EXECUTE p1;
776 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
777 EXPLAIN (COSTS false) EXECUTE p1 (1000);
778 EXPLAIN (COSTS false) EXECUTE p1 (1000);
779 EXPLAIN (COSTS false) EXECUTE p1 (1000);
780 EXPLAIN (COSTS false) EXECUTE p1 (1000);
781 EXPLAIN (COSTS false) EXECUTE p1 (1000);
782 EXPLAIN (COSTS false) EXECUTE p1 (1000);
788 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
790 EXPLAIN (COSTS false) EXECUTE p1;
791 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
793 EXPLAIN (COSTS false) EXECUTE p1;
797 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
799 EXPLAIN (COSTS false) EXECUTE p1 (1000);
801 EXPLAIN (COSTS false) EXECUTE p1 (1000);
803 EXPLAIN (COSTS false) EXECUTE p1 (1000);
805 EXPLAIN (COSTS false) EXECUTE p1 (1000);
807 EXPLAIN (COSTS false) EXECUTE p1 (1000);
809 EXPLAIN (COSTS false) EXECUTE p1 (1000);
810 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
812 EXPLAIN (COSTS false) EXECUTE p1 (1000);
818 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';
821 EXPLAIN (COSTS false) EXECUTE p1;
825 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
826 EXPLAIN (COSTS false) EXECUTE p1 (1000);
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 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
833 EXPLAIN (COSTS false) EXECUTE p1 (1000);
838 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
840 EXPLAIN (COSTS false) EXECUTE p1;
841 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
843 EXPLAIN (COSTS false) EXECUTE p1;
846 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
848 EXPLAIN (COSTS false) EXECUTE p1 (1000);
849 EXPLAIN (COSTS false) EXECUTE p1 (1000);
850 EXPLAIN (COSTS false) EXECUTE p1 (1000);
851 EXPLAIN (COSTS false) EXECUTE p1 (1000);
852 EXPLAIN (COSTS false) EXECUTE p1 (1000);
853 EXPLAIN (COSTS false) EXECUTE p1 (1000);
854 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
856 EXPLAIN (COSTS false) EXECUTE p1 (1000);
862 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
864 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
865 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
867 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
871 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
873 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
875 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
877 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
879 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
881 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
883 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
884 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
886 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
892 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';
895 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
899 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
900 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
901 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
902 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
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 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
907 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
912 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
914 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
915 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
917 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
920 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
922 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
924 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
926 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
928 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
930 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
932 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
933 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
935 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
939 ---- No. A-10-4 EXECUTE statement name error
944 SHOW pg_hint_plan.debug_print;
947 ---- No. A-11-5 EXECUTE statement name error
951 SELECT pg_stat_statements_reset();
952 SELECT * FROM s1.t1 WHERE t1.c1 = 1;
953 /*+Set(enable_seqscan off)*/ SELECT * FROM s1.t1 WHERE t1.c1 = 1;
954 /*+SeqScan(t1)*/ SELECT * FROM s1.t1 WHERE t1.c1 = 1;
955 SELECT s.query, s.calls
956 FROM public.pg_stat_statements s
957 JOIN pg_catalog.pg_database d
962 ---- No. A-12-1 reset of global variable of core at the error
963 ---- No. A-12-2 reset of global variable of original at the error
966 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
967 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)NestLoop(t1 t1)*/
968 PREPARE p1 AS SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
969 EXPLAIN (COSTS false) EXECUTE p1;
973 SELECT name, setting FROM settings;
974 SET pg_hint_plan.parse_messages TO error;
975 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)NestLoop(t1 t1)*/
976 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
977 SELECT name, setting FROM settings;
978 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)*/
979 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
983 SELECT name, setting FROM settings;
984 SET pg_hint_plan.parse_messages TO error;
985 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)NestLoop(t1 t1)*/
986 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
987 SELECT name, setting FROM settings;
988 EXPLAIN (COSTS false) EXECUTE p1;
992 SELECT name, setting FROM settings;
993 SET pg_hint_plan.parse_messages TO error;
994 EXPLAIN (COSTS false) EXECUTE p2;
995 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)*/
996 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
997 EXPLAIN (COSTS false) EXECUTE p1;
998 SELECT name, setting FROM settings;
1002 SELECT name, setting FROM settings;
1003 SET pg_hint_plan.parse_messages TO error;
1004 EXPLAIN (COSTS false) EXECUTE p2;
1005 EXPLAIN (COSTS false) EXECUTE p1;
1006 SELECT name, setting FROM settings;
1009 SET pg_hint_plan.parse_messages TO LOG;
1012 ---- No. A-12-3 effective range of the hint
1015 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1018 SET enable_indexscan TO off;
1019 SET enable_mergejoin TO off;
1020 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1021 SELECT name, setting FROM settings;
1022 /*+Set(enable_indexscan on)Set(geqo_threshold 100)IndexScan(t2)MergeJoin(t1 t2)Leading(t2 t1)*/
1023 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1024 SELECT name, setting FROM settings;
1025 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 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1031 SELECT name, setting FROM settings;
1033 /*+Set(enable_indexscan on)Set(geqo_threshold 100)IndexScan(t2)MergeJoin(t1 t2)Leading(t2 t1)*/
1034 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1037 SELECT name, setting FROM settings;
1038 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1042 SET enable_indexscan TO off;
1043 SET enable_mergejoin TO off;
1044 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1045 SELECT name, setting FROM settings;
1046 /*+Set(enable_indexscan on)Set(geqo_threshold 100)IndexScan(t2)MergeJoin(t1 t2)Leading(t2 t1)*/
1047 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 LOAD 'pg_hint_plan';
1052 SELECT name, setting FROM settings;
1053 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1055 SET pg_hint_plan.enable_hint TO on;
1056 SET pg_hint_plan.debug_print TO on;
1057 SET client_min_messages TO LOG;
1058 SET search_path TO public;
1059 RESET enable_indexscan;
1060 RESET enable_mergejoin;
1063 ---- No. A-13 call planner recursively
1066 CREATE OR REPLACE FUNCTION nested_planner(cnt int) RETURNS int AS $$
1070 RAISE NOTICE 'nested_planner(%)', cnt;
1077 SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1) INTO new_cnt
1079 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
1080 ORDER BY t_1.c1 LIMIT 1;
1084 $$ LANGUAGE plpgsql IMMUTABLE;
1087 ---- No. A-13-2 use hint of main query
1091 EXPLAIN (COSTS false) SELECT nested_planner(1) FROM s1.t1 t_1 ORDER BY t_1.c1;
1093 EXPLAIN (COSTS false) SELECT nested_planner(1) FROM s1.t1 t_1 ORDER BY t_1.c1;
1096 ---- No. A-13-3 output number of times of debugging log
1100 EXPLAIN (COSTS false) SELECT nested_planner(1) FROM s1.t1 t_1 ORDER BY t_1.c1;
1102 EXPLAIN (COSTS false) SELECT nested_planner(1) FROM s1.t1 t_1 ORDER BY t_1.c1;
1105 EXPLAIN (COSTS false) SELECT nested_planner(2) FROM s1.t1 t_1 ORDER BY t_1.c1;
1107 EXPLAIN (COSTS false) SELECT nested_planner(2) FROM s1.t1 t_1 ORDER BY t_1.c1;
1110 EXPLAIN (COSTS false) SELECT nested_planner(5) FROM s1.t1 t_1 ORDER BY t_1.c1;
1112 EXPLAIN (COSTS false) SELECT nested_planner(5) FROM s1.t1 t_1 ORDER BY t_1.c1;
1115 ---- No. A-13-4 output of debugging log on hint status
1117 CREATE OR REPLACE FUNCTION recall_planner() RETURNS int AS $$
1118 SELECT /*+ IndexScan(t_1) */t_1.c1
1120 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
1121 ORDER BY t_1.c1 LIMIT 1;
1122 $$ LANGUAGE SQL IMMUTABLE;
1125 /*+HashJoin(t_1 t_2)*/
1126 EXPLAIN (COSTS false)
1127 SELECT recall_planner() 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 recall_planner() 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 recall_planner() 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 recall_planner() 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 recall_planner() FROM s1.t1 t_1
1159 CREATE OR REPLACE FUNCTION recall_planner_one_t() RETURNS int AS $$
1160 SELECT /*+ IndexScan(t_1) */t_1.c1
1162 ORDER BY t_1.c1 LIMIT 1;
1163 $$ LANGUAGE SQL IMMUTABLE;
1165 EXPLAIN (COSTS false)
1166 SELECT recall_planner_one_t() FROM s1.t1 t_1
1167 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
1169 /*+HashJoin(t_1 t_1)*/
1170 EXPLAIN (COSTS false)
1171 SELECT recall_planner_one_t() FROM s1.t1 t_1
1172 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
1175 DROP FUNCTION recall_planner_one_t(int);
1178 /*+HashJoin(t_1 t_1)*/
1179 EXPLAIN (COSTS false)
1180 SELECT recall_planner() FROM s1.t1 t_1
1181 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
1185 /*+MergeJoin(t_1 t_2)HashJoin(t_1 t_2)*/
1186 EXPLAIN (COSTS false)
1187 SELECT recall_planner() FROM s1.t1 t_1
1188 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)