Sent from my iPad
On 14-Jul-2013, at 22:12, Hannu Krosing <ha...@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 <ha...@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 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 > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
