Horton

Re: BUG #19059: PostgreSQL fails to evaluate the cheaper expression first, leading to 45X performance degradation

Jinhui Lai <jinhui.lai@qq.com>

View thread
From: Jinhui Lai <jinhui.lai@qq.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
Date: 2025-09-21T16:05:34Z
Lists: pgsql-bugs 
&gt; We&nbsp;do&nbsp;do&nbsp;that&nbsp;at&nbsp;the&nbsp;top&nbsp;AND&nbsp;level&nbsp;(cf.&nbsp;order_qual_clauses()), but&nbsp;we&nbsp;have&nbsp;not&nbsp;bothered&nbsp;for&nbsp;OR&nbsp;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.&nbsp;
CREATE TABLE t0(c0 INT8); -- small table
INSERT INTO t0 SELECT * FROM generate_series(1, 1000);
CREATE TABLE t1(c1 INT8); &nbsp;-- 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.&nbsp;
SELECT (SELECT MIN(c1) FROM t1)&gt;0 OR TRUE; &nbsp;-- Time: 0.311 ms
SELECT (SELECT MIN(c1) FROM t1)&gt;0 AND FALSE; &nbsp;-- Time: 0.318 ms
explain SELECT (SELECT MIN(c1) FROM t1)&gt;0 OR TRUE;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; QUERY PLAN &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
------------------------------------------
&nbsp;Result &nbsp;(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)&gt;0".&nbsp;
SELECT (SELECT MIN(c0) FROM t0)&gt;0 OR (SELECT MIN(c1) FROM t1)&gt;0; &nbsp;-- Time: 0.416 ms
SELECT (SELECT MIN(c0) FROM t0)<0 AND (SELECT MIN(c1) FROM t1)&gt;0; -- Time: 0.640 ms


SELECT (SELECT MIN(c0) FROM t0)&gt;0; &nbsp;-- 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.&nbsp;
-- 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)&gt;0 OR (SELECT MIN(c0) FROM t0)&gt;0;
Time: 148.815 ms
explain SELECT (SELECT MIN(c1) FROM t1)&gt;0 OR (SELECT MIN(c0) FROM t0)&gt;0;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; QUERY PLAN &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;
-----------------------------------------------------------------------------------------------
&nbsp;Result &nbsp;(cost=97348.95..97348.96 rows=1 width=1)
&nbsp; &nbsp;InitPlan 1
&nbsp; &nbsp; &nbsp;-&gt; &nbsp;Finalize Aggregate &nbsp;(cost=97331.43..97331.44 rows=1 width=8)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-&gt; &nbsp;Gather &nbsp;(cost=97331.21..97331.42 rows=2 width=8)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Workers Planned: 2
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-&gt; &nbsp;Partial Aggregate &nbsp;(cost=96331.21..96331.22 rows=1 width=8)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-&gt; &nbsp;Parallel Seq Scan on t1 &nbsp;(cost=0.00..85914.57 rows=4166657 width=8)
&nbsp; &nbsp;InitPlan 2
&nbsp; &nbsp; &nbsp;-&gt; &nbsp;Aggregate &nbsp;(cost=17.50..17.51 rows=1 width=8)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-&gt; &nbsp;Seq Scan on t0 &nbsp;(cost=0.00..15.00 rows=1000 width=8)




SELECT (SELECT MIN(c1) FROM t1)&gt;0 AND (SELECT MIN(c0) FROM t0)<0;
Time: 153.308 ms


explain SELECT (SELECT MIN(c1) FROM t1)&gt;0 AND (SELECT MIN(c0) FROM t0)<0;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; QUERY PLAN &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;
-----------------------------------------------------------------------------------------------
&nbsp;Result &nbsp;(cost=97348.95..97348.96 rows=1 width=1)
&nbsp; &nbsp;InitPlan 1
&nbsp; &nbsp; &nbsp;-&gt; &nbsp;Finalize Aggregate &nbsp;(cost=97331.43..97331.44 rows=1 width=8)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-&gt; &nbsp;Gather &nbsp;(cost=97331.21..97331.42 rows=2 width=8)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Workers Planned: 2
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-&gt; &nbsp;Partial Aggregate &nbsp;(cost=96331.21..96331.22 rows=1 width=8)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-&gt; &nbsp;Parallel Seq Scan on t1 &nbsp;(cost=0.00..85914.57 rows=4166657 width=8)
&nbsp; &nbsp;InitPlan 2
&nbsp; &nbsp; &nbsp;-&gt; &nbsp;Aggregate &nbsp;(cost=17.50..17.51 rows=1 width=8)
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;-&gt; &nbsp;Seq Scan on t0 &nbsp;(cost=0.00..15.00 rows=1000 width=8)


Thanks you once again. I look forward to your reply.
Best regard,
Jinhui