From: Andrei Lepikhov <lepihov@gmail.com>
To: 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: Wed, 2 Oct 2024 07:35:04 +0700
Message-ID: <27ac7e14-05bb-40c6-85ad-ca6a0b61f512@gmail.com> (raw)
In-Reply-To: <1860123.1727801026@sss.pgh.pa.us>
References: <18643-8d455145acd8243e@postgresql.org>
<1860123.1727801026@sss.pgh.pa.us>
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
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: lepihov@gmail.com, 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: <27ac7e14-05bb-40c6-85ad-ca6a0b61f512@gmail.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