Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vo6L6-00FSip-0j for pgsql-hackers@arkaria.postgresql.org; Thu, 05 Feb 2026 20:52:36 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vo6L5-000y0d-0p for pgsql-hackers@arkaria.postgresql.org; Thu, 05 Feb 2026 20:52:35 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vo6L4-000y0U-2H for pgsql-hackers@lists.postgresql.org; Thu, 05 Feb 2026 20:52:34 +0000 Received: from fhigh-a4-smtp.messagingengine.com ([103.168.172.155]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1vo6L2-00000000jsu-0CT4 for pgsql-hackers@postgresql.org; Thu, 05 Feb 2026 20:52:33 +0000 Received: from phl-compute-01.internal (phl-compute-01.internal [10.202.2.41]) by mailfhigh.phl.internal (Postfix) with ESMTP id D8F09140012D; Thu, 5 Feb 2026 15:52:30 -0500 (EST) Received: from phl-frontend-03 ([10.202.2.162]) by phl-compute-01.internal (MEProxy); Thu, 05 Feb 2026 15:52:30 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=anarazel.de; h= cc:cc:content-transfer-encoding:content-type:content-type:date :date:from:from:in-reply-to:message-id:mime-version:reply-to :subject:subject:to:to; s=fm3; t=1770324750; x=1770411150; bh=MS zgazj36n6viqvD7WmPhiVl9KIg/NFw39OycmNl8Oc=; b=ZY77QZs6iS2tGw2I1n 5S6nre6QVXH+vjN8OwAyRcjzPAJfmri2NbE6IkQOr1MRPJ8ai0hl+wEesi4Ibo9U q7sXwHVIhQEI95Gcv8agNK8Hp65A+Jf+JQy1iAeVNN9QikqfMgI7ptq4zd9337O1 qG+o4xI2uzl9vYtP2LrEcdKjXXB743hPoUOhr6qU2098L/X/gTb0gxHy9nkD9/k1 8SDYTHrzU5+8px0wMLSmfzXo6hKAX7mhFMkU+1OXErpXCSJ56Z4M74gZK/C4LEDy zOLDpTXfj3Y6UUe7iP/5ZtMEdKut534JYUcFfnfC/Bv2pwuQGnsPyo7FHsUeVX3g p9dA== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-transfer-encoding :content-type:content-type:date:date:feedback-id:feedback-id :from:from:in-reply-to:message-id:mime-version:reply-to:subject :subject:to:to:x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s= fm3; t=1770324750; x=1770411150; bh=MSzgazj36n6viqvD7WmPhiVl9KIg /NFw39OycmNl8Oc=; b=ndwoHFgpY23XhEyIbMul7ZOBaoJQ/C/iOncj2v8dLJX0 HxlmuExYDeErWR77Ing0nzmnBbmHLFFJrPsSzEH30Gc4IDEB79Aegl12dwd0LTNb jVpNJhx7augr9aSDwqcKJiKUQmaAzCW8R6fkjIgVUCqz6Pf0sjAUJAEyOpKvUeKV OIC4aP4HCFF7UewgcyhdPnxJi0QZEICBjgjdnZPiuA1cuiex7SnepBH4kY6j89+H pbF+lFSoKhKd/cMRH+MQzyKzrEvM9/WugtrmbgIB5958//WRNVbkeYqQj002ri8p 1RfWW3C2oe68Z7gpF67K6UWXxeXQ+vw76XGX65PN9Q== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgeefgedrtddtgddukeeifeduucetufdoteggodetrf dotffvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfurfetoffkrfgpnffqhgenuceu rghilhhouhhtmecufedttdenucenucfjughrpeffhffvvefukfggtggugfestheksfdttd dtjeenucfhrhhomheptehnughrvghsucfhrhgvuhhnugcuoegrnhgurhgvshesrghnrghr rgiivghlrdguvgeqnecuggftrfgrthhtvghrnhepteehudehkedtueejjeefffduveetue dugfehleeiffffueduhfduheefledvhfegnecuffhomhgrihhnpehpohhsthhgrhdrvghs necuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehmrghilhhfrhhomheprghnug hrvghssegrnhgrrhgriigvlhdruggvpdhnsggprhgtphhtthhopedvpdhmohguvgepshhm thhpohhuthdprhgtphhtthhopegughhrohiflhgvhihmlhesghhmrghilhdrtghomhdprh gtphhtthhopehpghhsqhhlqdhhrggtkhgvrhhssehpohhsthhgrhgvshhqlhdrohhrgh X-ME-Proxy: Feedback-ID: id4a34324:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Thu, 5 Feb 2026 15:52:30 -0500 (EST) Date: Thu, 5 Feb 2026 15:52:30 -0500 From: Andres Freund To: pgsql-hackers@postgresql.org Cc: David Rowley Subject: Unfortunate pushing down of expressions below sort Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Hi, I was recently [1] reminded of something I've seen be problematic before: We push down expressions below a sort node, even though they could be evaluated above. That can very substantially increase the space needed for the sort. A simplified (and extreme-y-fied) example: EXPLAIN (VERBOSE, ANALYZE) SELECT repeat(g.i::text, 1000) FROM generate_series(1, 10000) g(i) ORDER BY g.i; ┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ QUERY PLAN │ ├────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ Sort (cost=839.39..864.39 rows=10000 width=36) (actual time=65.905..66.552 rows=10000.00 loops=1) │ │ Output: (repeat((i)::text, 1000)), i │ │ Sort Key: g.i │ │ Sort Method: quicksort Memory: 38601kB │ │ -> Function Scan on pg_catalog.generate_series g (cost=0.00..175.00 rows=10000 width=36) (actual time=0.896..48.459 rows=10000.00 loops=1) │ │ Output: repeat((i)::text, 1000), i │ │ Function Call: generate_series(1, 10000) │ │ Planning Time: 0.063 ms │ │ Execution Time: 69.253 ms │ └────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ (9 rows) I can manually rewrite that be executed better: EXPLAIN (VERBOSE, ANALYZE) SELECT repeat(i::text, 1000) FROM (SELECT * FROM generate_series(1, 10000) g(i) ORDER BY g.i); ┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ QUERY PLAN │ ├────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ Subquery Scan on unnamed_subquery (cost=764.39..864.39 rows=10000 width=32) (actual time=2.642..50.738 rows=10000.00 loops=1) │ │ Output: repeat((unnamed_subquery.i)::text, 1000) │ │ -> Sort (cost=764.39..789.39 rows=10000 width=4) (actual time=2.633..3.342 rows=10000.00 loops=1) │ │ Output: g.i │ │ Sort Key: g.i │ │ Sort Method: quicksort Memory: 385kB │ │ -> Function Scan on pg_catalog.generate_series g (cost=0.00..100.00 rows=10000 width=4) (actual time=0.999..1.690 rows=10000.00 loops=1) │ │ Output: g.i │ │ Function Call: generate_series(1, 10000) │ │ Planning Time: 0.063 ms │ │ Execution Time: 51.648 ms │ └────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ (11 rows) Note that the runtime as well as the memory usage are reduced noticeably. It's even worse when there is a LIMIT above the sort, because it leads to evaluating the expression way more often than needed: EXPLAIN (VERBOSE, ANALYZE) SELECT repeat(g.i::text, 1000) FROM generate_series(1, 10000) g(i) ORDER BY g.i LIMIT 10; ┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ QUERY PLAN │ ├──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ Limit (cost=391.10..391.12 rows=10 width=36) (actual time=50.910..50.912 rows=10.00 loops=1) │ │ Output: (repeat((i)::text, 1000)), i │ │ -> Sort (cost=391.10..416.10 rows=10000 width=36) (actual time=50.908..50.909 rows=10.00 loops=1) │ │ Output: (repeat((i)::text, 1000)), i │ │ Sort Key: g.i │ │ Sort Method: top-N heapsort Memory: 36kB │ │ -> Function Scan on pg_catalog.generate_series g (cost=0.00..175.00 rows=10000 width=36) (actual time=0.869..47.820 rows=10000.00 loops=1) │ │ Output: repeat((i)::text, 1000), i │ │ Function Call: generate_series(1, 10000) │ │ Planning Time: 0.074 ms │ │ Execution Time: 50.938 ms │ └──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ (11 rows) vs: EXPLAIN (VERBOSE, ANALYZE) SELECT repeat(i::text, 1000) FROM (SELECT * FROM generate_series(1, 10000) g(i) ORDER BY g.i LIMIT 10); ┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ QUERY PLAN │ ├──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ Subquery Scan on unnamed_subquery (cost=316.10..316.20 rows=10 width=32) (actual time=3.098..3.149 rows=10.00 loops=1) │ │ Output: repeat((unnamed_subquery.i)::text, 1000) │ │ -> Limit (cost=316.10..316.12 rows=10 width=4) (actual time=3.086..3.090 rows=10.00 loops=1) │ │ Output: g.i │ │ -> Sort (cost=316.10..341.10 rows=10000 width=4) (actual time=3.083..3.085 rows=10.00 loops=1) │ │ Output: g.i │ │ Sort Key: g.i │ │ Sort Method: top-N heapsort Memory: 25kB │ │ -> Function Scan on pg_catalog.generate_series g (cost=0.00..100.00 rows=10000 width=4) (actual time=1.482..2.244 rows=10000.00 loops=1) │ │ Output: g.i │ │ Function Call: generate_series(1, 10000) │ │ Planning Time: 0.073 ms │ │ Execution Time: 3.185 ms │ └──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ Now, a repeat(,1000) is obviously a silly example, but I think this is a real issue. In the case in [1], deferring the evaluation of acldefault() till after the sort reduces memory consumption by ~38% Why are we evaluating the expression below the sort instead of above? I can maybe see an argument for doing that if it's volatile, but it's not. Interestingly we seem to do the sane thing for aggregation: EXPLAIN (VERBOSE, ANALYZE) SELECT repeat(g.i::text, 1000) FROM generate_series(1, 10000) g(i) GROUP BY g.i; ┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ QUERY PLAN │ ├──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ HashAggregate (cost=125.00..128.50 rows=200 width=36) (actual time=4.575..52.142 rows=10000.00 loops=1) │ │ Output: repeat((i)::text, 1000), i │ │ Group Key: g.i │ │ Batches: 1 Memory Usage: 553kB │ │ -> Function Scan on pg_catalog.generate_series g (cost=0.00..100.00 rows=10000 width=4) (actual time=0.897..1.518 rows=10000.00 loops=1) │ │ Output: i │ │ Function Call: generate_series(1, 10000) │ │ Planning Time: 0.042 ms │ │ Execution Time: 53.126 ms │ └──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ (9 rows) Note that the repeat() is computed above the aggregate. That also is true if it's a sort based agg... Greetings, Andres Freund [1] https://postgr.es/m/wgf63h3doepg2jnmofzbygrg7jujbjvxwkvoc7arej2zqcuf6c%403tzz22tizuew