Skip to content

OFFSET n LIMIT m stops get_field() from being pushed into the file scan #27495

Open

Description

@cool9850311

Is your feature request related to a problem or challenge?

get_field() (s['a']) is normally pushed into the file scan, so the scan reads
only the field the query uses. Adding a skip to a fetch (OFFSET n LIMIT m)
against a source that supports skip pushdown stops that: the scan falls back to
reading the whole struct column and the field is extracted above it. The query
still returns the right rows, so only EXPLAIN shows it. Paginating over a wide
struct column therefore reads every field to use one.

datafusion-cli, stock configuration, run in a directory containing an empty
offset_repro/:

COPY (SELECT named_struct('a', value, 'b', value * 2) AS s FROM range(0, 20000))
TO 'offset_repro/st.parquet' STORED AS PARQUET;

CREATE EXTERNAL TABLE t
STORED AS PARQUET LOCATION 'offset_repro/st.parquet';

EXPLAIN FORMAT INDENT SELECT s['a'] AS v FROM t LIMIT 3;
EXPLAIN FORMAT INDENT SELECT s['a'] AS v FROM t OFFSET 5 LIMIT 3;
-- LIMIT 3
DataSourceExec: file_groups={1 group: [[st.parquet]]}, projection=[get_field(s@0, a) as v], limit=3, file_type=parquet

-- OFFSET 5 LIMIT 3
ProjectionExec: expr=[get_field(s@0, a) as v]
  GlobalLimitExec: skip=5, fetch=3
    DataSourceExec: file_groups={1 group: [[st.parquet]]}, projection=[s], limit=8, file_type=parquet

Both queries return rows and the values are right. Only the scan projection
differs. The queries have no ORDER BY, so which rows the skip lands on is not
fixed.

The cause is the same as #27494: a source that supports skip pushdown
wraps its scan in a GlobalLimitExec for the skip
(datafusion/catalog-listing/src/table.rs:814), which lands between the scan
and the projection the leaf-extraction rules placed on it, and nothing moves the
projection back down past that node. For file_row_index() and
input_file_name() that is an execution error; for get_field() it only costs
I/O, which is why it is filed separately.

Describe the solution you'd like

The scan should keep reading projection=[get_field(s@0, a)] whether or not the
query has an OFFSET:

Query DataFusion's scan reads Wanted
SELECT s['a'] AS v FROM t LIMIT 3 projection=[get_field(s@0, a) as v] unchanged
SELECT s['a'] AS v FROM t OFFSET 5 LIMIT 3 projection=[s], with the field extracted in a ProjectionExec above the limit projection=[get_field(s@0, a)], as without the OFFSET

Describe alternatives you've considered

  • Split a projection that mixes a leaf expression with one that must stay in place, and send only the leaf half below the limit. This is what PR fix: split a leaf projection stranded above a limit by a pushed-down OFFSET #27395 does: on that branch the second plan above becomes DataSourceExec: ..., projection=[get_field(s@0, a) as __datafusion_leaf_0], limit=8 with the GlobalLimitExec above it, so the field is extracted in the scan again.
  • Give GlobalLimitExec a try_swapping_with_projection so ProjectionPushdown can move the projection below it directly. That is narrower, but it has to hold for every projection above a limit, not only leaf ones, and a limit's skip/fetch must survive the swap.

Additional context

  • The skip reaches the scan through skip > 0 && scan.source.supports_skip_pushdown() (datafusion/optimizer/src/push_down_limit.rs:112), and GlobalLimitExec has no try_swapping_with_projection today.
  • With more than one file group, EnsureRequirements adds a CoalescePartitionsExec under that limit, so two nodes sit between the scan and the projection rather than one.
  • Split out of file_row_index() / input_file_name() error and get_field() loses pruning under OFFSET #27131 at the reviewer's request; the measurements there came from the same runs.
  • Tested on main at 1841fb6ec; line numbers are from that commit.

Willingness to contribute

I plan to contribute to this feature independently (PR #27395).

Activity

  1. cool9850311 commented on Nov 17, 2026

    @cool9850311
    ContributorAuthor

    take

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

No labels
No labels

Type

No type

Projects

No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions