pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19059: PostgreSQL fails to evaluate the cheaper expression first, leading to 45X performance degradation
6+ messages / 5 participants
[nested] [flat]
* BUG #19059: PostgreSQL fails to evaluate the cheaper expression first, leading to 45X performance degradation
@ 2025-09-20 16:08 PG Bug reporting form <noreply@postgresql.org>
2025-09-20 21:19 ` Re: BUG #19059: PostgreSQL fails to evaluate the cheaper expression first, leading to 45X performance degradation Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: PG Bug reporting form @ 2025-09-20 16:08 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: jinhui.lai@qq.com
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #19059: PostgreSQL fails to evaluate the cheaper expression first, leading to 45X performance degradation
2025-09-20 16:08 BUG #19059: PostgreSQL fails to evaluate the cheaper expression first, leading to 45X performance degradation PG Bug reporting form <noreply@postgresql.org>
@ 2025-09-20 21:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-09-20 22:54 ` Re: BUG #19059: PostgreSQL fails to evaluate the cheaper expression first, leading to 45X performance degradation Christophe Pettus <xof@thebuild.com>
2025-09-21 16:05 ` Re: BUG #19059: PostgreSQL fails to evaluate the cheaper expression first, leading to 45X performance degradation =?utf-8?B?SmluaHVpIExhaQ==?= <jinhui.lai@qq.com>
0 siblings, 2 replies; 6+ messages in thread
From: Tom Lane @ 2025-09-20 21:19 UTC (permalink / raw)
To: jinhui.lai@qq.com; +Cc: pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> writes:
> 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.
We do do that at the top AND level (cf. order_qual_clauses())
but we have not bothered for OR clauses.
The whole exercise is pretty questionable really, considering how weak
our cost model for expressions is. We could easily end up pessimizing
a clause that the user had put into carefully-selected order.
While we've not gotten a huge amount of push-back about the top-level
re-ordering, there's been some complaints. So I'm not eager to
propagate the idea further down.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #19059: PostgreSQL fails to evaluate the cheaper expression first, leading to 45X performance degradation
2025-09-20 16:08 BUG #19059: PostgreSQL fails to evaluate the cheaper expression first, leading to 45X performance degradation PG Bug reporting form <noreply@postgresql.org>
2025-09-20 21:19 ` Re: BUG #19059: PostgreSQL fails to evaluate the cheaper expression first, leading to 45X performance degradation Tom Lane <tgl@sss.pgh.pa.us>
@ 2025-09-20 22:54 ` Christophe Pettus <xof@thebuild.com>
2025-09-20 23:19 ` Re: BUG #19059: PostgreSQL fails to evaluate the cheaper expression first, leading to 45X performance degradation Tom Lane <tgl@sss.pgh.pa.us>
2025-09-21 09:09 ` Re: BUG #19059: PostgreSQL fails to evaluate the cheaper expression first, leading to 45X performance degradation Vik Fearing <vik@postgresfriends.org>
1 sibling, 2 replies; 6+ messages in thread
From: Christophe Pettus @ 2025-09-20 22:54 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: jinhui.lai@qq.com; pgsql-bugs@lists.postgresql.org
> On Sep 20, 2025, at 14:19, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> While we've not gotten a huge amount of push-back about the top-level
> re-ordering, there's been some complaints. So I'm not eager to
> propagate the idea further down.
And tangentially, doesn't the SQL standard make it implementation-defined if OR short-circuits or not?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #19059: PostgreSQL fails to evaluate the cheaper expression first, leading to 45X performance degradation
2025-09-20 16:08 BUG #19059: PostgreSQL fails to evaluate the cheaper expression first, leading to 45X performance degradation PG Bug reporting form <noreply@postgresql.org>
2025-09-20 21:19 ` Re: BUG #19059: PostgreSQL fails to evaluate the cheaper expression first, leading to 45X performance degradation Tom Lane <tgl@sss.pgh.pa.us>
2025-09-20 22:54 ` Re: BUG #19059: PostgreSQL fails to evaluate the cheaper expression first, leading to 45X performance degradation Christophe Pettus <xof@thebuild.com>
@ 2025-09-20 23:19 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 6+ messages in thread
From: Tom Lane @ 2025-09-20 23:19 UTC (permalink / raw)
To: Christophe Pettus <xof@thebuild.com>; +Cc: jinhui.lai@qq.com; pgsql-bugs@lists.postgresql.org
Christophe Pettus <xof@thebuild.com> writes:
> And tangentially, doesn't the SQL standard make it implementation-defined if OR short-circuits or not?
It looks like it says it's implementation-dependent, meaning
that not only is it not defined by the standard, but we're not
required to document it either:
Subclause 6.3.3.3, “Rule evaluation order”:
a) It is implementation-dependent whether expressions are
actually evaluated from left to right when the precedence is
not otherwise determined by the Formats or by parentheses.
b) If evaluation of the inessential parts of an expression or
search condition would cause an exception condition to be
raised, then it is implementation-dependent whether or not
that condition is raised.
c) The declared type of a site that contains an intermediate
result is implementation-dependent.
This is a good thing, because while we do mention boolean expression
re-ordering and short-circuiting, there's an awful lot of such details
that we've left undocumented.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #19059: PostgreSQL fails to evaluate the cheaper expression first, leading to 45X performance degradation
2025-09-20 16:08 BUG #19059: PostgreSQL fails to evaluate the cheaper expression first, leading to 45X performance degradation PG Bug reporting form <noreply@postgresql.org>
2025-09-20 21:19 ` Re: BUG #19059: PostgreSQL fails to evaluate the cheaper expression first, leading to 45X performance degradation Tom Lane <tgl@sss.pgh.pa.us>
2025-09-20 22:54 ` Re: BUG #19059: PostgreSQL fails to evaluate the cheaper expression first, leading to 45X performance degradation Christophe Pettus <xof@thebuild.com>
@ 2025-09-21 09:09 ` Vik Fearing <vik@postgresfriends.org>
1 sibling, 0 replies; 6+ messages in thread
From: Vik Fearing @ 2025-09-21 09:09 UTC (permalink / raw)
To: Christophe Pettus <xof@thebuild.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: jinhui.lai@qq.com; pgsql-bugs@lists.postgresql.org
On 21/09/2025 00:54, Christophe Pettus wrote:
>> On Sep 20, 2025, at 14:19, Tom Lane<tgl@sss.pgh.pa.us> wrote:
>> While we've not gotten a huge amount of push-back about the top-level
>> re-ordering, there's been some complaints. So I'm not eager to
>> propagate the idea further down.
> And tangentially, doesn't the SQL standard make it implementation-defined if OR short-circuits or not?
>
Not that I can see. The only implementation-defined mention in <boolean
value expression> is:
"If the SQL-implementation supports Feature T101, “Enhanced nullability
determination”, and if BVE conforms to an implementation-defined (IE011)
rule that enables the SQL-implementation to correctly infer that, when
BVE is True, then X cannot be null, then BVE is a known-not-null
condition for X. "
--
Vik Fearing
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #19059: PostgreSQL fails to evaluate the cheaper expression first, leading to 45X performance degradation
2025-09-20 16:08 BUG #19059: PostgreSQL fails to evaluate the cheaper expression first, leading to 45X performance degradation PG Bug reporting form <noreply@postgresql.org>
2025-09-20 21:19 ` Re: BUG #19059: PostgreSQL fails to evaluate the cheaper expression first, leading to 45X performance degradation Tom Lane <tgl@sss.pgh.pa.us>
@ 2025-09-21 16:05 ` =?utf-8?B?SmluaHVpIExhaQ==?= <jinhui.lai@qq.com>
1 sibling, 0 replies; 6+ messages in thread
From: Jinhui Lai @ 2025-09-21 16:05 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
> We do do that at the top AND level (cf. order_qual_clauses()), but we have not bothered for OR clauses.
Hi, Tom. Thanks for your reply.
I have another case that influences both AND/OR clauses. You can reproduce it as follows:
-- Create tables t0 and t1.
CREATE TABLE t0(c0 INT8); -- small table
INSERT INTO t0 SELECT * FROM generate_series(1, 1000);
CREATE TABLE t1(c1 INT8); -- large table
INSERT INTO t1 SELECT * FROM generate_series(1, 10000000);
-- These two short-circuit evaluation examples happen before scanning any tables, as their plans only contain one row.
SELECT (SELECT MIN(c1) FROM t1)>0 OR TRUE; -- Time: 0.311 ms
SELECT (SELECT MIN(c1) FROM t1)>0 AND FALSE; -- Time: 0.318 ms
explain SELECT (SELECT MIN(c1) FROM t1)>0 OR TRUE;
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.01 rows=1 width=1)
-- These two short-circuit evaluation examples may happen after scanning table t0, as their execution times are similar to that of the query "SELECT (SELECT MIN(c0) FROM t0)>0".
SELECT (SELECT MIN(c0) FROM t0)>0 OR (SELECT MIN(c1) FROM t1)>0; -- Time: 0.416 ms
SELECT (SELECT MIN(c0) FROM t0)<0 AND (SELECT MIN(c1) FROM t1)>0; -- Time: 0.640 ms
SELECT (SELECT MIN(c0) FROM t0)>0; -- Time: 0.665 ms
-- As demonstrated by the following two queries, the optimizer fails to reorder expressions in the SELECT clause for AND/OR operations. This can be observed in their execution plans. Since t0 is smaller than t1, evaluating t0 first (based on the cost model) would be more efficient.
-- Particularly, given that PostgreSQL applies short-circuit evaluation during execution, the logical reordering of these expressions becomes a crucial optimization opportunity.
SELECT (SELECT MIN(c1) FROM t1)>0 OR (SELECT MIN(c0) FROM t0)>0;
Time: 148.815 ms
explain SELECT (SELECT MIN(c1) FROM t1)>0 OR (SELECT MIN(c0) FROM t0)>0;
QUERY PLAN
-----------------------------------------------------------------------------------------------
Result (cost=97348.95..97348.96 rows=1 width=1)
InitPlan 1
-> Finalize Aggregate (cost=97331.43..97331.44 rows=1 width=8)
-> Gather (cost=97331.21..97331.42 rows=2 width=8)
Workers Planned: 2
-> Partial Aggregate (cost=96331.21..96331.22 rows=1 width=8)
-> Parallel Seq Scan on t1 (cost=0.00..85914.57 rows=4166657 width=8)
InitPlan 2
-> Aggregate (cost=17.50..17.51 rows=1 width=8)
-> Seq Scan on t0 (cost=0.00..15.00 rows=1000 width=8)
SELECT (SELECT MIN(c1) FROM t1)>0 AND (SELECT MIN(c0) FROM t0)<0;
Time: 153.308 ms
explain SELECT (SELECT MIN(c1) FROM t1)>0 AND (SELECT MIN(c0) FROM t0)<0;
QUERY PLAN
-----------------------------------------------------------------------------------------------
Result (cost=97348.95..97348.96 rows=1 width=1)
InitPlan 1
-> Finalize Aggregate (cost=97331.43..97331.44 rows=1 width=8)
-> Gather (cost=97331.21..97331.42 rows=2 width=8)
Workers Planned: 2
-> Partial Aggregate (cost=96331.21..96331.22 rows=1 width=8)
-> Parallel Seq Scan on t1 (cost=0.00..85914.57 rows=4166657 width=8)
InitPlan 2
-> Aggregate (cost=17.50..17.51 rows=1 width=8)
-> Seq Scan on t0 (cost=0.00..15.00 rows=1000 width=8)
Thanks you once again. I look forward to your reply.
Best regard,
Jinhui
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2025-09-21 16:05 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-09-20 16:08 BUG #19059: PostgreSQL fails to evaluate the cheaper expression first, leading to 45X performance degradation PG Bug reporting form <noreply@postgresql.org>
2025-09-20 21:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-09-20 22:54 ` Christophe Pettus <xof@thebuild.com>
2025-09-20 23:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-09-21 09:09 ` Vik Fearing <vik@postgresfriends.org>
2025-09-21 16:05 ` =?utf-8?B?SmluaHVpIExhaQ==?= <jinhui.lai@qq.com>
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