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
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 |