Re: Removing Inner Joins

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
>