public inbox for [email protected]
help / color / mirror / Atom feedinequality predicate not pushed down in JOIN?
4+ messages / 3 participants
[nested] [flat]
* inequality predicate not pushed down in JOIN?
@ 2024-07-11 23:31 Paul George <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Paul George @ 2024-07-11 23:31 UTC (permalink / raw)
To: [email protected]
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-
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: inequality predicate not pushed down in JOIN?
@ 2024-07-11 23:49 Andrei Lepikhov <[email protected]>
parent: Paul George <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Andrei Lepikhov @ 2024-07-11 23:49 UTC (permalink / raw)
To: Paul George <[email protected]>; [email protected]
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%40m...
--
regards, Andrei Lepikhov
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: inequality predicate not pushed down in JOIN?
@ 2024-07-12 00:07 Paul George <[email protected]>
parent: Andrei Lepikhov <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Paul George @ 2024-07-12 00:07 UTC (permalink / raw)
To: Andrei Lepikhov <[email protected]>; +Cc: [email protected]
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 <[email protected]> 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%40m...
>
> --
> regards, Andrei Lepikhov
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: inequality predicate not pushed down in JOIN?
@ 2024-07-12 00:58 Jerry Brenner <[email protected]>
parent: Paul George <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Jerry Brenner @ 2024-07-12 00:58 UTC (permalink / raw)
To: Paul George <[email protected]>; +Cc: Andrei Lepikhov <[email protected]>; [email protected]
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 <[email protected]> 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 <[email protected]> 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%40m...
>> <https://www.postgresql.org/message-id/flat/CAFQUnFhqkWuPCwQ1NmHYrisHJhYx4DoJak-dV%2BFcjyY6scooYA%40m...;
>>
>> --
>> regards, Andrei Lepikhov
>>
>>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-07-12 00:58 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-07-11 23:31 inequality predicate not pushed down in JOIN? Paul George <[email protected]>
2024-07-11 23:49 ` Andrei Lepikhov <[email protected]>
2024-07-12 00:07 ` Paul George <[email protected]>
2024-07-12 00:58 ` Jerry Brenner <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox