Re: Removing Inner Joins

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Ü