Re: Removing Inner Joins

Hi all,

I was reading about the plans to add functionality to the planner to
remove redundant inner joins where there is no member of the inner
relation present in the target list and the inner relation is only
used for the join clause. Also, we have a foreign key in the outer
relation to the inner relation.

Where are we with that functionality atm? Do we have plans to move forward?

Also, how difficult is it to go ahead with it?

Regards,

Atri

--
Regards,

Atri
l'apprenant

On Mon, Jul 8, 2013 at 11:20 PM, Atri Sharma <atri(dot)jiit(at)gmail(dot)com> wrote:
> Where are we with that functionality atm? Do we have plans to move forward?

PostgreSQL has had this capability for some time. See:

http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=488d70ab46311386801c10691196ec8d755f2283

--
Peter Geoghegan

On 2013-07-08 23:31:15 -0700, Peter Geoghegan wrote:
> On Mon, Jul 8, 2013 at 11:20 PM, Atri Sharma <atri(dot)jiit(at)gmail(dot)com> wrote:
> > Where are we with that functionality atm? Do we have plans to move forward?

> PostgreSQL has had this capability for some time. See:

> http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=488d70ab46311386801c10691196ec8d755f2283

That's for outer joins tho.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Tue, Jul 9, 2013 at 12:01 PM, Peter Geoghegan <pg(at)heroku(dot)com> wrote:
> On Mon, Jul 8, 2013 at 11:20 PM, Atri Sharma <atri(dot)jiit(at)gmail(dot)com> wrote:
>> Where are we with that functionality atm? Do we have plans to move forward?
>
> PostgreSQL has had this capability for some time. See:
>
> http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=488d70ab46311386801c10691196ec8d755f2283
>
>

Thanks, but doesn't that commit refer to left join removal? I was
referring to inner join removals.

Could you point me to a resource for that please? I was looking at the
mail threads for that earlier.

Regards,

Atri

On Mon, Jul 8, 2013 at 11:33 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> That's for outer joins tho.

Oh, right - I spoke too soon. Looks like I missed the whole discussion
on inner join removal.

--
Peter Geoghegan

My main point here is researching how difficult it is to add
functionality in the planner to allow it to to detect and make
decisions on foreign keys present in the outer relation.

I think that if this is added, rest of the work would be much easier.
I amy be completely wrong,though.

Thoughts/Comments?

Regards,

Atri

Regards,

Atri
l'apprenant

AFAIK,
There is code to remove the redundant relations (joins too are relations).
But I don't remember exactly where it is. Start looking at query_planner().
The removal of relations should happen before actually planning the joins.

On Tue, Jul 9, 2013 at 12:21 PM, Atri Sharma <atri(dot)jiit(at)gmail(dot)com> wrote:

> My main point here is researching how difficult it is to add
> functionality in the planner to allow it to to detect and make
> decisions on foreign keys present in the outer relation.
>
> I think that if this is added, rest of the work would be much easier.
> I amy be completely wrong,though.
>
> Thoughts/Comments?
>
> Regards,
>
> Atri
>
> Regards,
>
> Atri
> l'apprenant
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

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

On Wed, Jul 10, 2013 at 8:29 AM, Ashutosh Bapat
<ashutosh(dot)bapat(at)enterprisedb(dot)com> wrote:
> AFAIK,
> There is code to remove the redundant relations (joins too are relations).
> But I don't remember exactly where it is. Start looking at query_planner().
> The removal of relations should happen before actually planning the joins.
>
>

Thanks for your reply.

I am not sure if the part you mentioned is inline with the case I am
talking about. The specific case I mentioned does not seem to have
been implemented yet, and I was researching the roadblocks to it,
hence asked on the list.

Thanks and Regards,

Atri

--
Regards,

Atri
l'apprenant

On 07/10/2013 07:28 AM, Atri Sharma wrote:
> I am not sure if the part you mentioned is inline with the case I am
> talking about.
Can you please post an example of such a join removal? I mean a query
before and after the removal. Thanks,

//Tony

> Can you please post an example of such a join removal? I mean a query before
> and after the removal. Thanks,

Courtesy Robert Haas:

SELECT foo.x, foo.y, foo.z FROM foo WHERE foo.x = bar.x

Conditions:

1) foo.x is not null.

2) foo (x) is a foreign key referencing bar (x).

We can ignore bar completely in this case i.e. avoid scanning bar.

Regards,

Atri

--
Regards,

Atri
l'apprenant

On 07/10/2013 09:18 AM, Atri Sharma wrote:
>> Can you please post an example of such a join removal? I mean a query before
>> and after the removal. Thanks,
> Courtesy Robert Haas:
>
> SELECT foo.x, foo.y, foo.z FROM foo WHERE foo.x = bar.x
>
> Conditions:
>
> 1) foo.x is not null.
I guess that this is also not needed. you can just remove rows where

foo.x is null

That is, replace the join with "foo.x is not null"
>
> 2) foo (x) is a foreign key referencing bar (x).
>
> We can ignore bar completely in this case i.e. avoid scanning bar.
>
> Regards,
>
> Atri
>
>
> --
> Regards,
>
> Atri
> l'apprenant
>
>

--
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ

On Wed, Jul 10, 2013 at 1:11 PM, Hannu Krosing <hannu(at)2ndquadrant(dot)com> wrote:
> On 07/10/2013 09:18 AM, Atri Sharma wrote:
>>> Can you please post an example of such a join removal? I mean a query before
>>> and after the removal. Thanks,
>> Courtesy Robert Haas:
>>
>> SELECT foo.x, foo.y, foo.z FROM foo WHERE foo.x = bar.x
>>
>> Conditions:
>>
>> 1) foo.x is not null.
> I guess that this is also not needed. you can just remove rows where
>
> foo.x is null
>
> That is, replace the join with "foo.x is not null"

Yeah, sounds good. We should explore it further.

Regards,

Atri

--
Regards,

Atri
l'apprenant

Sent from my iPad

On 10-Jul-2013, at 13:11, Hannu Krosing <hannu(at)2ndQuadrant(dot)com> wrote:

> On 07/10/2013 09:18 AM, Atri Sharma wrote:
>>> Can you please post an example of such a join removal? I mean a query before
>>> and after the removal. Thanks,
>> Courtesy Robert Haas:
>>
>> SELECT foo.x, foo.y, foo.z FROM foo WHERE foo.x = bar.x
>>
>> Conditions:
>>
>> 1) foo.x is not null.
> I guess that this is also not needed. you can just remove rows where
>
> foo.x is null
>
> That is, replace the join with "foo.x is not null"
>>
>> 2) foo (x) is a foreign key referencing bar (x).
>>
>> We can ignore bar completely in this case i.e. avoid scanning bar.
>>
>> Regards,
>>
>> Atri
>>
>>
>> --
>> Regards,
>>
>> Atri
>> l'apprenant
>>
>>
>
>
>

I discussed with RhodiumToad and was exploring potential design methods with which we can solve the above problem. My focus is to add support for foreign key detection in planner and allow planner to make decisions based on it.

It wouldn't be too much of a cost to maintain the foreign key column and the referenced table. The main issue, as pointed out by RHodiumToad is primarily that, between the generation of the plan, which is made with accordance to the foreign key presence, and the execution of the plan, we may get into an inconsistent state when the corresponding row is deleted or constraints are changed and fk trigger has not yet run and detected those changes.

I was thinking of row level locks,which are done by the fk trigger.Is there any way we can modify the fk trigger to forcibly run? Or add an 'looked at' bit, which is reset when a table/row is changed, and set by the fk trigger when it runs on that table?

I am just thinking wild here, please let me know your thoughts, feedback and ideas.

Regards,

Atri

On 07/14/2013 06:10 PM, Atri Sharma wrote:
>
> Sent from my iPad
>
> On 10-Jul-2013, at 13:11, Hannu Krosing <hannu(at)2ndQuadrant(dot)com> wrote:
>
>> On 07/10/2013 09:18 AM, Atri Sharma wrote:
>>>> Can you please post an example of such a join removal? I mean a query before
>>>> and after the removal. Thanks,
>>> Courtesy Robert Haas:
>>>
>>> SELECT foo.x, foo.y, foo.z FROM foo WHERE foo.x = bar.x
>>>
>>> Conditions:
>>>
>>> 1) foo.x is not null.
>> I guess that this is also not needed. you can just remove rows where
>>
>> foo.x is null
>>
>> That is, replace the join with "foo.x is not null"
>>> 2) foo (x) is a foreign key referencing bar (x).
>>>
>>> We can ignore bar completely in this case i.e. avoid scanning bar.
>>>
>>> Regards,
>>>
>>> Atri
>>>
>>>
>>> --
>>> Regards,
>>>
>>> Atri
>>> l'apprenant
>>>
>>>
>>
>>
> I discussed with RhodiumToad and was exploring potential design methods with which we can solve the above problem. My focus is to add support for foreign key detection in planner and allow planner to make decisions based on it.
>
> It wouldn't be too much of a cost to maintain the foreign key column and the referenced table. The main issue, as pointed out by RHodiumToad is primarily that, between the generation of the plan, which is made with accordance to the foreign key presence, and the execution of the plan, we may get into an inconsistent state when the corresponding row is deleted or constraints are changed and fk trigger has not yet run and detected those changes.
Is this not all transactional and taken care of by MVCC ?

That is, the problem can only happen for prepared plans, which need
to have invalidation in case of underlaying DDL / schema changes anyway ?

Or are you worried about the case where the FK constraint is delayed and
thus the plan can be invalid between the change and running of FK trigger
in the same transaction ?
>
> I was thinking of row level locks,which are done by the fk trigger.Is there any way we can modify the fk trigger to forcibly run? Or add an 'looked at' bit, which is reset when a table/row is changed, and set by the fk trigger when it runs on that table?
>
> I am just thinking wild here, please let me know your thoughts, feedback and ideas.
>
> Regards,
>
> Atri

--
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ

Sent from my iPad

On 14-Jul-2013, at 22:12, Hannu Krosing <hannu(at)2ndQuadrant(dot)com> wrote:

> On 07/14/2013 06:10 PM, Atri Sharma wrote:
>>
>> Sent from my iPad
>>
>> On 10-Jul-2013, at 13:11, Hannu Krosing <hannu(at)2ndQuadrant(dot)com> wrote:
>>
>>> On 07/10/2013 09:18 AM, Atri Sharma wrote:
>>>>> Can you please post an example of such a join removal? I mean a query before
>>>>> and after the removal. Thanks,
>>>> Courtesy Robert Haas:
>>>>
>>>> SELECT foo.x, foo.y, foo.z FROM foo WHERE foo.x = bar.x
>>>>
>>>> Conditions:
>>>>
>>>> 1) foo.x is not null.
>>> I guess that this is also not needed. you can just remove rows where
>>>
>>> foo.x is null
>>>
>>> That is, replace the join with "foo.x is not null"
>>>> 2) foo (x) is a foreign key referencing bar (x).
>>>>
>>>> We can ignore bar completely in this case i.e. avoid scanning bar.
>>>>
>>>> Regards,
>>>>
>>>> Atri
>>>>
>>>>
>>>> --
>>>> Regards,
>>>>
>>>> Atri
>>>> l'apprenant
>> I discussed with RhodiumToad and was exploring potential design methods with which we can solve the above problem. My focus is to add support for foreign key detection in planner and allow planner to make decisions based on it.
>>
>> It wouldn't be too much of a cost to maintain the foreign key column and the referenced table. The main issue, as pointed out by RHodiumToad is primarily that, between the generation of the plan, which is made with accordance to the foreign key presence, and the execution of the plan, we may get into an inconsistent state when the corresponding row is deleted or constraints are changed and fk trigger has not yet run and detected those changes.
> Is this not all transactional and taken care of by MVCC ?
>
> That is, the problem can only happen for prepared plans, which need
> to have invalidation in case of underlaying DDL / schema changes anyway ?
>
> Or are you worried about the case where the FK constraint is delayed and
> thus the plan can be invalid between the change and running of FK trigger
> in the same transaction ?

Yes, that is precisely what I am concerned about.Thanks for wording it so nicely!

Regards,

Atri
>