Message-ID: <bc9562f9-5e6b-498d-9243-697a56961292@gmail.com>
Date: Thu, 3 Oct 2024 11:23:14 +0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: BUG #18643: EXPLAIN estimated rows mismatch
To: David Rowley <dgrowleyml@gmail.com>, ming wei tan <mingwei.tc@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-bugs@lists.postgresql.org
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>
Content-Language: en-AU
From: Andrei Lepikhov <lepihov@gmail.com>
In-Reply-To: 
 <CAApHDvqqQrVeZ0A5eyPDwOpFRA=xGHgKZp=iY3gKU4gZ4UyVwg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/bc9562f9-5e6b-498d-9243-697a56961292%40gmail.com>
Precedence: bulk

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