---
title: Optimize the multi-year player-year-week data-view fan-out (76s wide year/week views)
type: task
status: completed
description: >-
  A player_year_week data view with no year filter defaults to the 2000-current year range, fanning
  out to 13M intermediate rows and sorting ~1.7GB of temp spill (~76s execution, measured twice).
  Constrain or restructure the fan-out to meet the 5s data-view target.
base_uri: user:task/league/data-views/optimize-multi-year-player-year-week-fanout.md
created_at: '2026-08-19T22:40:19.345Z'
entity_id: 3a2ab7b1-c443-4ad4-b34b-390ff1ee0971
finished_at: '2026-08-22T00:19:40.382Z'
observations:
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
owner_identity_uri: █████████████████████████
priority: Medium
public_read: true
tags:
  - user:tag/league-xo-football.md
updated_at: '2026-09-15T14:56:59.100Z'
visibility_analyzed_at: '2026-11-05T23:48:55.935Z'
---

# Optimize the multi-year player-year-week data-view fan-out

Resolved 2026-08-21 in league `eac6bb44d`, deployed the same night. The worst-case
statement went from 59.0s to 1.18s on production against a 5s target.

## What it was

Two independent defects, both required for the fix.

**The row axis could not see the view's time scope.** `single_nfl_week_id` — the
param the dfs-salary and betting-market column families carry — reached neither
scope reader. `resolve_view_scope` matched only `params.nfl_week_id`, and
`get_year_range` / `get_week_range` matched only `params.year` / `params.week`,
which `resolve_nfl_week_params` populates from `nfl_week_id` and never from
`single_nfl_week_id`. A view scoped only by that spelling looked entirely
unscoped, so the year axis took the full 2000-current default: 27 years CROSS
JOIN 28,129 players joined to `nfl_year_week_timestamp`, 13,079,985 intermediate
rows and ~1.7GB of temp spill. All three spellings now go through one reader,
`resolve_explicit_nfl_week_ids`, which deliberately has no current-week fallback.

**The outer query joined the same relation twice.** `player_years` and
`player_years_weeks` were both joined on `(pid, year)`, which is redundant —
`player_years_weeks` is built FROM `player_years`, so each of its rows already
carries the `(pid, year)` pair of exactly one `player_years` row, and the
identity's `year_column` is `player_years_weeks.year`. Both CTEs are opaque to
the planner, so the two-column correlation multiplied two independent
selectivity estimates into 1,188 rows where 506,322 came back, and the resulting
nested loop ran 295M join-filter comparisons. The guard lives in the
`player->player_year` bridge so both call sites inherit it.

Candidate 1 (narrowing the default year range) was not taken: making the third
spelling behave like the other two is the rule that already held, so no operator
ruling on default semantics was needed. Candidates 2 and 3 were not needed to
clear the target.

## Measured

Production `EXPLAIN (ANALYZE, BUFFERS)`, one statement shape in one window,
re-baselined after the `nfl_play_stats` index of bulletin 391:

| Tree | Execution time |
| --- | --- |
| HEAD | 59,048 ms |
| Scope fix alone | 35,912 ms (nested-loop plan flip) |
| Both fixes | 1,299 ms / 1,184 ms |

## Consequences worth knowing

- The empty view scope was also suppressing the `participation_status` signal,
  so week-split views scoped only by `single_nfl_week_id` were silently missing
  the BYE and did-not-play distinction. They now carry it.
- Five saved DFS-salary week views (user 134) render the scoped season only,
  rather than each week duplicated across 27 invisible years.
- 10 goldens moved; every diff reduces to the removed join plus alias-hash
  normalization. Four also absorbed table-alias hashes that were already stale
  at HEAD.
- No golden exercises `single_nfl_week_id`, so
  `test/data-views.single-nfl-week-id-row-axis-scope.spec.mjs` is the gate. Its
  six behavioral assertions go red at HEAD; its three controls stay green.

## Out of scope

Signal 126228 (7.6s, signature `206b53287b8bb9e5`) is a different cost centre —
a per-player nested loop over the `game_opponent` team lookup — and was not
addressed here. Residual `base_years` statements on production at 6.7-16.1s all
carry explicit year params, so they were never this no-year-filter fan-out; they
belong to the receiver-rate and period-CTE sibling tasks.

## Key locations

- `libs-server/data-views/resolve-single-nfl-week-id.mjs` — `resolve_explicit_nfl_week_ids`
- `libs-server/data-views/resolve-view-scope.mjs`, `libs-server/get-data-view-results.mjs` — the two scope readers
- `libs-server/data-views/identity-bridges/player-to-player-year.mjs` — the week-axis join guard
- `docs/guides/data.md` § Join-group identity and caching — the durable lesson