Re: BUG #18643: EXPLAIN estimated rows mismatch

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