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.
To reproduce the problem see 'problem.sql' in attachment:
Out of explain analyze see in 'problem_explain.txt'
As you can see, executor scan each of 1e5 outer tuples despite the fact
that inner can't return any tuples.
Teodor Sigaev and I developed a patch to solve this problem. Result of
explain analyze procedure can be found in the 'optimized_execution.txt'.
--
Andrey Lepikhov
Postgres Professional
https://postgrespro.com
The Russian Postgres Company