diff options
| author | Tom Lane | 2015-02-28 17:43:04 +0000 |
|---|---|---|
| committer | Tom Lane | 2015-02-28 17:43:04 +0000 |
| commit | b514a7460d9127ddda6598307272c701cbb133b7 (patch) | |
| tree | 2a04ac7147a24fa7a2f939b79b097b1ece98a6be /src/test | |
| parent | c4f4c7ca99169ac609df67c2d0eb78162484420c (diff) | |
Fix planning of star-schema-style queries.
Part of the intent of the parameterized-path mechanism was to handle
star-schema queries efficiently, but some overly-restrictive search
limiting logic added in commit e2fa76d80ba571d4de8992de6386536867250474
prevented such cases from working as desired. Fix that and add a
regression test about it. Per gripe from Marc Cousin.
This is arguably a bug rather than a new feature, so back-patch to 9.2
where parameterized paths were introduced.
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/join.out | 19 | ||||
| -rw-r--r-- | src/test/regress/sql/join.sql | 9 |
2 files changed, 28 insertions, 0 deletions
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index 25011847a04..ca3a17b283e 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -2781,6 +2781,25 @@ where thousand = (q1 + q2); (12 rows) -- +-- test ability to generate a suitable plan for a star-schema query +-- +explain (costs off) +select * from + tenk1, int8_tbl a, int8_tbl b +where thousand = a.q1 and tenthous = b.q1 and a.q2 = 1 and b.q2 = 2; + QUERY PLAN +--------------------------------------------------------------------- + Nested Loop + -> Seq Scan on int8_tbl b + Filter: (q2 = 2) + -> Nested Loop + -> Seq Scan on int8_tbl a + Filter: (q2 = 1) + -> Index Scan using tenk1_thous_tenthous on tenk1 + Index Cond: ((thousand = a.q1) AND (tenthous = b.q1)) +(8 rows) + +-- -- test extraction of restriction OR clauses from join OR clause -- (we used to only do this for indexable clauses) -- diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index 718e1d9fc38..6005476c162 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -764,6 +764,15 @@ select * from where thousand = (q1 + q2); -- +-- test ability to generate a suitable plan for a star-schema query +-- + +explain (costs off) +select * from + tenk1, int8_tbl a, int8_tbl b +where thousand = a.q1 and tenthous = b.q1 and a.q2 = 1 and b.q2 = 2; + +-- -- test extraction of restriction OR clauses from join OR clause -- (we used to only do this for indexable clauses) -- |