pg.ddx.io pgsql-bugs@postgresql.org mailing list archive help / color / mirror / Atom feed From: Andy Fan <zhihuifan1213@163.com>
To: Richard Guo <guofenglinux@gmail.com>
Cc: David Rowley <dgrowleyml@gmail.com>
Cc: Andrei Lepikhov <a.lepikhov@postgrespro.ru>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: Removing const-false IS NULL quals and redundant IS NOT NULL quals
Date: Fri, 29 Dec 2023 09:25:09 +0800
Message-ID: <87edf5vl6u.fsf@163.com> (raw )
In-Reply-To: <CAMbWs4_SVvKOeOj62Ty4z2jtFdnwN_0nsV6KA2F9arvh5rmyVA@mail.gmail.com >
References: <17540-7aa1855ad5ec18b4@postgresql.org >
<CAApHDvqRR81Dvur_zcrY+RsKxv_e8uDpJ2xxAPMmnfgDY7VNew@mail.gmail.com >
<2732256.1657163219@sss.pgh.pa.us >
<CAApHDvqhr1eU9v42w5gt_gL=G15pB9RBpAH_Jmg7eDU6iZ_2zg@mail.gmail.com >
<2740652.1657165848@sss.pgh.pa.us >
<CAApHDvqHCNKJi9CrQZG-reQDXTfRWnT5rhzNtDQhnrBzAAusfA@mail.gmail.com >
<CAMbWs48fiio5RcsBHztjNXXYvU4QKTekbszECs0ydpY2faqo9A@mail.gmail.com >
<CAMbWs49E2Ps7xdPNTAQOuDg6B9RAidcgQ2S4_nEdveSg_GqiDg@mail.gmail.com >
<CAApHDvr+70jEZpraMZ8sj=UH77dBzCaeUc9EcJHB3YaxX9syNg@mail.gmail.com >
<CAMbWs4-cWwe0whACTuKTLT+4jNcSJ+RLEcSp4us1vBAt0k7GAA@mail.gmail.com >
<CAApHDvpkfS1hY3P4DWbOw6WCgRrja=yDLoEz+5g+E2z19Upsrg@mail.gmail.com >
<CAMbWs48krRDsB6PbG1DVjQLoYbmFSotMt-HnR3Cphsn78+yY9g@mail.gmail.com >
<CAApHDvpsgqopTiEFv5HoMkevAmxKapwxm6188U=KthZWWfAQ=w@mail.gmail.com >
<CAMbWs4-eNVNTNc94eF+O_UwHYKv43vyMurhcdqMV=Ht5fehcOg@mail.gmail.com >
<74a806ed-3b6c-4c45-a67a-27b5f3496ae5@postgrespro.ru >
<CAMbWs4-zYenAVp-fmgtvBtWKoauL8gBqUHctP6rOmny5Ox8SkA@mail.gmail.com >
<CAApHDvozMuFm44EoNKuckyDFHyca8cmWk_u5_BDvrsFcG90rsQ@mail.gmail.com >
<CAMbWs49xo3oCnL+3ouzer7UZsuso7e=z8TxU2J0ZSqc=YfFK6A@mail.gmail.com >
<CAApHDvqg6XZDhYRPz0zgOcevSMo0d3vxA9DvHrZtKfqO30WTnw@mail.gmail.com >
<87bkabri49.fsf@163.com >
<CAMbWs4_SVvKOeOj62Ty4z2jtFdnwN_0nsV6KA2F9arvh5rmyVA@mail.gmail.com >
Richard Guo <guofenglinux@gmail.com> writes:
>
> The detection of self-inconsistent restrictions already exists in
> planner.
>
> # set constraint_exclusion to on;
> SET
> # explain (costs off) select * from a where a > 3 and a is null;
> QUERY PLAN
> --------------------------
> Result
> One-Time Filter: false
> (2 rows)
It has a different scope and cost from what I suggested. I'd suggest
to detect the notnull constraint only with lower cost and it can be used
in another user case. the constaint_exclusion can covers more user
cases but more expensivly and default off.
Apart from the abve topic, I'm thinking if we should think about the
case like this:
create table t1(a int);
create table t2(a int);
explain (costs off) select * from t1 join t2 using(a) where a is NULL;
QUERY PLAN
-----------------------------------
Hash Join
Hash Cond: (t2.a = t1.a)
-> Seq Scan on t2
-> Hash
-> Seq Scan on t1
Filter: (a IS NULL)
Here a is nullable at the base relation side, but we know that the query
would not return anything at last. IIUC, there is no good place to
handle this in our current infrastructure, I still raise this up in case
I missed anything.
--
Best Regards
Andy Fan
view thread (9+ messages) latest in thread
Message-ID: <87edf5vl6u.fsf@163.com>
Permalink: ../87edf5vl6u.fsf@163.com/
Also on: postgresql.org/message-id/87edf5vl6u.fsf@163.com
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: zhihuifan1213@163.com, guofenglinux@gmail.com, dgrowleyml@gmail.com, a.lepikhov@postgrespro.ru, tgl@sss.pgh.pa.us, pgsql-hackers@lists.postgresql.org
Subject: Re: Removing const-false IS NULL quals and redundant IS NOT NULL quals
In-Reply-To: <87edf5vl6u.fsf@163.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox