cool9850311 opened a new issue, #27495:
URL: https://github.com/apache/datafusion/issues/27495

   ### 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/`:
   
   ```sql
   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;
   ```
   
   ```text
   -- 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 #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 #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).
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: [email protected]

For queries about this service, please contact Infrastructure at:
[email protected]


---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]

Reply via email to