Re: Patch to support SEMI and ANTI join removal

On 2014-09-28 17:32:21 +1300, David Rowley wrote:
> My understanding of foreign keys is that any pending foreign key triggers
> will be executed just before the query completes, so we should only ever
> encounter pending foreign key triggers during planning when we're planning
> a query that's being executed from somewhere like a volatile function or
> trigger function, if the outer query has updated or deleted some records
> which are referenced by a foreign key.

Note that foreign key checks also can be deferred. So the window for
these cases is actually larger.

> So I think with the check for pending triggers at planning time this is
> safe at least for queries being planned right before they're executed, but
> you've caused me to realise that I'll probably need to do some more work on
> this for when it comes to PREPARE'd queries, as it looks like if we
> executed a prepared query from inside a volatile function or trigger
> function that was called from a DELETE or UPDATE statement that caused
> foreign key triggers to be queued, and we'd happened to have removed some
> INNER JOINs when we originally planned that prepare statement, then that
> would be wrong.

I'm wondering whether this wouldn't actually be better handled by some
sort of 'one time filter' capability for joins. When noticing during
planning that one side of the join is nullable attach a check to the
join node. Then, whenever that check returns true, skip checking one
side of the join and return rows without looking at that side.

That capability might also be interesting for more efficient planning of
left joins that partially have a constant join expression.

Greetings,

Andres Freund

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