Re: Patch to support SEMI and ANTI join removal

On Tue, Sep 30, 2014 at 12:42 AM, Andres Freund <andres(at)2ndquadrant(dot)com>
wrote:

> On 2014-09-29 22:42:57 +1300, David Rowley wrote:
>
> > 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.
>
>
I had a look to see what Oracle does in this situation and I was quite
shocked to see that they're blatantly just ignoring the fact that the
foreign key is being deferred. I tested by deferring the foreign key in a
transaction then updating the referenced record and I see that Oracle just
return the wrong results as they're just blindly removing the join. So it
appears that they've not solved this one very well.

> 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.
>
>
I'm starting to think about how this might be done, but I'm a bit confused
and I don't know if it's something you've overlooked or something I've
misunderstood.

I've not quite gotten my head around how we might stop the unneeded
relation from being the primary path to join the other inner relations,
i.e. what would stop the planner making a plan that hashed all the other
relations and planned to perform a sequence scan on the relation that we
have no need to scan (because the foreign key tells us the join is
pointless). If we were not use use that relation then we'd just have a
bunch of hash tables with no path to join them up. If we did anything to
force the planner into creating a plan that would suit skipping relations,
then we could possibly be throwing away the optimal plan..... Right?

Regards

David Rowley