Message-ID: <27ac7e14-05bb-40c6-85ad-ca6a0b61f512@gmail.com>
Date: Wed, 2 Oct 2024 07:35:04 +0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: BUG #18643: EXPLAIN estimated rows mismatch
To: Tom Lane <tgl@sss.pgh.pa.us>, mingwei.tc@gmail.com
Cc: pgsql-bugs@lists.postgresql.org
References: <18643-8d455145acd8243e@postgresql.org>
 <1860123.1727801026@sss.pgh.pa.us>
Content-Language: en-US
From: Andrei Lepikhov <lepihov@gmail.com>
In-Reply-To: <1860123.1727801026@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/27ac7e14-05bb-40c6-85ad-ca6a0b61f512%40gmail.com>
Precedence: bulk

On 1/10/2024 18:43, Tom Lane wrote:
> PG Bug reporting form <noreply@postgresql.org> writes:
>> Given predicate A and B, it is expected that size (SELECT X where A)  <=
>> size (SELECT X WHERE A or B)
>> However, `EXPLAIN SELECT t2.c0 FROM t2 WHERE t2.c0 IN (t2.c0)` returns
>> rows=2537
> 
> I don't see any particular bug here.  If you look closely at the
> EXPLAIN output, you'll see that "t2.c0 IN (t2.c0)" is transformed
> to "c0 IS NOT NULL" --- but only if it's at top level.  So we're
> estimating selectivities for two quite different conditions in
> this example.
> 
> The NOT NULL bit happens because a top-level equality clause
> is transformed into an "EquivalenceClass", and then when we
> notice the class has only one member, we prefer to spit out
> "x IS NOT NULL" rather than "x = x".  That has the same effect
> (at top level of WHERE, anyway) and tends to be estimated
> more accurately.
I think their question was about why 'x IN (x)' transforms differently 
at the top and inside the OR clause. It is pretty typical question.

-- 
regards, Andrei Lepikhov