From: Tom Lane <tgl@sss.pgh.pa.us>
To: mingwei.tc@gmail.com
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18643: EXPLAIN estimated rows mismatch
In-reply-to: <18643-8d455145acd8243e@postgresql.org>
References: <18643-8d455145acd8243e@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Tue, 01 Oct 2024 07:47:34 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1860122.1727801026.1@sss.pgh.pa.us>
Date: Tue, 01 Oct 2024 12:43:46 -0400
Message-ID: <1860123.1727801026@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1860123.1727801026%40sss.pgh.pa.us>
Precedence: bulk

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.

In any case, in this toy example that lacks an ANALYZE step,
the selectivity estimates are mostly going to be garbage.

			regards, tom lane