---
title: Optimize the multi-year player-year receiver-rate data-view queries (5.5-8.9s)
type: task
status: completed
description: >-
  Three slow-query signatures (126394/126395/126396) share a player-year receiver-rate view over
  2023-2025; the fantasy-points-from-plays CTE scanning 2-3 seasons is the dominant cost (3.5-6s).
  Genuinely above the 5s data-view target; distinct from the year-week fan-out family.
base_uri: user:task/league/data-views/optimize-multi-year-player-year-receiver-rate-data-views.md
created_at: '2026-08-21T23:14:46.659Z'
entity_id: 1c122d5f-d6fb-4ac0-9ded-9542fe60939d
finished_at: '2026-08-21T23:50:05.267Z'
observations:
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
owner_identity_uri: █████████████████████████
priority: Medium
public_read: true
relations:
  - relates [[user:task/league/data-views/optimize-multi-year-player-year-week-fanout.md]]
  - >-
    relates
    [[█████████████████████-██████████████-████████-███████████-██████-██████-███-████-██████████]]
tags:
  - user:tag/league-xo-football.md
updated_at: '2026-08-22T00:30:49.936Z'
visibility_analyzed_at: '2026-11-06T00:17:46.824Z'
---

# Optimize the multi-year player-year receiver-rate data-view queries (5.5-8.9s)

## Context

Signals 126394 (low, 8888ms, `8ebe0f5ac5ac0661`), 126395 (low, 5533ms, `3e3e0da8b0a16928`) and 126396 (low, 5878ms, `0fd75bf7592a8428`) fired 2026-08-21 16:29-16:32 UTC from `libs-server/data-views/execute-data-view-request.mjs`, `path=socket`, anonymous, all from one transient view (`request_id c0230ca7-ab27-497d-a828-a15cc8b3be7b` — the same anonymous client toggling columns). Row counts 500 / 136 / 136. Statements and plans extracted from the production Postgres log (12:29-12:32 EDT).

## Mechanism (measured)

All three are the same family: a **player-year-grain receiver-rate view over a 3-year range (2023-2025)**, using the `base_years` + `player_years` (CROSS JOIN player) fan-out machinery. The three signatures differ only in which rate columns are present, the WR `player_position` filter (absent in 126394, present in 126395/126396), and the year span of a couple of CTEs.

The dominant cost in all three is the **fantasy-points-from-plays CTE** (`t3bdc9b5…`, the `filtered_plays` machinery — the same emitter as `t9b050d8…` in the 126307 query): per-player fantasy points over 2-3 full seasons of `nfl_plays` (`nfl_plays_year_2023/2024[/2025]`), including the `nfl_play_stats` return-TD gate. Measured CTE cost:

- 126394: **6041ms** (years 2023-2024) + final WindowAgg over 84,387 player-year rows (no WR filter)
- 126395: **3480ms** (2023-2024), total 5486ms, 136 rows after WR filter
- 126396: **3821ms** (2023-2025 receiver CTE), total 5791ms, 136 rows

Unlike the year-week fan-out task, these CTEs **do** carry `season_year in (…)` on `nfl_plays` (`add-player-stats-play-by-play-with-statement.mjs:181`), so partitions are pruned to the requested years. The cost is intrinsic multi-season computation, scaling roughly linearly with the year count and with the number of rate columns — each rate column emits its own CTE that re-scans the play/gamelog rows. The fantasy-points CTE computes for **all players** even when the view filters to WR (position is a `player` filter, not applied inside the fantasy CTE), so the WR-filtered variants pay the same dominant CTE cost for fewer displayed rows.

JIT is not a factor: `jit = off` since 2026-08-13 (bootstrap drop-in, `operational-changelog.md:1241`, verified), no JIT nodes in the plans. These are genuinely above the 5s data-view target.

## Candidate directions (need design + operator ruling)

1. Constrain the default year range when no year filter is set — the same semantic ruling as `optimize-multi-year-player-year-week-fanout.md`; these views carry explicit 3-year ranges, but the range-default path is shared.
2. Make the multi-year fantasy-points-from-plays CTE cheaper: reduce re-scans per column (share one materialized play scan across columns), or cut the per-year cost of the `nfl_play_stats` return-TD gate.
3. Push the `player_position` filter into the fantasy-points CTE's scan (only WR-relevant plays) when the view is position-filtered — saves the 3.5-6s fantasy CTE for a 136-row result.

## Plan

- [x] Profile a rebuilt multi-year receiver-rate statement (`EXPLAIN (ANALYZE, BUFFERS)`) to confirm the fantasy-points CTE is the dominant term across the family
- [x] Pick the cheapest candidate (2 or 3 above) that stays within the data-view golden contract; get operator ruling for any default-range change
- [x] Verify: `EXPLAIN (ANALYZE, BUFFERS)` under the 5s target for all three signatures' shapes
- [x] Keep `check-data-view-sql-validity` and `test/data-view-queries/*` goldens green

## Verification

Discriminator: `EXPLAIN (ANALYZE, BUFFERS)` of the statements in the production Postgres log; pass = Execution Time under 5000ms for each of the three shapes.

## Key locations

- `libs-server/data-views/add-player-stats-play-by-play-with-statement.mjs` — from-plays/fantasy-points emitter (the dominant CTE)
- `libs-server/data-views/get-data-view-results.mjs:508-574` — `get_year_range` (shared year-range default)
- Related: `user:task/league/data-views/optimize-multi-year-player-year-week-fanout.md`, `███████████████████████████████████████████████████████████████████████████████████████████`