---
title: Fix the data-view partition-pruning defect
type: task
status: planned
description: >-
  Period-denominator CTEs restrict the year on nfl_games and never on nfl_plays, so all 27
  partitions scan; a 2026-08-26 measurement contradicts the original win and must be reconciled
  first.
base_uri: user:task/league/data-views/fix-data-view-partition-pruning.md
created_at: '2026-09-22T15:32:03.011Z'
entity_id: 5ec4db03-1ae4-4937-bcb3-ac8f14a505e5
observations:
  - ████████████████████████████████████████████████
owner_identity_uri: █████████████████████████
priority: Medium
public_read: true
tags:
  - user:tag/league-xo-football.md
  - user:tag/software-task.md
updated_at: '2026-09-23T16:16:57.002Z'
visibility_analyzed_at: '2026-11-05T23:48:50.003Z'
---

## Problem

`nfl_plays` is `PARTITION BY RANGE (season_year)` with 27 partitions. The period-denominator CTEs the data-view generator emits restrict the year on `nfl_games.season_year` and never on `nfl_plays.season_year`, so the partitioned fact table has nothing to prune on and every query scans all 27 years.

```sql
from "nfl_plays" inner join "nfl_games" on "nfl_games"."esbid" = "nfl_plays"."esbid"
where "nfl_games"."season_year" in (2025)     -- restricts the join, not the partition
  and "nfl_plays"."season_type" in ('REG')     -- no season_year predicate on nfl_plays
```

The denominator CTE emitted immediately above it does it correctly, which is what made this look like an oversight.

## Measured value, as of 2026-08-05

Measured by rewriting the emitted SQL, output verified byte-identical on all 11 of 20 corpus views carrying the shape.

| View | Before | After |
| --- | --- | --- |
| Defense Coverage Matric | 4505ms | 806ms |
| Cover 1 By Week | 650ms | 104ms |
| Week Passing Matchup Preview | 3250ms | 629ms |
| 1st and 10 Tendencies by week | 337ms | 65ms |
| Team Plays By Quarter | 1438ms | 294ms |
| Week Receiving Matchup Preview | 3198ms | 1358ms |
| Advanced QB metrics | 3090ms | 1492ms |

Buffers for Defense Coverage Matric fall 2,570,276 to 133,902.

## Counter-evidence that lands AFTER the measurement, and must be reconciled first

This is the reason the work cannot be dispatched as originally briefed.

`libs-server/data-views/apply-scope-to-query.mjs:162` carries a comment added by `dc5e8defe` (2026-08-26, three weeks after the measurement above) recording the OPPOSITE result: emitting year on both `nfl_plays` and the `nfl_games` it joins "flipped the per-period CTE off a hash join and onto a 35,198-iteration nested loop (5.98M buffer hits against 179K)". The `has_season_year` parameter exists specifically to suppress the duplicate predicate, and `libs-server/data-views/output-aggregator/build-period-cte.mjs` passes `has_season_year: false` at two call sites for that reason.

Both results can be true on different CTE shapes, but nothing has established which shapes want the predicate and which are harmed by it. Settle that before writing the fix.

## Stale path in the original brief

The brief named `libs-server/data-views/rate-type/*.mjs`. That directory no longer exists — the modules live under `libs-server/data-views/period-denominator/` (`per-player-play.mjs`, `per-player-route.mjs`, `per-team-play.mjs`, `resolve-play-filter-scope.mjs`). The rename landed with the rate-type compat-shim retirement.

## Plan

- [ ] **Reconcile the two measurements.** Characterise which CTE shapes gain from the duplicate predicate and which regress. Verify: a stated rule predicting the direction, tested on a held-out view rather than the one it was derived from.
- [ ] **Decide where the fix belongs** — in `apply-scope-to-query.mjs` (emitting on both relations when both are in scope) or in the period-denominator callers. Read `apply-scope-to-query.mjs` and `physical-season-columns.mjs` before assuming; decide on the code, not on this plan.
- [ ] **Implement**, respecting the existing `has_season_year: false` call sites rather than overriding them.
- [ ] **Re-measure** on an isolated restore.
- [ ] **Regenerate goldens** only after the code is right; `EXPLAIN` third.

## Verification

Byte-identical output on every affected view — diff full result sets, not row counts. A faster wrong answer is the failure mode to guard against.

Harness: `scripts/benchmark-data-views.mjs`, corpus `test/data-view-benchmark/corpus.json` (20 views, `table_state` only, so `run` re-emits SQL from the working tree and measures the change). Both subcommands need `#db` pointed at a league-schema database in addition to `--dsn`, or every view reports `generate: role "league_test" does not exist`.

Measure on an ISOLATED restore, never production — production timings vary about 3.7x under batch load. Restore takes about 2m30s; the fact-table working set is about 3.2GB. Recipe and full findings in `scratch/data-view-columnar-eval/columnar-engine-evaluation.md`.

## Constraints

- Generate and measure from a CLEAN worktree at HEAD, never the shared tree — the emitter is read off the working tree, so a sibling's uncommitted rename is indistinguishable from the defect. This bit the original evaluation session.
- Run `node db/adhoc/check-data-view-sql-validity.mjs` from a clean worktree.
- Honor `.nvmrc` (Node 22.22.1).