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-2-1 uninstall
11 CREATE EXTENSION pg_hint_plan;
13 DROP EXTENSION pg_hint_plan;
15 CREATE SCHEMA other_schema;
16 CREATE EXTENSION pg_hint_plan SCHEMA other_schema;
17 ERROR: extension "pg_hint_plan" must be installed in schema "hint_plan"
18 CREATE EXTENSION pg_hint_plan;
19 DROP SCHEMA other_schema;
21 ---- No. A-5-1 comment pattern
25 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
41 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
43 ------------------------------
44 Index Scan using t1_i1 on t1
50 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
52 ------------------------------
53 Index Scan using t1_i1 on t1
59 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
61 ------------------------------
62 Index Scan using t1_i1 on t1
67 /* /*+SeqScan(t1)*/ */
68 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
70 ------------------------------
71 Index Scan using t1_i1 on t1
76 ---- No. A-5-2 hint position
79 EXPLAIN (COSTS false) SELECT c1 FROM s1.t1 WHERE t1.c1 = 1;
81 -----------------------------------
82 Index Only Scan using t1_i1 on t1
87 EXPLAIN (COSTS false) SELECT c1 FROM s1.t1 WHERE t1.c1 = 1;
102 EXPLAIN (COSTS false) SELECT c1, c2 AS c_2 /*+SeqScan(t1)*/ FROM s1.t1 WHERE t1.c1 = 1;
117 EXPLAIN (COSTS false) SELECT c1 AS "c1"/*+SeqScan(t1)*/ FROM s1.t1 WHERE t1.c1 = 1;
119 -----------------------------------
120 Index Only Scan using t1_i1 on t1
125 EXPLAIN (COSTS false) SELECT * /*+SeqScan(t1)*/ FROM s1.t1 WHERE t1.c1 = 1;
127 ------------------------------
128 Index Scan using t1_i1 on t1
133 ---- No. A-6-1 hint's table definition
135 SET pg_hint_plan.enable_hint_table TO on;
138 Table "hint_plan.hints"
139 Column | Type | Collation | Nullable | Default
140 -------------------+---------+-----------+----------+---------------------------------------------
141 id | integer | | not null | nextval('hint_plan.hints_id_seq'::regclass)
142 norm_query_string | text | | not null |
143 application_name | text | | not null |
144 hints | text | | not null |
146 "hints_pkey" PRIMARY KEY, btree (id)
147 "hints_norm_and_app" UNIQUE, btree (norm_query_string, application_name)
150 ---- No. A-6-2 search condition
152 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
154 ------------------------------
155 Index Scan using t1_i1 on t1
160 INSERT INTO hint_plan.hints (norm_query_string, application_name, hints)
162 'EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = ?;',
165 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
180 INSERT INTO hint_plan.hints (norm_query_string, application_name, hints)
182 'EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = ?;',
185 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
199 TRUNCATE hint_plan.hints;
201 INSERT INTO hint_plan.hints (norm_query_string, application_name, hints)
203 'EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = ?;',
204 'dummy_application_name',
207 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
209 ------------------------------
210 Index Scan using t1_i1 on t1
214 TRUNCATE hint_plan.hints;
216 INSERT INTO hint_plan.hints (norm_query_string, application_name, hints)
218 'EXPLAIN (COSTS false) SELECT * FROM s1.t1;',
222 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
224 ------------------------------
225 Index Scan using t1_i1 on t1
229 TRUNCATE hint_plan.hints;
231 ---- No. A-6-3 number of constant
234 INSERT INTO hint_plan.hints (norm_query_string, application_name, hints)
236 'EXPLAIN (COSTS false) SELECT c1 FROM s1.t1;',
240 EXPLAIN (COSTS false) SELECT c1 FROM s1.t1;
253 TRUNCATE hint_plan.hints;
255 INSERT INTO hint_plan.hints (norm_query_string, application_name, hints)
257 'EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = ?;',
261 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
275 TRUNCATE hint_plan.hints;
277 INSERT INTO hint_plan.hints (norm_query_string, application_name, hints)
279 'EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = ? OR t1.c1 = ?;',
283 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 OR t1.c1 = 0;
292 ----------------------------------
294 Filter: ((c1 = 1) OR (c1 = 0))
297 TRUNCATE hint_plan.hints;
298 SET pg_hint_plan.enable_hint_table TO off;
300 ---- No. A-7-2 hint delimiter
302 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
304 ------------------------------
305 Index Scan using t1_i1 on t1
316 /*+Set(enable_indexscan"off")Set(enable_bitmapscan"off")*/
317 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
320 Set(enable_bitmapscan off)
321 Set(enable_indexscan off)
333 /*+ Set(enable_indexscan"off")Set(enable_bitmapscan"off")*/
334 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
337 Set(enable_bitmapscan off)
338 Set(enable_indexscan off)
350 /*+Set(enable_indexscan"off")Set(enable_bitmapscan"off") */
351 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
354 Set(enable_bitmapscan off)
355 Set(enable_indexscan off)
367 /*+ Set (enable_indexscan"off") Set (enable_bitmapscan"off")*/
368 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
371 Set(enable_bitmapscan off)
372 Set(enable_indexscan off)
384 /*+Set ( enable_indexscan"off")Set ( enable_bitmapscan"off")*/
385 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
388 Set(enable_bitmapscan off)
389 Set(enable_indexscan off)
401 /*+Set(enable_indexscan"off" ) Set(enable_bitmapscan"off" ) */
402 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
405 Set(enable_bitmapscan off)
406 Set(enable_indexscan off)
418 /*+Set( enable_indexscan "off" )Set( enable_bitmapscan "off" )*/
419 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
422 Set(enable_bitmapscan off)
423 Set(enable_indexscan off)
435 /*+ Set ( enable_indexscan "off" ) Set ( enable_bitmapscan "off" ) */
436 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
439 Set(enable_bitmapscan off)
440 Set(enable_indexscan off)
452 /*+ Set(enable_indexscan"off")Set(enable_bitmapscan"off")*/
453 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
456 Set(enable_bitmapscan off)
457 Set(enable_indexscan off)
469 /*+Set(enable_indexscan"off")Set(enable_bitmapscan"off") */
470 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
473 Set(enable_bitmapscan off)
474 Set(enable_indexscan off)
486 /*+ Set (enable_indexscan"off") Set (enable_bitmapscan"off")*/
487 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
490 Set(enable_bitmapscan off)
491 Set(enable_indexscan off)
503 /*+Set ( enable_indexscan"off")Set ( enable_bitmapscan"off")*/
504 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
507 Set(enable_bitmapscan off)
508 Set(enable_indexscan off)
520 /*+Set(enable_indexscan"off" ) Set(enable_bitmapscan"off" ) */
521 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
524 Set(enable_bitmapscan off)
525 Set(enable_indexscan off)
537 /*+Set( enable_indexscan "off" )Set( enable_bitmapscan "off" )*/
538 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
541 Set(enable_bitmapscan off)
542 Set(enable_indexscan off)
554 /*+ Set ( enable_indexscan "off" ) Set ( enable_bitmapscan "off" ) */
555 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
558 Set(enable_bitmapscan off)
559 Set(enable_indexscan off)
572 Set(enable_indexscan"off")Set(enable_bitmapscan"off")*/
573 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
576 Set(enable_bitmapscan off)
577 Set(enable_indexscan off)
589 /*+Set(enable_indexscan"off")Set(enable_bitmapscan"off")
591 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
594 Set(enable_bitmapscan off)
595 Set(enable_indexscan off)
609 (enable_indexscan"off")
611 (enable_bitmapscan"off")*/
612 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
615 Set(enable_bitmapscan off)
616 Set(enable_indexscan off)
630 enable_indexscan"off")Set
632 enable_bitmapscan"off")*/
633 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
636 Set(enable_bitmapscan off)
637 Set(enable_indexscan off)
649 /*+Set(enable_indexscan"off"
651 Set(enable_bitmapscan"off"
654 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
657 Set(enable_bitmapscan off)
658 Set(enable_indexscan off)
677 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
680 Set(enable_bitmapscan off)
681 Set(enable_indexscan off)
705 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
708 Set(enable_bitmapscan off)
709 Set(enable_indexscan off)
722 Set(enable_indexscan"off")Set(enable_bitmapscan"off")*/
723 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
726 Set(enable_bitmapscan off)
727 Set(enable_indexscan off)
739 /*+Set(enable_indexscan"off")Set(enable_bitmapscan"off")
741 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
744 Set(enable_bitmapscan off)
745 Set(enable_indexscan off)
759 (enable_indexscan"off")
761 (enable_bitmapscan"off")*/
762 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
765 Set(enable_bitmapscan off)
766 Set(enable_indexscan off)
780 enable_indexscan"off")Set
782 enable_bitmapscan"off")*/
783 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
786 Set(enable_bitmapscan off)
787 Set(enable_indexscan off)
799 /*+Set(enable_indexscan"off"
801 Set(enable_bitmapscan"off"
804 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
807 Set(enable_bitmapscan off)
808 Set(enable_indexscan off)
827 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
830 Set(enable_bitmapscan off)
831 Set(enable_indexscan off)
855 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
858 Set(enable_bitmapscan off)
859 Set(enable_indexscan off)
871 ---- No. A-7-3 hint object pattern
872 ---- No. A-9-2 message object pattern
877 EXPLAIN (COSTS false) SELECT * FROM s1.t1 t WHERE t.c1 = 1;
892 EXPLAIN (COSTS false) SELECT * FROM s1.t1 ttt WHERE ttt.c1 = 1;
907 EXPLAIN (COSTS false) SELECT * FROM s1.t1 t WHERE t.c1 = 1;
922 EXPLAIN (COSTS false) SELECT * FROM s1.t1 ttt WHERE ttt.c1 = 1;
939 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "T" WHERE "T".c1 = 1;
954 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "TTT" WHERE "TTT".c1 = 1;
963 ----------------------
969 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "T" WHERE "T".c1 = 1;
984 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "TTT" WHERE "TTT".c1 = 1;
993 ----------------------
1001 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "(" WHERE "(".c1 = 1;
1002 INFO: pg_hint_plan: hint syntax error at or near "()"
1003 DETAIL: Zero-length delimited string.
1005 ----------------------------------
1006 Index Scan using t1_i1 on t1 "("
1007 Index Cond: (c1 = 1)
1011 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "(" WHERE "(".c1 = 1;
1020 --------------------
1028 EXPLAIN (COSTS false) SELECT * FROM s1.t1 ")" WHERE ")".c1 = 1;
1029 INFO: pg_hint_plan: hint syntax error at or near ")"
1030 DETAIL: SeqScan hint requires a relation.
1031 INFO: pg_hint_plan: hint syntax error at or near ")"
1032 DETAIL: Unrecognized hint keyword ")".
1041 ----------------------------------
1042 Index Scan using t1_i1 on t1 ")"
1043 Index Cond: (c1 = 1)
1047 EXPLAIN (COSTS false) SELECT * FROM s1.t1 ")" WHERE ")".c1 = 1;
1056 --------------------
1062 EXPLAIN (COSTS false) SELECT * FROM s1.t1 ")))" WHERE ")))".c1 = 1;
1071 ----------------------
1072 Seq Scan on t1 ")))"
1079 EXPLAIN (COSTS false) SELECT * FROM s1.t1 """" WHERE """".c1 = 1;
1080 INFO: pg_hint_plan: hint syntax error at or near ""
1081 DETAIL: Unterminated quoted string.
1083 -----------------------------------
1084 Index Scan using t1_i1 on t1 """"
1085 Index Cond: (c1 = 1)
1089 EXPLAIN (COSTS false) SELECT * FROM s1.t1 """" WHERE """".c1 = 1;
1098 ---------------------
1103 /*+SeqScan("""""""")*/
1104 EXPLAIN (COSTS false) SELECT * FROM s1.t1 """""""" WHERE """""""".c1 = 1;
1113 -------------------------
1114 Seq Scan on t1 """"""""
1121 EXPLAIN (COSTS false) SELECT * FROM s1.t1 " " WHERE " ".c1 = 1;
1122 INFO: pg_hint_plan: hint syntax error at or near ""
1123 DETAIL: SeqScan hint requires a relation.
1132 ----------------------------------
1133 Index Scan using t1_i1 on t1 " "
1134 Index Cond: (c1 = 1)
1138 EXPLAIN (COSTS false) SELECT * FROM s1.t1 " " WHERE " ".c1 = 1;
1147 --------------------
1153 EXPLAIN (COSTS false) SELECT * FROM s1.t1 " " WHERE " ".c1 = 1;
1162 ----------------------
1170 EXPLAIN (COSTS false) SELECT * FROM s1.t1 " " WHERE " ".c1 = 1;
1171 INFO: pg_hint_plan: hint syntax error at or near ""
1172 DETAIL: SeqScan hint requires a relation.
1181 -----------------------------------
1182 Index Scan using t1_i1 on t1 " "
1183 Index Cond: (c1 = 1)
1187 EXPLAIN (COSTS false) SELECT * FROM s1.t1 " " WHERE " ".c1 = 1;
1196 ---------------------------
1202 EXPLAIN (COSTS false) SELECT * FROM s1.t1 " " WHERE " ".c1 = 1;
1211 -------------------------------------------
1220 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "
1223 INFO: pg_hint_plan: hint syntax error at or near ""
1224 DETAIL: SeqScan hint requires a relation.
1233 --------------------------------
1234 Index Scan using t1_i1 on t1 "
1236 Index Cond: (c1 = 1)
1241 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "
1253 --------------------
1263 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "
1281 --------------------
1292 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "Set" WHERE "Set".c1 = 1;
1301 ----------------------
1302 Seq Scan on t1 "Set"
1307 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "Set" WHERE "Set".c1 = 1;
1316 ----------------------
1317 Seq Scan on t1 "Set"
1321 /*+SeqScan("Set SeqScan Leading")*/
1322 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "Set SeqScan Leading" WHERE "Set SeqScan Leading".c1 = 1;
1325 SeqScan("Set SeqScan Leading")
1331 --------------------------------------
1332 Seq Scan on t1 "Set SeqScan Leading"
1339 EXPLAIN (COSTS false) SELECT * FROM s1.t1 あ WHERE あ.c1 = 1;
1348 ---------------------
1354 EXPLAIN (COSTS false) SELECT * FROM s1.t1 あいう WHERE あいう.c1 = 1;
1363 -------------------------
1364 Seq Scan on t1 "あいう"
1369 EXPLAIN (COSTS false) SELECT * FROM s1.t1 あ WHERE あ.c1 = 1;
1378 ---------------------
1384 EXPLAIN (COSTS false) SELECT * FROM s1.t1 あいう WHERE あいう.c1 = 1;
1393 -------------------------
1394 Seq Scan on t1 "あいう"
1401 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "/**/" WHERE "/**/".c1 = 1;
1402 INFO: pg_hint_plan: hint syntax error at or near "/**/)*/
1403 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "/**/" WHERE "/**/".c1 = 1;"
1404 DETAIL: Nested block comments are not supported.
1406 -------------------------------------
1407 Index Scan using t1_i1 on t1 "/**/"
1408 Index Cond: (c1 = 1)
1411 /*+SeqScan(/**//**//**/)*/
1412 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "/**//**//**/" WHERE "/**//**//**/".c1 = 1;
1413 INFO: pg_hint_plan: hint syntax error at or near "/**//**//**/)*/
1414 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "/**//**//**/" WHERE "/**//**//**/".c1 = 1;"
1415 DETAIL: Nested block comments are not supported.
1417 ---------------------------------------------
1418 Index Scan using t1_i1 on t1 "/**//**//**/"
1419 Index Cond: (c1 = 1)
1426 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "tT()""
1427 Set/**/あ" WHERE "tT()""
1429 INFO: pg_hint_plan: hint syntax error at or near "/**/あ")*/
1430 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "tT()""
1431 Set/**/あ" WHERE "tT()""
1433 DETAIL: Nested block comments are not supported.
1435 ------------------------------------------
1436 Index Scan using t1_i1 on t1 "tT()""
1438 Index Cond: (c1 = 1)
1444 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "tT()""
1456 --------------------------
1457 Seq Scan on t1 "tT()""
1464 /*+SeqScan(a123456789b123456789c123456789d123456789e123456789f123)*/
1465 EXPLAIN (COSTS false) SELECT * FROM s1.t1 "123456789012345678901234567890123456789012345678901234" WHERE "123456789012345678901234567890123456789012345678901234".c1 = 1;
1469 SeqScan(a123456789b123456789c123456789d123456789e123456789f123)
1474 ---------------------------------------------------------------------------------------
1475 Index Scan using t1_i1 on t1 "123456789012345678901234567890123456789012345678901234"
1476 Index Cond: (c1 = 1)
1480 ---- No. A-7-4 hint parse error
1483 /*+Set(enable_indexscan off)Set enable_tidscan off)Set(enable_bitmapscan off)SeqScan(t1)*/
1484 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
1485 INFO: pg_hint_plan: hint syntax error at or near "enable_tidscan off)Set(enable_bitmapscan off)SeqScan(t1)"
1486 DETAIL: Opening parenthesis is necessary.
1489 Set(enable_indexscan off)
1495 ----------------------------------
1496 Bitmap Heap Scan on t1
1497 Recheck Cond: (c1 = 1)
1498 -> Bitmap Index Scan on t1_i1
1499 Index Cond: (c1 = 1)
1503 /*+Set(enable_indexscan off)Set(enable_tidscan off Set(enable_bitmapscan off)SeqScan(t1)*/
1504 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
1505 INFO: pg_hint_plan: hint syntax error at or near "(enable_bitmapscan off)SeqScan(t1)"
1506 DETAIL: Zero-length delimited string.
1509 Set(enable_indexscan off)
1515 ----------------------------------
1516 Bitmap Heap Scan on t1
1517 Recheck Cond: (c1 = 1)
1518 -> Bitmap Index Scan on t1_i1
1519 Index Cond: (c1 = 1)
1523 /*+Set(enable_indexscan off)Set(enable_tidscan "off)Set(enable_bitmapscan off)SeqScan(t1)*/
1524 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
1525 INFO: pg_hint_plan: hint syntax error at or near ""
1526 DETAIL: Unterminated quoted string.
1529 Set(enable_indexscan off)
1535 ----------------------------------
1536 Bitmap Heap Scan on t1
1537 Recheck Cond: (c1 = 1)
1538 -> Bitmap Index Scan on t1_i1
1539 Index Cond: (c1 = 1)
1543 /*+Set(enable_indexscan off)SeqScan("")Set(enable_bitmapscan off)*/
1544 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
1545 INFO: pg_hint_plan: hint syntax error at or near ")Set(enable_bitmapscan off)"
1546 DETAIL: Zero-length delimited string.
1549 Set(enable_indexscan off)
1555 ----------------------------------
1556 Bitmap Heap Scan on t1
1557 Recheck Cond: (c1 = 1)
1558 -> Bitmap Index Scan on t1_i1
1559 Index Cond: (c1 = 1)
1563 /*+Set(enable_indexscan off)NoSet(enable_tidscan off)Set(enable_bitmapscan off)SeqScan(t1)*/
1564 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
1565 INFO: pg_hint_plan: hint syntax error at or near "NoSet(enable_tidscan off)Set(enable_bitmapscan off)SeqScan(t1)"
1566 DETAIL: Unrecognized hint keyword "NoSet".
1569 Set(enable_indexscan off)
1575 ----------------------------------
1576 Bitmap Heap Scan on t1
1577 Recheck Cond: (c1 = 1)
1578 -> Bitmap Index Scan on t1_i1
1579 Index Cond: (c1 = 1)
1583 /*+Set(enable_indexscan off)"Set"(enable_tidscan off)Set(enable_bitmapscan off)SeqScan(t1)*/
1584 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
1585 INFO: pg_hint_plan: hint syntax error at or near ""Set"(enable_tidscan off)Set(enable_bitmapscan off)SeqScan(t1)"
1586 DETAIL: Unrecognized hint keyword ""Set"".
1589 Set(enable_indexscan off)
1595 ----------------------------------
1596 Bitmap Heap Scan on t1
1597 Recheck Cond: (c1 = 1)
1598 -> Bitmap Index Scan on t1_i1
1599 Index Cond: (c1 = 1)
1603 /*+Set(enable_indexscan off)Set(enable_tidscan /* value */off)Set(enable_bitmapscan off)SeqScan(t1)*/
1604 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
1605 INFO: pg_hint_plan: hint syntax error at or near "/* value */off)Set(enable_bitmapscan off)SeqScan(t1)*/
1606 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;"
1607 DETAIL: Nested block comments are not supported.
1609 ------------------------------
1610 Index Scan using t1_i1 on t1
1611 Index Cond: (c1 = 1)
1615 ---- No. A-8-1 original GUC parameter
1617 ---- Don't test postgresql itself.
1619 -- SET ROLE super_user;
1620 -- SET pg_hint_plan.debug_print TO off;
1621 -- SHOW pg_hint_plan.enable_hint;
1622 -- SHOW pg_hint_plan.debug_print;
1623 -- SHOW pg_hint_plan.parse_messages;
1624 -- SET pg_hint_plan.enable_hint TO off;
1625 -- SET pg_hint_plan.debug_print TO on;
1626 -- SET pg_hint_plan.parse_messages TO error;
1627 -- SHOW pg_hint_plan.enable_hint;
1628 -- SHOW pg_hint_plan.debug_print;
1629 -- SHOW pg_hint_plan.parse_messages;
1630 -- RESET pg_hint_plan.enable_hint;
1631 -- RESET pg_hint_plan.debug_print;
1632 -- RESET pg_hint_plan.parse_messages;
1633 -- SHOW pg_hint_plan.enable_hint;
1634 -- SHOW pg_hint_plan.debug_print;
1635 -- SHOW pg_hint_plan.parse_messages;
1638 -- SET ROLE normal_user;
1639 -- SHOW pg_hint_plan.enable_hint;
1640 -- SHOW pg_hint_plan.debug_print;
1641 -- SHOW pg_hint_plan.parse_messages;
1642 -- SET pg_hint_plan.enable_hint TO off;
1643 -- SET pg_hint_plan.debug_print TO on;
1644 -- SET pg_hint_plan.parse_messages TO error;
1645 -- SHOW pg_hint_plan.enable_hint;
1646 -- SHOW pg_hint_plan.debug_print;
1647 -- SHOW pg_hint_plan.parse_messages;
1648 -- RESET pg_hint_plan.enable_hint;
1649 -- RESET pg_hint_plan.debug_print;
1650 -- RESET pg_hint_plan.parse_messages;
1651 -- SHOW pg_hint_plan.enable_hint;
1652 -- SHOW pg_hint_plan.debug_print;
1653 -- SHOW pg_hint_plan.parse_messages;
1657 ---- No. A-8-2 original GUC parameter pg_hint_plan.enable_hint
1660 SET pg_hint_plan.debug_print TO off;
1661 SET pg_hint_plan.enable_hint TO on;
1662 SHOW pg_hint_plan.enable_hint;
1663 pg_hint_plan.enable_hint
1664 --------------------------
1668 /*+Set(enable_indexscan off)*/
1669 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
1671 ----------------------------------
1672 Bitmap Heap Scan on t1
1673 Recheck Cond: (c1 = 1)
1674 -> Bitmap Index Scan on t1_i1
1675 Index Cond: (c1 = 1)
1679 SET pg_hint_plan.enable_hint TO off;
1680 SHOW pg_hint_plan.enable_hint;
1681 pg_hint_plan.enable_hint
1682 --------------------------
1686 /*+Set(enable_indexscan off)*/
1687 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
1689 ------------------------------
1690 Index Scan using t1_i1 on t1
1691 Index Cond: (c1 = 1)
1695 -- Don't test PostgreSQL itself.
1696 -- SET pg_hint_plan.enable_hint TO DEFAULT;
1697 -- SHOW pg_hint_plan.enable_hint;
1698 -- /*+Set(enable_indexscan off)*/
1699 -- EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
1701 -- Don't test PostgreSQL itself
1702 -- SET pg_hint_plan.enable_hint TO enable;
1703 -- SHOW pg_hint_plan.enable_hint;
1705 ---- No. A-8-3 original GUC parameter pg_hint_plan.debug_print
1708 SET pg_hint_plan.enable_hint TO on;
1709 SHOW pg_hint_plan.enable_hint;
1710 pg_hint_plan.enable_hint
1711 --------------------------
1715 SET pg_hint_plan.debug_print TO on;
1716 SHOW pg_hint_plan.debug_print;
1717 pg_hint_plan.debug_print
1718 --------------------------
1722 /*+Set(enable_indexscan off)*/
1723 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
1726 Set(enable_indexscan off)
1732 ----------------------------------
1733 Bitmap Heap Scan on t1
1734 Recheck Cond: (c1 = 1)
1735 -> Bitmap Index Scan on t1_i1
1736 Index Cond: (c1 = 1)
1740 SET pg_hint_plan.debug_print TO off;
1741 SHOW pg_hint_plan.debug_print;
1742 pg_hint_plan.debug_print
1743 --------------------------
1747 /*+Set(enable_indexscan off)*/
1748 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
1750 ----------------------------------
1751 Bitmap Heap Scan on t1
1752 Recheck Cond: (c1 = 1)
1753 -> Bitmap Index Scan on t1_i1
1754 Index Cond: (c1 = 1)
1758 SET pg_hint_plan.debug_print TO DEFAULT;
1759 SHOW pg_hint_plan.debug_print;
1760 pg_hint_plan.debug_print
1761 --------------------------
1765 /*+Set(enable_indexscan off)*/
1766 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
1768 ----------------------------------
1769 Bitmap Heap Scan on t1
1770 Recheck Cond: (c1 = 1)
1771 -> Bitmap Index Scan on t1_i1
1772 Index Cond: (c1 = 1)
1776 SET pg_hint_plan.debug_print TO enable;
1777 ERROR: invalid value for parameter "pg_hint_plan.debug_print": "enable"
1778 HINT: Available values: off, on, detailed, verbose.
1779 SHOW pg_hint_plan.debug_print;
1780 pg_hint_plan.debug_print
1781 --------------------------
1786 ---- No. A-8-4 original GUC parameter pg_hint_plan.parse_messages
1788 SET client_min_messages TO debug5;
1789 DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 0/1/0
1791 SET pg_hint_plan.parse_messages TO debug5;
1792 DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0
1793 DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 0/1/0
1794 SHOW pg_hint_plan.parse_messages;
1795 DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0
1796 DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 0/1/0
1797 pg_hint_plan.parse_messages
1798 -----------------------------
1803 DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0
1804 DEBUG: pg_hint_plan: hint syntax error at or near ""
1805 DETAIL: Opening parenthesis is necessary.
1806 DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 0/1/0
1812 SET client_min_messages TO debug4;
1813 DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0
1821 SET pg_hint_plan.parse_messages TO debug4;
1822 SHOW pg_hint_plan.parse_messages;
1823 pg_hint_plan.parse_messages
1824 -----------------------------
1829 DEBUG: pg_hint_plan: hint syntax error at or near ""
1830 DETAIL: Opening parenthesis is necessary.
1836 SET client_min_messages TO debug3;
1844 SET pg_hint_plan.parse_messages TO debug3;
1845 SHOW pg_hint_plan.parse_messages;
1846 pg_hint_plan.parse_messages
1847 -----------------------------
1852 DEBUG: pg_hint_plan: hint syntax error at or near ""
1853 DETAIL: Opening parenthesis is necessary.
1859 SET client_min_messages TO debug2;
1867 SET pg_hint_plan.parse_messages TO debug2;
1868 SHOW pg_hint_plan.parse_messages;
1869 pg_hint_plan.parse_messages
1870 -----------------------------
1875 DEBUG: pg_hint_plan: hint syntax error at or near ""
1876 DETAIL: Opening parenthesis is necessary.
1882 SET client_min_messages TO debug1;
1890 SET pg_hint_plan.parse_messages TO debug1;
1891 SHOW pg_hint_plan.parse_messages;
1892 pg_hint_plan.parse_messages
1893 -----------------------------
1898 DEBUG: pg_hint_plan: hint syntax error at or near ""
1899 DETAIL: Opening parenthesis is necessary.
1905 SET client_min_messages TO log;
1913 SET pg_hint_plan.parse_messages TO log;
1914 SHOW pg_hint_plan.parse_messages;
1915 pg_hint_plan.parse_messages
1916 -----------------------------
1921 LOG: pg_hint_plan: hint syntax error at or near ""
1922 DETAIL: Opening parenthesis is necessary.
1928 SET client_min_messages TO info;
1936 SET pg_hint_plan.parse_messages TO info;
1937 SHOW pg_hint_plan.parse_messages;
1938 pg_hint_plan.parse_messages
1939 -----------------------------
1944 INFO: pg_hint_plan: hint syntax error at or near ""
1945 DETAIL: Opening parenthesis is necessary.
1951 SET client_min_messages TO notice;
1953 INFO: pg_hint_plan: hint syntax error at or near ""
1954 DETAIL: Opening parenthesis is necessary.
1961 SET pg_hint_plan.parse_messages TO notice;
1962 SHOW pg_hint_plan.parse_messages;
1963 pg_hint_plan.parse_messages
1964 -----------------------------
1969 NOTICE: pg_hint_plan: hint syntax error at or near ""
1970 DETAIL: Opening parenthesis is necessary.
1976 SET client_min_messages TO warning;
1984 SET pg_hint_plan.parse_messages TO warning;
1985 SHOW pg_hint_plan.parse_messages;
1986 pg_hint_plan.parse_messages
1987 -----------------------------
1992 WARNING: pg_hint_plan: hint syntax error at or near ""
1993 DETAIL: Opening parenthesis is necessary.
1999 SET client_min_messages TO error;
2007 SET pg_hint_plan.parse_messages TO error;
2008 SHOW pg_hint_plan.parse_messages;
2009 pg_hint_plan.parse_messages
2010 -----------------------------
2015 ERROR: pg_hint_plan: hint syntax error at or near ""
2016 DETAIL: Opening parenthesis is necessary.
2017 SET client_min_messages TO fatal;
2020 RESET client_min_messages;
2021 SET pg_hint_plan.parse_messages TO DEFAULT;
2022 SHOW pg_hint_plan.parse_messages;
2023 pg_hint_plan.parse_messages
2024 -----------------------------
2029 INFO: pg_hint_plan: hint syntax error at or near ""
2030 DETAIL: Opening parenthesis is necessary.
2037 SET pg_hint_plan.parse_messages TO fatal;
2038 ERROR: invalid value for parameter "pg_hint_plan.parse_messages": "fatal"
2039 HINT: Available values: debug5, debug4, debug3, debug2, debug1, log, info, notice, warning, error.
2040 SHOW pg_hint_plan.parse_messages;
2041 pg_hint_plan.parse_messages
2042 -----------------------------
2047 SET pg_hint_plan.parse_messages TO panic;
2048 ERROR: invalid value for parameter "pg_hint_plan.parse_messages": "panic"
2049 HINT: Available values: debug5, debug4, debug3, debug2, debug1, log, info, notice, warning, error.
2050 SHOW pg_hint_plan.parse_messages;
2051 pg_hint_plan.parse_messages
2052 -----------------------------
2057 SET pg_hint_plan.parse_messages TO on;
2058 ERROR: invalid value for parameter "pg_hint_plan.parse_messages": "on"
2059 HINT: Available values: debug5, debug4, debug3, debug2, debug1, log, info, notice, warning, error.
2060 SHOW pg_hint_plan.parse_messages;
2061 pg_hint_plan.parse_messages
2062 -----------------------------
2067 ---- No. A-8-5 original GUC parameter pg_hint_plan.enable_hint_table
2069 INSERT INTO hint_plan.hints (norm_query_string, application_name, hints)
2071 'EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = ?;',
2075 SET pg_hint_plan.enable_hint_table TO on;
2076 SHOW pg_hint_plan.enable_hint_table;
2077 pg_hint_plan.enable_hint_table
2078 --------------------------------
2082 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
2084 --------------------
2090 SET pg_hint_plan.enable_hint_table TO off;
2091 SHOW pg_hint_plan.enable_hint_table;
2092 pg_hint_plan.enable_hint_table
2093 --------------------------------
2097 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
2099 ------------------------------
2100 Index Scan using t1_i1 on t1
2101 Index Cond: (c1 = 1)
2105 SET pg_hint_plan.enable_hint_table TO DEFAULT;
2106 SHOW pg_hint_plan.enable_hint_table;
2107 pg_hint_plan.enable_hint_table
2108 --------------------------------
2112 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
2114 ------------------------------
2115 Index Scan using t1_i1 on t1
2116 Index Cond: (c1 = 1)
2120 SET pg_hint_plan.enable_hint_table TO enable;
2121 ERROR: parameter "pg_hint_plan.enable_hint_table" requires a Boolean value
2122 SHOW pg_hint_plan.enable_hint_table;
2123 pg_hint_plan.enable_hint_table
2124 --------------------------------
2128 TRUNCATE hint_plan.hints;
2130 ---- No. A-9-1 parse error message output
2133 /*+"Set"(enable_indexscan on)*/SELECT 1;
2134 INFO: pg_hint_plan: hint syntax error at or near ""Set"(enable_indexscan on)"
2135 DETAIL: Unrecognized hint keyword ""Set"".
2141 /*+Set()(enable_indexscan on)*/SELECT 1;
2142 INFO: pg_hint_plan: hint syntax error at or near "Set()(enable_indexscan on)"
2143 DETAIL: Set hint requires name and value of GUC parameter.
2144 INFO: pg_hint_plan: hint syntax error at or near "(enable_indexscan on)"
2145 DETAIL: Unrecognized hint keyword "".
2151 /*+Set(enable_indexscan on*/SELECT 1;
2152 INFO: pg_hint_plan: hint syntax error at or near ""
2153 DETAIL: Closing parenthesis is necessary.
2160 ---- No. A-9-3 hint state output
2162 SET pg_hint_plan.debug_print TO on;
2163 SET client_min_messages TO LOG;
2166 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
2175 --------------------
2181 /*+SeqScan(no_table)*/
2182 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
2191 ------------------------------
2192 Index Scan using t1_i1 on t1
2193 Index Cond: (c1 = 1)
2197 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
2199 -----------------------------------
2201 TID Cond: (ctid = '(1,1)'::tid)
2205 /*+TidScan(t1)BitmapScan(t1)*/
2206 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
2207 INFO: pg_hint_plan: hint syntax error at or near "TidScan(t1)BitmapScan(t1)"
2208 DETAIL: Conflict scan method hint.
2218 ----------------------------------
2219 Bitmap Heap Scan on t1
2220 Recheck Cond: (c1 = 1)
2221 Filter: (ctid = '(1,1)'::tid)
2222 -> Bitmap Index Scan on t1_i1
2223 Index Cond: (c1 = 1)
2226 /*+TidScan(t1)BitmapScan(t1)IndexScan(t1)*/
2227 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
2228 INFO: pg_hint_plan: hint syntax error at or near "TidScan(t1)BitmapScan(t1)IndexScan(t1)"
2229 DETAIL: Conflict scan method hint.
2230 INFO: pg_hint_plan: hint syntax error at or near "BitmapScan(t1)IndexScan(t1)"
2231 DETAIL: Conflict scan method hint.
2242 ---------------------------------
2243 Index Scan using t1_i1 on t1
2244 Index Cond: (c1 = 1)
2245 Filter: (ctid = '(1,1)'::tid)
2248 /*+TidScan(t1)BitmapScan(t1)IndexScan(t1)SeqScan(t1)*/
2249 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
2250 INFO: pg_hint_plan: hint syntax error at or near "TidScan(t1)BitmapScan(t1)IndexScan(t1)SeqScan(t1)"
2251 DETAIL: Conflict scan method hint.
2252 INFO: pg_hint_plan: hint syntax error at or near "BitmapScan(t1)IndexScan(t1)SeqScan(t1)"
2253 DETAIL: Conflict scan method hint.
2254 INFO: pg_hint_plan: hint syntax error at or near "IndexScan(t1)SeqScan(t1)"
2255 DETAIL: Conflict scan method hint.
2267 ------------------------------------------------
2269 Filter: ((c1 = 1) AND (ctid = '(1,1)'::tid))
2273 /*+Set(enable_indexscan enable)*/
2274 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
2275 INFO: parameter "enable_indexscan" requires a Boolean value
2281 Set(enable_indexscan enable)
2284 ------------------------------
2285 Index Scan using t1_i1 on t1
2286 Index Cond: (c1 = 1)
2290 ---- No. A-10-1 hint state output
2292 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
2293 EXPLAIN (COSTS false) EXECUTE p1;
2295 ------------------------------
2296 Index Scan using t1_i1 on t1
2297 Index Cond: (c1 = 1)
2301 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
2302 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2304 -----------------------
2309 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2311 -----------------------
2316 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2318 -----------------------
2323 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2325 -----------------------
2330 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2332 -----------------------
2337 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2339 ---------------------
2348 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
2350 EXPLAIN (COSTS false) EXECUTE p1;
2359 --------------------
2364 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
2366 EXPLAIN (COSTS false) EXECUTE p1;
2375 --------------------
2382 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
2384 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2393 ----------------------------------
2394 Bitmap Heap Scan on t1
2395 Recheck Cond: (c1 < 1000)
2396 -> Bitmap Index Scan on t1_i1
2397 Index Cond: (c1 < 1000)
2401 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2410 ----------------------------------
2411 Bitmap Heap Scan on t1
2412 Recheck Cond: (c1 < 1000)
2413 -> Bitmap Index Scan on t1_i1
2414 Index Cond: (c1 < 1000)
2418 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2427 ----------------------------------
2428 Bitmap Heap Scan on t1
2429 Recheck Cond: (c1 < 1000)
2430 -> Bitmap Index Scan on t1_i1
2431 Index Cond: (c1 < 1000)
2435 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2444 ----------------------------------
2445 Bitmap Heap Scan on t1
2446 Recheck Cond: (c1 < 1000)
2447 -> Bitmap Index Scan on t1_i1
2448 Index Cond: (c1 < 1000)
2452 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2461 ----------------------------------
2462 Bitmap Heap Scan on t1
2463 Recheck Cond: (c1 < 1000)
2464 -> Bitmap Index Scan on t1_i1
2465 Index Cond: (c1 < 1000)
2469 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2478 ----------------------------------
2479 Bitmap Heap Scan on t1
2480 Recheck Cond: (c1 < $1)
2481 -> Bitmap Index Scan on t1_i1
2482 Index Cond: (c1 < $1)
2485 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
2487 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2496 ----------------------------------
2497 Bitmap Heap Scan on t1
2498 Recheck Cond: (c1 < $1)
2499 -> Bitmap Index Scan on t1_i1
2500 Index Cond: (c1 < $1)
2507 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
2508 EXPLAIN (COSTS false) EXECUTE p1;
2517 --------------------
2522 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
2523 EXPLAIN (COSTS false) EXECUTE p1;
2532 --------------------
2539 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
2540 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2549 ----------------------------------
2550 Bitmap Heap Scan on t1
2551 Recheck Cond: (c1 < 1000)
2552 -> Bitmap Index Scan on t1_i1
2553 Index Cond: (c1 < 1000)
2556 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2565 ----------------------------------
2566 Bitmap Heap Scan on t1
2567 Recheck Cond: (c1 < 1000)
2568 -> Bitmap Index Scan on t1_i1
2569 Index Cond: (c1 < 1000)
2572 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2581 ----------------------------------
2582 Bitmap Heap Scan on t1
2583 Recheck Cond: (c1 < 1000)
2584 -> Bitmap Index Scan on t1_i1
2585 Index Cond: (c1 < 1000)
2588 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2597 ----------------------------------
2598 Bitmap Heap Scan on t1
2599 Recheck Cond: (c1 < 1000)
2600 -> Bitmap Index Scan on t1_i1
2601 Index Cond: (c1 < 1000)
2604 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2613 ----------------------------------
2614 Bitmap Heap Scan on t1
2615 Recheck Cond: (c1 < 1000)
2616 -> Bitmap Index Scan on t1_i1
2617 Index Cond: (c1 < 1000)
2620 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2629 ----------------------------------
2630 Bitmap Heap Scan on t1
2631 Recheck Cond: (c1 < $1)
2632 -> Bitmap Index Scan on t1_i1
2633 Index Cond: (c1 < $1)
2636 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
2637 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2646 ----------------------------------
2647 Bitmap Heap Scan on t1
2648 Recheck Cond: (c1 < $1)
2649 -> Bitmap Index Scan on t1_i1
2650 Index Cond: (c1 < $1)
2656 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
2658 EXPLAIN (COSTS false) EXECUTE p1;
2660 ------------------------------
2661 Index Scan using t1_i1 on t1
2662 Index Cond: (c1 = 1)
2665 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
2667 EXPLAIN (COSTS false) EXECUTE p1;
2669 ------------------------------
2670 Index Scan using t1_i1 on t1
2671 Index Cond: (c1 = 1)
2675 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
2677 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2679 -----------------------
2684 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2686 -----------------------
2691 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2693 -----------------------
2698 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2700 -----------------------
2705 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2707 -----------------------
2712 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2714 ---------------------
2719 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
2721 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2723 ---------------------
2732 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
2734 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
2743 --------------------
2748 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
2750 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
2759 --------------------
2766 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
2768 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
2777 ----------------------------------
2778 Bitmap Heap Scan on t1
2779 Recheck Cond: (c1 < 1000)
2780 -> Bitmap Index Scan on t1_i1
2781 Index Cond: (c1 < 1000)
2785 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
2794 ----------------------------------
2795 Bitmap Heap Scan on t1
2796 Recheck Cond: (c1 < 1000)
2797 -> Bitmap Index Scan on t1_i1
2798 Index Cond: (c1 < 1000)
2802 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
2811 ----------------------------------
2812 Bitmap Heap Scan on t1
2813 Recheck Cond: (c1 < 1000)
2814 -> Bitmap Index Scan on t1_i1
2815 Index Cond: (c1 < 1000)
2819 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
2828 ----------------------------------
2829 Bitmap Heap Scan on t1
2830 Recheck Cond: (c1 < 1000)
2831 -> Bitmap Index Scan on t1_i1
2832 Index Cond: (c1 < 1000)
2836 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
2845 ----------------------------------
2846 Bitmap Heap Scan on t1
2847 Recheck Cond: (c1 < 1000)
2848 -> Bitmap Index Scan on t1_i1
2849 Index Cond: (c1 < 1000)
2853 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
2862 ----------------------------------
2863 Bitmap Heap Scan on t1
2864 Recheck Cond: (c1 < $1)
2865 -> Bitmap Index Scan on t1_i1
2866 Index Cond: (c1 < $1)
2869 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
2871 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
2880 ----------------------------------
2881 Bitmap Heap Scan on t1
2882 Recheck Cond: (c1 < $1)
2883 -> Bitmap Index Scan on t1_i1
2884 Index Cond: (c1 < $1)
2891 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
2892 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
2901 --------------------
2906 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
2907 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
2916 --------------------
2923 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
2924 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
2933 ----------------------------------
2934 Bitmap Heap Scan on t1
2935 Recheck Cond: (c1 < 1000)
2936 -> Bitmap Index Scan on t1_i1
2937 Index Cond: (c1 < 1000)
2940 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
2949 ----------------------------------
2950 Bitmap Heap Scan on t1
2951 Recheck Cond: (c1 < 1000)
2952 -> Bitmap Index Scan on t1_i1
2953 Index Cond: (c1 < 1000)
2956 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
2965 ----------------------------------
2966 Bitmap Heap Scan on t1
2967 Recheck Cond: (c1 < 1000)
2968 -> Bitmap Index Scan on t1_i1
2969 Index Cond: (c1 < 1000)
2972 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
2981 ----------------------------------
2982 Bitmap Heap Scan on t1
2983 Recheck Cond: (c1 < 1000)
2984 -> Bitmap Index Scan on t1_i1
2985 Index Cond: (c1 < 1000)
2988 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
2997 ----------------------------------
2998 Bitmap Heap Scan on t1
2999 Recheck Cond: (c1 < 1000)
3000 -> Bitmap Index Scan on t1_i1
3001 Index Cond: (c1 < 1000)
3004 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
3013 ----------------------------------
3014 Bitmap Heap Scan on t1
3015 Recheck Cond: (c1 < $1)
3016 -> Bitmap Index Scan on t1_i1
3017 Index Cond: (c1 < $1)
3020 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
3021 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
3030 ----------------------------------
3031 Bitmap Heap Scan on t1
3032 Recheck Cond: (c1 < $1)
3033 -> Bitmap Index Scan on t1_i1
3034 Index Cond: (c1 < $1)
3040 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
3042 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
3044 ------------------------------
3045 Index Scan using t1_i1 on t1
3046 Index Cond: (c1 = 1)
3049 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
3051 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
3053 ------------------------------
3054 Index Scan using t1_i1 on t1
3055 Index Cond: (c1 = 1)
3059 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
3061 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
3063 -----------------------
3069 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
3071 -----------------------
3077 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
3079 -----------------------
3085 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
3087 -----------------------
3093 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
3095 -----------------------
3101 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
3103 ---------------------
3108 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
3110 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
3112 ---------------------
3119 ---- No. A-10-4 EXECUTE statement name error
3123 ERROR: prepared statement "p1" does not exist
3124 SHOW pg_hint_plan.debug_print;
3125 pg_hint_plan.debug_print
3126 --------------------------
3131 ---- No. A-11-5 EXECUTE statement name error
3134 SELECT pg_stat_statements_reset();
3135 pg_stat_statements_reset
3136 --------------------------
3140 SELECT * FROM s1.t1 WHERE t1.c1 = 1;
3146 /*+Set(enable_seqscan off)*/ SELECT * FROM s1.t1 WHERE t1.c1 = 1;
3149 Set(enable_seqscan off)
3159 /*+SeqScan(t1)*/ SELECT * FROM s1.t1 WHERE t1.c1 = 1;
3172 SELECT s.query, s.calls
3173 FROM public.pg_stat_statements s
3174 JOIN pg_catalog.pg_database d
3178 --------------------------------------+-------
3179 SELECT * FROM s1.t1 WHERE t1.c1 = $1 | 3
3180 SELECT pg_stat_statements_reset() | 1
3184 ---- No. A-12-1 reset of global variable of core at the error
3185 ---- No. A-12-2 reset of global variable of original at the error
3187 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
3189 ------------------------------------
3191 Merge Cond: (t1.c1 = t2.c1)
3192 -> Index Scan using t1_i1 on t1
3198 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)NestLoop(t1 t1)*/
3199 PREPARE p1 AS SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
3200 EXPLAIN (COSTS false) EXECUTE p1;
3201 INFO: pg_hint_plan: hint syntax error at or near "NestLoop(t1 t1)"
3202 DETAIL: Relation name "t1" is duplicated.
3207 Set(enable_seqscan off)
3208 Set(geqo_threshold 100)
3215 ------------------------------------
3217 Merge Cond: (t1.c1 = t2.c1)
3221 -> Index Scan using t2_i1 on t2
3226 SELECT name, setting FROM settings;
3228 ------------------------------+-----------
3231 geqo_generations | 0
3234 geqo_selection_bias | 2
3236 constraint_exclusion | partition
3237 cursor_tuple_fraction | 0.1
3238 default_statistics_target | 100
3239 force_parallel_mode | off
3240 from_collapse_limit | 8
3241 join_collapse_limit | 8
3242 cpu_index_tuple_cost | 0.005
3243 cpu_operator_cost | 0.0025
3244 cpu_tuple_cost | 0.01
3245 effective_cache_size | 16384
3246 min_parallel_index_scan_size | 64
3247 min_parallel_table_scan_size | 1024
3248 parallel_setup_cost | 1000
3249 parallel_tuple_cost | 0.1
3250 random_page_cost | 4
3252 enable_bitmapscan | on
3253 enable_gathermerge | on
3255 enable_hashjoin | on
3256 enable_indexonlyscan | on
3257 enable_indexscan | on
3258 enable_material | on
3259 enable_mergejoin | on
3260 enable_nestloop | on
3264 client_min_messages | log
3267 SET pg_hint_plan.parse_messages TO error;
3268 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)NestLoop(t1 t1)*/
3269 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
3270 ERROR: pg_hint_plan: hint syntax error at or near "NestLoop(t1 t1)"
3271 DETAIL: Relation name "t1" is duplicated.
3272 SELECT name, setting FROM settings;
3274 ------------------------------+-----------
3277 geqo_generations | 0
3280 geqo_selection_bias | 2
3282 constraint_exclusion | partition
3283 cursor_tuple_fraction | 0.1
3284 default_statistics_target | 100
3285 force_parallel_mode | off
3286 from_collapse_limit | 8
3287 join_collapse_limit | 8
3288 cpu_index_tuple_cost | 0.005
3289 cpu_operator_cost | 0.0025
3290 cpu_tuple_cost | 0.01
3291 effective_cache_size | 16384
3292 min_parallel_index_scan_size | 64
3293 min_parallel_table_scan_size | 1024
3294 parallel_setup_cost | 1000
3295 parallel_tuple_cost | 0.1
3296 random_page_cost | 4
3298 enable_bitmapscan | on
3299 enable_gathermerge | on
3301 enable_hashjoin | on
3302 enable_indexonlyscan | on
3303 enable_indexscan | on
3304 enable_material | on
3305 enable_mergejoin | on
3306 enable_nestloop | on
3310 client_min_messages | log
3313 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)*/
3314 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
3319 Set(enable_seqscan off)
3320 Set(geqo_threshold 100)
3326 ------------------------------------
3328 Merge Cond: (t1.c1 = t2.c1)
3332 -> Index Scan using t2_i1 on t2
3337 SELECT name, setting FROM settings;
3339 ------------------------------+-----------
3342 geqo_generations | 0
3345 geqo_selection_bias | 2
3347 constraint_exclusion | partition
3348 cursor_tuple_fraction | 0.1
3349 default_statistics_target | 100
3350 force_parallel_mode | off
3351 from_collapse_limit | 8
3352 join_collapse_limit | 8
3353 cpu_index_tuple_cost | 0.005
3354 cpu_operator_cost | 0.0025
3355 cpu_tuple_cost | 0.01
3356 effective_cache_size | 16384
3357 min_parallel_index_scan_size | 64
3358 min_parallel_table_scan_size | 1024
3359 parallel_setup_cost | 1000
3360 parallel_tuple_cost | 0.1
3361 random_page_cost | 4
3363 enable_bitmapscan | on
3364 enable_gathermerge | on
3366 enable_hashjoin | on
3367 enable_indexonlyscan | on
3368 enable_indexscan | on
3369 enable_material | on
3370 enable_mergejoin | on
3371 enable_nestloop | on
3375 client_min_messages | log
3378 SET pg_hint_plan.parse_messages TO error;
3379 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)NestLoop(t1 t1)*/
3380 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
3381 ERROR: pg_hint_plan: hint syntax error at or near "NestLoop(t1 t1)"
3382 DETAIL: Relation name "t1" is duplicated.
3383 SELECT name, setting FROM settings;
3385 ------------------------------+-----------
3388 geqo_generations | 0
3391 geqo_selection_bias | 2
3393 constraint_exclusion | partition
3394 cursor_tuple_fraction | 0.1
3395 default_statistics_target | 100
3396 force_parallel_mode | off
3397 from_collapse_limit | 8
3398 join_collapse_limit | 8
3399 cpu_index_tuple_cost | 0.005
3400 cpu_operator_cost | 0.0025
3401 cpu_tuple_cost | 0.01
3402 effective_cache_size | 16384
3403 min_parallel_index_scan_size | 64
3404 min_parallel_table_scan_size | 1024
3405 parallel_setup_cost | 1000
3406 parallel_tuple_cost | 0.1
3407 random_page_cost | 4
3409 enable_bitmapscan | on
3410 enable_gathermerge | on
3412 enable_hashjoin | on
3413 enable_indexonlyscan | on
3414 enable_indexscan | on
3415 enable_material | on
3416 enable_mergejoin | on
3417 enable_nestloop | on
3421 client_min_messages | log
3424 EXPLAIN (COSTS false) EXECUTE p1;
3426 ------------------------------------
3428 Merge Cond: (t1.c1 = t2.c1)
3432 -> Index Scan using t2_i1 on t2
3437 SELECT name, setting FROM settings;
3439 ------------------------------+-----------
3442 geqo_generations | 0
3445 geqo_selection_bias | 2
3447 constraint_exclusion | partition
3448 cursor_tuple_fraction | 0.1
3449 default_statistics_target | 100
3450 force_parallel_mode | off
3451 from_collapse_limit | 8
3452 join_collapse_limit | 8
3453 cpu_index_tuple_cost | 0.005
3454 cpu_operator_cost | 0.0025
3455 cpu_tuple_cost | 0.01
3456 effective_cache_size | 16384
3457 min_parallel_index_scan_size | 64
3458 min_parallel_table_scan_size | 1024
3459 parallel_setup_cost | 1000
3460 parallel_tuple_cost | 0.1
3461 random_page_cost | 4
3463 enable_bitmapscan | on
3464 enable_gathermerge | on
3466 enable_hashjoin | on
3467 enable_indexonlyscan | on
3468 enable_indexscan | on
3469 enable_material | on
3470 enable_mergejoin | on
3471 enable_nestloop | on
3475 client_min_messages | log
3478 SET pg_hint_plan.parse_messages TO error;
3479 EXPLAIN (COSTS false) EXECUTE p2;
3480 ERROR: prepared statement "p2" does not exist
3481 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)*/
3482 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
3487 Set(enable_seqscan off)
3488 Set(geqo_threshold 100)
3494 ------------------------------------
3496 Merge Cond: (t1.c1 = t2.c1)
3500 -> Index Scan using t2_i1 on t2
3503 EXPLAIN (COSTS false) EXECUTE p1;
3505 ------------------------------------
3507 Merge Cond: (t1.c1 = t2.c1)
3511 -> Index Scan using t2_i1 on t2
3514 SELECT name, setting FROM settings;
3516 ------------------------------+-----------
3519 geqo_generations | 0
3522 geqo_selection_bias | 2
3524 constraint_exclusion | partition
3525 cursor_tuple_fraction | 0.1
3526 default_statistics_target | 100
3527 force_parallel_mode | off
3528 from_collapse_limit | 8
3529 join_collapse_limit | 8
3530 cpu_index_tuple_cost | 0.005
3531 cpu_operator_cost | 0.0025
3532 cpu_tuple_cost | 0.01
3533 effective_cache_size | 16384
3534 min_parallel_index_scan_size | 64
3535 min_parallel_table_scan_size | 1024
3536 parallel_setup_cost | 1000
3537 parallel_tuple_cost | 0.1
3538 random_page_cost | 4
3540 enable_bitmapscan | on
3541 enable_gathermerge | on
3543 enable_hashjoin | on
3544 enable_indexonlyscan | on
3545 enable_indexscan | on
3546 enable_material | on
3547 enable_mergejoin | on
3548 enable_nestloop | on
3552 client_min_messages | log
3557 SELECT name, setting FROM settings;
3559 ------------------------------+-----------
3562 geqo_generations | 0
3565 geqo_selection_bias | 2
3567 constraint_exclusion | partition
3568 cursor_tuple_fraction | 0.1
3569 default_statistics_target | 100
3570 force_parallel_mode | off
3571 from_collapse_limit | 8
3572 join_collapse_limit | 8
3573 cpu_index_tuple_cost | 0.005
3574 cpu_operator_cost | 0.0025
3575 cpu_tuple_cost | 0.01
3576 effective_cache_size | 16384
3577 min_parallel_index_scan_size | 64
3578 min_parallel_table_scan_size | 1024
3579 parallel_setup_cost | 1000
3580 parallel_tuple_cost | 0.1
3581 random_page_cost | 4
3583 enable_bitmapscan | on
3584 enable_gathermerge | on
3586 enable_hashjoin | on
3587 enable_indexonlyscan | on
3588 enable_indexscan | on
3589 enable_material | on
3590 enable_mergejoin | on
3591 enable_nestloop | on
3595 client_min_messages | log
3598 SET pg_hint_plan.parse_messages TO error;
3599 EXPLAIN (COSTS false) EXECUTE p2;
3600 ERROR: prepared statement "p2" does not exist
3601 EXPLAIN (COSTS false) EXECUTE p1;
3603 ------------------------------------
3605 Merge Cond: (t1.c1 = t2.c1)
3609 -> Index Scan using t2_i1 on t2
3612 SELECT name, setting FROM settings;
3614 ------------------------------+-----------
3617 geqo_generations | 0
3620 geqo_selection_bias | 2
3622 constraint_exclusion | partition
3623 cursor_tuple_fraction | 0.1
3624 default_statistics_target | 100
3625 force_parallel_mode | off
3626 from_collapse_limit | 8
3627 join_collapse_limit | 8
3628 cpu_index_tuple_cost | 0.005
3629 cpu_operator_cost | 0.0025
3630 cpu_tuple_cost | 0.01
3631 effective_cache_size | 16384
3632 min_parallel_index_scan_size | 64
3633 min_parallel_table_scan_size | 1024
3634 parallel_setup_cost | 1000
3635 parallel_tuple_cost | 0.1
3636 random_page_cost | 4
3638 enable_bitmapscan | on
3639 enable_gathermerge | on
3641 enable_hashjoin | on
3642 enable_indexonlyscan | on
3643 enable_indexscan | on
3644 enable_material | on
3645 enable_mergejoin | on
3646 enable_nestloop | on
3650 client_min_messages | log
3654 SET pg_hint_plan.parse_messages TO LOG;
3656 ---- No. A-12-3 effective range of the hint
3658 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
3660 ------------------------------------
3662 Merge Cond: (t1.c1 = t2.c1)
3663 -> Index Scan using t1_i1 on t1
3670 SET enable_indexscan TO off;
3671 SET enable_mergejoin TO off;
3672 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
3674 ------------------------------
3676 Hash Cond: (t1.c1 = t2.c1)
3682 SELECT name, setting FROM settings;
3684 ------------------------------+-----------
3687 geqo_generations | 0
3690 geqo_selection_bias | 2
3692 constraint_exclusion | partition
3693 cursor_tuple_fraction | 0.1
3694 default_statistics_target | 100
3695 force_parallel_mode | off
3696 from_collapse_limit | 8
3697 join_collapse_limit | 8
3698 cpu_index_tuple_cost | 0.005
3699 cpu_operator_cost | 0.0025
3700 cpu_tuple_cost | 0.01
3701 effective_cache_size | 16384
3702 min_parallel_index_scan_size | 64
3703 min_parallel_table_scan_size | 1024
3704 parallel_setup_cost | 1000
3705 parallel_tuple_cost | 0.1
3706 random_page_cost | 4
3708 enable_bitmapscan | on
3709 enable_gathermerge | on
3711 enable_hashjoin | on
3712 enable_indexonlyscan | on
3713 enable_indexscan | off
3714 enable_material | on
3715 enable_mergejoin | off
3716 enable_nestloop | on
3720 client_min_messages | log
3723 /*+Set(enable_indexscan on)Set(geqo_threshold 100)IndexScan(t2)MergeJoin(t1 t2)Leading(t2 t1)*/
3724 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
3730 Set(enable_indexscan on)
3731 Set(geqo_threshold 100)
3737 ------------------------------------
3739 Merge Cond: (t1.c1 = t2.c1)
3740 -> Index Scan using t1_i1 on t1
3741 -> Index Scan using t2_i1 on t2
3744 SELECT name, setting FROM settings;
3746 ------------------------------+-----------
3749 geqo_generations | 0
3752 geqo_selection_bias | 2
3754 constraint_exclusion | partition
3755 cursor_tuple_fraction | 0.1
3756 default_statistics_target | 100
3757 force_parallel_mode | off
3758 from_collapse_limit | 8
3759 join_collapse_limit | 8
3760 cpu_index_tuple_cost | 0.005
3761 cpu_operator_cost | 0.0025
3762 cpu_tuple_cost | 0.01
3763 effective_cache_size | 16384
3764 min_parallel_index_scan_size | 64
3765 min_parallel_table_scan_size | 1024
3766 parallel_setup_cost | 1000
3767 parallel_tuple_cost | 0.1
3768 random_page_cost | 4
3770 enable_bitmapscan | on
3771 enable_gathermerge | on
3773 enable_hashjoin | on
3774 enable_indexonlyscan | on
3775 enable_indexscan | off
3776 enable_material | on
3777 enable_mergejoin | off
3778 enable_nestloop | on
3782 client_min_messages | log
3785 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
3787 ------------------------------
3789 Hash Cond: (t1.c1 = t2.c1)
3796 SET enable_indexscan TO off;
3797 SET enable_mergejoin TO off;
3798 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
3800 ------------------------------
3802 Hash Cond: (t1.c1 = t2.c1)
3808 SELECT name, setting FROM settings;
3810 ------------------------------+-----------
3813 geqo_generations | 0
3816 geqo_selection_bias | 2
3818 constraint_exclusion | partition
3819 cursor_tuple_fraction | 0.1
3820 default_statistics_target | 100
3821 force_parallel_mode | off
3822 from_collapse_limit | 8
3823 join_collapse_limit | 8
3824 cpu_index_tuple_cost | 0.005
3825 cpu_operator_cost | 0.0025
3826 cpu_tuple_cost | 0.01
3827 effective_cache_size | 16384
3828 min_parallel_index_scan_size | 64
3829 min_parallel_table_scan_size | 1024
3830 parallel_setup_cost | 1000
3831 parallel_tuple_cost | 0.1
3832 random_page_cost | 4
3834 enable_bitmapscan | on
3835 enable_gathermerge | on
3837 enable_hashjoin | on
3838 enable_indexonlyscan | on
3839 enable_indexscan | off
3840 enable_material | on
3841 enable_mergejoin | off
3842 enable_nestloop | on
3846 client_min_messages | log
3850 /*+Set(enable_indexscan on)Set(geqo_threshold 100)IndexScan(t2)MergeJoin(t1 t2)Leading(t2 t1)*/
3851 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
3857 Set(enable_indexscan on)
3858 Set(geqo_threshold 100)
3864 ------------------------------------
3866 Merge Cond: (t1.c1 = t2.c1)
3867 -> Index Scan using t1_i1 on t1
3868 -> Index Scan using t2_i1 on t2
3873 SELECT name, setting FROM settings;
3875 ------------------------------+-----------
3878 geqo_generations | 0
3881 geqo_selection_bias | 2
3883 constraint_exclusion | partition
3884 cursor_tuple_fraction | 0.1
3885 default_statistics_target | 100
3886 force_parallel_mode | off
3887 from_collapse_limit | 8
3888 join_collapse_limit | 8
3889 cpu_index_tuple_cost | 0.005
3890 cpu_operator_cost | 0.0025
3891 cpu_tuple_cost | 0.01
3892 effective_cache_size | 16384
3893 min_parallel_index_scan_size | 64
3894 min_parallel_table_scan_size | 1024
3895 parallel_setup_cost | 1000
3896 parallel_tuple_cost | 0.1
3897 random_page_cost | 4
3899 enable_bitmapscan | on
3900 enable_gathermerge | on
3902 enable_hashjoin | on
3903 enable_indexonlyscan | on
3904 enable_indexscan | off
3905 enable_material | on
3906 enable_mergejoin | off
3907 enable_nestloop | on
3911 client_min_messages | log
3914 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
3916 ------------------------------
3918 Hash Cond: (t1.c1 = t2.c1)
3926 SET enable_indexscan TO off;
3927 SET enable_mergejoin TO off;
3928 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
3930 ------------------------------
3932 Hash Cond: (t1.c1 = t2.c1)
3938 SELECT name, setting FROM settings;
3940 ------------------------------+-----------
3943 geqo_generations | 0
3946 geqo_selection_bias | 2
3948 constraint_exclusion | partition
3949 cursor_tuple_fraction | 0.1
3950 default_statistics_target | 100
3951 force_parallel_mode | off
3952 from_collapse_limit | 8
3953 join_collapse_limit | 8
3954 cpu_index_tuple_cost | 0.005
3955 cpu_operator_cost | 0.0025
3956 cpu_tuple_cost | 0.01
3957 effective_cache_size | 16384
3958 min_parallel_index_scan_size | 64
3959 min_parallel_table_scan_size | 1024
3960 parallel_setup_cost | 1000
3961 parallel_tuple_cost | 0.1
3962 random_page_cost | 4
3964 enable_bitmapscan | on
3965 enable_gathermerge | on
3967 enable_hashjoin | on
3968 enable_indexonlyscan | on
3969 enable_indexscan | off
3970 enable_material | on
3971 enable_mergejoin | off
3972 enable_nestloop | on
3976 client_min_messages | log
3979 /*+Set(enable_indexscan on)Set(geqo_threshold 100)IndexScan(t2)MergeJoin(t1 t2)Leading(t2 t1)*/
3980 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
3986 Set(enable_indexscan on)
3987 Set(geqo_threshold 100)
3993 ------------------------------------
3995 Merge Cond: (t1.c1 = t2.c1)
3996 -> Index Scan using t1_i1 on t1
3997 -> Index Scan using t2_i1 on t2
4001 SET enable_indexscan TO off;
4002 SET enable_mergejoin TO off;
4003 LOAD 'pg_hint_plan';
4004 SELECT name, setting FROM settings;
4006 ------------------------------+-----------
4009 geqo_generations | 0
4012 geqo_selection_bias | 2
4014 constraint_exclusion | partition
4015 cursor_tuple_fraction | 0.1
4016 default_statistics_target | 100
4017 force_parallel_mode | off
4018 from_collapse_limit | 8
4019 join_collapse_limit | 8
4020 cpu_index_tuple_cost | 0.005
4021 cpu_operator_cost | 0.0025
4022 cpu_tuple_cost | 0.01
4023 effective_cache_size | 16384
4024 min_parallel_index_scan_size | 64
4025 min_parallel_table_scan_size | 1024
4026 parallel_setup_cost | 1000
4027 parallel_tuple_cost | 0.1
4028 random_page_cost | 4
4030 enable_bitmapscan | on
4031 enable_gathermerge | on
4033 enable_hashjoin | on
4034 enable_indexonlyscan | on
4035 enable_indexscan | off
4036 enable_material | on
4037 enable_mergejoin | off
4038 enable_nestloop | on
4042 client_min_messages | notice
4045 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
4047 ------------------------------
4049 Hash Cond: (t1.c1 = t2.c1)
4055 SET pg_hint_plan.enable_hint TO on;
4056 SET pg_hint_plan.debug_print TO on;
4057 SET client_min_messages TO LOG;
4058 SET search_path TO public;
4059 RESET enable_indexscan;
4060 RESET enable_mergejoin;
4062 ---- No. A-13 call planner recursively
4064 CREATE OR REPLACE FUNCTION nested_planner(cnt int) RETURNS int AS $$
4068 RAISE NOTICE 'nested_planner(%)', cnt;
4075 SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1) INTO new_cnt
4077 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
4078 ORDER BY t_1.c1 LIMIT 1;
4082 $$ LANGUAGE plpgsql IMMUTABLE;
4084 ---- No. A-13-2 use hint of main query
4087 EXPLAIN (COSTS false) SELECT nested_planner(1) FROM s1.t1 t_1 ORDER BY t_1.c1;
4088 NOTICE: nested_planner(1)
4090 ---------------------------------------
4091 Index Only Scan using t1_i1 on t1 t_1
4095 EXPLAIN (COSTS false) SELECT nested_planner(1) FROM s1.t1 t_1 ORDER BY t_1.c1;
4096 NOTICE: nested_planner(1)
4105 --------------------------
4108 -> Seq Scan on t1 t_1
4112 ---- No. A-13-3 output number of times of debugging log
4115 EXPLAIN (COSTS false) SELECT nested_planner(1) FROM s1.t1 t_1 ORDER BY t_1.c1;
4116 NOTICE: nested_planner(1)
4118 ---------------------------------------
4119 Index Only Scan using t1_i1 on t1 t_1
4123 EXPLAIN (COSTS false) SELECT nested_planner(1) FROM s1.t1 t_1 ORDER BY t_1.c1;
4124 NOTICE: nested_planner(1)
4133 ---------------------------------------
4134 Index Only Scan using t1_i1 on t1 t_1
4138 EXPLAIN (COSTS false) SELECT nested_planner(2) FROM s1.t1 t_1 ORDER BY t_1.c1;
4139 NOTICE: nested_planner(2)
4140 NOTICE: nested_planner(1)
4149 ---------------------------------------
4150 Index Only Scan using t1_i1 on t1 t_1
4154 EXPLAIN (COSTS false) SELECT nested_planner(2) FROM s1.t1 t_1 ORDER BY t_1.c1;
4155 NOTICE: nested_planner(2)
4156 NOTICE: nested_planner(1)
4172 ---------------------------------------
4173 Index Only Scan using t1_i1 on t1 t_1
4178 -- Redefine not to use cached plan
4180 CREATE OR REPLACE FUNCTION nested_planner(cnt int) RETURNS int AS $$
4184 RAISE NOTICE 'nested_planner(%)', cnt;
4191 SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1) INTO new_cnt
4193 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
4194 ORDER BY t_1.c1 LIMIT 1;
4198 $$ LANGUAGE plpgsql IMMUTABLE;
4199 EXPLAIN (COSTS false) SELECT nested_planner(5) FROM s1.t1 t_1 ORDER BY t_1.c1;
4200 NOTICE: nested_planner(5)
4201 NOTICE: nested_planner(4)
4202 NOTICE: nested_planner(3)
4203 NOTICE: nested_planner(2)
4204 NOTICE: nested_planner(1)
4229 ---------------------------------------
4230 Index Only Scan using t1_i1 on t1 t_1
4234 EXPLAIN (COSTS false) SELECT nested_planner(5) FROM s1.t1 t_1 ORDER BY t_1.c1;
4235 NOTICE: nested_planner(5)
4236 NOTICE: nested_planner(4)
4237 NOTICE: nested_planner(3)
4238 NOTICE: nested_planner(2)
4239 NOTICE: nested_planner(1)
4276 ---------------------------------------
4277 Index Only Scan using t1_i1 on t1 t_1
4281 ---- No. A-13-4 output of debugging log on hint status
4283 CREATE OR REPLACE FUNCTION recall_planner() RETURNS int AS $$
4284 SELECT /*+ IndexScan(t_1) */t_1.c1
4286 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
4287 ORDER BY t_1.c1 LIMIT 1;
4288 $$ LANGUAGE SQL IMMUTABLE;
4290 /*+HashJoin(t_1 t_2)*/
4291 EXPLAIN (COSTS false)
4292 SELECT recall_planner() FROM s1.t1 t_1
4293 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
4310 --------------------------------------
4314 Hash Cond: (t_1.c1 = t_2.c1)
4315 -> Seq Scan on t1 t_1
4317 -> Seq Scan on t2 t_2
4321 /*+HashJoin(st_1 st_2)*/
4322 EXPLAIN (COSTS false)
4323 SELECT recall_planner() FROM s1.t1 st_1
4324 JOIN s1.t2 st_2 ON (st_1.c1 = st_2.c1)
4341 ----------------------------------------
4345 Hash Cond: (st_1.c1 = st_2.c1)
4346 -> Seq Scan on t1 st_1
4348 -> Seq Scan on t2 st_2
4352 /*+HashJoin(t_1 t_2)*/
4353 EXPLAIN (COSTS false)
4354 SELECT recall_planner() FROM s1.t1 st_1
4355 JOIN s1.t2 st_2 ON (st_1.c1 = st_2.c1)
4372 ----------------------------------------------
4374 Merge Cond: (st_1.c1 = st_2.c1)
4375 -> Index Only Scan using t1_i1 on t1 st_1
4378 -> Seq Scan on t2 st_2
4382 /*+HashJoin(st_1 st_2)*/
4383 EXPLAIN (COSTS false)
4384 SELECT recall_planner() FROM s1.t1 t_1
4385 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
4402 ---------------------------------------------
4404 Merge Cond: (t_1.c1 = t_2.c1)
4405 -> Index Only Scan using t1_i1 on t1 t_1
4408 -> Seq Scan on t2 t_2
4412 /*+HashJoin(t_1 t_1)*/
4413 EXPLAIN (COSTS false)
4414 SELECT recall_planner() FROM s1.t1 t_1
4416 INFO: pg_hint_plan: hint syntax error at or near "HashJoin(t_1 t_1)"
4417 DETAIL: Relation name "t_1" is duplicated.
4433 ---------------------------------------
4434 Index Only Scan using t1_i1 on t1 t_1
4438 CREATE OR REPLACE FUNCTION recall_planner_one_t() RETURNS int AS $$
4439 SELECT /*+ IndexScan(t_1) */t_1.c1
4441 ORDER BY t_1.c1 LIMIT 1;
4442 $$ LANGUAGE SQL IMMUTABLE;
4443 EXPLAIN (COSTS false)
4444 SELECT recall_planner_one_t() FROM s1.t1 t_1
4445 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
4448 ---------------------------------------------
4450 Merge Cond: (t_1.c1 = t_2.c1)
4451 -> Index Only Scan using t1_i1 on t1 t_1
4454 -> Seq Scan on t2 t_2
4457 /*+HashJoin(t_1 t_1)*/
4458 EXPLAIN (COSTS false)
4459 SELECT recall_planner_one_t() FROM s1.t1 t_1
4460 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
4469 INFO: pg_hint_plan: hint syntax error at or near "HashJoin(t_1 t_1)"
4470 DETAIL: Relation name "t_1" is duplicated.
4479 ---------------------------------------------
4481 Merge Cond: (t_1.c1 = t_2.c1)
4482 -> Index Only Scan using t1_i1 on t1 t_1
4485 -> Seq Scan on t2 t_2
4488 DROP FUNCTION recall_planner_one_t(int);
4489 ERROR: function recall_planner_one_t(integer) does not exist
4491 /*+HashJoin(t_1 t_1)*/
4492 EXPLAIN (COSTS false)
4493 SELECT recall_planner() FROM s1.t1 t_1
4494 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
4496 INFO: pg_hint_plan: hint syntax error at or near "HashJoin(t_1 t_1)"
4497 DETAIL: Relation name "t_1" is duplicated.
4505 INFO: pg_hint_plan: hint syntax error at or near "HashJoin(t_1 t_1)"
4506 DETAIL: Relation name "t_1" is duplicated.
4515 ---------------------------------------------
4517 Merge Cond: (t_1.c1 = t_2.c1)
4518 -> Index Only Scan using t1_i1 on t1 t_1
4521 -> Seq Scan on t2 t_2
4525 /*+MergeJoin(t_1 t_2)HashJoin(t_1 t_2)*/
4526 EXPLAIN (COSTS false)
4527 SELECT recall_planner() FROM s1.t1 t_1
4528 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
4530 INFO: pg_hint_plan: hint syntax error at or near "MergeJoin(t_1 t_2)HashJoin(t_1 t_2)"
4531 DETAIL: Conflict join method hint.
4532 INFO: pg_hint_plan: hint syntax error at or near "MergeJoin(t_1 t_2)HashJoin(t_1 t_2)"
4533 DETAIL: Conflict join method hint.
4551 --------------------------------------
4555 Hash Cond: (t_1.c1 = t_2.c1)
4556 -> Seq Scan on t1 t_1
4558 -> Seq Scan on t2 t_2