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
514 ---- Don't test postgresql itself.
516 -- SET ROLE super_user;
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.debug_print TO off;
560 SET pg_hint_plan.enable_hint TO on;
561 SHOW pg_hint_plan.enable_hint;
562 /*+Set(enable_indexscan off)*/
563 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
566 SET pg_hint_plan.enable_hint TO off;
567 SHOW pg_hint_plan.enable_hint;
568 /*+Set(enable_indexscan off)*/
569 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
572 -- Don't test PostgreSQL itself.
573 -- SET pg_hint_plan.enable_hint TO DEFAULT;
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 -- Don't test PostgreSQL itself
580 -- SET pg_hint_plan.enable_hint TO enable;
581 -- SHOW pg_hint_plan.enable_hint;
584 ---- No. A-8-3 original GUC parameter pg_hint_plan.debug_print
588 SET pg_hint_plan.enable_hint TO on;
589 SHOW pg_hint_plan.enable_hint;
590 SET pg_hint_plan.debug_print TO on;
591 SHOW pg_hint_plan.debug_print;
592 /*+Set(enable_indexscan off)*/
593 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
596 SET pg_hint_plan.debug_print TO off;
597 SHOW pg_hint_plan.debug_print;
598 /*+Set(enable_indexscan off)*/
599 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
602 SET pg_hint_plan.debug_print TO DEFAULT;
603 SHOW pg_hint_plan.debug_print;
604 /*+Set(enable_indexscan off)*/
605 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
608 SET pg_hint_plan.debug_print TO enable;
609 SHOW pg_hint_plan.debug_print;
612 ---- No. A-8-4 original GUC parameter pg_hint_plan.parse_messages
615 SET client_min_messages TO debug5;
618 SET pg_hint_plan.parse_messages TO debug5;
619 SHOW pg_hint_plan.parse_messages;
621 SET client_min_messages TO debug4;
625 SET pg_hint_plan.parse_messages TO debug4;
626 SHOW pg_hint_plan.parse_messages;
628 SET client_min_messages TO debug3;
632 SET pg_hint_plan.parse_messages TO debug3;
633 SHOW pg_hint_plan.parse_messages;
635 SET client_min_messages TO debug2;
639 SET pg_hint_plan.parse_messages TO debug2;
640 SHOW pg_hint_plan.parse_messages;
642 SET client_min_messages TO debug1;
646 SET pg_hint_plan.parse_messages TO debug1;
647 SHOW pg_hint_plan.parse_messages;
649 SET client_min_messages TO log;
653 SET pg_hint_plan.parse_messages TO log;
654 SHOW pg_hint_plan.parse_messages;
656 SET client_min_messages TO info;
660 SET pg_hint_plan.parse_messages TO info;
661 SHOW pg_hint_plan.parse_messages;
663 SET client_min_messages TO notice;
667 SET pg_hint_plan.parse_messages TO notice;
668 SHOW pg_hint_plan.parse_messages;
670 SET client_min_messages TO warning;
674 SET pg_hint_plan.parse_messages TO warning;
675 SHOW pg_hint_plan.parse_messages;
677 SET client_min_messages TO error;
681 SET pg_hint_plan.parse_messages TO error;
682 SHOW pg_hint_plan.parse_messages;
684 SET client_min_messages TO fatal;
688 RESET client_min_messages;
689 SET pg_hint_plan.parse_messages TO DEFAULT;
690 SHOW pg_hint_plan.parse_messages;
694 SET pg_hint_plan.parse_messages TO fatal;
695 SHOW pg_hint_plan.parse_messages;
698 SET pg_hint_plan.parse_messages TO panic;
699 SHOW pg_hint_plan.parse_messages;
702 SET pg_hint_plan.parse_messages TO on;
703 SHOW pg_hint_plan.parse_messages;
706 ---- No. A-8-5 original GUC parameter pg_hint_plan.enable_hint_table
709 INSERT INTO hint_plan.hints (norm_query_string, application_name, hints)
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 SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1) INTO new_cnt
1084 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
1085 ORDER BY t_1.c1 LIMIT 1;
1089 $$ LANGUAGE plpgsql IMMUTABLE;
1092 ---- No. A-13-2 use hint of main query
1096 EXPLAIN (COSTS false) SELECT nested_planner(1) FROM s1.t1 t_1 ORDER BY t_1.c1;
1098 EXPLAIN (COSTS false) SELECT nested_planner(1) FROM s1.t1 t_1 ORDER BY t_1.c1;
1101 ---- No. A-13-3 output number of times of debugging log
1105 EXPLAIN (COSTS false) SELECT nested_planner(1) FROM s1.t1 t_1 ORDER BY t_1.c1;
1107 EXPLAIN (COSTS false) SELECT nested_planner(1) FROM s1.t1 t_1 ORDER BY t_1.c1;
1110 EXPLAIN (COSTS false) SELECT nested_planner(2) FROM s1.t1 t_1 ORDER BY t_1.c1;
1112 EXPLAIN (COSTS false) SELECT nested_planner(2) FROM s1.t1 t_1 ORDER BY t_1.c1;
1116 -- Redefine not to use cached plan
1118 CREATE OR REPLACE FUNCTION nested_planner(cnt int) RETURNS int AS $$
1122 RAISE NOTICE 'nested_planner(%)', cnt;
1129 SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1) INTO new_cnt
1131 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
1132 ORDER BY t_1.c1 LIMIT 1;
1136 $$ LANGUAGE plpgsql IMMUTABLE;
1138 EXPLAIN (COSTS false) SELECT nested_planner(5) FROM s1.t1 t_1 ORDER BY t_1.c1;
1140 EXPLAIN (COSTS false) SELECT nested_planner(5) FROM s1.t1 t_1 ORDER BY t_1.c1;
1143 ---- No. A-13-4 output of debugging log on hint status
1145 CREATE OR REPLACE FUNCTION recall_planner() RETURNS int AS $$
1146 SELECT /*+ IndexScan(t_1) */t_1.c1
1148 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
1149 ORDER BY t_1.c1 LIMIT 1;
1150 $$ LANGUAGE SQL IMMUTABLE;
1153 /*+HashJoin(t_1 t_2)*/
1154 EXPLAIN (COSTS false)
1155 SELECT recall_planner() FROM s1.t1 t_1
1156 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
1160 /*+HashJoin(st_1 st_2)*/
1161 EXPLAIN (COSTS false)
1162 SELECT recall_planner() FROM s1.t1 st_1
1163 JOIN s1.t2 st_2 ON (st_1.c1 = st_2.c1)
1167 /*+HashJoin(t_1 t_2)*/
1168 EXPLAIN (COSTS false)
1169 SELECT recall_planner() FROM s1.t1 st_1
1170 JOIN s1.t2 st_2 ON (st_1.c1 = st_2.c1)
1174 /*+HashJoin(st_1 st_2)*/
1175 EXPLAIN (COSTS false)
1176 SELECT recall_planner() FROM s1.t1 t_1
1177 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
1181 /*+HashJoin(t_1 t_1)*/
1182 EXPLAIN (COSTS false)
1183 SELECT recall_planner() FROM s1.t1 t_1
1187 CREATE OR REPLACE FUNCTION recall_planner_one_t() RETURNS int AS $$
1188 SELECT /*+ IndexScan(t_1) */t_1.c1
1190 ORDER BY t_1.c1 LIMIT 1;
1191 $$ LANGUAGE SQL IMMUTABLE;
1193 EXPLAIN (COSTS false)
1194 SELECT recall_planner_one_t() FROM s1.t1 t_1
1195 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
1197 /*+HashJoin(t_1 t_1)*/
1198 EXPLAIN (COSTS false)
1199 SELECT recall_planner_one_t() FROM s1.t1 t_1
1200 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
1203 DROP FUNCTION recall_planner_one_t(int);
1206 /*+HashJoin(t_1 t_1)*/
1207 EXPLAIN (COSTS false)
1208 SELECT recall_planner() FROM s1.t1 t_1
1209 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
1213 /*+MergeJoin(t_1 t_2)HashJoin(t_1 t_2)*/
1214 EXPLAIN (COSTS false)
1215 SELECT recall_planner() FROM s1.t1 t_1
1216 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)