Thread

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

Andrei Lepikhov <lepihov@gmail.com> — 2026-05-27T10:11:17Z

Hi,

I think we should continue this discussion.

Andreas' idea for a gating Result node seems more interesting than the original
approach and is also looks less invasive.

Here is an updated patch that outlines the solution. It still needs to fix some
issues, benchmarking, and write more tests, but the results so far look
promising. For example, in the reproduction script provided in this thread
earlier, it greatly cuts down the number of inner table scans:

 Nested Loop Left Join (actual rows=10036.00 loops=1)
   ->  Nested Loop Left Join (actual rows=10036.00 loops=1)
         ->  Seq Scan on products p (actual rows=10036.00 loops=1)
               Filter: (price > '0.9'::double precision)
               Rows Removed by Filter: 89964
         ->  Result (actual rows=0.50 loops=10036)
               One-Time Filter: (p.type = 'p'::text)
               ->  Index Scan using phones_pkey on phones ph
			(actual rows=1.00 loops=5026)
                     Index Cond: (id = p.id)
                     Index Searches: 5026
   ->  Result (actual rows=0.50 loops=10036)
         One-Time Filter: (p.type = 'v'::text)
         ->  Index Scan using vehicles_pkey on vehicles v
		(actual rows=1.00 loops=5010)
               Index Cond: (id = p.id)
               Index Searches: 5010

The main question for me now is: is it possible to introduce gating into the
optimisation stage and let the planner pick a 'gated' nest loop based on cost
estimates? To do that, we need to introduce one more node, such as GatingPath,
or change ProjectionPath slightly. As this is a more invasive approach, I prefer
the current one unless evidence emerges that a Path machinery change would be
beneficial for something else.

-- 
regards, Andrei Lepikhov,
pgEdge