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)
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)
QUERY PLAN
-------------------------
Nested Loop
-> Seq Scan on t t1
Filter: (a = 1)
-> Seq Scan on t t2
Filter: (a = 1)
(5 rows)
-Paul-
