Re: Patch to support SEMI and ANTI join removal

On 2014-09-29 22:42:57 +1300, David Rowley wrote:
> On Mon, Sep 29, 2014 at 2:41 AM, Andres Freund <andres(at)2ndquadrant(dot)com>
> wrote:
>
> > 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.
> >
> >
> Thanks Andres, I know you had said this before but I had previously failed
> to realise exactly what you meant. I thought you were talking about
> defining a foreign key to reference a column that has a deferrable unique
> index. I now realise you were talking about making the foreign key itself
> as deferrable.

Oh. Don't remember doing that ;)

> I've made a change to the patch locally to ignore foreign
> keys that are marked as deferrable.

I have serious doubts about the general usefulness if this is onlyu
going to be useable in a very restricted set of circumstances (only one
time plans, no deferrable keys). I think it'd be awesome to have the
capability, but I don't think it's ok to restrict it that much.

To me that means you can't make the decision at plan time, but need to
move it to execution time. It really doesn't sound that hard to short
circuit the semi joins whenever, at execution time, there's no entries
in the deferred trigger queue. It's a bit annoying to have to add code
to all of nestloop/hashjoin/mergejoin to not check the outer tuple if
there's no such entry. But I don't think it'll be too bad. That'd mean
it can be used in prepared statements.

What I think would be a bit finnicky is the costing...

Greetings,

Andres Freund

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