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 SCHEMA other_schema;
20 CREATE EXTENSION pg_hint_plan SCHEMA other_schema;
22 CREATE EXTENSION pg_hint_plan;
23 DROP SCHEMA other_schema;
26 ---- No. A-5-1 comment pattern
31 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
35 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
39 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
43 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
46 /* /*+SeqScan(t1)*/ */
47 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
50 ---- No. A-5-2 hint position
54 EXPLAIN (COSTS false) SELECT c1 FROM s1.t1 WHERE t1.c1 = 1;
56 EXPLAIN (COSTS false) SELECT c1 FROM s1.t1 WHERE t1.c1 = 1;
59 EXPLAIN (COSTS false) SELECT c1, c2 AS c_2 /*+SeqScan(t1)*/ FROM s1.t1 WHERE t1.c1 = 1;
62 EXPLAIN (COSTS false) SELECT c1 AS "c1"/*+SeqScan(t1)*/ FROM s1.t1 WHERE t1.c1 = 1;
65 EXPLAIN (COSTS false) SELECT * /*+SeqScan(t1)*/ FROM s1.t1 WHERE t1.c1 = 1;
68 ---- No. A-6-1 hint's table definition
71 SET pg_hint_plan.enable_hint_table TO on;
76 ---- No. A-6-2 search condition
78 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
80 INSERT INTO hint_plan.hints (norm_query_string, application_name, hints)
82 'EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = ?;',
85 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
88 INSERT INTO hint_plan.hints (norm_query_string, application_name, hints)
90 'EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = ?;',
93 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
94 TRUNCATE hint_plan.hints;
97 INSERT INTO hint_plan.hints (norm_query_string, application_name, hints)
99 'EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = ?;',
100 'dummy_application_name',
103 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
104 TRUNCATE hint_plan.hints;
107 INSERT INTO hint_plan.hints (norm_query_string, application_name, hints)
109 'EXPLAIN (COSTS false) SELECT * FROM s1.t1;',
113 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
114 TRUNCATE hint_plan.hints;
117 ---- No. A-6-3 number of constant
121 INSERT INTO hint_plan.hints (norm_query_string, application_name, hints)
123 'EXPLAIN (COSTS false) SELECT c1 FROM s1.t1;',
127 EXPLAIN (COSTS false) SELECT c1 FROM s1.t1;
128 TRUNCATE hint_plan.hints;
131 INSERT INTO hint_plan.hints (norm_query_string, application_name, hints)
133 'EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = ?;',
137 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
138 TRUNCATE hint_plan.hints;
141 INSERT INTO hint_plan.hints (norm_query_string, application_name, hints)
143 'EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = ? OR t1.c1 = ?;',
147 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 OR t1.c1 = 0;
148 TRUNCATE hint_plan.hints;
149 SET pg_hint_plan.enable_hint_table TO off;
152 ---- No. A-7-2 hint delimiter
155 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
163 /*+Set(enable_indexscan"off")Set(enable_bitmapscan"off")*/
164 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
167 /*+ Set(enable_indexscan"off")Set(enable_bitmapscan"off")*/
168 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
171 /*+Set(enable_indexscan"off")Set(enable_bitmapscan"off") */
172 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
175 /*+ Set (enable_indexscan"off") Set (enable_bitmapscan"off")*/
176 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
179 /*+Set ( enable_indexscan"off")Set ( enable_bitmapscan"off")*/
180 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
183 /*+Set(enable_indexscan"off" ) Set(enable_bitmapscan"off" ) */
184 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
187 /*+Set( enable_indexscan "off" )Set( enable_bitmapscan "off" )*/
188 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
191 /*+ Set ( enable_indexscan "off" ) Set ( enable_bitmapscan "off" ) */
192 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
195 /*+ Set(enable_indexscan"off")Set(enable_bitmapscan"off")*/
196 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
199 /*+Set(enable_indexscan"off")Set(enable_bitmapscan"off") */
200 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
203 /*+ Set (enable_indexscan"off") Set (enable_bitmapscan"off")*/
204 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
207 /*+Set ( enable_indexscan"off")Set ( enable_bitmapscan"off")*/
208 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
211 /*+Set(enable_indexscan"off" ) Set(enable_bitmapscan"off" ) */
212 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
215 /*+Set( enable_indexscan "off" )Set( enable_bitmapscan "off" )*/
216 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
219 /*+ Set ( enable_indexscan "off" ) Set ( enable_bitmapscan "off" ) */
220 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
224 Set(enable_indexscan"off")Set(enable_bitmapscan"off")*/
225 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
228 /*+Set(enable_indexscan"off")Set(enable_bitmapscan"off")
230 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
235 (enable_indexscan"off")
237 (enable_bitmapscan"off")*/
238 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
243 enable_indexscan"off")Set
245 enable_bitmapscan"off")*/
246 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
249 /*+Set(enable_indexscan"off"
251 Set(enable_bitmapscan"off"
254 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
264 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
279 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
283 Set(enable_indexscan"off")Set(enable_bitmapscan"off")*/
284 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
287 /*+Set(enable_indexscan"off")Set(enable_bitmapscan"off")
289 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
294 (enable_indexscan"off")
296 (enable_bitmapscan"off")*/
297 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
302 enable_indexscan"off")Set
304 enable_bitmapscan"off")*/
305 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
308 /*+Set(enable_indexscan"off"
310 Set(enable_bitmapscan"off"
313 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
323 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
338 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
341 ---- No. A-7-3 hint object pattern
342 ---- No. A-9-2 message object pattern
348 EXPLAIN (COSTS false) SELECT * FROM s1.t1 t WHERE t.c1 = 1;
350 EXPLAIN (COSTS false) SELECT * FROM s1.t1 ttt WHERE ttt.c1 = 1;
352 EXPLAIN (COSTS false) SELECT * FROM s1.t1 t WHERE t.c1 = 1;
354 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;
363 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "T" WHERE "T".c1 = 1;
365 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "TTT" WHERE "TTT".c1 = 1;
370 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "(" WHERE "(".c1 = 1;
372 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "(" WHERE "(".c1 = 1;
377 EXPLAIN (COSTS false) SELECT * FROM s1.t1 ")" WHERE ")".c1 = 1;
379 EXPLAIN (COSTS false) SELECT * FROM s1.t1 ")" WHERE ")".c1 = 1;
381 EXPLAIN (COSTS false) SELECT * FROM s1.t1 ")))" WHERE ")))".c1 = 1;
386 EXPLAIN (COSTS false) SELECT * FROM s1.t1 """" WHERE """".c1 = 1;
388 EXPLAIN (COSTS false) SELECT * FROM s1.t1 """" WHERE """".c1 = 1;
389 /*+SeqScan("""""""")*/
390 EXPLAIN (COSTS false) SELECT * FROM s1.t1 """""""" WHERE """""""".c1 = 1;
395 EXPLAIN (COSTS false) SELECT * FROM s1.t1 " " WHERE " ".c1 = 1;
397 EXPLAIN (COSTS false) SELECT * FROM s1.t1 " " WHERE " ".c1 = 1;
399 EXPLAIN (COSTS false) SELECT * FROM s1.t1 " " WHERE " ".c1 = 1;
404 EXPLAIN (COSTS false) SELECT * FROM s1.t1 " " WHERE " ".c1 = 1;
406 EXPLAIN (COSTS false) SELECT * FROM s1.t1 " " WHERE " ".c1 = 1;
408 EXPLAIN (COSTS false) SELECT * FROM s1.t1 " " WHERE " ".c1 = 1;
414 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "
419 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "
426 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "
437 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "Set" WHERE "Set".c1 = 1;
439 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "Set" WHERE "Set".c1 = 1;
440 /*+SeqScan("Set SeqScan Leading")*/
441 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "Set SeqScan Leading" WHERE "Set SeqScan Leading".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;
452 EXPLAIN (COSTS false) SELECT * FROM s1.t1 あいう WHERE あいう.c1 = 1;
457 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "/**/" WHERE "/**/".c1 = 1;
458 /*+SeqScan(/**//**//**/)*/
459 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "/**//**//**/" WHERE "/**//**//**/".c1 = 1;
465 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "tT()""
466 Set/**/あ" WHERE "tT()""
472 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "tT()""
478 /*+SeqScan(a123456789b123456789c123456789d123456789e123456789f123)*/
479 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "123456789012345678901234567890123456789012345678901234" WHERE "123456789012345678901234567890123456789012345678901234".c1 = 1;
482 ---- No. A-7-4 hint parse error
486 /*+Set(enable_indexscan off)Set enable_tidscan off)Set(enable_bitmapscan off)SeqScan(t1)*/
487 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
490 /*+Set(enable_indexscan off)Set(enable_tidscan off Set(enable_bitmapscan off)SeqScan(t1)*/
491 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
494 /*+Set(enable_indexscan off)Set(enable_tidscan "off)Set(enable_bitmapscan off)SeqScan(t1)*/
495 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
498 /*+Set(enable_indexscan off)SeqScan("")Set(enable_bitmapscan off)*/
499 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
502 /*+Set(enable_indexscan off)NoSet(enable_tidscan off)Set(enable_bitmapscan off)SeqScan(t1)*/
503 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
506 /*+Set(enable_indexscan off)"Set"(enable_tidscan off)Set(enable_bitmapscan off)SeqScan(t1)*/
507 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
510 /*+Set(enable_indexscan off)Set(enable_tidscan /* value */off)Set(enable_bitmapscan off)SeqScan(t1)*/
511 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
514 ---- No. A-8-1 original GUC parameter
516 ---- Don't test postgresql itself.
518 -- SET ROLE super_user;
519 -- SET pg_hint_plan.debug_print TO off;
520 -- SHOW pg_hint_plan.enable_hint;
521 -- SHOW pg_hint_plan.debug_print;
522 -- SHOW pg_hint_plan.parse_messages;
523 -- SET pg_hint_plan.enable_hint TO off;
524 -- SET pg_hint_plan.debug_print TO on;
525 -- SET pg_hint_plan.parse_messages TO error;
526 -- SHOW pg_hint_plan.enable_hint;
527 -- SHOW pg_hint_plan.debug_print;
528 -- SHOW pg_hint_plan.parse_messages;
529 -- RESET pg_hint_plan.enable_hint;
530 -- RESET pg_hint_plan.debug_print;
531 -- RESET pg_hint_plan.parse_messages;
532 -- SHOW pg_hint_plan.enable_hint;
533 -- SHOW pg_hint_plan.debug_print;
534 -- SHOW pg_hint_plan.parse_messages;
537 -- SET ROLE normal_user;
538 -- SHOW pg_hint_plan.enable_hint;
539 -- SHOW pg_hint_plan.debug_print;
540 -- SHOW pg_hint_plan.parse_messages;
541 -- SET pg_hint_plan.enable_hint TO off;
542 -- SET pg_hint_plan.debug_print TO on;
543 -- SET pg_hint_plan.parse_messages TO error;
544 -- SHOW pg_hint_plan.enable_hint;
545 -- SHOW pg_hint_plan.debug_print;
546 -- SHOW pg_hint_plan.parse_messages;
547 -- RESET pg_hint_plan.enable_hint;
548 -- RESET pg_hint_plan.debug_print;
549 -- RESET pg_hint_plan.parse_messages;
550 -- SHOW pg_hint_plan.enable_hint;
551 -- SHOW pg_hint_plan.debug_print;
552 -- SHOW pg_hint_plan.parse_messages;
557 ---- No. A-8-2 original GUC parameter pg_hint_plan.enable_hint
561 SET pg_hint_plan.debug_print TO off;
562 SET pg_hint_plan.enable_hint TO on;
563 SHOW pg_hint_plan.enable_hint;
564 /*+Set(enable_indexscan off)*/
565 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
568 SET pg_hint_plan.enable_hint TO off;
569 SHOW pg_hint_plan.enable_hint;
570 /*+Set(enable_indexscan off)*/
571 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
574 -- Don't test PostgreSQL itself.
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 -- Don't test PostgreSQL itself
582 -- SET pg_hint_plan.enable_hint TO enable;
583 -- SHOW pg_hint_plan.enable_hint;
586 ---- No. A-8-3 original GUC parameter pg_hint_plan.debug_print
590 SET pg_hint_plan.enable_hint TO on;
591 SHOW pg_hint_plan.enable_hint;
592 SET pg_hint_plan.debug_print TO on;
593 SHOW pg_hint_plan.debug_print;
594 /*+Set(enable_indexscan off)*/
595 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
598 SET pg_hint_plan.debug_print TO off;
599 SHOW pg_hint_plan.debug_print;
600 /*+Set(enable_indexscan off)*/
601 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
604 SET pg_hint_plan.debug_print TO DEFAULT;
605 SHOW pg_hint_plan.debug_print;
606 /*+Set(enable_indexscan off)*/
607 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
610 SET pg_hint_plan.debug_print TO enable;
611 SHOW pg_hint_plan.debug_print;
614 ---- No. A-8-4 original GUC parameter pg_hint_plan.parse_messages
617 SET client_min_messages TO debug5;
620 SET pg_hint_plan.parse_messages TO debug5;
621 SHOW pg_hint_plan.parse_messages;
623 SET client_min_messages TO debug4;
627 SET pg_hint_plan.parse_messages TO debug4;
628 SHOW pg_hint_plan.parse_messages;
630 SET client_min_messages TO debug3;
634 SET pg_hint_plan.parse_messages TO debug3;
635 SHOW pg_hint_plan.parse_messages;
637 SET client_min_messages TO debug2;
641 SET pg_hint_plan.parse_messages TO debug2;
642 SHOW pg_hint_plan.parse_messages;
644 SET client_min_messages TO debug1;
648 SET pg_hint_plan.parse_messages TO debug1;
649 SHOW pg_hint_plan.parse_messages;
651 SET client_min_messages TO log;
655 SET pg_hint_plan.parse_messages TO log;
656 SHOW pg_hint_plan.parse_messages;
658 SET client_min_messages TO info;
662 SET pg_hint_plan.parse_messages TO info;
663 SHOW pg_hint_plan.parse_messages;
665 SET client_min_messages TO notice;
669 SET pg_hint_plan.parse_messages TO notice;
670 SHOW pg_hint_plan.parse_messages;
672 SET client_min_messages TO warning;
676 SET pg_hint_plan.parse_messages TO warning;
677 SHOW pg_hint_plan.parse_messages;
679 SET client_min_messages TO error;
683 SET pg_hint_plan.parse_messages TO error;
684 SHOW pg_hint_plan.parse_messages;
686 SET client_min_messages TO fatal;
690 RESET client_min_messages;
691 SET pg_hint_plan.parse_messages TO DEFAULT;
692 SHOW pg_hint_plan.parse_messages;
696 SET pg_hint_plan.parse_messages TO fatal;
697 SHOW pg_hint_plan.parse_messages;
700 SET pg_hint_plan.parse_messages TO panic;
701 SHOW pg_hint_plan.parse_messages;
704 SET pg_hint_plan.parse_messages TO on;
705 SHOW pg_hint_plan.parse_messages;
708 ---- No. A-8-5 original GUC parameter pg_hint_plan.enable_hint_table
711 INSERT INTO hint_plan.hints (norm_query_string, application_name, hints)
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 SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1) INTO new_cnt
1086 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
1087 ORDER BY t_1.c1 LIMIT 1;
1091 $$ LANGUAGE plpgsql IMMUTABLE;
1094 ---- No. A-13-2 use hint of main query
1098 EXPLAIN (COSTS false) SELECT nested_planner(1) FROM s1.t1 t_1 ORDER BY t_1.c1;
1100 EXPLAIN (COSTS false) SELECT nested_planner(1) FROM s1.t1 t_1 ORDER BY t_1.c1;
1103 ---- No. A-13-3 output number of times of debugging log
1107 EXPLAIN (COSTS false) SELECT nested_planner(1) FROM s1.t1 t_1 ORDER BY t_1.c1;
1109 EXPLAIN (COSTS false) SELECT nested_planner(1) 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;
1114 EXPLAIN (COSTS false) SELECT nested_planner(2) FROM s1.t1 t_1 ORDER BY t_1.c1;
1118 -- Redefine not to use cached plan
1120 CREATE OR REPLACE FUNCTION nested_planner(cnt int) RETURNS int AS $$
1124 RAISE NOTICE 'nested_planner(%)', cnt;
1131 SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1) INTO new_cnt
1133 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
1134 ORDER BY t_1.c1 LIMIT 1;
1138 $$ LANGUAGE plpgsql IMMUTABLE;
1140 EXPLAIN (COSTS false) SELECT nested_planner(5) FROM s1.t1 t_1 ORDER BY t_1.c1;
1142 EXPLAIN (COSTS false) SELECT nested_planner(5) FROM s1.t1 t_1 ORDER BY t_1.c1;
1145 ---- No. A-13-4 output of debugging log on hint status
1147 CREATE OR REPLACE FUNCTION recall_planner() RETURNS int AS $$
1148 SELECT /*+ IndexScan(t_1) */t_1.c1
1150 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
1151 ORDER BY t_1.c1 LIMIT 1;
1152 $$ LANGUAGE SQL IMMUTABLE;
1155 /*+HashJoin(t_1 t_2)*/
1156 EXPLAIN (COSTS false)
1157 SELECT recall_planner() FROM s1.t1 t_1
1158 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
1162 /*+HashJoin(st_1 st_2)*/
1163 EXPLAIN (COSTS false)
1164 SELECT recall_planner() FROM s1.t1 st_1
1165 JOIN s1.t2 st_2 ON (st_1.c1 = st_2.c1)
1169 /*+HashJoin(t_1 t_2)*/
1170 EXPLAIN (COSTS false)
1171 SELECT recall_planner() FROM s1.t1 st_1
1172 JOIN s1.t2 st_2 ON (st_1.c1 = st_2.c1)
1176 /*+HashJoin(st_1 st_2)*/
1177 EXPLAIN (COSTS false)
1178 SELECT recall_planner() FROM s1.t1 t_1
1179 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
1183 /*+HashJoin(t_1 t_1)*/
1184 EXPLAIN (COSTS false)
1185 SELECT recall_planner() FROM s1.t1 t_1
1189 CREATE OR REPLACE FUNCTION recall_planner_one_t() RETURNS int AS $$
1190 SELECT /*+ IndexScan(t_1) */t_1.c1
1192 ORDER BY t_1.c1 LIMIT 1;
1193 $$ LANGUAGE SQL IMMUTABLE;
1195 EXPLAIN (COSTS false)
1196 SELECT recall_planner_one_t() FROM s1.t1 t_1
1197 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
1199 /*+HashJoin(t_1 t_1)*/
1200 EXPLAIN (COSTS false)
1201 SELECT recall_planner_one_t() FROM s1.t1 t_1
1202 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
1205 DROP FUNCTION recall_planner_one_t(int);
1208 /*+HashJoin(t_1 t_1)*/
1209 EXPLAIN (COSTS false)
1210 SELECT recall_planner() FROM s1.t1 t_1
1211 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
1215 /*+MergeJoin(t_1 t_2)HashJoin(t_1 t_2)*/
1216 EXPLAIN (COSTS false)
1217 SELECT recall_planner() FROM s1.t1 t_1
1218 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)