inequality predicate not pushed down in JOIN?

From: Paul George <p(dot)a(dot)george19(at)gmail(dot)com>
To: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: inequality predicate not pushed down in JOIN?
Date: 2024-07-11 23:31:24
Message-ID: CALA8mJr2zKNBMqD=f-Ts-2CYtOUyvFJ7Z2wQVe3wkOnqZL1BJw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hey!

[version: PostgreSQL 16.3]

In the example below, I noticed that the JOIN predicate "t1.a<1" is not
pushed down to the scan over "t2", though it superficially seems like it
should be.

create table t as (select 1 a);
analyze t;
explain (costs off) select * from t t1 join t t2 on t1.a=t2.a and t1.a<1;
QUERY PLAN
-------------------------------
Hash Join
Hash Cond: (t2.a = t1.a)
-> Seq Scan on t t2
-> Hash
-> Seq Scan on t t1
Filter: (a < 1)
(6 rows)

The same is true for the predicate "t1.a in (0, 1)". For comparison, the
predicate "t1.a=1" does get pushed down to both scans.

explain (costs off) select * from t t1 join t t2 on t1.a=t2.a and t1.a=1;
QUERY PLAN
-------------------------
Nested Loop
-> Seq Scan on t t1
Filter: (a = 1)
-> Seq Scan on t t2
Filter: (a = 1)
(5 rows)

-Paul-

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Andrei Lepikhov 2024-07-11 23:49:34 Re: inequality predicate not pushed down in JOIN?
Previous Message Andrei Lepikhov 2024-07-11 15:34:54 Re: How to solve my slow disk i/o throughput during index scan