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;
----- TODO 後で適切な位置に移動する
----
----- No. J-3-4 hint state output
+---- No.A-1-1 install
+---- No.A-2-1 uninstall
----
--- No. J-3-4-7
-/*+Set(enable_indexscan off)NestLoop("")Set(enable_bitmapscan off)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+-- 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;
+
+CREATE EXTENSION pg_hint_plan;
+DROP SCHEMA other_schema;
----
----- No. A-5-1 hint format
+---- No. A-5-1 comment pattern
----
-- No. A-5-1-1
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
-- No. A-5-1-3
---+SeqScan(t1)
+/*SeqScan(t1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
-- No. A-5-1-4
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
-- No. A-5-1-5
--- +SeqScan(t1)
+/* /*+SeqScan(t1)*/ */
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
--- No. A-5-1-6
---SeqScan(t1)
+----
+---- No. A-5-2 hint position
+----
+
+-- No. A-5-2-1
+EXPLAIN (COSTS false) SELECT c1 FROM s1.t1 WHERE t1.c1 = 1;
+/*+SeqScan(t1)*/
+EXPLAIN (COSTS false) SELECT c1 FROM s1.t1 WHERE t1.c1 = 1;
+
+-- No. A-5-2-2
+EXPLAIN (COSTS false) SELECT c1, c2 AS c_2 /*+SeqScan(t1)*/ FROM s1.t1 WHERE t1.c1 = 1;
+
+-- No. A-5-2-3
+EXPLAIN (COSTS false) SELECT c1 AS "c1"/*+SeqScan(t1)*/ 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;
+
+----
+---- 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
+
+----
+---- No. A-6-2 search condition
+----
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+-- 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;
--- No. A-5-1-7
-/*+SeqScan(t1) /* nest comment */ */
+-- 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;
+TRUNCATE hint_plan.hints;
--- No. A-5-1-8
-/* +SeqScan(t1) /* nest comment */ */
+-- 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;
+TRUNCATE hint_plan.hints;
--- No. A-5-1-9
-/*SeqScan(t1) /* nest comment */ */
+-- 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;
+TRUNCATE hint_plan.hints;
----
----- No. A-5-2 hint position
+---- No. A-6-3 number of constant
----
--- No. A-5-2-1
-/*+SeqScan(t1)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+-- 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;
+TRUNCATE hint_plan.hints;
--- No. A-5-2-2
-/* normal comment */
-/*+SeqScan(t1)*/
+-- 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;
+TRUNCATE hint_plan.hints;
--- No. A-5-2-3
-EXPLAIN (COSTS false) SELECT /*+SeqScan(t1)*/ * FROM s1.t1 WHERE t1.c1 = 1;
+-- 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;
+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;
--- 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;
--- 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;
--- 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;
--- 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;
--- 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;
--- 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;
--- 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;
--- 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;
--- 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;
--- 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;
--- 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;
--- 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;
--- 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;
--- 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;
--- 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;
--- 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;
--- 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;
--- No. A-5-4-24
+-- No. A-7-2-24
/*+
Set
(enable_indexscan"off")
(enable_bitmapscan"off")*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
--- No. A-5-4-25
+-- No. A-7-2-25
/*+Set
(
enable_indexscan"off")Set
enable_bitmapscan"off")*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
--- No. A-5-4-26
+-- No. A-7-2-26
/*+Set(enable_indexscan"off"
)
Set(enable_bitmapscan"off"
*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
--- No. A-5-4-27
+-- No. A-7-2-27
/*+Set(
enable_indexscan
"off"
)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
--- No. A-5-4-28
+-- No. A-7-2-28
/*+
Set
(
*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
--- 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;
--- 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;
--- No. A-5-4-31
+-- No. A-7-2-31
/*+
Set
(enable_indexscan"off")
(enable_bitmapscan"off")*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
--- No. A-5-4-32
+-- No. A-7-2-32
/*+Set
(
enable_indexscan"off")Set
enable_bitmapscan"off")*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
--- No. A-5-4-33
+-- No. A-7-2-33
/*+Set(enable_indexscan"off"
)
Set(enable_bitmapscan"off"
*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
--- No. A-5-4-34
+-- No. A-7-2-34
/*+Set(
enable_indexscan
"off"
)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
--- No. A-5-4-35
+-- No. A-7-2-35
/*+
Set
(
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
----
----- 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;
/*+SeqScan(ttt)*/
/*+SeqScan("ttt")*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 ttt WHERE ttt.c1 = 1;
--- 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;
/*+SeqScan(TTT)*/
/*+SeqScan("TTT")*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 "TTT" WHERE "TTT".c1 = 1;
--- 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;
-/*+SeqScan(((()*/
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 "(((" WHERE "(((".c1 = 1;
/*+SeqScan("(")*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 "(" WHERE "(".c1 = 1;
-/*+SeqScan("(((")*/
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 "(((" WHERE "(((".c1 = 1;
--- No. A-5-5-4
--- No. A-7-2-4
+-- No. A-7-3-4
+-- No. A-9-2-4
/*+SeqScan())*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 ")" WHERE ")".c1 = 1;
/*+SeqScan(")")*/
/*+SeqScan(")))")*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 ")))" WHERE ")))".c1 = 1;
--- 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;
/*+SeqScan("""")*/
/*+SeqScan("""""""")*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 """""""" WHERE """""""".c1 = 1;
--- 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;
/*+SeqScan(" ")*/
/*+SeqScan(" ")*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 " " WHERE " ".c1 = 1;
--- 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;
/*+SeqScan(" ")*/
/*+SeqScan(" ")*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 " " WHERE " ".c1 = 1;
--- 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 "
".c1 = 1;
--- 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;
/*+SeqScan("Set")*/
/*+SeqScan("Set SeqScan Leading")*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 "Set SeqScan Leading" WHERE "Set SeqScan Leading".c1 = 1;
--- 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;
/*+SeqScan(あいう)*/
/*+SeqScan("あいう")*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 あいう WHERE あいう.c1 = 1;
--- 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;
/*+SeqScan(/**//**//**/)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 "/**//**//**/" WHERE "/**//**//**/".c1 = 1;
--- 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;
+-- No. A-7-3-13
+-- No. A-9-2-13
+/*+SeqScan(a123456789b123456789c123456789d123456789e123456789f123)*/
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 "123456789012345678901234567890123456789012345678901234" WHERE "123456789012345678901234567890123456789012345678901234".c1 = 1;
+
----
----- 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;
--- 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;
--- 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;
--- 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;
--- 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;
--- 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;
--- 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;
----
----- No. A-6-1 original GUC parameter
+---- No. A-8-1 original GUC parameter
----
-
--- No. A-6-1-1
-SET ROLE super_user;
-SET pg_hint_plan.debug_print TO off;
-SHOW pg_hint_plan.enable;
-SHOW pg_hint_plan.debug_print;
-SHOW pg_hint_plan.parse_messages;
-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;
-SHOW pg_hint_plan.debug_print;
-SHOW pg_hint_plan.parse_messages;
-RESET pg_hint_plan.enable;
-RESET pg_hint_plan.debug_print;
-RESET pg_hint_plan.parse_messages;
-SHOW pg_hint_plan.enable;
-SHOW pg_hint_plan.debug_print;
-SHOW pg_hint_plan.parse_messages;
-
--- No. A-6-1-2
-SET ROLE normal_user;
-SHOW pg_hint_plan.enable;
-SHOW pg_hint_plan.debug_print;
-SHOW pg_hint_plan.parse_messages;
-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;
-SHOW pg_hint_plan.debug_print;
-SHOW pg_hint_plan.parse_messages;
-RESET pg_hint_plan.enable;
-RESET pg_hint_plan.debug_print;
-RESET pg_hint_plan.parse_messages;
-SHOW pg_hint_plan.enable;
-SHOW pg_hint_plan.debug_print;
-SHOW pg_hint_plan.parse_messages;
-
-RESET ROLE;
+---- 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-6-2 original GUC parameter pg_hint_plan.enable
+---- No. A-8-2 original GUC parameter pg_hint_plan.enable_hint
----
--- No. A-6-2-1
-SET pg_hint_plan.enable TO on;
-SHOW pg_hint_plan.enable;
+-- No. A-8-2-1
+SET pg_hint_plan.debug_print TO off;
+SET pg_hint_plan.enable_hint TO on;
+SHOW pg_hint_plan.enable_hint;
/*+Set(enable_indexscan off)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
--- No. A-6-2-2
-SET pg_hint_plan.enable TO off;
-SHOW pg_hint_plan.enable;
+-- No. A-8-2-2
+SET pg_hint_plan.enable_hint TO off;
+SHOW pg_hint_plan.enable_hint;
/*+Set(enable_indexscan off)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
--- No. A-6-2-3
-SET pg_hint_plan.enable TO DEFAULT;
-SHOW pg_hint_plan.enable;
-/*+Set(enable_indexscan off)*/
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+-- 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-6-2-4
-SET pg_hint_plan.enable TO enable;
-SHOW pg_hint_plan.enable;
+-- 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-6-3 original GUC parameter pg_hint_plan.debug_print
+---- No. A-8-3 original GUC parameter pg_hint_plan.debug_print
----
--- No. A-6-3-1
+-- No. A-8-3-1
+SET pg_hint_plan.enable_hint TO on;
+SHOW pg_hint_plan.enable_hint;
SET pg_hint_plan.debug_print TO on;
SHOW pg_hint_plan.debug_print;
/*+Set(enable_indexscan off)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
--- No. A-6-3-2
+-- No. A-8-3-2
SET pg_hint_plan.debug_print TO off;
SHOW pg_hint_plan.debug_print;
/*+Set(enable_indexscan off)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
--- No. A-6-3-3
+-- No. A-8-3-3
SET pg_hint_plan.debug_print TO DEFAULT;
SHOW pg_hint_plan.debug_print;
/*+Set(enable_indexscan off)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
--- No. A-6-3-4
+-- No. A-8-3-4
SET pg_hint_plan.debug_print TO enable;
SHOW pg_hint_plan.debug_print;
----
----- 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;
--- No. A-6-4-1
+-- No. A-8-4-1
SET pg_hint_plan.parse_messages TO debug5;
SHOW pg_hint_plan.parse_messages;
/*+Set*/SELECT 1;
SET client_min_messages TO debug4;
/*+Set*/SELECT 1;
--- No. A-6-4-2
+-- No. A-8-4-2
SET pg_hint_plan.parse_messages TO debug4;
SHOW pg_hint_plan.parse_messages;
/*+Set*/SELECT 1;
SET client_min_messages TO debug3;
/*+Set*/SELECT 1;
--- No. A-6-4-3
+-- No. A-8-4-3
SET pg_hint_plan.parse_messages TO debug3;
SHOW pg_hint_plan.parse_messages;
/*+Set*/SELECT 1;
SET client_min_messages TO debug2;
/*+Set*/SELECT 1;
--- No. A-6-4-4
+-- No. A-8-4-4
SET pg_hint_plan.parse_messages TO debug2;
SHOW pg_hint_plan.parse_messages;
/*+Set*/SELECT 1;
SET client_min_messages TO debug1;
/*+Set*/SELECT 1;
--- No. A-6-4-5
+-- No. A-8-4-5
SET pg_hint_plan.parse_messages TO debug1;
SHOW pg_hint_plan.parse_messages;
/*+Set*/SELECT 1;
SET client_min_messages TO log;
/*+Set*/SELECT 1;
--- No. A-6-4-6
+-- No. A-8-4-6
SET pg_hint_plan.parse_messages TO log;
SHOW pg_hint_plan.parse_messages;
/*+Set*/SELECT 1;
SET client_min_messages TO info;
/*+Set*/SELECT 1;
--- No. A-6-4-7
+-- No. A-8-4-7
SET pg_hint_plan.parse_messages TO info;
SHOW pg_hint_plan.parse_messages;
/*+Set*/SELECT 1;
SET client_min_messages TO notice;
/*+Set*/SELECT 1;
--- No. A-6-4-8
+-- No. A-8-4-8
SET pg_hint_plan.parse_messages TO notice;
SHOW pg_hint_plan.parse_messages;
/*+Set*/SELECT 1;
SET client_min_messages TO warning;
/*+Set*/SELECT 1;
--- No. A-6-4-9
+-- No. A-8-4-9
SET pg_hint_plan.parse_messages TO warning;
SHOW pg_hint_plan.parse_messages;
/*+Set*/SELECT 1;
SET client_min_messages TO error;
/*+Set*/SELECT 1;
--- No. A-6-4-10
+-- No. A-8-4-10
SET pg_hint_plan.parse_messages TO error;
SHOW pg_hint_plan.parse_messages;
/*+Set*/SELECT 1;
SET client_min_messages TO fatal;
/*+Set*/SELECT 1;
--- No. A-6-4-11
+-- No. A-8-4-11
RESET client_min_messages;
SET pg_hint_plan.parse_messages TO DEFAULT;
SHOW pg_hint_plan.parse_messages;
/*+Set*/SELECT 1;
--- No. A-6-4-12
+-- No. A-8-4-12
SET pg_hint_plan.parse_messages TO fatal;
SHOW pg_hint_plan.parse_messages;
--- No. A-6-4-13
+-- No. A-8-4-13
SET pg_hint_plan.parse_messages TO panic;
SHOW pg_hint_plan.parse_messages;
--- No. A-6-4-14
+-- No. A-8-4-14
SET pg_hint_plan.parse_messages TO on;
SHOW pg_hint_plan.parse_messages;
----
----- 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;
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+
+-- No. A-8-5-2
+SET pg_hint_plan.enable_hint_table TO off;
+SHOW pg_hint_plan.enable_hint_table;
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+
+-- No. A-8-5-3
+SET pg_hint_plan.enable_hint_table TO DEFAULT;
+SHOW pg_hint_plan.enable_hint_table;
+EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
+
+-- No. A-8-5-4
+SET pg_hint_plan.enable_hint_table TO enable;
+SHOW pg_hint_plan.enable_hint_table;
+
+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;
/*+Set()(enable_indexscan on)*/SELECT 1;
/*+Set(enable_indexscan on*/SELECT 1;
----
----- 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;
--- No. A-7-3-2
+-- No. A-9-3-2
/*+SeqScan(no_table)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
--- 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)';
/*+TidScan(t1)BitmapScan(t1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
/*+TidScan(t1)BitmapScan(t1)IndexScan(t1)SeqScan(t1)*/
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1 AND t1.ctid = '(1,1)';
--- 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;
----
----- 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;
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);
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
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)*/
/*+BitmapScan(t1)*/
PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
/*+SeqScan(t1)*/
-EXPLAIN (COSTS false) EXECUTE p1 (1);
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
+/*+SeqScan(t1)*/
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
+/*+SeqScan(t1)*/
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
+/*+SeqScan(t1)*/
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
+/*+SeqScan(t1)*/
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
+/*+SeqScan(t1)*/
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
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);
DEALLOCATE p1;
--- No. A-8-1-3
--- No. A-8-1-4
+-- No. A-10-1-3
+-- No. A-10-1-4
/*+SeqScan(t1)*/
PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 = 1;
EXPLAIN (COSTS false) EXECUTE p1;
/*+BitmapScan(t1)*/
PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
-EXPLAIN (COSTS false) EXECUTE p1 (1);
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
-EXPLAIN (COSTS false) EXECUTE p1 (1);
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
DEALLOCATE p1;
--- No. A-8-1-5
--- No. A-8-1-6
+-- 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;
PREPARE p1 AS SELECT * FROM s1.t1 WHERE t1.c1 < $1;
/*+BitmapScan(t1)*/
-EXPLAIN (COSTS false) EXECUTE p1 (1);
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
+UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
+/*+BitmapScan(t1)*/
+EXPLAIN (COSTS false) EXECUTE p1 (1000);
+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;
+UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
+/*+BitmapScan(t1)*/
+EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
+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);
+/*+SeqScan(t1)*/
+EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
+/*+SeqScan(t1)*/
+EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
+/*+SeqScan(t1)*/
+EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
+/*+SeqScan(t1)*/
+EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
+/*+SeqScan(t1)*/
+EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
+UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
+/*+SeqScan(t1)*/
+EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
+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;
+UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
+EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
+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);
+EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
+EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
+EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
+EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
+EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
+UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
+EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
+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;
UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
/*+BitmapScan(t1)*/
-EXPLAIN (COSTS false) EXECUTE p1 (1);
+EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1;
+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);
+/*+BitmapScan(t1)*/
+EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
+/*+BitmapScan(t1)*/
+EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
+/*+BitmapScan(t1)*/
+EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
+/*+BitmapScan(t1)*/
+EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
+/*+BitmapScan(t1)*/
+EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
+UPDATE pg_catalog.pg_class SET relpages = relpages WHERE relname = 't1';
+/*+BitmapScan(t1)*/
+EXPLAIN (COSTS false) CREATE TABLE test AS EXECUTE p1 (1000);
DEALLOCATE p1;
----
----- No. A-8-4 EXECUTE statement name error
+---- No. A-10-4 EXECUTE statement name error
----
--- No. A-8-4-1
+-- No. A-10-4-1
EXECUTE p1;
SHOW pg_hint_plan.debug_print;
----
----- No. A-9-5 EXECUTE statement name error
+---- No. A-11-5 EXECUTE statement name error
----
--- No. A-9-5-1
-CREATE EXTENSION pg_stat_statements;
+-- No. A-11-5-1
SELECT pg_stat_statements_reset();
SELECT * FROM s1.t1 WHERE t1.c1 = 1;
/*+Set(enable_seqscan off)*/ SELECT * FROM s1.t1 WHERE t1.c1 = 1;
/*+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;
\connect
+SET enable_indexscan TO off;
+SET enable_mergejoin TO off;
LOAD 'pg_hint_plan';
SELECT name, setting FROM settings;
EXPLAIN (COSTS false) SELECT * FROM s1.t1, s1.t2 WHERE t1.c1 = t2.c1;
-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;
+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;
+/*+SeqScan(t_1)*/
+EXPLAIN (COSTS false) SELECT nested_planner(1) FROM s1.t1 t_1 ORDER BY t_1.c1;
+
+----
+---- 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;
+/*+SeqScan(t_2)*/
+EXPLAIN (COSTS false) SELECT nested_planner(1) FROM s1.t1 t_1 ORDER BY t_1.c1;
+
+--No.13-3-2
+EXPLAIN (COSTS false) SELECT nested_planner(2) FROM s1.t1 t_1 ORDER BY t_1.c1;
+/*+SeqScan(t_2)*/
+EXPLAIN (COSTS false) SELECT nested_planner(2) FROM s1.t1 t_1 ORDER BY t_1.c1;
+
+--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;
+
+EXPLAIN (COSTS false) SELECT nested_planner(5) FROM s1.t1 t_1 ORDER BY t_1.c1;
+/*+SeqScan(t_2)*/
+EXPLAIN (COSTS false) SELECT nested_planner(5) FROM s1.t1 t_1 ORDER BY t_1.c1;
+
+----
+---- 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
+/*+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;
+
+--No.13-4-2
+/*+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;
+
+--No.13-4-3
+/*+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;
+
+--No.13-4-4
+/*+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;
+
+--No.13-4-5
+/*+HashJoin(t_1 t_1)*/
+EXPLAIN (COSTS false)
+ SELECT recall_planner() FROM s1.t1 t_1
+ ORDER BY t_1.c1;
+
+--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;
+/*+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;
+
+DROP FUNCTION recall_planner_one_t(int);
+
+--No.13-4-7
+/*+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;
+
+--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;