Re: Do not scan index in right table if condition for left join evaluates to false using columns in left table

On 01/09/2026 11:16, Andrei Lepikhov wrote:
> On 28/07/2026 10:13, Andrei Lepikhov wrote:
>
> Just a rebase onto current master
>

Another rebase to follow the master branch.

I have also looked at the downside of the opportunistic approach this feature
takes. The pros are obvious: zero planning overhead, no search-space blowup, and
simple code. But what does the worst case look like?

By gating the inner side, we add one more node (Result) and force the gating
qual to be evaluated once per outer row. The vanilla NestLoop sometimes does not
do that: when the inner side returns no tuple, a LEFT join goes straight to the
null-fill path and ExecQual(joinqual) is never reached at all. So the gated plan
pays for the clause on every outer row. I think a parameterised index scan that
finds no match is the common case for this behaviour.

The worst query therefore looks like this:

CREATE TABLE o (a int, t text);
INSERT INTO o SELECT g, 'row'||g FROM generate_series(1,1000000) g;
CREATE TABLE i (a int, b int);
ANALYZE o, i;

SET enable_hashjoin = off;
SET enable_mergejoin = off;
SET max_parallel_workers_per_gather = 0;
\timing on

SELECT count(*) FROM ( SELECT * FROM o
LEFT JOIN i ON (md5(md5(md5(md5(o.t)))) <> 'z')) s;

Making the gate progressively more expensive shows what is going on.
On the current master, I see ~470ms, with the gating clause ~1500ms. This effect
is linear but isn't limited; it depends on the number of evaluations and the
outer size.

The basic case, when each row passes the gate (and correspondent join clause)
doesn't show any degradation, there are no significant difference between gated
and no-gated approach. To reproduce just add one row into the inner table and
re-execute the query above:

INSERT INTO i (a,b) VALUES (1, 1);

My conclusion is this. A gated NestLoop usually behaves like a normal NestLoop
adding benefit of reduced inner scans or nothing. In the degenerate case where
the inner side returns nothing, it adds an overhead proportional to the outer
size times the cost of the gating clause, with no ceiling. Separately, the
Result projection adds a per-joined- tuple cost that grows with the width of the
inner targetlist.

I do not see this as a fundamental problem, except for SubPlan cases - but
SubPlan is a different animal, and every new correlated pull-up technique
attracts complaints of exactly this shape.

So the real question is whether the cost model can account for these two terms
well enough to choose between a gated and a non-gated NestLoop, assuming we turn
this into a cost-based optimisation.

--
regards, Andrei Lepikhov,
pgEdge