Re: Removing INNER JOINs

On 1 December 2017 at 15:30, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> On 1 December 2017 at 12:20, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> wrote:
>> The large hurdle which a good workaround was never really found for
>> was the fact that foreign key triggers only update the referenced rows
>> at the end of the statement, or end of query when the foreign key
>> constraint is deferred. I don't recall much concern about planner
>> overhead. It's likely not going to be too big a concern since we're
>> already checking for foreign keys nowadays during selectivity
>> estimation.
>>
>> I do still have all the code I wrote all those years ago, and possibly
>> it will still apply to master as I rebased it just several months ago.
>> I've just not yet come up with any bright ideas on how to solve the
>> foreign key trigger timing problem.
>
> So it would work if the Foreign Keys are marked NOT DEFERRABLE?

Unfortunately not, since a query may call some volatile function which
makes changes to referenced rows which could cause the removed join to
falsely "match" to referenced rows which no longer exist due to the
changes made by the volatile function not having been CASCADEd to the
referenced table yet.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services