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.georg...@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 <lepi...@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
>> <https://www.postgresql.org/message-id/flat/CAFQUnFhqkWuPCwQ1NmHYrisHJhYx4DoJak-dV%2BFcjyY6scooYA%40mail.gmail.com>
>>
>> --
>> regards, Andrei Lepikhov
>>
>>

Reply via email to