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
