On 2016/06/15 9:13, Amit Langote wrote:
On 2016/06/15 0:50, Robert Haas wrote:
On Tue, Jun 14, 2016 at 4:06 AM, Amit Langote wrote:
Attached new version of the patch with following changes:


OK, I committed this version with some cosmetic changes.

Thank you all for working on this!

While reviewing the patch, I noticed that the patch is still restrictive. Consider:

postgres=# explain verbose select ft1.a, (ft3.a IS NOT NULL) from (ft1 inner join ft2 on ft1.a = ft2.a) left join ft3 on ft1.a = ft3.a;
 QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
  Foreign Scan  (cost=100.00..103.10 rows=2 width=5)
    Output: ft1.a, (ft3.a IS NOT NULL)
Relations: ((public.ft1) INNER JOIN (public.ft2)) LEFT JOIN (public.ft3) Remote SQL: SELECT r1.a, r4.a FROM ((public.t1 r1 INNER JOIN public.t2 r2 ON (((r1.a = r2.a)))) LEFT JOIN public.t3 r4 ON (((r1.a = r4.a))))
(4 rows)

That's great, but:

postgres=# explain verbose select * from t1 left join (select ft1.a, (ft3.a IS NOT NULL) from (ft1 inner join ft2 on ft1.a = ft2.a) left join ft3 on ft1.a = ft3.a) ss (a, b) on t1.a = ss.a;
                                                QUERY PLAN
--------------------------------------------------------------------------------------------------------
  Hash Right Join  (cost=202.11..204.25 rows=3 width=13)
    Output: t1.a, t1.b, ft1.a, ((ft3.a IS NOT NULL))
    Hash Cond: (ft1.a = t1.a)
    ->  Hash Left Join  (cost=201.04..203.15 rows=2 width=5)
          Output: ft1.a, (ft3.a IS NOT NULL)
          Hash Cond: (ft1.a = ft3.a)
          ->  Foreign Scan  (cost=100.00..102.09 rows=2 width=4)
                Output: ft1.a
                Relations: (public.ft1) INNER JOIN (public.ft2)
Remote SQL: SELECT r4.a FROM (public.t1 r4 INNER JOIN public.t2 r5 ON (((r4.a = r5.a))))
          ->  Hash  (cost=101.03..101.03 rows=1 width=4)
                Output: ft3.a
-> Foreign Scan on public.ft3 (cost=100.00..101.03 rows=1 width=4)
                      Output: ft3.a
                      Remote SQL: SELECT a FROM public.t3
    ->  Hash  (cost=1.03..1.03 rows=3 width=8)
          Output: t1.a, t1.b
          ->  Seq Scan on public.t1  (cost=0.00..1.03 rows=3 width=8)
                Output: t1.a, t1.b
(19 rows)

As in the example shown upthread, we could still push down the ft1-ft2-ft3 join and then perform the join between the result and t1. However, the patch doesn't allow that, because ph_eval_at is (b 4 7) and relids for the ft1-ft2-ft3 join is (b 4 5 7), and so the bms_nonempty_difference(relids, phinfo->ph_eval_at) test returns true.

ISTM that a robuster solution to this is to push down the ft1-ft2-ft3 join with the PHV by extending deparseExplicitTargetList() and/or something else so that we can send the remote query like:

select ft1.a, (ft3.a IS NOT NULL) from (ft1 inner join ft2 on ft1.a = ft2.a) left join ft3 on ft1.a = ft3.a

Right?

Best regards,
Etsuro Fujita




--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Reply via email to