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

On 2024-12-07 16:37:32 -0500, Andres Freund wrote:
> On 2024-12-07 21:30:46 +0300, Илья Жарков wrote:
> > Note that the only record in *parent *table has dtype == 'A', but the join
> > condition has p.dtype = 'B'.
> > The query plan still shows Index Only Scan on *child *table with loops=1.
>
> The relevant difference between the inner and left join is that for the inner
> join we push down the p.dtype = 'B'::text condition down. However, we do *not*
> do so for outer joins.
>
> Outer:
> ┌───────────────────────────────────────────────────┐
> │ QUERY PLAN │
> ├───────────────────────────────────────────────────┤
> │ Nested Loop Left Join │
> │ Join Filter: (p.dtype = 'B'::text) │
> │ -> Index Scan using parent_pkey on parent p │
> │ Index Cond: (id = 1) │
> │ -> Index Only Scan using child_pkey on child c │
> │ Index Cond: (id = 1) │
> └───────────────────────────────────────────────────┘
>
> Inner:
> ┌───────────────────────────────────────────────────┐
> │ QUERY PLAN │
> ├───────────────────────────────────────────────────┤
> │ Nested Loop │
> │ -> Index Scan using parent_pkey on parent p │
> │ Index Cond: (id = 1) │
> │ Filter: (dtype = 'B'::text) │
> │ -> Index Only Scan using child_pkey on child c │
> │ Index Cond: (id = 1) │
> └───────────────────────────────────────────────────┘
>
>
> We *do* have code that recognizes the case where a clause in a join's ON only
> references the nullable side. We however don't have code that recognizes the
> same if it's the non-nullable side.
>
> That's somewhat surprising, but it does kinda make sense: A after all, in a
> query like yours, you could just have had the p.dtype = 'B' in the WHERE list,
> rather than inside the join's ON. The same isn't true for the nullable side of
> the join, as a condition for te nullable side in the WHERE clause breaks the
> join's "outerness".
>
> I.e. you can write your query as
> SELECT * FROM parent p LEFT JOIN child c ON p.id = c.id WHERE p.id = 1 AND p.dtype = 'B';
> in which case you get the expected query plan:
> ┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
> │ QUERY PLAN │
> ├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
> │ Nested Loop Left Join (cost=0.31..16.36 rows=1 width=40) (actual time=0.035..0.036 rows=0 loops=1) │
> │ -> Index Scan using parent_pkey on parent p (cost=0.15..8.17 rows=1 width=36) (actual time=0.034..0.034 rows=0 loops=1) │
> │ Index Cond: (id = 1) │
> │ Filter: (dtype = 'B'::text) │
> │ Rows Removed by Filter: 1 │
> │ -> Index Only Scan using child_pkey on child c (cost=0.15..8.17 rows=1 width=4) (never executed) │
> │ Index Cond: (id = 1) │
> │ Heap Fetches: 0 │
> │ Planning Time: 29.912 ms │
> │ Execution Time: 0.095 ms │
> └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

Ah, wait - that's not actually equivalent. When p.dtype = 'B' inthe ON clause,
we still produce an output row, but not if in the WHERE clause.

So:

> ISTM that it shouldn't be expensive to recognize this type of join clause and
> pushes them down. While it could be done by the query's author, it seems worth
> handling this on our side. But maybe I'm missing something here?

yes, I was missing something, it would not be a valid transformation.

I guess it's still somewhat silly that we do an index scan for the inner side,
even though we could know that we'll always fail to the joinqual. We could
evaluate the qual after fetching the outer row, before fetching the matching
inner row.

It's might not be worth adding code to handle such cases to the the nested
loop code, this is probably not that common a query pattern. If we don't want
to have explict execution time code paths, we could emit a "constant
qualification" Result node above the inner side of a parametrized nested loop?

Greetings,

Andres Freund