Re: Allowing join removals for more join types

David Rowley <dgrowley(at)gmail(dot)com> writes:
> On 9 July 2014 09:27, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> On review it looks like analyzejoins.c would possibly benefit from an
>> earlier fast-path check as well.

> Do you mean for non-subqueries? There already is a check to see if the
> relation has no indexes.

Oh, sorry, that was a typo: I meant to write pathnode.c. Specifically,
we could skip the translate_sub_tlist step. Admittedly that's not
hugely expensive, but as long as we have the infrastructure for a quick
check it might be worth doing.

>> TBH I find the checks for FOR UPDATE and volatile functions to be
>> questionable as well.

> Well, that's a real tough one for me as I only added that based on what you
> told me here:
>> I doubt you should drop a subquery containing FOR UPDATE, either.
>> That's a side effect, just as much as a volatile function would be.

Hah ;-). But the analogy to qual pushdown hadn't occurred to me at the
time.

> As far as I know the FOR UPDATE check is pretty much void as of now anyway,
> since the current state of query_is_distinct_for() demands that there's
> either a DISTINCT, GROUP BY or just a plain old aggregate without any
> grouping, which will just return a single row, neither of these will allow
> FOR UPDATE anyway.

True.

> So the effort here should be probably be more focused on if we should allow
> the join removal when the subquery contains volatile functions. We should
> probably think fairly hard on this now as I'm still planning on working on
> INNER JOIN removals at some point and I'm thinking we should likely be
> consistent between the 2 types of join for when it comes to FOR UPDATE and
> volatile functions, and I'm thinking right now that for INNER JOINs that,
> since they're INNER that we could remove either side of the join. In that
> case maybe it would be harder for the user to understand why their volatile
> function didn't get executed.

Color me dubious. In exactly what circumstances would it be valid to
suppress an inner join involving a sub-select?

regards, tom lane