Skip to content

SQL whole-table read: Stage 3 stores the table's raw rows as an ingestion-time view with retention 0 and costs it as one row (SELECT corr(a, b) FROM t) #1321

Open

Description

@zzylol

Example

A SQL query over a whole table, with no time predicate. It runs every 10 s, needs an exact answer, and the table is ingesting at 100 rows/s with rows_held: 1000:

SELECT corr(a, b) FROM t

Table t is (ts timestamp, k utf8, x float64, a float64, b float64). The result is the same without the ts column.

Workload, as run with asap_planner on main (59365ce):

{"query_workload": {"language": {"sql": "datafusion_sql"}, "repeating_queries": [
  {"query": "SELECT corr(a, b) FROM t",
   "demand": {"fixed_interval_at": {"interval": 10000, "evaluation_phase": 0}},
   "requirements": {"accuracy": {"max_error": 0.0, "failure_probability": 0.0}},
   "predictability": {"predictable": {"known_at": null}}}]},
 "data_workload": {"arrival": {"ingesting": {"rows_per_second": 100.0}},
   "sources": {"t": {"series_count": 10, "sample_interval": 10000, "rows_held": 1000}}}}

Today. Stage 3 selects this plan, at 1.19e-4 per second:

node operator timing / lifetime retention_ms cost detail
0 Scan(t) IngestionTime / None scan 1 sample
1 Project(a, b) IngestionTime / AcrossEvaluations 0 "keeps 96 bytes" (one row)
2 Aggregate(PearsonCorr) QueryTime / OneEvaluation "hash aggregate 1 rows"
3 Project QueryTime / OneEvaluation

plan_installable refuses it: installable plan: materialized view 1 reads a source outside a raw-data window.

The plan is also wrong, apart from the refusal:

  • Node 1 is a copy of the table's raw rows, stored as a view.
  • The query reads every row of the table at each evaluation, but the view gets retention_ms 0: it keeps only the newest ingestion run's rows.
  • asap-cost charges the view for one row (96 bytes), and the query-time Aggregate for reading one row.
  • The same query planned over data at rest reads the table at query time. Its Aggregate is charged for 1,000 rows, and the plan for 7.8e-4 per second, about 6.5 times the selected one. So the selected plan wins only because it is costed as reading one row.

Expected. The view of raw rows should either not be selected, or be selected with a retention and a cost that cover every row the Aggregate reads. Today that leaves the query-time plan Scan → Project → Aggregate → Project, which reads the retained table at each evaluation.

A second case: SELECT a FROM t, which returns the whole column, gets the same kind of plan at 1.88e-5 per second.

  • Scan feeds a root Project (IngestionTime, AcrossEvaluations), with retention_ms 0 and charged for one row ("keeps 96 bytes").
  • Install fails with the same error for view 1.
  • Over data at rest, the same query plans at query time (6.8e-4 per second) and installs.

SELECT sum(a) FROM t also fails on main, with the same error for view 2. That plan is a different shape: Scan → Aggregate(Sum) (IngestionTime, None) → Project (IngestionTime, AcrossEvaluations). It is the SQL case of #1288, also filed as #1320. Its view holds one aggregate row, which is the correct size for a running sum. The planner is not wrong there; the executor cannot refresh it yet. This issue is only about the raw-row views in the corr and SELECT a plans. Those are wrong whatever the installer accepts.

Why it happens

  1. Stage 2 offers the raw-row Project an ingestion-time view.
    • The Project's group does not depend on the evaluation time.
    • So ingestion_time_offered_per_group and across_evaluations_offered_per_group (crates/stage2-physical-optimization/src/group.rs:39, :70) both offer it, and (IngestionTime, AcrossEvaluations) is a candidate.
  2. Stage 3 gives that view retention 0.
    • retention_need_ms (crates/stage3-plan-selection/src/cost.rs:102) calls asap_types::plan::time_window::windows_readers_need (crates/types/src/plan/time_window.rs:252).
    • The walk asks whether a CurrentTimeOnly reader of the producer needs every row: plan.current_time_read_needs_every_row(producer), at line 256.
    • Every implementation answers yes only for a Scan:
      • Stage 3's candidates: crates/stage3-plan-selection/src/retention.rs:97;
      • the plan: crates/types/src/plan/time_window.rs:209.
    • The Aggregate reads the Project CurrentTimeOnly. The Project is not a Scan, so line 273 gives the window {0, 0}, and the view's retention is 0.
    • The Project's rows are raw rows, though, and are not stored by evaluation time, just like the Scan below it. Read whole, the Project needs every row, exactly like the Scan.
    • In SELECT a FROM t the view is the root. Only the query output reads it, and the query output is not a reader in the walk, so the walk finds no reader. asap_cost::read_back_ms then returns 0 ("0 when nothing reads it").
  3. The cost follows from that retention.
    • A view with retention_ms 0 keeps one output, and one output of an ingestion run is the rows of one run.
    • So the view and the Aggregate reading it are each charged for 1 row.
  4. The installer is right to refuse the plan.

Proposal

Change the answer to "does a current-time read of this producer need every row" to include raw rows passed through.

  • Rule. A producer needs every row when it is a Scan, or when it keeps raw rows one for one: a Project or Filter whose input needs every row, with no TimeRange between.
  • Query outputs. A query output that reads a root counts as a CurrentTimeOnly reader, as the query-time Aggregate does in the corr plan.
  • Effect on the walk. windows_readers_need then returns None (unbounded) for both Project views.
  • Effect on the cost. node_resource_usage_and_cost does not cost a view without a retention (crates/cost/src/node/usage/mod.rs:301), so Stage 3 leaves the candidate out. The query-time plan is selected, at 7.8e-4 per second in this example.
  • Where to change it. The rule can be derived inside windows_readers_need from inputs_of and operator_of. Then the Stage 3 candidates, the plan (plan_cost, materialized_views) and the executor agree, with no change to the TimeIndexedPlan implementations.

Alternative: Stage 2 stops offering AcrossEvaluations to a raw-row Project or Filter over a Scan, since such a view only copies raw data that raw-data retention keeps already. This is narrower. It still leaves the retention walk wrong for any other path that stores raw rows above a Scan.

Out of scope: views over a whole table whose size does not grow with the table, such as the running sum above. Those are #1288.

Acceptance

Planner integration tests that fail today, both with the workload above:

  • SELECT a FROM t. It plans without an ingestion-time view of raw rows, plan_installable returns Ok, and the answer is every row of the table.
  • SELECT corr(a, b) FROM t. It plans without such a view either. On main the executor has no PearsonCorr kernel yet ("aggregate intent has no native implementation"), so its install-and-answer check needs the exact reduce-aggregate kernels in feat(executor): grouped stddev/variance, corr, group, count_values, exact topk and without grouping #1242.
  • Retention unit test. A windows_readers_need test: a stored Project over a Scan, read by a CurrentTimeOnly reader, is read unbounded (None).

Related

Activity

  1. added
    bugSomething isn't working
    cost-modelCost formulas, statistics, ranking, and selection
    optimizerWorkload optimization and plan selection
    sqlSQL front-end lowering (DataFusion → L2)
    on Nov 15, 2026
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't workingcost-modelCost formulas, statistics, ranking, and selectionoptimizerWorkload optimization and plan selectionsqlSQL front-end lowering (DataFusion → L2)

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions