Re: Optimization of NestLoop join in the case of guaranteed empty inner subtree

Andrey Lepikhov <a(dot)lepikhov(at)postgrespro(dot)ru> writes:
> During NestLoop execution we have bad corner case: if outer subtree
> contains tuples the join node will scan inner subtree even if it does
> not return any tuples.

So the first question about corner-case optimizations like this is always
"how much overhead does it add in the normal case where it fails to gain
anything?". I see no performance numbers in your proposal.

I do not much like anything about the code, either: as written it's
only helpful for an especially narrow corner case (so narrow that
I wonder if it really ever helps at all: surely calling a nodeMaterial
whose tuplestore is empty doesn't cost much). But that doesn't stop it
from adding a bool to the generic PlanState struct, with global
implications. What I'd expected from your text description is that
nodeNestLoop would remember whether its inner child had returned zero rows
the first time, and assume that subsequent executions could be skipped
unless the inner child's parameters change.

regards, tom lane