From 5f180172a644e3af2ca8c7c7346c7d4f1bd29e31 Mon Sep 17 00:00:00 2001 From: Takashi Suzuki Date: Wed, 16 Oct 2013 14:29:34 +0900 Subject: [PATCH] =?utf8?q?PG=E6=9C=AC=E4=BD=93=E3=81=AEResult=E3=83=8E?= =?utf8?q?=E3=83=BC=E3=83=89=E5=87=BA=E5=8A=9B=E6=8A=91=E5=88=B6=E3=81=AB?= =?utf8?q?=E4=BC=B4=E3=81=84=E3=80=81=E8=A9=A6=E9=A8=93=E4=BA=88=E6=B8=AC?= =?utf8?q?=E7=B5=90=E6=9E=9C=E3=83=95=E3=82=A1=E3=82=A4=E3=83=AB=E3=82=92?= =?utf8?q?=E4=BF=AE=E6=AD=A3=E3=81=97=E3=81=9F=E3=80=82?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit PG93の実行計画生成でResultノードの出力が抑制されたので、それにそっ て試験予測ファイルの内容を修正した。 --- expected/pg_hint_plan.out | 1895 ++++++++++++++++++++++----------------------- expected/ut-S.out | 338 ++++---- 2 files changed, 1086 insertions(+), 1147 deletions(-) diff --git a/expected/pg_hint_plan.out b/expected/pg_hint_plan.out index dd006b4..40eadec 100644 --- a/expected/pg_hint_plan.out +++ b/expected/pg_hint_plan.out @@ -3017,45 +3017,43 @@ error hint: -- inherite table test SET constraint_exclusion TO off; EXPLAIN (COSTS false) SELECT * FROM p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)'; - QUERY PLAN ------------------------------------------------------------------------------ - Result - -> Append - -> Seq Scan on p1 - Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) - -> Seq Scan on p1_c1 p1 - Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) - -> Seq Scan on p1_c2 p1 - Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) - -> Seq Scan on p1_c3 p1 - Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) - -> Seq Scan on p1_c4 p1 - Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) - -> Seq Scan on p1_c1_c1 p1 - Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) - -> Seq Scan on p1_c1_c2 p1 - Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) - -> Seq Scan on p1_c3_c1 p1 - Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) - -> Seq Scan on p1_c3_c2 p1 - Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) -(20 rows) + QUERY PLAN +----------------------------------------------------------------------- + Append + -> Seq Scan on p1 + Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) + -> Seq Scan on p1_c1 p1 + Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) + -> Seq Scan on p1_c2 p1 + Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) + -> Seq Scan on p1_c3 p1 + Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) + -> Seq Scan on p1_c4 p1 + Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) + -> Seq Scan on p1_c1_c1 p1 + Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) + -> Seq Scan on p1_c1_c2 p1 + Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) + -> Seq Scan on p1_c3_c1 p1 + Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) + -> Seq Scan on p1_c3_c2 p1 + Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) +(19 rows) SET constraint_exclusion TO on; EXPLAIN (COSTS false) SELECT * FROM p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)'; - QUERY PLAN ------------------------------------------------------------------------------ - Result - -> Append - -> Seq Scan on p1 - Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) - -> Seq Scan on p1_c1 p1 - Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) - -> Seq Scan on p1_c1_c1 p1 - Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) - -> Seq Scan on p1_c1_c2 p1 - Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) -(10 rows) + QUERY PLAN +----------------------------------------------------------------------- + Append + -> Seq Scan on p1 + Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) + -> Seq Scan on p1_c1 p1 + Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) + -> Seq Scan on p1_c1_c1 p1 + Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) + -> Seq Scan on p1_c1_c2 p1 + Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) +(9 rows) SET constraint_exclusion TO off; /*+SeqScan(p1)*/ @@ -3067,29 +3065,28 @@ not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------------------------ - Result - -> Append - -> Seq Scan on p1 - Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) - -> Seq Scan on p1_c1 p1 - Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) - -> Seq Scan on p1_c2 p1 - Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) - -> Seq Scan on p1_c3 p1 - Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) - -> Seq Scan on p1_c4 p1 - Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) - -> Seq Scan on p1_c1_c1 p1 - Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) - -> Seq Scan on p1_c1_c2 p1 - Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) - -> Seq Scan on p1_c3_c1 p1 - Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) - -> Seq Scan on p1_c3_c2 p1 - Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) -(20 rows) + QUERY PLAN +----------------------------------------------------------------------- + Append + -> Seq Scan on p1 + Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) + -> Seq Scan on p1_c1 p1 + Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) + -> Seq Scan on p1_c2 p1 + Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) + -> Seq Scan on p1_c3 p1 + Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) + -> Seq Scan on p1_c4 p1 + Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) + -> Seq Scan on p1_c1_c1 p1 + Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) + -> Seq Scan on p1_c1_c2 p1 + Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) + -> Seq Scan on p1_c3_c1 p1 + Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) + -> Seq Scan on p1_c3_c2 p1 + Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) +(19 rows) /*+IndexScan(p1)*/ EXPLAIN (COSTS false) SELECT * FROM p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)'; @@ -3100,38 +3097,37 @@ not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------ - Result - -> Append - -> Index Scan using p1_pkey on p1 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p1_c1_pkey on p1_c1 p1 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p1_c2_pkey on p1_c2 p1 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p1_c3_pkey on p1_c3 p1 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p1_c4_pkey on p1_c4 p1 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p1_c1_c1_pkey on p1_c1_c1 p1 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p1_c1_c2_pkey on p1_c1_c2 p1 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p1_c3_c1_pkey on p1_c3_c1 p1 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p1_c3_c2_pkey on p1_c3_c2 p1 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) -(29 rows) + QUERY PLAN +-------------------------------------------------- + Append + -> Index Scan using p1_pkey on p1 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p1_c1_pkey on p1_c1 p1 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p1_c2_pkey on p1_c2 p1 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p1_c3_pkey on p1_c3 p1 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p1_c4_pkey on p1_c4 p1 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p1_c1_c1_pkey on p1_c1_c1 p1 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p1_c1_c2_pkey on p1_c1_c2 p1 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p1_c3_c1_pkey on p1_c3_c1 p1 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p1_c3_c2_pkey on p1_c3_c2 p1 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) +(28 rows) /*+BitmapScan(p1)*/ EXPLAIN (COSTS false) SELECT * FROM p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)'; @@ -3142,56 +3138,55 @@ not used hint: duplication hint: error hint: - QUERY PLAN -------------------------------------------------------------- - Result - -> Append - -> Bitmap Heap Scan on p1 - Recheck Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Bitmap Index Scan on p1_pkey - Index Cond: ((id >= 50) AND (id <= 51)) - -> Bitmap Heap Scan on p1_c1 p1 - Recheck Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Bitmap Index Scan on p1_c1_pkey - Index Cond: ((id >= 50) AND (id <= 51)) - -> Bitmap Heap Scan on p1_c2 p1 - Recheck Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Bitmap Index Scan on p1_c2_pkey - Index Cond: ((id >= 50) AND (id <= 51)) - -> Bitmap Heap Scan on p1_c3 p1 - Recheck Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Bitmap Index Scan on p1_c3_pkey - Index Cond: ((id >= 50) AND (id <= 51)) - -> Bitmap Heap Scan on p1_c4 p1 - Recheck Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Bitmap Index Scan on p1_c4_pkey - Index Cond: ((id >= 50) AND (id <= 51)) - -> Bitmap Heap Scan on p1_c1_c1 p1 - Recheck Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Bitmap Index Scan on p1_c1_c1_pkey - Index Cond: ((id >= 50) AND (id <= 51)) - -> Bitmap Heap Scan on p1_c1_c2 p1 - Recheck Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Bitmap Index Scan on p1_c1_c2_pkey - Index Cond: ((id >= 50) AND (id <= 51)) - -> Bitmap Heap Scan on p1_c3_c1 p1 - Recheck Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Bitmap Index Scan on p1_c3_c1_pkey - Index Cond: ((id >= 50) AND (id <= 51)) - -> Bitmap Heap Scan on p1_c3_c2 p1 - Recheck Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Bitmap Index Scan on p1_c3_c2_pkey - Index Cond: ((id >= 50) AND (id <= 51)) -(47 rows) + QUERY PLAN +------------------------------------------------------- + Append + -> Bitmap Heap Scan on p1 + Recheck Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Index Scan on p1_pkey + Index Cond: ((id >= 50) AND (id <= 51)) + -> Bitmap Heap Scan on p1_c1 p1 + Recheck Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Index Scan on p1_c1_pkey + Index Cond: ((id >= 50) AND (id <= 51)) + -> Bitmap Heap Scan on p1_c2 p1 + Recheck Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Index Scan on p1_c2_pkey + Index Cond: ((id >= 50) AND (id <= 51)) + -> Bitmap Heap Scan on p1_c3 p1 + Recheck Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Index Scan on p1_c3_pkey + Index Cond: ((id >= 50) AND (id <= 51)) + -> Bitmap Heap Scan on p1_c4 p1 + Recheck Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Index Scan on p1_c4_pkey + Index Cond: ((id >= 50) AND (id <= 51)) + -> Bitmap Heap Scan on p1_c1_c1 p1 + Recheck Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Index Scan on p1_c1_c1_pkey + Index Cond: ((id >= 50) AND (id <= 51)) + -> Bitmap Heap Scan on p1_c1_c2 p1 + Recheck Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Index Scan on p1_c1_c2_pkey + Index Cond: ((id >= 50) AND (id <= 51)) + -> Bitmap Heap Scan on p1_c3_c1 p1 + Recheck Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Index Scan on p1_c3_c1_pkey + Index Cond: ((id >= 50) AND (id <= 51)) + -> Bitmap Heap Scan on p1_c3_c2 p1 + Recheck Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Index Scan on p1_c3_c2_pkey + Index Cond: ((id >= 50) AND (id <= 51)) +(46 rows) /*+TidScan(p1)*/ EXPLAIN (COSTS false) SELECT * FROM p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)'; @@ -3202,38 +3197,37 @@ not used hint: duplication hint: error hint: - QUERY PLAN ---------------------------------------------------- - Result - -> Append - -> Tid Scan on p1 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((id >= 50) AND (id <= 51)) - -> Tid Scan on p1_c1 p1 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((id >= 50) AND (id <= 51)) - -> Tid Scan on p1_c2 p1 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((id >= 50) AND (id <= 51)) - -> Tid Scan on p1_c3 p1 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((id >= 50) AND (id <= 51)) - -> Tid Scan on p1_c4 p1 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((id >= 50) AND (id <= 51)) - -> Tid Scan on p1_c1_c1 p1 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((id >= 50) AND (id <= 51)) - -> Tid Scan on p1_c1_c2 p1 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((id >= 50) AND (id <= 51)) - -> Tid Scan on p1_c3_c1 p1 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((id >= 50) AND (id <= 51)) - -> Tid Scan on p1_c3_c2 p1 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((id >= 50) AND (id <= 51)) -(29 rows) + QUERY PLAN +--------------------------------------------- + Append + -> Tid Scan on p1 + TID Cond: (ctid = '(1,1)'::tid) + Filter: ((id >= 50) AND (id <= 51)) + -> Tid Scan on p1_c1 p1 + TID Cond: (ctid = '(1,1)'::tid) + Filter: ((id >= 50) AND (id <= 51)) + -> Tid Scan on p1_c2 p1 + TID Cond: (ctid = '(1,1)'::tid) + Filter: ((id >= 50) AND (id <= 51)) + -> Tid Scan on p1_c3 p1 + TID Cond: (ctid = '(1,1)'::tid) + Filter: ((id >= 50) AND (id <= 51)) + -> Tid Scan on p1_c4 p1 + TID Cond: (ctid = '(1,1)'::tid) + Filter: ((id >= 50) AND (id <= 51)) + -> Tid Scan on p1_c1_c1 p1 + TID Cond: (ctid = '(1,1)'::tid) + Filter: ((id >= 50) AND (id <= 51)) + -> Tid Scan on p1_c1_c2 p1 + TID Cond: (ctid = '(1,1)'::tid) + Filter: ((id >= 50) AND (id <= 51)) + -> Tid Scan on p1_c3_c1 p1 + TID Cond: (ctid = '(1,1)'::tid) + Filter: ((id >= 50) AND (id <= 51)) + -> Tid Scan on p1_c3_c2 p1 + TID Cond: (ctid = '(1,1)'::tid) + Filter: ((id >= 50) AND (id <= 51)) +(28 rows) SET constraint_exclusion TO on; /*+SeqScan(p1)*/ @@ -3245,19 +3239,18 @@ not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------------------------ - Result - -> Append - -> Seq Scan on p1 - Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) - -> Seq Scan on p1_c1 p1 - Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) - -> Seq Scan on p1_c1_c1 p1 - Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) - -> Seq Scan on p1_c1_c2 p1 - Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) -(10 rows) + QUERY PLAN +----------------------------------------------------------------------- + Append + -> Seq Scan on p1 + Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) + -> Seq Scan on p1_c1 p1 + Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) + -> Seq Scan on p1_c1_c1 p1 + Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) + -> Seq Scan on p1_c1_c2 p1 + Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) +(9 rows) /*+IndexScan(p1)*/ EXPLAIN (COSTS false) SELECT * FROM p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)'; @@ -3268,23 +3261,22 @@ not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------ - Result - -> Append - -> Index Scan using p1_pkey on p1 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p1_c1_pkey on p1_c1 p1 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p1_c1_c1_pkey on p1_c1_c1 p1 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p1_c1_c2_pkey on p1_c1_c2 p1 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) -(14 rows) + QUERY PLAN +-------------------------------------------------- + Append + -> Index Scan using p1_pkey on p1 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p1_c1_pkey on p1_c1 p1 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p1_c1_c1_pkey on p1_c1_c1 p1 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p1_c1_c2_pkey on p1_c1_c2 p1 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) +(13 rows) /*+BitmapScan(p1)*/ EXPLAIN (COSTS false) SELECT * FROM p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)'; @@ -3295,31 +3287,30 @@ not used hint: duplication hint: error hint: - QUERY PLAN -------------------------------------------------------------- - Result - -> Append - -> Bitmap Heap Scan on p1 - Recheck Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Bitmap Index Scan on p1_pkey - Index Cond: ((id >= 50) AND (id <= 51)) - -> Bitmap Heap Scan on p1_c1 p1 - Recheck Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Bitmap Index Scan on p1_c1_pkey - Index Cond: ((id >= 50) AND (id <= 51)) - -> Bitmap Heap Scan on p1_c1_c1 p1 - Recheck Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Bitmap Index Scan on p1_c1_c1_pkey - Index Cond: ((id >= 50) AND (id <= 51)) - -> Bitmap Heap Scan on p1_c1_c2 p1 - Recheck Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Bitmap Index Scan on p1_c1_c2_pkey - Index Cond: ((id >= 50) AND (id <= 51)) -(22 rows) + QUERY PLAN +------------------------------------------------------- + Append + -> Bitmap Heap Scan on p1 + Recheck Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Index Scan on p1_pkey + Index Cond: ((id >= 50) AND (id <= 51)) + -> Bitmap Heap Scan on p1_c1 p1 + Recheck Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Index Scan on p1_c1_pkey + Index Cond: ((id >= 50) AND (id <= 51)) + -> Bitmap Heap Scan on p1_c1_c1 p1 + Recheck Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Index Scan on p1_c1_c1_pkey + Index Cond: ((id >= 50) AND (id <= 51)) + -> Bitmap Heap Scan on p1_c1_c2 p1 + Recheck Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Bitmap Index Scan on p1_c1_c2_pkey + Index Cond: ((id >= 50) AND (id <= 51)) +(21 rows) /*+TidScan(p1)*/ EXPLAIN (COSTS false) SELECT * FROM p1 WHERE id >= 50 AND id <= 51 AND p1.ctid = '(1,1)'; @@ -3330,23 +3321,22 @@ not used hint: duplication hint: error hint: - QUERY PLAN ---------------------------------------------------- - Result - -> Append - -> Tid Scan on p1 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((id >= 50) AND (id <= 51)) - -> Tid Scan on p1_c1 p1 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((id >= 50) AND (id <= 51)) - -> Tid Scan on p1_c1_c1 p1 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((id >= 50) AND (id <= 51)) - -> Tid Scan on p1_c1_c2 p1 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((id >= 50) AND (id <= 51)) -(14 rows) + QUERY PLAN +--------------------------------------------- + Append + -> Tid Scan on p1 + TID Cond: (ctid = '(1,1)'::tid) + Filter: ((id >= 50) AND (id <= 51)) + -> Tid Scan on p1_c1 p1 + TID Cond: (ctid = '(1,1)'::tid) + Filter: ((id >= 50) AND (id <= 51)) + -> Tid Scan on p1_c1_c1 p1 + TID Cond: (ctid = '(1,1)'::tid) + Filter: ((id >= 50) AND (id <= 51)) + -> Tid Scan on p1_c1_c2 p1 + TID Cond: (ctid = '(1,1)'::tid) + Filter: ((id >= 50) AND (id <= 51)) +(13 rows) SET constraint_exclusion TO off; EXPLAIN (COSTS false) SELECT * FROM p1, t1 WHERE p1.id >= 50 AND p1.id <= 51 AND p1.ctid = '(1,1)' AND p1.id = t1.id AND t1.id < 10; @@ -6412,21 +6402,20 @@ error hint: -- inherite table test to specify the index's name EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)'; - QUERY PLAN ------------------------------------------------------------------------------ - Result - -> Append - -> Seq Scan on p2 - Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) - -> Seq Scan on p2_c1 p2 - Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) - -> Tid Scan on p2_c1_c1 p2 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((id >= 50) AND (id <= 51)) - -> Tid Scan on p2_c1_c2 p2 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((id >= 50) AND (id <= 51)) -(12 rows) + QUERY PLAN +----------------------------------------------------------------------- + Append + -> Seq Scan on p2 + Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) + -> Seq Scan on p2_c1 p2 + Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) + -> Tid Scan on p2_c1_c1 p2 + TID Cond: (ctid = '(1,1)'::tid) + Filter: ((id >= 50) AND (id <= 51)) + -> Tid Scan on p2_c1_c2 p2 + TID Cond: (ctid = '(1,1)'::tid) + Filter: ((id >= 50) AND (id <= 51)) +(11 rows) /*+IndexScan(p2 p2_pkey)*/ EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)'; @@ -6446,23 +6435,22 @@ not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------ - Result - -> Append - -> Index Scan using p2_pkey on p2 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p2_c1_pkey on p2_c1 p2 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p2_c1_c1_pkey on p2_c1_c1 p2 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p2_c1_c2_pkey on p2_c1_c2 p2 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) -(14 rows) + QUERY PLAN +-------------------------------------------------- + Append + -> Index Scan using p2_pkey on p2 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p2_c1_pkey on p2_c1 p2 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p2_c1_c1_pkey on p2_c1_c1 p2 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p2_c1_c2_pkey on p2_c1_c2 p2 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) +(13 rows) /*+IndexScan(p2 p2_id_val_idx)*/ EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)'; @@ -6482,23 +6470,22 @@ not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------------ - Result - -> Append - -> Index Scan using p2_id_val_idx on p2 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p2_c1_id_val_idx on p2_c1 p2 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p2_c1_c1_id_val_idx on p2_c1_c1 p2 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p2_c1_c2_id_val_idx on p2_c1_c2 p2 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) -(14 rows) + QUERY PLAN +-------------------------------------------------------- + Append + -> Index Scan using p2_id_val_idx on p2 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p2_c1_id_val_idx on p2_c1 p2 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p2_c1_c1_id_val_idx on p2_c1_c1 p2 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p2_c1_c2_id_val_idx on p2_c1_c2 p2 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) +(13 rows) /*+IndexScan(p2 p2_val_id_idx)*/ EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)'; @@ -6518,53 +6505,51 @@ not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------------ - Result - -> Append - -> Index Scan using p2_val_id_idx on p2 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p2_c1_val_id_idx on p2_c1 p2 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p2_c1_c1_val_id_idx on p2_c1_c1 p2 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p2_c1_c2_val_id_idx on p2_c1_c2 p2 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) -(14 rows) + QUERY PLAN +-------------------------------------------------------- + Append + -> Index Scan using p2_val_id_idx on p2 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p2_c1_val_id_idx on p2_c1 p2 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p2_c1_c1_val_id_idx on p2_c1_c1 p2 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p2_c1_c2_val_id_idx on p2_c1_c2 p2 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) +(13 rows) EXPLAIN (COSTS false) SELECT val FROM p2 WHERE val >= '50' AND val <= '51' AND p2.ctid = '(1,1)'; - QUERY PLAN ------------------------------------------------------------------------------------------------ - Result - -> Append - -> Seq Scan on p2 - Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid)) - -> Seq Scan on p2_c1 p2 - Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid)) - -> Seq Scan on p2_c2 p2 - Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid)) - -> Seq Scan on p2_c3 p2 - Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid)) - -> Tid Scan on p2_c4 p2 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((val >= '50'::text) AND (val <= '51'::text)) - -> Tid Scan on p2_c1_c1 p2 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((val >= '50'::text) AND (val <= '51'::text)) - -> Tid Scan on p2_c1_c2 p2 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((val >= '50'::text) AND (val <= '51'::text)) - -> Tid Scan on p2_c3_c1 p2 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((val >= '50'::text) AND (val <= '51'::text)) - -> Tid Scan on p2_c3_c2 p2 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((val >= '50'::text) AND (val <= '51'::text)) -(25 rows) + QUERY PLAN +----------------------------------------------------------------------------------------- + Append + -> Seq Scan on p2 + Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid)) + -> Seq Scan on p2_c1 p2 + Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid)) + -> Seq Scan on p2_c2 p2 + Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid)) + -> Seq Scan on p2_c3 p2 + Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid)) + -> Tid Scan on p2_c4 p2 + TID Cond: (ctid = '(1,1)'::tid) + Filter: ((val >= '50'::text) AND (val <= '51'::text)) + -> Tid Scan on p2_c1_c1 p2 + TID Cond: (ctid = '(1,1)'::tid) + Filter: ((val >= '50'::text) AND (val <= '51'::text)) + -> Tid Scan on p2_c1_c2 p2 + TID Cond: (ctid = '(1,1)'::tid) + Filter: ((val >= '50'::text) AND (val <= '51'::text)) + -> Tid Scan on p2_c3_c1 p2 + TID Cond: (ctid = '(1,1)'::tid) + Filter: ((val >= '50'::text) AND (val <= '51'::text)) + -> Tid Scan on p2_c3_c2 p2 + TID Cond: (ctid = '(1,1)'::tid) + Filter: ((val >= '50'::text) AND (val <= '51'::text)) +(24 rows) /*+IndexScan(p2 p2_val)*/ EXPLAIN (COSTS false) SELECT val FROM p2 WHERE val >= '50' AND val <= '51' AND p2.ctid = '(1,1)'; @@ -6584,34 +6569,33 @@ not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------------------------------------------ - Result - -> Append - -> Seq Scan on p2 - Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid)) - -> Seq Scan on p2_c1 p2 - Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid)) - -> Seq Scan on p2_c2 p2 - Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid)) - -> Seq Scan on p2_c3 p2 - Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid)) - -> Tid Scan on p2_c4 p2 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((val >= '50'::text) AND (val <= '51'::text)) - -> Tid Scan on p2_c1_c1 p2 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((val >= '50'::text) AND (val <= '51'::text)) - -> Tid Scan on p2_c1_c2 p2 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((val >= '50'::text) AND (val <= '51'::text)) - -> Tid Scan on p2_c3_c1 p2 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((val >= '50'::text) AND (val <= '51'::text)) - -> Tid Scan on p2_c3_c2 p2 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((val >= '50'::text) AND (val <= '51'::text)) -(25 rows) + QUERY PLAN +----------------------------------------------------------------------------------------- + Append + -> Seq Scan on p2 + Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid)) + -> Seq Scan on p2_c1 p2 + Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid)) + -> Seq Scan on p2_c2 p2 + Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid)) + -> Seq Scan on p2_c3 p2 + Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid)) + -> Tid Scan on p2_c4 p2 + TID Cond: (ctid = '(1,1)'::tid) + Filter: ((val >= '50'::text) AND (val <= '51'::text)) + -> Tid Scan on p2_c1_c1 p2 + TID Cond: (ctid = '(1,1)'::tid) + Filter: ((val >= '50'::text) AND (val <= '51'::text)) + -> Tid Scan on p2_c1_c2 p2 + TID Cond: (ctid = '(1,1)'::tid) + Filter: ((val >= '50'::text) AND (val <= '51'::text)) + -> Tid Scan on p2_c3_c1 p2 + TID Cond: (ctid = '(1,1)'::tid) + Filter: ((val >= '50'::text) AND (val <= '51'::text)) + -> Tid Scan on p2_c3_c2 p2 + TID Cond: (ctid = '(1,1)'::tid) + Filter: ((val >= '50'::text) AND (val <= '51'::text)) +(24 rows) /*+IndexScan(p2 p2_pkey)*/ EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)'; @@ -6631,23 +6615,22 @@ not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------ - Result - -> Append - -> Index Scan using p2_pkey on p2 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p2_c1_pkey on p2_c1 p2 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p2_c1_c1_pkey on p2_c1_c1 p2 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p2_c1_c2_pkey on p2_c1_c2 p2 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) -(14 rows) + QUERY PLAN +-------------------------------------------------- + Append + -> Index Scan using p2_pkey on p2 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p2_c1_pkey on p2_c1 p2 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p2_c1_c1_pkey on p2_c1_c1 p2 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p2_c1_c2_pkey on p2_c1_c2 p2 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) +(13 rows) /*+IndexScan(p2 p2_id2_val)*/ EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)'; @@ -6667,23 +6650,22 @@ not used hint: duplication hint: error hint: - QUERY PLAN --------------------------------------------------------- - Result - -> Append - -> Index Scan using p2_id2_val on p2 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p2_c1_id2_val on p2_c1 p2 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Tid Scan on p2_c1_c1 p2 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((id >= 50) AND (id <= 51)) - -> Tid Scan on p2_c1_c2 p2 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((id >= 50) AND (id <= 51)) -(14 rows) + QUERY PLAN +------------------------------------------------- + Append + -> Index Scan using p2_id2_val on p2 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p2_c1_id2_val on p2_c1 p2 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Tid Scan on p2_c1_c1 p2 + TID Cond: (ctid = '(1,1)'::tid) + Filter: ((id >= 50) AND (id <= 51)) + -> Tid Scan on p2_c1_c2 p2 + TID Cond: (ctid = '(1,1)'::tid) + Filter: ((id >= 50) AND (id <= 51)) +(13 rows) /*+IndexScan(p2 p2_val2_id)*/ EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)'; @@ -6703,22 +6685,21 @@ not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------------------------ - Result - -> Append - -> Index Scan using p2_val2_id on p2 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Seq Scan on p2_c1 p2 - Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) - -> Tid Scan on p2_c1_c1 p2 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((id >= 50) AND (id <= 51)) - -> Tid Scan on p2_c1_c2 p2 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((id >= 50) AND (id <= 51)) -(13 rows) + QUERY PLAN +----------------------------------------------------------------------- + Append + -> Index Scan using p2_val2_id on p2 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Seq Scan on p2_c1 p2 + Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) + -> Tid Scan on p2_c1_c1 p2 + TID Cond: (ctid = '(1,1)'::tid) + Filter: ((id >= 50) AND (id <= 51)) + -> Tid Scan on p2_c1_c2 p2 + TID Cond: (ctid = '(1,1)'::tid) + Filter: ((id >= 50) AND (id <= 51)) +(12 rows) /*+IndexScan(p2 p2_pkey)*/ EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)'; @@ -6738,23 +6719,22 @@ not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------ - Result - -> Append - -> Index Scan using p2_pkey on p2 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p2_c1_pkey on p2_c1 p2 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p2_c1_c1_pkey on p2_c1_c1 p2 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p2_c1_c2_pkey on p2_c1_c2 p2 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) -(14 rows) + QUERY PLAN +-------------------------------------------------- + Append + -> Index Scan using p2_pkey on p2 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p2_c1_pkey on p2_c1 p2 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p2_c1_c1_pkey on p2_c1_c1 p2 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p2_c1_c2_pkey on p2_c1_c2 p2 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) +(13 rows) /*+IndexScan(p2 p2_c1_id_val_idx)*/ EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)'; @@ -6774,22 +6754,21 @@ not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------------------------ - Result - -> Append - -> Seq Scan on p2 - Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) - -> Index Scan using p2_c1_id_val_idx on p2_c1 p2 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Tid Scan on p2_c1_c1 p2 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((id >= 50) AND (id <= 51)) - -> Tid Scan on p2_c1_c2 p2 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((id >= 50) AND (id <= 51)) -(13 rows) + QUERY PLAN +----------------------------------------------------------------------- + Append + -> Seq Scan on p2 + Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) + -> Index Scan using p2_c1_id_val_idx on p2_c1 p2 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Tid Scan on p2_c1_c1 p2 + TID Cond: (ctid = '(1,1)'::tid) + Filter: ((id >= 50) AND (id <= 51)) + -> Tid Scan on p2_c1_c2 p2 + TID Cond: (ctid = '(1,1)'::tid) + Filter: ((id >= 50) AND (id <= 51)) +(12 rows) /*+IndexScan(p2 no_exist)*/ EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)'; @@ -6809,21 +6788,20 @@ not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------------------------ - Result - -> Append - -> Seq Scan on p2 - Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) - -> Seq Scan on p2_c1 p2 - Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) - -> Tid Scan on p2_c1_c1 p2 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((id >= 50) AND (id <= 51)) - -> Tid Scan on p2_c1_c2 p2 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((id >= 50) AND (id <= 51)) -(12 rows) + QUERY PLAN +----------------------------------------------------------------------- + Append + -> Seq Scan on p2 + Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) + -> Seq Scan on p2_c1 p2 + Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) + -> Tid Scan on p2_c1_c1 p2 + TID Cond: (ctid = '(1,1)'::tid) + Filter: ((id >= 50) AND (id <= 51)) + -> Tid Scan on p2_c1_c2 p2 + TID Cond: (ctid = '(1,1)'::tid) + Filter: ((id >= 50) AND (id <= 51)) +(11 rows) /*+IndexScan(p2 p2_pkey p2_c1_id_val_idx)*/ EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)'; @@ -6843,23 +6821,22 @@ not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------ - Result - -> Append - -> Index Scan using p2_pkey on p2 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p2_c1_id_val_idx on p2_c1 p2 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p2_c1_c1_pkey on p2_c1_c1 p2 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p2_c1_c2_pkey on p2_c1_c2 p2 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) -(14 rows) + QUERY PLAN +-------------------------------------------------- + Append + -> Index Scan using p2_pkey on p2 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p2_c1_id_val_idx on p2_c1 p2 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p2_c1_c1_pkey on p2_c1_c1 p2 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p2_c1_c2_pkey on p2_c1_c2 p2 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) +(13 rows) /*+IndexScan(p2 p2_pkey no_exist)*/ EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)'; @@ -6879,23 +6856,22 @@ not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------ - Result - -> Append - -> Index Scan using p2_pkey on p2 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p2_c1_pkey on p2_c1 p2 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p2_c1_c1_pkey on p2_c1_c1 p2 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p2_c1_c2_pkey on p2_c1_c2 p2 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) -(14 rows) + QUERY PLAN +-------------------------------------------------- + Append + -> Index Scan using p2_pkey on p2 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p2_c1_pkey on p2_c1 p2 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p2_c1_c1_pkey on p2_c1_c1 p2 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p2_c1_c2_pkey on p2_c1_c2 p2 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) +(13 rows) /*+IndexScan(p2 p2_c1_id_val_idx no_exist)*/ EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)'; @@ -6915,22 +6891,21 @@ not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------------------------ - Result - -> Append - -> Seq Scan on p2 - Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) - -> Index Scan using p2_c1_id_val_idx on p2_c1 p2 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Tid Scan on p2_c1_c1 p2 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((id >= 50) AND (id <= 51)) - -> Tid Scan on p2_c1_c2 p2 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((id >= 50) AND (id <= 51)) -(13 rows) + QUERY PLAN +----------------------------------------------------------------------- + Append + -> Seq Scan on p2 + Filter: ((id >= 50) AND (id <= 51) AND (ctid = '(1,1)'::tid)) + -> Index Scan using p2_c1_id_val_idx on p2_c1 p2 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Tid Scan on p2_c1_c1 p2 + TID Cond: (ctid = '(1,1)'::tid) + Filter: ((id >= 50) AND (id <= 51)) + -> Tid Scan on p2_c1_c2 p2 + TID Cond: (ctid = '(1,1)'::tid) + Filter: ((id >= 50) AND (id <= 51)) +(12 rows) /*+IndexScan(p2 p2_pkey p2_c1_id_val_idx no_exist)*/ EXPLAIN (COSTS false) SELECT * FROM p2 WHERE id >= 50 AND id <= 51 AND p2.ctid = '(1,1)'; @@ -6950,23 +6925,22 @@ not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------ - Result - -> Append - -> Index Scan using p2_pkey on p2 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p2_c1_id_val_idx on p2_c1 p2 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p2_c1_c1_pkey on p2_c1_c1 p2 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p2_c1_c2_pkey on p2_c1_c2 p2 - Index Cond: ((id >= 50) AND (id <= 51)) - Filter: (ctid = '(1,1)'::tid) -(14 rows) + QUERY PLAN +-------------------------------------------------- + Append + -> Index Scan using p2_pkey on p2 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p2_c1_id_val_idx on p2_c1 p2 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p2_c1_c1_pkey on p2_c1_c1 p2 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p2_c1_c2_pkey on p2_c1_c2 p2 + Index Cond: ((id >= 50) AND (id <= 51)) + Filter: (ctid = '(1,1)'::tid) +(13 rows) /*+IndexScan(p2 p2_val_idx)*/ EXPLAIN (COSTS false) SELECT val FROM p2 WHERE val >= '50' AND val <= '51' AND p2.ctid = '(1,1)'; @@ -6986,38 +6960,37 @@ not used hint: duplication hint: error hint: - QUERY PLAN -------------------------------------------------------------------------- - Result - -> Append - -> Index Scan using p2_val_idx on p2 - Index Cond: ((val >= '50'::text) AND (val <= '51'::text)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p2_c1_val_idx on p2_c1 p2 - Index Cond: ((val >= '50'::text) AND (val <= '51'::text)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p2_c2_val_idx on p2_c2 p2 - Index Cond: ((val >= '50'::text) AND (val <= '51'::text)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p2_c3_val_idx on p2_c3 p2 - Index Cond: ((val >= '50'::text) AND (val <= '51'::text)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p2_c4_val_idx on p2_c4 p2 - Index Cond: ((val >= '50'::text) AND (val <= '51'::text)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p2_c1_c1_val_idx on p2_c1_c1 p2 - Index Cond: ((val >= '50'::text) AND (val <= '51'::text)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p2_c1_c2_val_idx on p2_c1_c2 p2 - Index Cond: ((val >= '50'::text) AND (val <= '51'::text)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p2_c3_c1_val_idx on p2_c3_c1 p2 - Index Cond: ((val >= '50'::text) AND (val <= '51'::text)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p2_c3_c2_val_idx on p2_c3_c2 p2 - Index Cond: ((val >= '50'::text) AND (val <= '51'::text)) - Filter: (ctid = '(1,1)'::tid) -(29 rows) + QUERY PLAN +------------------------------------------------------------------- + Append + -> Index Scan using p2_val_idx on p2 + Index Cond: ((val >= '50'::text) AND (val <= '51'::text)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p2_c1_val_idx on p2_c1 p2 + Index Cond: ((val >= '50'::text) AND (val <= '51'::text)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p2_c2_val_idx on p2_c2 p2 + Index Cond: ((val >= '50'::text) AND (val <= '51'::text)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p2_c3_val_idx on p2_c3 p2 + Index Cond: ((val >= '50'::text) AND (val <= '51'::text)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p2_c4_val_idx on p2_c4 p2 + Index Cond: ((val >= '50'::text) AND (val <= '51'::text)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p2_c1_c1_val_idx on p2_c1_c1 p2 + Index Cond: ((val >= '50'::text) AND (val <= '51'::text)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p2_c1_c2_val_idx on p2_c1_c2 p2 + Index Cond: ((val >= '50'::text) AND (val <= '51'::text)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p2_c3_c1_val_idx on p2_c3_c1 p2 + Index Cond: ((val >= '50'::text) AND (val <= '51'::text)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p2_c3_c2_val_idx on p2_c3_c2 p2 + Index Cond: ((val >= '50'::text) AND (val <= '51'::text)) + Filter: (ctid = '(1,1)'::tid) +(28 rows) /*+IndexScan(p2 p2_expr)*/ EXPLAIN (COSTS false) SELECT val FROM p2 WHERE val >= '50' AND val <= '51' AND p2.ctid = '(1,1)'; @@ -7037,34 +7010,33 @@ not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------------------------------------------ - Result - -> Append - -> Seq Scan on p2 - Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid)) - -> Seq Scan on p2_c1 p2 - Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid)) - -> Seq Scan on p2_c2 p2 - Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid)) - -> Seq Scan on p2_c3 p2 - Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid)) - -> Tid Scan on p2_c4 p2 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((val >= '50'::text) AND (val <= '51'::text)) - -> Tid Scan on p2_c1_c1 p2 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((val >= '50'::text) AND (val <= '51'::text)) - -> Tid Scan on p2_c1_c2 p2 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((val >= '50'::text) AND (val <= '51'::text)) - -> Tid Scan on p2_c3_c1 p2 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((val >= '50'::text) AND (val <= '51'::text)) - -> Tid Scan on p2_c3_c2 p2 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((val >= '50'::text) AND (val <= '51'::text)) -(25 rows) + QUERY PLAN +----------------------------------------------------------------------------------------- + Append + -> Seq Scan on p2 + Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid)) + -> Seq Scan on p2_c1 p2 + Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid)) + -> Seq Scan on p2_c2 p2 + Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid)) + -> Seq Scan on p2_c3 p2 + Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid)) + -> Tid Scan on p2_c4 p2 + TID Cond: (ctid = '(1,1)'::tid) + Filter: ((val >= '50'::text) AND (val <= '51'::text)) + -> Tid Scan on p2_c1_c1 p2 + TID Cond: (ctid = '(1,1)'::tid) + Filter: ((val >= '50'::text) AND (val <= '51'::text)) + -> Tid Scan on p2_c1_c2 p2 + TID Cond: (ctid = '(1,1)'::tid) + Filter: ((val >= '50'::text) AND (val <= '51'::text)) + -> Tid Scan on p2_c3_c1 p2 + TID Cond: (ctid = '(1,1)'::tid) + Filter: ((val >= '50'::text) AND (val <= '51'::text)) + -> Tid Scan on p2_c3_c2 p2 + TID Cond: (ctid = '(1,1)'::tid) + Filter: ((val >= '50'::text) AND (val <= '51'::text)) +(24 rows) /*+IndexScan(p2 p2_val_idx6)*/ EXPLAIN (COSTS false) SELECT val FROM p2 WHERE val >= '50' AND val <= '51' AND p2.ctid = '(1,1)'; @@ -7084,34 +7056,33 @@ not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------------------------------------------ - Result - -> Append - -> Seq Scan on p2 - Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid)) - -> Seq Scan on p2_c1 p2 - Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid)) - -> Seq Scan on p2_c2 p2 - Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid)) - -> Seq Scan on p2_c3 p2 - Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid)) - -> Tid Scan on p2_c4 p2 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((val >= '50'::text) AND (val <= '51'::text)) - -> Tid Scan on p2_c1_c1 p2 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((val >= '50'::text) AND (val <= '51'::text)) - -> Tid Scan on p2_c1_c2 p2 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((val >= '50'::text) AND (val <= '51'::text)) - -> Tid Scan on p2_c3_c1 p2 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((val >= '50'::text) AND (val <= '51'::text)) - -> Tid Scan on p2_c3_c2 p2 - TID Cond: (ctid = '(1,1)'::tid) - Filter: ((val >= '50'::text) AND (val <= '51'::text)) -(25 rows) + QUERY PLAN +----------------------------------------------------------------------------------------- + Append + -> Seq Scan on p2 + Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid)) + -> Seq Scan on p2_c1 p2 + Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid)) + -> Seq Scan on p2_c2 p2 + Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid)) + -> Seq Scan on p2_c3 p2 + Filter: ((val >= '50'::text) AND (val <= '51'::text) AND (ctid = '(1,1)'::tid)) + -> Tid Scan on p2_c4 p2 + TID Cond: (ctid = '(1,1)'::tid) + Filter: ((val >= '50'::text) AND (val <= '51'::text)) + -> Tid Scan on p2_c1_c1 p2 + TID Cond: (ctid = '(1,1)'::tid) + Filter: ((val >= '50'::text) AND (val <= '51'::text)) + -> Tid Scan on p2_c1_c2 p2 + TID Cond: (ctid = '(1,1)'::tid) + Filter: ((val >= '50'::text) AND (val <= '51'::text)) + -> Tid Scan on p2_c3_c1 p2 + TID Cond: (ctid = '(1,1)'::tid) + Filter: ((val >= '50'::text) AND (val <= '51'::text)) + -> Tid Scan on p2_c3_c2 p2 + TID Cond: (ctid = '(1,1)'::tid) + Filter: ((val >= '50'::text) AND (val <= '51'::text)) +(24 rows) /*+IndexScan(p2 p2_val_idx p2_val_idx6)*/ EXPLAIN (COSTS false) SELECT val FROM p2 WHERE val >= '50' AND val <= '51' AND p2.ctid = '(1,1)'; @@ -7131,38 +7102,37 @@ not used hint: duplication hint: error hint: - QUERY PLAN -------------------------------------------------------------------------- - Result - -> Append - -> Index Scan using p2_val_idx on p2 - Index Cond: ((val >= '50'::text) AND (val <= '51'::text)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p2_c1_val_idx on p2_c1 p2 - Index Cond: ((val >= '50'::text) AND (val <= '51'::text)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p2_c2_val_idx on p2_c2 p2 - Index Cond: ((val >= '50'::text) AND (val <= '51'::text)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p2_c3_val_idx on p2_c3 p2 - Index Cond: ((val >= '50'::text) AND (val <= '51'::text)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p2_c4_val_idx on p2_c4 p2 - Index Cond: ((val >= '50'::text) AND (val <= '51'::text)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p2_c1_c1_val_idx on p2_c1_c1 p2 - Index Cond: ((val >= '50'::text) AND (val <= '51'::text)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p2_c1_c2_val_idx on p2_c1_c2 p2 - Index Cond: ((val >= '50'::text) AND (val <= '51'::text)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p2_c3_c1_val_idx on p2_c3_c1 p2 - Index Cond: ((val >= '50'::text) AND (val <= '51'::text)) - Filter: (ctid = '(1,1)'::tid) - -> Index Scan using p2_c3_c2_val_idx on p2_c3_c2 p2 - Index Cond: ((val >= '50'::text) AND (val <= '51'::text)) - Filter: (ctid = '(1,1)'::tid) -(29 rows) + QUERY PLAN +------------------------------------------------------------------- + Append + -> Index Scan using p2_val_idx on p2 + Index Cond: ((val >= '50'::text) AND (val <= '51'::text)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p2_c1_val_idx on p2_c1 p2 + Index Cond: ((val >= '50'::text) AND (val <= '51'::text)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p2_c2_val_idx on p2_c2 p2 + Index Cond: ((val >= '50'::text) AND (val <= '51'::text)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p2_c3_val_idx on p2_c3 p2 + Index Cond: ((val >= '50'::text) AND (val <= '51'::text)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p2_c4_val_idx on p2_c4 p2 + Index Cond: ((val >= '50'::text) AND (val <= '51'::text)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p2_c1_c1_val_idx on p2_c1_c1 p2 + Index Cond: ((val >= '50'::text) AND (val <= '51'::text)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p2_c1_c2_val_idx on p2_c1_c2 p2 + Index Cond: ((val >= '50'::text) AND (val <= '51'::text)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p2_c3_c1_val_idx on p2_c3_c1 p2 + Index Cond: ((val >= '50'::text) AND (val <= '51'::text)) + Filter: (ctid = '(1,1)'::tid) + -> Index Scan using p2_c3_c2_val_idx on p2_c3_c2 p2 + Index Cond: ((val >= '50'::text) AND (val <= '51'::text)) + Filter: (ctid = '(1,1)'::tid) +(28 rows) -- regular expression -- ordinary table @@ -7419,29 +7389,28 @@ error hint: -- Inheritance EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1; - QUERY PLAN -------------------------------------- - Result - -> Append - -> Seq Scan on p1 - Filter: (val = 1) - -> Seq Scan on p1_c1 p1 - Filter: (val = 1) - -> Seq Scan on p1_c2 p1 - Filter: (val = 1) - -> Seq Scan on p1_c3 p1 - Filter: (val = 1) - -> Seq Scan on p1_c4 p1 - Filter: (val = 1) - -> Seq Scan on p1_c1_c1 p1 - Filter: (val = 1) - -> Seq Scan on p1_c1_c2 p1 - Filter: (val = 1) - -> Seq Scan on p1_c3_c1 p1 - Filter: (val = 1) - -> Seq Scan on p1_c3_c2 p1 - Filter: (val = 1) -(20 rows) + QUERY PLAN +---------------------------- + Append + -> Seq Scan on p1 + Filter: (val = 1) + -> Seq Scan on p1_c1 p1 + Filter: (val = 1) + -> Seq Scan on p1_c2 p1 + Filter: (val = 1) + -> Seq Scan on p1_c3 p1 + Filter: (val = 1) + -> Seq Scan on p1_c4 p1 + Filter: (val = 1) + -> Seq Scan on p1_c1_c1 p1 + Filter: (val = 1) + -> Seq Scan on p1_c1_c2 p1 + Filter: (val = 1) + -> Seq Scan on p1_c3_c1 p1 + Filter: (val = 1) + -> Seq Scan on p1_c3_c2 p1 + Filter: (val = 1) +(19 rows) /*+ IndexScanRegexp(p1 p1_.*[^0-9]$)*/ EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1; @@ -7461,29 +7430,28 @@ not used hint: duplication hint: error hint: - QUERY PLAN -------------------------------------- - Result - -> Append - -> Seq Scan on p1 - Filter: (val = 1) - -> Seq Scan on p1_c1 p1 - Filter: (val = 1) - -> Seq Scan on p1_c2 p1 - Filter: (val = 1) - -> Seq Scan on p1_c3 p1 - Filter: (val = 1) - -> Seq Scan on p1_c4 p1 - Filter: (val = 1) - -> Seq Scan on p1_c1_c1 p1 - Filter: (val = 1) - -> Seq Scan on p1_c1_c2 p1 - Filter: (val = 1) - -> Seq Scan on p1_c3_c1 p1 - Filter: (val = 1) - -> Seq Scan on p1_c3_c2 p1 - Filter: (val = 1) -(20 rows) + QUERY PLAN +---------------------------- + Append + -> Seq Scan on p1 + Filter: (val = 1) + -> Seq Scan on p1_c1 p1 + Filter: (val = 1) + -> Seq Scan on p1_c2 p1 + Filter: (val = 1) + -> Seq Scan on p1_c3 p1 + Filter: (val = 1) + -> Seq Scan on p1_c4 p1 + Filter: (val = 1) + -> Seq Scan on p1_c1_c1 p1 + Filter: (val = 1) + -> Seq Scan on p1_c1_c2 p1 + Filter: (val = 1) + -> Seq Scan on p1_c3_c1 p1 + Filter: (val = 1) + -> Seq Scan on p1_c3_c2 p1 + Filter: (val = 1) +(19 rows) /*+ IndexScanRegexp(p1 p1_.*val2.*)*/ EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1; @@ -7503,29 +7471,28 @@ not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------ - Result - -> Append - -> Index Scan using p1_val2 on p1 - Index Cond: (val = 1) - -> Index Scan using p1_c1_val2 on p1_c1 p1 - Index Cond: (val = 1) - -> Index Scan using p1_c2_val2 on p1_c2 p1 - Index Cond: (val = 1) - -> Index Scan using p1_c3_val2 on p1_c3 p1 - Index Cond: (val = 1) - -> Index Scan using p1_c4_val2 on p1_c4 p1 - Index Cond: (val = 1) - -> Index Scan using p1_c1_c1_val2 on p1_c1_c1 p1 - Index Cond: (val = 1) - -> Index Scan using p1_c1_c2_val2 on p1_c1_c2 p1 - Index Cond: (val = 1) - -> Index Scan using p1_c3_c1_val2 on p1_c3_c1 p1 - Index Cond: (val = 1) - -> Index Scan using p1_c3_c2_val2 on p1_c3_c2 p1 - Index Cond: (val = 1) -(20 rows) + QUERY PLAN +-------------------------------------------------- + Append + -> Index Scan using p1_val2 on p1 + Index Cond: (val = 1) + -> Index Scan using p1_c1_val2 on p1_c1 p1 + Index Cond: (val = 1) + -> Index Scan using p1_c2_val2 on p1_c2 p1 + Index Cond: (val = 1) + -> Index Scan using p1_c3_val2 on p1_c3 p1 + Index Cond: (val = 1) + -> Index Scan using p1_c4_val2 on p1_c4 p1 + Index Cond: (val = 1) + -> Index Scan using p1_c1_c1_val2 on p1_c1_c1 p1 + Index Cond: (val = 1) + -> Index Scan using p1_c1_c2_val2 on p1_c1_c2 p1 + Index Cond: (val = 1) + -> Index Scan using p1_c3_c1_val2 on p1_c3_c1 p1 + Index Cond: (val = 1) + -> Index Scan using p1_c3_c2_val2 on p1_c3_c2 p1 + Index Cond: (val = 1) +(19 rows) /*+ IndexScanRegexp(p1 p1[^_].*)*/ EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1; @@ -7545,29 +7512,28 @@ not used hint: duplication hint: error hint: - QUERY PLAN -------------------------------------- - Result - -> Append - -> Seq Scan on p1 - Filter: (val = 1) - -> Seq Scan on p1_c1 p1 - Filter: (val = 1) - -> Seq Scan on p1_c2 p1 - Filter: (val = 1) - -> Seq Scan on p1_c3 p1 - Filter: (val = 1) - -> Seq Scan on p1_c4 p1 - Filter: (val = 1) - -> Seq Scan on p1_c1_c1 p1 - Filter: (val = 1) - -> Seq Scan on p1_c1_c2 p1 - Filter: (val = 1) - -> Seq Scan on p1_c3_c1 p1 - Filter: (val = 1) - -> Seq Scan on p1_c3_c2 p1 - Filter: (val = 1) -(20 rows) + QUERY PLAN +---------------------------- + Append + -> Seq Scan on p1 + Filter: (val = 1) + -> Seq Scan on p1_c1 p1 + Filter: (val = 1) + -> Seq Scan on p1_c2 p1 + Filter: (val = 1) + -> Seq Scan on p1_c3 p1 + Filter: (val = 1) + -> Seq Scan on p1_c4 p1 + Filter: (val = 1) + -> Seq Scan on p1_c1_c1 p1 + Filter: (val = 1) + -> Seq Scan on p1_c1_c2 p1 + Filter: (val = 1) + -> Seq Scan on p1_c3_c1 p1 + Filter: (val = 1) + -> Seq Scan on p1_c3_c2 p1 + Filter: (val = 1) +(19 rows) /*+ IndexScan(p1 p1_.*val2.*)*/ EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1; @@ -7587,29 +7553,28 @@ not used hint: duplication hint: error hint: - QUERY PLAN -------------------------------------- - Result - -> Append - -> Seq Scan on p1 - Filter: (val = 1) - -> Seq Scan on p1_c1 p1 - Filter: (val = 1) - -> Seq Scan on p1_c2 p1 - Filter: (val = 1) - -> Seq Scan on p1_c3 p1 - Filter: (val = 1) - -> Seq Scan on p1_c4 p1 - Filter: (val = 1) - -> Seq Scan on p1_c1_c1 p1 - Filter: (val = 1) - -> Seq Scan on p1_c1_c2 p1 - Filter: (val = 1) - -> Seq Scan on p1_c3_c1 p1 - Filter: (val = 1) - -> Seq Scan on p1_c3_c2 p1 - Filter: (val = 1) -(20 rows) + QUERY PLAN +---------------------------- + Append + -> Seq Scan on p1 + Filter: (val = 1) + -> Seq Scan on p1_c1 p1 + Filter: (val = 1) + -> Seq Scan on p1_c2 p1 + Filter: (val = 1) + -> Seq Scan on p1_c3 p1 + Filter: (val = 1) + -> Seq Scan on p1_c4 p1 + Filter: (val = 1) + -> Seq Scan on p1_c1_c1 p1 + Filter: (val = 1) + -> Seq Scan on p1_c1_c2 p1 + Filter: (val = 1) + -> Seq Scan on p1_c3_c1 p1 + Filter: (val = 1) + -> Seq Scan on p1_c3_c2 p1 + Filter: (val = 1) +(19 rows) /*+ IndexOnlyScanRegexp(p1 p1_.*[^0-9]$)*/ EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1; @@ -7629,29 +7594,28 @@ not used hint: duplication hint: error hint: - QUERY PLAN -------------------------------------- - Result - -> Append - -> Seq Scan on p1 - Filter: (val = 1) - -> Seq Scan on p1_c1 p1 - Filter: (val = 1) - -> Seq Scan on p1_c2 p1 - Filter: (val = 1) - -> Seq Scan on p1_c3 p1 - Filter: (val = 1) - -> Seq Scan on p1_c4 p1 - Filter: (val = 1) - -> Seq Scan on p1_c1_c1 p1 - Filter: (val = 1) - -> Seq Scan on p1_c1_c2 p1 - Filter: (val = 1) - -> Seq Scan on p1_c3_c1 p1 - Filter: (val = 1) - -> Seq Scan on p1_c3_c2 p1 - Filter: (val = 1) -(20 rows) + QUERY PLAN +---------------------------- + Append + -> Seq Scan on p1 + Filter: (val = 1) + -> Seq Scan on p1_c1 p1 + Filter: (val = 1) + -> Seq Scan on p1_c2 p1 + Filter: (val = 1) + -> Seq Scan on p1_c3 p1 + Filter: (val = 1) + -> Seq Scan on p1_c4 p1 + Filter: (val = 1) + -> Seq Scan on p1_c1_c1 p1 + Filter: (val = 1) + -> Seq Scan on p1_c1_c2 p1 + Filter: (val = 1) + -> Seq Scan on p1_c3_c1 p1 + Filter: (val = 1) + -> Seq Scan on p1_c3_c2 p1 + Filter: (val = 1) +(19 rows) /*+ IndexOnlyScanRegexp(p1 p1_.*val2.*)*/ EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1; @@ -7671,29 +7635,28 @@ not used hint: duplication hint: error hint: - QUERY PLAN ----------------------------------------------------------------- - Result - -> Append - -> Index Only Scan using p1_val2 on p1 - Index Cond: (val = 1) - -> Index Only Scan using p1_c1_val2 on p1_c1 p1 - Index Cond: (val = 1) - -> Index Only Scan using p1_c2_val2 on p1_c2 p1 - Index Cond: (val = 1) - -> Index Only Scan using p1_c3_val2 on p1_c3 p1 - Index Cond: (val = 1) - -> Index Only Scan using p1_c4_val2 on p1_c4 p1 - Index Cond: (val = 1) - -> Index Only Scan using p1_c1_c1_val2 on p1_c1_c1 p1 - Index Cond: (val = 1) - -> Index Only Scan using p1_c1_c2_val2 on p1_c1_c2 p1 - Index Cond: (val = 1) - -> Index Only Scan using p1_c3_c1_val2 on p1_c3_c1 p1 - Index Cond: (val = 1) - -> Index Only Scan using p1_c3_c2_val2 on p1_c3_c2 p1 - Index Cond: (val = 1) -(20 rows) + QUERY PLAN +------------------------------------------------------- + Append + -> Index Only Scan using p1_val2 on p1 + Index Cond: (val = 1) + -> Index Only Scan using p1_c1_val2 on p1_c1 p1 + Index Cond: (val = 1) + -> Index Only Scan using p1_c2_val2 on p1_c2 p1 + Index Cond: (val = 1) + -> Index Only Scan using p1_c3_val2 on p1_c3 p1 + Index Cond: (val = 1) + -> Index Only Scan using p1_c4_val2 on p1_c4 p1 + Index Cond: (val = 1) + -> Index Only Scan using p1_c1_c1_val2 on p1_c1_c1 p1 + Index Cond: (val = 1) + -> Index Only Scan using p1_c1_c2_val2 on p1_c1_c2 p1 + Index Cond: (val = 1) + -> Index Only Scan using p1_c3_c1_val2 on p1_c3_c1 p1 + Index Cond: (val = 1) + -> Index Only Scan using p1_c3_c2_val2 on p1_c3_c2 p1 + Index Cond: (val = 1) +(19 rows) /*+ IndexOnlyScanRegexp(p1 p1[^_].*)*/ EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1; @@ -7713,29 +7676,28 @@ not used hint: duplication hint: error hint: - QUERY PLAN -------------------------------------- - Result - -> Append - -> Seq Scan on p1 - Filter: (val = 1) - -> Seq Scan on p1_c1 p1 - Filter: (val = 1) - -> Seq Scan on p1_c2 p1 - Filter: (val = 1) - -> Seq Scan on p1_c3 p1 - Filter: (val = 1) - -> Seq Scan on p1_c4 p1 - Filter: (val = 1) - -> Seq Scan on p1_c1_c1 p1 - Filter: (val = 1) - -> Seq Scan on p1_c1_c2 p1 - Filter: (val = 1) - -> Seq Scan on p1_c3_c1 p1 - Filter: (val = 1) - -> Seq Scan on p1_c3_c2 p1 - Filter: (val = 1) -(20 rows) + QUERY PLAN +---------------------------- + Append + -> Seq Scan on p1 + Filter: (val = 1) + -> Seq Scan on p1_c1 p1 + Filter: (val = 1) + -> Seq Scan on p1_c2 p1 + Filter: (val = 1) + -> Seq Scan on p1_c3 p1 + Filter: (val = 1) + -> Seq Scan on p1_c4 p1 + Filter: (val = 1) + -> Seq Scan on p1_c1_c1 p1 + Filter: (val = 1) + -> Seq Scan on p1_c1_c2 p1 + Filter: (val = 1) + -> Seq Scan on p1_c3_c1 p1 + Filter: (val = 1) + -> Seq Scan on p1_c3_c2 p1 + Filter: (val = 1) +(19 rows) /*+ IndexOnlyScan(p1 p1_.*val2.*)*/ EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1; @@ -7755,29 +7717,28 @@ not used hint: duplication hint: error hint: - QUERY PLAN -------------------------------------- - Result - -> Append - -> Seq Scan on p1 - Filter: (val = 1) - -> Seq Scan on p1_c1 p1 - Filter: (val = 1) - -> Seq Scan on p1_c2 p1 - Filter: (val = 1) - -> Seq Scan on p1_c3 p1 - Filter: (val = 1) - -> Seq Scan on p1_c4 p1 - Filter: (val = 1) - -> Seq Scan on p1_c1_c1 p1 - Filter: (val = 1) - -> Seq Scan on p1_c1_c2 p1 - Filter: (val = 1) - -> Seq Scan on p1_c3_c1 p1 - Filter: (val = 1) - -> Seq Scan on p1_c3_c2 p1 - Filter: (val = 1) -(20 rows) + QUERY PLAN +---------------------------- + Append + -> Seq Scan on p1 + Filter: (val = 1) + -> Seq Scan on p1_c1 p1 + Filter: (val = 1) + -> Seq Scan on p1_c2 p1 + Filter: (val = 1) + -> Seq Scan on p1_c3 p1 + Filter: (val = 1) + -> Seq Scan on p1_c4 p1 + Filter: (val = 1) + -> Seq Scan on p1_c1_c1 p1 + Filter: (val = 1) + -> Seq Scan on p1_c1_c2 p1 + Filter: (val = 1) + -> Seq Scan on p1_c3_c1 p1 + Filter: (val = 1) + -> Seq Scan on p1_c3_c2 p1 + Filter: (val = 1) +(19 rows) /*+ BitmapScanRegexp(p1 p1_.*[^0-9]$)*/ EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1; @@ -7797,29 +7758,28 @@ not used hint: duplication hint: error hint: - QUERY PLAN -------------------------------------- - Result - -> Append - -> Seq Scan on p1 - Filter: (val = 1) - -> Seq Scan on p1_c1 p1 - Filter: (val = 1) - -> Seq Scan on p1_c2 p1 - Filter: (val = 1) - -> Seq Scan on p1_c3 p1 - Filter: (val = 1) - -> Seq Scan on p1_c4 p1 - Filter: (val = 1) - -> Seq Scan on p1_c1_c1 p1 - Filter: (val = 1) - -> Seq Scan on p1_c1_c2 p1 - Filter: (val = 1) - -> Seq Scan on p1_c3_c1 p1 - Filter: (val = 1) - -> Seq Scan on p1_c3_c2 p1 - Filter: (val = 1) -(20 rows) + QUERY PLAN +---------------------------- + Append + -> Seq Scan on p1 + Filter: (val = 1) + -> Seq Scan on p1_c1 p1 + Filter: (val = 1) + -> Seq Scan on p1_c2 p1 + Filter: (val = 1) + -> Seq Scan on p1_c3 p1 + Filter: (val = 1) + -> Seq Scan on p1_c4 p1 + Filter: (val = 1) + -> Seq Scan on p1_c1_c1 p1 + Filter: (val = 1) + -> Seq Scan on p1_c1_c2 p1 + Filter: (val = 1) + -> Seq Scan on p1_c3_c1 p1 + Filter: (val = 1) + -> Seq Scan on p1_c3_c2 p1 + Filter: (val = 1) +(19 rows) /*+ BitmapScanRegexp(p1 p1_.*val2.*)*/ EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1; @@ -7839,47 +7799,46 @@ not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------------------------- - Result - -> Append - -> Bitmap Heap Scan on p1 - Recheck Cond: (val = 1) - -> Bitmap Index Scan on p1_val2 - Index Cond: (val = 1) - -> Bitmap Heap Scan on p1_c1 p1 - Recheck Cond: (val = 1) - -> Bitmap Index Scan on p1_c1_val2 - Index Cond: (val = 1) - -> Bitmap Heap Scan on p1_c2 p1 - Recheck Cond: (val = 1) - -> Bitmap Index Scan on p1_c2_val2 - Index Cond: (val = 1) - -> Bitmap Heap Scan on p1_c3 p1 - Recheck Cond: (val = 1) - -> Bitmap Index Scan on p1_c3_val2 - Index Cond: (val = 1) - -> Bitmap Heap Scan on p1_c4 p1 - Recheck Cond: (val = 1) - -> Bitmap Index Scan on p1_c4_val2 - Index Cond: (val = 1) - -> Bitmap Heap Scan on p1_c1_c1 p1 - Recheck Cond: (val = 1) - -> Bitmap Index Scan on p1_c1_c1_val2 - Index Cond: (val = 1) - -> Bitmap Heap Scan on p1_c1_c2 p1 - Recheck Cond: (val = 1) - -> Bitmap Index Scan on p1_c1_c2_val2 - Index Cond: (val = 1) - -> Bitmap Heap Scan on p1_c3_c1 p1 - Recheck Cond: (val = 1) - -> Bitmap Index Scan on p1_c3_c1_val2 - Index Cond: (val = 1) - -> Bitmap Heap Scan on p1_c3_c2 p1 - Recheck Cond: (val = 1) - -> Bitmap Index Scan on p1_c3_c2_val2 - Index Cond: (val = 1) -(38 rows) + QUERY PLAN +------------------------------------------------ + Append + -> Bitmap Heap Scan on p1 + Recheck Cond: (val = 1) + -> Bitmap Index Scan on p1_val2 + Index Cond: (val = 1) + -> Bitmap Heap Scan on p1_c1 p1 + Recheck Cond: (val = 1) + -> Bitmap Index Scan on p1_c1_val2 + Index Cond: (val = 1) + -> Bitmap Heap Scan on p1_c2 p1 + Recheck Cond: (val = 1) + -> Bitmap Index Scan on p1_c2_val2 + Index Cond: (val = 1) + -> Bitmap Heap Scan on p1_c3 p1 + Recheck Cond: (val = 1) + -> Bitmap Index Scan on p1_c3_val2 + Index Cond: (val = 1) + -> Bitmap Heap Scan on p1_c4 p1 + Recheck Cond: (val = 1) + -> Bitmap Index Scan on p1_c4_val2 + Index Cond: (val = 1) + -> Bitmap Heap Scan on p1_c1_c1 p1 + Recheck Cond: (val = 1) + -> Bitmap Index Scan on p1_c1_c1_val2 + Index Cond: (val = 1) + -> Bitmap Heap Scan on p1_c1_c2 p1 + Recheck Cond: (val = 1) + -> Bitmap Index Scan on p1_c1_c2_val2 + Index Cond: (val = 1) + -> Bitmap Heap Scan on p1_c3_c1 p1 + Recheck Cond: (val = 1) + -> Bitmap Index Scan on p1_c3_c1_val2 + Index Cond: (val = 1) + -> Bitmap Heap Scan on p1_c3_c2 p1 + Recheck Cond: (val = 1) + -> Bitmap Index Scan on p1_c3_c2_val2 + Index Cond: (val = 1) +(37 rows) /*+ BitmapScanRegexp(p1 p1[^_].*)*/ EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1; @@ -7899,29 +7858,28 @@ not used hint: duplication hint: error hint: - QUERY PLAN -------------------------------------- - Result - -> Append - -> Seq Scan on p1 - Filter: (val = 1) - -> Seq Scan on p1_c1 p1 - Filter: (val = 1) - -> Seq Scan on p1_c2 p1 - Filter: (val = 1) - -> Seq Scan on p1_c3 p1 - Filter: (val = 1) - -> Seq Scan on p1_c4 p1 - Filter: (val = 1) - -> Seq Scan on p1_c1_c1 p1 - Filter: (val = 1) - -> Seq Scan on p1_c1_c2 p1 - Filter: (val = 1) - -> Seq Scan on p1_c3_c1 p1 - Filter: (val = 1) - -> Seq Scan on p1_c3_c2 p1 - Filter: (val = 1) -(20 rows) + QUERY PLAN +---------------------------- + Append + -> Seq Scan on p1 + Filter: (val = 1) + -> Seq Scan on p1_c1 p1 + Filter: (val = 1) + -> Seq Scan on p1_c2 p1 + Filter: (val = 1) + -> Seq Scan on p1_c3 p1 + Filter: (val = 1) + -> Seq Scan on p1_c4 p1 + Filter: (val = 1) + -> Seq Scan on p1_c1_c1 p1 + Filter: (val = 1) + -> Seq Scan on p1_c1_c2 p1 + Filter: (val = 1) + -> Seq Scan on p1_c3_c1 p1 + Filter: (val = 1) + -> Seq Scan on p1_c3_c2 p1 + Filter: (val = 1) +(19 rows) /*+ BitmapScan(p1 p1_.*val2.*)*/ EXPLAIN (COSTS false) SELECT val FROM p1 WHERE val = 1; @@ -7941,29 +7899,28 @@ not used hint: duplication hint: error hint: - QUERY PLAN -------------------------------------- - Result - -> Append - -> Seq Scan on p1 - Filter: (val = 1) - -> Seq Scan on p1_c1 p1 - Filter: (val = 1) - -> Seq Scan on p1_c2 p1 - Filter: (val = 1) - -> Seq Scan on p1_c3 p1 - Filter: (val = 1) - -> Seq Scan on p1_c4 p1 - Filter: (val = 1) - -> Seq Scan on p1_c1_c1 p1 - Filter: (val = 1) - -> Seq Scan on p1_c1_c2 p1 - Filter: (val = 1) - -> Seq Scan on p1_c3_c1 p1 - Filter: (val = 1) - -> Seq Scan on p1_c3_c2 p1 - Filter: (val = 1) -(20 rows) + QUERY PLAN +---------------------------- + Append + -> Seq Scan on p1 + Filter: (val = 1) + -> Seq Scan on p1_c1 p1 + Filter: (val = 1) + -> Seq Scan on p1_c2 p1 + Filter: (val = 1) + -> Seq Scan on p1_c3 p1 + Filter: (val = 1) + -> Seq Scan on p1_c4 p1 + Filter: (val = 1) + -> Seq Scan on p1_c1_c1 p1 + Filter: (val = 1) + -> Seq Scan on p1_c1_c2 p1 + Filter: (val = 1) + -> Seq Scan on p1_c3_c1 p1 + Filter: (val = 1) + -> Seq Scan on p1_c3_c2 p1 + Filter: (val = 1) +(19 rows) -- search from hint table INSERT INTO hint_plan.hints (norm_query_string, application_name, hints) VALUES ('EXPLAIN (COSTS false) SELECT * FROM t1 WHERE t1.id = ?;', '', 'SeqScan(t1)'); diff --git a/expected/ut-S.out b/expected/ut-S.out index 417de3c..7128658 100644 --- a/expected/ut-S.out +++ b/expected/ut-S.out @@ -366,15 +366,14 @@ error hint: -- No. S-1-5-2 EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE p1.c1 = 1; - QUERY PLAN ---------------------------------- - Result - -> Append - -> Seq Scan on p1 - Filter: (c1 = 1) - -> Seq Scan on p1c1 p1 - Filter: (c1 = 1) -(6 rows) + QUERY PLAN +-------------------------- + Append + -> Seq Scan on p1 + Filter: (c1 = 1) + -> Seq Scan on p1c1 p1 + Filter: (c1 = 1) +(5 rows) /*+IndexScan(p1)*/ EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE p1.c1 = 1; @@ -385,15 +384,14 @@ 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) + QUERY PLAN +--------------------------------------- + Append + -> Index Scan using p1_i on p1 + Index Cond: (c1 = 1) + -> Index Scan using p1c1_i on p1c1 p1 + Index Cond: (c1 = 1) +(5 rows) -- No. S-1-5-3 EXPLAIN (COSTS false) SELECT * FROM s1.ul1 WHERE ul1.c1 = 1; @@ -4974,15 +4972,14 @@ error hint: -- No. S-3-8-2 EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1; - QUERY PLAN ---------------------------------- - Result - -> Append - -> Seq Scan on p1 - Filter: (c1 = 1) - -> Seq Scan on p1c1 p1 - Filter: (c1 = 1) -(6 rows) + QUERY PLAN +-------------------------- + Append + -> Seq Scan on p1 + Filter: (c1 = 1) + -> Seq Scan on p1c1 p1 + Filter: (c1 = 1) +(5 rows) /*+IndexScan(p1)*/ EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1; @@ -4993,30 +4990,28 @@ 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) + QUERY PLAN +--------------------------------------- + Append + -> Index Scan using p1_i on p1 + Index Cond: (c1 = 1) + -> Index Scan using p1c1_i on p1c1 p1 + Index Cond: (c1 = 1) +(5 rows) ---- ---- No. S-3-9 inheritance table number ---- -- No. S-3-9-1 EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1; - QUERY PLAN ---------------------------------- - Result - -> Append - -> Seq Scan on p1 - Filter: (c1 = 1) - -> Seq Scan on p1c1 p1 - Filter: (c1 = 1) -(6 rows) + QUERY PLAN +-------------------------- + Append + -> Seq Scan on p1 + Filter: (c1 = 1) + -> Seq Scan on p1c1 p1 + Filter: (c1 = 1) +(5 rows) /*+IndexScan(p1)*/ EXPLAIN (COSTS false) SELECT * FROM s1.p1 WHERE c1 = 1; @@ -5027,29 +5022,27 @@ 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) + QUERY PLAN +--------------------------------------- + Append + -> Index Scan using p1_i on p1 + Index Cond: (c1 = 1) + -> Index Scan using p1c1_i on p1c1 p1 + Index Cond: (c1 = 1) +(5 rows) -- No. S-3-9-2 EXPLAIN (COSTS false) SELECT * FROM s1.p2 WHERE c1 = 1; - QUERY PLAN ------------------------------------ - Result - -> Append - -> Seq Scan on p2 - Filter: (c1 = 1) - -> Seq Scan on p2c1 p2 - Filter: (c1 = 1) - -> Seq Scan on p2c1c1 p2 - Filter: (c1 = 1) -(8 rows) + QUERY PLAN +-------------------------- + Append + -> Seq Scan on p2 + Filter: (c1 = 1) + -> Seq Scan on p2c1 p2 + Filter: (c1 = 1) + -> Seq Scan on p2c1c1 p2 + Filter: (c1 = 1) +(7 rows) /*+IndexScan(p2)*/ EXPLAIN (COSTS false) SELECT * FROM s1.p2 WHERE c1 = 1; @@ -5060,33 +5053,31 @@ not used hint: duplication hint: error hint: - QUERY PLAN ----------------------------------------------------- - Result - -> Append - -> Index Scan using p2_i on p2 - Index Cond: (c1 = 1) - -> Index Scan using p2c1_i on p2c1 p2 - Index Cond: (c1 = 1) - -> Index Scan using p2c1c1_i on p2c1c1 p2 - Index Cond: (c1 = 1) -(8 rows) + QUERY PLAN +------------------------------------------- + Append + -> Index Scan using p2_i on p2 + Index Cond: (c1 = 1) + -> Index Scan using p2c1_i on p2c1 p2 + Index Cond: (c1 = 1) + -> Index Scan using p2c1c1_i on p2c1c1 p2 + Index Cond: (c1 = 1) +(7 rows) ---- ---- No. S-3-10 inheritance table specified table ---- EXPLAIN (COSTS false) SELECT * FROM s1.p2 WHERE c1 = 1; - QUERY PLAN ------------------------------------ - Result - -> Append - -> Seq Scan on p2 - Filter: (c1 = 1) - -> Seq Scan on p2c1 p2 - Filter: (c1 = 1) - -> Seq Scan on p2c1c1 p2 - Filter: (c1 = 1) -(8 rows) + QUERY PLAN +-------------------------- + Append + -> Seq Scan on p2 + Filter: (c1 = 1) + -> Seq Scan on p2c1 p2 + Filter: (c1 = 1) + -> Seq Scan on p2c1c1 p2 + Filter: (c1 = 1) +(7 rows) -- No. S-3-10-1 /*+IndexScan(p2)*/ @@ -5098,17 +5089,16 @@ not used hint: duplication hint: error hint: - QUERY PLAN ----------------------------------------------------- - Result - -> Append - -> Index Scan using p2_i on p2 - Index Cond: (c1 = 1) - -> Index Scan using p2c1_i on p2c1 p2 - Index Cond: (c1 = 1) - -> Index Scan using p2c1c1_i on p2c1c1 p2 - Index Cond: (c1 = 1) -(8 rows) + QUERY PLAN +------------------------------------------- + Append + -> Index Scan using p2_i on p2 + Index Cond: (c1 = 1) + -> Index Scan using p2c1_i on p2c1 p2 + Index Cond: (c1 = 1) + -> Index Scan using p2c1c1_i on p2c1c1 p2 + Index Cond: (c1 = 1) +(7 rows) -- No. S-3-10-2 /*+IndexScan(p2c1)*/ @@ -5120,29 +5110,27 @@ IndexScan(p2c1) duplication hint: error hint: - QUERY PLAN ------------------------------------ - Result - -> Append - -> Seq Scan on p2 - Filter: (c1 = 1) - -> Seq Scan on p2c1 p2 - Filter: (c1 = 1) - -> Seq Scan on p2c1c1 p2 - Filter: (c1 = 1) -(8 rows) + QUERY PLAN +-------------------------- + Append + -> Seq Scan on p2 + Filter: (c1 = 1) + -> Seq Scan on p2c1 p2 + Filter: (c1 = 1) + -> Seq Scan on p2c1c1 p2 + Filter: (c1 = 1) +(7 rows) -- No. S-3-10-3 EXPLAIN SELECT c4 FROM s1.p1 WHERE c2 * 2 < 100 AND c1 < 10; - QUERY PLAN -------------------------------------------------------------------- - Result (cost=0.00..2.75 rows=4 width=10) - -> Append (cost=0.00..2.75 rows=4 width=10) - -> Seq Scan on p1 (cost=0.00..0.00 rows=1 width=32) - Filter: ((c1 < 10) AND ((c2 * 2) < 100)) - -> Seq Scan on p1c1 p1 (cost=0.00..2.75 rows=3 width=2) - Filter: ((c1 < 10) AND ((c2 * 2) < 100)) -(6 rows) + QUERY PLAN +---------------------------------------------------------- + Append (cost=0.00..2.75 rows=4 width=10) + -> Seq Scan on p1 (cost=0.00..0.00 rows=1 width=32) + Filter: ((c1 < 10) AND ((c2 * 2) < 100)) + -> Seq Scan on p1c1 p1 (cost=0.00..2.75 rows=3 width=2) + Filter: ((c1 < 10) AND ((c2 * 2) < 100)) +(5 rows) /*+IndexScan(p1 p1_parent)*/ EXPLAIN SELECT c4 FROM s1.p1 WHERE c2 * 2 < 100 AND c1 < 10; @@ -5157,15 +5145,14 @@ not used hint: duplication hint: error hint: - QUERY PLAN ---------------------------------------------------------------------------------------------- - Result (cost=0.00..20.73 rows=4 width=10) - -> Append (cost=0.00..20.73 rows=4 width=10) - -> Index Scan using p1_parent on p1 (cost=0.00..8.27 rows=1 width=32) - Filter: ((c2 * 2) < 100) - -> Index Scan using p1c1_c4_expr_idx on p1c1 p1 (cost=0.00..12.45 rows=3 width=2) - Filter: ((c2 * 2) < 100) -(6 rows) + QUERY PLAN +------------------------------------------------------------------------------------ + Append (cost=0.12..20.48 rows=4 width=10) + -> Index Scan using p1_parent on p1 (cost=0.12..8.15 rows=1 width=32) + Filter: ((c2 * 2) < 100) + -> Index Scan using p1c1_c4_expr_idx on p1c1 p1 (cost=0.14..12.34 rows=3 width=2) + Filter: ((c2 * 2) < 100) +(5 rows) -- No. S-3-10-4 /*+IndexScan(p1 p1_i2)*/ @@ -5181,19 +5168,18 @@ not used hint: duplication hint: error hint: - QUERY PLAN ---------------------------------------------------------------------------------------- - Result (cost=0.00..30000000015.02 rows=4 width=4) - -> Append (cost=0.00..30000000015.02 rows=4 width=4) - -> Index Scan using p1_i2 on p1 (cost=0.00..8.27 rows=1 width=4) - Index Cond: (c2 = 1) - -> Seq Scan on p1c1 p1 (cost=10000000000.00..10000000002.25 rows=1 width=4) - Filter: (c2 = 1) - -> Seq Scan on p1c2 p1 (cost=10000000000.00..10000000002.25 rows=1 width=4) - Filter: (c2 = 1) - -> Seq Scan on p1c3 p1 (cost=10000000000.00..10000000002.25 rows=1 width=4) - Filter: (c2 = 1) -(10 rows) + QUERY PLAN +------------------------------------------------------------------------------ + Append (cost=0.12..30000000014.89 rows=4 width=4) + -> Index Scan using p1_i2 on p1 (cost=0.12..8.14 rows=1 width=4) + Index Cond: (c2 = 1) + -> Seq Scan on p1c1 (cost=10000000000.00..10000000002.25 rows=1 width=4) + Filter: (c2 = 1) + -> Seq Scan on p1c2 p1 (cost=10000000000.00..10000000002.25 rows=1 width=4) + Filter: (c2 = 1) + -> Seq Scan on p1c3 p1 (cost=10000000000.00..10000000002.25 rows=1 width=4) + Filter: (c2 = 1) +(9 rows) -- No. S-3-10-5 /*+IndexScan(p2 p2c1_pkey)*/ @@ -5215,17 +5201,16 @@ not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------------------------------------- - Result (cost=10000000000.00..20000000009.89 rows=3 width=34) - -> Append (cost=10000000000.00..20000000009.89 rows=3 width=34) - -> Seq Scan on p2 (cost=10000000000.00..10000000000.00 rows=1 width=44) - Filter: (c1 = 1) - -> Index Scan using p2c1_pkey on p2c1 p2 (cost=0.00..8.27 rows=1 width=44) - Index Cond: (c1 = 1) - -> Seq Scan on p2c1c1 p2 (cost=10000000000.00..10000000001.62 rows=1 width=14) - Filter: (c1 = 1) -(8 rows) + QUERY PLAN +--------------------------------------------------------------------------------- + Append (cost=10000000000.00..20000000009.77 rows=3 width=34) + -> Seq Scan on p2 (cost=10000000000.00..10000000000.00 rows=1 width=44) + Filter: (c1 = 1) + -> Index Scan using p2c1_pkey on p2c1 p2 (cost=0.12..8.14 rows=1 width=44) + Index Cond: (c1 = 1) + -> Seq Scan on p2c1c1 p2 (cost=10000000000.00..10000000001.62 rows=1 width=14) + Filter: (c1 = 1) +(7 rows) ---- ---- No. S-3-11 specified same table @@ -5947,15 +5932,14 @@ 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) + QUERY PLAN +------------------------------------------ + Append + -> Index Scan using p1_pkey on p1 + Index Cond: (c1 = 1) + -> Index Scan using p1c1_pkey on p1c1 p1 + Index Cond: (c1 = 1) +(5 rows) -- No. S-3-13-5 /*+IndexScanRegexp(p1 p1.*i)*/ @@ -5971,15 +5955,14 @@ 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) + QUERY PLAN +--------------------------------------- + Append + -> Index Scan using p1_i on p1 + Index Cond: (c1 = 1) + -> Index Scan using p1c1_i on p1c1 p1 + Index Cond: (c1 = 1) +(5 rows) -- No. S-3-13-6 /*+IndexScanRegexp(p1 no.*_exist)*/ @@ -5995,15 +5978,14 @@ 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) + QUERY PLAN +-------------------------- + Append + -> Seq Scan on p1 + Filter: (c1 = 1) + -> Seq Scan on p1c1 p1 + Filter: (c1 = 1) +(5 rows) ---- ---- No. S-3-14 message output -- 2.11.0