diff options
| author | Robert Haas <rhaas@postgresql.org> | 2016-04-20 23:34:07 -0400 |
|---|---|---|
| committer | Robert Haas <rhaas@postgresql.org> | 2016-04-20 23:54:19 -0400 |
| commit | 5b1f9ce1d9e8dcae2bcd93b2becffaba5e4f3049 (patch) | |
| tree | af17513006e12e0225dc30d07d1668c78cdd2966 /contrib/postgres_fdw/sql/postgres_fdw.sql | |
| parent | cbabb70f35bb0e5bac84b9f15ecadc82868ad9f9 (diff) | |
| download | postgresql-5b1f9ce1d9e8dcae2bcd93b2becffaba5e4f3049.tar.gz postgresql-5b1f9ce1d9e8dcae2bcd93b2becffaba5e4f3049.zip | |
postgres_fdw: Don't push down certain full joins.
If there's a filter condition on either side of a full outer join,
it is neither correct to attach it to the join's ON clause nor to
throw it into the toplevel WHERE clause. Just don't push down the
join in that case.
To maximize the number of cases where we can still push down full
joins, push inner join conditions into the ON clause at the first
opportunity rather than postponing them to the top-level WHERE
clause. This produces nicer SQL, anyway.
This bug was introduced in e4106b2528727c4b48639c0e12bf2f70a766b910.
Ashutosh Bapat, per report from Rajkumar Raghuwanshi.
Diffstat (limited to 'contrib/postgres_fdw/sql/postgres_fdw.sql')
| -rw-r--r-- | contrib/postgres_fdw/sql/postgres_fdw.sql | 8 |
1 files changed, 8 insertions, 0 deletions
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index f420b230e76..d1f44d6ef0f 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -372,6 +372,14 @@ SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 RIGHT JOIN ft2 t2 ON (t1.c1 = t2.c1) RIGH EXPLAIN (COSTS false, VERBOSE) SELECT t1.c1, t2.c1 FROM ft4 t1 FULL JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 45 LIMIT 10; SELECT t1.c1, t2.c1 FROM ft4 t1 FULL JOIN ft5 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1 OFFSET 45 LIMIT 10; +-- full outer join with restrictions on the joining relations +EXPLAIN (COSTS false, VERBOSE) +SELECT t1.c1, t2.c1 FROM (SELECT c1 FROM ft4 WHERE c1 between 50 and 60) t1 FULL JOIN (SELECT c1 FROM ft5 WHERE c1 between 50 and 60) t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1; +SELECT t1.c1, t2.c1 FROM (SELECT c1 FROM ft4 WHERE c1 between 50 and 60) t1 FULL JOIN (SELECT c1 FROM ft5 WHERE c1 between 50 and 60) t2 ON (t1.c1 = t2.c1) ORDER BY t1.c1, t2.c1; +-- full outer join + inner join +EXPLAIN (COSTS false, VERBOSE) +SELECT t1.c1, t2.c1, t3.c1 FROM ft4 t1 INNER JOIN ft5 t2 ON (t1.c1 = t2.c1 + 1 and t1.c1 between 50 and 60) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) ORDER BY t1.c1, t2.c1, t3.c1 LIMIT 10; +SELECT t1.c1, t2.c1, t3.c1 FROM ft4 t1 INNER JOIN ft5 t2 ON (t1.c1 = t2.c1 + 1 and t1.c1 between 50 and 60) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) ORDER BY t1.c1, t2.c1, t3.c1 LIMIT 10; -- full outer join three tables EXPLAIN (COSTS false, VERBOSE) SELECT t1.c1, t2.c2, t3.c3 FROM ft2 t1 FULL JOIN ft2 t2 ON (t1.c1 = t2.c1) FULL JOIN ft4 t3 ON (t2.c1 = t3.c1) OFFSET 10 LIMIT 10; |