---
title: Pre-filter the KeepTradeCut arm before the multi-year player-year fan-out
type: task
status: completed
description: >-
  A multi-year KeepTradeCut player-year view probes 196,903 player-year rows three times over for
  valuations only 893 of 28,129 players have, so ~97 percent of 590,709 probes return nothing. Not
  an index defect and not reachable by the dfs_slate_sizes fix; the fan-out itself has to shrink.
base_uri: user:task/league/data-views/prefilter-keeptradecut-arm-before-fanout.md
created_at: '2026-08-25T23:12:22.861Z'
entity_id: d02cd368-91be-4d96-b63d-396be2e31bf5
finished_at: '2026-08-29T16:46:59.785Z'
observations:
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
owner_identity_uri: █████████████████████████
priority: Medium
public_read: true
relations:
  - relates [[█████████████████████-████████████-███-█████-█████-████-███████]]
  - relates [[████████████████████████-██-█████████████-████-███████-██████████████]]
started_at: '2026-08-28T22:49:52.167Z'
tags:
  - user:tag/league-xo-football.md
  - user:tag/software-task.md
updated_at: '2026-08-29T17:51:39.016Z'
visibility_analyzed_at: '2026-11-05T23:48:57.318Z'
---

# Pre-filter the KeepTradeCut arm before the multi-year player-year fan-out

## Overview

The task's original premise was wrong and the emission plan disproves it. Signal 126668's query
contains NO correlated KeepTradeCut subqueries: all three arms already ran as the `bebe363cd`
`DISTINCT ON` CTEs, so the "590,709 correlated probes" this task was created to remove did not
exist, and the remedy it names was already shipped.

The real cost was cold physical I/O. The `DISTINCT ON` form scanned 112,115 rows in the as-of
window and sorted them down to 3,347, taking one heap fetch per candidate row -- 4.26s of the
7.7s emission, and the whole gap between 7,765ms cold and 2,731ms warm.

Replaced with a lateral probe per (valued player, year) against the existing primary key.
Full-query buffers 429,408 -> 188,422.

## Background

Two candidate fixes were measured and REFUTED; do not retry either without new evidence.

| candidate | result |
|---|---|
| Restrict `opening_days` to the view's years | No benefit: 113,189 -> 112,980 buffers. Entry 031's warm rejection was correct. |
| Covering index on the metric columns | REFUTED ON THE WRONG GROUND, then re-refuted on a sound one. The first refutation measured `relallvisible` at 32 of 21,378 pages, which was a missing visibility map rather than a property of the shape. Post-vacuum the index DOES work -- `(is_superflex, observed_at, pid) INCLUDE (metrics)` turns the DISTINCT ON into a pure index-only scan at 899 buffers against the lateral's 25,102. It still loses: 2.8x slower warm (161ms vs 58ms, the cost is CPU on cached buffers), and the importer merges exactly the three metric columns it must INCLUDE, so it would break HOT on a table running 95.5% HOT across 11.1M updates. Index built on production, measured, dropped. |

Note the year restriction IS load-bearing for the lateral form (a lateral does work per year), even
though it bought nothing under `DISTINCT ON`.

## Plan

- [x] **Re-measure the current shape on production**
  - Done via the emission plan itself (pg log 46382-46578, backend 2680008) rather than a re-run --
    better evidence, and the host was under a multi-hour format regeneration throughout.

- [x] **Restructure the KTC arm**
  - File: `libs-server/data-views-column-definitions/player-keeptradecut-column-definitions.mjs`
  - Lateral per-(player, year) probe; a shared `ktc_valued_pids` CTE (the list was being built three
    times, once per column); a loose index scan for that list (21,378 -> 3,636 buffers).
  - Equivalence verified across all 12 combinations of 3 metrics x 2 superflex x 2 boundary types,
    zero symmetric difference in each.

- [x] **Run the standing gates and regenerate moved goldens**
  - Full suite 5,067 passing. Golden query-match 279 passing, equal to a HEAD control worktree.
    Executed-result parity 55 passing, all 10 KTC cases green (the 1 `afterAll` swagger failure
    reproduces identically at HEAD -- a single-spec isolation artifact).
  - `db/gates/check-data-view-sql-validity.mjs`: 4,602 statements EXPLAINed cleanly.
    `db/gates/check-schema-conformance-ratchet.mjs`: 0 violations.
  - 16 goldens regenerated, diff is `expected_query` ONLY -- no `expected_rows` touched, so the
    hand-written oracles judged the change rather than absorbing it.

- [x] **Confirm no table-alias hash moved**
  - Emitted SQL in both trees and compared every `t[0-9a-f]{32}` across 27 KTC fixtures. Every
    difference is the same single ADDITION (the new `ktc_valued_pids` CTE hash); no pre-existing
    alias moved.

- [x] **Commit and merge**
  - Merged to master as `0a0d8c91d` (feature commit `037ebcbfc`). Worktree and branch removed.
  - Full suite on the merged branch: 5,082 passing, exit 0. Deployed 2026-08-29 as part of
    `b3c6af5f9` (see Notes).

## Notes

- **This is a cold-cache win only.** Warm the shape already met the sub-5s target (2,731ms) and the
  rewrite leaves warm roughly unchanged. It stops the target being missed when the cache is cold; it
  does not make the view fast.
- The title no longer describes the work (nothing is pre-filtered before the fan-out, and the
  fan-out is load-bearing for `__data_view_total_count__`). Retitling was raised and not ruled.
- `career_year` is now the largest single term in the optimized plan at 86,461 buffers, 46 percent.
  Dispatched to session `██████████████████████████` (thread `d2715494`) with the measured findings
  and the `COALESCE(..., 1)` correctness trap.
- The league test database is a single shared container and the suite drops every table. FIXED
  2026-08-29: `yarn test:isolated` (league `fee349dd4`) gives a run its own database and drops it
  afterwards, and `test/data-view-sql-sandbox.spec.mjs` no longer hardcodes `league_test` in its
  database-level GRANT/REVOKE -- which had made an "isolated" run both fail a live security
  assertion and mutate the shared database under any sibling.
- **DEPLOYED 2026-08-29.** Both hosts and `dist/build-manifest.json` at `b3c6af5f9`, verified by
  executing the deployed emitter on production rather than grepping source.

## Follow-on work, 2026-08-29

The successor session took the residue and closed all of it. Detail is in the observations; the
shape:

- **The week arm was the real win, and the brief had it as "possibly leave alone".** Measured, it
  was the largest cost in this column family -- 1,510,276 buffers over 458,250 correlated probes to
  produce 28,076 rows, paying twice per row. Rewritten as a lateral over valued pids
  (`0a6650fd2`): 1,510,276 -> 158,110 buffers, 2,243ms -> 423ms, zero symmetric difference, and the
  win holds at every population rather than trading narrow views for wide.
- **The database-wide empty visibility map was not autovacuum starvation.** The 0.0%-visible tables
  had no `_vm` fork on disk at all, dropped by the 2026-08-04/08-08 migration rewrites; no
  autovacuum knob observes that, and `nfl_plays_year_2022` sat at 0.0% with zero dead tuples. All
  57 tables below 95% were vacuumed in under two minutes. The general rule is now in
  `docs/guides/schema.md`, and `maintenance_io_concurrency` is 200 in the bootstrap drop-in.
- **The `withMaterialized` audit is closed with no action**, on measurement rather than reading:
  none of 24 materialized CTE instances reproduces the rows=1-driving-a-nested-loop signature.
- `career_year` remains the largest unowned data-view cost and its session is gone; folded into
  [[████████████████████████-██-█████████████-████-███████-██████████████]] rather than left here.