Re: Push down more full joins in postgres_fdw

On Wed, Sep 14, 2016 at 8:52 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Tue, Sep 13, 2016 at 11:38 PM, Ashutosh Bapat
> <ashutosh(dot)bapat(at)enterprisedb(dot)com> wrote:
>> On Tue, Sep 13, 2016 at 10:28 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>> On Tue, Sep 6, 2016 at 9:07 AM, Ashutosh Bapat
>>> <ashutosh(dot)bapat(at)enterprisedb(dot)com> wrote:
>>>> That's not true with the alias information. As long as we detect which
>>>> relations need subqueries, their RTIs are enough to create unique aliases
>>>> e.g. if a relation involves RTIs 1, 2, 3 corresponding subquery can have
>>>> alias r123 without conflicting with any other alias.
>>>
>>> What if RTI 123 is also used in the query?
>>
>> Good catch. I actually meant some combination of 1, 2 and 3, which is
>> unique for a join between r1, r2 and r3. How about r1_2_3 or
>> r1_r2_r3?
>
> Sure, something like that can work, but if you have a big enough join
> the identifier might get too long. I'm not sure why it wouldn't work
> to just use the lowest RTI involved in the join, though; the others
> won't appear anywhere else at that query level.
>

Yes, that will work too and is much more preferred than long r1_2_3.
The idea is to come with a unique alias name from RTIs involved in
that relation. Thinking loudly, r1_2_3 is more descriptive to debug
issues. It tells that the subquery it refers to covers RTIs 1, 2 and
3. That information may be quite helpful to understand the remote SQL.
r1 on the other hand can refer to relation with RTI 1 or a join
relation where least RTI is 1. That can be solved by using s<RTI> for
subquery and r<RTI> for a bare relation. But it still doesn't tell us
which all relations are involved.

But since the subquery is part of remote SQL and we have to take a
look at it to find out meaning of individual columns, that benefit may
be smaller compared to the convenience of smaller alias. So +1 for
using the smallest RTI to indicate a subquery.

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company