Thread: inequality predicate not pushed down in JOIN?
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-
On 12/7/2024 06:31, Paul George wrote: > 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. It has already discussed at least couple of years ago, see [1]. Summarising, it is more complicated when equivalences and wastes CPU cycles more probably than helps. > > 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) [1] Condition pushdown: why (=) is pushed down into join, but BETWEEN or >= is not? https://www.postgresql.org/message-id/flat/CAFQUnFhqkWuPCwQ1NmHYrisHJhYx4DoJak-dV%2BFcjyY6scooYA%40mail.gmail.com -- regards, Andrei Lepikhov
Cool! Thanks for the speedy reply, link, and summary! I'm not sure how I missed this, but apologies for the noise.
-Paul-
On Thu, Jul 11, 2024 at 4:49 PM Andrei Lepikhov <lepihov@gmail.com> wrote:
On 12/7/2024 06:31, Paul George wrote:
> 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.
It has already discussed at least couple of years ago, see [1].
Summarising, it is more complicated when equivalences and wastes CPU
cycles more probably than helps.
>
> 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)
[1] Condition pushdown: why (=) is pushed down into join, but BETWEEN or
>= is not?
https://www.postgresql.org/message-id/flat/CAFQUnFhqkWuPCwQ1NmHYrisHJhYx4DoJak-dV%2BFcjyY6scooYA%40mail.gmail.com
--
regards, Andrei Lepikhov
While applying transitivity to non-equality conditions is less frequently beneficial than applying it to equality conditions, it can be very helpful, especially with third party apps and dynamically changing data. One possible implementation to avoid the mentioned overhead would be to mark the internally generated predicate(s) as potentially redundant and discard it on the inner table of the join after planning (and enhance the optimizer to recognize redundant predicates and adjust accordingly when costing).
Jerry
On Thu, Jul 11, 2024 at 5:16 PM Paul George <p.a.george19@gmail.com> wrote:
Cool! Thanks for the speedy reply, link, and summary! I'm not sure how I missed this, but apologies for the noise.-Paul-On Thu, Jul 11, 2024 at 4:49 PM Andrei Lepikhov <lepihov@gmail.com> wrote:On 12/7/2024 06:31, Paul George wrote:
> 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.
It has already discussed at least couple of years ago, see [1].
Summarising, it is more complicated when equivalences and wastes CPU
cycles more probably than helps.
>
> 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)
[1] Condition pushdown: why (=) is pushed down into join, but BETWEEN or
>= is not?
https://www.postgresql.org/message-id/flat/CAFQUnFhqkWuPCwQ1NmHYrisHJhYx4DoJak-dV%2BFcjyY6scooYA%40mail.gmail.com
--
regards, Andrei Lepikhov