2 SET pg_hint_plan.enable 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;
432 SHOW pg_hint_plan.debug_print;
433 SHOW pg_hint_plan.parse_messages;
434 SET pg_hint_plan.enable 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;
438 SHOW pg_hint_plan.debug_print;
439 SHOW pg_hint_plan.parse_messages;
440 RESET pg_hint_plan.enable;
441 RESET pg_hint_plan.debug_print;
442 RESET pg_hint_plan.parse_messages;
443 SHOW pg_hint_plan.enable;
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;
450 SHOW pg_hint_plan.debug_print;
451 SHOW pg_hint_plan.parse_messages;
452 SET pg_hint_plan.enable 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;
456 SHOW pg_hint_plan.debug_print;
457 SHOW pg_hint_plan.parse_messages;
458 RESET pg_hint_plan.enable;
459 RESET pg_hint_plan.debug_print;
460 RESET pg_hint_plan.parse_messages;
461 SHOW pg_hint_plan.enable;
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
472 SET pg_hint_plan.enable TO on;
473 SHOW pg_hint_plan.enable;
474 /*+Set(enable_indexscan off)*/
475 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
478 SET pg_hint_plan.enable TO off;
479 SHOW pg_hint_plan.enable;
480 /*+Set(enable_indexscan off)*/
481 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
484 SET pg_hint_plan.enable TO DEFAULT;
485 SHOW pg_hint_plan.enable;
486 /*+Set(enable_indexscan off)*/
487 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
490 SET pg_hint_plan.enable TO enable;
491 SHOW pg_hint_plan.enable;
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 (1);
665 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
667 EXPLAIN (COSTS false) EXECUTE p1;
668 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
670 EXPLAIN (COSTS false) EXECUTE p1;
674 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
676 EXPLAIN (COSTS false) EXECUTE p1 (1);
677 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
679 EXPLAIN (COSTS false) EXECUTE p1 (1);
685 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
686 EXPLAIN (COSTS false) EXECUTE p1;
687 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
688 EXPLAIN (COSTS false) EXECUTE p1;
692 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
693 EXPLAIN (COSTS false) EXECUTE p1 (1);
694 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
695 EXPLAIN (COSTS false) EXECUTE p1 (1);
700 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
702 EXPLAIN (COSTS false) EXECUTE p1;
703 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
705 EXPLAIN (COSTS false) EXECUTE p1;
708 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
710 EXPLAIN (COSTS false) EXECUTE p1 (1);
711 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
713 EXPLAIN (COSTS false) EXECUTE p1 (1);
717 ---- No. A-8-4 EXECUTE statement name error
722 SHOW pg_hint_plan.debug_print;
725 ---- No. A-9-5 EXECUTE statement name error
729 SELECT pg_stat_statements_reset();
730 SELECT * FROM s1.t1 WHERE t1.c1 = 1;
731 /*+Set(enable_seqscan off)*/ SELECT * FROM s1.t1 WHERE t1.c1 = 1;
732 /*+SeqScan(t1)*/ SELECT * FROM s1.t1 WHERE t1.c1 = 1;
733 SELECT s.query, s.calls
734 FROM public.pg_stat_statements s
735 JOIN pg_catalog.pg_database d
740 ---- No. A-10-1 duplicate hint
744 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)';
746 Set(enable_tidscan aaa)
747 Set(enable_tidscan on)
748 Set(enable_tidscan off)
760 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)';
763 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)';
766 Set(enable_tidscan aaa)
770 Set(enable_tidscan on)
774 Set(enable_tidscan off)
779 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)';
782 ---- No. A-10-2 restrict query type
786 EXPLAIN (COSTS false) SELECT * FROM s1.t1 FULL OUTER JOIN s1.t2 ON (t1.c1 = t2.c1);
788 EXPLAIN (COSTS false) SELECT * FROM s1.t1 FULL OUTER JOIN s1.t2 ON (t1.c1 = t2.c1);
789 /*+MergeJoin(t1 t2)*/
790 EXPLAIN (COSTS false) SELECT * FROM s1.t1 FULL OUTER JOIN s1.t2 ON (t1.c1 = t2.c1);
792 EXPLAIN (COSTS true) SELECT * FROM s1.t1 FULL OUTER JOIN s1.t2 ON (t1.c1 = t2.c1);
795 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c3 = 1 AND t1.ctid = '(1,1)';
797 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c3 = 1 AND t1.ctid = '(1,1)';
798 /*+IndexScan(t1 t1_i)*/
799 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c3 = 1 AND t1.ctid = '(1,1)';
800 /*+IndexScan(t1 t1_i1)*/
801 EXPLAIN (COSTS true) SELECT * FROM s1.t1 WHERE t1.c3 = 1 AND t1.ctid = '(1,1)';
804 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
806 EXPLAIN (COSTS true) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
808 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
811 ---- No. A-10-3 VIEW, RULE multi specified
815 EXPLAIN (COSTS false) SELECT * FROM s1.v1 v1, s1.v1 v2 WHERE v1.c1 = v2.c1;
816 /*+Leading(v1t1 v1t1)HashJoin(v1t1 v1t1)BitmapScan(v1t1)*/
817 EXPLAIN (COSTS false) SELECT * FROM s1.v1 v1, s1.v1 v2 WHERE v1.c1 = v2.c1;
820 EXPLAIN (COSTS false) SELECT * FROM s1.v1 v1, s1.v1_ v2 WHERE v1.c1 = v2.c1;
821 /*+Leading(v1t1 v1t1_)NestLoop(v1t1 v1t1_)SeqScan(v1t1)BitmapScan(v1t1_)*/
822 EXPLAIN (COSTS false) SELECT * FROM s1.v1 v1, s1.v1_ v2 WHERE v1.c1 = v2.c1;
825 EXPLAIN (COSTS false) SELECT * FROM s1.r4 t1, s1.r4 t2 WHERE t1.c1 = t2.c1;
826 /*+Leading(r4t1 r4t1)HashJoin(r4t1 r4t1)BitmapScan(r4t1)*/
827 EXPLAIN (COSTS false) SELECT * FROM s1.r4 t1, s1.r4 t2 WHERE t1.c1 = t2.c1;
830 EXPLAIN (COSTS false) SELECT * FROM s1.r4 t1, s1.r5 t2 WHERE t1.c1 = t2.c1;
831 /*+Leading(r4t1 r5t1)NestLoop(r4t1 r5t1)SeqScan(r4t1)BitmapScan(r5t1)*/
832 EXPLAIN (COSTS false) SELECT * FROM s1.r4 t1, s1.r5 t2 WHERE t1.c1 = t2.c1;
835 ---- No. A-11-1 psql command
838 SELECT count(*) FROM s1.t1 WHERE t1.c1 = 1;
840 SELECT count(*) FROM s1.t1 WHERE t1.c1 = 1;
844 SELECT count(*) FROM s1.t1 WHERE t1.c1 = 1;
848 SELECT count(*) FROM s1.t1 WHERE t1.c1 = 1;
852 ---- No. A-12-4 PL/pgSQL function
856 CREATE OR REPLACE FUNCTION f1() RETURNS SETOF text LANGUAGE plpgsql AS $$
860 FOR r IN EXPLAIN SELECT c4 FROM s1.t1 WHERE t1.c1 = 1
862 RETURN NEXT r; -- return current row of SELECT
872 /*+SeqScan(t1)*/CREATE OR REPLACE FUNCTION f1() RETURNS SETOF text LANGUAGE plpgsql AS $$
876 /*+SeqScan(t1)*/FOR r IN EXPLAIN /*+SeqScan(t1)*/SELECT c4 FROM s1.t1 WHERE t1.c1 = 1
878 /*+SeqScan(t1)*/RETURN NEXT r; -- return current row of SELECT
880 /*+SeqScan(t1)*/RETURN;
886 ---- No. A-12-1 reset of global variable of core at the error
887 ---- No. A-12-2 reset of global variable of original at the error
890 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
891 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)NestLoop(t1 t1)*/
892 PREPARE p1 AS SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
893 EXPLAIN (COSTS false) EXECUTE p1;
897 SELECT name, setting FROM settings;
898 SET pg_hint_plan.parse_messages TO error;
899 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)NestLoop(t1 t1)*/
900 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
901 SELECT name, setting FROM settings;
902 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)*/
903 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
907 SELECT name, setting FROM settings;
908 SET pg_hint_plan.parse_messages TO error;
909 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)NestLoop(t1 t1)*/
910 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
911 SELECT name, setting FROM settings;
912 EXPLAIN (COSTS false) EXECUTE p1;
916 SELECT name, setting FROM settings;
917 SET pg_hint_plan.parse_messages TO error;
918 EXPLAIN (COSTS false) EXECUTE p2;
919 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)*/
920 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
921 EXPLAIN (COSTS false) EXECUTE p1;
922 SELECT name, setting FROM settings;
926 SELECT name, setting FROM settings;
927 SET pg_hint_plan.parse_messages TO error;
928 EXPLAIN (COSTS false) EXECUTE p2;
929 EXPLAIN (COSTS false) EXECUTE p1;
930 SELECT name, setting FROM settings;
933 SET pg_hint_plan.parse_messages TO LOG;
936 ---- No. A-12-3 effective range of the hint
939 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
942 SET enable_indexscan TO off;
943 SET enable_mergejoin TO off;
944 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
945 SELECT name, setting FROM settings;
946 /*+Set(enable_indexscan on)Set(geqo_threshold 100)IndexScan(t2)MergeJoin(t1 t2)Leading(t2 t1)*/
947 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
948 SELECT name, setting FROM settings;
949 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
952 SET enable_indexscan TO off;
953 SET enable_mergejoin TO off;
954 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
955 SELECT name, setting FROM settings;
957 /*+Set(enable_indexscan on)Set(geqo_threshold 100)IndexScan(t2)MergeJoin(t1 t2)Leading(t2 t1)*/
958 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
961 SELECT name, setting FROM settings;
962 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
966 SET enable_indexscan TO off;
967 SET enable_mergejoin TO off;
968 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
969 SELECT name, setting FROM settings;
970 /*+Set(enable_indexscan on)Set(geqo_threshold 100)IndexScan(t2)MergeJoin(t1 t2)Leading(t2 t1)*/
971 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
974 SELECT name, setting FROM settings;
975 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
977 SET pg_hint_plan.enable TO on;
978 SET pg_hint_plan.debug_print TO on;
979 SET client_min_messages TO LOG;
980 SET search_path TO public;