EXTENSION機構の試験のSQLを追加した

[pghintplan/pg_hint_plan.git] / sql / pg_hint_plan.sql

diff --git a/sql/pg_hint_plan.sql b/sql/pg_hint_plan.sql
index e517ad5..1034fc9 100644 (file)
--- a/sql/pg_hint_plan.sql
+++ b/sql/pg_hint_plan.sql
@@ -25,6 +25,8 @@ EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
 /*+Set(enable_indexscan off)*/
 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
+EXPLAIN (COSTS false) /*+Set(enable_indexscan off)*/
+ SELECT * FROM t1, t2 WHERE t1.id = t2.id;
 /*+ Set(enable_indexscan off) Set(enable_hashjoin off) */
 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
 
@@ -819,3 +821,24 @@ EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
 EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
 /*+ BitmapScan(p1 p1_.*val2.*)*/
 EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1;
+
+-- search from hint table
+INSERT INTO hint_plan.hints VALUES ('EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = ?;', '', 'SeqScan(t1)');
+SET pg_hint_plan.enable_hint_table = on;
+EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = 1;
+SET pg_hint_plan.enable_hint_table = off;
+EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = 1;
+TRUNCATE hint_plan.hints;
+VACUUM ANALYZE hint_plan.hints;
+
+-- plpgsql test
+EXPLAIN (COSTS false) SELECT id FROM t1 WHERE t1.id = 1;
+DO LANGUAGE plpgsql $$
+DECLARE
+    id integer;
+BEGIN
+       SELECT /*+SeqScan(t1)*/ t1.id INTO id FROM t1 WHERE t1.id = 1;
+       RETURN;
+END;
+$$;
+DROP EXTENSION pg_hint_plan;