--- title: Tune or accept the projected player-week data-view shape (8.4s) type: task status: planned description: >- Decide whether to tune or accept a recurring ~8.4s projected player-week data-view query (██████████ 6f82f1253f847bc4), driven by wide projection fan-out. base_uri: user:task/league/data-views/tune-or-accept-projected-week-player-year-shape.md created_at: '2026-09-17T01:19:53.398Z' entity_id: 84bcc46b-d149-4b86-a7fb-dc2bcd6aecc9 observations: - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ - ████████████████████████████████████████████████ owner_identity_uri: █████████████████████████ priority: Low public_read: true tags: - user:tag/league-xo-football.md - user:tag/software-task.md updated_at: '2026-10-02T05:08:09.792Z' visibility_analyzed_at: '2026-11-05T23:49:04.159Z' --- ## Overview `██████████` signature `6f82f1253f847bc4` (view `3a7fa1f2-bb37-4519-8bc9-868005ab8db0`) is a recurring ~8.4s data-view execution: year+week axes, 17 projected columns (`player_week_projected_*`), 500-row cap, user 134. Emitted 2026-09-16 03:13 (`130418`, closed as a one-off with "no recurrence") and re-emitted 09-16 13:40 (`130498`, 8416ms execution, admission wait 1ms), so the one-off call was wrong — the shape recurs whenever user 134 runs this view (the same statement ran ~8.4s three times within three minutes in the 13:40 window). ## Recovered mechanism From `postgres-log@2026-09-16T13:40:24.964Z` (auto_explain, league host): single-year (2026) statement, weeks 1-18, over per-year partitions (`player_gamelogs_year_2026` seq scan 8831 rows, replacement-slot-pruned). The cost is not partition scans — it is the wide projection fan-out: a `Nested Loop` at estimated cost ~2.87M over a `Gather Merge -> Sort -> Nested Loop -> Hash Join` spine fanning ~74,418 (player, week, dim) rows against materialized projection aggregates, 8.4s on CPU with 1GB work_mem. This is the same class as the accepted 7-year shape `da81f8407752c611` (measured 6.6s, accepted at 8s): wide view, no dominant planning defect, index-driven except the unavoidable projections fan. ## Why a task and not a quick fix No `ACCEPTED_SLOW_QUERIES` entry exists for this signature, so each run correctly emits. The remedy is either an acceptance entry (measured, ~8.4s ceiling, but the acceptance doctrine requires a stated declined cost and re-earn on shape change) or a genuine plan fix (the projection fan is the candidate target, e.g. materializing/ordering the dims join so the nested loop degenerates to index probes). Both are league-repo changes in the deployed API path (`libs-server/data-views/execute-data-view-request.mjs`), and deploy is operator-gated. ## Plan - [ ] Reproduce the statement (recovered in the postgres log at 13:40:33 UTC 09-16; the debug workflow's SQL-extraction path is `███████████████████████████████████████████`) under `EXPLAIN (ANALYZE, BUFFERS)` quiet and loaded, per the service-objectives oracle (data-view telemetry, never pg_stat_statements). - [ ] Decide per signature: add `6f82f1253f847bc4` to `ACCEPTED_SLOW_QUERIES` with the measured ceiling and declined cost stated, or land a plan fix that brings it under the 5s target. - [ ] Verify: a repeat run at the new ceiling emits nothing, a run past the ceiling still emits; back-to-back pair per the league verification rule. - [ ] Close `130498` (and the dedup key's future re-fires) only with the deploy of the decide. ## Cluster ███████████████, 2026-09-23 — nine more firings, mechanism verified Nine `██████████` rows from 09-20 to 09-22 are this family. All are user 134 ALT-prop views carrying a `line` row axis alongside `year` and `week`, with `player_week_projected_*` columns. | Signal | Path | Graded | Admission wait | Rows | Signature | | ------ | ---- | ------ | -------------- | ---- | --------- | | 131490 | export (bulk) | 44,181ms | 1ms | 12,315 | `ff2a926faedf9a89` | | 131488 | socket | 44,277ms | 0ms | 500 | `99a487b4233cde23` | | 131486 | socket | 43,261ms | 1ms | 500 | `fc86ac09abc58c8c` | | 131485 | socket | 30,421ms | 1ms | 500 | `d3bca5d2c10b11e3` | | 131491 | export (bulk) | 21,801ms | 1ms | 12,315 | `f597622c8ae122ee` | | 131487 | socket | 21,790ms | 0ms | 500 | `9aa9dad0aaa4fcd6` | | 131109 | export (bulk) | 14,284ms | 0ms | 3,334 | `eb430ac4ef957328` | | 131110 | export (bulk) | 7,291ms | 0ms | 2,001 | `55a4d2995af145cd` | | 131489 | export (bulk) | 5,458ms | 1ms | 153 | `c2c7006ce3c9bdce` | `131109` and `131110` repeat the exact signatures already verified here as 130964 and 130965. **The cost driver is the line axis over ALT markets.** The 44.1s plan for 131490, read from `auto_explain` on host `league` at 2026-09-21 22:15:32, builds a `player_years_weeks_lines` CTE as a DISTINCT over `prop_markets_index` joined to `prop_market_selections_index` on `selection_metric_line`, then joins projections onto that grain. An ALT market publishes many alternate lines per player-game, so the row grain multiplies by the line count before any limit applies — the plan carries 16 Sort nodes and 15 Nested Loops even with `enable_nestloop = off`, which is a cost penalty rather than a prohibition. The saved views confirm the shape: `5cebb9b1` and `6a03fc43` are "Testing - Alt Receiving Yards" for 2024 and 2025, `31cfad35` and `ae512205` are the rushing-yards equivalents, each bounded to one season's 18 weeks. So the years are already narrow — narrowing the year range is NOT the available lever here, which distinguishes this family from the all-history from-plays one. **Not the bulk-admission class.** Every row waited 0-1ms for admission, and `e53f8af49` (grade bulk on execution, not on the serial lane's wait) is merged with two commits on top of it, so the exports here are genuine execution cost rather than the lane reporting its own designed behaviour. None of the nine carries an `ACCEPTED_SLOW_QUERIES` entry, so each emitted correctly.