From 761662dbf7c8d7e7a817b1d0d817713bb5c4d8aa Mon Sep 17 00:00:00 2001 From: Ryohei OKADA Date: Thu, 20 Jun 2013 17:20:04 +0900 Subject: [PATCH] =?utf8?q?PG9.1=E3=81=A7ut-*=E3=83=86=E3=82=B9=E3=83=88?= =?utf8?q?=E4=BB=A5=E5=A4=96=E3=81=AE=E3=83=AA=E3=82=B0=E3=83=AC=E3=83=83?= =?utf8?q?=E3=82=B7=E3=83=A7=E3=83=B3=E3=83=86=E3=82=B9=E3=83=88=E3=81=8C?= =?utf8?q?=E9=80=9A=E3=82=8B=E3=82=88=E3=81=86=E3=80=81=E4=BA=88=E6=83=B3?= =?utf8?q?=E7=B5=90=E6=9E=9C=E3=82=92=E4=BF=AE=E6=AD=A3=E3=81=97=E3=81=9F?= =?utf8?q?=E3=80=82?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- expected/init-9.1.out | 1 + expected/pg_hint_plan-9.1.out | 168 +++++++++++++++++++++--------------------- 2 files changed, 85 insertions(+), 84 deletions(-) diff --git a/expected/init-9.1.out b/expected/init-9.1.out index 28b3f99..7e9dd44 100644 --- a/expected/init-9.1.out +++ b/expected/init-9.1.out @@ -1,4 +1,5 @@ SET search_path TO public; +CREATE EXTENSION pg_hint_plan; CREATE SCHEMA s0; CREATE TABLE t1 (id int PRIMARY KEY, val int); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1" diff --git a/expected/pg_hint_plan-9.1.out b/expected/pg_hint_plan-9.1.out index 9590688..4794411 100644 --- a/expected/pg_hint_plan-9.1.out +++ b/expected/pg_hint_plan-9.1.out @@ -902,27 +902,27 @@ error hint: (5 rows) -- single table scan hint test -EXPLAIN SELECT (SELECT max(id) FROM t1 v_1 WHERE id < 10), id FROM v1 WHERE v1.id = (SELECT max(id) FROM t1 v_2 WHERE id < 10); - QUERY PLAN ------------------------------------------------------------------------------------------------------ - Index Scan using t1_pkey on t1 (cost=1.89..10.16 rows=1 width=4) +EXPLAIN (COSTS false) SELECT (SELECT max(id) FROM t1 v_1 WHERE id < 10), id FROM v1 WHERE v1.id = (SELECT max(id) FROM t1 v_2 WHERE id < 10); + QUERY PLAN +---------------------------------------------------------------------- + Index Scan using t1_pkey on t1 Index Cond: (id = $3) InitPlan 2 (returns $1) - -> Result (cost=0.94..0.95 rows=1 width=0) + -> Result InitPlan 1 (returns $0) - -> Limit (cost=0.00..0.94 rows=1 width=4) - -> Index Scan Backward using t1_pkey on t1 v_1 (cost=0.00..8.43 rows=9 width=4) + -> Limit + -> Index Scan Backward using t1_pkey on t1 v_1 Index Cond: ((id IS NOT NULL) AND (id < 10)) InitPlan 4 (returns $3) - -> Result (cost=0.94..0.95 rows=1 width=0) + -> Result InitPlan 3 (returns $2) - -> Limit (cost=0.00..0.94 rows=1 width=4) - -> Index Scan Backward using t1_pkey on t1 v_2 (cost=0.00..8.43 rows=9 width=4) + -> Limit + -> Index Scan Backward using t1_pkey on t1 v_2 Index Cond: ((id IS NOT NULL) AND (id < 10)) (14 rows) /*+BitmapScan(v_1)*/ -EXPLAIN SELECT (SELECT max(id) FROM t1 v_1 WHERE id < 10), id FROM v1 WHERE v1.id = (SELECT max(id) FROM t1 v_2 WHERE id < 10); +EXPLAIN (COSTS false) SELECT (SELECT max(id) FROM t1 v_1 WHERE id < 10), id FROM v1 WHERE v1.id = (SELECT max(id) FROM t1 v_2 WHERE id < 10); LOG: pg_hint_plan: used hint: BitmapScan(v_1) @@ -930,26 +930,26 @@ not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------------------------------------------------ - Index Scan using t1_pkey on t1 (cost=29.34..37.61 rows=1 width=4) + QUERY PLAN +---------------------------------------------------------------------- + Index Scan using t1_pkey on t1 Index Cond: (id = $2) InitPlan 1 (returns $0) - -> Aggregate (cost=28.38..28.39 rows=1 width=4) - -> Bitmap Heap Scan on t1 v_1 (cost=4.32..28.36 rows=9 width=4) + -> Aggregate + -> Bitmap Heap Scan on t1 v_1 Recheck Cond: (id < 10) - -> Bitmap Index Scan on t1_pkey (cost=0.00..4.32 rows=9 width=0) + -> Bitmap Index Scan on t1_pkey Index Cond: (id < 10) InitPlan 3 (returns $2) - -> Result (cost=0.94..0.95 rows=1 width=0) + -> Result InitPlan 2 (returns $1) - -> Limit (cost=0.00..0.94 rows=1 width=4) - -> Index Scan Backward using t1_pkey on t1 v_2 (cost=0.00..8.43 rows=9 width=4) + -> Limit + -> Index Scan Backward using t1_pkey on t1 v_2 Index Cond: ((id IS NOT NULL) AND (id < 10)) (14 rows) /*+BitmapScan(v_2)*/ -EXPLAIN SELECT (SELECT max(id) FROM t1 v_1 WHERE id < 10), id FROM v1 WHERE v1.id = (SELECT max(id) FROM t1 v_2 WHERE id < 10); +EXPLAIN (COSTS false) SELECT (SELECT max(id) FROM t1 v_1 WHERE id < 10), id FROM v1 WHERE v1.id = (SELECT max(id) FROM t1 v_2 WHERE id < 10); LOG: pg_hint_plan: used hint: BitmapScan(v_2) @@ -957,26 +957,26 @@ not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------------------------------------------------ - Index Scan using t1_pkey on t1 (cost=29.34..37.61 rows=1 width=4) + QUERY PLAN +---------------------------------------------------------------------- + Index Scan using t1_pkey on t1 Index Cond: (id = $2) InitPlan 2 (returns $1) - -> Result (cost=0.94..0.95 rows=1 width=0) + -> Result InitPlan 1 (returns $0) - -> Limit (cost=0.00..0.94 rows=1 width=4) - -> Index Scan Backward using t1_pkey on t1 v_1 (cost=0.00..8.43 rows=9 width=4) + -> Limit + -> Index Scan Backward using t1_pkey on t1 v_1 Index Cond: ((id IS NOT NULL) AND (id < 10)) InitPlan 3 (returns $2) - -> Aggregate (cost=28.38..28.39 rows=1 width=4) - -> Bitmap Heap Scan on t1 v_2 (cost=4.32..28.36 rows=9 width=4) + -> Aggregate + -> Bitmap Heap Scan on t1 v_2 Recheck Cond: (id < 10) - -> Bitmap Index Scan on t1_pkey (cost=0.00..4.32 rows=9 width=0) + -> Bitmap Index Scan on t1_pkey Index Cond: (id < 10) (14 rows) /*+BitmapScan(t1)*/ -EXPLAIN SELECT (SELECT max(id) FROM t1 v_1 WHERE id < 10), id FROM v1 WHERE v1.id = (SELECT max(id) FROM t1 v_2 WHERE id < 10); +EXPLAIN (COSTS false) SELECT (SELECT max(id) FROM t1 v_1 WHERE id < 10), id FROM v1 WHERE v1.id = (SELECT max(id) FROM t1 v_2 WHERE id < 10); LOG: pg_hint_plan: used hint: BitmapScan(t1) @@ -984,28 +984,28 @@ not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------------------------------------------------ - Bitmap Heap Scan on t1 (cost=6.15..10.17 rows=1 width=4) + QUERY PLAN +---------------------------------------------------------------------- + Bitmap Heap Scan on t1 Recheck Cond: (id = $3) InitPlan 2 (returns $1) - -> Result (cost=0.94..0.95 rows=1 width=0) + -> Result InitPlan 1 (returns $0) - -> Limit (cost=0.00..0.94 rows=1 width=4) - -> Index Scan Backward using t1_pkey on t1 v_1 (cost=0.00..8.43 rows=9 width=4) + -> Limit + -> Index Scan Backward using t1_pkey on t1 v_1 Index Cond: ((id IS NOT NULL) AND (id < 10)) InitPlan 4 (returns $3) - -> Result (cost=0.94..0.95 rows=1 width=0) + -> Result InitPlan 3 (returns $2) - -> Limit (cost=0.00..0.94 rows=1 width=4) - -> Index Scan Backward using t1_pkey on t1 v_2 (cost=0.00..8.43 rows=9 width=4) + -> Limit + -> Index Scan Backward using t1_pkey on t1 v_2 Index Cond: ((id IS NOT NULL) AND (id < 10)) - -> Bitmap Index Scan on t1_pkey (cost=0.00..4.26 rows=1 width=0) + -> Bitmap Index Scan on t1_pkey Index Cond: (id = $3) (16 rows) /*+BitmapScan(v_1)BitmapScan(v_2)*/ -EXPLAIN SELECT (SELECT max(id) FROM t1 v_1 WHERE id < 10), id FROM v1 WHERE v1.id = (SELECT max(id) FROM t1 v_2 WHERE id < 10); +EXPLAIN (COSTS false) SELECT (SELECT max(id) FROM t1 v_1 WHERE id < 10), id FROM v1 WHERE v1.id = (SELECT max(id) FROM t1 v_2 WHERE id < 10); LOG: pg_hint_plan: used hint: BitmapScan(v_1) @@ -1014,26 +1014,26 @@ not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------------------------------- - Index Scan using t1_pkey on t1 (cost=56.78..65.05 rows=1 width=4) + QUERY PLAN +-------------------------------------------------- + Index Scan using t1_pkey on t1 Index Cond: (id = $1) InitPlan 1 (returns $0) - -> Aggregate (cost=28.38..28.39 rows=1 width=4) - -> Bitmap Heap Scan on t1 v_1 (cost=4.32..28.36 rows=9 width=4) + -> Aggregate + -> Bitmap Heap Scan on t1 v_1 Recheck Cond: (id < 10) - -> Bitmap Index Scan on t1_pkey (cost=0.00..4.32 rows=9 width=0) + -> Bitmap Index Scan on t1_pkey Index Cond: (id < 10) InitPlan 2 (returns $1) - -> Aggregate (cost=28.38..28.39 rows=1 width=4) - -> Bitmap Heap Scan on t1 v_2 (cost=4.32..28.36 rows=9 width=4) + -> Aggregate + -> Bitmap Heap Scan on t1 v_2 Recheck Cond: (id < 10) - -> Bitmap Index Scan on t1_pkey (cost=0.00..4.32 rows=9 width=0) + -> Bitmap Index Scan on t1_pkey Index Cond: (id < 10) (14 rows) /*+BitmapScan(v_1)BitmapScan(t1)*/ -EXPLAIN SELECT (SELECT max(id) FROM t1 v_1 WHERE id < 10), id FROM v1 WHERE v1.id = (SELECT max(id) FROM t1 v_2 WHERE id < 10); +EXPLAIN (COSTS false) SELECT (SELECT max(id) FROM t1 v_1 WHERE id < 10), id FROM v1 WHERE v1.id = (SELECT max(id) FROM t1 v_2 WHERE id < 10); LOG: pg_hint_plan: used hint: BitmapScan(t1) @@ -1042,28 +1042,28 @@ not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------------------------------------------------ - Bitmap Heap Scan on t1 (cost=33.60..37.61 rows=1 width=4) + QUERY PLAN +---------------------------------------------------------------------- + Bitmap Heap Scan on t1 Recheck Cond: (id = $2) InitPlan 1 (returns $0) - -> Aggregate (cost=28.38..28.39 rows=1 width=4) - -> Bitmap Heap Scan on t1 v_1 (cost=4.32..28.36 rows=9 width=4) + -> Aggregate + -> Bitmap Heap Scan on t1 v_1 Recheck Cond: (id < 10) - -> Bitmap Index Scan on t1_pkey (cost=0.00..4.32 rows=9 width=0) + -> Bitmap Index Scan on t1_pkey Index Cond: (id < 10) InitPlan 3 (returns $2) - -> Result (cost=0.94..0.95 rows=1 width=0) + -> Result InitPlan 2 (returns $1) - -> Limit (cost=0.00..0.94 rows=1 width=4) - -> Index Scan Backward using t1_pkey on t1 v_2 (cost=0.00..8.43 rows=9 width=4) + -> Limit + -> Index Scan Backward using t1_pkey on t1 v_2 Index Cond: ((id IS NOT NULL) AND (id < 10)) - -> Bitmap Index Scan on t1_pkey (cost=0.00..4.26 rows=1 width=0) + -> Bitmap Index Scan on t1_pkey Index Cond: (id = $2) (16 rows) /*+BitmapScan(v_2)BitmapScan(t1)*/ -EXPLAIN SELECT (SELECT max(id) FROM t1 v_1 WHERE id < 10), id FROM v1 WHERE v1.id = (SELECT max(id) FROM t1 v_2 WHERE id < 10); +EXPLAIN (COSTS false) SELECT (SELECT max(id) FROM t1 v_1 WHERE id < 10), id FROM v1 WHERE v1.id = (SELECT max(id) FROM t1 v_2 WHERE id < 10); LOG: pg_hint_plan: used hint: BitmapScan(t1) @@ -1072,28 +1072,28 @@ not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------------------------------------------------ - Bitmap Heap Scan on t1 (cost=33.60..37.61 rows=1 width=4) + QUERY PLAN +---------------------------------------------------------------------- + Bitmap Heap Scan on t1 Recheck Cond: (id = $2) InitPlan 2 (returns $1) - -> Result (cost=0.94..0.95 rows=1 width=0) + -> Result InitPlan 1 (returns $0) - -> Limit (cost=0.00..0.94 rows=1 width=4) - -> Index Scan Backward using t1_pkey on t1 v_1 (cost=0.00..8.43 rows=9 width=4) + -> Limit + -> Index Scan Backward using t1_pkey on t1 v_1 Index Cond: ((id IS NOT NULL) AND (id < 10)) InitPlan 3 (returns $2) - -> Aggregate (cost=28.38..28.39 rows=1 width=4) - -> Bitmap Heap Scan on t1 v_2 (cost=4.32..28.36 rows=9 width=4) + -> Aggregate + -> Bitmap Heap Scan on t1 v_2 Recheck Cond: (id < 10) - -> Bitmap Index Scan on t1_pkey (cost=0.00..4.32 rows=9 width=0) + -> Bitmap Index Scan on t1_pkey Index Cond: (id < 10) - -> Bitmap Index Scan on t1_pkey (cost=0.00..4.26 rows=1 width=0) + -> Bitmap Index Scan on t1_pkey Index Cond: (id = $2) (16 rows) /*+BitmapScan(v_1)BitmapScan(v_2)BitmapScan(t1)*/ -EXPLAIN SELECT (SELECT max(id) FROM t1 v_1 WHERE id < 10), id FROM v1 WHERE v1.id = (SELECT max(id) FROM t1 v_2 WHERE id < 10); +EXPLAIN (COSTS false) SELECT (SELECT max(id) FROM t1 v_1 WHERE id < 10), id FROM v1 WHERE v1.id = (SELECT max(id) FROM t1 v_2 WHERE id < 10); LOG: pg_hint_plan: used hint: BitmapScan(t1) @@ -1103,23 +1103,23 @@ not used hint: duplication hint: error hint: - QUERY PLAN ------------------------------------------------------------------------------------- - Bitmap Heap Scan on t1 (cost=61.04..65.05 rows=1 width=4) + QUERY PLAN +-------------------------------------------------- + Bitmap Heap Scan on t1 Recheck Cond: (id = $1) InitPlan 1 (returns $0) - -> Aggregate (cost=28.38..28.39 rows=1 width=4) - -> Bitmap Heap Scan on t1 v_1 (cost=4.32..28.36 rows=9 width=4) + -> Aggregate + -> Bitmap Heap Scan on t1 v_1 Recheck Cond: (id < 10) - -> Bitmap Index Scan on t1_pkey (cost=0.00..4.32 rows=9 width=0) + -> Bitmap Index Scan on t1_pkey Index Cond: (id < 10) InitPlan 2 (returns $1) - -> Aggregate (cost=28.38..28.39 rows=1 width=4) - -> Bitmap Heap Scan on t1 v_2 (cost=4.32..28.36 rows=9 width=4) + -> Aggregate + -> Bitmap Heap Scan on t1 v_2 Recheck Cond: (id < 10) - -> Bitmap Index Scan on t1_pkey (cost=0.00..4.32 rows=9 width=0) + -> Bitmap Index Scan on t1_pkey Index Cond: (id < 10) - -> Bitmap Index Scan on t1_pkey (cost=0.00..4.26 rows=1 width=0) + -> Bitmap Index Scan on t1_pkey Index Cond: (id = $1) (16 rows) -- 2.11.0