Re: Condition pushdown: why (=) is pushed down into join, but BETWEEN or >= is not?

From: Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com>
To: Tomas Vondra <tomas(dot)vondra(at)enterprisedb(dot)com>
Cc: Justin Pryzby <pryzby(at)telsasoft(dot)com>, David Rowley <dgrowleyml(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Dmitry Astapov <dastapov(at)gmail(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Condition pushdown: why (=) is pushed down into join, but BETWEEN or >= is not?
Date: 2022-02-11 05:32:20
Message-ID: CAKU4AWrdeQZ8xvf=DVhndUs=RGn8oVoSJvYK3Yj7uWq2=dt=Mg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

>
>
>
> > Of course, this breaks the estimates in the faster query, because we now
> > apply the condition twice - once for the index scan, one as the join
> > clause. So instead of ~100k rows the join is estimated as ~1000 rows.
>
> I think my patch has addressed this. Here is the example: ...
>

>
So I think knowing what bad it is to have this feature is the key point to
> discussion now.
>
> [1]
> https://www.postgresql.org/message-id/CAKU4AWpo9z0hMHDWUKuce4Z-NpcybV0J2UVu5%2BDVwyP-CrHCQg%40mail.gmail.com
>
>
>
I forgot to upload these patches, upload them now.

--
Best Regards
Andy Fan

Attachment Content-Type Size
v2-0005-Support-ScalarArrayOpExpr-and-perudoconstant-on-e.patch application/octet-stream 8.1 KB
v2-0003-Introduce-RelOptInfo.filtered_rows.patch application/octet-stream 3.5 KB
v2-0002-After-distributing-the-new-derived-RestrictInfo-i.patch application/octet-stream 12.6 KB
v2-0004-Remove-duplicated-qual-executing-for-executor.patch application/octet-stream 4.9 KB
v2-0001-Introudce-ec_filters-in-EquivalenceClass-struct-t.patch application/octet-stream 50.4 KB
v2-0006-Added-the-testcase-for-this-feature-and-fix-the-p.patch application/octet-stream 36.4 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Andres Freund 2022-02-11 05:56:09 Re: Race condition in TransactionIdIsInProgress
Previous Message Julien Rouhaud 2022-02-11 05:03:07 Re: Is there any documentation on how to correctly create extensions in HA(primary-standby) setup?