On Fri, Jun 10, 2016 at 4:14 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> Although I have done a bit of review of this patch, it needs more
> thought than I have so far had time to give it. I will update again
> by Tuesday.
I've reviewed this a bit further and have discovered an infelicity.
The following is all with the patch applied.
By itself, this join can be pushed down:
contrib_regression=# EXPLAIN SELECT 13 FROM ft1 RIGHT JOIN ft2 ON
ft1.c1 = ft2.c1; QUERY PLAN
------------------------------------------------------Foreign Scan (cost=100.00..137.66 rows=822 width=4) Relations:
(public.ft2)LEFT JOIN (public.ft1)
(2 rows)
That's great. However, when that query is used as the outer rel of a
left join, it can't:
contrib_regression=# explain verbose select * from ft4 LEFT JOIN
(SELECT 13 FROM ft1 RIGHT JOIN ft2 ON ft1.c1 = ft2.c1) q on true; QUERY PLAN
---------------------------------------------------------------------------------------------Nested Loop Left Join
(cost=353.50..920.77rows=41100 width=19) Output: ft4.c1, ft4.c2, ft4.c3, (13) -> Foreign Scan on public.ft4
(cost=100.00..102.50rows=50 width=15) Output: ft4.c1, ft4.c2, ft4.c3 Remote SQL: SELECT c1, c2, c3 FROM
"S1"."T 3" -> Materialize (cost=253.50..306.57 rows=822 width=4) Output: (13) -> Hash Left Join
(cost=253.50..302.46rows=822 width=4) Output: 13 Hash Cond: (ft2.c1 = ft1.c1) ->
Foreign Scan on public.ft2 (cost=100.00..137.66
rows=822 width=4) Output: ft2.c1 Remote SQL: SELECT "C 1" FROM "S 1"."T 1"
-> Hash (cost=141.00..141.00 rows=1000 width=4) Output: ft1.c1 -> Foreign
Scanon public.ft1
(cost=100.00..141.00 rows=1000 width=4) Output: ft1.c1 Remote SQL:
SELECT"C 1" FROM "S 1"."T 1"
(18 rows)
Of course, because of the PlaceHolderVar, there's no way to push down
the ft1-ft2-ft4 join to the remote side. But we could still push down
the ft1-ft2 join and then locally perform the join between the result
and ft4. However, the proposed fix doesn't allow that, because
ph_eval_at is (b 4 5) and relids for the ft1-ft2 join is also (b 4 5),
and so the bms_is_subset(phinfo->ph_eval_at, relids) test returns
true.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company