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

From: Jinhui Lai <jinhui(dot)lai(at)qq(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-bugs <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: Re: BUG #19059: PostgreSQL fails to evaluate the cheaper expression first, leading to 45X performance degradation
Date: 2025-09-21 16:05:34
Message-ID: tencent_B680F384162B9BF0262DD675C6C21E9ED106@qq.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
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

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message myzhen 2025-09-22 04:56:10 coredump of language internal function
Previous Message Vik Fearing 2025-09-21 09:09:02 Re: BUG #19059: PostgreSQL fails to evaluate the cheaper expression first, leading to 45X performance degradation