Error: LIMIT/OFFSET Scans Entire Result Set Before Applying Offset in DuckDB

Error: LIMIT/OFFSET Scans Entire Result Set Before Applying Offset

When a query includes a large OFFSET value, DuckDB’s planner performs a full scan of the entire result set before discarding the first n rows. This behavior can lead to significant performance degradation on tables with millions of rows.

Additionally, the planner does not automatically reorder predicates so that a preceding WHERE clause is applied before the LIMIT/OFFSET. Nested subqueries that contain their own LIMIT are also not pushed down; the outer query still scans the entire subquery result before applying its own limit.

These constraints make efficient pagination difficult without manual query rewrites, and it is unclear whether future releases will address these optimization gaps.

Unresolved Questions

• Does DuckDB provide a mechanism to push LIMIT/OFFSET into nested subqueries or materialized views?
• Will upcoming versions include automatic predicate reordering to apply WHERE clauses before LIMIT/OFFSET?
• What is the recommended pattern for avoiding large OFFSET values when paginating large datasets?