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?