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;
2019 ERROR: pg_hint_plan: hint syntax error at or near ""
2020 DETAIL: Opening parenthesis is necessary.
2022 RESET client_min_messages;
2023 SET pg_hint_plan.parse_messages TO DEFAULT;
2024 SHOW pg_hint_plan.parse_messages;
2025 pg_hint_plan.parse_messages
2026 -----------------------------
2031 INFO: pg_hint_plan: hint syntax error at or near ""
2032 DETAIL: Opening parenthesis is necessary.
2039 SET pg_hint_plan.parse_messages TO fatal;
2040 ERROR: invalid value for parameter "pg_hint_plan.parse_messages": "fatal"
2041 HINT: Available values: debug5, debug4, debug3, debug2, debug1, log, info, notice, warning, error.
2042 SHOW pg_hint_plan.parse_messages;
2043 pg_hint_plan.parse_messages
2044 -----------------------------
2049 SET pg_hint_plan.parse_messages TO panic;
2050 ERROR: invalid value for parameter "pg_hint_plan.parse_messages": "panic"
2051 HINT: Available values: debug5, debug4, debug3, debug2, debug1, log, info, notice, warning, error.
2052 SHOW pg_hint_plan.parse_messages;
2053 pg_hint_plan.parse_messages
2054 -----------------------------
2059 SET pg_hint_plan.parse_messages TO on;
2060 ERROR: invalid value for parameter "pg_hint_plan.parse_messages": "on"
2061 HINT: Available values: debug5, debug4, debug3, debug2, debug1, log, info, notice, warning, error.
2062 SHOW pg_hint_plan.parse_messages;
2063 pg_hint_plan.parse_messages
2064 -----------------------------
2069 ---- No. A-8-5 original GUC parameter pg_hint_plan.enable_hint_table
2071 INSERT INTO hint_plan.hints (norm_query_string, application_name, hints)
2073 'EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = ?;',
2077 SET pg_hint_plan.enable_hint_table TO on;
2078 SHOW pg_hint_plan.enable_hint_table;
2079 pg_hint_plan.enable_hint_table
2080 --------------------------------
2084 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
2086 --------------------
2092 SET pg_hint_plan.enable_hint_table TO off;
2093 SHOW pg_hint_plan.enable_hint_table;
2094 pg_hint_plan.enable_hint_table
2095 --------------------------------
2099 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
2101 ------------------------------
2102 Index Scan using t1_i1 on t1
2103 Index Cond: (c1 = 1)
2107 SET pg_hint_plan.enable_hint_table TO DEFAULT;
2108 SHOW pg_hint_plan.enable_hint_table;
2109 pg_hint_plan.enable_hint_table
2110 --------------------------------
2114 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
2116 ------------------------------
2117 Index Scan using t1_i1 on t1
2118 Index Cond: (c1 = 1)
2122 SET pg_hint_plan.enable_hint_table TO enable;
2123 ERROR: parameter "pg_hint_plan.enable_hint_table" requires a Boolean value
2124 SHOW pg_hint_plan.enable_hint_table;
2125 pg_hint_plan.enable_hint_table
2126 --------------------------------
2130 TRUNCATE hint_plan.hints;
2132 ---- No. A-9-1 parse error message output
2135 /*+"Set"(enable_indexscan on)*/SELECT 1;
2136 INFO: pg_hint_plan: hint syntax error at or near ""Set"(enable_indexscan on)"
2137 DETAIL: Unrecognized hint keyword ""Set"".
2143 /*+Set()(enable_indexscan on)*/SELECT 1;
2144 INFO: pg_hint_plan: hint syntax error at or near "Set()(enable_indexscan on)"
2145 DETAIL: Set hint requires name and value of GUC parameter.
2146 INFO: pg_hint_plan: hint syntax error at or near "(enable_indexscan on)"
2147 DETAIL: Unrecognized hint keyword "".
2153 /*+Set(enable_indexscan on*/SELECT 1;
2154 INFO: pg_hint_plan: hint syntax error at or near ""
2155 DETAIL: Closing parenthesis is necessary.
2162 ---- No. A-9-3 hint state output
2164 SET pg_hint_plan.debug_print TO on;
2165 SET client_min_messages TO LOG;
2168 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
2177 --------------------
2183 /*+SeqScan(no_table)*/
2184 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
2193 ------------------------------
2194 Index Scan using t1_i1 on t1
2195 Index Cond: (c1 = 1)
2199 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
2201 -----------------------------------
2203 TID Cond: (ctid = '(1,1)'::tid)
2207 /*+TidScan(t1)BitmapScan(t1)*/
2208 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
2209 INFO: pg_hint_plan: hint syntax error at or near "TidScan(t1)BitmapScan(t1)"
2210 DETAIL: Conflict scan method hint.
2220 ----------------------------------
2221 Bitmap Heap Scan on t1
2222 Recheck Cond: (c1 = 1)
2223 Filter: (ctid = '(1,1)'::tid)
2224 -> Bitmap Index Scan on t1_i1
2225 Index Cond: (c1 = 1)
2228 /*+TidScan(t1)BitmapScan(t1)IndexScan(t1)*/
2229 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
2230 INFO: pg_hint_plan: hint syntax error at or near "TidScan(t1)BitmapScan(t1)IndexScan(t1)"
2231 DETAIL: Conflict scan method hint.
2232 INFO: pg_hint_plan: hint syntax error at or near "BitmapScan(t1)IndexScan(t1)"
2233 DETAIL: Conflict scan method hint.
2244 ---------------------------------
2245 Index Scan using t1_i1 on t1
2246 Index Cond: (c1 = 1)
2247 Filter: (ctid = '(1,1)'::tid)
2250 /*+TidScan(t1)BitmapScan(t1)IndexScan(t1)SeqScan(t1)*/
2251 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
2252 INFO: pg_hint_plan: hint syntax error at or near "TidScan(t1)BitmapScan(t1)IndexScan(t1)SeqScan(t1)"
2253 DETAIL: Conflict scan method hint.
2254 INFO: pg_hint_plan: hint syntax error at or near "BitmapScan(t1)IndexScan(t1)SeqScan(t1)"
2255 DETAIL: Conflict scan method hint.
2256 INFO: pg_hint_plan: hint syntax error at or near "IndexScan(t1)SeqScan(t1)"
2257 DETAIL: Conflict scan method hint.
2269 ------------------------------------------------
2271 Filter: ((c1 = 1) AND (ctid = '(1,1)'::tid))
2275 /*+Set(enable_indexscan enable)*/
2276 EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
2277 INFO: parameter "enable_indexscan" requires a Boolean value
2283 Set(enable_indexscan enable)
2286 ------------------------------
2287 Index Scan using t1_i1 on t1
2288 Index Cond: (c1 = 1)
2292 ---- No. A-10-1 hint state output
2294 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
2295 EXPLAIN (COSTS false) EXECUTE p1;
2297 ------------------------------
2298 Index Scan using t1_i1 on t1
2299 Index Cond: (c1 = 1)
2303 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
2304 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2306 -----------------------
2311 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2313 -----------------------
2318 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2320 -----------------------
2325 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2327 -----------------------
2332 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2334 -----------------------
2339 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2341 ---------------------
2350 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
2352 EXPLAIN (COSTS false) EXECUTE p1;
2361 --------------------
2366 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
2368 EXPLAIN (COSTS false) EXECUTE p1;
2377 --------------------
2384 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
2386 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2395 ----------------------------------
2396 Bitmap Heap Scan on t1
2397 Recheck Cond: (c1 < 1000)
2398 -> Bitmap Index Scan on t1_i1
2399 Index Cond: (c1 < 1000)
2403 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2412 ----------------------------------
2413 Bitmap Heap Scan on t1
2414 Recheck Cond: (c1 < 1000)
2415 -> Bitmap Index Scan on t1_i1
2416 Index Cond: (c1 < 1000)
2420 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2429 ----------------------------------
2430 Bitmap Heap Scan on t1
2431 Recheck Cond: (c1 < 1000)
2432 -> Bitmap Index Scan on t1_i1
2433 Index Cond: (c1 < 1000)
2437 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2446 ----------------------------------
2447 Bitmap Heap Scan on t1
2448 Recheck Cond: (c1 < 1000)
2449 -> Bitmap Index Scan on t1_i1
2450 Index Cond: (c1 < 1000)
2454 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2463 ----------------------------------
2464 Bitmap Heap Scan on t1
2465 Recheck Cond: (c1 < 1000)
2466 -> Bitmap Index Scan on t1_i1
2467 Index Cond: (c1 < 1000)
2471 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2480 ----------------------------------
2481 Bitmap Heap Scan on t1
2482 Recheck Cond: (c1 < $1)
2483 -> Bitmap Index Scan on t1_i1
2484 Index Cond: (c1 < $1)
2487 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
2489 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2498 ----------------------------------
2499 Bitmap Heap Scan on t1
2500 Recheck Cond: (c1 < $1)
2501 -> Bitmap Index Scan on t1_i1
2502 Index Cond: (c1 < $1)
2509 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
2510 EXPLAIN (COSTS false) EXECUTE p1;
2519 --------------------
2524 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
2525 EXPLAIN (COSTS false) EXECUTE p1;
2534 --------------------
2541 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
2542 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2551 ----------------------------------
2552 Bitmap Heap Scan on t1
2553 Recheck Cond: (c1 < 1000)
2554 -> Bitmap Index Scan on t1_i1
2555 Index Cond: (c1 < 1000)
2558 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2567 ----------------------------------
2568 Bitmap Heap Scan on t1
2569 Recheck Cond: (c1 < 1000)
2570 -> Bitmap Index Scan on t1_i1
2571 Index Cond: (c1 < 1000)
2574 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2583 ----------------------------------
2584 Bitmap Heap Scan on t1
2585 Recheck Cond: (c1 < 1000)
2586 -> Bitmap Index Scan on t1_i1
2587 Index Cond: (c1 < 1000)
2590 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2599 ----------------------------------
2600 Bitmap Heap Scan on t1
2601 Recheck Cond: (c1 < 1000)
2602 -> Bitmap Index Scan on t1_i1
2603 Index Cond: (c1 < 1000)
2606 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2615 ----------------------------------
2616 Bitmap Heap Scan on t1
2617 Recheck Cond: (c1 < 1000)
2618 -> Bitmap Index Scan on t1_i1
2619 Index Cond: (c1 < 1000)
2622 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2631 ----------------------------------
2632 Bitmap Heap Scan on t1
2633 Recheck Cond: (c1 < $1)
2634 -> Bitmap Index Scan on t1_i1
2635 Index Cond: (c1 < $1)
2638 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
2639 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2648 ----------------------------------
2649 Bitmap Heap Scan on t1
2650 Recheck Cond: (c1 < $1)
2651 -> Bitmap Index Scan on t1_i1
2652 Index Cond: (c1 < $1)
2658 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
2660 EXPLAIN (COSTS false) EXECUTE p1;
2662 ------------------------------
2663 Index Scan using t1_i1 on t1
2664 Index Cond: (c1 = 1)
2667 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
2669 EXPLAIN (COSTS false) EXECUTE p1;
2671 ------------------------------
2672 Index Scan using t1_i1 on t1
2673 Index Cond: (c1 = 1)
2677 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
2679 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2681 -----------------------
2686 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2688 -----------------------
2693 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2695 -----------------------
2700 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2702 -----------------------
2707 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2709 -----------------------
2714 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2716 ---------------------
2721 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
2723 EXPLAIN (COSTS false) EXECUTE p1 (1000);
2725 ---------------------
2734 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
2736 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
2745 --------------------
2750 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
2752 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
2761 --------------------
2768 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
2770 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
2779 ----------------------------------
2780 Bitmap Heap Scan on t1
2781 Recheck Cond: (c1 < 1000)
2782 -> Bitmap Index Scan on t1_i1
2783 Index Cond: (c1 < 1000)
2787 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
2796 ----------------------------------
2797 Bitmap Heap Scan on t1
2798 Recheck Cond: (c1 < 1000)
2799 -> Bitmap Index Scan on t1_i1
2800 Index Cond: (c1 < 1000)
2804 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
2813 ----------------------------------
2814 Bitmap Heap Scan on t1
2815 Recheck Cond: (c1 < 1000)
2816 -> Bitmap Index Scan on t1_i1
2817 Index Cond: (c1 < 1000)
2821 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
2830 ----------------------------------
2831 Bitmap Heap Scan on t1
2832 Recheck Cond: (c1 < 1000)
2833 -> Bitmap Index Scan on t1_i1
2834 Index Cond: (c1 < 1000)
2838 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
2847 ----------------------------------
2848 Bitmap Heap Scan on t1
2849 Recheck Cond: (c1 < 1000)
2850 -> Bitmap Index Scan on t1_i1
2851 Index Cond: (c1 < 1000)
2855 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
2864 ----------------------------------
2865 Bitmap Heap Scan on t1
2866 Recheck Cond: (c1 < $1)
2867 -> Bitmap Index Scan on t1_i1
2868 Index Cond: (c1 < $1)
2871 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
2873 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
2882 ----------------------------------
2883 Bitmap Heap Scan on t1
2884 Recheck Cond: (c1 < $1)
2885 -> Bitmap Index Scan on t1_i1
2886 Index Cond: (c1 < $1)
2893 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
2894 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
2903 --------------------
2908 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
2909 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
2918 --------------------
2925 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
2926 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
2935 ----------------------------------
2936 Bitmap Heap Scan on t1
2937 Recheck Cond: (c1 < 1000)
2938 -> Bitmap Index Scan on t1_i1
2939 Index Cond: (c1 < 1000)
2942 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
2951 ----------------------------------
2952 Bitmap Heap Scan on t1
2953 Recheck Cond: (c1 < 1000)
2954 -> Bitmap Index Scan on t1_i1
2955 Index Cond: (c1 < 1000)
2958 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
2967 ----------------------------------
2968 Bitmap Heap Scan on t1
2969 Recheck Cond: (c1 < 1000)
2970 -> Bitmap Index Scan on t1_i1
2971 Index Cond: (c1 < 1000)
2974 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
2983 ----------------------------------
2984 Bitmap Heap Scan on t1
2985 Recheck Cond: (c1 < 1000)
2986 -> Bitmap Index Scan on t1_i1
2987 Index Cond: (c1 < 1000)
2990 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
2999 ----------------------------------
3000 Bitmap Heap Scan on t1
3001 Recheck Cond: (c1 < 1000)
3002 -> Bitmap Index Scan on t1_i1
3003 Index Cond: (c1 < 1000)
3006 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
3015 ----------------------------------
3016 Bitmap Heap Scan on t1
3017 Recheck Cond: (c1 < $1)
3018 -> Bitmap Index Scan on t1_i1
3019 Index Cond: (c1 < $1)
3022 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
3023 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
3032 ----------------------------------
3033 Bitmap Heap Scan on t1
3034 Recheck Cond: (c1 < $1)
3035 -> Bitmap Index Scan on t1_i1
3036 Index Cond: (c1 < $1)
3042 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
3044 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
3046 ------------------------------
3047 Index Scan using t1_i1 on t1
3048 Index Cond: (c1 = 1)
3051 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
3053 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
3055 ------------------------------
3056 Index Scan using t1_i1 on t1
3057 Index Cond: (c1 = 1)
3061 PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
3063 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
3065 -----------------------
3071 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
3073 -----------------------
3079 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
3081 -----------------------
3087 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
3089 -----------------------
3095 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
3097 -----------------------
3103 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
3105 ---------------------
3110 UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
3112 EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
3114 ---------------------
3121 ---- No. A-10-4 EXECUTE statement name error
3125 ERROR: prepared statement "p1" does not exist
3126 SHOW pg_hint_plan.debug_print;
3127 pg_hint_plan.debug_print
3128 --------------------------
3133 ---- No. A-11-5 EXECUTE statement name error
3136 SELECT pg_stat_statements_reset();
3137 pg_stat_statements_reset
3138 --------------------------
3142 SELECT * FROM s1.t1 WHERE t1.c1 = 1;
3148 /*+Set(enable_seqscan off)*/ SELECT * FROM s1.t1 WHERE t1.c1 = 1;
3151 Set(enable_seqscan off)
3161 /*+SeqScan(t1)*/ SELECT * FROM s1.t1 WHERE t1.c1 = 1;
3174 SELECT s.query, s.calls
3175 FROM public.pg_stat_statements s
3176 JOIN pg_catalog.pg_database d
3180 --------------------------------------+-------
3181 SELECT * FROM s1.t1 WHERE t1.c1 = $1 | 3
3182 SELECT pg_stat_statements_reset() | 1
3186 ---- No. A-12-1 reset of global variable of core at the error
3187 ---- No. A-12-2 reset of global variable of original at the error
3189 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
3191 ------------------------------------
3193 Merge Cond: (t1.c1 = t2.c1)
3194 -> Index Scan using t1_i1 on t1
3200 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)NestLoop(t1 t1)*/
3201 PREPARE p1 AS SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
3202 EXPLAIN (COSTS false) EXECUTE p1;
3203 INFO: pg_hint_plan: hint syntax error at or near "NestLoop(t1 t1)"
3204 DETAIL: Relation name "t1" is duplicated.
3209 Set(enable_seqscan off)
3210 Set(geqo_threshold 100)
3217 ------------------------------------
3219 Merge Cond: (t1.c1 = t2.c1)
3223 -> Index Scan using t2_i1 on t2
3228 SELECT name, setting FROM settings;
3230 ------------------------------+-----------
3231 client_min_messages | log
3234 geqo_generations | 0
3237 geqo_selection_bias | 2
3239 constraint_exclusion | partition
3240 cursor_tuple_fraction | 0.1
3241 default_statistics_target | 100
3242 force_parallel_mode | off
3243 from_collapse_limit | 8
3244 join_collapse_limit | 8
3245 cpu_index_tuple_cost | 0.005
3246 cpu_operator_cost | 0.0025
3247 cpu_tuple_cost | 0.01
3248 effective_cache_size | 16384
3249 min_parallel_index_scan_size | 64
3250 min_parallel_table_scan_size | 1024
3251 parallel_setup_cost | 1000
3252 parallel_tuple_cost | 0.1
3253 random_page_cost | 4
3255 enable_bitmapscan | on
3256 enable_gathermerge | on
3258 enable_hashjoin | on
3259 enable_indexonlyscan | on
3260 enable_indexscan | on
3261 enable_material | on
3262 enable_mergejoin | on
3263 enable_nestloop | on
3269 SET pg_hint_plan.parse_messages TO error;
3270 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)NestLoop(t1 t1)*/
3271 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
3272 ERROR: pg_hint_plan: hint syntax error at or near "NestLoop(t1 t1)"
3273 DETAIL: Relation name "t1" is duplicated.
3274 SELECT name, setting FROM settings;
3276 ------------------------------+-----------
3277 client_min_messages | log
3280 geqo_generations | 0
3283 geqo_selection_bias | 2
3285 constraint_exclusion | partition
3286 cursor_tuple_fraction | 0.1
3287 default_statistics_target | 100
3288 force_parallel_mode | off
3289 from_collapse_limit | 8
3290 join_collapse_limit | 8
3291 cpu_index_tuple_cost | 0.005
3292 cpu_operator_cost | 0.0025
3293 cpu_tuple_cost | 0.01
3294 effective_cache_size | 16384
3295 min_parallel_index_scan_size | 64
3296 min_parallel_table_scan_size | 1024
3297 parallel_setup_cost | 1000
3298 parallel_tuple_cost | 0.1
3299 random_page_cost | 4
3301 enable_bitmapscan | on
3302 enable_gathermerge | on
3304 enable_hashjoin | on
3305 enable_indexonlyscan | on
3306 enable_indexscan | on
3307 enable_material | on
3308 enable_mergejoin | on
3309 enable_nestloop | on
3315 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)*/
3316 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
3321 Set(enable_seqscan off)
3322 Set(geqo_threshold 100)
3328 ------------------------------------
3330 Merge Cond: (t1.c1 = t2.c1)
3334 -> Index Scan using t2_i1 on t2
3339 SELECT name, setting FROM settings;
3341 ------------------------------+-----------
3342 client_min_messages | log
3345 geqo_generations | 0
3348 geqo_selection_bias | 2
3350 constraint_exclusion | partition
3351 cursor_tuple_fraction | 0.1
3352 default_statistics_target | 100
3353 force_parallel_mode | off
3354 from_collapse_limit | 8
3355 join_collapse_limit | 8
3356 cpu_index_tuple_cost | 0.005
3357 cpu_operator_cost | 0.0025
3358 cpu_tuple_cost | 0.01
3359 effective_cache_size | 16384
3360 min_parallel_index_scan_size | 64
3361 min_parallel_table_scan_size | 1024
3362 parallel_setup_cost | 1000
3363 parallel_tuple_cost | 0.1
3364 random_page_cost | 4
3366 enable_bitmapscan | on
3367 enable_gathermerge | on
3369 enable_hashjoin | on
3370 enable_indexonlyscan | on
3371 enable_indexscan | on
3372 enable_material | on
3373 enable_mergejoin | on
3374 enable_nestloop | on
3380 SET pg_hint_plan.parse_messages TO error;
3381 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)NestLoop(t1 t1)*/
3382 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
3383 ERROR: pg_hint_plan: hint syntax error at or near "NestLoop(t1 t1)"
3384 DETAIL: Relation name "t1" is duplicated.
3385 SELECT name, setting FROM settings;
3387 ------------------------------+-----------
3388 client_min_messages | log
3391 geqo_generations | 0
3394 geqo_selection_bias | 2
3396 constraint_exclusion | partition
3397 cursor_tuple_fraction | 0.1
3398 default_statistics_target | 100
3399 force_parallel_mode | off
3400 from_collapse_limit | 8
3401 join_collapse_limit | 8
3402 cpu_index_tuple_cost | 0.005
3403 cpu_operator_cost | 0.0025
3404 cpu_tuple_cost | 0.01
3405 effective_cache_size | 16384
3406 min_parallel_index_scan_size | 64
3407 min_parallel_table_scan_size | 1024
3408 parallel_setup_cost | 1000
3409 parallel_tuple_cost | 0.1
3410 random_page_cost | 4
3412 enable_bitmapscan | on
3413 enable_gathermerge | on
3415 enable_hashjoin | on
3416 enable_indexonlyscan | on
3417 enable_indexscan | on
3418 enable_material | on
3419 enable_mergejoin | on
3420 enable_nestloop | on
3426 EXPLAIN (COSTS false) EXECUTE p1;
3428 ------------------------------------
3430 Merge Cond: (t1.c1 = t2.c1)
3434 -> Index Scan using t2_i1 on t2
3439 SELECT name, setting FROM settings;
3441 ------------------------------+-----------
3442 client_min_messages | log
3445 geqo_generations | 0
3448 geqo_selection_bias | 2
3450 constraint_exclusion | partition
3451 cursor_tuple_fraction | 0.1
3452 default_statistics_target | 100
3453 force_parallel_mode | off
3454 from_collapse_limit | 8
3455 join_collapse_limit | 8
3456 cpu_index_tuple_cost | 0.005
3457 cpu_operator_cost | 0.0025
3458 cpu_tuple_cost | 0.01
3459 effective_cache_size | 16384
3460 min_parallel_index_scan_size | 64
3461 min_parallel_table_scan_size | 1024
3462 parallel_setup_cost | 1000
3463 parallel_tuple_cost | 0.1
3464 random_page_cost | 4
3466 enable_bitmapscan | on
3467 enable_gathermerge | on
3469 enable_hashjoin | on
3470 enable_indexonlyscan | on
3471 enable_indexscan | on
3472 enable_material | on
3473 enable_mergejoin | on
3474 enable_nestloop | on
3480 SET pg_hint_plan.parse_messages TO error;
3481 EXPLAIN (COSTS false) EXECUTE p2;
3482 ERROR: prepared statement "p2" does not exist
3483 /*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)*/
3484 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
3489 Set(enable_seqscan off)
3490 Set(geqo_threshold 100)
3496 ------------------------------------
3498 Merge Cond: (t1.c1 = t2.c1)
3502 -> Index Scan using t2_i1 on t2
3505 EXPLAIN (COSTS false) EXECUTE p1;
3507 ------------------------------------
3509 Merge Cond: (t1.c1 = t2.c1)
3513 -> Index Scan using t2_i1 on t2
3516 SELECT name, setting FROM settings;
3518 ------------------------------+-----------
3519 client_min_messages | log
3522 geqo_generations | 0
3525 geqo_selection_bias | 2
3527 constraint_exclusion | partition
3528 cursor_tuple_fraction | 0.1
3529 default_statistics_target | 100
3530 force_parallel_mode | off
3531 from_collapse_limit | 8
3532 join_collapse_limit | 8
3533 cpu_index_tuple_cost | 0.005
3534 cpu_operator_cost | 0.0025
3535 cpu_tuple_cost | 0.01
3536 effective_cache_size | 16384
3537 min_parallel_index_scan_size | 64
3538 min_parallel_table_scan_size | 1024
3539 parallel_setup_cost | 1000
3540 parallel_tuple_cost | 0.1
3541 random_page_cost | 4
3543 enable_bitmapscan | on
3544 enable_gathermerge | on
3546 enable_hashjoin | on
3547 enable_indexonlyscan | on
3548 enable_indexscan | on
3549 enable_material | on
3550 enable_mergejoin | on
3551 enable_nestloop | on
3559 SELECT name, setting FROM settings;
3561 ------------------------------+-----------
3562 client_min_messages | log
3565 geqo_generations | 0
3568 geqo_selection_bias | 2
3570 constraint_exclusion | partition
3571 cursor_tuple_fraction | 0.1
3572 default_statistics_target | 100
3573 force_parallel_mode | off
3574 from_collapse_limit | 8
3575 join_collapse_limit | 8
3576 cpu_index_tuple_cost | 0.005
3577 cpu_operator_cost | 0.0025
3578 cpu_tuple_cost | 0.01
3579 effective_cache_size | 16384
3580 min_parallel_index_scan_size | 64
3581 min_parallel_table_scan_size | 1024
3582 parallel_setup_cost | 1000
3583 parallel_tuple_cost | 0.1
3584 random_page_cost | 4
3586 enable_bitmapscan | on
3587 enable_gathermerge | on
3589 enable_hashjoin | on
3590 enable_indexonlyscan | on
3591 enable_indexscan | on
3592 enable_material | on
3593 enable_mergejoin | on
3594 enable_nestloop | on
3600 SET pg_hint_plan.parse_messages TO error;
3601 EXPLAIN (COSTS false) EXECUTE p2;
3602 ERROR: prepared statement "p2" does not exist
3603 EXPLAIN (COSTS false) EXECUTE p1;
3605 ------------------------------------
3607 Merge Cond: (t1.c1 = t2.c1)
3611 -> Index Scan using t2_i1 on t2
3614 SELECT name, setting FROM settings;
3616 ------------------------------+-----------
3617 client_min_messages | log
3620 geqo_generations | 0
3623 geqo_selection_bias | 2
3625 constraint_exclusion | partition
3626 cursor_tuple_fraction | 0.1
3627 default_statistics_target | 100
3628 force_parallel_mode | off
3629 from_collapse_limit | 8
3630 join_collapse_limit | 8
3631 cpu_index_tuple_cost | 0.005
3632 cpu_operator_cost | 0.0025
3633 cpu_tuple_cost | 0.01
3634 effective_cache_size | 16384
3635 min_parallel_index_scan_size | 64
3636 min_parallel_table_scan_size | 1024
3637 parallel_setup_cost | 1000
3638 parallel_tuple_cost | 0.1
3639 random_page_cost | 4
3641 enable_bitmapscan | on
3642 enable_gathermerge | on
3644 enable_hashjoin | on
3645 enable_indexonlyscan | on
3646 enable_indexscan | on
3647 enable_material | on
3648 enable_mergejoin | on
3649 enable_nestloop | on
3656 SET pg_hint_plan.parse_messages TO LOG;
3658 ---- No. A-12-3 effective range of the hint
3660 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
3662 ------------------------------------
3664 Merge Cond: (t1.c1 = t2.c1)
3665 -> Index Scan using t1_i1 on t1
3672 SET enable_indexscan TO off;
3673 SET enable_mergejoin TO off;
3674 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
3676 ------------------------------
3678 Hash Cond: (t1.c1 = t2.c1)
3684 SELECT name, setting FROM settings;
3686 ------------------------------+-----------
3687 client_min_messages | log
3690 geqo_generations | 0
3693 geqo_selection_bias | 2
3695 constraint_exclusion | partition
3696 cursor_tuple_fraction | 0.1
3697 default_statistics_target | 100
3698 force_parallel_mode | off
3699 from_collapse_limit | 8
3700 join_collapse_limit | 8
3701 cpu_index_tuple_cost | 0.005
3702 cpu_operator_cost | 0.0025
3703 cpu_tuple_cost | 0.01
3704 effective_cache_size | 16384
3705 min_parallel_index_scan_size | 64
3706 min_parallel_table_scan_size | 1024
3707 parallel_setup_cost | 1000
3708 parallel_tuple_cost | 0.1
3709 random_page_cost | 4
3711 enable_bitmapscan | on
3712 enable_gathermerge | on
3714 enable_hashjoin | on
3715 enable_indexonlyscan | on
3716 enable_indexscan | off
3717 enable_material | on
3718 enable_mergejoin | off
3719 enable_nestloop | on
3725 /*+Set(enable_indexscan on)Set(geqo_threshold 100)IndexScan(t2)MergeJoin(t1 t2)Leading(t2 t1)*/
3726 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
3732 Set(enable_indexscan on)
3733 Set(geqo_threshold 100)
3739 ------------------------------------
3741 Merge Cond: (t1.c1 = t2.c1)
3742 -> Index Scan using t1_i1 on t1
3743 -> Index Scan using t2_i1 on t2
3746 SELECT name, setting FROM settings;
3748 ------------------------------+-----------
3749 client_min_messages | log
3752 geqo_generations | 0
3755 geqo_selection_bias | 2
3757 constraint_exclusion | partition
3758 cursor_tuple_fraction | 0.1
3759 default_statistics_target | 100
3760 force_parallel_mode | off
3761 from_collapse_limit | 8
3762 join_collapse_limit | 8
3763 cpu_index_tuple_cost | 0.005
3764 cpu_operator_cost | 0.0025
3765 cpu_tuple_cost | 0.01
3766 effective_cache_size | 16384
3767 min_parallel_index_scan_size | 64
3768 min_parallel_table_scan_size | 1024
3769 parallel_setup_cost | 1000
3770 parallel_tuple_cost | 0.1
3771 random_page_cost | 4
3773 enable_bitmapscan | on
3774 enable_gathermerge | on
3776 enable_hashjoin | on
3777 enable_indexonlyscan | on
3778 enable_indexscan | off
3779 enable_material | on
3780 enable_mergejoin | off
3781 enable_nestloop | on
3787 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
3789 ------------------------------
3791 Hash Cond: (t1.c1 = t2.c1)
3798 SET enable_indexscan TO off;
3799 SET enable_mergejoin TO off;
3800 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
3802 ------------------------------
3804 Hash Cond: (t1.c1 = t2.c1)
3810 SELECT name, setting FROM settings;
3812 ------------------------------+-----------
3813 client_min_messages | log
3816 geqo_generations | 0
3819 geqo_selection_bias | 2
3821 constraint_exclusion | partition
3822 cursor_tuple_fraction | 0.1
3823 default_statistics_target | 100
3824 force_parallel_mode | off
3825 from_collapse_limit | 8
3826 join_collapse_limit | 8
3827 cpu_index_tuple_cost | 0.005
3828 cpu_operator_cost | 0.0025
3829 cpu_tuple_cost | 0.01
3830 effective_cache_size | 16384
3831 min_parallel_index_scan_size | 64
3832 min_parallel_table_scan_size | 1024
3833 parallel_setup_cost | 1000
3834 parallel_tuple_cost | 0.1
3835 random_page_cost | 4
3837 enable_bitmapscan | on
3838 enable_gathermerge | on
3840 enable_hashjoin | on
3841 enable_indexonlyscan | on
3842 enable_indexscan | off
3843 enable_material | on
3844 enable_mergejoin | off
3845 enable_nestloop | on
3852 /*+Set(enable_indexscan on)Set(geqo_threshold 100)IndexScan(t2)MergeJoin(t1 t2)Leading(t2 t1)*/
3853 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
3859 Set(enable_indexscan on)
3860 Set(geqo_threshold 100)
3866 ------------------------------------
3868 Merge Cond: (t1.c1 = t2.c1)
3869 -> Index Scan using t1_i1 on t1
3870 -> Index Scan using t2_i1 on t2
3875 SELECT name, setting FROM settings;
3877 ------------------------------+-----------
3878 client_min_messages | log
3881 geqo_generations | 0
3884 geqo_selection_bias | 2
3886 constraint_exclusion | partition
3887 cursor_tuple_fraction | 0.1
3888 default_statistics_target | 100
3889 force_parallel_mode | off
3890 from_collapse_limit | 8
3891 join_collapse_limit | 8
3892 cpu_index_tuple_cost | 0.005
3893 cpu_operator_cost | 0.0025
3894 cpu_tuple_cost | 0.01
3895 effective_cache_size | 16384
3896 min_parallel_index_scan_size | 64
3897 min_parallel_table_scan_size | 1024
3898 parallel_setup_cost | 1000
3899 parallel_tuple_cost | 0.1
3900 random_page_cost | 4
3902 enable_bitmapscan | on
3903 enable_gathermerge | on
3905 enable_hashjoin | on
3906 enable_indexonlyscan | on
3907 enable_indexscan | off
3908 enable_material | on
3909 enable_mergejoin | off
3910 enable_nestloop | on
3916 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
3918 ------------------------------
3920 Hash Cond: (t1.c1 = t2.c1)
3928 SET enable_indexscan TO off;
3929 SET enable_mergejoin TO off;
3930 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
3932 ------------------------------
3934 Hash Cond: (t1.c1 = t2.c1)
3940 SELECT name, setting FROM settings;
3942 ------------------------------+-----------
3943 client_min_messages | log
3946 geqo_generations | 0
3949 geqo_selection_bias | 2
3951 constraint_exclusion | partition
3952 cursor_tuple_fraction | 0.1
3953 default_statistics_target | 100
3954 force_parallel_mode | off
3955 from_collapse_limit | 8
3956 join_collapse_limit | 8
3957 cpu_index_tuple_cost | 0.005
3958 cpu_operator_cost | 0.0025
3959 cpu_tuple_cost | 0.01
3960 effective_cache_size | 16384
3961 min_parallel_index_scan_size | 64
3962 min_parallel_table_scan_size | 1024
3963 parallel_setup_cost | 1000
3964 parallel_tuple_cost | 0.1
3965 random_page_cost | 4
3967 enable_bitmapscan | on
3968 enable_gathermerge | on
3970 enable_hashjoin | on
3971 enable_indexonlyscan | on
3972 enable_indexscan | off
3973 enable_material | on
3974 enable_mergejoin | off
3975 enable_nestloop | on
3981 /*+Set(enable_indexscan on)Set(geqo_threshold 100)IndexScan(t2)MergeJoin(t1 t2)Leading(t2 t1)*/
3982 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
3988 Set(enable_indexscan on)
3989 Set(geqo_threshold 100)
3995 ------------------------------------
3997 Merge Cond: (t1.c1 = t2.c1)
3998 -> Index Scan using t1_i1 on t1
3999 -> Index Scan using t2_i1 on t2
4003 SET enable_indexscan TO off;
4004 SET enable_mergejoin TO off;
4005 LOAD 'pg_hint_plan';
4006 SELECT name, setting FROM settings;
4008 ------------------------------+-----------
4009 client_min_messages | notice
4012 geqo_generations | 0
4015 geqo_selection_bias | 2
4017 constraint_exclusion | partition
4018 cursor_tuple_fraction | 0.1
4019 default_statistics_target | 100
4020 force_parallel_mode | off
4021 from_collapse_limit | 8
4022 join_collapse_limit | 8
4023 cpu_index_tuple_cost | 0.005
4024 cpu_operator_cost | 0.0025
4025 cpu_tuple_cost | 0.01
4026 effective_cache_size | 16384
4027 min_parallel_index_scan_size | 64
4028 min_parallel_table_scan_size | 1024
4029 parallel_setup_cost | 1000
4030 parallel_tuple_cost | 0.1
4031 random_page_cost | 4
4033 enable_bitmapscan | on
4034 enable_gathermerge | on
4036 enable_hashjoin | on
4037 enable_indexonlyscan | on
4038 enable_indexscan | off
4039 enable_material | on
4040 enable_mergejoin | off
4041 enable_nestloop | on
4047 EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
4049 ------------------------------
4051 Hash Cond: (t1.c1 = t2.c1)
4057 SET pg_hint_plan.enable_hint TO on;
4058 SET pg_hint_plan.debug_print TO on;
4059 SET client_min_messages TO LOG;
4060 SET search_path TO public;
4061 RESET enable_indexscan;
4062 RESET enable_mergejoin;
4064 ---- No. A-13 call planner recursively
4066 CREATE OR REPLACE FUNCTION nested_planner(cnt int) RETURNS int AS $$
4070 RAISE NOTICE 'nested_planner(%)', cnt;
4077 SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1) INTO new_cnt
4079 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
4080 ORDER BY t_1.c1 LIMIT 1;
4084 $$ LANGUAGE plpgsql IMMUTABLE;
4086 ---- No. A-13-2 use hint of main query
4089 EXPLAIN (COSTS false) SELECT nested_planner(1) FROM s1.t1 t_1 ORDER BY t_1.c1;
4090 NOTICE: nested_planner(1)
4092 ---------------------------------------
4093 Index Only Scan using t1_i1 on t1 t_1
4097 EXPLAIN (COSTS false) SELECT nested_planner(1) FROM s1.t1 t_1 ORDER BY t_1.c1;
4098 NOTICE: nested_planner(1)
4107 --------------------------
4110 -> Seq Scan on t1 t_1
4114 ---- No. A-13-3 output number of times of debugging log
4117 EXPLAIN (COSTS false) SELECT nested_planner(1) FROM s1.t1 t_1 ORDER BY t_1.c1;
4118 NOTICE: nested_planner(1)
4120 ---------------------------------------
4121 Index Only Scan using t1_i1 on t1 t_1
4125 EXPLAIN (COSTS false) SELECT nested_planner(1) FROM s1.t1 t_1 ORDER BY t_1.c1;
4126 NOTICE: nested_planner(1)
4135 ---------------------------------------
4136 Index Only Scan using t1_i1 on t1 t_1
4140 EXPLAIN (COSTS false) SELECT nested_planner(2) FROM s1.t1 t_1 ORDER BY t_1.c1;
4141 NOTICE: nested_planner(2)
4142 NOTICE: nested_planner(1)
4151 ---------------------------------------
4152 Index Only Scan using t1_i1 on t1 t_1
4156 EXPLAIN (COSTS false) SELECT nested_planner(2) FROM s1.t1 t_1 ORDER BY t_1.c1;
4157 NOTICE: nested_planner(2)
4158 NOTICE: nested_planner(1)
4174 ---------------------------------------
4175 Index Only Scan using t1_i1 on t1 t_1
4180 -- Redefine not to use cached plan
4182 CREATE OR REPLACE FUNCTION nested_planner(cnt int) RETURNS int AS $$
4186 RAISE NOTICE 'nested_planner(%)', cnt;
4193 SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1) INTO new_cnt
4195 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
4196 ORDER BY t_1.c1 LIMIT 1;
4200 $$ LANGUAGE plpgsql IMMUTABLE;
4201 -- The function called at the bottom desn't use a hint, the immediate
4202 -- caller level should restore its own hint. So, the first LOG from
4203 -- pg_hint_plan should use the IndexScan(t_1) hint
4204 EXPLAIN (COSTS false) SELECT nested_planner(5) FROM s1.t1 t_1 ORDER BY t_1.c1;
4205 NOTICE: nested_planner(5)
4206 NOTICE: nested_planner(4)
4207 NOTICE: nested_planner(3)
4208 NOTICE: nested_planner(2)
4209 NOTICE: nested_planner(1)
4239 ---------------------------------------
4240 Index Only Scan using t1_i1 on t1 t_1
4243 -- The top level uses SeqScan(t_1), but the function should use only
4244 -- the hint in the function.
4245 /*+SeqScan(t_1) SeqScan(t_2)*/
4246 EXPLAIN (COSTS false) SELECT nested_planner(5) FROM s1.t1 t_1 ORDER BY t_1.c1;
4247 NOTICE: nested_planner(5)
4248 NOTICE: nested_planner(4)
4249 NOTICE: nested_planner(3)
4250 NOTICE: nested_planner(2)
4251 NOTICE: nested_planner(1)
4289 --------------------------
4292 -> Seq Scan on t1 t_1
4296 ---- No. A-13-4 output of debugging log on hint status
4298 CREATE OR REPLACE FUNCTION recall_planner() RETURNS int AS $$
4299 SELECT /*+ IndexScan(t_1) */t_1.c1
4301 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
4302 ORDER BY t_1.c1 LIMIT 1;
4303 $$ LANGUAGE SQL IMMUTABLE;
4305 -- recall_planner() is reduced to constant while planning using the
4306 -- hint defined in the function. Then the outer query is planned based
4307 -- on the following hint. pg_hint_plan shows the log for the function
4308 -- but the resulting explain output doesn't contain the corresponding
4310 /*+HashJoin(t_1 t_2)*/
4311 EXPLAIN (COSTS false)
4312 SELECT recall_planner() FROM s1.t1 t_1
4313 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
4330 --------------------------------------
4334 Hash Cond: (t_1.c1 = t_2.c1)
4335 -> Seq Scan on t1 t_1
4337 -> Seq Scan on t2 t_2
4341 --See description for No.13-4-1
4342 /*+HashJoin(st_1 st_2)*/
4343 EXPLAIN (COSTS false)
4344 SELECT recall_planner() FROM s1.t1 st_1
4345 JOIN s1.t2 st_2 ON (st_1.c1 = st_2.c1)
4362 ----------------------------------------
4366 Hash Cond: (st_1.c1 = st_2.c1)
4367 -> Seq Scan on t1 st_1
4369 -> Seq Scan on t2 st_2
4373 --See description for No.13-4-1
4374 /*+HashJoin(t_1 t_2)*/
4375 EXPLAIN (COSTS false)
4376 SELECT recall_planner() FROM s1.t1 st_1
4377 JOIN s1.t2 st_2 ON (st_1.c1 = st_2.c1)
4394 ----------------------------------------------
4396 Merge Cond: (st_1.c1 = st_2.c1)
4397 -> Index Only Scan using t1_i1 on t1 st_1
4400 -> Seq Scan on t2 st_2
4404 --See description for No.13-4-1
4405 /*+HashJoin(st_1 st_2)*/
4406 EXPLAIN (COSTS false)
4407 SELECT recall_planner() FROM s1.t1 t_1
4408 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
4425 ---------------------------------------------
4427 Merge Cond: (t_1.c1 = t_2.c1)
4428 -> Index Only Scan using t1_i1 on t1 t_1
4431 -> Seq Scan on t2 t_2
4435 -- See description for No.13-4-1. No joins in ths plan, so
4436 -- pg_hint_plan doesn't complain on the wrongly written error hint.
4437 /*+HashJoin(t_1 t_1)*/
4438 EXPLAIN (COSTS false)
4439 SELECT recall_planner() FROM s1.t1 t_1
4456 ---------------------------------------
4457 Index Only Scan using t1_i1 on t1 t_1
4461 CREATE OR REPLACE FUNCTION recall_planner_one_t() RETURNS int AS $$
4462 SELECT /*+ IndexScan(t_1) */t_1.c1
4464 ORDER BY t_1.c1 LIMIT 1;
4465 $$ LANGUAGE SQL IMMUTABLE;
4466 EXPLAIN (COSTS false)
4467 SELECT recall_planner_one_t() FROM s1.t1 t_1
4468 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
4478 ---------------------------------------------
4480 Merge Cond: (t_1.c1 = t_2.c1)
4481 -> Index Only Scan using t1_i1 on t1 t_1
4484 -> Seq Scan on t2 t_2
4487 /*+HashJoin(t_1 t_1)*/
4488 EXPLAIN (COSTS false)
4489 SELECT recall_planner_one_t() FROM s1.t1 t_1
4490 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
4499 INFO: pg_hint_plan: hint syntax error at or near "HashJoin(t_1 t_1)"
4500 DETAIL: Relation name "t_1" is duplicated.
4509 ---------------------------------------------
4511 Merge Cond: (t_1.c1 = t_2.c1)
4512 -> Index Only Scan using t1_i1 on t1 t_1
4515 -> Seq Scan on t2 t_2
4518 DROP FUNCTION recall_planner_one_t(int);
4519 ERROR: function recall_planner_one_t(integer) does not exist
4521 -- See description for No.13-4-1. Complains on the wrongly written hint.
4522 /*+HashJoin(t_1 t_1)*/
4523 EXPLAIN (COSTS false)
4524 SELECT recall_planner() FROM s1.t1 t_1
4525 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
4534 INFO: pg_hint_plan: hint syntax error at or near "HashJoin(t_1 t_1)"
4535 DETAIL: Relation name "t_1" is duplicated.
4544 ---------------------------------------------
4546 Merge Cond: (t_1.c1 = t_2.c1)
4547 -> Index Only Scan using t1_i1 on t1 t_1
4550 -> Seq Scan on t2 t_2
4554 /*+MergeJoin(t_1 t_2)HashJoin(t_1 t_2)*/
4555 EXPLAIN (COSTS false)
4556 SELECT recall_planner() FROM s1.t1 t_1
4557 JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
4559 INFO: pg_hint_plan: hint syntax error at or near "MergeJoin(t_1 t_2)HashJoin(t_1 t_2)"
4560 DETAIL: Conflict join method hint.
4577 --------------------------------------
4581 Hash Cond: (t_1.c1 = t_2.c1)
4582 -> Seq Scan on t1 t_1
4584 -> Seq Scan on t2 t_2
4587 --No.14-1-1 plancache invalidation
4588 CREATE TABLE s1.tpc AS SELECT a FROM generate_series(0, 999) a;
4589 CREATE INDEX ON s1.tpc(a);
4590 PREPARE p1 AS SELECT * FROM s1.tpc WHERE a < 999;
4591 /*+ IndexScan(tpc) */PREPARE p2 AS SELECT * FROM s1.tpc WHERE a < 999;
4592 /*+ SeqScan(tpc) */PREPARE p3(int) AS SELECT * FROM s1.tpc WHERE a = $1;
4595 ------------------------------------------------------
4596 Seq Scan on tpc (cost=0.00..17.50 rows=333 width=4)
4609 ------------------------------------------------------------------------
4610 Index Scan using tpc_a_idx on tpc (cost=0.28..34.10 rows=333 width=4)
4611 Index Cond: (a < 999)
4614 EXPLAIN EXECUTE p3(500);
4623 ----------------------------------------------------
4624 Seq Scan on tpc (cost=0.00..17.50 rows=5 width=4)
4628 -- The DROP invalidates the plan caches
4631 ERROR: relation "s1.tpc" does not exist
4633 ERROR: relation "s1.tpc" does not exist
4634 EXPLAIN EXECUTE p3(500);
4635 ERROR: relation "s1.tpc" does not exist
4636 CREATE TABLE s1.tpc AS SELECT a FROM generate_series(0, 999) a;
4637 CREATE INDEX ON s1.tpc(a);
4640 ------------------------------------------------------
4641 Seq Scan on tpc (cost=0.00..17.50 rows=333 width=4)
4654 ------------------------------------------------------------------------
4655 Index Scan using tpc_a_idx on tpc (cost=0.28..34.10 rows=333 width=4)
4656 Index Cond: (a < 999)
4659 EXPLAIN EXECUTE p3(500);
4668 ----------------------------------------------------
4669 Seq Scan on tpc (cost=0.00..17.50 rows=5 width=4)