From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: mingwei.tc@gmail.com
Subject: BUG #18643: EXPLAIN estimated rows mismatch
Date: Tue, 01 Oct 2024 07:47:34 +0000
Message-ID: <18643-8d455145acd8243e@postgresql.org> (raw)
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))
Running on docker image postgres:12
Test cases:
DROP DATABASE IF EXISTS database4;
CREATE DATABASE database4 WITH ENCODING 'UTF8' TEMPLATE template0;
\c database4;
CREATE TABLE t2(c0 int);
INSERT INTO t2(c0) VALUES(1);
INSERT INTO t2(c0) VALUES(2);
EXPLAIN SELECT t2.c0 FROM t2 WHERE t2.c0 IN (t2.c0);
EXPLAIN SELECT t2.c0 FROM t2 WHERE (t2.c0 IN (t2.c0)) OR (t2.c0 > 4);
DROP DATABASE IF EXISTS database4;
CREATE DATABASE database4 WITH ENCODING 'UTF8' TEMPLATE template0;
\c database4;
CREATE TABLE t2(c0 int);
INSERT INTO t2(c0) VALUES(1);
INSERT INTO t2(c0) VALUES(2);
EXPLAIN SELECT t2.c0 FROM t2 WHERE (t2.c0)=(t2.c0);
EXPLAIN SELECT t2.c0 FROM t2 WHERE ((t2.c0)=(t2.c0) OR (t2.c0 > 4);
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: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, mingwei.tc@gmail.com
Subject: Re: BUG #18643: EXPLAIN estimated rows mismatch
In-Reply-To: <18643-8d455145acd8243e@postgresql.org>
* 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