Re: [PATCH] Remove useless ORDER BY from scalar subqueries

Trying to fix formatting here:

Hi,

Had a case when an UPDATE on a 1342-row table ran 33 minutes and moved
~370 GB through temp files before failing with "more than one row returned
by a subquery". Default settings, 17.11. Reduced from a fuzzer case:

CREATE TEMP TABLE t AS SELECT g AS a FROM generate_series(1, 1342) g;
UPDATE t SET a = (SELECT 1 FROM t t1, t t2, t t3 ORDER BY t1.a);

Every branch from 13 to master plans a Sort above the join. A scalar
subquery returns at most one row, so the ORDER BY is useless, but the
Sort reads all 2.4e9 rows before the error.

The patch drops the ORDER BY of EXPR, MULTIEXPR and ROWCOMPARE sublinks
in make_subplan, as simplify_EXISTS_query does for EXISTS. It is kept
for LIMIT/OFFSET, DISTINCT ON, FOR UPDATE/SHARE, volatile sort keys, set
operations and data-modifying CTEs.

400 rows, as a SELECT, median of 3, -O2:

stock patched
master 6156 ms 0.10 ms
19 (RC1) 6293 ms 0.09 ms
18.6 6046 ms 0.11 ms
17.11 6053 ms 0.08 ms
16.15 6276 ms 0.04 ms
15.19 6407 ms 0.10 ms
14.24 8595 ms 0.15 ms
13.23 8484 ms 0.08 ms

master-0001 is against 7787aec8ab0. REL_13 to REL_19 versions attached
for back-patching. make check passes on all, check-world (with TAP) on
master.

Regards,
Iliia

On Sun, Oct 18, 2026 at 9:32 PM Iliia Khaprov <i(dot)khaprov(at)gmail(dot)com> wrote:
>
> Hi, Had a case when an UPDATE on a 1342-row table ran 33 minutes and moved
> ~370 GB through temp files before failing with "more than one row returned
> by a subquery". Default settings, 17.11. Reduced from a fuzzer case: CREATE TEMP TABLE t AS SELECT g AS a FROM generate_series(1, 1342) g; UPDATE t SET a = (SELECT 1 FROM t t1, t t2, t t3 ORDER BY t1.a); Every branch from 13 to master plans a Sort above the join. A scalar subquery returns at most one row, so the ORDER BY is useless, but the Sort reads all 2.4e9 rows before the error. The patch drops the ORDER BY of EXPR, MULTIEXPR and ROWCOMPARE sublinks in make_subplan, as simplify_EXISTS_query does for EXISTS. It is kept for LIMIT/OFFSET, DISTINCT ON, FOR UPDATE/SHARE, volatile sort keys, set operations and data-modifying CTEs. 400 rows, as a SELECT, median of 3, -O2: stock patched master 6156 ms 0.10 ms 19 (RC1) 6293 ms 0.09 ms 18.6 6046 ms 0.11 ms 17.11 6053 ms 0.08 ms 16.15 6276 ms 0.04 ms 15.19 6407 ms 0.10 ms 14.24 8595 ms 0.15 ms 13.23 8484 ms 0.08 ms master-0001 is against 7787aec8ab0. REL_13 to REL_19 versions attached for back-patching. make check passes on all, check-world (with TAP) on master. Regards,
> Iliia