Re: BUG #18643: EXPLAIN estimated rows mismatch

On 1/10/2024 09:47, PG Bug reporting form wrote:
> The following bug has been logged on the website:
> 
> Bug reference:      18643
> Logged by:          ming wei tan
> Email address:      mingwei.tc@gmail.com
> PostgreSQL version: 12.20
> Operating system:   Debian 12.20-1.pgdg120+1 (Docker)
> Description:
> 
> 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
>                        QUERY PLAN
> ------------------------------------------------------
>   Seq Scan on t2  (cost=0.00..35.50 rows=2537 width=4)
>     Filter: (c0 IS NOT NULL)
> 
> 
> While, `EXPLAIN SELECT t2.c0 FROM t2 WHERE (t2.c0 IN (t2.c0)) OR (t2.c0 >
> 4)` returns rows=858
>                       QUERY PLAN
> -----------------------------------------------------
>   Seq Scan on t2  (cost=0.00..48.25 rows=858 width=4)
>     Filter: ((c0 = c0) OR (c0 > 4))
I found one link, which is connected to your case:
https://www.postgresql.org/message-id/flat/CAMjNa7cC4X9YR-vAJS-jSYCajhRDvJQnN7m2sLH1wLh-_Z2bsw%40mail.gmail.com
there you can find answer to your question. In general - it is too 
expensive to test each clause (especially non-top level one) on possible 
evaluation into constant expression.

-- 
regards, Andrei Lepikhov