ORDER BY with function gets much slower when combined with LIMIT as function is evaluated for all rows

What happens?

When I use a scalar function in the ORDER BY clause together with LIMIT, DuckDB seems to evaluate the function for all rows in the input, which becomes very slow on large or remote datasets (for example Parquet files on S3). The surprising part is that the problem only appears when there is a LIMIT. If I remove the LIMIT, the query is significantly faster and shows less S3 activity. So performance gets worse when I add a LIMIT, which feels counter intuitive. On a 75GB over S3 dataset, it just hangs almost forever !

To Reproduce

See the difference between the query plans

CREATE TABLE t AS
SELECT
    i AS id,
    'some fairly long string ' || i::VARCHAR AS txt
FROM range(1000000) AS t(i);

EXPLAIN SELECT id, txt
  FROM t
  ORDER BY length(txt);

EXPLAIN SELECT id, txt
  FROM t
  ORDER BY length(txt) LIMIT 10;

OS:

any

DuckDB Version:

1.2, 1.3, 1.4

DuckDB Client:

CLI

Hardware:

No response

Full Name:

Guillaume SUEUR

Affiliation:

Geomermaids LLC

Did you include all relevant configuration (e.g., CPU architecture, Linux distribution) to reproduce the issue?

• Yes, I have

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant data sets for reproducing the issue?

Not applicable - the reproduction does not require a data set