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
Date: Tue, 01 Oct 2024 12:43:46 -0400
Message-ID: <1860123.1727801026@sss.pgh.pa.us> (raw)
In-Reply-To: <18643-8d455145acd8243e@postgresql.org>
References: <18643-8d455145acd8243e@postgresql.org>
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
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: tgl@sss.pgh.pa.us, mingwei.tc@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18643: EXPLAIN estimated rows mismatch
In-Reply-To: <1860123.1727801026@sss.pgh.pa.us>
* 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