From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: jinhui.lai@qq.com
Subject: BUG #19059: PostgreSQL fails to evaluate the cheaper expression first, leading to 45X performance degradation
Date: Sat, 20 Sep 2025 16:08:11 +0000
Message-ID: <19059-c751eed385314309@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 19059
Logged by: Jinhui Lai
Email address: jinhui.lai@qq.com
PostgreSQL version: 17.6
Operating system: ubuntu 22.04
Description:
Dear PG developers,
Thanks for reading my report. You can reproduce it as follows, please.
PG has applied short-circuit evaluation for the following queries, which
contain an OR expression in their WHERE clause. When "t0.c0 > 0" is true, PG
will skip to evaluate "EXISTS (SELECT 1 FROM t1 WHERE t1.c1 = t0.c0)",
since true and any boolean expression is true.
However, the optimizer fails to reorder the expressions in the WHERE clause
for the second query. You can observe this from the second row in the plan:
"Filter: (EXISTS(SubPlan 1) OR (c0 > 0))"
A more optimal strategy would be for PG to use its cost model to reorder
expressions, prioritizing the evaluation of less expensive operations first.
CREATE TABLE t0(c0 INT8);
INSERT INTO t1 VALUES(1);
CREATE TABLE t1(c1 INT8);
INSERT INTO t1 SELECT * FROM generate_series(1, 1000000);
SELECT t0.c0 FROM t0 WHERE t0.c0 > 0 OR EXISTS (SELECT 1 FROM t1 WHERE
t1.c1 = t0.c0);
Time: 139.416 ms
SELECT t0.c0 FROM t0 WHERE EXISTS (SELECT 1 FROM t1 WHERE t1.c1 = t0.c0) OR
t0.c0 > 0;
Time: 6221.886 ms (00:06.222)
explain SELECT t0.c0 FROM t0 WHERE EXISTS (SELECT 1 FROM t1 WHERE t1.c1 =
t0.c0) OR t0.c0 > 0;
QUERY PLAN
-------------------------------------------------------------------------------
Seq Scan on t0 (cost=0.00..893306001.25 rows=1700 width=4)
Filter: (EXISTS(SubPlan 1) OR (c0 > 0))
SubPlan 1
-> Seq Scan on t1 (cost=0.00..350316.06 rows=1 width=0)
Filter: (c1 = t0.c0)
JIT:
Functions: 7
Options: Inlining true, Optimization true, Expressions true, Deforming
true
Thanks you once again. I look forward to your reply.
Best regard,
Jinhui
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, jinhui.lai@qq.com
Subject: Re: BUG #19059: PostgreSQL fails to evaluate the cheaper expression first, leading to 45X performance degradation
In-Reply-To: <19059-c751eed385314309@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