LOAD 'pg_hint_plan';
SET pg_hint_plan.enable_hint TO on;
-SET pg_hint_plan.enable_hint_table TO on;
SET pg_hint_plan.debug_print TO on;
SET client_min_messages TO LOG;
SET search_path TO public;
DROP EXTENSION pg_hint_plan;
-- No.A-1-1-4
-CREATE EXTENSION pg_hint_plan SCHEMA other_name;
+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 comment pattarn
+---- 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)
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
-
--- No. A-5-1-6
---SeqScan(t1)
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
-
--- No. A-5-1-7
-/*+SeqScan(t1) /* nest comment */ */
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
-
--- No. A-5-1-8
-/* +SeqScan(t1) /* nest comment */ */
-EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
-
--- No. A-5-1-9
-/*SeqScan(t1) /* nest comment */ */
+/* /*+SeqScan(t1)*/ */
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
----
EXPLAIN (COSTS false) SELECT c1 FROM s1.t1 WHERE t1.c1 = 1;
-- No. A-5-2-2
-EXPLAIN (COSTS false) SELECT c1 /*+SeqScan(t1)*/ FROM s1.t1 WHERE t1.c1 = 1;
+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-6-1 hint's table definition
----
+
+SET pg_hint_plan.enable_hint_table TO on;
-- No. A-6-1-1
\d hint_plan.hints
----
EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = 1;
-- No. A-6-2-1
-INSERT INTO hint_plan.hints VALUES (
+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-6-2-2
-INSERT INTO hint_plan.hints VALUES (
+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)');
TRUNCATE hint_plan.hints;
-- No. A-6-2-3
-INSERT INTO hint_plan.hints VALUES (
+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)'
TRUNCATE hint_plan.hints;
-- No. A-6-2-4
-INSERT INTO hint_plan.hints VALUES (
+INSERT INTO hint_plan.hints (norm_query_string, application_name, hints)
+ VALUES (
'EXPLAIN (COSTS false) SELECT * FROM s1.t1;',
'',
'SeqScan(t1)'
----
-- No. A-6-3-1
-INSERT INTO hint_plan.hints VALUES (
+INSERT INTO hint_plan.hints (norm_query_string, application_name, hints)
+ VALUES (
'EXPLAIN (COSTS false) SELECT c1 FROM s1.t1;',
'',
'SeqScan(t1)'
TRUNCATE hint_plan.hints;
-- No. A-6-3-2
-INSERT INTO hint_plan.hints VALUES (
+INSERT INTO hint_plan.hints (norm_query_string, application_name, hints)
+ VALUES (
'EXPLAIN (COSTS false) SELECT * FROM s1.t1 WHERE t1.c1 = ?;',
'',
'SeqScan(t1)'
TRUNCATE hint_plan.hints;
-- No. A-6-3-3
-INSERT INTO hint_plan.hints VALUES (
+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-7-2 hint delimiter
-- 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-7-3-4
-- No. A-9-2-4
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-7-4 hint parse error
----
----
---- No. A-8-1 original GUC parameter
----
-
+---- 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;
+-- 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;
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-8-2-3
-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;
+-- 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
-SET pg_hint_plan.enable_hint TO enable;
-SHOW pg_hint_plan.enable_hint;
+-- 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;
SET pg_hint_plan.debug_print TO on;
SHOW pg_hint_plan.debug_print;
/*+Set(enable_indexscan off)*/
---- 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
----
RETURN 0;
END IF;
- EXECUTE '/*+ IndexScan(t_1) */'
- ' SELECT nested_planner($1) FROM s1.t1 t_1'
- ' JOIN s1.t2 t_2 ON (t_1.c1 = t_2.c1)'
- ' ORDER BY t_1.c1 LIMIT 1'
- INTO new_cnt USING cnt - 1;
+ 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;
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 nested_planner(2) FROM s1.t1 t_1
+ 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 nested_planner(2) FROM s1.t1 st_1
+ 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 nested_planner(2) FROM s1.t1 st_1
+ 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 nested_planner(2) FROM s1.t1 t_1
+ 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 nested_planner(2) FROM s1.t1 t_1
+ SELECT recall_planner() FROM s1.t1 t_1
ORDER BY t_1.c1;
--No.13-4-6
-CREATE OR REPLACE FUNCTION nested_planner_one_t(cnt int) RETURNS int AS $$
-DECLARE
- new_cnt int;
-BEGIN
- RAISE NOTICE 'nested_planner_one_t(%)', cnt;
-
- IF cnt <= 1 THEN
- RETURN 0;
- END IF;
-
- EXECUTE '/*+ IndexScan(t_1) */'
- ' SELECT nested_planner_one_t($1) FROM s1.t1 t_1'
- ' ORDER BY t_1.c1 LIMIT 1'
- INTO new_cnt USING cnt - 1;
-
- RETURN new_cnt;
-END;
-$$ LANGUAGE plpgsql IMMUTABLE;
+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 nested_planner_one_t(2) FROM s1.t1 t_1
+ 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 nested_planner_one_t(2) FROM s1.t1 t_1
+ 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 nested_planner_one_t(int);
+DROP FUNCTION recall_planner_one_t(int);
--No.13-4-7
/*+HashJoin(t_1 t_1)*/
EXPLAIN (COSTS false)
- SELECT nested_planner(2) FROM s1.t1 t_1
+ 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 nested_planner(2) FROM s1.t1 t_1
+ 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;
OSDN Git Service
