From: Andrei Lepikhov <lepihov@gmail.com>
To: mingwei.tc@gmail.com, pgsql-bugs@lists.postgresql.org, PG Bug reporting form <noreply@postgresql.org>
Subject: Re: BUG #18643: EXPLAIN estimated rows mismatch
Date: Wed, 2 Oct 2024 16:40:01 +0700
Message-ID: <6ea4b223-43c3-43f9-b405-b2bdc3e14445@gmail.com> (raw)
In-Reply-To: <18643-8d455145acd8243e@postgresql.org>
References: <18643-8d455145acd8243e@postgresql.org>
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%40mai...
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
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, noreply@postgresql.org
Subject: Re: BUG #18643: EXPLAIN estimated rows mismatch
In-Reply-To: <6ea4b223-43c3-43f9-b405-b2bdc3e14445@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