Re: Foreign join pushdown vs EvalPlanQual - Mailing list pgsql-hackers

On 2015/10/28 6:04, Robert Haas wrote:
> On Tue, Oct 20, 2015 at 12:39 PM, Etsuro Fujita
> <fujita.etsuro@lab.ntt.co.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.

Right.  Sorry for my mistake.

>> 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.

We assume here that ft1 uses late row locking, so I thought the above 
SQL should include "FOR UPDATE of ft1".  But I still don't think that 
that is right; the SQL with "FOR UPDATE of ft1" wouldn't generate the 
fake ft1/ft2-join tuple with nulls for ft2, as expected.  The reason for 
that is that the updated version of the ft1 tuple wouldn't satisfy the 
ft1.tid = $0 condition in an EPQ recheck, because the ctid for the 
updated version of the ft1 tuple has changed.  (IIUC, I think that if we 
use a TID scan for ft1, the SQL would generate the expected result, 
because I think that the TID condition would be ignored in the EPQ 
recheck, but I don't think it's guaranteed to use a TID scan for ft1.) 
Maybe I'm missing something, though.

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

Maybe I think we could fix the SQL, so I have to admit that, but I'm 
just wondering (1) what would happen for the case when ft1 uses late row 
rocking and ft2 uses early row rocking and (2) that would be still more 
efficient than re-fetching only the base row from ft1.

What I thought to improve the efficiency in the secondary-plan approach 
that I proposed was that if we could parallelize re-fetching foreign 
rows in ExecLockRows and EvalPlanQualFetchRowMarks, we would be able to 
improve the efficiency not only for the case when performing a join of 
foreign tables remotely but for the case when performing the join locally.

Best regards,
Etsuro Fujita