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-

Reply via email to