提交: f8b0032df46fe4a4940c611d0c6567cbe7685fcc - pg_hint_plan (git)

firtst release

Commit MetaInfo

• 修订版: f8b0032df46fe4a4940c611d0c6567cbe7685fcc (tree)
• 时间: 2013-06-21 13:21:59
• 作者: Takashi Suzuki <suzuki.takashi@metr...>
• Commiter: Takashi Suzuki

Log Message

既存機能の向上や新規機能の追加を考慮してリグレッションテストを更新した。

追加で以下の機能を試験するようにした。
・記述位置の自由度が向上したクエリコメント方式によるヒント取得機能
・ヒント用テーブル方式によるヒント取得機能
・ヒント用テーブル検索の制御機能
・plpgsqlで記述したクエリから、クエリコメント方式でヒントを取得する

機能

更改概述

• modified: expected/pg_hint_plan-9.1.out
• modified: expected/pg_hint_plan-9.2.out
• modified: sql/pg_hint_plan.sql

差异

--- a/expected/pg_hint_plan-9.1.out
+++ b/expected/pg_hint_plan-9.1.out
@@ -115,6 +115,24 @@ error hint:
          ->  Seq Scan on t2
 (5 rows)
 
+EXPLAIN (COSTS false) /*+Set(enable_indexscan off)*/
+ SELECT * FROM t1, t2 WHERE t1.id = t2.id;
+LOG:  pg_hint_plan:
+used hint:
+Set(enable_indexscan off)
+not used hint:
+duplication hint:
+error hint:
+
+          QUERY PLAN          
+------------------------------
+ Hash Join
+   Hash Cond: (t1.id = t2.id)
+   ->  Seq Scan on t1
+   ->  Hash
+         ->  Seq Scan on t2
+(5 rows)
+
 /*+ Set(enable_indexscan off) Set(enable_hashjoin off) */
 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
 LOG:  pg_hint_plan:
@@ -7859,3 +7877,58 @@ error hint:
                Filter: (val = 1)
 (20 rows)
 
+-- 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.lookup_hint_in_table = on;
+EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = 1;
+LOG:  pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+     QUERY PLAN     
+--------------------
+ Seq Scan on t1
+   Filter: (id = 1)
+(2 rows)
+
+SET pg_hint_plan.lookup_hint_in_table = off;
+EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = 1;
+
+          QUERY PLAN            
+--------------------------------
+ Index Scan using t1_pkey on t1
+   Index Cond: (id = 1)
+(2 rows)
+
+TRUNCATE hint_plan.hints;
+VACUUM ANALYZE;
+-- plpgsql test
+EXPLAIN SELECT id FROM t1 WHERE t1.id = 1;
+                            QUERY PLAN                            
+------------------------------------------------------------------
+ Index Scan using t1_pkey on t1  (cost=0.00..8.27 rows=1 width=4)
+   Index Cond: (id = 1)
+(2 rows)
+
+SET client_min_messages = LOG;
+DO LANGUAGE plpgsql $$
+DECLARE
+    id integer;
+BEGIN
+	SELECT /*+SeqScan(t1)*/ t1.id INTO id FROM t1 WHERE t1.id = 1;
+	RETURN;
+END;
+$$;
+LOG:  pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+CONTEXT:  SQL statement "SELECT /*+SeqScan(t1)*/ t1.id         FROM t1 WHERE t1.id = 1"
+PL/pgSQL function "inline_code_block" line 5 at SQL statement
+RESET client_min_messages;

--- a/expected/pg_hint_plan-9.2.out
+++ b/expected/pg_hint_plan-9.2.out
@@ -115,6 +115,24 @@ error hint:
          ->  Seq Scan on t2
 (5 rows)
 
+EXPLAIN (COSTS false) /*+Set(enable_indexscan off)*/
+ SELECT * FROM t1, t2 WHERE t1.id = t2.id;
+LOG:  pg_hint_plan:
+used hint:
+Set(enable_indexscan off)
+not used hint:
+duplication hint:
+error hint:
+
+          QUERY PLAN          
+------------------------------
+ Hash Join
+   Hash Cond: (t1.id = t2.id)
+   ->  Seq Scan on t1
+   ->  Hash
+         ->  Seq Scan on t2
+(5 rows)
+
 /*+ Set(enable_indexscan off) Set(enable_hashjoin off) */
 EXPLAIN (COSTS false) SELECT * FROM t1, t2 WHERE t1.id = t2.id;
 LOG:  pg_hint_plan:
@@ -7947,3 +7965,57 @@ error hint:
                Filter: (val = 1)
 (20 rows)
 
+-- 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.lookup_hint_in_table = on;
+EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = 1;
+LOG:  pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+     QUERY PLAN     
+--------------------
+ Seq Scan on t1
+   Filter: (id = 1)
+(2 rows)
+
+SET pg_hint_plan.lookup_hint_in_table = off;
+EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = 1;
+           QUERY PLAN           
+--------------------------------
+ Index Scan using t1_pkey on t1
+   Index Cond: (id = 1)
+(2 rows)
+
+TRUNCATE hint_plan.hints;
+VACUUM ANALYZE;
+-- plpgsql test
+EXPLAIN SELECT id FROM t1 WHERE t1.id = 1;
+                              QUERY PLAN                               
+-----------------------------------------------------------------------
+ Index Only Scan using t1_pkey on t1  (cost=0.00..4.28 rows=1 width=4)
+   Index Cond: (id = 1)
+(2 rows)
+
+SET client_min_messages = LOG;
+DO LANGUAGE plpgsql $$
+DECLARE
+    id integer;
+BEGIN
+	SELECT /*+SeqScan(t1)*/ t1.id INTO id FROM t1 WHERE t1.id = 1;
+	RETURN;
+END;
+$$;
+LOG:  pg_hint_plan:
+used hint:
+SeqScan(t1)
+not used hint:
+duplication hint:
+error hint:
+
+CONTEXT:  SQL statement "SELECT /*+SeqScan(t1)*/ t1.id         FROM t1 WHERE t1.id = 1"
+PL/pgSQL function inline_code_block line 5 at SQL statement
+RESET client_min_messages;

--- 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,25 @@ 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.lookup_hint_in_table = on;
+EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = 1;
+SET pg_hint_plan.lookup_hint_in_table = off;
+EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = 1;
+TRUNCATE hint_plan.hints;
+VACUUM ANALYZE;
+
+-- plpgsql test
+EXPLAIN SELECT id FROM t1 WHERE t1.id = 1;
+SET client_min_messages = LOG;
+DO LANGUAGE plpgsql $$
+DECLARE
+    id integer;
+BEGIN
+	SELECT /*+SeqScan(t1)*/ t1.id INTO id FROM t1 WHERE t1.id = 1;
+	RETURN;
+END;
+$$;
+RESET client_min_messages;