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

On 9/12/2026 7:02 AM, Andrei Lepikhov wrote:
> 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.
>
I've continued doing security audits of commitfest patches, and as part
of that I occasionally turn up differences in output that aren't
security issues but seemed worth passing along. v3 has a few. They all
show up on the gated nestloop plan, so I forced it with:

set enable_hashjoin = off;
set enable_mergejoin = off;

1) A left join that returns a row on HEAD raises an error with the patch:

create table o (k int, x int); insert into o values (1, 0);
create table i (k int); -- empty
select * from o left join i on o.k = i.k and 1/o.x = 1;
-- HEAD: (1, 0, NULL)
-- v3: ERROR: division by zero

2) A volatile condition returns a different number of rows:

create table a (k int, x int); insert into a values (1, 2);
create table b (k int, y int); insert into b values
(1,1),(1,2),(1,3),(1,4);
create sequence s;
select a.k, b.y from a left join b on a.k = b.k and a.x > nextval('s');
-- HEAD: 1 row
-- v3: 4 rows

3) The same when the volatile is inside a sub-select:

alter sequence s restart;
select a.k, b.y from a left join b
on a.k = b.k and a.x > (select nextval('s') where a.k = 1);
-- HEAD: 1 row
-- v3: 4 rows

On HEAD I get the same answers under hash and merge joins as well, so
these aren't just plan-to-plan variation. I haven't worked out whether
any of them matter for what you're doing here. Passing them along in
case they do.

--
Bryan Green
EDB: https://www.enterprisedb.com