• R/O
  • HTTP
  • SSH
  • HTTPS

pg_hint_plan: Commit

firtst release


Commit MetaInfo

Revisionbb68989269e4a1b5f529bb2a04906e3a879c1df1 (tree)
Time2013-08-09 15:35:44
AuthorTakashi Suzuki <suzuki.takashi@metr...>
CommiterTakashi Suzuki

Log Message

インデックスの正規化とインデックスメッセージ出力の試験を追加した。

Change Summary

Incremental Difference

--- a/expected/ut-S-9.1.out
+++ b/expected/ut-S-9.1.out
@@ -5736,3 +5736,224 @@ DETAIL: Unrecognized hint keyword "NoIndexOnlyScan".
57365736 Index Cond: (c1 = 1)
57375737 (2 rows)
57385738
5739+----
5740+---- No. S-3-13 message output
5741+----
5742+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
5743+ QUERY PLAN
5744+--------------------------------
5745+ Index Scan using ti1_i2 on ti1
5746+ Index Cond: (c2 = 1)
5747+(2 rows)
5748+
5749+-- No. S-3-13-1
5750+/*+IndexScanRegexp(ti1 ti1_.*_key)*/
5751+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
5752+LOG: available indexes for IndexScanRegexp(ti1): ti1_c2_key
5753+LOG: pg_hint_plan:
5754+used hint:
5755+IndexScanRegexp(ti1 ti1_.*_key)
5756+not used hint:
5757+duplication hint:
5758+error hint:
5759+
5760+ QUERY PLAN
5761+------------------------------------
5762+ Index Scan using ti1_c2_key on ti1
5763+ Index Cond: (c2 = 1)
5764+(2 rows)
5765+
5766+-- No. S-3-13-2
5767+/*+IndexScanRegexp(ti1 ti1_i.)*/
5768+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
5769+LOG: available indexes for IndexScanRegexp(ti1): ti1_i4 ti1_i3 ti1_i2 ti1_i1
5770+LOG: pg_hint_plan:
5771+used hint:
5772+IndexScanRegexp(ti1 ti1_i.)
5773+not used hint:
5774+duplication hint:
5775+error hint:
5776+
5777+ QUERY PLAN
5778+--------------------------------
5779+ Index Scan using ti1_i2 on ti1
5780+ Index Cond: (c2 = 1)
5781+(2 rows)
5782+
5783+-- No. S-3-13-3
5784+/*+IndexScanRegexp(ti1 no.*_exist)*/
5785+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
5786+LOG: available indexes for IndexScanRegexp(ti1):
5787+LOG: pg_hint_plan:
5788+used hint:
5789+IndexScanRegexp(ti1 no.*_exist)
5790+not used hint:
5791+duplication hint:
5792+error hint:
5793+
5794+ QUERY PLAN
5795+--------------------
5796+ Seq Scan on ti1
5797+ Filter: (c2 = 1)
5798+(2 rows)
5799+
5800+-- No. S-3-13-4
5801+/*+IndexScanRegexp(p1 .*pkey)*/
5802+EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1;
5803+LOG: available indexes for IndexScanRegexp(p1): p1_pkey
5804+LOG: available indexes for IndexScanRegexp(p1c1): p1c1_pkey
5805+LOG: available indexes for IndexScanRegexp(p1c2): p1c2_pkey
5806+LOG: available indexes for IndexScanRegexp(p1c3): p1c3_pkey
5807+LOG: pg_hint_plan:
5808+used hint:
5809+IndexScanRegexp(p1 .*pkey)
5810+not used hint:
5811+duplication hint:
5812+error hint:
5813+
5814+ QUERY PLAN
5815+---------------------------------------------------
5816+ Result
5817+ -> Append
5818+ -> Index Scan using p1_pkey on p1
5819+ Index Cond: (c1 = 1)
5820+ -> Index Scan using p1c1_pkey on p1c1 p1
5821+ Index Cond: (c1 = 1)
5822+(6 rows)
5823+
5824+-- No. S-3-13-5
5825+/*+IndexScanRegexp(p1 p1.*i)*/
5826+EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1;
5827+LOG: available indexes for IndexScanRegexp(p1): p1_i2 p1_i
5828+LOG: available indexes for IndexScanRegexp(p1c1): p1c1_i p1c1_c4_expr_idx
5829+LOG: available indexes for IndexScanRegexp(p1c2): p1c2_i p1c2_c4_expr_idx
5830+LOG: available indexes for IndexScanRegexp(p1c3): p1c3_i p1c3_c4_expr_idx
5831+LOG: pg_hint_plan:
5832+used hint:
5833+IndexScanRegexp(p1 p1.*i)
5834+not used hint:
5835+duplication hint:
5836+error hint:
5837+
5838+ QUERY PLAN
5839+------------------------------------------------
5840+ Result
5841+ -> Append
5842+ -> Index Scan using p1_i on p1
5843+ Index Cond: (c1 = 1)
5844+ -> Index Scan using p1c1_i on p1c1 p1
5845+ Index Cond: (c1 = 1)
5846+(6 rows)
5847+
5848+-- No. S-3-13-6
5849+/*+IndexScanRegexp(p1 no.*_exist)*/
5850+EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1;
5851+LOG: available indexes for IndexScanRegexp(p1):
5852+LOG: available indexes for IndexScanRegexp(p1c1):
5853+LOG: available indexes for IndexScanRegexp(p1c2):
5854+LOG: available indexes for IndexScanRegexp(p1c3):
5855+LOG: pg_hint_plan:
5856+used hint:
5857+IndexScanRegexp(p1 no.*_exist)
5858+not used hint:
5859+duplication hint:
5860+error hint:
5861+
5862+ QUERY PLAN
5863+---------------------------------
5864+ Result
5865+ -> Append
5866+ -> Seq Scan on p1
5867+ Filter: (c1 = 1)
5868+ -> Seq Scan on p1c1 p1
5869+ Filter: (c1 = 1)
5870+(6 rows)
5871+
5872+----
5873+---- No. S-3-14 message output
5874+----
5875+-- No. S-3-14-1
5876+/*+IndexScan(ti1 ti1_i1)*/
5877+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
5878+LOG: available indexes for IndexScan(ti1): ti1_i1
5879+LOG: pg_hint_plan:
5880+used hint:
5881+IndexScan(ti1 ti1_i1)
5882+not used hint:
5883+duplication hint:
5884+error hint:
5885+
5886+ QUERY PLAN
5887+--------------------------------
5888+ Index Scan using ti1_i1 on ti1
5889+ Index Cond: (c2 = 1)
5890+(2 rows)
5891+
5892+-- No. S-3-14-2
5893+/*+IndexScan(ti1 not_exist)*/
5894+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
5895+LOG: available indexes for IndexScan(ti1):
5896+LOG: pg_hint_plan:
5897+used hint:
5898+IndexScan(ti1 not_exist)
5899+not used hint:
5900+duplication hint:
5901+error hint:
5902+
5903+ QUERY PLAN
5904+--------------------
5905+ Seq Scan on ti1
5906+ Filter: (c2 = 1)
5907+(2 rows)
5908+
5909+-- No. S-3-14-3
5910+/*+IndexScan(ti1 ti1_i1 ti1_i2)*/
5911+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
5912+LOG: available indexes for IndexScan(ti1): ti1_i2 ti1_i1
5913+LOG: pg_hint_plan:
5914+used hint:
5915+IndexScan(ti1 ti1_i1 ti1_i2)
5916+not used hint:
5917+duplication hint:
5918+error hint:
5919+
5920+ QUERY PLAN
5921+--------------------------------
5922+ Index Scan using ti1_i2 on ti1
5923+ Index Cond: (c2 = 1)
5924+(2 rows)
5925+
5926+-- No. S-3-14-4
5927+/*+IndexScan(ti1 ti1_i1 not_exist)*/
5928+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
5929+LOG: available indexes for IndexScan(ti1): ti1_i1
5930+LOG: pg_hint_plan:
5931+used hint:
5932+IndexScan(ti1 ti1_i1 not_exist)
5933+not used hint:
5934+duplication hint:
5935+error hint:
5936+
5937+ QUERY PLAN
5938+--------------------------------
5939+ Index Scan using ti1_i1 on ti1
5940+ Index Cond: (c2 = 1)
5941+(2 rows)
5942+
5943+-- No. S-3-14-5
5944+/*+IndexScan(ti1 not_exist1 not_exist2)*/
5945+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
5946+LOG: available indexes for IndexScan(ti1):
5947+LOG: pg_hint_plan:
5948+used hint:
5949+IndexScan(ti1 not_exist1 not_exist2)
5950+not used hint:
5951+duplication hint:
5952+error hint:
5953+
5954+ QUERY PLAN
5955+--------------------
5956+ Seq Scan on ti1
5957+ Filter: (c2 = 1)
5958+(2 rows)
5959+
--- a/expected/ut-S-9.2.out
+++ b/expected/ut-S-9.2.out
@@ -5873,3 +5873,224 @@ NoIndexOnlyScan(ti1 ti1_pkey ti1_btree)
58735873 Index Cond: (c1 = 1)
58745874 (2 rows)
58755875
5876+----
5877+---- No. S-3-13 message output
5878+----
5879+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
5880+ QUERY PLAN
5881+--------------------------------
5882+ Index Scan using ti1_i2 on ti1
5883+ Index Cond: (c2 = 1)
5884+(2 rows)
5885+
5886+-- No. S-3-13-1
5887+/*+IndexScanRegexp(ti1 ti1_.*_key)*/
5888+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
5889+LOG: available indexes for IndexScanRegexp(ti1): ti1_c2_key
5890+LOG: pg_hint_plan:
5891+used hint:
5892+IndexScanRegexp(ti1 ti1_.*_key)
5893+not used hint:
5894+duplication hint:
5895+error hint:
5896+
5897+ QUERY PLAN
5898+------------------------------------
5899+ Index Scan using ti1_c2_key on ti1
5900+ Index Cond: (c2 = 1)
5901+(2 rows)
5902+
5903+-- No. S-3-13-2
5904+/*+IndexScanRegexp(ti1 ti1_i.)*/
5905+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
5906+LOG: available indexes for IndexScanRegexp(ti1): ti1_i4 ti1_i3 ti1_i2 ti1_i1
5907+LOG: pg_hint_plan:
5908+used hint:
5909+IndexScanRegexp(ti1 ti1_i.)
5910+not used hint:
5911+duplication hint:
5912+error hint:
5913+
5914+ QUERY PLAN
5915+--------------------------------
5916+ Index Scan using ti1_i2 on ti1
5917+ Index Cond: (c2 = 1)
5918+(2 rows)
5919+
5920+-- No. S-3-13-3
5921+/*+IndexScanRegexp(ti1 no.*_exist)*/
5922+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
5923+LOG: available indexes for IndexScanRegexp(ti1):
5924+LOG: pg_hint_plan:
5925+used hint:
5926+IndexScanRegexp(ti1 no.*_exist)
5927+not used hint:
5928+duplication hint:
5929+error hint:
5930+
5931+ QUERY PLAN
5932+--------------------
5933+ Seq Scan on ti1
5934+ Filter: (c2 = 1)
5935+(2 rows)
5936+
5937+-- No. S-3-13-4
5938+/*+IndexScanRegexp(p1 .*pkey)*/
5939+EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1;
5940+LOG: available indexes for IndexScanRegexp(p1): p1_pkey
5941+LOG: available indexes for IndexScanRegexp(p1c1): p1c1_pkey
5942+LOG: available indexes for IndexScanRegexp(p1c2): p1c2_pkey
5943+LOG: available indexes for IndexScanRegexp(p1c3): p1c3_pkey
5944+LOG: pg_hint_plan:
5945+used hint:
5946+IndexScanRegexp(p1 .*pkey)
5947+not used hint:
5948+duplication hint:
5949+error hint:
5950+
5951+ QUERY PLAN
5952+---------------------------------------------------
5953+ Result
5954+ -> Append
5955+ -> Index Scan using p1_pkey on p1
5956+ Index Cond: (c1 = 1)
5957+ -> Index Scan using p1c1_pkey on p1c1 p1
5958+ Index Cond: (c1 = 1)
5959+(6 rows)
5960+
5961+-- No. S-3-13-5
5962+/*+IndexScanRegexp(p1 p1.*i)*/
5963+EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1;
5964+LOG: available indexes for IndexScanRegexp(p1): p1_i2 p1_i
5965+LOG: available indexes for IndexScanRegexp(p1c1): p1c1_i p1c1_c4_expr_idx
5966+LOG: available indexes for IndexScanRegexp(p1c2): p1c2_i p1c2_c4_expr_idx
5967+LOG: available indexes for IndexScanRegexp(p1c3): p1c3_i p1c3_c4_expr_idx
5968+LOG: pg_hint_plan:
5969+used hint:
5970+IndexScanRegexp(p1 p1.*i)
5971+not used hint:
5972+duplication hint:
5973+error hint:
5974+
5975+ QUERY PLAN
5976+------------------------------------------------
5977+ Result
5978+ -> Append
5979+ -> Index Scan using p1_i on p1
5980+ Index Cond: (c1 = 1)
5981+ -> Index Scan using p1c1_i on p1c1 p1
5982+ Index Cond: (c1 = 1)
5983+(6 rows)
5984+
5985+-- No. S-3-13-6
5986+/*+IndexScanRegexp(p1 no.*_exist)*/
5987+EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1;
5988+LOG: available indexes for IndexScanRegexp(p1):
5989+LOG: available indexes for IndexScanRegexp(p1c1):
5990+LOG: available indexes for IndexScanRegexp(p1c2):
5991+LOG: available indexes for IndexScanRegexp(p1c3):
5992+LOG: pg_hint_plan:
5993+used hint:
5994+IndexScanRegexp(p1 no.*_exist)
5995+not used hint:
5996+duplication hint:
5997+error hint:
5998+
5999+ QUERY PLAN
6000+---------------------------------
6001+ Result
6002+ -> Append
6003+ -> Seq Scan on p1
6004+ Filter: (c1 = 1)
6005+ -> Seq Scan on p1c1 p1
6006+ Filter: (c1 = 1)
6007+(6 rows)
6008+
6009+----
6010+---- No. S-3-14 message output
6011+----
6012+-- No. S-3-14-1
6013+/*+IndexScan(ti1 ti1_i1)*/
6014+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
6015+LOG: available indexes for IndexScan(ti1): ti1_i1
6016+LOG: pg_hint_plan:
6017+used hint:
6018+IndexScan(ti1 ti1_i1)
6019+not used hint:
6020+duplication hint:
6021+error hint:
6022+
6023+ QUERY PLAN
6024+--------------------------------
6025+ Index Scan using ti1_i1 on ti1
6026+ Index Cond: (c2 = 1)
6027+(2 rows)
6028+
6029+-- No. S-3-14-2
6030+/*+IndexScan(ti1 not_exist)*/
6031+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
6032+LOG: available indexes for IndexScan(ti1):
6033+LOG: pg_hint_plan:
6034+used hint:
6035+IndexScan(ti1 not_exist)
6036+not used hint:
6037+duplication hint:
6038+error hint:
6039+
6040+ QUERY PLAN
6041+--------------------
6042+ Seq Scan on ti1
6043+ Filter: (c2 = 1)
6044+(2 rows)
6045+
6046+-- No. S-3-14-3
6047+/*+IndexScan(ti1 ti1_i1 ti1_i2)*/
6048+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
6049+LOG: available indexes for IndexScan(ti1): ti1_i2 ti1_i1
6050+LOG: pg_hint_plan:
6051+used hint:
6052+IndexScan(ti1 ti1_i1 ti1_i2)
6053+not used hint:
6054+duplication hint:
6055+error hint:
6056+
6057+ QUERY PLAN
6058+--------------------------------
6059+ Index Scan using ti1_i2 on ti1
6060+ Index Cond: (c2 = 1)
6061+(2 rows)
6062+
6063+-- No. S-3-14-4
6064+/*+IndexScan(ti1 ti1_i1 not_exist)*/
6065+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
6066+LOG: available indexes for IndexScan(ti1): ti1_i1
6067+LOG: pg_hint_plan:
6068+used hint:
6069+IndexScan(ti1 ti1_i1 not_exist)
6070+not used hint:
6071+duplication hint:
6072+error hint:
6073+
6074+ QUERY PLAN
6075+--------------------------------
6076+ Index Scan using ti1_i1 on ti1
6077+ Index Cond: (c2 = 1)
6078+(2 rows)
6079+
6080+-- No. S-3-14-5
6081+/*+IndexScan(ti1 not_exist1 not_exist2)*/
6082+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
6083+LOG: available indexes for IndexScan(ti1):
6084+LOG: pg_hint_plan:
6085+used hint:
6086+IndexScan(ti1 not_exist1 not_exist2)
6087+not used hint:
6088+duplication hint:
6089+error hint:
6090+
6091+ QUERY PLAN
6092+--------------------
6093+ Seq Scan on ti1
6094+ Filter: (c2 = 1)
6095+(2 rows)
6096+
--- a/sql/ut-S.sql
+++ b/sql/ut-S.sql
@@ -1104,3 +1104,53 @@ EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 = 1;
11041104 -- No. S-3-12-30
11051105 /*+NoIndexOnlyScan(ti1 ti1_pkey ti1_btree)*/
11061106 EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 = 1;
1107+
1108+
1109+----
1110+---- No. S-3-13 message output
1111+----
1112+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
1113+
1114+-- No. S-3-13-1
1115+/*+IndexScanRegexp(ti1 ti1_.*_key)*/
1116+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
1117+
1118+-- No. S-3-13-2
1119+/*+IndexScanRegexp(ti1 ti1_i.)*/
1120+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
1121+
1122+-- No. S-3-13-3
1123+/*+IndexScanRegexp(ti1 no.*_exist)*/
1124+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
1125+
1126+-- No. S-3-13-4
1127+/*+IndexScanRegexp(p1 .*pkey)*/
1128+EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1;
1129+
1130+-- No. S-3-13-5
1131+/*+IndexScanRegexp(p1 p1.*i)*/
1132+EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1;
1133+
1134+-- No. S-3-13-6
1135+/*+IndexScanRegexp(p1 no.*_exist)*/
1136+EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1;
1137+
1138+----
1139+---- No. S-3-14 message output
1140+----
1141+
1142+-- No. S-3-14-1
1143+/*+IndexScan(ti1 ti1_i1)*/
1144+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
1145+-- No. S-3-14-2
1146+/*+IndexScan(ti1 not_exist)*/
1147+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
1148+-- No. S-3-14-3
1149+/*+IndexScan(ti1 ti1_i1 ti1_i2)*/
1150+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
1151+-- No. S-3-14-4
1152+/*+IndexScan(ti1 ti1_i1 not_exist)*/
1153+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
1154+-- No. S-3-14-5
1155+/*+IndexScan(ti1 not_exist1 not_exist2)*/
1156+EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1;
Show on old repository browser