LOAD 'pg_hint_plan';
-SET pg_hint_plan.enable TO on;
+SET pg_hint_plan.enable_hint TO on;
SET pg_hint_plan.debug_print TO on;
SET client_min_messages TO LOG;
SET search_path TO public;
----
----- No. A-5-1 hint format
+---- No.A-1-1 install
+---- No.A-2-1 uninstall
+----
+-- No.A-1-1-3
+CREATE EXTENSION pg_hint_plan;
+-- No.A-1-2-3
+DROP EXTENSION pg_hint_plan;
+-- No.A-1-1-4
+CREATE SCHEMA other_schema;
+CREATE EXTENSION pg_hint_plan SCHEMA other_schema;
+ERROR: extension "pg_hint_plan" must be installed in schema "hint_plan"
+CREATE EXTENSION pg_hint_plan;
+DROP SCHEMA other_schema;
+----
+---- No. A-5-1 comment pattern
----
-- No. A-5-1-1
/*+SeqScan(t1)*/
(2 rows)
-- No. A-5-1-3
---+SeqScan(t1)
+/*SeqScan(t1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
QUERY PLAN
------------------------------
(2 rows)
-- No. A-5-1-5
--- +SeqScan(t1)
+/* /*+SeqScan(t1)*/ */
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
QUERY PLAN
------------------------------
Index Cond: (c1 = 1)
(2 rows)
--- No. A-5-1-6
---SeqScan(t1)
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
- QUERY PLAN
-------------------------------
- Index Scan using t1_i1 on t1
+----
+---- No. A-5-2 hint position
+----
+-- No. A-5-2-1
+EXPLAIN (COSTS false) SELECT c1 FROM s1.t1 WHERE t1.c1 = 1;
+ QUERY PLAN
+-----------------------------------
+ Index Only Scan using t1_i1 on t1
Index Cond: (c1 = 1)
(2 rows)
--- No. A-5-1-7
-/*+SeqScan(t1) /* nest comment */ */
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
-INFO: hint syntax error at or near "/* nest comment */ */
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;"
-DETAIL: Block comments nest doesn't supported.
- QUERY PLAN
-------------------------------
- Index Scan using t1_i1 on t1
+/*+SeqScan(t1)*/
+EXPLAIN (COSTS false) SELECT c1 FROM s1.t1 WHERE t1.c1 = 1;
+LOG: pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------
+ Seq Scan on t1
+ Filter: (c1 = 1)
+(2 rows)
+
+-- No. A-5-2-2
+EXPLAIN (COSTS false) SELECT c1, c2 AS c_2 /*+SeqScan(t1)*/ FROM s1.t1 WHERE t1.c1 = 1;
+LOG: pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------
+ Seq Scan on t1
+ Filter: (c1 = 1)
+(2 rows)
+
+-- No. A-5-2-3
+EXPLAIN (COSTS false) SELECT c1 AS "c1"/*+SeqScan(t1)*/ FROM s1.t1 WHERE t1.c1 = 1;
+ QUERY PLAN
+-----------------------------------
+ Index Only Scan using t1_i1 on t1
Index Cond: (c1 = 1)
(2 rows)
--- No. A-5-1-8
-/* +SeqScan(t1) /* nest comment */ */
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+-- No. A-5-2-4
+EXPLAIN (COSTS false) SELECT * /*+SeqScan(t1)*/ FROM s1.t1 WHERE t1.c1 = 1;
QUERY PLAN
------------------------------
Index Scan using t1_i1 on t1
Index Cond: (c1 = 1)
(2 rows)
--- No. A-5-1-9
-/*SeqScan(t1) /* nest comment */ */
+----
+---- No. A-6-1 hint's table definition
+----
+SET pg_hint_plan.enable_hint_table TO on;
+-- No. A-6-1-1
+\d hint_plan.hints
+ Table "hint_plan.hints"
+ Column | Type | Collation | Nullable | Default
+-------------------+---------+-----------+----------+---------------------------------------------
+ id | integer | | not null | nextval('hint_plan.hints_id_seq'::regclass)
+ norm_query_string | text | | not null |
+ application_name | text | | not null |
+ hints | text | | not null |
+Indexes:
+ "hints_pkey" PRIMARY KEY, btree (id)
+ "hints_norm_and_app" UNIQUE, btree (norm_query_string, application_name)
+
+----
+---- No. A-6-2 search condition
+----
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
QUERY PLAN
------------------------------
Index Cond: (c1 = 1)
(2 rows)
-----
----- No. A-5-2 hint position
-----
--- No. A-5-2-1
-/*+SeqScan(t1)*/
+-- No. A-6-2-1
+INSERT INTO hint_plan.hints (norm_query_string, application_name, hints)
+ VALUES (
+ 'EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = ?;',
+ '',
+ 'SeqScan(t1)');
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
LOG: pg_hint_plan:
used hint:
Filter: (c1 = 1)
(2 rows)
--- No. A-5-2-2
-/* normal comment */
-/*+SeqScan(t1)*/
+-- No. A-6-2-2
+INSERT INTO hint_plan.hints (norm_query_string, application_name, hints)
+ VALUES (
+ 'EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = ?;',
+ 'psql',
+ 'BitmapScan(t1)');
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+LOG: pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------
+ Seq Scan on t1
+ Filter: (c1 = 1)
+(2 rows)
+
+TRUNCATE hint_plan.hints;
+-- No. A-6-2-3
+INSERT INTO hint_plan.hints (norm_query_string, application_name, hints)
+ VALUES (
+ 'EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = ?;',
+ 'dummy_application_name',
+ 'SeqScan(t1)'
+);
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
QUERY PLAN
------------------------------
Index Cond: (c1 = 1)
(2 rows)
--- No. A-5-2-3
-EXPLAIN (COSTS false) SELECT /*+SeqScan(t1)*/ * FROM s1.t1 WHERE t1.c1 = 1;
+TRUNCATE hint_plan.hints;
+-- No. A-6-2-4
+INSERT INTO hint_plan.hints (norm_query_string, application_name, hints)
+ VALUES (
+ 'EXPLAIN (COSTS false) SELECT * FROM s1.t1;',
+ '',
+ 'SeqScan(t1)'
+);
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
QUERY PLAN
------------------------------
Index Scan using t1_i1 on t1
Index Cond: (c1 = 1)
(2 rows)
+TRUNCATE hint_plan.hints;
+----
+---- No. A-6-3 number of constant
+----
+-- No. A-6-3-1
+INSERT INTO hint_plan.hints (norm_query_string, application_name, hints)
+ VALUES (
+ 'EXPLAIN (COSTS false) SELECT c1 FROM s1.t1;',
+ '',
+ 'SeqScan(t1)'
+);
+EXPLAIN (COSTS false) SELECT c1 FROM s1.t1;
+LOG: pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+----------------
+ Seq Scan on t1
+(1 row)
+
+TRUNCATE hint_plan.hints;
+-- No. A-6-3-2
+INSERT INTO hint_plan.hints (norm_query_string, application_name, hints)
+ VALUES (
+ 'EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = ?;',
+ '',
+ 'SeqScan(t1)'
+);
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+LOG: pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------
+ Seq Scan on t1
+ Filter: (c1 = 1)
+(2 rows)
+
+TRUNCATE hint_plan.hints;
+-- No. A-6-3-3
+INSERT INTO hint_plan.hints (norm_query_string, application_name, hints)
+ VALUES (
+ 'EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = ? OR t1.c1 = ?;',
+ '',
+ 'SeqScan(t1)'
+);
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 OR t1.c1 = 0;
+LOG: pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+----------------------------------
+ Seq Scan on t1
+ Filter: ((c1 = 1) OR (c1 = 0))
+(2 rows)
+
+TRUNCATE hint_plan.hints;
+SET pg_hint_plan.enable_hint_table TO off;
----
----- No. A-5-4 hint delimiter
+---- No. A-7-2 hint delimiter
----
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
QUERY PLAN
Index Cond: (c1 = 1)
(2 rows)
--- No. A-5-4-1
--- No. A-5-4-2
--- No. A-5-4-3
--- No. A-5-4-4
--- No. A-5-4-5
--- No. A-5-4-6
--- No. A-5-4-7
+-- No. A-7-2-1
+-- No. A-7-2-2
+-- No. A-7-2-3
+-- No. A-7-2-4
+-- No. A-7-2-5
+-- No. A-7-2-6
+-- No. A-7-2-7
/*+Set(enable_indexscan"off")Set(enable_bitmapscan"off")*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
LOG: pg_hint_plan:
Filter: (c1 = 1)
(2 rows)
--- No. A-5-4-8
+-- No. A-7-2-8
/*+ Set(enable_indexscan"off")Set(enable_bitmapscan"off")*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
LOG: pg_hint_plan:
Filter: (c1 = 1)
(2 rows)
--- No. A-5-4-9
+-- No. A-7-2-9
/*+Set(enable_indexscan"off")Set(enable_bitmapscan"off") */
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
LOG: pg_hint_plan:
Filter: (c1 = 1)
(2 rows)
--- No. A-5-4-10
+-- No. A-7-2-10
/*+ Set (enable_indexscan"off") Set (enable_bitmapscan"off")*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
LOG: pg_hint_plan:
Filter: (c1 = 1)
(2 rows)
--- No. A-5-4-11
+-- No. A-7-2-11
/*+Set ( enable_indexscan"off")Set ( enable_bitmapscan"off")*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
LOG: pg_hint_plan:
Filter: (c1 = 1)
(2 rows)
--- No. A-5-4-12
+-- No. A-7-2-12
/*+Set(enable_indexscan"off" ) Set(enable_bitmapscan"off" ) */
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
LOG: pg_hint_plan:
Filter: (c1 = 1)
(2 rows)
--- No. A-5-4-13
+-- No. A-7-2-13
/*+Set( enable_indexscan "off" )Set( enable_bitmapscan "off" )*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
LOG: pg_hint_plan:
Filter: (c1 = 1)
(2 rows)
--- No. A-5-4-14
+-- No. A-7-2-14
/*+ Set ( enable_indexscan "off" ) Set ( enable_bitmapscan "off" ) */
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
LOG: pg_hint_plan:
Filter: (c1 = 1)
(2 rows)
--- No. A-5-4-15
+-- No. A-7-2-15
/*+ Set(enable_indexscan"off")Set(enable_bitmapscan"off")*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
LOG: pg_hint_plan:
Filter: (c1 = 1)
(2 rows)
--- No. A-5-4-16
+-- No. A-7-2-16
/*+Set(enable_indexscan"off")Set(enable_bitmapscan"off") */
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
LOG: pg_hint_plan:
Filter: (c1 = 1)
(2 rows)
--- No. A-5-4-17
+-- No. A-7-2-17
/*+ Set (enable_indexscan"off") Set (enable_bitmapscan"off")*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
LOG: pg_hint_plan:
Filter: (c1 = 1)
(2 rows)
--- No. A-5-4-18
+-- No. A-7-2-18
/*+Set ( enable_indexscan"off")Set ( enable_bitmapscan"off")*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
LOG: pg_hint_plan:
Filter: (c1 = 1)
(2 rows)
--- No. A-5-4-19
+-- No. A-7-2-19
/*+Set(enable_indexscan"off" ) Set(enable_bitmapscan"off" ) */
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
LOG: pg_hint_plan:
Filter: (c1 = 1)
(2 rows)
--- No. A-5-4-20
+-- No. A-7-2-20
/*+Set( enable_indexscan "off" )Set( enable_bitmapscan "off" )*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
LOG: pg_hint_plan:
Filter: (c1 = 1)
(2 rows)
--- No. A-5-4-21
+-- No. A-7-2-21
/*+ Set ( enable_indexscan "off" ) Set ( enable_bitmapscan "off" ) */
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
LOG: pg_hint_plan:
Filter: (c1 = 1)
(2 rows)
--- No. A-5-4-22
+-- No. A-7-2-22
/*+
Set(enable_indexscan"off")Set(enable_bitmapscan"off")*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
Filter: (c1 = 1)
(2 rows)
--- No. A-5-4-23
+-- No. A-7-2-23
/*+Set(enable_indexscan"off")Set(enable_bitmapscan"off")
*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
Filter: (c1 = 1)
(2 rows)
--- No. A-5-4-24
+-- No. A-7-2-24
/*+
Set
(enable_indexscan"off")
Filter: (c1 = 1)
(2 rows)
--- No. A-5-4-25
+-- No. A-7-2-25
/*+Set
(
enable_indexscan"off")Set
Filter: (c1 = 1)
(2 rows)
--- No. A-5-4-26
+-- No. A-7-2-26
/*+Set(enable_indexscan"off"
)
Set(enable_bitmapscan"off"
Filter: (c1 = 1)
(2 rows)
--- No. A-5-4-27
+-- No. A-7-2-27
/*+Set(
enable_indexscan
"off"
Filter: (c1 = 1)
(2 rows)
--- No. A-5-4-28
+-- No. A-7-2-28
/*+
Set
(
Filter: (c1 = 1)
(2 rows)
--- No. A-5-4-29
+-- No. A-7-2-29
/*+
Set(enable_indexscan"off")Set(enable_bitmapscan"off")*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
Filter: (c1 = 1)
(2 rows)
--- No. A-5-4-30
+-- No. A-7-2-30
/*+Set(enable_indexscan"off")Set(enable_bitmapscan"off")
*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
Filter: (c1 = 1)
(2 rows)
--- No. A-5-4-31
+-- No. A-7-2-31
/*+
Set
(enable_indexscan"off")
Filter: (c1 = 1)
(2 rows)
--- No. A-5-4-32
+-- No. A-7-2-32
/*+Set
(
enable_indexscan"off")Set
Filter: (c1 = 1)
(2 rows)
--- No. A-5-4-33
+-- No. A-7-2-33
/*+Set(enable_indexscan"off"
)
Set(enable_bitmapscan"off"
Filter: (c1 = 1)
(2 rows)
--- No. A-5-4-34
+-- No. A-7-2-34
/*+Set(
enable_indexscan
"off"
Filter: (c1 = 1)
(2 rows)
--- No. A-5-4-35
+-- No. A-7-2-35
/*+
Set
(
(2 rows)
----
----- No. A-5-5 hint object pattern
----- No. A-7-2 message object pattern
+---- No. A-7-3 hint object pattern
+---- No. A-9-2 message object pattern
----
--- No. A-5-5-1
--- No. A-7-2-1
+-- No. A-7-3-1
+-- No. A-9-2-1
/*+SeqScan(t)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 t WHERE t.c1 = 1;
LOG: pg_hint_plan:
Filter: (c1 = 1)
(2 rows)
--- No. A-5-5-2
--- No. A-7-2-2
+-- No. A-7-3-2
+-- No. A-9-2-2
/*+SeqScan(T)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 "T" WHERE "T".c1 = 1;
LOG: pg_hint_plan:
Filter: (c1 = 1)
(2 rows)
--- No. A-5-5-3
--- No. A-7-2-3
+-- No. A-7-3-3
+-- No. A-9-2-3
/*+SeqScan(()*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 "(" WHERE "(".c1 = 1;
-LOG: pg_hint_plan:
-used hint:
-SeqScan(()
-not used hint:
-duplication hint:
-error hint:
-
- QUERY PLAN
---------------------
- Seq Scan on t1 "("
- Filter: (c1 = 1)
-(2 rows)
-
-/*+SeqScan(((()*/
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 "(((" WHERE "(((".c1 = 1;
-LOG: pg_hint_plan:
-used hint:
-SeqScan(((()
-not used hint:
-duplication hint:
-error hint:
-
- QUERY PLAN
-----------------------
- Seq Scan on t1 "((("
- Filter: (c1 = 1)
+INFO: pg_hint_plan: hint syntax error at or near "()"
+DETAIL: Zero-length delimited string.
+ QUERY PLAN
+----------------------------------
+ Index Scan using t1_i1 on t1 "("
+ Index Cond: (c1 = 1)
(2 rows)
/*+SeqScan("(")*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 "(" WHERE "(".c1 = 1;
LOG: pg_hint_plan:
used hint:
-SeqScan(()
+SeqScan("(")
not used hint:
duplication hint:
error hint:
Filter: (c1 = 1)
(2 rows)
-/*+SeqScan("(((")*/
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 "(((" WHERE "(((".c1 = 1;
+-- No. A-7-3-4
+-- No. A-9-2-4
+/*+SeqScan())*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 ")" WHERE ")".c1 = 1;
+INFO: pg_hint_plan: hint syntax error at or near ")"
+DETAIL: SeqScan hint requires a relation.
+INFO: pg_hint_plan: hint syntax error at or near ")"
+DETAIL: Unrecognized hint keyword ")".
LOG: pg_hint_plan:
used hint:
-SeqScan(((()
not used hint:
duplication hint:
error hint:
+SeqScan()
- QUERY PLAN
-----------------------
- Seq Scan on t1 "((("
- Filter: (c1 = 1)
-(2 rows)
-
--- No. A-5-5-4
--- No. A-7-2-4
-/*+SeqScan())*/
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 ")" WHERE ")".c1 = 1;
-INFO: hint syntax error at or near "))"
-DETAIL: Relation name is necessary.
QUERY PLAN
----------------------------------
Index Scan using t1_i1 on t1 ")"
Filter: (c1 = 1)
(2 rows)
--- No. A-5-5-5
--- No. A-7-2-5
+-- No. A-7-3-5
+-- No. A-9-2-5
/*+SeqScan(")*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 """" WHERE """".c1 = 1;
-INFO: hint syntax error at or near ""
-DETAIL: Unterminated quoted relation name.
+INFO: pg_hint_plan: hint syntax error at or near ""
+DETAIL: Unterminated quoted string.
QUERY PLAN
-----------------------------------
Index Scan using t1_i1 on t1 """"
Filter: (c1 = 1)
(2 rows)
--- No. A-5-5-6
--- No. A-7-2-6
+-- No. A-7-3-6
+-- No. A-9-2-6
/*+SeqScan( )*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 " " WHERE " ".c1 = 1;
-INFO: hint syntax error at or near ")"
-DETAIL: Relation name is necessary.
+INFO: pg_hint_plan: hint syntax error at or near ""
+DETAIL: SeqScan hint requires a relation.
+LOG: pg_hint_plan:
+used hint:
+not used hint:
+duplication hint:
+error hint:
+SeqScan()
+
QUERY PLAN
----------------------------------
Index Scan using t1_i1 on t1 " "
Filter: (c1 = 1)
(2 rows)
--- No. A-5-5-7
--- No. A-7-2-7
+-- No. A-7-3-7
+-- No. A-9-2-7
/*+SeqScan( )*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 " " WHERE " ".c1 = 1;
-INFO: hint syntax error at or near ")"
-DETAIL: Relation name is necessary.
+INFO: pg_hint_plan: hint syntax error at or near ""
+DETAIL: SeqScan hint requires a relation.
+LOG: pg_hint_plan:
+used hint:
+not used hint:
+duplication hint:
+error hint:
+SeqScan()
+
QUERY PLAN
-----------------------------------
Index Scan using t1_i1 on t1 " "
Filter: (c1 = 1)
(2 rows)
--- No. A-5-5-8
--- No. A-7-2-8
+-- No. A-7-3-8
+-- No. A-9-2-8
/*+SeqScan(
)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 "
" WHERE "
".c1 = 1;
-INFO: hint syntax error at or near ")"
-DETAIL: Relation name is necessary.
+INFO: pg_hint_plan: hint syntax error at or near ""
+DETAIL: SeqScan hint requires a relation.
+LOG: pg_hint_plan:
+used hint:
+not used hint:
+duplication hint:
+error hint:
+SeqScan()
+
QUERY PLAN
--------------------------------
Index Scan using t1_i1 on t1 "
Filter: (c1 = 1)
(5 rows)
--- No. A-5-5-9
--- No. A-7-2-9
+-- No. A-7-3-9
+-- No. A-9-2-9
/*+SeqScan(Set)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 "Set" WHERE "Set".c1 = 1;
LOG: pg_hint_plan:
Filter: (c1 = 1)
(2 rows)
--- No. A-5-5-10
--- No. A-7-2-10
+-- No. A-7-3-10
+-- No. A-9-2-10
/*+SeqScan(あ)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 あ WHERE あ.c1 = 1;
LOG: pg_hint_plan:
Filter: (c1 = 1)
(2 rows)
--- No. A-5-5-11
--- No. A-7-2-11
+-- No. A-7-3-11
+-- No. A-9-2-11
/*+SeqScan(/**/)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 "/**/" WHERE "/**/".c1 = 1;
-INFO: hint syntax error at or near "/**/)*/
+INFO: pg_hint_plan: hint syntax error at or near "/**/)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 "/**/" WHERE "/**/".c1 = 1;"
-DETAIL: Block comments nest doesn't supported.
+DETAIL: Nested block comments are not supported.
QUERY PLAN
-------------------------------------
Index Scan using t1_i1 on t1 "/**/"
/*+SeqScan(/**//**//**/)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 "/**//**//**/" WHERE "/**//**//**/".c1 = 1;
-INFO: hint syntax error at or near "/**//**//**/)*/
+INFO: pg_hint_plan: hint syntax error at or near "/**//**//**/)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 "/**//**//**/" WHERE "/**//**//**/".c1 = 1;"
-DETAIL: Block comments nest doesn't supported.
+DETAIL: Nested block comments are not supported.
QUERY PLAN
---------------------------------------------
Index Scan using t1_i1 on t1 "/**//**//**/"
Index Cond: (c1 = 1)
(2 rows)
--- No. A-5-5-12
--- No. A-7-2-12
+-- No. A-7-3-12
+-- No. A-9-2-12
/*+SeqScan("tT()""
Set/**/あ")*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 "tT()""
Set/**/あ" WHERE "tT()""
Set/**/あ".c1 = 1;
-INFO: hint syntax error at or near "/**/あ")*/
+INFO: pg_hint_plan: hint syntax error at or near "/**/あ")*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 "tT()""
Set/**/あ" WHERE "tT()""
Set/**/あ".c1 = 1;"
-DETAIL: Block comments nest doesn't supported.
+DETAIL: Nested block comments are not supported.
QUERY PLAN
------------------------------------------
Index Scan using t1_i1 on t1 "tT()""
Filter: (c1 = 1)
(3 rows)
+-- No. A-7-3-13
+-- No. A-9-2-13
+/*+SeqScan(a123456789b123456789c123456789d123456789e123456789f123)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 "123456789012345678901234567890123456789012345678901234" WHERE "123456789012345678901234567890123456789012345678901234".c1 = 1;
+LOG: pg_hint_plan:
+used hint:
+not used hint:
+SeqScan(a123456789b123456789c123456789d123456789e123456789f123)
+duplication hint:
+error hint:
+
+ QUERY PLAN
+---------------------------------------------------------------------------------------
+ Index Scan using t1_i1 on t1 "123456789012345678901234567890123456789012345678901234"
+ Index Cond: (c1 = 1)
+(2 rows)
+
----
----- No. A-5-6 hint parse error
+---- No. A-7-4 hint parse error
----
--- No. A-5-6-1
+-- No. A-7-4-1
/*+Set(enable_indexscan off)Set enable_tidscan off)Set(enable_bitmapscan off)SeqScan(t1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
-INFO: hint syntax error at or near "enable_tidscan off)Set(enable_bitmapscan off)SeqScan(t1)"
-DETAIL: Opened parenthesis is necessary.
+INFO: pg_hint_plan: hint syntax error at or near "enable_tidscan off)Set(enable_bitmapscan off)SeqScan(t1)"
+DETAIL: Opening parenthesis is necessary.
LOG: pg_hint_plan:
used hint:
Set(enable_indexscan off)
Index Cond: (c1 = 1)
(4 rows)
--- No. A-5-6-2
+-- No. A-7-4-2
/*+Set(enable_indexscan off)Set(enable_tidscan off Set(enable_bitmapscan off)SeqScan(t1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
-INFO: hint syntax error at or near "Set(enable_bitmapscan off)SeqScan(t1)"
-DETAIL: Closed parenthesis is necessary.
+INFO: pg_hint_plan: hint syntax error at or near "(enable_bitmapscan off)SeqScan(t1)"
+DETAIL: Zero-length delimited string.
LOG: pg_hint_plan:
used hint:
Set(enable_indexscan off)
Index Cond: (c1 = 1)
(4 rows)
--- No. A-5-6-3
+-- No. A-7-4-3
/*+Set(enable_indexscan off)Set(enable_tidscan "off)Set(enable_bitmapscan off)SeqScan(t1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
-INFO: hint syntax error at or near ""
-DETAIL: Unterminated quoted parameter value.
+INFO: pg_hint_plan: hint syntax error at or near ""
+DETAIL: Unterminated quoted string.
LOG: pg_hint_plan:
used hint:
Set(enable_indexscan off)
Index Cond: (c1 = 1)
(4 rows)
--- No. A-5-6-4
+-- No. A-7-4-4
/*+Set(enable_indexscan off)SeqScan("")Set(enable_bitmapscan off)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
-INFO: hint syntax error at or near ")Set(enable_bitmapscan off)"
-DETAIL: Relation name is necessary.
+INFO: pg_hint_plan: hint syntax error at or near ")Set(enable_bitmapscan off)"
+DETAIL: Zero-length delimited string.
LOG: pg_hint_plan:
used hint:
Set(enable_indexscan off)
Index Cond: (c1 = 1)
(4 rows)
--- No. A-5-6-5
+-- No. A-7-4-5
/*+Set(enable_indexscan off)NoSet(enable_tidscan off)Set(enable_bitmapscan off)SeqScan(t1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
-INFO: hint syntax error at or near "NoSet(enable_tidscan off)Set(enable_bitmapscan off)SeqScan(t1)"
-DETAIL: Keyword "NoSet" does not exist.
+INFO: pg_hint_plan: hint syntax error at or near "NoSet(enable_tidscan off)Set(enable_bitmapscan off)SeqScan(t1)"
+DETAIL: Unrecognized hint keyword "NoSet".
LOG: pg_hint_plan:
used hint:
Set(enable_indexscan off)
Index Cond: (c1 = 1)
(4 rows)
--- No. A-5-6-6
+-- No. A-7-4-6
/*+Set(enable_indexscan off)"Set"(enable_tidscan off)Set(enable_bitmapscan off)SeqScan(t1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
-INFO: hint syntax error at or near ""Set"(enable_tidscan off)Set(enable_bitmapscan off)SeqScan(t1)"
-DETAIL: Keyword ""Set"" does not exist.
+INFO: pg_hint_plan: hint syntax error at or near ""Set"(enable_tidscan off)Set(enable_bitmapscan off)SeqScan(t1)"
+DETAIL: Unrecognized hint keyword ""Set"".
LOG: pg_hint_plan:
used hint:
Set(enable_indexscan off)
Index Cond: (c1 = 1)
(4 rows)
--- No. A-5-6-7
+-- No. A-7-4-7
/*+Set(enable_indexscan off)Set(enable_tidscan /* value */off)Set(enable_bitmapscan off)SeqScan(t1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
-INFO: hint syntax error at or near "/* value */off)Set(enable_bitmapscan off)SeqScan(t1)*/
+INFO: pg_hint_plan: hint syntax error at or near "/* value */off)Set(enable_bitmapscan off)SeqScan(t1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;"
-DETAIL: Block comments nest doesn't supported.
+DETAIL: Nested block comments are not supported.
QUERY PLAN
------------------------------
Index Scan using t1_i1 on t1
(2 rows)
----
----- No. A-6-1 original GUC parameter
+---- No. A-8-1 original GUC parameter
----
--- No. A-6-1-1
-SET ROLE super_user;
+---- Don't test postgresql itself.
+-- No. A-8-1-1
+-- SET ROLE super_user;
+-- SET pg_hint_plan.debug_print TO off;
+-- SHOW pg_hint_plan.enable_hint;
+-- SHOW pg_hint_plan.debug_print;
+-- SHOW pg_hint_plan.parse_messages;
+-- SET pg_hint_plan.enable_hint TO off;
+-- SET pg_hint_plan.debug_print TO on;
+-- SET pg_hint_plan.parse_messages TO error;
+-- SHOW pg_hint_plan.enable_hint;
+-- SHOW pg_hint_plan.debug_print;
+-- SHOW pg_hint_plan.parse_messages;
+-- RESET pg_hint_plan.enable_hint;
+-- RESET pg_hint_plan.debug_print;
+-- RESET pg_hint_plan.parse_messages;
+-- SHOW pg_hint_plan.enable_hint;
+-- SHOW pg_hint_plan.debug_print;
+-- SHOW pg_hint_plan.parse_messages;
+--
+-- -- No. A-8-1-2
+-- SET ROLE normal_user;
+-- SHOW pg_hint_plan.enable_hint;
+-- SHOW pg_hint_plan.debug_print;
+-- SHOW pg_hint_plan.parse_messages;
+-- SET pg_hint_plan.enable_hint TO off;
+-- SET pg_hint_plan.debug_print TO on;
+-- SET pg_hint_plan.parse_messages TO error;
+-- SHOW pg_hint_plan.enable_hint;
+-- SHOW pg_hint_plan.debug_print;
+-- SHOW pg_hint_plan.parse_messages;
+-- RESET pg_hint_plan.enable_hint;
+-- RESET pg_hint_plan.debug_print;
+-- RESET pg_hint_plan.parse_messages;
+-- SHOW pg_hint_plan.enable_hint;
+-- SHOW pg_hint_plan.debug_print;
+-- SHOW pg_hint_plan.parse_messages;
+--
+-- RESET ROLE;
+----
+---- No. A-8-2 original GUC parameter pg_hint_plan.enable_hint
+----
+-- No. A-8-2-1
SET pg_hint_plan.debug_print TO off;
-SHOW pg_hint_plan.enable;
- pg_hint_plan.enable
----------------------
+SET pg_hint_plan.enable_hint TO on;
+SHOW pg_hint_plan.enable_hint;
+ pg_hint_plan.enable_hint
+--------------------------
on
(1 row)
-SHOW pg_hint_plan.debug_print;
- pg_hint_plan.debug_print
+/*+Set(enable_indexscan off)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+ QUERY PLAN
+----------------------------------
+ Bitmap Heap Scan on t1
+ Recheck Cond: (c1 = 1)
+ -> Bitmap Index Scan on t1_i1
+ Index Cond: (c1 = 1)
+(4 rows)
+
+-- No. A-8-2-2
+SET pg_hint_plan.enable_hint TO off;
+SHOW pg_hint_plan.enable_hint;
+ pg_hint_plan.enable_hint
--------------------------
off
(1 row)
-SHOW pg_hint_plan.parse_messages;
- pg_hint_plan.parse_messages
------------------------------
- info
-(1 row)
+/*+Set(enable_indexscan off)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+ QUERY PLAN
+------------------------------
+ Index Scan using t1_i1 on t1
+ Index Cond: (c1 = 1)
+(2 rows)
-SET pg_hint_plan.enable TO off;
-SET pg_hint_plan.debug_print TO on;
-SET pg_hint_plan.parse_messages TO error;
-SHOW pg_hint_plan.enable;
- pg_hint_plan.enable
----------------------
- off
+-- No. A-8-2-3
+-- Don't test PostgreSQL itself.
+-- SET pg_hint_plan.enable_hint TO DEFAULT;
+-- SHOW pg_hint_plan.enable_hint;
+-- /*+Set(enable_indexscan off)*/
+-- EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+-- No. A-8-2-4
+-- Don't test PostgreSQL itself
+-- SET pg_hint_plan.enable_hint TO enable;
+-- SHOW pg_hint_plan.enable_hint;
+----
+---- No. A-8-3 original GUC parameter pg_hint_plan.debug_print
+----
+-- No. A-8-3-1
+SET pg_hint_plan.enable_hint TO on;
+SHOW pg_hint_plan.enable_hint;
+ pg_hint_plan.enable_hint
+--------------------------
+ on
(1 row)
-SHOW pg_hint_plan.debug_print;
- pg_hint_plan.debug_print
---------------------------
- on
-(1 row)
-
-SHOW pg_hint_plan.parse_messages;
- pg_hint_plan.parse_messages
------------------------------
- error
-(1 row)
-
-RESET pg_hint_plan.enable;
-RESET pg_hint_plan.debug_print;
-RESET pg_hint_plan.parse_messages;
-SHOW pg_hint_plan.enable;
- pg_hint_plan.enable
----------------------
- on
-(1 row)
-
-SHOW pg_hint_plan.debug_print;
- pg_hint_plan.debug_print
---------------------------
- off
-(1 row)
-
-SHOW pg_hint_plan.parse_messages;
- pg_hint_plan.parse_messages
------------------------------
- info
-(1 row)
-
--- No. A-6-1-2
-SET ROLE normal_user;
-SHOW pg_hint_plan.enable;
- pg_hint_plan.enable
----------------------
- on
-(1 row)
-
-SHOW pg_hint_plan.debug_print;
- pg_hint_plan.debug_print
---------------------------
- off
-(1 row)
-
-SHOW pg_hint_plan.parse_messages;
- pg_hint_plan.parse_messages
------------------------------
- info
-(1 row)
-
-SET pg_hint_plan.enable TO off;
-SET pg_hint_plan.debug_print TO on;
-SET pg_hint_plan.parse_messages TO error;
-SHOW pg_hint_plan.enable;
- pg_hint_plan.enable
----------------------
- off
-(1 row)
-
-SHOW pg_hint_plan.debug_print;
- pg_hint_plan.debug_print
---------------------------
- on
-(1 row)
-
-SHOW pg_hint_plan.parse_messages;
- pg_hint_plan.parse_messages
------------------------------
- error
-(1 row)
-
-RESET pg_hint_plan.enable;
-RESET pg_hint_plan.debug_print;
-RESET pg_hint_plan.parse_messages;
-SHOW pg_hint_plan.enable;
- pg_hint_plan.enable
----------------------
- on
-(1 row)
-
-SHOW pg_hint_plan.debug_print;
- pg_hint_plan.debug_print
---------------------------
- off
-(1 row)
-
-SHOW pg_hint_plan.parse_messages;
- pg_hint_plan.parse_messages
------------------------------
- info
-(1 row)
-
-RESET ROLE;
-----
----- No. A-6-2 original GUC parameter pg_hint_plan.enable
-----
--- No. A-6-2-1
-SET pg_hint_plan.enable TO on;
-SHOW pg_hint_plan.enable;
- pg_hint_plan.enable
----------------------
- on
-(1 row)
-
-/*+Set(enable_indexscan off)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
- QUERY PLAN
-----------------------------------
- Bitmap Heap Scan on t1
- Recheck Cond: (c1 = 1)
- -> Bitmap Index Scan on t1_i1
- Index Cond: (c1 = 1)
-(4 rows)
-
--- No. A-6-2-2
-SET pg_hint_plan.enable TO off;
-SHOW pg_hint_plan.enable;
- pg_hint_plan.enable
----------------------
- off
-(1 row)
-
-/*+Set(enable_indexscan off)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
- QUERY PLAN
-------------------------------
- Index Scan using t1_i1 on t1
- Index Cond: (c1 = 1)
-(2 rows)
-
--- No. A-6-2-3
-SET pg_hint_plan.enable TO DEFAULT;
-SHOW pg_hint_plan.enable;
- pg_hint_plan.enable
----------------------
- on
-(1 row)
-
-/*+Set(enable_indexscan off)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
- QUERY PLAN
-----------------------------------
- Bitmap Heap Scan on t1
- Recheck Cond: (c1 = 1)
- -> Bitmap Index Scan on t1_i1
- Index Cond: (c1 = 1)
-(4 rows)
-
--- No. A-6-2-4
-SET pg_hint_plan.enable TO enable;
-ERROR: parameter "pg_hint_plan.enable" requires a Boolean value
-SHOW pg_hint_plan.enable;
- pg_hint_plan.enable
----------------------
- on
-(1 row)
-
-----
----- No. A-6-3 original GUC parameter pg_hint_plan.debug_print
-----
--- No. A-6-3-1
-SET pg_hint_plan.debug_print TO on;
+SET pg_hint_plan.debug_print TO on;
SHOW pg_hint_plan.debug_print;
pg_hint_plan.debug_print
--------------------------
Index Cond: (c1 = 1)
(4 rows)
--- No. A-6-3-2
+-- No. A-8-3-2
SET pg_hint_plan.debug_print TO off;
SHOW pg_hint_plan.debug_print;
pg_hint_plan.debug_print
Index Cond: (c1 = 1)
(4 rows)
--- No. A-6-3-3
+-- No. A-8-3-3
SET pg_hint_plan.debug_print TO DEFAULT;
SHOW pg_hint_plan.debug_print;
pg_hint_plan.debug_print
Index Cond: (c1 = 1)
(4 rows)
--- No. A-6-3-4
+-- No. A-8-3-4
SET pg_hint_plan.debug_print TO enable;
-ERROR: parameter "pg_hint_plan.debug_print" requires a Boolean value
+ERROR: invalid value for parameter "pg_hint_plan.debug_print": "enable"
+HINT: Available values: off, on, detailed, verbose.
SHOW pg_hint_plan.debug_print;
pg_hint_plan.debug_print
--------------------------
(1 row)
----
----- No. A-6-4 original GUC parameter pg_hint_plan.parse_messages
+---- No. A-8-4 original GUC parameter pg_hint_plan.parse_messages
----
SET client_min_messages TO debug5;
-DEBUG: CommitTransactionCommand
-DEBUG: CommitTransaction
-DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
--- No. A-6-4-1
+DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
+-- No. A-8-4-1
SET pg_hint_plan.parse_messages TO debug5;
-DEBUG: StartTransactionCommand
-DEBUG: StartTransaction
-DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
-DEBUG: ProcessUtility
-DEBUG: CommitTransactionCommand
-DEBUG: CommitTransaction
-DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
+DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
+DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
SHOW pg_hint_plan.parse_messages;
-DEBUG: StartTransactionCommand
-DEBUG: StartTransaction
-DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
-DEBUG: ProcessUtility
-DEBUG: CommitTransactionCommand
-DEBUG: CommitTransaction
-DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
+DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
+DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
pg_hint_plan.parse_messages
-----------------------------
debug5
(1 row)
/*+Set*/SELECT 1;
-DEBUG: StartTransactionCommand
-DEBUG: StartTransaction
-DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
-DEBUG: hint syntax error at or near ""
-DETAIL: Opened parenthesis is necessary.
-DEBUG: CommitTransactionCommand
-DEBUG: CommitTransaction
-DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
+DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
+DEBUG: pg_hint_plan: hint syntax error at or near ""
+DETAIL: Opening parenthesis is necessary.
+DEBUG: CommitTransaction(1) name: unnamed; blockState: STARTED; state: INPROGRESS, xid/subid/cid: 0/1/0
?column?
----------
1
(1 row)
SET client_min_messages TO debug4;
-DEBUG: StartTransactionCommand
-DEBUG: StartTransaction
-DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
-DEBUG: ProcessUtility
-DEBUG: CommitTransactionCommand
-DEBUG: CommitTransaction
-DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
+DEBUG: StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0
/*+Set*/SELECT 1;
-DEBUG: StartTransactionCommand
-DEBUG: StartTransaction
-DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
-DEBUG: CommitTransactionCommand
-DEBUG: CommitTransaction
-DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
?column?
----------
1
(1 row)
--- No. A-6-4-2
+-- No. A-8-4-2
SET pg_hint_plan.parse_messages TO debug4;
-DEBUG: StartTransactionCommand
-DEBUG: StartTransaction
-DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
-DEBUG: ProcessUtility
-DEBUG: CommitTransactionCommand
-DEBUG: CommitTransaction
-DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
SHOW pg_hint_plan.parse_messages;
-DEBUG: StartTransactionCommand
-DEBUG: StartTransaction
-DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
-DEBUG: ProcessUtility
-DEBUG: CommitTransactionCommand
-DEBUG: CommitTransaction
-DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
pg_hint_plan.parse_messages
-----------------------------
debug4
(1 row)
/*+Set*/SELECT 1;
-DEBUG: StartTransactionCommand
-DEBUG: StartTransaction
-DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
-DEBUG: hint syntax error at or near ""
-DETAIL: Opened parenthesis is necessary.
-DEBUG: CommitTransactionCommand
-DEBUG: CommitTransaction
-DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
+DEBUG: pg_hint_plan: hint syntax error at or near ""
+DETAIL: Opening parenthesis is necessary.
?column?
----------
1
(1 row)
SET client_min_messages TO debug3;
-DEBUG: StartTransactionCommand
-DEBUG: StartTransaction
-DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
-DEBUG: ProcessUtility
-DEBUG: CommitTransactionCommand
-DEBUG: CommitTransaction
-DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
/*+Set*/SELECT 1;
-DEBUG: StartTransactionCommand
-DEBUG: StartTransaction
-DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
-DEBUG: CommitTransactionCommand
-DEBUG: CommitTransaction
-DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
?column?
----------
1
(1 row)
--- No. A-6-4-3
+-- No. A-8-4-3
SET pg_hint_plan.parse_messages TO debug3;
-DEBUG: StartTransactionCommand
-DEBUG: StartTransaction
-DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
-DEBUG: ProcessUtility
-DEBUG: CommitTransactionCommand
-DEBUG: CommitTransaction
-DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
SHOW pg_hint_plan.parse_messages;
-DEBUG: StartTransactionCommand
-DEBUG: StartTransaction
-DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
-DEBUG: ProcessUtility
-DEBUG: CommitTransactionCommand
-DEBUG: CommitTransaction
-DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
pg_hint_plan.parse_messages
-----------------------------
debug3
(1 row)
/*+Set*/SELECT 1;
-DEBUG: StartTransactionCommand
-DEBUG: StartTransaction
-DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
-DEBUG: hint syntax error at or near ""
-DETAIL: Opened parenthesis is necessary.
-DEBUG: CommitTransactionCommand
-DEBUG: CommitTransaction
-DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
+DEBUG: pg_hint_plan: hint syntax error at or near ""
+DETAIL: Opening parenthesis is necessary.
?column?
----------
1
(1 row)
SET client_min_messages TO debug2;
-DEBUG: StartTransactionCommand
-DEBUG: StartTransaction
-DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
-DEBUG: ProcessUtility
/*+Set*/SELECT 1;
?column?
----------
1
(1 row)
--- No. A-6-4-4
+-- No. A-8-4-4
SET pg_hint_plan.parse_messages TO debug2;
SHOW pg_hint_plan.parse_messages;
pg_hint_plan.parse_messages
(1 row)
/*+Set*/SELECT 1;
-DEBUG: hint syntax error at or near ""
-DETAIL: Opened parenthesis is necessary.
+DEBUG: pg_hint_plan: hint syntax error at or near ""
+DETAIL: Opening parenthesis is necessary.
?column?
----------
1
1
(1 row)
--- No. A-6-4-5
+-- No. A-8-4-5
SET pg_hint_plan.parse_messages TO debug1;
SHOW pg_hint_plan.parse_messages;
pg_hint_plan.parse_messages
(1 row)
/*+Set*/SELECT 1;
-DEBUG: hint syntax error at or near ""
-DETAIL: Opened parenthesis is necessary.
+DEBUG: pg_hint_plan: hint syntax error at or near ""
+DETAIL: Opening parenthesis is necessary.
?column?
----------
1
1
(1 row)
--- No. A-6-4-6
+-- No. A-8-4-6
SET pg_hint_plan.parse_messages TO log;
SHOW pg_hint_plan.parse_messages;
pg_hint_plan.parse_messages
(1 row)
/*+Set*/SELECT 1;
-LOG: hint syntax error at or near ""
-DETAIL: Opened parenthesis is necessary.
+LOG: pg_hint_plan: hint syntax error at or near ""
+DETAIL: Opening parenthesis is necessary.
?column?
----------
1
1
(1 row)
--- No. A-6-4-7
+-- No. A-8-4-7
SET pg_hint_plan.parse_messages TO info;
SHOW pg_hint_plan.parse_messages;
pg_hint_plan.parse_messages
(1 row)
/*+Set*/SELECT 1;
-INFO: hint syntax error at or near ""
-DETAIL: Opened parenthesis is necessary.
+INFO: pg_hint_plan: hint syntax error at or near ""
+DETAIL: Opening parenthesis is necessary.
?column?
----------
1
SET client_min_messages TO notice;
/*+Set*/SELECT 1;
-INFO: hint syntax error at or near ""
-DETAIL: Opened parenthesis is necessary.
+INFO: pg_hint_plan: hint syntax error at or near ""
+DETAIL: Opening parenthesis is necessary.
?column?
----------
1
(1 row)
--- No. A-6-4-8
+-- No. A-8-4-8
SET pg_hint_plan.parse_messages TO notice;
SHOW pg_hint_plan.parse_messages;
pg_hint_plan.parse_messages
(1 row)
/*+Set*/SELECT 1;
-NOTICE: hint syntax error at or near ""
-DETAIL: Opened parenthesis is necessary.
+NOTICE: pg_hint_plan: hint syntax error at or near ""
+DETAIL: Opening parenthesis is necessary.
?column?
----------
1
1
(1 row)
--- No. A-6-4-9
+-- No. A-8-4-9
SET pg_hint_plan.parse_messages TO warning;
SHOW pg_hint_plan.parse_messages;
pg_hint_plan.parse_messages
(1 row)
/*+Set*/SELECT 1;
-WARNING: hint syntax error at or near ""
-DETAIL: Opened parenthesis is necessary.
+WARNING: pg_hint_plan: hint syntax error at or near ""
+DETAIL: Opening parenthesis is necessary.
?column?
----------
1
1
(1 row)
--- No. A-6-4-10
+-- No. A-8-4-10
SET pg_hint_plan.parse_messages TO error;
SHOW pg_hint_plan.parse_messages;
pg_hint_plan.parse_messages
(1 row)
/*+Set*/SELECT 1;
-ERROR: hint syntax error at or near ""
-DETAIL: Opened parenthesis is necessary.
-SET client_min_messages TO fatal;
+ERROR: pg_hint_plan: hint syntax error at or near ""
+DETAIL: Opening parenthesis is necessary.
+SET client_min_messages TO error;
/*+Set*/SELECT 1;
--- No. A-6-4-11
+ERROR: pg_hint_plan: hint syntax error at or near ""
+DETAIL: Opening parenthesis is necessary.
+-- No. A-8-4-11
RESET client_min_messages;
SET pg_hint_plan.parse_messages TO DEFAULT;
SHOW pg_hint_plan.parse_messages;
(1 row)
/*+Set*/SELECT 1;
-INFO: hint syntax error at or near ""
-DETAIL: Opened parenthesis is necessary.
+INFO: pg_hint_plan: hint syntax error at or near ""
+DETAIL: Opening parenthesis is necessary.
?column?
----------
1
(1 row)
--- No. A-6-4-12
+-- No. A-8-4-12
SET pg_hint_plan.parse_messages TO fatal;
ERROR: invalid value for parameter "pg_hint_plan.parse_messages": "fatal"
HINT: Available values: debug5, debug4, debug3, debug2, debug1, log, info, notice, warning, error.
info
(1 row)
--- No. A-6-4-13
+-- No. A-8-4-13
SET pg_hint_plan.parse_messages TO panic;
ERROR: invalid value for parameter "pg_hint_plan.parse_messages": "panic"
HINT: Available values: debug5, debug4, debug3, debug2, debug1, log, info, notice, warning, error.
info
(1 row)
--- No. A-6-4-14
+-- No. A-8-4-14
SET pg_hint_plan.parse_messages TO on;
ERROR: invalid value for parameter "pg_hint_plan.parse_messages": "on"
HINT: Available values: debug5, debug4, debug3, debug2, debug1, log, info, notice, warning, error.
(1 row)
----
----- No. A-7-1 parse error message output
+---- No. A-8-5 original GUC parameter pg_hint_plan.enable_hint_table
+----
+INSERT INTO hint_plan.hints (norm_query_string, application_name, hints)
+ VALUES (
+ 'EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = ?;',
+ '',
+ 'SeqScan(t1)');
+-- No. A-8-5-1
+SET pg_hint_plan.enable_hint_table TO on;
+SHOW pg_hint_plan.enable_hint_table;
+ pg_hint_plan.enable_hint_table
+--------------------------------
+ on
+(1 row)
+
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+ QUERY PLAN
+--------------------
+ Seq Scan on t1
+ Filter: (c1 = 1)
+(2 rows)
+
+-- No. A-8-5-2
+SET pg_hint_plan.enable_hint_table TO off;
+SHOW pg_hint_plan.enable_hint_table;
+ pg_hint_plan.enable_hint_table
+--------------------------------
+ off
+(1 row)
+
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+ QUERY PLAN
+------------------------------
+ Index Scan using t1_i1 on t1
+ Index Cond: (c1 = 1)
+(2 rows)
+
+-- No. A-8-5-3
+SET pg_hint_plan.enable_hint_table TO DEFAULT;
+SHOW pg_hint_plan.enable_hint_table;
+ pg_hint_plan.enable_hint_table
+--------------------------------
+ off
+(1 row)
+
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+ QUERY PLAN
+------------------------------
+ Index Scan using t1_i1 on t1
+ Index Cond: (c1 = 1)
+(2 rows)
+
+-- No. A-8-5-4
+SET pg_hint_plan.enable_hint_table TO enable;
+ERROR: parameter "pg_hint_plan.enable_hint_table" requires a Boolean value
+SHOW pg_hint_plan.enable_hint_table;
+ pg_hint_plan.enable_hint_table
+--------------------------------
+ off
+(1 row)
+
+TRUNCATE hint_plan.hints;
+----
+---- No. A-9-1 parse error message output
----
--- No. A-7-1-1
+-- No. A-9-1-1
/*+"Set"(enable_indexscan on)*/SELECT 1;
-INFO: hint syntax error at or near ""Set"(enable_indexscan on)"
-DETAIL: Keyword ""Set"" does not exist.
+INFO: pg_hint_plan: hint syntax error at or near ""Set"(enable_indexscan on)"
+DETAIL: Unrecognized hint keyword ""Set"".
?column?
----------
1
(1 row)
/*+Set()(enable_indexscan on)*/SELECT 1;
-INFO: hint syntax error at or near ")(enable_indexscan on)"
-DETAIL: Parameter name is necessary.
+INFO: pg_hint_plan: hint syntax error at or near "Set()(enable_indexscan on)"
+DETAIL: Set hint requires name and value of GUC parameter.
+INFO: pg_hint_plan: hint syntax error at or near "(enable_indexscan on)"
+DETAIL: Unrecognized hint keyword "".
?column?
----------
1
(1 row)
/*+Set(enable_indexscan on*/SELECT 1;
-INFO: hint syntax error at or near ""
-DETAIL: Closed parenthesis is necessary.
+INFO: pg_hint_plan: hint syntax error at or near ""
+DETAIL: Closing parenthesis is necessary.
?column?
----------
1
(1 row)
----
----- No. A-7-3 hint state output
+---- No. A-9-3 hint state output
----
SET pg_hint_plan.debug_print TO on;
SET client_min_messages TO LOG;
--- No. A-7-3-1
+-- No. A-9-3-1
/*+SeqScan(t1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
LOG: pg_hint_plan:
Filter: (c1 = 1)
(2 rows)
--- No. A-7-3-2
+-- No. A-9-3-2
/*+SeqScan(no_table)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
LOG: pg_hint_plan:
Index Cond: (c1 = 1)
(2 rows)
--- No. A-7-3-3
+-- No. A-9-3-3
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
QUERY PLAN
-----------------------------------
/*+TidScan(t1)BitmapScan(t1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
-INFO: hint syntax error at or near "TidScan(t1)BitmapScan(t1)"
+INFO: pg_hint_plan: hint syntax error at or near "TidScan(t1)BitmapScan(t1)"
DETAIL: Conflict scan method hint.
LOG: pg_hint_plan:
used hint:
/*+TidScan(t1)BitmapScan(t1)IndexScan(t1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
-INFO: hint syntax error at or near "TidScan(t1)BitmapScan(t1)IndexScan(t1)"
+INFO: pg_hint_plan: hint syntax error at or near "TidScan(t1)BitmapScan(t1)IndexScan(t1)"
DETAIL: Conflict scan method hint.
-INFO: hint syntax error at or near "BitmapScan(t1)IndexScan(t1)"
+INFO: pg_hint_plan: hint syntax error at or near "BitmapScan(t1)IndexScan(t1)"
DETAIL: Conflict scan method hint.
LOG: pg_hint_plan:
used hint:
/*+TidScan(t1)BitmapScan(t1)IndexScan(t1)SeqScan(t1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
-INFO: hint syntax error at or near "TidScan(t1)BitmapScan(t1)IndexScan(t1)SeqScan(t1)"
+INFO: pg_hint_plan: hint syntax error at or near "TidScan(t1)BitmapScan(t1)IndexScan(t1)SeqScan(t1)"
DETAIL: Conflict scan method hint.
-INFO: hint syntax error at or near "BitmapScan(t1)IndexScan(t1)SeqScan(t1)"
+INFO: pg_hint_plan: hint syntax error at or near "BitmapScan(t1)IndexScan(t1)SeqScan(t1)"
DETAIL: Conflict scan method hint.
-INFO: hint syntax error at or near "IndexScan(t1)SeqScan(t1)"
+INFO: pg_hint_plan: hint syntax error at or near "IndexScan(t1)SeqScan(t1)"
DETAIL: Conflict scan method hint.
LOG: pg_hint_plan:
used hint:
Filter: ((c1 = 1) AND (ctid = '(1,1)'::tid))
(2 rows)
--- No. A-7-3-4
+-- No. A-9-3-4
/*+Set(enable_indexscan enable)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
INFO: parameter "enable_indexscan" requires a Boolean value
(2 rows)
----
----- No. A-8-1 hint state output
+---- No. A-10-1 hint state output
----
PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
EXPLAIN (COSTS false) EXECUTE p1;
DEALLOCATE p1;
PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
-EXPLAIN (COSTS false) EXECUTE p1 (1);
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
+ QUERY PLAN
+-----------------------
+ Seq Scan on t1
+ Filter: (c1 < 1000)
+(2 rows)
+
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
+ QUERY PLAN
+-----------------------
+ Seq Scan on t1
+ Filter: (c1 < 1000)
+(2 rows)
+
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
+ QUERY PLAN
+-----------------------
+ Seq Scan on t1
+ Filter: (c1 < 1000)
+(2 rows)
+
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
+ QUERY PLAN
+-----------------------
+ Seq Scan on t1
+ Filter: (c1 < 1000)
+(2 rows)
+
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
+ QUERY PLAN
+-----------------------
+ Seq Scan on t1
+ Filter: (c1 < 1000)
+(2 rows)
+
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
QUERY PLAN
---------------------
Seq Scan on t1
(2 rows)
DEALLOCATE p1;
--- No. A-8-1-1
--- No. A-8-1-2
+-- No. A-10-1-1
+-- No. A-10-1-2
/*+SeqScan(t1)*/
PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+/*+BitmapScan(t1)*/
+EXPLAIN (COSTS false) EXECUTE p1;
LOG: pg_hint_plan:
used hint:
SeqScan(t1)
duplication hint:
error hint:
-/*+BitmapScan(t1)*/
-EXPLAIN (COSTS false) EXECUTE p1;
QUERY PLAN
--------------------
Seq Scan on t1
DEALLOCATE p1;
/*+BitmapScan(t1)*/
PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
+/*+SeqScan(t1)*/
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
LOG: pg_hint_plan:
used hint:
BitmapScan(t1)
duplication hint:
error hint:
+ QUERY PLAN
+----------------------------------
+ Bitmap Heap Scan on t1
+ Recheck Cond: (c1 < 1000)
+ -> Bitmap Index Scan on t1_i1
+ Index Cond: (c1 < 1000)
+(4 rows)
+
/*+SeqScan(t1)*/
-EXPLAIN (COSTS false) EXECUTE p1 (1);
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
+LOG: pg_hint_plan:
+used hint:
+BitmapScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
QUERY PLAN
----------------------------------
Bitmap Heap Scan on t1
- Recheck Cond: (c1 < $1)
+ Recheck Cond: (c1 < 1000)
-> Bitmap Index Scan on t1_i1
- Index Cond: (c1 < $1)
+ Index Cond: (c1 < 1000)
(4 rows)
-UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
/*+SeqScan(t1)*/
-EXPLAIN (COSTS false) EXECUTE p1 (1);
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
LOG: pg_hint_plan:
used hint:
BitmapScan(t1)
QUERY PLAN
----------------------------------
Bitmap Heap Scan on t1
- Recheck Cond: (c1 < $1)
+ Recheck Cond: (c1 < 1000)
-> Bitmap Index Scan on t1_i1
- Index Cond: (c1 < $1)
+ Index Cond: (c1 < 1000)
(4 rows)
-DEALLOCATE p1;
--- No. A-8-1-3
--- No. A-8-1-4
/*+SeqScan(t1)*/
-PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
LOG: pg_hint_plan:
used hint:
-SeqScan(t1)
+BitmapScan(t1)
not used hint:
duplication hint:
error hint:
-EXPLAIN (COSTS false) EXECUTE p1;
- QUERY PLAN
---------------------
- Seq Scan on t1
- Filter: (c1 = 1)
-(2 rows)
+ QUERY PLAN
+----------------------------------
+ Bitmap Heap Scan on t1
+ Recheck Cond: (c1 < 1000)
+ -> Bitmap Index Scan on t1_i1
+ Index Cond: (c1 < 1000)
+(4 rows)
-UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
-EXPLAIN (COSTS false) EXECUTE p1;
+/*+SeqScan(t1)*/
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
LOG: pg_hint_plan:
used hint:
-SeqScan(t1)
+BitmapScan(t1)
not used hint:
duplication hint:
error hint:
- QUERY PLAN
---------------------
- Seq Scan on t1
- Filter: (c1 = 1)
-(2 rows)
+ QUERY PLAN
+----------------------------------
+ Bitmap Heap Scan on t1
+ Recheck Cond: (c1 < 1000)
+ -> Bitmap Index Scan on t1_i1
+ Index Cond: (c1 < 1000)
+(4 rows)
-DEALLOCATE p1;
-/*+BitmapScan(t1)*/
-PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
+/*+SeqScan(t1)*/
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
LOG: pg_hint_plan:
used hint:
BitmapScan(t1)
duplication hint:
error hint:
-EXPLAIN (COSTS false) EXECUTE p1 (1);
QUERY PLAN
----------------------------------
Bitmap Heap Scan on t1
(4 rows)
UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
-EXPLAIN (COSTS false) EXECUTE p1 (1);
+/*+SeqScan(t1)*/
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
LOG: pg_hint_plan:
used hint:
BitmapScan(t1)
(4 rows)
DEALLOCATE p1;
--- No. A-8-1-5
--- No. A-8-1-6
+-- No. A-10-1-3
+-- No. A-10-1-4
+/*+SeqScan(t1)*/
PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
-/*+BitmapScan(t1)*/
EXPLAIN (COSTS false) EXECUTE p1;
- QUERY PLAN
-------------------------------
- Index Scan using t1_i1 on t1
- Index Cond: (c1 = 1)
-(2 rows)
-
-UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
-/*+BitmapScan(t1)*/
-EXPLAIN (COSTS false) EXECUTE p1;
- QUERY PLAN
-------------------------------
- Index Scan using t1_i1 on t1
- Index Cond: (c1 = 1)
-(2 rows)
+LOG: pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
-DEALLOCATE p1;
-PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
-/*+BitmapScan(t1)*/
-EXPLAIN (COSTS false) EXECUTE p1 (1);
- QUERY PLAN
----------------------
+ QUERY PLAN
+--------------------
Seq Scan on t1
- Filter: (c1 < $1)
+ Filter: (c1 = 1)
(2 rows)
UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
-/*+BitmapScan(t1)*/
-EXPLAIN (COSTS false) EXECUTE p1 (1);
- QUERY PLAN
----------------------
- Seq Scan on t1
- Filter: (c1 < $1)
-(2 rows)
-
-DEALLOCATE p1;
-----
----- No. A-8-4 EXECUTE statement name error
-----
--- No. A-8-4-1
-EXECUTE p1;
-ERROR: prepared statement "p1" does not exist
-SHOW pg_hint_plan.debug_print;
- pg_hint_plan.debug_print
---------------------------
- on
-(1 row)
-
-----
----- No. A-9-5 EXECUTE statement name error
-----
--- No. A-9-5-1
-SELECT pg_stat_statements_reset();
- pg_stat_statements_reset
---------------------------
-
-(1 row)
-
-SELECT * FROM s1.t1 WHERE t1.c1 = 1;
- c1 | c2 | c3 | c4
-----+----+----+----
- 1 | 1 | 1 | 1
-(1 row)
-
-/*+Set(enable_seqscan off)*/ SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+EXPLAIN (COSTS false) EXECUTE p1;
LOG: pg_hint_plan:
used hint:
-Set(enable_seqscan off)
+SeqScan(t1)
not used hint:
duplication hint:
error hint:
- c1 | c2 | c3 | c4
-----+----+----+----
- 1 | 1 | 1 | 1
-(1 row)
+ QUERY PLAN
+--------------------
+ Seq Scan on t1
+ Filter: (c1 = 1)
+(2 rows)
-/*+SeqScan(t1)*/ SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+DEALLOCATE p1;
+/*+BitmapScan(t1)*/
+PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
LOG: pg_hint_plan:
used hint:
-SeqScan(t1)
+BitmapScan(t1)
not used hint:
duplication hint:
error hint:
- c1 | c2 | c3 | c4
-----+----+----+----
- 1 | 1 | 1 | 1
-(1 row)
-
-SELECT s.query, s.calls
- FROM public.pg_stat_statements s
- JOIN pg_catalog.pg_database d
- ON (s.dbid = d.oid)
- ORDER BY 1;
- query | calls
--------------------------------------------------------------------+-------
- /*+SeqScan(t1)*/ SELECT * FROM s1.t1 WHERE t1.c1 = 1; | 1
- /*+Set(enable_seqscan off)*/ SELECT * FROM s1.t1 WHERE t1.c1 = 1; | 1
- SELECT * FROM s1.t1 WHERE t1.c1 = 1; | 1
- SELECT pg_stat_statements_reset(); | 1
+ QUERY PLAN
+----------------------------------
+ Bitmap Heap Scan on t1
+ Recheck Cond: (c1 < 1000)
+ -> Bitmap Index Scan on t1_i1
+ Index Cond: (c1 < 1000)
(4 rows)
-----
----- No. A-10-1 duplicate hint
-----
--- No. A-10-1-1
-EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2, s1.t3, s1.t4 WHERE t1.ctid = '(1,1)' AND t1.c1 = t2.c1 AND t2.ctid = '(1,1)' AND t1.c1 = t3.c1 AND t3.ctid = '(1,1)' AND t1.c1 = t4.c1 AND t4.ctid = '(1,1)';
- QUERY PLAN
------------------------------------------------------
- Nested Loop
- Join Filter: (t1.c1 = t4.c1)
- -> Nested Loop
- Join Filter: (t1.c1 = t3.c1)
- -> Nested Loop
- Join Filter: (t1.c1 = t2.c1)
- -> Tid Scan on t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2
- Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t4
- TID Cond: (ctid = '(1,1)'::tid)
-(14 rows)
-
-/*+
-Set(enable_tidscan aaa)
-Set(enable_tidscan on)
-Set(enable_tidscan off)
-SeqScan(t4)
-IndexScan(t4)
-BitmapScan(t4)
-TidScan(t4)
-NestLoop(t4 t3)
-MergeJoin(t4 t3)
-HashJoin(t4 t3)
-Leading(t2 t1 t4 t3)
-Leading(t1 t4 t3 t2)
-Leading(t4 t3 t2 t1)
-*/
-EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2, s1.t3, s1.t4 WHERE t1.ctid = '(1,1)' AND t1.c1 = t2.c1 AND t2.ctid = '(1,1)' AND t1.c1 = t3.c1 AND t3.ctid = '(1,1)' AND t1.c1 = t4.c1 AND t4.ctid = '(1,1)';
-INFO: hint syntax error at or near "SeqScan(t4)
-IndexScan(t4)
-BitmapScan(t4)
-TidScan(t4)
-NestLoop(t4 t3)
-MergeJoin(t4 t3)
-HashJoin(t4 t3)
-Leading(t2 t1 t4 t3)
-Leading(t1 t4 t3 t2)
-Leading(t4 t3 t2 t1)
-"
-DETAIL: Conflict scan method hint.
-INFO: hint syntax error at or near "IndexScan(t4)
-BitmapScan(t4)
-TidScan(t4)
-NestLoop(t4 t3)
-MergeJoin(t4 t3)
-HashJoin(t4 t3)
-Leading(t2 t1 t4 t3)
-Leading(t1 t4 t3 t2)
-Leading(t4 t3 t2 t1)
-"
-DETAIL: Conflict scan method hint.
-INFO: hint syntax error at or near "BitmapScan(t4)
-TidScan(t4)
-NestLoop(t4 t3)
-MergeJoin(t4 t3)
-HashJoin(t4 t3)
-Leading(t2 t1 t4 t3)
-Leading(t1 t4 t3 t2)
-Leading(t4 t3 t2 t1)
-"
-DETAIL: Conflict scan method hint.
-INFO: hint syntax error at or near "NestLoop(t4 t3)
-MergeJoin(t4 t3)
-HashJoin(t4 t3)
-Leading(t2 t1 t4 t3)
-Leading(t1 t4 t3 t2)
-Leading(t4 t3 t2 t1)
-"
-DETAIL: Conflict join method hint.
-INFO: hint syntax error at or near "MergeJoin(t4 t3)
-HashJoin(t4 t3)
-Leading(t2 t1 t4 t3)
-Leading(t1 t4 t3 t2)
-Leading(t4 t3 t2 t1)
-"
-DETAIL: Conflict join method hint.
-INFO: hint syntax error at or near "Leading(t2 t1 t4 t3)
-Leading(t1 t4 t3 t2)
-Leading(t4 t3 t2 t1)
-"
-DETAIL: Conflict leading hint.
-INFO: hint syntax error at or near "Leading(t1 t4 t3 t2)
-Leading(t4 t3 t2 t1)
-"
-DETAIL: Conflict leading hint.
-INFO: hint syntax error at or near "Set(enable_tidscan aaa)
-Set(enable_tidscan on)
-Set(enable_tidscan off)
-SeqScan(t4)
-IndexScan(t4)
-BitmapScan(t4)
-TidScan(t4)
-NestLoop(t4 t3)
-MergeJoin(t4 t3)
-HashJoin(t4 t3)
-Leading(t2 t1 t4 t3)
-Leading(t1 t4 t3 t2)
-Leading(t4 t3 t2 t1)
-"
-DETAIL: Conflict set hint.
-INFO: hint syntax error at or near "Set(enable_tidscan on)
-Set(enable_tidscan off)
-SeqScan(t4)
-IndexScan(t4)
-BitmapScan(t4)
-TidScan(t4)
-NestLoop(t4 t3)
-MergeJoin(t4 t3)
-HashJoin(t4 t3)
-Leading(t2 t1 t4 t3)
-Leading(t1 t4 t3 t2)
-Leading(t4 t3 t2 t1)
-"
-DETAIL: Conflict set hint.
-LOG: pg_hint_plan:
-used hint:
-TidScan(t4)
-HashJoin(t3 t4)
-Leading(t4 t3 t2 t1)
-Set(enable_tidscan off)
-not used hint:
-duplication hint:
-SeqScan(t4)
-IndexScan(t4)
-BitmapScan(t4)
-NestLoop(t3 t4)
-MergeJoin(t3 t4)
-Leading(t2 t1 t4 t3)
-Leading(t1 t4 t3 t2)
-Set(enable_tidscan aaa)
-Set(enable_tidscan on)
-error hint:
-
- QUERY PLAN
------------------------------------------------------------
- Nested Loop
- -> Nested Loop
- Join Filter: (t3.c1 = t2.c1)
- -> Hash Join
- Hash Cond: (t3.c1 = t4.c1)
- -> Seq Scan on t3
- Filter: (ctid = '(1,1)'::tid)
- -> Hash
- -> Tid Scan on t4
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2
- Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using t1_i1 on t1
- Index Cond: (c1 = t2.c1)
- Filter: (ctid = '(1,1)'::tid)
-(15 rows)
-
--- No. A-10-1-2
-EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2, s1.t3, s1.t4 WHERE t1.ctid = '(1,1)' AND t1.c1 = t2.c1 AND t2.ctid = '(1,1)' AND t1.c1 = t3.c1 AND t3.ctid = '(1,1)' AND t1.c1 = t4.c1 AND t4.ctid = '(1,1)';
- QUERY PLAN
------------------------------------------------------
- Nested Loop
- Join Filter: (t1.c1 = t4.c1)
- -> Nested Loop
- Join Filter: (t1.c1 = t3.c1)
- -> Nested Loop
- Join Filter: (t1.c1 = t2.c1)
- -> Tid Scan on t1
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2
- Filter: (ctid = '(1,1)'::tid)
- -> Tid Scan on t3
- TID Cond: (ctid = '(1,1)'::tid)
- -> Tid Scan on t4
- TID Cond: (ctid = '(1,1)'::tid)
-(14 rows)
-
-/*+
-SeqScan(t4)
-Set(enable_tidscan aaa)
-IndexScan(t4)
-NestLoop(t4 t3)
-Leading(t2 t1 t4 t3)
-Set(enable_tidscan on)
-BitmapScan(t4)
-MergeJoin(t4 t3)
-Leading(t1 t4 t3 t2)
-Set(enable_tidscan off)
-TidScan(t4)
-HashJoin(t4 t3)
-Leading(t4 t3 t2 t1)
-*/
-EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2, s1.t3, s1.t4 WHERE t1.ctid = '(1,1)' AND t1.c1 = t2.c1 AND t2.ctid = '(1,1)' AND t1.c1 = t3.c1 AND t3.ctid = '(1,1)' AND t1.c1 = t4.c1 AND t4.ctid = '(1,1)';
-INFO: hint syntax error at or near "SeqScan(t4)
-Set(enable_tidscan aaa)
-IndexScan(t4)
-NestLoop(t4 t3)
-Leading(t2 t1 t4 t3)
-Set(enable_tidscan on)
-BitmapScan(t4)
-MergeJoin(t4 t3)
-Leading(t1 t4 t3 t2)
-Set(enable_tidscan off)
-TidScan(t4)
-HashJoin(t4 t3)
-Leading(t4 t3 t2 t1)
-"
-DETAIL: Conflict scan method hint.
-INFO: hint syntax error at or near "IndexScan(t4)
-NestLoop(t4 t3)
-Leading(t2 t1 t4 t3)
-Set(enable_tidscan on)
-BitmapScan(t4)
-MergeJoin(t4 t3)
-Leading(t1 t4 t3 t2)
-Set(enable_tidscan off)
-TidScan(t4)
-HashJoin(t4 t3)
-Leading(t4 t3 t2 t1)
-"
-DETAIL: Conflict scan method hint.
-INFO: hint syntax error at or near "BitmapScan(t4)
-MergeJoin(t4 t3)
-Leading(t1 t4 t3 t2)
-Set(enable_tidscan off)
-TidScan(t4)
-HashJoin(t4 t3)
-Leading(t4 t3 t2 t1)
-"
-DETAIL: Conflict scan method hint.
-INFO: hint syntax error at or near "NestLoop(t4 t3)
-Leading(t2 t1 t4 t3)
-Set(enable_tidscan on)
-BitmapScan(t4)
-MergeJoin(t4 t3)
-Leading(t1 t4 t3 t2)
-Set(enable_tidscan off)
-TidScan(t4)
-HashJoin(t4 t3)
-Leading(t4 t3 t2 t1)
-"
-DETAIL: Conflict join method hint.
-INFO: hint syntax error at or near "MergeJoin(t4 t3)
-Leading(t1 t4 t3 t2)
-Set(enable_tidscan off)
-TidScan(t4)
-HashJoin(t4 t3)
-Leading(t4 t3 t2 t1)
-"
-DETAIL: Conflict join method hint.
-INFO: hint syntax error at or near "Leading(t2 t1 t4 t3)
-Set(enable_tidscan on)
-BitmapScan(t4)
-MergeJoin(t4 t3)
-Leading(t1 t4 t3 t2)
-Set(enable_tidscan off)
-TidScan(t4)
-HashJoin(t4 t3)
-Leading(t4 t3 t2 t1)
-"
-DETAIL: Conflict leading hint.
-INFO: hint syntax error at or near "Leading(t1 t4 t3 t2)
-Set(enable_tidscan off)
-TidScan(t4)
-HashJoin(t4 t3)
-Leading(t4 t3 t2 t1)
-"
-DETAIL: Conflict leading hint.
-INFO: hint syntax error at or near "Set(enable_tidscan aaa)
-IndexScan(t4)
-NestLoop(t4 t3)
-Leading(t2 t1 t4 t3)
-Set(enable_tidscan on)
-BitmapScan(t4)
-MergeJoin(t4 t3)
-Leading(t1 t4 t3 t2)
-Set(enable_tidscan off)
-TidScan(t4)
-HashJoin(t4 t3)
-Leading(t4 t3 t2 t1)
-"
-DETAIL: Conflict set hint.
-INFO: hint syntax error at or near "Set(enable_tidscan on)
-BitmapScan(t4)
-MergeJoin(t4 t3)
-Leading(t1 t4 t3 t2)
-Set(enable_tidscan off)
-TidScan(t4)
-HashJoin(t4 t3)
-Leading(t4 t3 t2 t1)
-"
-DETAIL: Conflict set hint.
-LOG: pg_hint_plan:
-used hint:
-TidScan(t4)
-HashJoin(t3 t4)
-Leading(t4 t3 t2 t1)
-Set(enable_tidscan off)
-not used hint:
-duplication hint:
-SeqScan(t4)
-IndexScan(t4)
-BitmapScan(t4)
-NestLoop(t3 t4)
-MergeJoin(t3 t4)
-Leading(t2 t1 t4 t3)
-Leading(t1 t4 t3 t2)
-Set(enable_tidscan aaa)
-Set(enable_tidscan on)
-error hint:
-
- QUERY PLAN
------------------------------------------------------------
- Nested Loop
- -> Nested Loop
- Join Filter: (t3.c1 = t2.c1)
- -> Hash Join
- Hash Cond: (t3.c1 = t4.c1)
- -> Seq Scan on t3
- Filter: (ctid = '(1,1)'::tid)
- -> Hash
- -> Tid Scan on t4
- TID Cond: (ctid = '(1,1)'::tid)
- -> Seq Scan on t2
- Filter: (ctid = '(1,1)'::tid)
- -> Index Scan using t1_i1 on t1
- Index Cond: (c1 = t2.c1)
- Filter: (ctid = '(1,1)'::tid)
-(15 rows)
-
-----
----- No. A-10-2 restrict query type
-----
--- No. A-10-2-1
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 FULL OUTER JOIN s1.t2 ON (t1.c1 = t2.c1);
- QUERY PLAN
-------------------------------
- Hash Full Join
- Hash Cond: (t1.c1 = t2.c1)
- -> Seq Scan on t1
- -> Hash
- -> Seq Scan on t2
-(5 rows)
-
-/*+HashJoin(t1 t2)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 FULL OUTER JOIN s1.t2 ON (t1.c1 = t2.c1);
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
LOG: pg_hint_plan:
used hint:
-HashJoin(t1 t2)
+BitmapScan(t1)
not used hint:
duplication hint:
error hint:
- QUERY PLAN
-------------------------------
- Hash Full Join
- Hash Cond: (t1.c1 = t2.c1)
- -> Seq Scan on t1
- -> Hash
- -> Seq Scan on t2
-(5 rows)
+ QUERY PLAN
+----------------------------------
+ Bitmap Heap Scan on t1
+ Recheck Cond: (c1 < 1000)
+ -> Bitmap Index Scan on t1_i1
+ Index Cond: (c1 < 1000)
+(4 rows)
-/*+MergeJoin(t1 t2)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 FULL OUTER JOIN s1.t2 ON (t1.c1 = t2.c1);
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
LOG: pg_hint_plan:
used hint:
-MergeJoin(t1 t2)
+BitmapScan(t1)
not used hint:
duplication hint:
error hint:
- QUERY PLAN
-------------------------------------
- Merge Full Join
- Merge Cond: (t1.c1 = t2.c1)
- -> Index Scan using t1_i1 on t1
- -> Sort
- Sort Key: t2.c1
- -> Seq Scan on t2
-(6 rows)
+ QUERY PLAN
+----------------------------------
+ Bitmap Heap Scan on t1
+ Recheck Cond: (c1 < 1000)
+ -> Bitmap Index Scan on t1_i1
+ Index Cond: (c1 < 1000)
+(4 rows)
-/*+NestLoop(t1 t2)*/
-EXPLAIN (COSTS true) SELECT * FROM s1.t1 FULL OUTER JOIN s1.t2 ON (t1.c1 = t2.c1);
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
LOG: pg_hint_plan:
used hint:
-NestLoop(t1 t2)
+BitmapScan(t1)
not used hint:
duplication hint:
error hint:
- QUERY PLAN
---------------------------------------------------------------------------
- Hash Full Join (cost=10000000003.25..10000000024.00 rows=1000 width=29)
- Hash Cond: (t1.c1 = t2.c1)
- -> Seq Scan on t1 (cost=0.00..16.00 rows=1000 width=15)
- -> Hash (cost=2.00..2.00 rows=100 width=14)
- -> Seq Scan on t2 (cost=0.00..2.00 rows=100 width=14)
-(5 rows)
-
--- No. A-10-2-2
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c3 = 1 AND t1.ctid = '(1,1)';
- QUERY PLAN
------------------------------------
- Tid Scan on t1
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: (c3 = 1)
-(3 rows)
+ QUERY PLAN
+----------------------------------
+ Bitmap Heap Scan on t1
+ Recheck Cond: (c1 < 1000)
+ -> Bitmap Index Scan on t1_i1
+ Index Cond: (c1 < 1000)
+(4 rows)
-/*+IndexScan(t1)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c3 = 1 AND t1.ctid = '(1,1)';
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
LOG: pg_hint_plan:
used hint:
-IndexScan(t1)
+BitmapScan(t1)
not used hint:
duplication hint:
error hint:
- QUERY PLAN
----------------------------------
- Index Scan using t1_i on t1
- Index Cond: (c3 = 1)
- Filter: (ctid = '(1,1)'::tid)
-(3 rows)
+ QUERY PLAN
+----------------------------------
+ Bitmap Heap Scan on t1
+ Recheck Cond: (c1 < 1000)
+ -> Bitmap Index Scan on t1_i1
+ Index Cond: (c1 < 1000)
+(4 rows)
-/*+IndexScan(t1 t1_i)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c3 = 1 AND t1.ctid = '(1,1)';
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
LOG: pg_hint_plan:
used hint:
-IndexScan(t1 t1_i)
+BitmapScan(t1)
not used hint:
duplication hint:
error hint:
- QUERY PLAN
----------------------------------
- Index Scan using t1_i on t1
- Index Cond: (c3 = 1)
- Filter: (ctid = '(1,1)'::tid)
-(3 rows)
+ QUERY PLAN
+----------------------------------
+ Bitmap Heap Scan on t1
+ Recheck Cond: (c1 < $1)
+ -> Bitmap Index Scan on t1_i1
+ Index Cond: (c1 < $1)
+(4 rows)
-/*+IndexScan(t1 t1_i1)*/
-EXPLAIN (COSTS true) SELECT * FROM s1.t1 WHERE t1.c3 = 1 AND t1.ctid = '(1,1)';
+UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
LOG: pg_hint_plan:
used hint:
-IndexScan(t1 t1_i1)
+BitmapScan(t1)
not used hint:
duplication hint:
error hint:
- QUERY PLAN
------------------------------------------------------------------------
- Tid Scan on t1 (cost=10000000000.00..10000000004.02 rows=1 width=15)
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: (c3 = 1)
-(3 rows)
+ QUERY PLAN
+----------------------------------
+ Bitmap Heap Scan on t1
+ Recheck Cond: (c1 < $1)
+ -> Bitmap Index Scan on t1_i1
+ Index Cond: (c1 < $1)
+(4 rows)
--- No. A-10-2-3
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+DEALLOCATE p1;
+-- No. A-10-1-5
+-- No. A-10-1-6
+PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+/*+BitmapScan(t1)*/
+EXPLAIN (COSTS false) EXECUTE p1;
QUERY PLAN
------------------------------
Index Scan using t1_i1 on t1
Index Cond: (c1 = 1)
(2 rows)
-/*+TidScan(t1)*/
-EXPLAIN (COSTS true) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
-LOG: pg_hint_plan:
-used hint:
-TidScan(t1)
-not used hint:
-duplication hint:
-error hint:
+UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
+/*+BitmapScan(t1)*/
+EXPLAIN (COSTS false) EXECUTE p1;
+ QUERY PLAN
+------------------------------
+ Index Scan using t1_i1 on t1
+ Index Cond: (c1 = 1)
+(2 rows)
- QUERY PLAN
------------------------------------------------------------------------
- Seq Scan on t1 (cost=10000000000.00..10000000018.50 rows=1 width=15)
- Filter: (c1 = 1)
+DEALLOCATE p1;
+PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
+/*+BitmapScan(t1)*/
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
+ QUERY PLAN
+-----------------------
+ Seq Scan on t1
+ Filter: (c1 < 1000)
(2 rows)
-/*+TidScan(t1)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
-LOG: pg_hint_plan:
-used hint:
-TidScan(t1)
-not used hint:
-duplication hint:
-error hint:
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
+ QUERY PLAN
+-----------------------
+ Seq Scan on t1
+ Filter: (c1 < 1000)
+(2 rows)
- QUERY PLAN
------------------------------------
- Tid Scan on t1
- TID Cond: (ctid = '(1,1)'::tid)
- Filter: (c1 = 1)
-(3 rows)
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
+ QUERY PLAN
+-----------------------
+ Seq Scan on t1
+ Filter: (c1 < 1000)
+(2 rows)
-----
----- No. A-10-3 VIEW, RULE multi specified
-----
--- No. A-10-3-1
-EXPLAIN (COSTS false) SELECT * FROM s1.v1 v1, s1.v1 v2 WHERE v1.c1 = v2.c1;
- QUERY PLAN
-----------------------------------
- Hash Join
- Hash Cond: (v1t1.c1 = v1t1.c1)
- -> Seq Scan on t1 v1t1
- -> Hash
- -> Seq Scan on t1 v1t1
-(5 rows)
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
+ QUERY PLAN
+-----------------------
+ Seq Scan on t1
+ Filter: (c1 < 1000)
+(2 rows)
-/*+Leading(v1t1 v1t1)HashJoin(v1t1 v1t1)BitmapScan(v1t1)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.v1 v1, s1.v1 v2 WHERE v1.c1 = v2.c1;
-INFO: hint syntax error at or near "HashJoin(v1t1 v1t1)BitmapScan(v1t1)"
-DETAIL: Relation name "v1t1" is ambiguous.
-INFO: hint syntax error at or near "Leading(v1t1 v1t1)HashJoin(v1t1 v1t1)BitmapScan(v1t1)"
-DETAIL: Relation name "v1t1" is ambiguous.
-LOG: pg_hint_plan:
-used hint:
-BitmapScan(v1t1)
-not used hint:
-Leading(v1t1 v1t1)
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
+ QUERY PLAN
+-----------------------
+ Seq Scan on t1
+ Filter: (c1 < 1000)
+(2 rows)
+
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
+ QUERY PLAN
+---------------------
+ Seq Scan on t1
+ Filter: (c1 < $1)
+(2 rows)
+
+UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
+/*+BitmapScan(t1)*/
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
+ QUERY PLAN
+---------------------
+ Seq Scan on t1
+ Filter: (c1 < $1)
+(2 rows)
+
+DEALLOCATE p1;
+-- No. A-10-1-9
+-- No. A-10-1-10
+/*+SeqScan(t1)*/
+PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+/*+BitmapScan(t1)*/
+EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
+LOG: pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
duplication hint:
error hint:
-HashJoin(v1t1 v1t1)
-
- QUERY PLAN
-------------------------------------------
- Nested Loop
- -> Index Scan using t1_i1 on t1 v1t1
- -> Bitmap Heap Scan on t1 v1t1
- Recheck Cond: (c1 = v1t1.c1)
- -> Bitmap Index Scan on t1_i1
- Index Cond: (c1 = v1t1.c1)
-(6 rows)
--- No. A-10-3-2
-EXPLAIN (COSTS false) SELECT * FROM s1.v1 v1, s1.v1_ v2 WHERE v1.c1 = v2.c1;
- QUERY PLAN
------------------------------------
- Hash Join
- Hash Cond: (v1t1.c1 = v1t1_.c1)
- -> Seq Scan on t1 v1t1
- -> Hash
- -> Seq Scan on t1 v1t1_
-(5 rows)
+ QUERY PLAN
+--------------------
+ Seq Scan on t1
+ Filter: (c1 = 1)
+(2 rows)
-/*+Leading(v1t1 v1t1_)NestLoop(v1t1 v1t1_)SeqScan(v1t1)BitmapScan(v1t1_)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.v1 v1, s1.v1_ v2 WHERE v1.c1 = v2.c1;
+UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
+/*+BitmapScan(t1)*/
+EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
LOG: pg_hint_plan:
used hint:
-SeqScan(v1t1)
-BitmapScan(v1t1_)
-NestLoop(v1t1 v1t1_)
-Leading(v1t1 v1t1_)
+SeqScan(t1)
not used hint:
duplication hint:
error hint:
- QUERY PLAN
-------------------------------------------
- Nested Loop
- -> Seq Scan on t1 v1t1
- -> Bitmap Heap Scan on t1 v1t1_
- Recheck Cond: (c1 = v1t1.c1)
- -> Bitmap Index Scan on t1_i1
- Index Cond: (c1 = v1t1.c1)
-(6 rows)
+ QUERY PLAN
+--------------------
+ Seq Scan on t1
+ Filter: (c1 = 1)
+(2 rows)
+
+DEALLOCATE p1;
+/*+BitmapScan(t1)*/
+PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
+/*+SeqScan(t1)*/
+EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
+LOG: pg_hint_plan:
+used hint:
+BitmapScan(t1)
+not used hint:
+duplication hint:
+error hint:
--- No. A-10-3-3
-EXPLAIN (COSTS false) SELECT * FROM s1.r4 t1, s1.r4 t2 WHERE t1.c1 = t2.c1;
QUERY PLAN
----------------------------------
- Hash Join
- Hash Cond: (r4t1.c1 = r4t1.c1)
- -> Seq Scan on t1 r4t1
- -> Hash
- -> Seq Scan on t1 r4t1
-(5 rows)
+ Bitmap Heap Scan on t1
+ Recheck Cond: (c1 < 1000)
+ -> Bitmap Index Scan on t1_i1
+ Index Cond: (c1 < 1000)
+(4 rows)
-/*+Leading(r4t1 r4t1)HashJoin(r4t1 r4t1)BitmapScan(r4t1)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.r4 t1, s1.r4 t2 WHERE t1.c1 = t2.c1;
-INFO: hint syntax error at or near "HashJoin(r4t1 r4t1)BitmapScan(r4t1)"
-DETAIL: Relation name "r4t1" is ambiguous.
-INFO: hint syntax error at or near "Leading(r4t1 r4t1)HashJoin(r4t1 r4t1)BitmapScan(r4t1)"
-DETAIL: Relation name "r4t1" is ambiguous.
+/*+SeqScan(t1)*/
+EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
LOG: pg_hint_plan:
used hint:
-BitmapScan(r4t1)
+BitmapScan(t1)
not used hint:
-Leading(r4t1 r4t1)
duplication hint:
error hint:
-HashJoin(r4t1 r4t1)
- QUERY PLAN
-------------------------------------------
- Nested Loop
- -> Index Scan using t1_i1 on t1 r4t1
- -> Bitmap Heap Scan on t1 r4t1
- Recheck Cond: (c1 = r4t1.c1)
- -> Bitmap Index Scan on t1_i1
- Index Cond: (c1 = r4t1.c1)
-(6 rows)
+ QUERY PLAN
+----------------------------------
+ Bitmap Heap Scan on t1
+ Recheck Cond: (c1 < 1000)
+ -> Bitmap Index Scan on t1_i1
+ Index Cond: (c1 < 1000)
+(4 rows)
+
+/*+SeqScan(t1)*/
+EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
+LOG: pg_hint_plan:
+used hint:
+BitmapScan(t1)
+not used hint:
+duplication hint:
+error hint:
--- No. A-10-3-4
-EXPLAIN (COSTS false) SELECT * FROM s1.r4 t1, s1.r5 t2 WHERE t1.c1 = t2.c1;
QUERY PLAN
----------------------------------
- Hash Join
- Hash Cond: (r4t1.c1 = r5t1.c1)
- -> Seq Scan on t1 r4t1
- -> Hash
- -> Seq Scan on t1 r5t1
-(5 rows)
+ Bitmap Heap Scan on t1
+ Recheck Cond: (c1 < 1000)
+ -> Bitmap Index Scan on t1_i1
+ Index Cond: (c1 < 1000)
+(4 rows)
-/*+Leading(r4t1 r5t1)NestLoop(r4t1 r5t1)SeqScan(r4t1)BitmapScan(r5t1)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.r4 t1, s1.r5 t2 WHERE t1.c1 = t2.c1;
+/*+SeqScan(t1)*/
+EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
LOG: pg_hint_plan:
used hint:
-SeqScan(r4t1)
-BitmapScan(r5t1)
-NestLoop(r4t1 r5t1)
-Leading(r4t1 r5t1)
+BitmapScan(t1)
not used hint:
duplication hint:
error hint:
- QUERY PLAN
-------------------------------------------
- Nested Loop
- -> Seq Scan on t1 r4t1
- -> Bitmap Heap Scan on t1 r5t1
- Recheck Cond: (c1 = r4t1.c1)
- -> Bitmap Index Scan on t1_i1
- Index Cond: (c1 = r4t1.c1)
-(6 rows)
+ QUERY PLAN
+----------------------------------
+ Bitmap Heap Scan on t1
+ Recheck Cond: (c1 < 1000)
+ -> Bitmap Index Scan on t1_i1
+ Index Cond: (c1 < 1000)
+(4 rows)
-----
----- No. A-12-1 reset of global variable of core at the error
----- No. A-12-2 reset of global variable of original at the error
-----
-EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
- QUERY PLAN
-------------------------------------
- Merge Join
- Merge Cond: (t1.c1 = t2.c1)
- -> Index Scan using t1_i1 on t1
- -> Sort
- Sort Key: t2.c1
- -> Seq Scan on t2
-(6 rows)
+/*+SeqScan(t1)*/
+EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
+LOG: pg_hint_plan:
+used hint:
+BitmapScan(t1)
+not used hint:
+duplication hint:
+error hint:
-/*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)NestLoop(t1 t1)*/
-PREPARE p1 AS SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
-INFO: hint syntax error at or near "NestLoop(t1 t1)"
-DETAIL: Relation name "t1" is duplicate.
+ QUERY PLAN
+----------------------------------
+ Bitmap Heap Scan on t1
+ Recheck Cond: (c1 < 1000)
+ -> Bitmap Index Scan on t1_i1
+ Index Cond: (c1 < 1000)
+(4 rows)
+
+/*+SeqScan(t1)*/
+EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
LOG: pg_hint_plan:
used hint:
-SeqScan(t1)
-MergeJoin(t1 t2)
-Set(enable_seqscan off)
-Set(geqo_threshold 100)
+BitmapScan(t1)
not used hint:
duplication hint:
error hint:
-NestLoop(t1 t1)
-EXPLAIN (COSTS false) EXECUTE p1;
- QUERY PLAN
-------------------------------------
- Merge Join
- Merge Cond: (t1.c1 = t2.c1)
- -> Sort
- Sort Key: t1.c1
- -> Seq Scan on t1
- -> Index Scan using t2_i1 on t2
-(6 rows)
+ QUERY PLAN
+----------------------------------
+ Bitmap Heap Scan on t1
+ Recheck Cond: (c1 < $1)
+ -> Bitmap Index Scan on t1_i1
+ Index Cond: (c1 < $1)
+(4 rows)
--- No. A-12-1-1
--- No. A-12-2-1
-SELECT name, setting FROM settings;
- name | setting
----------------------------+-----------
- geqo | on
- geqo_effort | 5
- geqo_generations | 0
- geqo_pool_size | 0
- geqo_seed | 0
- geqo_selection_bias | 2
- geqo_threshold | 12
- constraint_exclusion | partition
- cursor_tuple_fraction | 0.1
- default_statistics_target | 100
- from_collapse_limit | 8
- join_collapse_limit | 8
- cpu_index_tuple_cost | 0.005
- cpu_operator_cost | 0.0025
- cpu_tuple_cost | 0.01
- effective_cache_size | 16384
- random_page_cost | 4
- seq_page_cost | 1
- enable_bitmapscan | on
- enable_hashagg | on
- enable_hashjoin | on
- enable_indexscan | on
- enable_material | on
- enable_mergejoin | on
- enable_nestloop | on
- enable_seqscan | on
- enable_sort | on
- enable_tidscan | on
- client_min_messages | log
-(29 rows)
+UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
+/*+SeqScan(t1)*/
+EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
+LOG: pg_hint_plan:
+used hint:
+BitmapScan(t1)
+not used hint:
+duplication hint:
+error hint:
-SET pg_hint_plan.parse_messages TO error;
-/*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)NestLoop(t1 t1)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
-ERROR: hint syntax error at or near "NestLoop(t1 t1)"
-DETAIL: Relation name "t1" is duplicate.
-SELECT name, setting FROM settings;
- name | setting
----------------------------+-----------
- geqo | on
- geqo_effort | 5
- geqo_generations | 0
- geqo_pool_size | 0
- geqo_seed | 0
- geqo_selection_bias | 2
- geqo_threshold | 12
- constraint_exclusion | partition
- cursor_tuple_fraction | 0.1
- default_statistics_target | 100
- from_collapse_limit | 8
- join_collapse_limit | 8
- cpu_index_tuple_cost | 0.005
- cpu_operator_cost | 0.0025
- cpu_tuple_cost | 0.01
- effective_cache_size | 16384
- random_page_cost | 4
- seq_page_cost | 1
- enable_bitmapscan | on
- enable_hashagg | on
- enable_hashjoin | on
- enable_indexscan | on
- enable_material | on
- enable_mergejoin | on
- enable_nestloop | on
- enable_seqscan | on
- enable_sort | on
- enable_tidscan | on
- client_min_messages | log
-(29 rows)
+ QUERY PLAN
+----------------------------------
+ Bitmap Heap Scan on t1
+ Recheck Cond: (c1 < $1)
+ -> Bitmap Index Scan on t1_i1
+ Index Cond: (c1 < $1)
+(4 rows)
-/*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+DEALLOCATE p1;
+-- No. A-10-1-11
+-- No. A-10-1-12
+/*+SeqScan(t1)*/
+PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
LOG: pg_hint_plan:
used hint:
SeqScan(t1)
-MergeJoin(t1 t2)
-Set(enable_seqscan off)
-Set(geqo_threshold 100)
not used hint:
duplication hint:
error hint:
- QUERY PLAN
-------------------------------------
- Merge Join
- Merge Cond: (t1.c1 = t2.c1)
- -> Sort
- Sort Key: t1.c1
- -> Seq Scan on t1
- -> Index Scan using t2_i1 on t2
-(6 rows)
+ QUERY PLAN
+--------------------
+ Seq Scan on t1
+ Filter: (c1 = 1)
+(2 rows)
--- No. A-12-1-2
--- No. A-12-2-2
-SELECT name, setting FROM settings;
- name | setting
----------------------------+-----------
- geqo | on
- geqo_effort | 5
- geqo_generations | 0
- geqo_pool_size | 0
- geqo_seed | 0
- geqo_selection_bias | 2
- geqo_threshold | 12
- constraint_exclusion | partition
- cursor_tuple_fraction | 0.1
- default_statistics_target | 100
- from_collapse_limit | 8
- join_collapse_limit | 8
- cpu_index_tuple_cost | 0.005
- cpu_operator_cost | 0.0025
- cpu_tuple_cost | 0.01
- effective_cache_size | 16384
- random_page_cost | 4
- seq_page_cost | 1
- enable_bitmapscan | on
- enable_hashagg | on
- enable_hashjoin | on
- enable_indexscan | on
- enable_material | on
- enable_mergejoin | on
- enable_nestloop | on
- enable_seqscan | on
- enable_sort | on
- enable_tidscan | on
- client_min_messages | log
-(29 rows)
+UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
+EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
+LOG: pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
-SET pg_hint_plan.parse_messages TO error;
-/*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)NestLoop(t1 t1)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
-ERROR: hint syntax error at or near "NestLoop(t1 t1)"
-DETAIL: Relation name "t1" is duplicate.
-SELECT name, setting FROM settings;
- name | setting
----------------------------+-----------
- geqo | on
- geqo_effort | 5
- geqo_generations | 0
- geqo_pool_size | 0
- geqo_seed | 0
- geqo_selection_bias | 2
- geqo_threshold | 12
- constraint_exclusion | partition
- cursor_tuple_fraction | 0.1
- default_statistics_target | 100
- from_collapse_limit | 8
- join_collapse_limit | 8
- cpu_index_tuple_cost | 0.005
- cpu_operator_cost | 0.0025
- cpu_tuple_cost | 0.01
- effective_cache_size | 16384
- random_page_cost | 4
- seq_page_cost | 1
- enable_bitmapscan | on
- enable_hashagg | on
- enable_hashjoin | on
- enable_indexscan | on
- enable_material | on
- enable_mergejoin | on
- enable_nestloop | on
- enable_seqscan | on
- enable_sort | on
- enable_tidscan | on
- client_min_messages | log
-(29 rows)
+ QUERY PLAN
+--------------------
+ Seq Scan on t1
+ Filter: (c1 = 1)
+(2 rows)
-EXPLAIN (COSTS false) EXECUTE p1;
- QUERY PLAN
-------------------------------------
- Merge Join
- Merge Cond: (t1.c1 = t2.c1)
- -> Sort
- Sort Key: t1.c1
- -> Seq Scan on t1
- -> Index Scan using t2_i1 on t2
-(6 rows)
+DEALLOCATE p1;
+/*+BitmapScan(t1)*/
+PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
+EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
+LOG: pg_hint_plan:
+used hint:
+BitmapScan(t1)
+not used hint:
+duplication hint:
+error hint:
--- No. A-12-1-3
--- No. A-12-2-3
-SELECT name, setting FROM settings;
- name | setting
----------------------------+-----------
- geqo | on
- geqo_effort | 5
- geqo_generations | 0
- geqo_pool_size | 0
- geqo_seed | 0
- geqo_selection_bias | 2
- geqo_threshold | 12
- constraint_exclusion | partition
- cursor_tuple_fraction | 0.1
- default_statistics_target | 100
- from_collapse_limit | 8
- join_collapse_limit | 8
- cpu_index_tuple_cost | 0.005
- cpu_operator_cost | 0.0025
- cpu_tuple_cost | 0.01
- effective_cache_size | 16384
- random_page_cost | 4
- seq_page_cost | 1
- enable_bitmapscan | on
- enable_hashagg | on
- enable_hashjoin | on
- enable_indexscan | on
- enable_material | on
- enable_mergejoin | on
- enable_nestloop | on
- enable_seqscan | on
- enable_sort | on
- enable_tidscan | on
- client_min_messages | log
-(29 rows)
+ QUERY PLAN
+----------------------------------
+ Bitmap Heap Scan on t1
+ Recheck Cond: (c1 < 1000)
+ -> Bitmap Index Scan on t1_i1
+ Index Cond: (c1 < 1000)
+(4 rows)
-SET pg_hint_plan.parse_messages TO error;
-EXPLAIN (COSTS false) EXECUTE p2;
-ERROR: prepared statement "p2" does not exist
-/*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
LOG: pg_hint_plan:
used hint:
-SeqScan(t1)
-MergeJoin(t1 t2)
-Set(enable_seqscan off)
-Set(geqo_threshold 100)
+BitmapScan(t1)
not used hint:
duplication hint:
error hint:
- QUERY PLAN
-------------------------------------
+ QUERY PLAN
+----------------------------------
+ Bitmap Heap Scan on t1
+ Recheck Cond: (c1 < 1000)
+ -> Bitmap Index Scan on t1_i1
+ Index Cond: (c1 < 1000)
+(4 rows)
+
+EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
+LOG: pg_hint_plan:
+used hint:
+BitmapScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+----------------------------------
+ Bitmap Heap Scan on t1
+ Recheck Cond: (c1 < 1000)
+ -> Bitmap Index Scan on t1_i1
+ Index Cond: (c1 < 1000)
+(4 rows)
+
+EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
+LOG: pg_hint_plan:
+used hint:
+BitmapScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+----------------------------------
+ Bitmap Heap Scan on t1
+ Recheck Cond: (c1 < 1000)
+ -> Bitmap Index Scan on t1_i1
+ Index Cond: (c1 < 1000)
+(4 rows)
+
+EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
+LOG: pg_hint_plan:
+used hint:
+BitmapScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+----------------------------------
+ Bitmap Heap Scan on t1
+ Recheck Cond: (c1 < 1000)
+ -> Bitmap Index Scan on t1_i1
+ Index Cond: (c1 < 1000)
+(4 rows)
+
+EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
+LOG: pg_hint_plan:
+used hint:
+BitmapScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+----------------------------------
+ Bitmap Heap Scan on t1
+ Recheck Cond: (c1 < $1)
+ -> Bitmap Index Scan on t1_i1
+ Index Cond: (c1 < $1)
+(4 rows)
+
+UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
+EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
+LOG: pg_hint_plan:
+used hint:
+BitmapScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+----------------------------------
+ Bitmap Heap Scan on t1
+ Recheck Cond: (c1 < $1)
+ -> Bitmap Index Scan on t1_i1
+ Index Cond: (c1 < $1)
+(4 rows)
+
+DEALLOCATE p1;
+-- No. A-10-1-13
+-- No. A-10-1-14
+PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+/*+BitmapScan(t1)*/
+EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
+ QUERY PLAN
+------------------------------
+ Index Scan using t1_i1 on t1
+ Index Cond: (c1 = 1)
+(2 rows)
+
+UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
+/*+BitmapScan(t1)*/
+EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
+ QUERY PLAN
+------------------------------
+ Index Scan using t1_i1 on t1
+ Index Cond: (c1 = 1)
+(2 rows)
+
+DEALLOCATE p1;
+PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
+/*+BitmapScan(t1)*/
+EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
+ QUERY PLAN
+-----------------------
+ Seq Scan on t1
+ Filter: (c1 < 1000)
+(2 rows)
+
+/*+BitmapScan(t1)*/
+EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
+ QUERY PLAN
+-----------------------
+ Seq Scan on t1
+ Filter: (c1 < 1000)
+(2 rows)
+
+/*+BitmapScan(t1)*/
+EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
+ QUERY PLAN
+-----------------------
+ Seq Scan on t1
+ Filter: (c1 < 1000)
+(2 rows)
+
+/*+BitmapScan(t1)*/
+EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
+ QUERY PLAN
+-----------------------
+ Seq Scan on t1
+ Filter: (c1 < 1000)
+(2 rows)
+
+/*+BitmapScan(t1)*/
+EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
+ QUERY PLAN
+-----------------------
+ Seq Scan on t1
+ Filter: (c1 < 1000)
+(2 rows)
+
+/*+BitmapScan(t1)*/
+EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
+ QUERY PLAN
+---------------------
+ Seq Scan on t1
+ Filter: (c1 < $1)
+(2 rows)
+
+UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
+/*+BitmapScan(t1)*/
+EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
+ QUERY PLAN
+---------------------
+ Seq Scan on t1
+ Filter: (c1 < $1)
+(2 rows)
+
+DEALLOCATE p1;
+----
+---- No. A-10-4 EXECUTE statement name error
+----
+-- No. A-10-4-1
+EXECUTE p1;
+ERROR: prepared statement "p1" does not exist
+SHOW pg_hint_plan.debug_print;
+ pg_hint_plan.debug_print
+--------------------------
+ on
+(1 row)
+
+----
+---- No. A-11-5 EXECUTE statement name error
+----
+-- No. A-11-5-1
+SELECT pg_stat_statements_reset();
+ pg_stat_statements_reset
+--------------------------
+
+(1 row)
+
+SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+ c1 | c2 | c3 | c4
+----+----+----+----
+ 1 | 1 | 1 | 1
+(1 row)
+
+/*+Set(enable_seqscan off)*/ SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+LOG: pg_hint_plan:
+used hint:
+Set(enable_seqscan off)
+not used hint:
+duplication hint:
+error hint:
+
+ c1 | c2 | c3 | c4
+----+----+----+----
+ 1 | 1 | 1 | 1
+(1 row)
+
+/*+SeqScan(t1)*/ SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+LOG: pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+ c1 | c2 | c3 | c4
+----+----+----+----
+ 1 | 1 | 1 | 1
+(1 row)
+
+SELECT s.query, s.calls
+ FROM public.pg_stat_statements s
+ JOIN pg_catalog.pg_database d
+ ON (s.dbid = d.oid)
+ ORDER BY 1;
+ query | calls
+--------------------------------------+-------
+ SELECT * FROM s1.t1 WHERE t1.c1 = $1 | 3
+ SELECT pg_stat_statements_reset() | 1
+(2 rows)
+
+----
+---- No. A-12-1 reset of global variable of core at the error
+---- No. A-12-2 reset of global variable of original at the error
+----
+EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+ QUERY PLAN
+------------------------------------
+ Merge Join
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Index Scan using t1_i1 on t1
+ -> Sort
+ Sort Key: t2.c1
+ -> Seq Scan on t2
+(6 rows)
+
+/*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)NestLoop(t1 t1)*/
+PREPARE p1 AS SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+EXPLAIN (COSTS false) EXECUTE p1;
+INFO: pg_hint_plan: hint syntax error at or near "NestLoop(t1 t1)"
+DETAIL: Relation name "t1" is duplicated.
+LOG: pg_hint_plan:
+used hint:
+SeqScan(t1)
+MergeJoin(t1 t2)
+Set(enable_seqscan off)
+Set(geqo_threshold 100)
+not used hint:
+duplication hint:
+error hint:
+NestLoop(t1 t1)
+
+ QUERY PLAN
+------------------------------------
+ Merge Join
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Sort
+ Sort Key: t1.c1
+ -> Seq Scan on t1
+ -> Index Scan using t2_i1 on t2
+(6 rows)
+
+-- No. A-12-1-1
+-- No. A-12-2-1
+SELECT name, setting FROM settings;
+ name | setting
+--------------------------------+-----------
+ client_min_messages | log
+ geqo | on
+ geqo_effort | 5
+ geqo_generations | 0
+ geqo_pool_size | 0
+ geqo_seed | 0
+ geqo_selection_bias | 2
+ geqo_threshold | 12
+ constraint_exclusion | partition
+ cursor_tuple_fraction | 0.1
+ default_statistics_target | 100
+ from_collapse_limit | 8
+ jit | on
+ join_collapse_limit | 8
+ plan_cache_mode | auto
+ cpu_index_tuple_cost | 0.005
+ cpu_operator_cost | 0.0025
+ cpu_tuple_cost | 0.01
+ effective_cache_size | 16384
+ jit_above_cost | 100000
+ jit_inline_above_cost | 500000
+ jit_optimize_above_cost | 500000
+ min_parallel_index_scan_size | 64
+ min_parallel_table_scan_size | 1024
+ parallel_setup_cost | 1000
+ parallel_tuple_cost | 0.1
+ random_page_cost | 4
+ seq_page_cost | 1
+ enable_async_append | on
+ enable_bitmapscan | on
+ enable_gathermerge | on
+ enable_hashagg | on
+ enable_hashjoin | on
+ enable_incremental_sort | on
+ enable_indexonlyscan | on
+ enable_indexscan | on
+ enable_material | on
+ enable_memoize | on
+ enable_mergejoin | on
+ enable_nestloop | on
+ enable_parallel_append | on
+ enable_parallel_hash | on
+ enable_partition_pruning | on
+ enable_partitionwise_aggregate | off
+ enable_partitionwise_join | off
+ enable_seqscan | on
+ enable_sort | on
+ enable_tidscan | on
+(48 rows)
+
+SET pg_hint_plan.parse_messages TO error;
+/*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)NestLoop(t1 t1)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+ERROR: pg_hint_plan: hint syntax error at or near "NestLoop(t1 t1)"
+DETAIL: Relation name "t1" is duplicated.
+SELECT name, setting FROM settings;
+ name | setting
+--------------------------------+-----------
+ client_min_messages | log
+ geqo | on
+ geqo_effort | 5
+ geqo_generations | 0
+ geqo_pool_size | 0
+ geqo_seed | 0
+ geqo_selection_bias | 2
+ geqo_threshold | 12
+ constraint_exclusion | partition
+ cursor_tuple_fraction | 0.1
+ default_statistics_target | 100
+ from_collapse_limit | 8
+ jit | on
+ join_collapse_limit | 8
+ plan_cache_mode | auto
+ cpu_index_tuple_cost | 0.005
+ cpu_operator_cost | 0.0025
+ cpu_tuple_cost | 0.01
+ effective_cache_size | 16384
+ jit_above_cost | 100000
+ jit_inline_above_cost | 500000
+ jit_optimize_above_cost | 500000
+ min_parallel_index_scan_size | 64
+ min_parallel_table_scan_size | 1024
+ parallel_setup_cost | 1000
+ parallel_tuple_cost | 0.1
+ random_page_cost | 4
+ seq_page_cost | 1
+ enable_async_append | on
+ enable_bitmapscan | on
+ enable_gathermerge | on
+ enable_hashagg | on
+ enable_hashjoin | on
+ enable_incremental_sort | on
+ enable_indexonlyscan | on
+ enable_indexscan | on
+ enable_material | on
+ enable_memoize | on
+ enable_mergejoin | on
+ enable_nestloop | on
+ enable_parallel_append | on
+ enable_parallel_hash | on
+ enable_partition_pruning | on
+ enable_partitionwise_aggregate | off
+ enable_partitionwise_join | off
+ enable_seqscan | on
+ enable_sort | on
+ enable_tidscan | on
+(48 rows)
+
+/*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+LOG: pg_hint_plan:
+used hint:
+SeqScan(t1)
+MergeJoin(t1 t2)
+Set(enable_seqscan off)
+Set(geqo_threshold 100)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------------
+ Merge Join
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Sort
+ Sort Key: t1.c1
+ -> Seq Scan on t1
+ -> Index Scan using t2_i1 on t2
+(6 rows)
+
+-- No. A-12-1-2
+-- No. A-12-2-2
+SELECT name, setting FROM settings;
+ name | setting
+--------------------------------+-----------
+ client_min_messages | log
+ geqo | on
+ geqo_effort | 5
+ geqo_generations | 0
+ geqo_pool_size | 0
+ geqo_seed | 0
+ geqo_selection_bias | 2
+ geqo_threshold | 12
+ constraint_exclusion | partition
+ cursor_tuple_fraction | 0.1
+ default_statistics_target | 100
+ from_collapse_limit | 8
+ jit | on
+ join_collapse_limit | 8
+ plan_cache_mode | auto
+ cpu_index_tuple_cost | 0.005
+ cpu_operator_cost | 0.0025
+ cpu_tuple_cost | 0.01
+ effective_cache_size | 16384
+ jit_above_cost | 100000
+ jit_inline_above_cost | 500000
+ jit_optimize_above_cost | 500000
+ min_parallel_index_scan_size | 64
+ min_parallel_table_scan_size | 1024
+ parallel_setup_cost | 1000
+ parallel_tuple_cost | 0.1
+ random_page_cost | 4
+ seq_page_cost | 1
+ enable_async_append | on
+ enable_bitmapscan | on
+ enable_gathermerge | on
+ enable_hashagg | on
+ enable_hashjoin | on
+ enable_incremental_sort | on
+ enable_indexonlyscan | on
+ enable_indexscan | on
+ enable_material | on
+ enable_memoize | on
+ enable_mergejoin | on
+ enable_nestloop | on
+ enable_parallel_append | on
+ enable_parallel_hash | on
+ enable_partition_pruning | on
+ enable_partitionwise_aggregate | off
+ enable_partitionwise_join | off
+ enable_seqscan | on
+ enable_sort | on
+ enable_tidscan | on
+(48 rows)
+
+SET pg_hint_plan.parse_messages TO error;
+/*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)NestLoop(t1 t1)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+ERROR: pg_hint_plan: hint syntax error at or near "NestLoop(t1 t1)"
+DETAIL: Relation name "t1" is duplicated.
+SELECT name, setting FROM settings;
+ name | setting
+--------------------------------+-----------
+ client_min_messages | log
+ geqo | on
+ geqo_effort | 5
+ geqo_generations | 0
+ geqo_pool_size | 0
+ geqo_seed | 0
+ geqo_selection_bias | 2
+ geqo_threshold | 12
+ constraint_exclusion | partition
+ cursor_tuple_fraction | 0.1
+ default_statistics_target | 100
+ from_collapse_limit | 8
+ jit | on
+ join_collapse_limit | 8
+ plan_cache_mode | auto
+ cpu_index_tuple_cost | 0.005
+ cpu_operator_cost | 0.0025
+ cpu_tuple_cost | 0.01
+ effective_cache_size | 16384
+ jit_above_cost | 100000
+ jit_inline_above_cost | 500000
+ jit_optimize_above_cost | 500000
+ min_parallel_index_scan_size | 64
+ min_parallel_table_scan_size | 1024
+ parallel_setup_cost | 1000
+ parallel_tuple_cost | 0.1
+ random_page_cost | 4
+ seq_page_cost | 1
+ enable_async_append | on
+ enable_bitmapscan | on
+ enable_gathermerge | on
+ enable_hashagg | on
+ enable_hashjoin | on
+ enable_incremental_sort | on
+ enable_indexonlyscan | on
+ enable_indexscan | on
+ enable_material | on
+ enable_memoize | on
+ enable_mergejoin | on
+ enable_nestloop | on
+ enable_parallel_append | on
+ enable_parallel_hash | on
+ enable_partition_pruning | on
+ enable_partitionwise_aggregate | off
+ enable_partitionwise_join | off
+ enable_seqscan | on
+ enable_sort | on
+ enable_tidscan | on
+(48 rows)
+
+EXPLAIN (COSTS false) EXECUTE p1;
+ QUERY PLAN
+------------------------------------
+ Merge Join
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Sort
+ Sort Key: t1.c1
+ -> Seq Scan on t1
+ -> Index Scan using t2_i1 on t2
+(6 rows)
+
+-- No. A-12-1-3
+-- No. A-12-2-3
+SELECT name, setting FROM settings;
+ name | setting
+--------------------------------+-----------
+ client_min_messages | log
+ geqo | on
+ geqo_effort | 5
+ geqo_generations | 0
+ geqo_pool_size | 0
+ geqo_seed | 0
+ geqo_selection_bias | 2
+ geqo_threshold | 12
+ constraint_exclusion | partition
+ cursor_tuple_fraction | 0.1
+ default_statistics_target | 100
+ from_collapse_limit | 8
+ jit | on
+ join_collapse_limit | 8
+ plan_cache_mode | auto
+ cpu_index_tuple_cost | 0.005
+ cpu_operator_cost | 0.0025
+ cpu_tuple_cost | 0.01
+ effective_cache_size | 16384
+ jit_above_cost | 100000
+ jit_inline_above_cost | 500000
+ jit_optimize_above_cost | 500000
+ min_parallel_index_scan_size | 64
+ min_parallel_table_scan_size | 1024
+ parallel_setup_cost | 1000
+ parallel_tuple_cost | 0.1
+ random_page_cost | 4
+ seq_page_cost | 1
+ enable_async_append | on
+ enable_bitmapscan | on
+ enable_gathermerge | on
+ enable_hashagg | on
+ enable_hashjoin | on
+ enable_incremental_sort | on
+ enable_indexonlyscan | on
+ enable_indexscan | on
+ enable_material | on
+ enable_memoize | on
+ enable_mergejoin | on
+ enable_nestloop | on
+ enable_parallel_append | on
+ enable_parallel_hash | on
+ enable_partition_pruning | on
+ enable_partitionwise_aggregate | off
+ enable_partitionwise_join | off
+ enable_seqscan | on
+ enable_sort | on
+ enable_tidscan | on
+(48 rows)
+
+SET pg_hint_plan.parse_messages TO error;
+EXPLAIN (COSTS false) EXECUTE p2;
+ERROR: prepared statement "p2" does not exist
+/*+Set(enable_seqscan off)Set(geqo_threshold 100)SeqScan(t1)MergeJoin(t1 t2)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+LOG: pg_hint_plan:
+used hint:
+SeqScan(t1)
+MergeJoin(t1 t2)
+Set(enable_seqscan off)
+Set(geqo_threshold 100)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------------
+ Merge Join
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Sort
+ Sort Key: t1.c1
+ -> Seq Scan on t1
+ -> Index Scan using t2_i1 on t2
+(6 rows)
+
+EXPLAIN (COSTS false) EXECUTE p1;
+ QUERY PLAN
+------------------------------------
+ Merge Join
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Sort
+ Sort Key: t1.c1
+ -> Seq Scan on t1
+ -> Index Scan using t2_i1 on t2
+(6 rows)
+
+SELECT name, setting FROM settings;
+ name | setting
+--------------------------------+-----------
+ client_min_messages | log
+ geqo | on
+ geqo_effort | 5
+ geqo_generations | 0
+ geqo_pool_size | 0
+ geqo_seed | 0
+ geqo_selection_bias | 2
+ geqo_threshold | 12
+ constraint_exclusion | partition
+ cursor_tuple_fraction | 0.1
+ default_statistics_target | 100
+ from_collapse_limit | 8
+ jit | on
+ join_collapse_limit | 8
+ plan_cache_mode | auto
+ cpu_index_tuple_cost | 0.005
+ cpu_operator_cost | 0.0025
+ cpu_tuple_cost | 0.01
+ effective_cache_size | 16384
+ jit_above_cost | 100000
+ jit_inline_above_cost | 500000
+ jit_optimize_above_cost | 500000
+ min_parallel_index_scan_size | 64
+ min_parallel_table_scan_size | 1024
+ parallel_setup_cost | 1000
+ parallel_tuple_cost | 0.1
+ random_page_cost | 4
+ seq_page_cost | 1
+ enable_async_append | on
+ enable_bitmapscan | on
+ enable_gathermerge | on
+ enable_hashagg | on
+ enable_hashjoin | on
+ enable_incremental_sort | on
+ enable_indexonlyscan | on
+ enable_indexscan | on
+ enable_material | on
+ enable_memoize | on
+ enable_mergejoin | on
+ enable_nestloop | on
+ enable_parallel_append | on
+ enable_parallel_hash | on
+ enable_partition_pruning | on
+ enable_partitionwise_aggregate | off
+ enable_partitionwise_join | off
+ enable_seqscan | on
+ enable_sort | on
+ enable_tidscan | on
+(48 rows)
+
+-- No. A-12-1-4
+-- No. A-12-2-4
+SELECT name, setting FROM settings;
+ name | setting
+--------------------------------+-----------
+ client_min_messages | log
+ geqo | on
+ geqo_effort | 5
+ geqo_generations | 0
+ geqo_pool_size | 0
+ geqo_seed | 0
+ geqo_selection_bias | 2
+ geqo_threshold | 12
+ constraint_exclusion | partition
+ cursor_tuple_fraction | 0.1
+ default_statistics_target | 100
+ from_collapse_limit | 8
+ jit | on
+ join_collapse_limit | 8
+ plan_cache_mode | auto
+ cpu_index_tuple_cost | 0.005
+ cpu_operator_cost | 0.0025
+ cpu_tuple_cost | 0.01
+ effective_cache_size | 16384
+ jit_above_cost | 100000
+ jit_inline_above_cost | 500000
+ jit_optimize_above_cost | 500000
+ min_parallel_index_scan_size | 64
+ min_parallel_table_scan_size | 1024
+ parallel_setup_cost | 1000
+ parallel_tuple_cost | 0.1
+ random_page_cost | 4
+ seq_page_cost | 1
+ enable_async_append | on
+ enable_bitmapscan | on
+ enable_gathermerge | on
+ enable_hashagg | on
+ enable_hashjoin | on
+ enable_incremental_sort | on
+ enable_indexonlyscan | on
+ enable_indexscan | on
+ enable_material | on
+ enable_memoize | on
+ enable_mergejoin | on
+ enable_nestloop | on
+ enable_parallel_append | on
+ enable_parallel_hash | on
+ enable_partition_pruning | on
+ enable_partitionwise_aggregate | off
+ enable_partitionwise_join | off
+ enable_seqscan | on
+ enable_sort | on
+ enable_tidscan | on
+(48 rows)
+
+SET pg_hint_plan.parse_messages TO error;
+EXPLAIN (COSTS false) EXECUTE p2;
+ERROR: prepared statement "p2" does not exist
+EXPLAIN (COSTS false) EXECUTE p1;
+ QUERY PLAN
+------------------------------------
+ Merge Join
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Sort
+ Sort Key: t1.c1
+ -> Seq Scan on t1
+ -> Index Scan using t2_i1 on t2
+(6 rows)
+
+SELECT name, setting FROM settings;
+ name | setting
+--------------------------------+-----------
+ client_min_messages | log
+ geqo | on
+ geqo_effort | 5
+ geqo_generations | 0
+ geqo_pool_size | 0
+ geqo_seed | 0
+ geqo_selection_bias | 2
+ geqo_threshold | 12
+ constraint_exclusion | partition
+ cursor_tuple_fraction | 0.1
+ default_statistics_target | 100
+ from_collapse_limit | 8
+ jit | on
+ join_collapse_limit | 8
+ plan_cache_mode | auto
+ cpu_index_tuple_cost | 0.005
+ cpu_operator_cost | 0.0025
+ cpu_tuple_cost | 0.01
+ effective_cache_size | 16384
+ jit_above_cost | 100000
+ jit_inline_above_cost | 500000
+ jit_optimize_above_cost | 500000
+ min_parallel_index_scan_size | 64
+ min_parallel_table_scan_size | 1024
+ parallel_setup_cost | 1000
+ parallel_tuple_cost | 0.1
+ random_page_cost | 4
+ seq_page_cost | 1
+ enable_async_append | on
+ enable_bitmapscan | on
+ enable_gathermerge | on
+ enable_hashagg | on
+ enable_hashjoin | on
+ enable_incremental_sort | on
+ enable_indexonlyscan | on
+ enable_indexscan | on
+ enable_material | on
+ enable_memoize | on
+ enable_mergejoin | on
+ enable_nestloop | on
+ enable_parallel_append | on
+ enable_parallel_hash | on
+ enable_partition_pruning | on
+ enable_partitionwise_aggregate | off
+ enable_partitionwise_join | off
+ enable_seqscan | on
+ enable_sort | on
+ enable_tidscan | on
+(48 rows)
+
+DEALLOCATE p1;
+SET pg_hint_plan.parse_messages TO LOG;
+----
+---- No. A-12-3 effective range of the hint
+----
+EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+ QUERY PLAN
+------------------------------------
+ Merge Join
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Index Scan using t1_i1 on t1
+ -> Sort
+ Sort Key: t2.c1
+ -> Seq Scan on t2
+(6 rows)
+
+-- No. A-12-3-1
+SET enable_indexscan TO off;
+SET enable_mergejoin TO off;
+EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+ QUERY PLAN
+------------------------------
+ Hash Join
+ Hash Cond: (t1.c1 = t2.c1)
+ -> Seq Scan on t1
+ -> Hash
+ -> Seq Scan on t2
+(5 rows)
+
+SELECT name, setting FROM settings;
+ name | setting
+--------------------------------+-----------
+ client_min_messages | log
+ geqo | on
+ geqo_effort | 5
+ geqo_generations | 0
+ geqo_pool_size | 0
+ geqo_seed | 0
+ geqo_selection_bias | 2
+ geqo_threshold | 12
+ constraint_exclusion | partition
+ cursor_tuple_fraction | 0.1
+ default_statistics_target | 100
+ from_collapse_limit | 8
+ jit | on
+ join_collapse_limit | 8
+ plan_cache_mode | auto
+ cpu_index_tuple_cost | 0.005
+ cpu_operator_cost | 0.0025
+ cpu_tuple_cost | 0.01
+ effective_cache_size | 16384
+ jit_above_cost | 100000
+ jit_inline_above_cost | 500000
+ jit_optimize_above_cost | 500000
+ min_parallel_index_scan_size | 64
+ min_parallel_table_scan_size | 1024
+ parallel_setup_cost | 1000
+ parallel_tuple_cost | 0.1
+ random_page_cost | 4
+ seq_page_cost | 1
+ enable_async_append | on
+ enable_bitmapscan | on
+ enable_gathermerge | on
+ enable_hashagg | on
+ enable_hashjoin | on
+ enable_incremental_sort | on
+ enable_indexonlyscan | on
+ enable_indexscan | off
+ enable_material | on
+ enable_memoize | on
+ enable_mergejoin | off
+ enable_nestloop | on
+ enable_parallel_append | on
+ enable_parallel_hash | on
+ enable_partition_pruning | on
+ enable_partitionwise_aggregate | off
+ enable_partitionwise_join | off
+ enable_seqscan | on
+ enable_sort | on
+ enable_tidscan | on
+(48 rows)
+
+/*+Set(enable_indexscan on)Set(geqo_threshold 100)IndexScan(t2)MergeJoin(t1 t2)Leading(t2 t1)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+LOG: pg_hint_plan:
+used hint:
+IndexScan(t2)
+MergeJoin(t1 t2)
+Leading(t2 t1)
+Set(enable_indexscan on)
+Set(geqo_threshold 100)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------------
+ Merge Join
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Index Scan using t1_i1 on t1
+ -> Index Scan using t2_i1 on t2
+(4 rows)
+
+SELECT name, setting FROM settings;
+ name | setting
+--------------------------------+-----------
+ client_min_messages | log
+ geqo | on
+ geqo_effort | 5
+ geqo_generations | 0
+ geqo_pool_size | 0
+ geqo_seed | 0
+ geqo_selection_bias | 2
+ geqo_threshold | 12
+ constraint_exclusion | partition
+ cursor_tuple_fraction | 0.1
+ default_statistics_target | 100
+ from_collapse_limit | 8
+ jit | on
+ join_collapse_limit | 8
+ plan_cache_mode | auto
+ cpu_index_tuple_cost | 0.005
+ cpu_operator_cost | 0.0025
+ cpu_tuple_cost | 0.01
+ effective_cache_size | 16384
+ jit_above_cost | 100000
+ jit_inline_above_cost | 500000
+ jit_optimize_above_cost | 500000
+ min_parallel_index_scan_size | 64
+ min_parallel_table_scan_size | 1024
+ parallel_setup_cost | 1000
+ parallel_tuple_cost | 0.1
+ random_page_cost | 4
+ seq_page_cost | 1
+ enable_async_append | on
+ enable_bitmapscan | on
+ enable_gathermerge | on
+ enable_hashagg | on
+ enable_hashjoin | on
+ enable_incremental_sort | on
+ enable_indexonlyscan | on
+ enable_indexscan | off
+ enable_material | on
+ enable_memoize | on
+ enable_mergejoin | off
+ enable_nestloop | on
+ enable_parallel_append | on
+ enable_parallel_hash | on
+ enable_partition_pruning | on
+ enable_partitionwise_aggregate | off
+ enable_partitionwise_join | off
+ enable_seqscan | on
+ enable_sort | on
+ enable_tidscan | on
+(48 rows)
+
+EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+ QUERY PLAN
+------------------------------
+ Hash Join
+ Hash Cond: (t1.c1 = t2.c1)
+ -> Seq Scan on t1
+ -> Hash
+ -> Seq Scan on t2
+(5 rows)
+
+-- No. A-12-3-2
+SET enable_indexscan TO off;
+SET enable_mergejoin TO off;
+EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+ QUERY PLAN
+------------------------------
+ Hash Join
+ Hash Cond: (t1.c1 = t2.c1)
+ -> Seq Scan on t1
+ -> Hash
+ -> Seq Scan on t2
+(5 rows)
+
+SELECT name, setting FROM settings;
+ name | setting
+--------------------------------+-----------
+ client_min_messages | log
+ geqo | on
+ geqo_effort | 5
+ geqo_generations | 0
+ geqo_pool_size | 0
+ geqo_seed | 0
+ geqo_selection_bias | 2
+ geqo_threshold | 12
+ constraint_exclusion | partition
+ cursor_tuple_fraction | 0.1
+ default_statistics_target | 100
+ from_collapse_limit | 8
+ jit | on
+ join_collapse_limit | 8
+ plan_cache_mode | auto
+ cpu_index_tuple_cost | 0.005
+ cpu_operator_cost | 0.0025
+ cpu_tuple_cost | 0.01
+ effective_cache_size | 16384
+ jit_above_cost | 100000
+ jit_inline_above_cost | 500000
+ jit_optimize_above_cost | 500000
+ min_parallel_index_scan_size | 64
+ min_parallel_table_scan_size | 1024
+ parallel_setup_cost | 1000
+ parallel_tuple_cost | 0.1
+ random_page_cost | 4
+ seq_page_cost | 1
+ enable_async_append | on
+ enable_bitmapscan | on
+ enable_gathermerge | on
+ enable_hashagg | on
+ enable_hashjoin | on
+ enable_incremental_sort | on
+ enable_indexonlyscan | on
+ enable_indexscan | off
+ enable_material | on
+ enable_memoize | on
+ enable_mergejoin | off
+ enable_nestloop | on
+ enable_parallel_append | on
+ enable_parallel_hash | on
+ enable_partition_pruning | on
+ enable_partitionwise_aggregate | off
+ enable_partitionwise_join | off
+ enable_seqscan | on
+ enable_sort | on
+ enable_tidscan | on
+(48 rows)
+
+BEGIN;
+/*+Set(enable_indexscan on)Set(geqo_threshold 100)IndexScan(t2)MergeJoin(t1 t2)Leading(t2 t1)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+LOG: pg_hint_plan:
+used hint:
+IndexScan(t2)
+MergeJoin(t1 t2)
+Leading(t2 t1)
+Set(enable_indexscan on)
+Set(geqo_threshold 100)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------------
+ Merge Join
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Index Scan using t1_i1 on t1
+ -> Index Scan using t2_i1 on t2
+(4 rows)
+
+COMMIT;
+BEGIN;
+SELECT name, setting FROM settings;
+ name | setting
+--------------------------------+-----------
+ client_min_messages | log
+ geqo | on
+ geqo_effort | 5
+ geqo_generations | 0
+ geqo_pool_size | 0
+ geqo_seed | 0
+ geqo_selection_bias | 2
+ geqo_threshold | 12
+ constraint_exclusion | partition
+ cursor_tuple_fraction | 0.1
+ default_statistics_target | 100
+ from_collapse_limit | 8
+ jit | on
+ join_collapse_limit | 8
+ plan_cache_mode | auto
+ cpu_index_tuple_cost | 0.005
+ cpu_operator_cost | 0.0025
+ cpu_tuple_cost | 0.01
+ effective_cache_size | 16384
+ jit_above_cost | 100000
+ jit_inline_above_cost | 500000
+ jit_optimize_above_cost | 500000
+ min_parallel_index_scan_size | 64
+ min_parallel_table_scan_size | 1024
+ parallel_setup_cost | 1000
+ parallel_tuple_cost | 0.1
+ random_page_cost | 4
+ seq_page_cost | 1
+ enable_async_append | on
+ enable_bitmapscan | on
+ enable_gathermerge | on
+ enable_hashagg | on
+ enable_hashjoin | on
+ enable_incremental_sort | on
+ enable_indexonlyscan | on
+ enable_indexscan | off
+ enable_material | on
+ enable_memoize | on
+ enable_mergejoin | off
+ enable_nestloop | on
+ enable_parallel_append | on
+ enable_parallel_hash | on
+ enable_partition_pruning | on
+ enable_partitionwise_aggregate | off
+ enable_partitionwise_join | off
+ enable_seqscan | on
+ enable_sort | on
+ enable_tidscan | on
+(48 rows)
+
+EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+ QUERY PLAN
+------------------------------
+ Hash Join
+ Hash Cond: (t1.c1 = t2.c1)
+ -> Seq Scan on t1
+ -> Hash
+ -> Seq Scan on t2
+(5 rows)
+
+COMMIT;
+-- No. A-12-3-3
+SET enable_indexscan TO off;
+SET enable_mergejoin TO off;
+EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+ QUERY PLAN
+------------------------------
+ Hash Join
+ Hash Cond: (t1.c1 = t2.c1)
+ -> Seq Scan on t1
+ -> Hash
+ -> Seq Scan on t2
+(5 rows)
+
+SELECT name, setting FROM settings;
+ name | setting
+--------------------------------+-----------
+ client_min_messages | log
+ geqo | on
+ geqo_effort | 5
+ geqo_generations | 0
+ geqo_pool_size | 0
+ geqo_seed | 0
+ geqo_selection_bias | 2
+ geqo_threshold | 12
+ constraint_exclusion | partition
+ cursor_tuple_fraction | 0.1
+ default_statistics_target | 100
+ from_collapse_limit | 8
+ jit | on
+ join_collapse_limit | 8
+ plan_cache_mode | auto
+ cpu_index_tuple_cost | 0.005
+ cpu_operator_cost | 0.0025
+ cpu_tuple_cost | 0.01
+ effective_cache_size | 16384
+ jit_above_cost | 100000
+ jit_inline_above_cost | 500000
+ jit_optimize_above_cost | 500000
+ min_parallel_index_scan_size | 64
+ min_parallel_table_scan_size | 1024
+ parallel_setup_cost | 1000
+ parallel_tuple_cost | 0.1
+ random_page_cost | 4
+ seq_page_cost | 1
+ enable_async_append | on
+ enable_bitmapscan | on
+ enable_gathermerge | on
+ enable_hashagg | on
+ enable_hashjoin | on
+ enable_incremental_sort | on
+ enable_indexonlyscan | on
+ enable_indexscan | off
+ enable_material | on
+ enable_memoize | on
+ enable_mergejoin | off
+ enable_nestloop | on
+ enable_parallel_append | on
+ enable_parallel_hash | on
+ enable_partition_pruning | on
+ enable_partitionwise_aggregate | off
+ enable_partitionwise_join | off
+ enable_seqscan | on
+ enable_sort | on
+ enable_tidscan | on
+(48 rows)
+
+/*+Set(enable_indexscan on)Set(geqo_threshold 100)IndexScan(t2)MergeJoin(t1 t2)Leading(t2 t1)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+LOG: pg_hint_plan:
+used hint:
+IndexScan(t2)
+MergeJoin(t1 t2)
+Leading(t2 t1)
+Set(enable_indexscan on)
+Set(geqo_threshold 100)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+------------------------------------
+ Merge Join
+ Merge Cond: (t1.c1 = t2.c1)
+ -> Index Scan using t1_i1 on t1
+ -> Index Scan using t2_i1 on t2
+(4 rows)
+
+\connect
+SET enable_indexscan TO off;
+SET enable_mergejoin TO off;
+LOAD 'pg_hint_plan';
+SELECT name, setting FROM settings;
+ name | setting
+--------------------------------+-----------
+ client_min_messages | notice
+ geqo | on
+ geqo_effort | 5
+ geqo_generations | 0
+ geqo_pool_size | 0
+ geqo_seed | 0
+ geqo_selection_bias | 2
+ geqo_threshold | 12
+ constraint_exclusion | partition
+ cursor_tuple_fraction | 0.1
+ default_statistics_target | 100
+ from_collapse_limit | 8
+ jit | on
+ join_collapse_limit | 8
+ plan_cache_mode | auto
+ cpu_index_tuple_cost | 0.005
+ cpu_operator_cost | 0.0025
+ cpu_tuple_cost | 0.01
+ effective_cache_size | 16384
+ jit_above_cost | 100000
+ jit_inline_above_cost | 500000
+ jit_optimize_above_cost | 500000
+ min_parallel_index_scan_size | 64
+ min_parallel_table_scan_size | 1024
+ parallel_setup_cost | 1000
+ parallel_tuple_cost | 0.1
+ random_page_cost | 4
+ seq_page_cost | 1
+ enable_async_append | on
+ enable_bitmapscan | on
+ enable_gathermerge | on
+ enable_hashagg | on
+ enable_hashjoin | on
+ enable_incremental_sort | on
+ enable_indexonlyscan | on
+ enable_indexscan | off
+ enable_material | on
+ enable_memoize | on
+ enable_mergejoin | off
+ enable_nestloop | on
+ enable_parallel_append | on
+ enable_parallel_hash | on
+ enable_partition_pruning | on
+ enable_partitionwise_aggregate | off
+ enable_partitionwise_join | off
+ enable_seqscan | on
+ enable_sort | on
+ enable_tidscan | on
+(48 rows)
+
+EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+ QUERY PLAN
+------------------------------
+ Hash Join
+ Hash Cond: (t1.c1 = t2.c1)
+ -> Seq Scan on t1
+ -> Hash
+ -> Seq Scan on t2
+(5 rows)
+
+SET pg_hint_plan.enable_hint TO on;
+SET pg_hint_plan.debug_print TO on;
+SET client_min_messages TO LOG;
+SET search_path TO public;
+RESET enable_indexscan;
+RESET enable_mergejoin;
+----
+---- No. A-13 call planner recursively
+----
+CREATE OR REPLACE FUNCTION nested_planner(cnt int) RETURNS int AS $$
+DECLARE
+ new_cnt int;
+BEGIN
+ RAISE NOTICE 'nested_planner(%)', cnt;
+
+ /* 再帰終了の判断 */
+ IF cnt <= 1 THEN
+ RETURN 0;
+ END IF;
+
+ SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1) INTO new_cnt
+ FROM s1.t1 t_1
+ JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
+ ORDER BY t_1.c1 LIMIT 1;
+
+ RETURN new_cnt;
+END;
+$$ LANGUAGE plpgsql IMMUTABLE;
+----
+---- No. A-13-2 use hint of main query
+----
+--No.13-2-1
+EXPLAIN (COSTS false) SELECT nested_planner(1) FROM s1.t1 t_1 ORDER BY t_1.c1;
+NOTICE: nested_planner(1)
+ QUERY PLAN
+---------------------------------------
+ Index Only Scan using t1_i1 on t1 t_1
+(1 row)
+
+/*+SeqScan(t_1)*/
+EXPLAIN (COSTS false) SELECT nested_planner(1) FROM s1.t1 t_1 ORDER BY t_1.c1;
+NOTICE: nested_planner(1)
+LOG: pg_hint_plan:
+used hint:
+SeqScan(t_1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------------
+ Sort
+ Sort Key: c1
+ -> Seq Scan on t1 t_1
+(3 rows)
+
+----
+---- No. A-13-3 output number of times of debugging log
+----
+--No.13-3-1
+EXPLAIN (COSTS false) SELECT nested_planner(1) FROM s1.t1 t_1 ORDER BY t_1.c1;
+NOTICE: nested_planner(1)
+ QUERY PLAN
+---------------------------------------
+ Index Only Scan using t1_i1 on t1 t_1
+(1 row)
+
+/*+SeqScan(t_2)*/
+EXPLAIN (COSTS false) SELECT nested_planner(1) FROM s1.t1 t_1 ORDER BY t_1.c1;
+NOTICE: nested_planner(1)
+LOG: pg_hint_plan:
+used hint:
+not used hint:
+SeqScan(t_2)
+duplication hint:
+error hint:
+
+ QUERY PLAN
+---------------------------------------
+ Index Only Scan using t1_i1 on t1 t_1
+(1 row)
+
+--No.13-3-2
+EXPLAIN (COSTS false) SELECT nested_planner(2) FROM s1.t1 t_1 ORDER BY t_1.c1;
+NOTICE: nested_planner(2)
+NOTICE: nested_planner(1)
+LOG: pg_hint_plan:
+used hint:
+IndexScan(t_1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+---------------------------------------
+ Index Only Scan using t1_i1 on t1 t_1
+(1 row)
+
+/*+SeqScan(t_2)*/
+EXPLAIN (COSTS false) SELECT nested_planner(2) FROM s1.t1 t_1 ORDER BY t_1.c1;
+NOTICE: nested_planner(2)
+NOTICE: nested_planner(1)
+LOG: pg_hint_plan:
+used hint:
+IndexScan(t_1)
+not used hint:
+duplication hint:
+error hint:
+
+LOG: pg_hint_plan:
+used hint:
+not used hint:
+SeqScan(t_2)
+duplication hint:
+error hint:
+
+ QUERY PLAN
+---------------------------------------
+ Index Only Scan using t1_i1 on t1 t_1
+(1 row)
+
+--No.13-3-3
+--
+-- Redefine not to use cached plan
+--
+CREATE OR REPLACE FUNCTION nested_planner(cnt int) RETURNS int AS $$
+DECLARE
+ new_cnt int;
+BEGIN
+ RAISE NOTICE 'nested_planner(%)', cnt;
+
+ /* 再帰終了の判断 */
+ IF cnt <= 1 THEN
+ RETURN 0;
+ END IF;
+
+ SELECT /*+ IndexScan(t_1) */ nested_planner(cnt - 1) INTO new_cnt
+ FROM s1.t1 t_1
+ JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
+ ORDER BY t_1.c1 LIMIT 1;
+
+ RETURN new_cnt;
+END;
+$$ LANGUAGE plpgsql IMMUTABLE;
+-- The function called at the bottom desn't use a hint, the immediate
+-- caller level should restore its own hint. So, the first LOG from
+-- pg_hint_plan should use the IndexScan(t_1) hint
+EXPLAIN (COSTS false) SELECT nested_planner(5) FROM s1.t1 t_1 ORDER BY t_1.c1;
+NOTICE: nested_planner(5)
+NOTICE: nested_planner(4)
+NOTICE: nested_planner(3)
+NOTICE: nested_planner(2)
+NOTICE: nested_planner(1)
+LOG: pg_hint_plan:
+used hint:
+IndexScan(t_1)
+not used hint:
+duplication hint:
+error hint:
+
+LOG: pg_hint_plan:
+used hint:
+IndexScan(t_1)
+not used hint:
+duplication hint:
+error hint:
+
+LOG: pg_hint_plan:
+used hint:
+IndexScan(t_1)
+not used hint:
+duplication hint:
+error hint:
+
+LOG: pg_hint_plan:
+used hint:
+IndexScan(t_1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+---------------------------------------
+ Index Only Scan using t1_i1 on t1 t_1
+(1 row)
+
+-- The top level uses SeqScan(t_1), but the function should use only
+-- the hint in the function.
+/*+SeqScan(t_1) SeqScan(t_2)*/
+EXPLAIN (COSTS false) SELECT nested_planner(5) FROM s1.t1 t_1 ORDER BY t_1.c1;
+NOTICE: nested_planner(5)
+NOTICE: nested_planner(4)
+NOTICE: nested_planner(3)
+NOTICE: nested_planner(2)
+NOTICE: nested_planner(1)
+LOG: pg_hint_plan:
+used hint:
+IndexScan(t_1)
+not used hint:
+duplication hint:
+error hint:
+
+LOG: pg_hint_plan:
+used hint:
+IndexScan(t_1)
+not used hint:
+duplication hint:
+error hint:
+
+LOG: pg_hint_plan:
+used hint:
+IndexScan(t_1)
+not used hint:
+duplication hint:
+error hint:
+
+LOG: pg_hint_plan:
+used hint:
+IndexScan(t_1)
+not used hint:
+duplication hint:
+error hint:
+
+LOG: pg_hint_plan:
+used hint:
+SeqScan(t_1)
+not used hint:
+SeqScan(t_2)
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------------
+ Sort
+ Sort Key: c1
+ -> Seq Scan on t1 t_1
+(3 rows)
+
+----
+---- No. A-13-4 output of debugging log on hint status
+----
+CREATE OR REPLACE FUNCTION recall_planner() RETURNS int AS $$
+ SELECT /*+ IndexScan(t_1) */t_1.c1
+ FROM s1.t1 t_1
+ JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
+ ORDER BY t_1.c1 LIMIT 1;
+$$ LANGUAGE SQL IMMUTABLE;
+--No.13-4-1
+-- recall_planner() is reduced to constant while planning using the
+-- hint defined in the function. Then the outer query is planned based
+-- on the following hint. pg_hint_plan shows the log for the function
+-- but the resulting explain output doesn't contain the corresponding
+-- plan.
+/*+HashJoin(t_1 t_2)*/
+EXPLAIN (COSTS false)
+ SELECT recall_planner() FROM s1.t1 t_1
+ JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
+ ORDER BY t_1.c1;
+LOG: pg_hint_plan:
+used hint:
+IndexScan(t_1)
+not used hint:
+duplication hint:
+error hint:
+
+LOG: pg_hint_plan:
+used hint:
+HashJoin(t_1 t_2)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+--------------------------------------
+ Sort
+ Sort Key: t_1.c1
+ -> Hash Join
+ Hash Cond: (t_1.c1 = t_2.c1)
+ -> Seq Scan on t1 t_1
+ -> Hash
+ -> Seq Scan on t2 t_2
+(7 rows)
+
+--No.13-4-2
+--See description for No.13-4-1
+/*+HashJoin(st_1 st_2)*/
+EXPLAIN (COSTS false)
+ SELECT recall_planner() FROM s1.t1 st_1
+ JOIN s1.t2 st_2 ON (st_1.c1 = st_2.c1)
+ ORDER BY st_1.c1;
+LOG: pg_hint_plan:
+used hint:
+IndexScan(t_1)
+not used hint:
+duplication hint:
+error hint:
+
+LOG: pg_hint_plan:
+used hint:
+HashJoin(st_1 st_2)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+----------------------------------------
+ Sort
+ Sort Key: st_1.c1
+ -> Hash Join
+ Hash Cond: (st_1.c1 = st_2.c1)
+ -> Seq Scan on t1 st_1
+ -> Hash
+ -> Seq Scan on t2 st_2
+(7 rows)
+
+--No.13-4-3
+--See description for No.13-4-1
+/*+HashJoin(t_1 t_2)*/
+EXPLAIN (COSTS false)
+ SELECT recall_planner() FROM s1.t1 st_1
+ JOIN s1.t2 st_2 ON (st_1.c1 = st_2.c1)
+ ORDER BY st_1.c1;
+LOG: pg_hint_plan:
+used hint:
+IndexScan(t_1)
+not used hint:
+duplication hint:
+error hint:
+
+LOG: pg_hint_plan:
+used hint:
+not used hint:
+HashJoin(t_1 t_2)
+duplication hint:
+error hint:
+
+ QUERY PLAN
+----------------------------------------------
Merge Join
- Merge Cond: (t1.c1 = t2.c1)
+ Merge Cond: (st_1.c1 = st_2.c1)
+ -> Index Only Scan using t1_i1 on t1 st_1
-> Sort
- Sort Key: t1.c1
- -> Seq Scan on t1
- -> Index Scan using t2_i1 on t2
+ Sort Key: st_2.c1
+ -> Seq Scan on t2 st_2
(6 rows)
-EXPLAIN (COSTS false) EXECUTE p1;
- QUERY PLAN
-------------------------------------
+--No.13-4-4
+--See description for No.13-4-1
+/*+HashJoin(st_1 st_2)*/
+EXPLAIN (COSTS false)
+ SELECT recall_planner() FROM s1.t1 t_1
+ JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
+ ORDER BY t_1.c1;
+LOG: pg_hint_plan:
+used hint:
+IndexScan(t_1)
+not used hint:
+duplication hint:
+error hint:
+
+LOG: pg_hint_plan:
+used hint:
+not used hint:
+HashJoin(st_1 st_2)
+duplication hint:
+error hint:
+
+ QUERY PLAN
+---------------------------------------------
Merge Join
- Merge Cond: (t1.c1 = t2.c1)
+ Merge Cond: (t_1.c1 = t_2.c1)
+ -> Index Only Scan using t1_i1 on t1 t_1
-> Sort
- Sort Key: t1.c1
- -> Seq Scan on t1
- -> Index Scan using t2_i1 on t2
+ Sort Key: t_2.c1
+ -> Seq Scan on t2 t_2
(6 rows)
-SELECT name, setting FROM settings;
- name | setting
----------------------------+-----------
- geqo | on
- geqo_effort | 5
- geqo_generations | 0
- geqo_pool_size | 0
- geqo_seed | 0
- geqo_selection_bias | 2
- geqo_threshold | 12
- constraint_exclusion | partition
- cursor_tuple_fraction | 0.1
- default_statistics_target | 100
- from_collapse_limit | 8
- join_collapse_limit | 8
- cpu_index_tuple_cost | 0.005
- cpu_operator_cost | 0.0025
- cpu_tuple_cost | 0.01
- effective_cache_size | 16384
- random_page_cost | 4
- seq_page_cost | 1
- enable_bitmapscan | on
- enable_hashagg | on
- enable_hashjoin | on
- enable_indexscan | on
- enable_material | on
- enable_mergejoin | on
- enable_nestloop | on
- enable_seqscan | on
- enable_sort | on
- enable_tidscan | on
- client_min_messages | log
-(29 rows)
+--No.13-4-5
+-- See description for No.13-4-1. No joins in ths plan, so
+-- pg_hint_plan doesn't complain on the wrongly written error hint.
+/*+HashJoin(t_1 t_1)*/
+EXPLAIN (COSTS false)
+ SELECT recall_planner() FROM s1.t1 t_1
+ ORDER BY t_1.c1;
+LOG: pg_hint_plan:
+used hint:
+IndexScan(t_1)
+not used hint:
+duplication hint:
+error hint:
--- No. A-12-1-4
--- No. A-12-2-4
-SELECT name, setting FROM settings;
- name | setting
----------------------------+-----------
- geqo | on
- geqo_effort | 5
- geqo_generations | 0
- geqo_pool_size | 0
- geqo_seed | 0
- geqo_selection_bias | 2
- geqo_threshold | 12
- constraint_exclusion | partition
- cursor_tuple_fraction | 0.1
- default_statistics_target | 100
- from_collapse_limit | 8
- join_collapse_limit | 8
- cpu_index_tuple_cost | 0.005
- cpu_operator_cost | 0.0025
- cpu_tuple_cost | 0.01
- effective_cache_size | 16384
- random_page_cost | 4
- seq_page_cost | 1
- enable_bitmapscan | on
- enable_hashagg | on
- enable_hashjoin | on
- enable_indexscan | on
- enable_material | on
- enable_mergejoin | on
- enable_nestloop | on
- enable_seqscan | on
- enable_sort | on
- enable_tidscan | on
- client_min_messages | log
-(29 rows)
+LOG: pg_hint_plan:
+used hint:
+not used hint:
+HashJoin(t_1 t_1)
+duplication hint:
+error hint:
-SET pg_hint_plan.parse_messages TO error;
-EXPLAIN (COSTS false) EXECUTE p2;
-ERROR: prepared statement "p2" does not exist
-EXPLAIN (COSTS false) EXECUTE p1;
- QUERY PLAN
-------------------------------------
+ QUERY PLAN
+---------------------------------------
+ Index Only Scan using t1_i1 on t1 t_1
+(1 row)
+
+--No.13-4-6
+CREATE OR REPLACE FUNCTION recall_planner_one_t() RETURNS int AS $$
+ SELECT /*+ IndexScan(t_1) */t_1.c1
+ FROM s1.t1 t_1
+ ORDER BY t_1.c1 LIMIT 1;
+$$ LANGUAGE SQL IMMUTABLE;
+EXPLAIN (COSTS false)
+ SELECT recall_planner_one_t() FROM s1.t1 t_1
+ JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
+ ORDER BY t_1.c1;
+LOG: pg_hint_plan:
+used hint:
+IndexScan(t_1)
+not used hint:
+duplication hint:
+error hint:
+
+ QUERY PLAN
+---------------------------------------------
Merge Join
- Merge Cond: (t1.c1 = t2.c1)
+ Merge Cond: (t_1.c1 = t_2.c1)
+ -> Index Only Scan using t1_i1 on t1 t_1
-> Sort
- Sort Key: t1.c1
- -> Seq Scan on t1
- -> Index Scan using t2_i1 on t2
+ Sort Key: t_2.c1
+ -> Seq Scan on t2 t_2
(6 rows)
-SELECT name, setting FROM settings;
- name | setting
----------------------------+-----------
- geqo | on
- geqo_effort | 5
- geqo_generations | 0
- geqo_pool_size | 0
- geqo_seed | 0
- geqo_selection_bias | 2
- geqo_threshold | 12
- constraint_exclusion | partition
- cursor_tuple_fraction | 0.1
- default_statistics_target | 100
- from_collapse_limit | 8
- join_collapse_limit | 8
- cpu_index_tuple_cost | 0.005
- cpu_operator_cost | 0.0025
- cpu_tuple_cost | 0.01
- effective_cache_size | 16384
- random_page_cost | 4
- seq_page_cost | 1
- enable_bitmapscan | on
- enable_hashagg | on
- enable_hashjoin | on
- enable_indexscan | on
- enable_material | on
- enable_mergejoin | on
- enable_nestloop | on
- enable_seqscan | on
- enable_sort | on
- enable_tidscan | on
- client_min_messages | log
-(29 rows)
+/*+HashJoin(t_1 t_1)*/
+EXPLAIN (COSTS false)
+ SELECT recall_planner_one_t() FROM s1.t1 t_1
+ JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
+ ORDER BY t_1.c1;
+LOG: pg_hint_plan:
+used hint:
+IndexScan(t_1)
+not used hint:
+duplication hint:
+error hint:
-DEALLOCATE p1;
-SET pg_hint_plan.parse_messages TO LOG;
-----
----- No. A-12-3 effective range of the hint
-----
-EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
- QUERY PLAN
-------------------------------------
+INFO: pg_hint_plan: hint syntax error at or near "HashJoin(t_1 t_1)"
+DETAIL: Relation name "t_1" is duplicated.
+LOG: pg_hint_plan:
+used hint:
+not used hint:
+duplication hint:
+error hint:
+HashJoin(t_1 t_1)
+
+ QUERY PLAN
+---------------------------------------------
Merge Join
- Merge Cond: (t1.c1 = t2.c1)
- -> Index Scan using t1_i1 on t1
+ Merge Cond: (t_1.c1 = t_2.c1)
+ -> Index Only Scan using t1_i1 on t1 t_1
-> Sort
- Sort Key: t2.c1
- -> Seq Scan on t2
+ Sort Key: t_2.c1
+ -> Seq Scan on t2 t_2
(6 rows)
--- No. A-12-3-1
-SET enable_indexscan TO off;
-SET enable_mergejoin TO off;
-EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
- QUERY PLAN
-------------------------------
- Hash Join
- Hash Cond: (t1.c1 = t2.c1)
- -> Seq Scan on t1
- -> Hash
- -> Seq Scan on t2
-(5 rows)
-
-SELECT name, setting FROM settings;
- name | setting
----------------------------+-----------
- geqo | on
- geqo_effort | 5
- geqo_generations | 0
- geqo_pool_size | 0
- geqo_seed | 0
- geqo_selection_bias | 2
- geqo_threshold | 12
- constraint_exclusion | partition
- cursor_tuple_fraction | 0.1
- default_statistics_target | 100
- from_collapse_limit | 8
- join_collapse_limit | 8
- cpu_index_tuple_cost | 0.005
- cpu_operator_cost | 0.0025
- cpu_tuple_cost | 0.01
- effective_cache_size | 16384
- random_page_cost | 4
- seq_page_cost | 1
- enable_bitmapscan | on
- enable_hashagg | on
- enable_hashjoin | on
- enable_indexscan | off
- enable_material | on
- enable_mergejoin | off
- enable_nestloop | on
- enable_seqscan | on
- enable_sort | on
- enable_tidscan | on
- client_min_messages | log
-(29 rows)
+DROP FUNCTION recall_planner_one_t(int);
+ERROR: function recall_planner_one_t(integer) does not exist
+--No.13-4-7
+-- See description for No.13-4-1. Complains on the wrongly written hint.
+/*+HashJoin(t_1 t_1)*/
+EXPLAIN (COSTS false)
+ SELECT recall_planner() FROM s1.t1 t_1
+ JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
+ ORDER BY t_1.c1;
+LOG: pg_hint_plan:
+used hint:
+IndexScan(t_1)
+not used hint:
+duplication hint:
+error hint:
-/*+Set(enable_indexscan on)Set(geqo_threshold 100)IndexScan(t2)MergeJoin(t1 t2)Leading(t2 t1)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+INFO: pg_hint_plan: hint syntax error at or near "HashJoin(t_1 t_1)"
+DETAIL: Relation name "t_1" is duplicated.
LOG: pg_hint_plan:
used hint:
-IndexScan(t2)
-MergeJoin(t1 t2)
-Leading(t2 t1)
-Set(enable_indexscan on)
-Set(geqo_threshold 100)
not used hint:
duplication hint:
error hint:
+HashJoin(t_1 t_1)
- QUERY PLAN
-------------------------------------
+ QUERY PLAN
+---------------------------------------------
Merge Join
- Merge Cond: (t1.c1 = t2.c1)
- -> Index Scan using t1_i1 on t1
- -> Index Scan using t2_i1 on t2
-(4 rows)
-
-SELECT name, setting FROM settings;
- name | setting
----------------------------+-----------
- geqo | on
- geqo_effort | 5
- geqo_generations | 0
- geqo_pool_size | 0
- geqo_seed | 0
- geqo_selection_bias | 2
- geqo_threshold | 12
- constraint_exclusion | partition
- cursor_tuple_fraction | 0.1
- default_statistics_target | 100
- from_collapse_limit | 8
- join_collapse_limit | 8
- cpu_index_tuple_cost | 0.005
- cpu_operator_cost | 0.0025
- cpu_tuple_cost | 0.01
- effective_cache_size | 16384
- random_page_cost | 4
- seq_page_cost | 1
- enable_bitmapscan | on
- enable_hashagg | on
- enable_hashjoin | on
- enable_indexscan | off
- enable_material | on
- enable_mergejoin | off
- enable_nestloop | on
- enable_seqscan | on
- enable_sort | on
- enable_tidscan | on
- client_min_messages | log
-(29 rows)
+ Merge Cond: (t_1.c1 = t_2.c1)
+ -> Index Only Scan using t1_i1 on t1 t_1
+ -> Sort
+ Sort Key: t_2.c1
+ -> Seq Scan on t2 t_2
+(6 rows)
-EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
- QUERY PLAN
-------------------------------
- Hash Join
- Hash Cond: (t1.c1 = t2.c1)
- -> Seq Scan on t1
- -> Hash
- -> Seq Scan on t2
-(5 rows)
+--No.13-4-8
+/*+MergeJoin(t_1 t_2)HashJoin(t_1 t_2)*/
+EXPLAIN (COSTS false)
+ SELECT recall_planner() FROM s1.t1 t_1
+ JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)
+ ORDER BY t_1.c1;
+INFO: pg_hint_plan: hint syntax error at or near "MergeJoin(t_1 t_2)HashJoin(t_1 t_2)"
+DETAIL: Conflict join method hint.
+LOG: pg_hint_plan:
+used hint:
+IndexScan(t_1)
+not used hint:
+duplication hint:
+error hint:
--- No. A-12-3-2
-SET enable_indexscan TO off;
-SET enable_mergejoin TO off;
-EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
- QUERY PLAN
-------------------------------
- Hash Join
- Hash Cond: (t1.c1 = t2.c1)
- -> Seq Scan on t1
- -> Hash
- -> Seq Scan on t2
-(5 rows)
+LOG: pg_hint_plan:
+used hint:
+HashJoin(t_1 t_2)
+not used hint:
+duplication hint:
+MergeJoin(t_1 t_2)
+error hint:
-SELECT name, setting FROM settings;
- name | setting
----------------------------+-----------
- geqo | on
- geqo_effort | 5
- geqo_generations | 0
- geqo_pool_size | 0
- geqo_seed | 0
- geqo_selection_bias | 2
- geqo_threshold | 12
- constraint_exclusion | partition
- cursor_tuple_fraction | 0.1
- default_statistics_target | 100
- from_collapse_limit | 8
- join_collapse_limit | 8
- cpu_index_tuple_cost | 0.005
- cpu_operator_cost | 0.0025
- cpu_tuple_cost | 0.01
- effective_cache_size | 16384
- random_page_cost | 4
- seq_page_cost | 1
- enable_bitmapscan | on
- enable_hashagg | on
- enable_hashjoin | on
- enable_indexscan | off
- enable_material | on
- enable_mergejoin | off
- enable_nestloop | on
- enable_seqscan | on
- enable_sort | on
- enable_tidscan | on
- client_min_messages | log
-(29 rows)
+ QUERY PLAN
+--------------------------------------
+ Sort
+ Sort Key: t_1.c1
+ -> Hash Join
+ Hash Cond: (t_1.c1 = t_2.c1)
+ -> Seq Scan on t1 t_1
+ -> Hash
+ -> Seq Scan on t2 t_2
+(7 rows)
+
+--No.14-1-1 plancache invalidation
+CREATE TABLE s1.tpc AS SELECT a FROM generate_series(0, 999) a;
+CREATE INDEX ON s1.tpc(a);
+PREPARE p1 AS SELECT * FROM s1.tpc WHERE a < 999;
+/*+ IndexScan(tpc) */PREPARE p2 AS SELECT * FROM s1.tpc WHERE a < 999;
+/*+ SeqScan(tpc) */PREPARE p3(int) AS SELECT * FROM s1.tpc WHERE a = $1;
+EXPLAIN EXECUTE p1;
+ QUERY PLAN
+------------------------------------------------------
+ Seq Scan on tpc (cost=0.00..17.50 rows=333 width=4)
+ Filter: (a < 999)
+(2 rows)
-BEGIN;
-/*+Set(enable_indexscan on)Set(geqo_threshold 100)IndexScan(t2)MergeJoin(t1 t2)Leading(t2 t1)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+EXPLAIN EXECUTE p2;
LOG: pg_hint_plan:
used hint:
-IndexScan(t2)
-MergeJoin(t1 t2)
-Leading(t2 t1)
-Set(enable_indexscan on)
-Set(geqo_threshold 100)
+IndexScan(tpc)
not used hint:
duplication hint:
error hint:
- QUERY PLAN
-------------------------------------
- Merge Join
- Merge Cond: (t1.c1 = t2.c1)
- -> Index Scan using t1_i1 on t1
- -> Index Scan using t2_i1 on t2
-(4 rows)
-
-COMMIT;
-BEGIN;
-SELECT name, setting FROM settings;
- name | setting
----------------------------+-----------
- geqo | on
- geqo_effort | 5
- geqo_generations | 0
- geqo_pool_size | 0
- geqo_seed | 0
- geqo_selection_bias | 2
- geqo_threshold | 12
- constraint_exclusion | partition
- cursor_tuple_fraction | 0.1
- default_statistics_target | 100
- from_collapse_limit | 8
- join_collapse_limit | 8
- cpu_index_tuple_cost | 0.005
- cpu_operator_cost | 0.0025
- cpu_tuple_cost | 0.01
- effective_cache_size | 16384
- random_page_cost | 4
- seq_page_cost | 1
- enable_bitmapscan | on
- enable_hashagg | on
- enable_hashjoin | on
- enable_indexscan | off
- enable_material | on
- enable_mergejoin | off
- enable_nestloop | on
- enable_seqscan | on
- enable_sort | on
- enable_tidscan | on
- client_min_messages | log
-(29 rows)
+ QUERY PLAN
+------------------------------------------------------------------------
+ Index Scan using tpc_a_idx on tpc (cost=0.28..34.10 rows=333 width=4)
+ Index Cond: (a < 999)
+(2 rows)
-EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
- QUERY PLAN
-------------------------------
- Hash Join
- Hash Cond: (t1.c1 = t2.c1)
- -> Seq Scan on t1
- -> Hash
- -> Seq Scan on t2
-(5 rows)
+EXPLAIN EXECUTE p3(500);
+LOG: pg_hint_plan:
+used hint:
+SeqScan(tpc)
+not used hint:
+duplication hint:
+error hint:
-COMMIT;
--- No. A-12-3-3
-SET enable_indexscan TO off;
-SET enable_mergejoin TO off;
-EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
- QUERY PLAN
-------------------------------
- Hash Join
- Hash Cond: (t1.c1 = t2.c1)
- -> Seq Scan on t1
- -> Hash
- -> Seq Scan on t2
-(5 rows)
+ QUERY PLAN
+----------------------------------------------------
+ Seq Scan on tpc (cost=0.00..17.50 rows=5 width=4)
+ Filter: (a = 500)
+(2 rows)
-SELECT name, setting FROM settings;
- name | setting
----------------------------+-----------
- geqo | on
- geqo_effort | 5
- geqo_generations | 0
- geqo_pool_size | 0
- geqo_seed | 0
- geqo_selection_bias | 2
- geqo_threshold | 12
- constraint_exclusion | partition
- cursor_tuple_fraction | 0.1
- default_statistics_target | 100
- from_collapse_limit | 8
- join_collapse_limit | 8
- cpu_index_tuple_cost | 0.005
- cpu_operator_cost | 0.0025
- cpu_tuple_cost | 0.01
- effective_cache_size | 16384
- random_page_cost | 4
- seq_page_cost | 1
- enable_bitmapscan | on
- enable_hashagg | on
- enable_hashjoin | on
- enable_indexscan | off
- enable_material | on
- enable_mergejoin | off
- enable_nestloop | on
- enable_seqscan | on
- enable_sort | on
- enable_tidscan | on
- client_min_messages | log
-(29 rows)
+-- The DROP invalidates the plan caches
+DROP TABLE s1.tpc;
+CREATE TABLE s1.tpc AS SELECT a FROM generate_series(0, 999) a;
+CREATE INDEX ON s1.tpc(a);
+EXPLAIN EXECUTE p1;
+ QUERY PLAN
+------------------------------------------------------
+ Seq Scan on tpc (cost=0.00..17.50 rows=333 width=4)
+ Filter: (a < 999)
+(2 rows)
-/*+Set(enable_indexscan on)Set(geqo_threshold 100)IndexScan(t2)MergeJoin(t1 t2)Leading(t2 t1)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
+EXPLAIN EXECUTE p2;
LOG: pg_hint_plan:
used hint:
-IndexScan(t2)
-MergeJoin(t1 t2)
-Leading(t2 t1)
-Set(enable_indexscan on)
-Set(geqo_threshold 100)
+IndexScan(tpc)
not used hint:
duplication hint:
error hint:
- QUERY PLAN
-------------------------------------
- Merge Join
- Merge Cond: (t1.c1 = t2.c1)
- -> Index Scan using t1_i1 on t1
- -> Index Scan using t2_i1 on t2
-(4 rows)
+ QUERY PLAN
+------------------------------------------------------------------------
+ Index Scan using tpc_a_idx on tpc (cost=0.28..34.10 rows=333 width=4)
+ Index Cond: (a < 999)
+(2 rows)
-\connect
-LOAD 'pg_hint_plan';
-SELECT name, setting FROM settings;
- name | setting
----------------------------+-----------
- geqo | on
- geqo_effort | 5
- geqo_generations | 0
- geqo_pool_size | 0
- geqo_seed | 0
- geqo_selection_bias | 2
- geqo_threshold | 12
- constraint_exclusion | partition
- cursor_tuple_fraction | 0.1
- default_statistics_target | 100
- from_collapse_limit | 8
- join_collapse_limit | 8
- cpu_index_tuple_cost | 0.005
- cpu_operator_cost | 0.0025
- cpu_tuple_cost | 0.01
- effective_cache_size | 16384
- random_page_cost | 4
- seq_page_cost | 1
- enable_bitmapscan | on
- enable_hashagg | on
- enable_hashjoin | on
- enable_indexscan | on
- enable_material | on
- enable_mergejoin | on
- enable_nestloop | on
- enable_seqscan | on
- enable_sort | on
- enable_tidscan | on
- client_min_messages | notice
-(29 rows)
+EXPLAIN EXECUTE p3(500);
+LOG: pg_hint_plan:
+used hint:
+SeqScan(tpc)
+not used hint:
+duplication hint:
+error hint:
-EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
- QUERY PLAN
-------------------------------------
- Merge Join
- Merge Cond: (t1.c1 = t2.c1)
- -> Index Scan using t1_i1 on t1
- -> Sort
- Sort Key: t2.c1
- -> Seq Scan on t2
-(6 rows)
+ QUERY PLAN
+----------------------------------------------------
+ Seq Scan on tpc (cost=0.00..17.50 rows=5 width=4)
+ Filter: (a = 500)
+(2 rows)
-SET pg_hint_plan.enable TO on;
-SET pg_hint_plan.debug_print TO on;
-SET client_min_messages TO LOG;
-SET search_path TO public;
+DEALLOCATE p1;
+DEALLOCATE p2;
+DEALLOCATE p3;
+DROP TABLE s1.tpc;
OSDN Git Service
