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