diff options
| author | Tom Lane | 2012-08-16 17:03:54 +0000 |
|---|---|---|
| committer | Tom Lane | 2012-08-16 17:03:54 +0000 |
| commit | f5983923d81d6327bdacf9d439a1536c4c8c4c15 (patch) | |
| tree | 997abf7798809e86853da59c57efc23580817f6f /src/test | |
| parent | 56ba337e6fdff1e7ecb916f3037c666a30a98af0 (diff) | |
Allow create_index_paths() to consider multiple join bitmapscan paths.
In the initial cut at the "parameterized paths" feature, I'd simplified
create_index_paths() to the point where it would only generate a single
parameterized bitmap path per relation. Experimentation with an example
supplied by Josh Berkus convinces me that that's not good enough: we really
need to consider a bitmap path for each possible outer relation. Otherwise
we have regressions relative to pre-9.2 versions, in which the planner
picks a plain indexscan where it should have used a bitmap scan in queries
involving three or more tables. Indeed, after fixing this, several queries
in the regression tests show improved plans as a result of using bitmap not
plain indexscans.
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/join.out | 28 |
1 files changed, 17 insertions, 11 deletions
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index 6705706f02e..51aeb8de7ba 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -2725,11 +2725,13 @@ where t1.unique1 = 1; Index Cond: (unique1 = 1) -> Nested Loop Join Filter: (t1.ten = t3.ten) - -> Index Scan using tenk1_hundred on tenk1 t2 - Index Cond: (t1.hundred = hundred) + -> Bitmap Heap Scan on tenk1 t2 + Recheck Cond: (t1.hundred = hundred) + -> Bitmap Index Scan on tenk1_hundred + Index Cond: (t1.hundred = hundred) -> Index Scan using tenk1_unique2 on tenk1 t3 Index Cond: (unique2 = t2.thousand) -(9 rows) +(11 rows) explain (costs off) select * from tenk1 t1 left join @@ -2743,32 +2745,36 @@ where t1.unique1 = 1; Index Cond: (unique1 = 1) -> Nested Loop Join Filter: ((t1.ten + t2.ten) = t3.ten) - -> Index Scan using tenk1_hundred on tenk1 t2 - Index Cond: (t1.hundred = hundred) + -> Bitmap Heap Scan on tenk1 t2 + Recheck Cond: (t1.hundred = hundred) + -> Bitmap Index Scan on tenk1_hundred + Index Cond: (t1.hundred = hundred) -> Index Scan using tenk1_unique2 on tenk1 t3 Index Cond: (unique2 = t2.thousand) -(9 rows) +(11 rows) explain (costs off) select count(*) from tenk1 a join tenk1 b on a.unique1 = b.unique2 left join tenk1 c on a.unique2 = b.unique1 and c.thousand = a.thousand join int4_tbl on b.thousand = f1; - QUERY PLAN --------------------------------------------------------------------------- + QUERY PLAN +------------------------------------------------------------------------- Aggregate -> Nested Loop Left Join Join Filter: (a.unique2 = b.unique1) -> Nested Loop -> Nested Loop -> Seq Scan on int4_tbl - -> Index Scan using tenk1_thous_tenthous on tenk1 b - Index Cond: (thousand = int4_tbl.f1) + -> Bitmap Heap Scan on tenk1 b + Recheck Cond: (thousand = int4_tbl.f1) + -> Bitmap Index Scan on tenk1_thous_tenthous + Index Cond: (thousand = int4_tbl.f1) -> Index Scan using tenk1_unique1 on tenk1 a Index Cond: (unique1 = b.unique2) -> Index Only Scan using tenk1_thous_tenthous on tenk1 c Index Cond: (thousand = a.thousand) -(12 rows) +(14 rows) select count(*) from tenk1 a join tenk1 b on a.unique1 = b.unique2 |