Hi, could you help to understand why Postgres scans index in right table in the following case:
CREATE TABLE parent (
id integer PRIMARY KEY,
dtype text
);
CREATE TABLE child (
id integer PRIMARY KEY
);
INSERT INTO parent (id, dtype) values (1, 'A');
INSERT INTO child (id) values (1);
EXPLAIN ANALYZE
SELECT *
FROM parent p
LEFT JOIN child c
ON p.id = c.id
AND p.dtype = 'B'
WHERE p.id = 1;
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.
Nested Loop Left Join (cost=0.31..16.36 rows=1 width=40) (actual time=0.104..0.107 rows=1 loops=1)
Join Filter: (p.dtype = 'B'::text)
Rows Removed by Join Filter: 1
-> Index Scan using parent_pkey on parent p (cost=0.15..8.17 rows=1 width=36) (actual time=0.018..0.019 rows=1 loops=1)
Index Cond: (id = 1)
-> Index Only Scan using child_pkey on child c (cost=0.15..8.17 rows=1 width=4) (actual time=0.078..0.080 rows=1 loops=1)
Index Cond: (id = 1)
Heap Fetches: 1
In comparison, if using INNER JOIN, Index Only Scan on child table is never executed.
Tested on PostgreSQL 17.2
