public inbox for [email protected]  

help / color / mirror / Atom feed

From: Chengpeng Yan <[email protected]>
To: Tom Lane <[email protected]>
To: Andres Freund <[email protected]>
Cc: PostgreSQL-development <[email protected]>
Cc: David Rowley <[email protected]>
Subject: Re: Unfortunate pushing down of expressions below sort
Date: Fri, 6 Feb 2026 14:37:59 +0000
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <qddu2agmbiadpqkavqlamccrqxg6rm64sdq6q7ihpyndyfim6k@2dpox3fupa2g>
	<[email protected]>
	<[email protected]>

Hi,

I took a closer look at make_sort_input_target() in
src/backend/optimizer/plan/planner.c.

The current heuristics only defer targetlist expressions past a Sort
when they are: 
* volatile, or 
* set-returning (due to SRF synchronization constraints), or 
* considered “expensive” (cost > 10 * cpu_operator_cost) and there is a
LIMIT.

Functions like repeat() and acldefault() have the default procost = 1,
so they don’t meet the “expensive” threshold and therefore remain below
the Sort, which is what leads to the tuple width inflation seen in these
examples.

Grouping behaves differently: make_group_input_target() unconditionally
flattens non-grouping expressions to Vars, which is why repeat() ends up
above the Aggregate node there.

Tom mentioned the md5(widecol) counterexample, where evaluating the
expression before the sort can actually reduce memory usage. The key
distinction seems to be whether the expression depends solely on sort
keys.

The approach I’m experimenting with is to defer an expression only when
all the Vars it depends on are sort keys. That gives the desired
behavior in both cases: 
* repeat(i, 1000) ORDER BY i: i is the sort key, so we defer and keep
the sort tuples narrow. 
* md5(widecol) ORDER BY id: widecol is not a sort key, so we keep the
expression below the sort and avoid carrying the wide column.

This seems to address the cases discussed in this thread and should be
low-risk for the common case.

I’m working on a patch along these lines; any thoughts?

--
Best regards,
Chengpeng Yan

view thread (4+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Unfortunate pushing down of expressions below sort
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox