Thread: 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.jiit@gmail.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.jiit@gmail.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@heroku.com> wrote: > On Mon, Jul 8, 2013 at 11:20 PM, Atri Sharma <atri.jiit@gmail.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@2ndquadrant.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.jiit@gmail.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
--
Best Wishes,
Ashutosh Bapat
EntepriseDB Corporation
The Postgres Database Company
Ashutosh Bapat
EntepriseDB Corporation
The Postgres Database Company
On Wed, Jul 10, 2013 at 8:29 AM, Ashutosh Bapat <ashutosh.bapat@enterprisedb.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@2ndquadrant.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@2ndQuadrant.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 focusis 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 pointedout by RHodiumToad is primarily that, between the generation of the plan, which is made with accordance to the foreignkey presence, and the execution of the plan, we may get into an inconsistent state when the corresponding row is deletedor 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 forciblyrun? Or add an 'looked at' bit, which is reset when a table/row is changed, and set by the fk trigger when it runson 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@2ndQuadrant.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 focusis 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 pointedout by RHodiumToad is primarily that, between the generation of the plan, which is made with accordance to the foreignkey presence, and the execution of the plan, we may get into an inconsistent state when the corresponding row is deletedor 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 forciblyrun? Or add an 'looked at' bit, which is reset when a table/row is changed, and set by the fk trigger when it runson 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@2ndQuadrant.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@2ndQuadrant.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. Myfocus 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 pointedout by RHodiumToad is primarily that, between the generation of the plan, which is made with accordance to the foreignkey presence, and the execution of the plan, we may get into an inconsistent state when the corresponding row is deletedor 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 >