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>
User-agent: mu4e 1.10.7; emacs 29.1
From: Andy Fan <zhihuifan1213@163.com>
To: Richard Guo <guofenglinux@gmail.com>
Cc: David Rowley <dgrowleyml@gmail.com>, Andrei Lepikhov
 <a.lepikhov@postgrespro.ru>, Tom Lane <tgl@sss.pgh.pa.us>,
 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
In-reply-to: 
 <CAMbWs4_SVvKOeOj62Ty4z2jtFdnwN_0nsV6KA2F9arvh5rmyVA@mail.gmail.com>
Message-ID: <87edf5vl6u.fsf@163.com>
MIME-Version: 1.0
Content-Type: text/plain
Archived-At: <https://www.postgresql.org/message-id/87edf5vl6u.fsf%40163.com>
Precedence: bulk


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