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 pattern
----
---- 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)*/
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;