Do not scan index in right table if condition for left join evaluates to false using columns in left table
Илья Жарков <izharkov1243@gmail.com>
From: Илья Жарков <izharkov1243@gmail.com>
To: pgsql-hackers@lists.postgresql.org
Date: 2024-12-07T18:30:46Z
Lists: pgsql-hackers
Commits
Same data as JSON:
GET /api/v1/messages/:b64id/commits
the thread's linked commits as JSON, with link sources.
API reference →
-
Rewrite maybe_reread_subscription() comment
- 3191eccd8a9b 18.0 cited
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