Re: Push down more full joins in postgres_fdw - Mailing list pgsql-hackers

On 2016/10/26 16:11, Ashutosh Bapat wrote:

You wrote:
>>> For
>>> example, let's assume that a relation (1, 2, 3) is required to be
>>> deparsed as subquery for an immediate upper relation (1, 2, 3, 4, 5)
>>> (thus the other joining relation being (4,5)). While deparsing for
>>> relation (1,2,3,4,5), the context will contain a 5 element array, with
>>> positions 1, 2, 3 filled by same targetlist and alias whereas
>>> positions 4 and 5 will not be filled as those relations are not
>>> deparsed as subqueries.

I wrote:
>> Sorry, I don't understand this.  In that case, the immediate upper relation
>> (1, 2, 3, 4, 5) would need to fill the targetlist and aliases for *the join
>> relation (1, 2, 3) somewhere*, not the targetlist and aliases for each of
>> the component relations 1, 2, and 3, because the join relation is deparsed
>> as a subquery.  Maybe I'm missing something, though.

> The description above does not specify "targetlist and alias" for each
> of (1, 2, 3). The array in the context will have positions 1, 2, 3
> filled with *same* alias and targetlist which is derived from relation
> (1, 2, 3).

OK

>>> Let's assume in relation (1, 2, 3), (1, 3) in
>>> turn requires subquery but (2) does not. Thus the context created
>>> while deparsing (1, 2, 3) will have a 3 element array with positions 1
>>> and 3 containing the same targetlist and alias, where as position 2
>>> will be empty.

>>> When deparsing a Var node with varno = N and varattno =
>>> m, if the nth position in the array in the context is empty, that Var
>>> node will be deparsed as rN.<column name>.

>> What happens when deparsing eg, a Var with varno = 2 at the topmost relation
>> (1, 2, 3, 4, 5)?  The second position of the array is empty, but the join
>> relation (1, 2, 3) is deparsed as a subquery, so the Var should be deparsed
>> as an alias of an output column of the subquery at the topmost relation, I
>> think.

> position 2 will not be empty, it will be filled by the alias and
> targetlist derived from relation (1, 2, 3).

OK

>>> But if that position is has
>>> alias sZ, then we search for that Var node in the targetlist and if
>>> it's found at kth position in the targetlist, we will deparse it as
>>> sZ.ck. The search in the targetlist can be performed using
>>> tlist_member, and then fetching the position by TargetEntry::resno.

>>> This does not require any recursion and thus saves stack space and
>>> some CPU cycles required for recursion.

>> Is that true?

> Yes, unless you explain why is that false.

OK, that would be true, I think.

>>> I guess, the arrays need to be
>>> computed only once for any relation when the query for that relation
>>> is deparsed the first time.

>> Does this algorithm extend to the case where we consider paths for every
>> join order?

> Yes, if we store the information about which of relations need
> subquery and which don't for every join order.

Hmm.  Sorry, I'm not so excited about this proposal.  I think (1) that 
is solving a problem that hasn't been proven to be a problem, (2) that 
would complicate the deparser logic, and (3) the cost of creating this 
array for each relation by the bottom-up method while deparsing a remote 
query would be not small (especially when the query is large), so that 
might need more cycles for deparsing the query than what I proposed when 
use_remote_estimate=false.  So, I'd like to go with what I proposed, at 
least as the first cut.

Best regards,
Etsuro Fujita