[No]IndexOnlyScanヒントの試験パターンを追加し、目的コメントを加えた。

diff --git a/expected/indexonly-9.1.out b/expected/indexonly-9.1.out
index 68b523a..a11e056 100644 (file)
--- a/expected/indexonly-9.1.out
+++ b/expected/indexonly-9.1.out
@@ -4,6 +4,7 @@ SET client_min_messages TO LOG;
 --
 -- IndexOnlyScan hint test
 --
+-- 1.ヒントつきクエリとの比較用
 EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 t_1 WHERE c2 < 1000;
       QUERY PLAN       
 -----------------------
@@ -11,6 +12,18 @@ EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 t_1 WHERE c2 < 1000;
    Filter: (c2 < 1000)
 (2 rows)
 
+-- 2.テーブルを指定、1と比較
+/*+IndexOnlyScan(ti1)*/
+EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 WHERE c2 < 1000;
+INFO:  hint syntax error at or near "IndexOnlyScan(ti1)"
+DETAIL:  Unrecognized hint keyword "IndexOnlyScan".
+      QUERY PLAN       
+-----------------------
+ Seq Scan on ti1
+   Filter: (c2 < 1000)
+(2 rows)
+
+-- 3.エイリアスを指定、1と比較
 /*+IndexOnlyScan(t_1)*/
 EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 t_1 WHERE c2 < 1000;
 INFO:  hint syntax error at or near "IndexOnlyScan(t_1)"
@@ -21,9 +34,21 @@ DETAIL:  Unrecognized hint keyword "IndexOnlyScan".
    Filter: (c2 < 1000)
 (2 rows)
 
-/*+IndexOnlyScan(t_1 ti1_i1)*/
+-- 4.インデックスを指定、1と比較
+/*+IndexOnlyScan(ti1_i2)*/
+EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 WHERE c2 < 1000;
+INFO:  hint syntax error at or near "IndexOnlyScan(ti1_i2)"
+DETAIL:  Unrecognized hint keyword "IndexOnlyScan".
+      QUERY PLAN       
+-----------------------
+ Seq Scan on ti1
+   Filter: (c2 < 1000)
+(2 rows)
+
+-- 5.エイリアスとインデックスを指定・指定したインデックスを使用、2と比較
+/*+IndexOnlyScan(t_1 ti1_i2)*/
 EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 t_1 WHERE c2 < 1000;
-INFO:  hint syntax error at or near "IndexOnlyScan(t_1 ti1_i1)"
+INFO:  hint syntax error at or near "IndexOnlyScan(t_1 ti1_i2)"
 DETAIL:  Unrecognized hint keyword "IndexOnlyScan".
       QUERY PLAN       
 -----------------------
@@ -31,6 +56,7 @@ DETAIL:  Unrecognized hint keyword "IndexOnlyScan".
    Filter: (c2 < 1000)
 (2 rows)
 
+-- 6.エイリアス1つとインデックス2つを指定、2つ目のインデックスを使用、5と比較
 /*+IndexOnlyScan(t_1 ti1_i2 ti1_i1)*/
 EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 t_1 WHERE c2 < 1000;
 INFO:  hint syntax error at or near "IndexOnlyScan(t_1 ti1_i2 ti1_i1)"
@@ -41,6 +67,8 @@ DETAIL:  Unrecognized hint keyword "IndexOnlyScan".
    Filter: (c2 < 1000)
 (2 rows)
 
+-- 7.Index ScanとIndex Only Scanの両方で使用しないようなインデックスを指定、
+--   5と比較
 /*+IndexOnlyScan(t_1 ti1_pred)*/
 EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 t_1 WHERE c2 < 1000;
 INFO:  hint syntax error at or near "IndexOnlyScan(t_1 ti1_pred)"
@@ -51,6 +79,7 @@ DETAIL:  Unrecognized hint keyword "IndexOnlyScan".
    Filter: (c2 < 1000)
 (2 rows)
 
+-- 8.存在しないインデックスを指定
 /*+IndexOnlyScan(t_1 ti1_i)*/
 EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 t_1 WHERE c2 < 1000;
 INFO:  hint syntax error at or near "IndexOnlyScan(t_1 ti1_i)"
@@ -61,6 +90,10 @@ DETAIL:  Unrecognized hint keyword "IndexOnlyScan".
    Filter: (c2 < 1000)
 (2 rows)
 
+--
+-- IndexScan hint test
+--
+-- 1.IndexScanヒントが今までどおり使えることの確認
 /*+IndexScan(t_1)*/
 EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 t_1 WHERE c2 < 1000;
 LOG:  pg_hint_plan:
@@ -79,6 +112,7 @@ error hint:
 --
 -- NoIndexOnlyScan hint test
 --
+-- 1.ヒントつきクエリとの比較用
 EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 t_1 WHERE c2 < 10;
              QUERY PLAN             
 ------------------------------------
@@ -86,6 +120,18 @@ EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 t_1 WHERE c2 < 10;
    Index Cond: (c2 < 10)
 (2 rows)
 
+-- 2.テーブルを指定、1と比較
+/*+NoIndexOnlyScan(ti1)*/
+EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 WHERE c2 < 10;
+INFO:  hint syntax error at or near "NoIndexOnlyScan(ti1)"
+DETAIL:  Unrecognized hint keyword "NoIndexOnlyScan".
+           QUERY PLAN           
+--------------------------------
+ Index Scan using ti1_i1 on ti1
+   Index Cond: (c2 < 10)
+(2 rows)
+
+-- 3.エイリアスを指定、1と比較
 /*+NoIndexOnlyScan(t_1)*/
 EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 t_1 WHERE c2 < 10;
 INFO:  hint syntax error at or near "NoIndexOnlyScan(t_1)"
@@ -96,6 +142,18 @@ DETAIL:  Unrecognized hint keyword "NoIndexOnlyScan".
    Index Cond: (c2 < 10)
 (2 rows)
 
+-- 4.インデックスを指定、1と比較
+/*+NoIndexOnlyScan(ti1_i2)*/
+EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 WHERE c2 < 10;
+INFO:  hint syntax error at or near "NoIndexOnlyScan(ti1_i2)"
+DETAIL:  Unrecognized hint keyword "NoIndexOnlyScan".
+           QUERY PLAN           
+--------------------------------
+ Index Scan using ti1_i1 on ti1
+   Index Cond: (c2 < 10)
+(2 rows)
+
+-- 5.エイリアスとインデックスを指定、2と比較
 /*+NoIndexOnlyScan(t_1 ti1_i1)*/
 EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 t_1 WHERE c2 < 10;
 INFO:  hint syntax error at or near "NoIndexOnlyScan(t_1 ti1_i1)"
@@ -106,6 +164,10 @@ DETAIL:  Unrecognized hint keyword "NoIndexOnlyScan".
    Index Cond: (c2 < 10)
 (2 rows)
 
+--
+-- NoIndexScan hint test
+--
+-- 1.Index ScanだけでなIndex Only Scanも選択されないことの確認
 /*+NoIndexScan(t_1)*/
 EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 t_1 WHERE c2 < 10;
 LOG:  pg_hint_plan:

diff --git a/expected/indexonly-9.2.out b/expected/indexonly-9.2.out
index ab6fb5c..18dcc56 100644 (file)
--- a/expected/indexonly-9.2.out
+++ b/expected/indexonly-9.2.out
@@ -4,6 +4,7 @@ SET client_min_messages TO LOG;
 --
 -- IndexOnlyScan hint test
 --
+-- 1.ヒントつきクエリとの比較用
 EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 t_1 WHERE c2 < 1000;
       QUERY PLAN       
 -----------------------
@@ -11,6 +12,23 @@ EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 t_1 WHERE c2 < 1000;
    Filter: (c2 < 1000)
 (2 rows)
 
+-- 2.テーブルを指定、1と比較
+/*+IndexOnlyScan(ti1)*/
+EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 WHERE c2 < 1000;
+LOG:  pg_hint_plan:
+used hint:
+IndexOnlyScan(ti1)
+not used hint:
+duplication hint:
+error hint:
+
+             QUERY PLAN              
+-------------------------------------
+ Index Only Scan using ti1_i1 on ti1
+   Index Cond: (c2 < 1000)
+(2 rows)
+
+-- 3.エイリアスを指定、1と比較
 /*+IndexOnlyScan(t_1)*/
 EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 t_1 WHERE c2 < 1000;
 LOG:  pg_hint_plan:
@@ -26,21 +44,39 @@ error hint:
    Index Cond: (c2 < 1000)
 (2 rows)
 
-/*+IndexOnlyScan(t_1 ti1_i1)*/
+-- 4.インデックスを指定、1と比較
+/*+IndexOnlyScan(ti1_i2)*/
+EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 WHERE c2 < 1000;
+LOG:  pg_hint_plan:
+used hint:
+not used hint:
+IndexOnlyScan(ti1_i2)
+duplication hint:
+error hint:
+
+      QUERY PLAN       
+-----------------------
+ Seq Scan on ti1
+   Filter: (c2 < 1000)
+(2 rows)
+
+-- 5.エイリアスとインデックスを指定・指定したインデックスを使用、2と比較
+/*+IndexOnlyScan(t_1 ti1_i2)*/
 EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 t_1 WHERE c2 < 1000;
 LOG:  pg_hint_plan:
 used hint:
-IndexOnlyScan(t_1 ti1_i1)
+IndexOnlyScan(t_1 ti1_i2)
 not used hint:
 duplication hint:
 error hint:
 
                QUERY PLAN                
 -----------------------------------------
- Index Only Scan using ti1_i1 on ti1 t_1
+ Index Only Scan using ti1_i2 on ti1 t_1
    Index Cond: (c2 < 1000)
 (2 rows)
 
+-- 6.エイリアス1つとインデックス2つを指定、2つ目のインデックスを使用、5と比較
 /*+IndexOnlyScan(t_1 ti1_i2 ti1_i1)*/
 EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 t_1 WHERE c2 < 1000;
 LOG:  pg_hint_plan:
@@ -56,6 +92,8 @@ error hint:
    Index Cond: (c2 < 1000)
 (2 rows)
 
+-- 7.Index ScanとIndex Only Scanの両方で使用しないようなインデックスを指定、
+--   5と比較
 /*+IndexOnlyScan(t_1 ti1_pred)*/
 EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 t_1 WHERE c2 < 1000;
 LOG:  pg_hint_plan:
@@ -71,6 +109,7 @@ error hint:
    Filter: (c2 < 1000)
 (2 rows)
 
+-- 8.存在しないインデックスを指定
 /*+IndexOnlyScan(t_1 ti1_i)*/
 EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 t_1 WHERE c2 < 1000;
 LOG:  pg_hint_plan:
@@ -86,6 +125,10 @@ error hint:
    Filter: (c2 < 1000)
 (2 rows)
 
+--
+-- IndexScan hint test
+--
+-- 1.IndexScanヒントが今までどおり使えることの確認
 /*+IndexScan(t_1)*/
 EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 t_1 WHERE c2 < 1000;
 LOG:  pg_hint_plan:
@@ -104,6 +147,7 @@ error hint:
 --
 -- NoIndexOnlyScan hint test
 --
+-- 1.ヒントつきクエリとの比較用
 EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 t_1 WHERE c2 < 10;
                QUERY PLAN                
 -----------------------------------------
@@ -111,6 +155,23 @@ EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 t_1 WHERE c2 < 10;
    Index Cond: (c2 < 10)
 (2 rows)
 
+-- 2.テーブルを指定、1と比較
+/*+NoIndexOnlyScan(ti1)*/
+EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 WHERE c2 < 10;
+LOG:  pg_hint_plan:
+used hint:
+NoIndexOnlyScan(ti1)
+not used hint:
+duplication hint:
+error hint:
+
+           QUERY PLAN           
+--------------------------------
+ Index Scan using ti1_i1 on ti1
+   Index Cond: (c2 < 10)
+(2 rows)
+
+-- 3.エイリアスを指定、1と比較
 /*+NoIndexOnlyScan(t_1)*/
 EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 t_1 WHERE c2 < 10;
 LOG:  pg_hint_plan:
@@ -126,6 +187,23 @@ error hint:
    Index Cond: (c2 < 10)
 (2 rows)
 
+-- 4.インデックスを指定、1と比較
+/*+NoIndexOnlyScan(ti1_i2)*/
+EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 WHERE c2 < 10;
+LOG:  pg_hint_plan:
+used hint:
+not used hint:
+NoIndexOnlyScan(ti1_i2)
+duplication hint:
+error hint:
+
+             QUERY PLAN              
+-------------------------------------
+ Index Only Scan using ti1_i1 on ti1
+   Index Cond: (c2 < 10)
+(2 rows)
+
+-- 5.エイリアスとインデックスを指定、2と比較
 /*+NoIndexOnlyScan(t_1 ti1_i1)*/
 EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 t_1 WHERE c2 < 10;
 INFO:  hint syntax error at or near "ti1_i1)"
@@ -136,6 +214,10 @@ DETAIL:  Closing parenthesis is necessary.
    Index Cond: (c2 < 10)
 (2 rows)
 
+--
+-- NoIndexScan hint test
+--
+-- 1.Index ScanだけでなIndex Only Scanも選択されないことの確認
 /*+NoIndexScan(t_1)*/
 EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 t_1 WHERE c2 < 10;
 LOG:  pg_hint_plan:

diff --git a/sql/indexonly.sql b/sql/indexonly.sql
index e89c349..599128c 100644 (file)
--- a/sql/indexonly.sql
+++ b/sql/indexonly.sql
@@ -5,37 +5,75 @@ SET client_min_messages TO LOG;
 --
 -- IndexOnlyScan hint test
 --
+
+-- 1.ヒントつきクエリとの比較用
 EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 t_1 WHERE c2 < 1000;
 
+-- 2.テーブルを指定、1と比較
+/*+IndexOnlyScan(ti1)*/
+EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 WHERE c2 < 1000;
+
+-- 3.エイリアスを指定、1と比較
 /*+IndexOnlyScan(t_1)*/
 EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 t_1 WHERE c2 < 1000;
 
-/*+IndexOnlyScan(t_1 ti1_i1)*/
+-- 4.インデックスを指定、1と比較
+/*+IndexOnlyScan(ti1_i2)*/
+EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 WHERE c2 < 1000;
+
+-- 5.エイリアスとインデックスを指定・指定したインデックスを使用、2と比較
+/*+IndexOnlyScan(t_1 ti1_i2)*/
 EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 t_1 WHERE c2 < 1000;
 
+-- 6.エイリアス1つとインデックス2つを指定、2つ目のインデックスを使用、5と比較
 /*+IndexOnlyScan(t_1 ti1_i2 ti1_i1)*/
 EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 t_1 WHERE c2 < 1000;
 
+-- 7.Index ScanとIndex Only Scanの両方で使用しないようなインデックスを指定、
+--   5と比較
 /*+IndexOnlyScan(t_1 ti1_pred)*/
 EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 t_1 WHERE c2 < 1000;
 
+-- 8.存在しないインデックスを指定
 /*+IndexOnlyScan(t_1 ti1_i)*/
 EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 t_1 WHERE c2 < 1000;
 
+--
+-- IndexScan hint test
+--
+
+-- 1.IndexScanヒントが今までどおり使えることの確認
 /*+IndexScan(t_1)*/
 EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 t_1 WHERE c2 < 1000;
 
 --
 -- NoIndexOnlyScan hint test
 --
+
+-- 1.ヒントつきクエリとの比較用
 EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 t_1 WHERE c2 < 10;
 
+-- 2.テーブルを指定、1と比較
+/*+NoIndexOnlyScan(ti1)*/
+EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 WHERE c2 < 10;
+
+-- 3.エイリアスを指定、1と比較
 /*+NoIndexOnlyScan(t_1)*/
 EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 t_1 WHERE c2 < 10;
 
+-- 4.インデックスを指定、1と比較
+/*+NoIndexOnlyScan(ti1_i2)*/
+EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 WHERE c2 < 10;
+
+-- 5.エイリアスとインデックスを指定、2と比較
 /*+NoIndexOnlyScan(t_1 ti1_i1)*/
 EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 t_1 WHERE c2 < 10;
 
+--
+-- NoIndexScan hint test
+--
+
+-- 1.Index ScanだけでなIndex Only Scanも選択されないことの確認
 /*+NoIndexScan(t_1)*/
 EXPLAIN (COSTS false) SELECT c2 FROM s1.ti1 t_1 WHERE c2 < 10;