From bb68989269e4a1b5f529bb2a04906e3a879c1df1 Mon Sep 17 00:00:00 2001 From: Takashi Suzuki Date: Fri, 9 Aug 2013 15:35:44 +0900 Subject: [PATCH] =?utf8?q?=E3=82=A4=E3=83=B3=E3=83=87=E3=83=83=E3=82=AF?= =?utf8?q?=E3=82=B9=E3=81=AE=E6=AD=A3=E8=A6=8F=E5=8C=96=E3=81=A8=E3=82=A4?= =?utf8?q?=E3=83=B3=E3=83=87=E3=83=83=E3=82=AF=E3=82=B9=E3=83=A1=E3=83=83?= =?utf8?q?=E3=82=BB=E3=83=BC=E3=82=B8=E5=87=BA=E5=8A=9B=E3=81=AE=E8=A9=A6?= =?utf8?q?=E9=A8=93=E3=82=92=E8=BF=BD=E5=8A=A0=E3=81=97=E3=81=9F=E3=80=82?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- expected/ut-S-9.1.out | 221 ++++++++++++++++++++++++++++++++++++++++++++++++++ expected/ut-S-9.2.out | 221 ++++++++++++++++++++++++++++++++++++++++++++++++++ sql/ut-S.sql | 50 ++++++++++++ 3 files changed, 492 insertions(+) diff --git a/expected/ut-S-9.1.out b/expected/ut-S-9.1.out index 9781a60..b95e77b 100644 --- a/expected/ut-S-9.1.out +++ b/expected/ut-S-9.1.out @@ -5736,3 +5736,224 @@ DETAIL: Unrecognized hint keyword "NoIndexOnlyScan". Index Cond: (c1 = 1) (2 rows) +---- +---- No. S-3-13 message output +---- +EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1; + QUERY PLAN +-------------------------------- + Index Scan using ti1_i2 on ti1 + Index Cond: (c2 = 1) +(2 rows) + +-- No. S-3-13-1 +/*+IndexScanRegexp(ti1 ti1_.*_key)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1; +LOG: available indexes for IndexScanRegexp(ti1): ti1_c2_key +LOG: pg_hint_plan: +used hint: +IndexScanRegexp(ti1 ti1_.*_key) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +------------------------------------ + Index Scan using ti1_c2_key on ti1 + Index Cond: (c2 = 1) +(2 rows) + +-- No. S-3-13-2 +/*+IndexScanRegexp(ti1 ti1_i.)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1; +LOG: available indexes for IndexScanRegexp(ti1): ti1_i4 ti1_i3 ti1_i2 ti1_i1 +LOG: pg_hint_plan: +used hint: +IndexScanRegexp(ti1 ti1_i.) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +-------------------------------- + Index Scan using ti1_i2 on ti1 + Index Cond: (c2 = 1) +(2 rows) + +-- No. S-3-13-3 +/*+IndexScanRegexp(ti1 no.*_exist)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1; +LOG: available indexes for IndexScanRegexp(ti1): +LOG: pg_hint_plan: +used hint: +IndexScanRegexp(ti1 no.*_exist) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +-------------------- + Seq Scan on ti1 + Filter: (c2 = 1) +(2 rows) + +-- No. S-3-13-4 +/*+IndexScanRegexp(p1 .*pkey)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1; +LOG: available indexes for IndexScanRegexp(p1): p1_pkey +LOG: available indexes for IndexScanRegexp(p1c1): p1c1_pkey +LOG: available indexes for IndexScanRegexp(p1c2): p1c2_pkey +LOG: available indexes for IndexScanRegexp(p1c3): p1c3_pkey +LOG: pg_hint_plan: +used hint: +IndexScanRegexp(p1 .*pkey) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +--------------------------------------------------- + Result + -> Append + -> Index Scan using p1_pkey on p1 + Index Cond: (c1 = 1) + -> Index Scan using p1c1_pkey on p1c1 p1 + Index Cond: (c1 = 1) +(6 rows) + +-- No. S-3-13-5 +/*+IndexScanRegexp(p1 p1.*i)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1; +LOG: available indexes for IndexScanRegexp(p1): p1_i2 p1_i +LOG: available indexes for IndexScanRegexp(p1c1): p1c1_i p1c1_c4_expr_idx +LOG: available indexes for IndexScanRegexp(p1c2): p1c2_i p1c2_c4_expr_idx +LOG: available indexes for IndexScanRegexp(p1c3): p1c3_i p1c3_c4_expr_idx +LOG: pg_hint_plan: +used hint: +IndexScanRegexp(p1 p1.*i) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +------------------------------------------------ + Result + -> Append + -> Index Scan using p1_i on p1 + Index Cond: (c1 = 1) + -> Index Scan using p1c1_i on p1c1 p1 + Index Cond: (c1 = 1) +(6 rows) + +-- No. S-3-13-6 +/*+IndexScanRegexp(p1 no.*_exist)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1; +LOG: available indexes for IndexScanRegexp(p1): +LOG: available indexes for IndexScanRegexp(p1c1): +LOG: available indexes for IndexScanRegexp(p1c2): +LOG: available indexes for IndexScanRegexp(p1c3): +LOG: pg_hint_plan: +used hint: +IndexScanRegexp(p1 no.*_exist) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +--------------------------------- + Result + -> Append + -> Seq Scan on p1 + Filter: (c1 = 1) + -> Seq Scan on p1c1 p1 + Filter: (c1 = 1) +(6 rows) + +---- +---- No. S-3-14 message output +---- +-- No. S-3-14-1 +/*+IndexScan(ti1 ti1_i1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1; +LOG: available indexes for IndexScan(ti1): ti1_i1 +LOG: pg_hint_plan: +used hint: +IndexScan(ti1 ti1_i1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +-------------------------------- + Index Scan using ti1_i1 on ti1 + Index Cond: (c2 = 1) +(2 rows) + +-- No. S-3-14-2 +/*+IndexScan(ti1 not_exist)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1; +LOG: available indexes for IndexScan(ti1): +LOG: pg_hint_plan: +used hint: +IndexScan(ti1 not_exist) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +-------------------- + Seq Scan on ti1 + Filter: (c2 = 1) +(2 rows) + +-- No. S-3-14-3 +/*+IndexScan(ti1 ti1_i1 ti1_i2)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1; +LOG: available indexes for IndexScan(ti1): ti1_i2 ti1_i1 +LOG: pg_hint_plan: +used hint: +IndexScan(ti1 ti1_i1 ti1_i2) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +-------------------------------- + Index Scan using ti1_i2 on ti1 + Index Cond: (c2 = 1) +(2 rows) + +-- No. S-3-14-4 +/*+IndexScan(ti1 ti1_i1 not_exist)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1; +LOG: available indexes for IndexScan(ti1): ti1_i1 +LOG: pg_hint_plan: +used hint: +IndexScan(ti1 ti1_i1 not_exist) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +-------------------------------- + Index Scan using ti1_i1 on ti1 + Index Cond: (c2 = 1) +(2 rows) + +-- No. S-3-14-5 +/*+IndexScan(ti1 not_exist1 not_exist2)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1; +LOG: available indexes for IndexScan(ti1): +LOG: pg_hint_plan: +used hint: +IndexScan(ti1 not_exist1 not_exist2) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +-------------------- + Seq Scan on ti1 + Filter: (c2 = 1) +(2 rows) + diff --git a/expected/ut-S-9.2.out b/expected/ut-S-9.2.out index d675d75..6a22e75 100644 --- a/expected/ut-S-9.2.out +++ b/expected/ut-S-9.2.out @@ -5873,3 +5873,224 @@ NoIndexOnlyScan(ti1 ti1_pkey ti1_btree) Index Cond: (c1 = 1) (2 rows) +---- +---- No. S-3-13 message output +---- +EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1; + QUERY PLAN +-------------------------------- + Index Scan using ti1_i2 on ti1 + Index Cond: (c2 = 1) +(2 rows) + +-- No. S-3-13-1 +/*+IndexScanRegexp(ti1 ti1_.*_key)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1; +LOG: available indexes for IndexScanRegexp(ti1): ti1_c2_key +LOG: pg_hint_plan: +used hint: +IndexScanRegexp(ti1 ti1_.*_key) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +------------------------------------ + Index Scan using ti1_c2_key on ti1 + Index Cond: (c2 = 1) +(2 rows) + +-- No. S-3-13-2 +/*+IndexScanRegexp(ti1 ti1_i.)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1; +LOG: available indexes for IndexScanRegexp(ti1): ti1_i4 ti1_i3 ti1_i2 ti1_i1 +LOG: pg_hint_plan: +used hint: +IndexScanRegexp(ti1 ti1_i.) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +-------------------------------- + Index Scan using ti1_i2 on ti1 + Index Cond: (c2 = 1) +(2 rows) + +-- No. S-3-13-3 +/*+IndexScanRegexp(ti1 no.*_exist)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1; +LOG: available indexes for IndexScanRegexp(ti1): +LOG: pg_hint_plan: +used hint: +IndexScanRegexp(ti1 no.*_exist) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +-------------------- + Seq Scan on ti1 + Filter: (c2 = 1) +(2 rows) + +-- No. S-3-13-4 +/*+IndexScanRegexp(p1 .*pkey)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1; +LOG: available indexes for IndexScanRegexp(p1): p1_pkey +LOG: available indexes for IndexScanRegexp(p1c1): p1c1_pkey +LOG: available indexes for IndexScanRegexp(p1c2): p1c2_pkey +LOG: available indexes for IndexScanRegexp(p1c3): p1c3_pkey +LOG: pg_hint_plan: +used hint: +IndexScanRegexp(p1 .*pkey) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +--------------------------------------------------- + Result + -> Append + -> Index Scan using p1_pkey on p1 + Index Cond: (c1 = 1) + -> Index Scan using p1c1_pkey on p1c1 p1 + Index Cond: (c1 = 1) +(6 rows) + +-- No. S-3-13-5 +/*+IndexScanRegexp(p1 p1.*i)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1; +LOG: available indexes for IndexScanRegexp(p1): p1_i2 p1_i +LOG: available indexes for IndexScanRegexp(p1c1): p1c1_i p1c1_c4_expr_idx +LOG: available indexes for IndexScanRegexp(p1c2): p1c2_i p1c2_c4_expr_idx +LOG: available indexes for IndexScanRegexp(p1c3): p1c3_i p1c3_c4_expr_idx +LOG: pg_hint_plan: +used hint: +IndexScanRegexp(p1 p1.*i) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +------------------------------------------------ + Result + -> Append + -> Index Scan using p1_i on p1 + Index Cond: (c1 = 1) + -> Index Scan using p1c1_i on p1c1 p1 + Index Cond: (c1 = 1) +(6 rows) + +-- No. S-3-13-6 +/*+IndexScanRegexp(p1 no.*_exist)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1; +LOG: available indexes for IndexScanRegexp(p1): +LOG: available indexes for IndexScanRegexp(p1c1): +LOG: available indexes for IndexScanRegexp(p1c2): +LOG: available indexes for IndexScanRegexp(p1c3): +LOG: pg_hint_plan: +used hint: +IndexScanRegexp(p1 no.*_exist) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +--------------------------------- + Result + -> Append + -> Seq Scan on p1 + Filter: (c1 = 1) + -> Seq Scan on p1c1 p1 + Filter: (c1 = 1) +(6 rows) + +---- +---- No. S-3-14 message output +---- +-- No. S-3-14-1 +/*+IndexScan(ti1 ti1_i1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1; +LOG: available indexes for IndexScan(ti1): ti1_i1 +LOG: pg_hint_plan: +used hint: +IndexScan(ti1 ti1_i1) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +-------------------------------- + Index Scan using ti1_i1 on ti1 + Index Cond: (c2 = 1) +(2 rows) + +-- No. S-3-14-2 +/*+IndexScan(ti1 not_exist)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1; +LOG: available indexes for IndexScan(ti1): +LOG: pg_hint_plan: +used hint: +IndexScan(ti1 not_exist) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +-------------------- + Seq Scan on ti1 + Filter: (c2 = 1) +(2 rows) + +-- No. S-3-14-3 +/*+IndexScan(ti1 ti1_i1 ti1_i2)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1; +LOG: available indexes for IndexScan(ti1): ti1_i2 ti1_i1 +LOG: pg_hint_plan: +used hint: +IndexScan(ti1 ti1_i1 ti1_i2) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +-------------------------------- + Index Scan using ti1_i2 on ti1 + Index Cond: (c2 = 1) +(2 rows) + +-- No. S-3-14-4 +/*+IndexScan(ti1 ti1_i1 not_exist)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1; +LOG: available indexes for IndexScan(ti1): ti1_i1 +LOG: pg_hint_plan: +used hint: +IndexScan(ti1 ti1_i1 not_exist) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +-------------------------------- + Index Scan using ti1_i1 on ti1 + Index Cond: (c2 = 1) +(2 rows) + +-- No. S-3-14-5 +/*+IndexScan(ti1 not_exist1 not_exist2)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1; +LOG: available indexes for IndexScan(ti1): +LOG: pg_hint_plan: +used hint: +IndexScan(ti1 not_exist1 not_exist2) +not used hint: +duplication hint: +error hint: + + QUERY PLAN +-------------------- + Seq Scan on ti1 + Filter: (c2 = 1) +(2 rows) + diff --git a/sql/ut-S.sql b/sql/ut-S.sql index b47d473..bf75ed7 100644 --- 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; -- No. S-3-12-30 /*+NoIndexOnlyScan(ti1 ti1_pkey ti1_btree)*/ EXPLAIN (COSTS false) SELECT c1 FROM s1.ti1 WHERE c1 = 1; + + +---- +---- No. S-3-13 message output +---- +EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1; + +-- No. S-3-13-1 +/*+IndexScanRegexp(ti1 ti1_.*_key)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1; + +-- No. S-3-13-2 +/*+IndexScanRegexp(ti1 ti1_i.)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1; + +-- No. S-3-13-3 +/*+IndexScanRegexp(ti1 no.*_exist)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1; + +-- No. S-3-13-4 +/*+IndexScanRegexp(p1 .*pkey)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1; + +-- No. S-3-13-5 +/*+IndexScanRegexp(p1 p1.*i)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1; + +-- No. S-3-13-6 +/*+IndexScanRegexp(p1 no.*_exist)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1; + +---- +---- No. S-3-14 message output +---- + +-- No. S-3-14-1 +/*+IndexScan(ti1 ti1_i1)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1; +-- No. S-3-14-2 +/*+IndexScan(ti1 not_exist)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1; +-- No. S-3-14-3 +/*+IndexScan(ti1 ti1_i1 ti1_i2)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1; +-- No. S-3-14-4 +/*+IndexScan(ti1 ti1_i1 not_exist)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1; +-- No. S-3-14-5 +/*+IndexScan(ti1 not_exist1 not_exist2)*/ +EXPLAIN (COSTS false) SELECT * FROM s1.ti1 WHERE c2 = 1; -- 2.11.0