[PATCH] Remove useless ORDER BY from scalar subqueries

Started by Iliia Khaprovabout 1 month ago9 messageshackers
Jump to latest
#1Iliia Khaprov
i.khaprov@gmail.com

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

Attachments:

REL_16-0001-Remove-useless-ORDER-BY-from-scalar-subqueries.patchapplication/octet-stream; name=REL_16-0001-Remove-useless-ORDER-BY-from-scalar-subqueries.patchDownload+160-1
master-0001-Remove-useless-ORDER-BY-from-scalar-subqueries.patchapplication/octet-stream; name=master-0001-Remove-useless-ORDER-BY-from-scalar-subqueries.patchDownload+160-1
REL_13-0001-Remove-useless-ORDER-BY-from-scalar-subqueries.patchapplication/octet-stream; name=REL_13-0001-Remove-useless-ORDER-BY-from-scalar-subqueries.patchDownload+160-1
REL_15-0001-Remove-useless-ORDER-BY-from-scalar-subqueries.patchapplication/octet-stream; name=REL_15-0001-Remove-useless-ORDER-BY-from-scalar-subqueries.patchDownload+160-1
REL_14-0001-Remove-useless-ORDER-BY-from-scalar-subqueries.patchapplication/octet-stream; name=REL_14-0001-Remove-useless-ORDER-BY-from-scalar-subqueries.patchDownload+160-1
REL_17-0001-Remove-useless-ORDER-BY-from-scalar-subqueries.patchapplication/octet-stream; name=REL_17-0001-Remove-useless-ORDER-BY-from-scalar-subqueries.patchDownload+160-1
REL_19-0001-Remove-useless-ORDER-BY-from-scalar-subqueries.patchapplication/octet-stream; name=REL_19-0001-Remove-useless-ORDER-BY-from-scalar-subqueries.patchDownload+160-1
REL_18-0001-Remove-useless-ORDER-BY-from-scalar-subqueries.patchapplication/octet-stream; name=REL_18-0001-Remove-useless-ORDER-BY-from-scalar-subqueries.patchDownload+160-1
#2Iliia Khaprov
i.khaprov@gmail.com
In reply to: Iliia Khaprov (#1)
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

Show quoted text

On Sun, Oct 18, 2026 at 9:32 PM Iliia Khaprov <i.khaprov@gmail.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

#3Tom Lane
tgl@sss.pgh.pa.us
In reply to: Iliia Khaprov (#1)
Re: [PATCH] Remove useless ORDER BY from scalar subqueries

Iliia Khaprov <i.khaprov@gmail.com> writes:

The patch drops the ORDER BY of EXPR, MULTIEXPR and ROWCOMPARE
sublinks in make_subplan, as simplify_EXISTS_query does for EXISTS.

Why is this worth spending development effort on? "One person
wasted a lot of time on a badly-written query" isn't a good
enough answer.

regards, tom lane

#4Iliia Khaprov
i.khaprov@gmail.com
In reply to: Iliia Khaprov (#1)
Re: [PATCH] Remove useless ORDER BY from scalar subqueries

The query is valid, the plan is bad. ORDER BY can't change the outcome
so it is wasteful. The Sort reads the whole input first.

The Planner already drops the ORDER BY for EXISTS in
simplify_EXISTS_query.
This does the same for scalar subqueries.

Best regards,
Iliia

Show quoted text

On Sun, Oct 18, 2026 at 11:19 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Iliia Khaprov <i.khaprov@gmail.com> writes:

The patch drops the ORDER BY of EXPR, MULTIEXPR and ROWCOMPARE
sublinks in make_subplan, as simplify_EXISTS_query does for EXISTS.

Why is this worth spending development effort on? "One person
wasted a lot of time on a badly-written query" isn't a good
enough answer.

regards, tom lane

#5David Geier
geidav.pg@gmail.com
In reply to: Iliia Khaprov (#4)
Re: [PATCH] Remove useless ORDER BY from scalar subqueries

Hi Iliia,

The query is valid, the plan is bad. ORDER BY can't change the outcome
so it is wasteful. The Sort reads the whole input first.

Agreed.

Worth pointing out that this works because the executor will return an
error if the sub-query returns > 1 row. So it can only ever return 0
rows (result is NULL, no sort needed) or 1 row (result is well defined
without sort).

The Planner already drops the ORDER BY for EXISTS in
simplify_EXISTS_query.
This does the same for scalar subqueries.

Good point that there's precedence for this type of change. This is
especially important for the error case, e.g.

SELECT (SELECT a FROM t WHERE a = 2 ORDER BY 1 / (a - 2));

Without the optimization, this results in a division-by-zero error. With
the optimization this is no longer the case. There's a comment in the
optimization for EXISTS which states the accepted semantic change:

* Note: by suppressing the targetlist we could cause an observable
behavioral
* change, namely that any errors that might occur in evaluating the tlist
* won't occur, nor will other side-effects of volatile functions. This
seems
* unlikely to bother anyone in practice.

So for EXISTS we apply the optimization even in the presence of volatile
expressions and accept that those side effects may vanish.

Your patch for sublinks is stricter. Why not do it the same way it's
done for EXISTS?

--
David Geier

#6Iliia Khaprov
i.khaprov@gmail.com
In reply to: David Geier (#5)
Re: [PATCH] Remove useless ORDER BY from scalar subqueries

Hi David,

The sort expressions stay in the targetlist, only sortClause is
cleared, so your example still raises division by zero. There is a
regression test for it.

Stock:
InitPlan expr_1
-> Sort
Output: t.a, ((1 / (t.a - 2)))
Sort Key: ((1 / (t.a - 2)))
-> Seq Scan on public.t
Output: t.a, (1 / (t.a - 2))
Filter: (t.a = 2)

ERROR: division by zero

Patched:
InitPlan expr_1
-> Seq Scan on public.t
Output: t.a, (1 / (t.a - 2))
Filter: (t.a = 2)

ERROR: division by zero

In the attached v2 (against b536d5a0bd7 and REL_13 to REL_19)
I drop ORDER BY for volatile sort keys too, but keep it
for set operations like here:

SELECT (SELECT a FROM t1 UNION ALL SELECT a FROM t2 ORDER BY 1);

Best regards,
Iliia

Show quoted text

On Mon, Oct 19, 2026 at 8:30 PM David Geier <geidav.pg@gmail.com> wrote:

Hi Iliia,

The query is valid, the plan is bad. ORDER BY can't change the outcome
so it is wasteful. The Sort reads the whole input first.

Agreed.

Worth pointing out that this works because the executor will return an
error if the sub-query returns > 1 row. So it can only ever return 0
rows (result is NULL, no sort needed) or 1 row (result is well defined
without sort).

The Planner already drops the ORDER BY for EXISTS in
simplify_EXISTS_query.
This does the same for scalar subqueries.

Good point that there's precedence for this type of change. This is
especially important for the error case, e.g.

SELECT (SELECT a FROM t WHERE a = 2 ORDER BY 1 / (a - 2));

Without the optimization, this results in a division-by-zero error. With
the optimization this is no longer the case. There's a comment in the
optimization for EXISTS which states the accepted semantic change:

* Note: by suppressing the targetlist we could cause an observable
behavioral
* change, namely that any errors that might occur in evaluating the tlist
* won't occur, nor will other side-effects of volatile functions. This
seems
* unlikely to bother anyone in practice.

So for EXISTS we apply the optimization even in the presence of volatile
expressions and accept that those side effects may vanish.

Your patch for sublinks is stricter. Why not do it the same way it's
done for EXISTS?

--
David Geier

Attachments:

REL_13-v2-0001-Remove-useless-ORDER-BY-from-scalar-subqueries.patchapplication/octet-stream; name=REL_13-v2-0001-Remove-useless-ORDER-BY-from-scalar-subqueries.patchDownload+150-1
master-v2-0001-Remove-useless-ORDER-BY-from-scalar-subqueries.patchapplication/octet-stream; name=master-v2-0001-Remove-useless-ORDER-BY-from-scalar-subqueries.patchDownload+150-1
REL_15-v2-0001-Remove-useless-ORDER-BY-from-scalar-subqueries.patchapplication/octet-stream; name=REL_15-v2-0001-Remove-useless-ORDER-BY-from-scalar-subqueries.patchDownload+150-1
REL_16-v2-0001-Remove-useless-ORDER-BY-from-scalar-subqueries.patchapplication/octet-stream; name=REL_16-v2-0001-Remove-useless-ORDER-BY-from-scalar-subqueries.patchDownload+150-1
REL_14-v2-0001-Remove-useless-ORDER-BY-from-scalar-subqueries.patchapplication/octet-stream; name=REL_14-v2-0001-Remove-useless-ORDER-BY-from-scalar-subqueries.patchDownload+150-1
REL_18-v2-0001-Remove-useless-ORDER-BY-from-scalar-subqueries.patchapplication/octet-stream; name=REL_18-v2-0001-Remove-useless-ORDER-BY-from-scalar-subqueries.patchDownload+150-1
REL_19-v2-0001-Remove-useless-ORDER-BY-from-scalar-subqueries.patchapplication/octet-stream; name=REL_19-v2-0001-Remove-useless-ORDER-BY-from-scalar-subqueries.patchDownload+150-1
REL_17-v2-0001-Remove-useless-ORDER-BY-from-scalar-subqueries.patchapplication/octet-stream; name=REL_17-v2-0001-Remove-useless-ORDER-BY-from-scalar-subqueries.patchDownload+150-1
#7David Rowley
dgrowleyml@gmail.com
In reply to: Iliia Khaprov (#6)
Re: [PATCH] Remove useless ORDER BY from scalar subqueries

On Wed, 21 Oct 2026 at 08:46, Iliia Khaprov <i.khaprov@gmail.com> wrote:

In the attached v2 (against b536d5a0bd7 and REL_13 to REL_19)

I've not looked at any of this, but just so you're aware that since
this isn't a bug fix, there's zero chance of any of this going into
the backbranches. So you needn't bother with the hoard of backbranch
patches.

David

#8Iliia Khaprov
i.khaprov@gmail.com
In reply to: David Rowley (#7)
Re: [PATCH] Remove useless ORDER BY from scalar subqueries

Hi David,

Noted on backbranch patches, thanks. Does this include REL_19 too?
Would you mind looking at the master patch?

Best regards,
Iliia

Show quoted text

On Wed, Oct 21, 2026 at 4:50 AM David Rowley <dgrowleyml@gmail.com> wrote:

On Wed, 21 Oct 2026 at 08:46, Iliia Khaprov <i.khaprov@gmail.com> wrote:

In the attached v2 (against b536d5a0bd7 and REL_13 to REL_19)

I've not looked at any of this, but just so you're aware that since
this isn't a bug fix, there's zero chance of any of this going into
the backbranches. So you needn't bother with the hoard of backbranch
patches.

David

#9David Rowley
dgrowleyml@gmail.com
In reply to: Iliia Khaprov (#8)
Re: [PATCH] Remove useless ORDER BY from scalar subqueries

On Wed, 28 Oct 2026 at 22:06, Iliia Khaprov <i.khaprov@gmail.com> wrote:

Noted on backbranch patches, thanks. Does this include REL_19 too?

If the branch name ends in _STABLE, then it's a branch that won't
receive any new features.

Would you mind looking at the master patch?

I think there's generally very little bandwidth at the moment for
looking at any new features. I won't be dedicating any of mine towards
this in the foreseeable future. Sorry. I just dropped in to reset your
expectations and to try and save you some wasted effort.

David