From: Andrei Lepikhov <lepihov@gmail.com>
To: David Rowley <dgrowleyml@gmail.com>
To: ming wei tan <mingwei.tc@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18643: EXPLAIN estimated rows mismatch
Date: Thu, 3 Oct 2024 11:23:14 +0700
Message-ID: <bc9562f9-5e6b-498d-9243-697a56961292@gmail.com> (raw)
In-Reply-To: <CAApHDvqqQrVeZ0A5eyPDwOpFRA=xGHgKZp=iY3gKU4gZ4UyVwg@mail.gmail.com>
References: <18643-8d455145acd8243e@postgresql.org>
<1860123.1727801026@sss.pgh.pa.us>
<27ac7e14-05bb-40c6-85ad-ca6a0b61f512@gmail.com>
<CAALvCkZz+7hpdsg-cSV93QK4jwVw1z7E5CS3+w-mmCp__Tpu0g@mail.gmail.com>
<CAApHDvqqQrVeZ0A5eyPDwOpFRA=xGHgKZp=iY3gKU4gZ4UyVwg@mail.gmail.com>
On 10/3/24 03:57, David Rowley wrote:
> On Thu, 3 Oct 2024 at 07:17, ming wei tan <mingwei.tc@gmail.com> wrote:
>>
>> On 1/10/2024 18:43, Tom Lane wrote:
>>> In any case, in this toy example that lacks an ANALYZE step,
>>> the selectivity estimates are mostly going to be garbage.
>>
>> Thanks for the replies. I'm just checking if a bug is present here
>> is a bug. Even with ANALYZE, the first EXPLAIN estimates more rows
>> compared to the second, even though the second WHERE clause is
>> less restrictive.
>
> I think you already checked that and Tom answered mentioning the
> reason that this happens.
>
> We certainly could do better here, but, as Tom mentioned your example
> does not seem convincing enough to warrant much effort.
>
> You should read the commit message in [1] as that might help you
> understand the project's point of view for these sort of
> optimisations. See in particular the first sentence of the second
> paragraph.
I can agree with the source reason. But presence of prepqual.c makes it
less clear:
Optimiser already attempts to remove duplicated ORs by the
find_duplicate_ors function. And does it in some strange manner:
explain
SELECT oid,relname FROM pg_class WHERE oid=1 OR oid=1;
Index Scan using pg_class_oid_index on pg_class
Index Cond: (oid = '1'::oid)
explain
SELECT oid,relname FROM pg_class WHERE oid=1 OR oid=1 OR oid=2;
Seq Scan on pg_class
Filter: ((oid = '1'::oid) OR (oid = '1'::oid) OR (oid = 2))
So, we already pass through the OR clauses. Why not to check semi-equal
clauses and remove duplicates even if not all clauses are such
duplicates? At least, it continually raises users' questions.
--
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, dgrowleyml@gmail.com, mingwei.tc@gmail.com, tgl@sss.pgh.pa.us, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18643: EXPLAIN estimated rows mismatch
In-Reply-To: <bc9562f9-5e6b-498d-9243-697a56961292@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