Re: Do not scan index in right table if condition for left join evaluates to false using columns in left table - Mailing list pgsql-hackers

Regarding merge joins, I suppose in some edge cases inner set scan might not even be started.

FROM parent p
LEFT JOIN child c
  ON p.id = c.id
  AND p.dtype = 'B'

        ┌───┐ ┌───┐
parent  │1,A│ │2,A│
        └───┘ └───┘
       ^    
        ┌───┐ ┌───┐
child   │ 1 │ │ 2 │
        └───┘ └───┘
       ^

If p.dtype = 'B' was evaluated early, the pointer could move through the outer set as long as it is evaluated to false.
In the above example, it reaches the end without even accessing the inner set.

        ┌───┐ ┌───┐
parent  │1,A│ │2,A│
        └───┘ └───┘
                   ^
        ┌───┐ ┌───┐
child   │ 1 │ │ 2 │
        └───┘ └───┘
       ^

In the opposite scenario:

        ┌───┐ ┌───┐
parent  │1,B│ │2,A│
        └───┘ └───┘
       ^    
        ┌───┐ ┌───┐
child   │ 1 │ │ 2 │
        └───┘ └───┘
       ^

it would need to start moving the inner pointer at some point.

        ┌───┐ ┌───┐
parent  │1,B│ │2,A│
        └───┘ └───┘
                   ^
        ┌───┐ ┌───┐
child   │ 1 │ │ 2 │
        └───┘ └───┘
             ^    

But even in this case, the pointer may not reach the end in the inner set.

Though this highly depends on how merge join is implemented in the Postgres code. I have to admit that I have a very vague idea on this...