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

Поиск
Список
Период
Сортировка
От Илья Жарков
Тема Do not scan index in right table if condition for left join evaluates to false using columns in left table
Дата
Msg-id CAKE=rqQ-LHuh2eVsKC7ihkRJoCBZafSR72o3Xk4Xb=LcQMQfsA@mail.gmail.com
обсуждение исходный текст
Ответы Re: Do not scan index in right table if condition for left join evaluates to false using columns in left table
Список 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

В списке pgsql-hackers по дате отправления: