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

From: Илья Жарков <izharkov1243(at)gmail(dot)com>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Do not scan index in right table if condition for left join evaluates to false using columns in left table
Date: 2024-12-07 18:30:46
Message-ID: CAKE=rqQ-LHuh2eVsKC7ihkRJoCBZafSR72o3Xk4Xb=LcQMQfsA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Jeff Davis 2024-12-07 19:27:51 Re: Statistics Import and Export
Previous Message Jack Bay 2024-12-07 16:51:54 Re: Support for unsigned integer types