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
REL_16-0001-Remove-useless-ORDER-BY-from-scalar-subqueries.patch
Description: Binary data
Description: Binary data
Description: Binary data
Description: Binary data
Description: Binary data
Description: Binary data
Description: Binary data
Description: Binary data
