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;
8 ---- No. A-5-1 hint format
13 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
17 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
21 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
25 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
29 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
33 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
36 /*+SeqScan(t1) /* nest comment */ */
37 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
40 /* +SeqScan(t1) /* nest comment */ */
41 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
44 /*SeqScan(t1) /* nest comment */ */
45 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
48 ---- No. A-5-2 hint position
53 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
58 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
61 EXPLAIN (COSTS false) SELECT /*+SeqScan(t1)*/ * FROM s1.t1 WHERE t1.c1 = 1;
64 ---- No. A-5-4 hint delimiter
67 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
75 /*+Set(enable_indexscan"off")Set(enable_bitmapscan"off")*/
76 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
79 /*+ Set(enable_indexscan"off")Set(enable_bitmapscan"off")*/
80 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
83 /*+Set(enable_indexscan"off")Set(enable_bitmapscan"off") */
84 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
87 /*+ Set (enable_indexscan"off") Set (enable_bitmapscan"off")*/
88 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
91 /*+Set ( enable_indexscan"off")Set ( enable_bitmapscan"off")*/
92 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
95 /*+Set(enable_indexscan"off" ) Set(enable_bitmapscan"off" ) */
96 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
99 /*+Set( enable_indexscan "off" )Set( enable_bitmapscan "off" )*/
100 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
103 /*+ Set ( enable_indexscan "off" ) Set ( enable_bitmapscan "off" ) */
104 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
107 /*+ Set(enable_indexscan"off")Set(enable_bitmapscan"off")*/
108 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
111 /*+Set(enable_indexscan"off")Set(enable_bitmapscan"off") */
112 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
115 /*+ Set (enable_indexscan"off") Set (enable_bitmapscan"off")*/
116 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
119 /*+Set ( enable_indexscan"off")Set ( enable_bitmapscan"off")*/
120 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
123 /*+Set(enable_indexscan"off" ) Set(enable_bitmapscan"off" ) */
124 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
127 /*+Set( enable_indexscan "off" )Set( enable_bitmapscan "off" )*/
128 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
131 /*+ Set ( enable_indexscan "off" ) Set ( enable_bitmapscan "off" ) */
132 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
136 Set(enable_indexscan"off")Set(enable_bitmapscan"off")*/
137 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
140 /*+Set(enable_indexscan"off")Set(enable_bitmapscan"off")
142 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
147 (enable_indexscan"off")
149 (enable_bitmapscan"off")*/
150 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
155 enable_indexscan"off")Set
157 enable_bitmapscan"off")*/
158 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
161 /*+Set(enable_indexscan"off"
163 Set(enable_bitmapscan"off"
166 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
176 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
191 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")
201 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
206 (enable_indexscan"off")
208 (enable_bitmapscan"off")*/
209 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
214 enable_indexscan"off")Set
216 enable_bitmapscan"off")*/
217 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
220 /*+Set(enable_indexscan"off"
222 Set(enable_bitmapscan"off"
225 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
235 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
250 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
253 ---- No. A-5-5 hint object pattern
254 ---- No. A-7-2 message object pattern
260 EXPLAIN (COSTS false) SELECT * FROM s1.t1 t WHERE t.c1 = 1;
262 EXPLAIN (COSTS false) SELECT * FROM s1.t1 ttt WHERE ttt.c1 = 1;
264 EXPLAIN (COSTS false) SELECT * FROM s1.t1 t WHERE t.c1 = 1;
266 EXPLAIN (COSTS false) SELECT * FROM s1.t1 ttt WHERE ttt.c1 = 1;
271 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "T" WHERE "T".c1 = 1;
273 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "TTT" WHERE "TTT".c1 = 1;
275 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "T" WHERE "T".c1 = 1;
277 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "TTT" WHERE "TTT".c1 = 1;
282 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "(" WHERE "(".c1 = 1;
284 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "(((" WHERE "(((".c1 = 1;
286 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "(" WHERE "(".c1 = 1;
288 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "(((" WHERE "(((".c1 = 1;
293 EXPLAIN (COSTS false) SELECT * FROM s1.t1 ")" WHERE ")".c1 = 1;
295 EXPLAIN (COSTS false) SELECT * FROM s1.t1 ")" WHERE ")".c1 = 1;
297 EXPLAIN (COSTS false) SELECT * FROM s1.t1 ")))" WHERE ")))".c1 = 1;
302 EXPLAIN (COSTS false) SELECT * FROM s1.t1 """" WHERE """".c1 = 1;
304 EXPLAIN (COSTS false) SELECT * FROM s1.t1 """" WHERE """".c1 = 1;
305 /*+SeqScan("""""""")*/
306 EXPLAIN (COSTS false) SELECT * FROM s1.t1 """""""" WHERE """""""".c1 = 1;
311 EXPLAIN (COSTS false) SELECT * FROM s1.t1 " " WHERE " ".c1 = 1;
313 EXPLAIN (COSTS false) SELECT * FROM s1.t1 " " WHERE " ".c1 = 1;
315 EXPLAIN (COSTS false) SELECT * FROM s1.t1 " " WHERE " ".c1 = 1;
320 EXPLAIN (COSTS false) SELECT * FROM s1.t1 " " WHERE " ".c1 = 1;
322 EXPLAIN (COSTS false) SELECT * FROM s1.t1 " " WHERE " ".c1 = 1;
324 EXPLAIN (COSTS false) SELECT * FROM s1.t1 " " WHERE " ".c1 = 1;
330 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "
335 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "
342 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "
353 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "Set" WHERE "Set".c1 = 1;
355 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "Set" WHERE "Set".c1 = 1;
356 /*+SeqScan("Set SeqScan Leading")*/
357 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "Set SeqScan Leading" WHERE "Set SeqScan Leading".c1 = 1;
362 EXPLAIN (COSTS false) SELECT * FROM s1.t1 あ WHERE あ.c1 = 1;
364 EXPLAIN (COSTS false) SELECT * FROM s1.t1 あいう WHERE あいう.c1 = 1;
366 EXPLAIN (COSTS false) SELECT * FROM s1.t1 あ WHERE あ.c1 = 1;
368 EXPLAIN (COSTS false) SELECT * FROM s1.t1 あいう WHERE あいう.c1 = 1;
373 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "/**/" WHERE "/**/".c1 = 1;
374 /*+SeqScan(/**//**//**/)*/
375 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "/**//**//**/" WHERE "/**//**//**/".c1 = 1;
381 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "tT()""
382 Set/**/あ" WHERE "tT()""
388 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "tT()""
393 ---- No. A-5-6 hint parse error
397 /*+Set(enable_indexscan off)Set enable_tidscan off)Set(enable_bitmapscan off)SeqScan(t1)*/
398 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
401 /*+Set(enable_indexscan off)Set(enable_tidscan off Set(enable_bitmapscan off)SeqScan(t1)*/
402 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
405 /*+Set(enable_indexscan off)Set(enable_tidscan "off)Set(enable_bitmapscan off)SeqScan(t1)*/
406 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
409 /*+Set(enable_indexscan off)SeqScan("")Set(enable_bitmapscan off)*/
410 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
413 /*+Set(enable_indexscan off)NoSet(enable_tidscan off)Set(enable_bitmapscan off)SeqScan(t1)*/
414 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
417 /*+Set(enable_indexscan off)"Set"(enable_tidscan off)Set(enable_bitmapscan off)SeqScan(t1)*/
418 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
421 /*+Set(enable_indexscan off)Set(enable_tidscan /* value */off)Set(enable_bitmapscan off)SeqScan(t1)*/
422 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
425 ---- No. A-6-1 original GUC parameter
430 SET pg_hint_plan.debug_print TO off;
431 SHOW pg_hint_plan.enable_hint;
432 SHOW pg_hint_plan.debug_print;
433 SHOW pg_hint_plan.parse_messages;
434 SET pg_hint_plan.enable_hint TO off;
435 SET pg_hint_plan.debug_print TO on;
436 SET pg_hint_plan.parse_messages TO error;
437 SHOW pg_hint_plan.enable_hint;
438 SHOW pg_hint_plan.debug_print;
439 SHOW pg_hint_plan.parse_messages;
440 RESET pg_hint_plan.enable_hint;
441 RESET pg_hint_plan.debug_print;
442 RESET pg_hint_plan.parse_messages;
443 SHOW pg_hint_plan.enable_hint;
444 SHOW pg_hint_plan.debug_print;
445 SHOW pg_hint_plan.parse_messages;
448 SET ROLE normal_user;
449 SHOW pg_hint_plan.enable_hint;
450 SHOW pg_hint_plan.debug_print;
451 SHOW pg_hint_plan.parse_messages;
452 SET pg_hint_plan.enable_hint TO off;
453 SET pg_hint_plan.debug_print TO on;
454 SET pg_hint_plan.parse_messages TO error;
455 SHOW pg_hint_plan.enable_hint;
456 SHOW pg_hint_plan.debug_print;
457 SHOW pg_hint_plan.parse_messages;
458 RESET pg_hint_plan.enable_hint;
459 RESET pg_hint_plan.debug_print;
460 RESET pg_hint_plan.parse_messages;
461 SHOW pg_hint_plan.enable_hint;
462 SHOW pg_hint_plan.debug_print;
463 SHOW pg_hint_plan.parse_messages;
468 ---- No. A-6-2 original GUC parameter pg_hint_plan.enable_hint
472 SET pg_hint_plan.enable_hint TO on;
473 SHOW pg_hint_plan.enable_hint;
474 /*+Set(enable_indexscan off)*/
475 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
478 SET pg_hint_plan.enable_hint TO off;
479 SHOW pg_hint_plan.enable_hint;
480 /*+Set(enable_indexscan off)*/
481 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
484 SET pg_hint_plan.enable_hint TO DEFAULT;
485 SHOW pg_hint_plan.enable_hint;
486 /*+Set(enable_indexscan off)*/
487 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
490 SET pg_hint_plan.enable_hint TO enable;
491 SHOW pg_hint_plan.enable_hint;
494 ---- No. A-6-3 original GUC parameter pg_hint_plan.debug_print
498 SET pg_hint_plan.debug_print TO on;
499 SHOW pg_hint_plan.debug_print;
500 /*+Set(enable_indexscan off)*/
501 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
504 SET pg_hint_plan.debug_print TO off;
505 SHOW pg_hint_plan.debug_print;
506 /*+Set(enable_indexscan off)*/
507 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
510 SET pg_hint_plan.debug_print TO DEFAULT;
511 SHOW pg_hint_plan.debug_print;
512 /*+Set(enable_indexscan off)*/
513 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
516 SET pg_hint_plan.debug_print TO enable;
517 SHOW pg_hint_plan.debug_print;
520 ---- No. A-6-4 original GUC parameter pg_hint_plan.parse_messages
523 SET client_min_messages TO debug5;
526 SET pg_hint_plan.parse_messages TO debug5;
527 SHOW pg_hint_plan.parse_messages;
529 SET client_min_messages TO debug4;
533 SET pg_hint_plan.parse_messages TO debug4;
534 SHOW pg_hint_plan.parse_messages;
536 SET client_min_messages TO debug3;
540 SET pg_hint_plan.parse_messages TO debug3;
541 SHOW pg_hint_plan.parse_messages;
543 SET client_min_messages TO debug2;
547 SET pg_hint_plan.parse_messages TO debug2;
548 SHOW pg_hint_plan.parse_messages;
550 SET client_min_messages TO debug1;
554 SET pg_hint_plan.parse_messages TO debug1;
555 SHOW pg_hint_plan.parse_messages;
557 SET client_min_messages TO log;
561 SET pg_hint_plan.parse_messages TO log;
562 SHOW pg_hint_plan.parse_messages;
564 SET client_min_messages TO info;
568 SET pg_hint_plan.parse_messages TO info;
569 SHOW pg_hint_plan.parse_messages;
571 SET client_min_messages TO notice;
575 SET pg_hint_plan.parse_messages TO notice;
576 SHOW pg_hint_plan.parse_messages;
578 SET client_min_messages TO warning;
582 SET pg_hint_plan.parse_messages TO warning;
583 SHOW pg_hint_plan.parse_messages;
585 SET client_min_messages TO error;
589 SET pg_hint_plan.parse_messages TO error;
590 SHOW pg_hint_plan.parse_messages;
592 SET client_min_messages TO fatal;
596 RESET client_min_messages;
597 SET pg_hint_plan.parse_messages TO DEFAULT;
598 SHOW pg_hint_plan.parse_messages;
602 SET pg_hint_plan.parse_messages TO fatal;
603 SHOW pg_hint_plan.parse_messages;
606 SET pg_hint_plan.parse_messages TO panic;
607 SHOW pg_hint_plan.parse_messages;
610 SET pg_hint_plan.parse_messages TO on;
611 SHOW pg_hint_plan.parse_messages;
614 ---- No. A-7-1 parse error message output
618 /*+"Set"(enable_indexscan on)*/SELECT 1;
619 /*+Set()(enable_indexscan on)*/SELECT 1;
620 /*+Set(enable_indexscan on*/SELECT 1;
623 ---- No. A-7-3 hint state output
626 SET pg_hint_plan.debug_print TO on;
627 SET client_min_messages TO LOG;
631 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
634 /*+SeqScan(no_table)*/
635 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
638 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
639 /*+TidScan(t1)BitmapScan(t1)*/
640 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
641 /*+TidScan(t1)BitmapScan(t1)IndexScan(t1)*/
642 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
643 /*+TidScan(t1)BitmapScan(t1)IndexScan(t1)SeqScan(t1)*/
644 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
647 /*+Set(enable_indexscan enable)*/
648 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
651 ---- No. A-8-1 hint state output
654 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
655 EXPLAIN (COSTS false) EXECUTE p1;
658 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
659 EXPLAIN (COSTS false) EXECUTE p1 (1000);
660 EXPLAIN (COSTS false) EXECUTE p1 (1000);
661 EXPLAIN (COSTS false) EXECUTE p1 (1000);
662 EXPLAIN (COSTS false) EXECUTE p1 (1000);
663 EXPLAIN (COSTS false) EXECUTE p1 (1000);
664 EXPLAIN (COSTS false) EXECUTE p1 (1000);
670 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
672 EXPLAIN (COSTS false) EXECUTE p1;
673 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
675 EXPLAIN (COSTS false) EXECUTE p1;
679 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
681 EXPLAIN (COSTS false) EXECUTE p1 (1000);
683 EXPLAIN (COSTS false) EXECUTE p1 (1000);
685 EXPLAIN (COSTS false) EXECUTE p1 (1000);
687 EXPLAIN (COSTS false) EXECUTE p1 (1000);
689 EXPLAIN (COSTS false) EXECUTE p1 (1000);
691 EXPLAIN (COSTS false) EXECUTE p1 (1000);
692 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
694 EXPLAIN (COSTS false) EXECUTE p1 (1000);
700 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
701 EXPLAIN (COSTS false) EXECUTE p1;
702 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
703 EXPLAIN (COSTS false) EXECUTE p1;
707 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
708 EXPLAIN (COSTS false) EXECUTE p1 (1000);
709 EXPLAIN (COSTS false) EXECUTE p1 (1000);
710 EXPLAIN (COSTS false) EXECUTE p1 (1000);
711 EXPLAIN (COSTS false) EXECUTE p1 (1000);
712 EXPLAIN (COSTS false) EXECUTE p1 (1000);
713 EXPLAIN (COSTS false) EXECUTE p1 (1000);
714 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
715 EXPLAIN (COSTS false) EXECUTE p1 (1000);
720 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
722 EXPLAIN (COSTS false) EXECUTE p1;
723 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
725 EXPLAIN (COSTS false) EXECUTE p1;
728 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
730 EXPLAIN (COSTS false) EXECUTE p1 (1000);
731 EXPLAIN (COSTS false) EXECUTE p1 (1000);
732 EXPLAIN (COSTS false) EXECUTE p1 (1000);
733 EXPLAIN (COSTS false) EXECUTE p1 (1000);
734 EXPLAIN (COSTS false) EXECUTE p1 (1000);
735 EXPLAIN (COSTS false) EXECUTE p1 (1000);
736 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
738 EXPLAIN (COSTS false) EXECUTE p1 (1000);
744 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
746 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
747 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
749 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
753 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
755 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
757 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
759 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
761 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
763 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
765 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
766 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
768 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
774 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
775 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
776 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
777 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
781 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
782 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
783 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
784 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
785 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
786 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
787 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
788 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
789 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
794 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
796 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
797 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
799 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
802 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
804 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
806 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
808 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
810 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
812 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
814 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
815 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
817 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
821 ---- No. A-8-4 EXECUTE statement name error
826 SHOW pg_hint_plan.debug_print;
829 ---- No. A-9-5 EXECUTE statement name error
833 SELECT pg_stat_statements_reset();
834 SELECT * FROM s1.t1 WHERE t1.c1 = 1;
835 /*+Set(enable_seqscan off)*/ SELECT * FROM s1.t1 WHERE t1.c1 = 1;
836 /*+SeqScan(t1)*/ SELECT * FROM s1.t1 WHERE t1.c1 = 1;
837 SELECT s.query, s.calls
838 FROM public.pg_stat_statements s
839 JOIN pg_catalog.pg_database d
844 ---- No. A-10-1 duplicate hint
848 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2, s1.t3, s1.t4 WHERE t1.ctid = '(1,1)' AND t1.c1 = t2.c1 AND t2.ctid = '(1,1)' AND t1.c1 = t3.c1 AND t3.ctid = '(1,1)' AND t1.c1 = t4.c1 AND t4.ctid = '(1,1)';
850 Set(enable_tidscan aaa)
851 Set(enable_tidscan on)
852 Set(enable_tidscan off)
864 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2, s1.t3, s1.t4 WHERE t1.ctid = '(1,1)' AND t1.c1 = t2.c1 AND t2.ctid = '(1,1)' AND t1.c1 = t3.c1 AND t3.ctid = '(1,1)' AND t1.c1 = t4.c1 AND t4.ctid = '(1,1)';
867 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2, s1.t3, s1.t4 WHERE t1.ctid = '(1,1)' AND t1.c1 = t2.c1 AND t2.ctid = '(1,1)' AND t1.c1 = t3.c1 AND t3.ctid = '(1,1)' AND t1.c1 = t4.c1 AND t4.ctid = '(1,1)';
870 Set(enable_tidscan aaa)
874 Set(enable_tidscan on)
878 Set(enable_tidscan off)
883 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2, s1.t3, s1.t4 WHERE t1.ctid = '(1,1)' AND t1.c1 = t2.c1 AND t2.ctid = '(1,1)' AND t1.c1 = t3.c1 AND t3.ctid = '(1,1)' AND t1.c1 = t4.c1 AND t4.ctid = '(1,1)';
886 ---- No. A-10-2 restrict query type
890 EXPLAIN (COSTS false) SELECT * FROM s1.t1 FULL OUTER JOIN s1.t2 ON (t1.c1 = t2.c1);
892 EXPLAIN (COSTS true) SELECT * FROM s1.t1 FULL OUTER JOIN s1.t2 ON (t1.c1 = t2.c1);
895 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c3 = 1;
896 /*+IndexScan(t1 t1_i1)*/
897 EXPLAIN (COSTS true) SELECT * FROM s1.t1 WHERE t1.c3 = 1;
900 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
902 EXPLAIN (COSTS true) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
905 ---- No. A-10-3 VIEW, RULE multi specified
909 EXPLAIN (COSTS false) SELECT * FROM s1.v1 v1, s1.v1 v2 WHERE v1.c1 = v2.c1;
910 /*+Leading(v1t1 v1t1)HashJoin(v1t1 v1t1)BitmapScan(v1t1)*/
911 EXPLAIN (COSTS false) SELECT * FROM s1.v1 v1, s1.v1 v2 WHERE v1.c1 = v2.c1;
914 EXPLAIN (COSTS false) SELECT * FROM s1.v1 v1, s1.v1_ v2 WHERE v1.c1 = v2.c1;
915 /*+Leading(v1t1 v1t1_)NestLoop(v1t1 v1t1_)SeqScan(v1t1)BitmapScan(v1t1_)*/
916 EXPLAIN (COSTS false) SELECT * FROM s1.v1 v1, s1.v1_ v2 WHERE v1.c1 = v2.c1;
919 EXPLAIN (COSTS false) SELECT * FROM s1.r4 t1, s1.r4 t2 WHERE t1.c1 = t2.c1;
920 /*+Leading(r4t1 r4t1)HashJoin(r4t1 r4t1)BitmapScan(r4t1)*/
921 EXPLAIN (COSTS false) SELECT * FROM s1.r4 t1, s1.r4 t2 WHERE t1.c1 = t2.c1;
924 EXPLAIN (COSTS false) SELECT * FROM s1.r4 t1, s1.r5 t2 WHERE t1.c1 = t2.c1;
925 /*+Leading(r4t1 r5t1)NestLoop(r4t1 r5t1)SeqScan(r4t1)BitmapScan(r5t1)*/
926 EXPLAIN (COSTS false) SELECT * FROM s1.r4 t1, s1.r5 t2 WHERE t1.c1 = t2.c1;
929 ---- No. A-11-1 psql command
932 SELECT count(*) FROM s1.t1 WHERE t1.c1 = 1;
934 SELECT count(*) FROM s1.t1 WHERE t1.c1 = 1;
938 SELECT count(*) FROM s1.t1 WHERE t1.c1 = 1;
942 SELECT count(*) FROM s1.t1 WHERE t1.c1 = 1;
946 ---- No. A-11-4 PL/pgSQL function
950 CREATE OR REPLACE FUNCTION f1() RETURNS SETOF text LANGUAGE plpgsql AS $$
954 FOR r IN EXPLAIN SELECT c4 FROM s1.t1 WHERE t1.c1 = 1
956 RETURN NEXT r; -- return current row of SELECT
966 /*+SeqScan(t1)*/CREATE OR REPLACE FUNCTION f1() RETURNS SETOF text LANGUAGE plpgsql AS $$
970 /*+SeqScan(t1)*/FOR r IN EXPLAIN /*+SeqScan(t1)*/SELECT c4 FROM s1.t1 WHERE t1.c1 = 1
972 /*+SeqScan(t1)*/RETURN NEXT r; -- return current row of SELECT
974 /*+SeqScan(t1)*/RETURN;
980 ---- No. A-12-1 reset of global variable of core at the error
981 ---- No. A-12-2 reset of global variable of original at the error
984 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
985 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)NestLoop(t1 t1)*/
986 PREPARE p1 AS SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
987 EXPLAIN (COSTS false) EXECUTE p1;
991 SELECT name, setting FROM settings;
992 SET pg_hint_plan.parse_messages TO error;
993 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)NestLoop(t1 t1)*/
994 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
995 SELECT name, setting FROM settings;
996 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)*/
997 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1001 SELECT name, setting FROM settings;
1002 SET pg_hint_plan.parse_messages TO error;
1003 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)NestLoop(t1 t1)*/
1004 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1005 SELECT name, setting FROM settings;
1006 EXPLAIN (COSTS false) EXECUTE p1;
1010 SELECT name, setting FROM settings;
1011 SET pg_hint_plan.parse_messages TO error;
1012 EXPLAIN (COSTS false) EXECUTE p2;
1013 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)*/
1014 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1015 EXPLAIN (COSTS false) EXECUTE p1;
1016 SELECT name, setting FROM settings;
1020 SELECT name, setting FROM settings;
1021 SET pg_hint_plan.parse_messages TO error;
1022 EXPLAIN (COSTS false) EXECUTE p2;
1023 EXPLAIN (COSTS false) EXECUTE p1;
1024 SELECT name, setting FROM settings;
1027 SET pg_hint_plan.parse_messages TO LOG;
1030 ---- No. A-12-3 effective range of the hint
1033 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1036 SET enable_indexscan TO off;
1037 SET enable_mergejoin TO off;
1038 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1039 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;
1042 SELECT name, setting FROM settings;
1043 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1046 SET enable_indexscan TO off;
1047 SET enable_mergejoin TO off;
1048 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1049 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;
1055 SELECT name, setting FROM settings;
1056 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1060 SET enable_indexscan TO off;
1061 SET enable_mergejoin TO off;
1062 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1063 SELECT name, setting FROM settings;
1064 /*+Set(enable_indexscan on)Set(geqo_threshold 100)IndexScan(t2)MergeJoin(t1 t2)Leading(t2 t1)*/
1065 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1067 SET enable_indexscan TO off;
1068 SET enable_mergejoin TO off;
1069 LOAD 'pg_hint_plan';
1070 SELECT name, setting FROM settings;
1071 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
1073 SET pg_hint_plan.enable_hint TO on;
1074 SET pg_hint_plan.debug_print TO on;
1075 SET client_min_messages TO LOG;
1076 SET search_path TO public;
1077 RESET enable_indexscan;
1078 RESET enable_mergejoin;
1081 ---- No. A-13 call planner recursively
1084 CREATE OR REPLACE FUNCTION nested_planner(cnt int) RETURNS int AS $$
1088 RAISE NOTICE 'nested_planner(%)', cnt;
1095 EXECUTE '/*+ IndexScan(t_1) */'
1096 ' SELECT nested_planner($1) FROM s1.t1 t_1'
1097 ' JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)'
1098 ' ORDER BY t_1.c1 LIMIT 1'
1099 INTO new_cnt USING cnt - 1;
1103 $$ LANGUAGE plpgsql IMMUTABLE;
1106 ---- No. A-13-2 use hint of main query
1110 EXPLAIN (COSTS false) SELECT nested_planner(1) FROM s1.t1 t_1 ORDER BY t_1.c1;
1112 EXPLAIN (COSTS false) SELECT nested_planner(1) FROM s1.t1 t_1 ORDER BY t_1.c1;
1115 ---- No. A-13-3 output number of times of debugging log
1119 EXPLAIN (COSTS false) SELECT nested_planner(1) FROM s1.t1 t_1 ORDER BY t_1.c1;
1121 EXPLAIN (COSTS false) SELECT nested_planner(1) FROM s1.t1 t_1 ORDER BY t_1.c1;
1124 EXPLAIN (COSTS false) SELECT nested_planner(2) FROM s1.t1 t_1 ORDER BY t_1.c1;
1126 EXPLAIN (COSTS false) SELECT nested_planner(2) FROM s1.t1 t_1 ORDER BY t_1.c1;
1129 EXPLAIN (COSTS false) SELECT nested_planner(5) FROM s1.t1 t_1 ORDER BY t_1.c1;
1131 EXPLAIN (COSTS false) SELECT nested_planner(5) FROM s1.t1 t_1 ORDER BY t_1.c1;
1134 ---- No. A-13-4 output of debugging log on hint status
1138 /*+HashJoin(t_1 t_2)*/
1139 EXPLAIN (COSTS false)
1140 SELECT nested_planner(2) FROM s1.t1 t_1
1141 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
1145 /*+HashJoin(st_1 st_2)*/
1146 EXPLAIN (COSTS false)
1147 SELECT nested_planner(2) FROM s1.t1 st_1
1148 JOIN s1.t2 st_2 ON (st_1.c1 = st_2.c1)
1152 /*+HashJoin(t_1 t_2)*/
1153 EXPLAIN (COSTS false)
1154 SELECT nested_planner(2) FROM s1.t1 st_1
1155 JOIN s1.t2 st_2 ON (st_1.c1 = st_2.c1)
1159 /*+HashJoin(st_1 st_2)*/
1160 EXPLAIN (COSTS false)
1161 SELECT nested_planner(2) FROM s1.t1 t_1
1162 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
1166 /*+HashJoin(t_1 t_1)*/
1167 EXPLAIN (COSTS false)
1168 SELECT nested_planner(2) FROM s1.t1 t_1
1172 CREATE OR REPLACE FUNCTION nested_planner_one_t(cnt int) RETURNS int AS $$
1176 RAISE NOTICE 'nested_planner_one_t(%)', cnt;
1182 EXECUTE '/*+ IndexScan(t_1) */'
1183 ' SELECT nested_planner_one_t($1) FROM s1.t1 t_1'
1184 ' ORDER BY t_1.c1 LIMIT 1'
1185 INTO new_cnt USING cnt - 1;
1189 $$ LANGUAGE plpgsql IMMUTABLE;
1191 EXPLAIN (COSTS false)
1192 SELECT nested_planner_one_t(2) FROM s1.t1 t_1
1193 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
1195 /*+HashJoin(t_1 t_1)*/
1196 EXPLAIN (COSTS false)
1197 SELECT nested_planner_one_t(2) FROM s1.t1 t_1
1198 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
1201 DROP FUNCTION nested_planner_one_t(int);
1204 /*+HashJoin(t_1 t_1)*/
1205 EXPLAIN (COSTS false)
1206 SELECT nested_planner(2) FROM s1.t1 t_1
1207 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
1211 /*+MergeJoin(t_1 t_2)HashJoin(t_1 t_2)*/
1212 EXPLAIN (COSTS false)
1213 SELECT nested_planner(2) FROM s1.t1 t_1
1214 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)