From: Ryohei OKADA Date: Thu, 20 Jun 2013 08:20:04 +0000 (+0900) Subject: PG9.1でut-*テスト以外のリグレッションテストが通るよう、予想結果を修正した。 X-Git-Tag: REL91_1_1_1~95^2 X-Git-Url: http://git.osdn.net/view?p=pghintplan%2Fpg_hint_plan.git;a=commitdiff_plain;h=761662dbf7c8d7e7a817b1d0d817713bb5c4d8aa PG9.1でut-*テスト以外のリグレッションテストが通るよう、予想結果を修正した。 --- 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)