Re: Patch to support SEMI and ANTI join removal

On 2014-09-30 23:25:45 +1300, David Rowley wrote:
> On Tue, Sep 30, 2014 at 12:42 AM, Andres Freund <andres@2ndquadrant.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.

Ick. I'm pretty strongly against going that way.

> > 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?

I'm not 100% sure I understand your problem description, but let me
describe how I think this would work. During planning, you'd emit the
exactly same plan as you'd today, with two exceptions:
a) When costing a node where one side of a join is very likely to be  removable, you'd cost it nearly as if there
wasn'ta join.
 
b) The planner would attach some sort of 'one time join qual' to the  'likely removable' join nodes. If, during
executorinit, that qual  returns false, simply don't perform the join. Just check the inner  relation, but entirely
skipthe outer relation.
 

With regard to your comment hash tables that aren't joined up: Currently
hash tables aren't built if they're not used. I.e. it's not
ExecInitHash() that does the hashing, but they're generally only built
when needed. E.g. nodeHashJoin.c:ExecHashJoin() only calls
MultiExecProcNode() when in the HJ_BUILD_HASHTABLE state - which it only
initially and sometimes after rescans is.

Does that clear things up or have I completely missed your angle?

Greetings,

Andres Freund

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