Re: Removing Inner Joins

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