Re: Foreign join pushdown vs EvalPlanQual

On Tue, Oct 20, 2015 at 12:39 PM, Etsuro Fujita
<fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp> wrote:
> Sorry, my explanation was not correct. (Needed to take in caffeine.) What
> I'm concerned about is the following:
>
> SELECT * FROM localtab JOIN (ft1 LEFT JOIN ft2 ON ft1.x = ft2.x) ON
> localtab.id = ft1.id FOR UPDATE OF ft1
>
> LockRows
> -> Nested Loop
> Join Filter: (localtab.id = ft1.id)
> -> Seq Scan on localtab
> -> Foreign Scan on <ft1, ft2>
> Remote SQL: SELECT * FROM ft1 LEFT JOIN ft2 WHERE ft1.x = ft2.x
> FOR UPDATE OF ft1
>
> Assume that ft1 performs late row locking.

If the SQL includes "FOR UPDATE of ft1", then it clearly performs
early row locking. I assume you meant to omit that.

> If an EPQ recheck was invoked
> due to a concurrent transaction on the remote server that changed only the
> value x of the ft1 tuple previously retrieved, then we would have to
> generate a fake ft1/ft2-join tuple with nulls for ft2. (Assume that the ft2
> tuple previously retrieved was not a null tuple.) However, I'm not sure how
> we can do that in ForeignRecheck; we can't know for example, which one is
> outer and which one is inner, without an alternative local join execution
> plan. Maybe I'm missing something, though.

I would expect it to issue a new query like: SELECT * FROM ft1 LEFT
JOIN ft2 WHERE ft1.x = ft2.x AND ft1.tid = $0 AND ft2.tid = $1.

This should be significantly more efficient than fetching the base
rows from each of two tables with two separate queries.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company