---
title: Retire or gate the data-view nested-loop clamp
type: task
status: completed
description: >-
  Measured both ways over 228 saved views the clamp looked net-harmful, but that reading came from a
  25s cap that excluded every rescue by construction; at 300s the clamp takes one view from 186s to
  44s. Harm and rescue are both real, retiring now prices worst, and run-then-retry at a 30s trigger
  prices best.
base_uri: user:task/league/data-views/retire-or-gate-the-nested-loop-clamp.md
created_at: '2026-09-23T15:55:07.048Z'
entity_id: f92ce3a3-bd18-4583-af57-616528acdffd
finished_at: '2026-09-23T21:09:19.053Z'
observations:
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
  - ████████████████████████████████████████████████
owner_identity_uri: █████████████████████████
priority: High
public_read: true
relations:
  - relates [[█████████████████████-██████████████-████-████-███████████-███████████]]
  - relates [[user:task/league/data-views/tune-or-accept-projected-week-player-year-shape.md]]
  - relates [[█████████████████████-████████████-██-██████-████-████-█████-█████████]]
  - relates [[████████████████████████-████-██████-████-██████████████]]
  - blocked_by [[█████████████████████-██████████-███-███████-██████████-███-████-████-███████]]
tags:
  - user:tag/league-xo-football.md
  - user:tag/software-task.md
updated_at: '2026-09-23T21:09:19.053Z'
visibility_analyzed_at: '2026-11-05T23:49:00.015Z'
---

## Overview

`libs-server/get-data-view-results.mjs` plans every data-view statement twice: it runs `EXPLAIN (FORMAT JSON)`, asks `plan_carries_clamp_signature` whether the plan shows a join estimated at exactly one row alongside a Nested Loop carrying a Join Filter, and if so re-runs the statement under `enable_nestloop = off` with `max_parallel_workers_per_gather = 2`.

That arm was validated on plan STRUCTURE and never on latency. Its own module records the criterion: "every one of the 107 it selects has every Nested-Loop-with-Join-Filter site removed under `enable_nestloop = off`." Removing the rescan sites is true. It does not follow that the resulting plan is faster, and measured against the live saved-view corpus it usually is not — the clamp trades a nested-loop rescan for a Merge Join that generates and discards the same rows.

## What was measured

2026-09-23, host `league` quiet at load 0.46 of 12 cores. SQL regenerated from all 231 saved `user_data_views` through `get_data_view_results_query` so every statement is valid against today's schema, then each view timed in both arms with a 25s `statement_timeout`, randomized order. Evidence and re-run instructions in [[████████████████████████-████-██████-████-██████████████]].

On the 65 signature-positive views where both arms completed:

| Strategy | Total | vs today |
| --- | --- | --- |
| today — clamp whenever the signature matches | 167.1s | — |
| refined trigger — signature AND default-arm `nl_jf` >= 5 | 122.4s | -26.8% |
| **never clamp — retire the arm** | **96.9s** | **-42.0%** |
| per-signature measured choice (oracle) | 89.0s | -46.7% |

Supporting counts on the same population: 33 views more than 25% slower under the clamp against 11 more than 20% faster, and 21 neutral.

**The asymmetry at the timeout boundary is the sharpest single reading.** Six views completed on the default planner and then exceeded the 25s cap under the clamp — `20ae2077`, `ae512205`, `195d4ef2`, `294029d4`, `c4050d83`, `3dcbbfef`. Not one view went the other way **at a 25s cap** — and that qualifier turned out to carry the whole claim. Raising the cap reverses it; see the second-session section below before spending this reading.

## Why this is not measurement noise

- **Reproducible.** Six views re-timed twice each with the arms REVERSED reproduce within a few percent in both directions: `8cdadea6` 0.75s default against 9.55s clamped, `d10df45a` 0.29s against 5.7s, `c08b7f2a` 2.1s against 14.0s, `af5148e3` 0.06s against 2.2s — and genuinely helped in the other direction, `382308c4` 3.4s default against 2.6s clamped, `7e33594c` 0.98s against 0.38s.
- **The cache bias runs against the conclusion.** The sweep always ran default first, so warming favours the clamp arm, and the clamp still loses.
- **Not a result-size artifact.** The same six at `limit = 2000` rather than 500 give the same winners, losers and magnitudes, so the effect is a property of the shape.
- **`EXPLAIN ANALYZE` timings were NOT used for the comparison.** Instrumentation inflated the worst case from 44.8s wall clock to 95.0s; the table above is wall clock around `psql`.

## Mechanism

`EXPLAIN (ANALYZE, BUFFERS)` of the worst regression, saved view `ae512205` (the shape behind resolved signal 131490):

The join the builder emits spans three relations — `left join <market_cte> on <cte>.selection_pid = player.pid and <cte>.year = player_years_weeks.year and <cte>.week = player_years_weeks.week and <cte>.selection_metric_line = player_years_weeks_lines.selection_metric_line`. Under `enable_nestloop = off` the planner merges on `player.pid` alone and demotes year, week and line to a Join Filter, so one node produces 1,550,911 rows and discards **1,538,972** of them to return 12,315. That is the same discard pathology the clamp exists to prevent, relocated from a Nested Loop into a Merge Join.

Every join in the chain is estimated at 1 row against 12,315 actual, which is the cardinality collapse `diagnose-data-view-cardinality-collapse.md` already characterised. The clamp does not fix the collapse; it changes which operator pays for it.

## Why a cost comparison cannot decide this

The obvious fix — plan both ways and keep the cheaper estimate — does not work. `enable_nestloop = off` is implemented as a large additive cost penalty rather than a prohibition, so the clamped plan's estimate is dominated by that penalty. Measured across five views, the clamped arm estimates 1e13 to 1e14 against 1e5 for the default arm, on every one, INCLUDING the two where the clamp is genuinely 1.6x and 4.2x faster. Estimated cost is not comparable across the two settings and must not be used as the discriminator.

## Decision

Three options, and they are ordered by how much they recover.

1. **Retire the clamp arm.** *No longer recommended — see the second-session section; it prices worst on the line-axis population and leaves a 186s statement in production.* Recovers 42%, which is 90% of what a perfect per-signature oracle could achieve, and deletes a mechanism rather than adding one. `plan_carries_clamp_signature` and its double-planning `EXPLAIN` go with it.
2. **Gate it on a measured per-signature record.** Recovers 46.7% at the oracle. Worth only 8.1% more than option 1 — 7.8s across 65 views — and costs a new persisted arm-selection cache keyed on signature, plus a probe policy. Hard to justify against option 1 on those numbers.
3. **Tighten the trigger.** Requiring `nl_jf >= 5` in the default-arm plan recovers 26.8%, but hurt still outnumbers help at every threshold up to 8, where the trigger fires on only 10 views. Strictly worse than option 1.

## The risk, stated plainly

The clamp was introduced because four statement shapes were taking about 305s each and 53.5% of all data-view time, and forcing hash joins took the worst to about 1,600ms. **Those shapes are absent from this corpus.** The current production log carries 234 slow data-view statements and NOT ONE exceeds 60s in either arm, clamped or unclamped — the 300s population is gone. Whether the clamp is what removed it cannot be established from the log, because the schema and the query builder both moved substantially in the same window.

So option 1 is well-evidenced for the workload that exists now, and its exposure is a workload that no longer appears. Before shipping it, decide whether to keep a narrow safety net for a returning 300s shape — a `statement_timeout`-triggered retry under the clamp would serve that without paying the clamp on every matching statement.

## 2026-09-23, second session — the "rescues nothing" claim does not survive a higher cap

**The 25s cap hid every rescue, and the headline population excluded them by construction.** The table above covers the 65 signature-positive views *where both arms completed*. A view the clamp takes from 186s to 44s fails both arms at 25s, so it never entered the population — the measurement could only ever see harm.

Re-measured at a **300s** cap on host `league`, quiet:

| View | Default planner | Clamped | |
| --- | --- | --- | --- |
| `6a03fc43` | 185,991ms | 44,087ms | **4.2x rescue** |
| `bc6cd0c8` | 67,724ms | 42,559ms | 1.6x rescue |

Both were recorded as double-failures at 25s. This restores the original corpus finding that the clamp rescues statements the default planner cannot complete — the population did not disappear, the cap hid it.

**Harm is still real, and so is rescue, on the same population.** Over the 16 saved views carrying the line-axis spine, at a 60s cap: the clamp causes 2 non-completions (`20ae2077` 24.2s default, `294029d4` 16.2s default) and rescues 2. Results verified identical by md5 across all four arms on all 16 views.

### Policy prices on the corrected data

Sixteen line-axis views, non-completion charged at its 60s cap:

| Policy | Total | Non-completions | Worst single view |
| --- | --- | --- | --- |
| Retire the clamp | 402.0s | 0 | **186.0s** |
| Today — clamp on signature | 359.1s | 2 | 60s+ |
| **Run-then-retry, 30s trigger** | **328.2s** | **0** | ~74s |
| Per-view oracle | 207.3s | 0 | 44.1s |

Run-then-retry was rejected on the *old* corpus at a 10s trigger, where 52 statements could not complete by default and so every one of them paid the trigger before its real run. That population has inverted, which is exactly why the rejection does not carry forward.

### The spine rewrite is refuted as the builder fix

`identities.player_year_week_line` spells its four keys across three relations — `player.pid`, `player_years_weeks.year`, `player_years_weeks.week`, `player_years_weeks_lines.selection_metric_line` — so every column join is a four-key equality spanning three relations, which is what the clamped plan demotes to a Join Filter. `player_years_weeks_lines` already carries all four columns, so re-pointing every key at it is a four-line change making each join a single-relation equijoin.

**Measured, it is inert: 276.1s against 268.3s default over the same 16 views**, within noise on fifteen of them and worse on `ae512205` (5.7s to 10.5s). Identical results. The planner already propagates those equalities through equivalence classes; it does not need them spelled on one relation. Harness: `spine_rewrite.py`, `spinesweep.py`, `spinesweep.jsonl` in the scratch directory.

So the Join Filter demotion is a consequence of the clamp forcing a merge join, not of how the builder spells the join — which removes the most attractive-looking candidate for the root-cause fix.

## Recommendation — retire the pre-emptive clamp, keep it as a timeout-triggered retry

**Every global planner setting loses. Only per-statement selection wins**, and run-then-retry is the implementable approximation of it. Measured over the 16 line-axis views, 60s cap:

| Arm | Total | Non-completions |
| --- | --- | --- |
| Default planner | 267.5s | 2 |
| `enable_nestloop = off` (shipped) | 359.6s | 2 |
| `enable_nestloop = off` + `enable_mergejoin = off` | 546.2s | 6 |

Forcing true hash joins — which is what the original 305,000ms-to-1,600ms finding actually did — is the worst of the three on today's corpus, and times out `93b67571`, a view that runs in 257ms by default. The shipped arm was never the validated arm: it disables nested loops and leaves merge joins on, which is how the worst regression became a Merge Join.

So the answer to the standing question is **yes, a `statement_timeout`-triggered retry replaces the clamp, and it is required rather than optional** — retiring the arm with nothing behind it prices worst of all (402.0s, with `6a03fc43` at 186.0s).

Concretely: drop `should_disable_nested_loops` and its `EXPLAIN` round trip, run every statement on the default planner with a 30s soft timeout, and on expiry re-run once under the existing arm. That reuses the retry shape `fa96387f7` already established for the dynamic-shared-memory error, deletes a per-execution round trip, and prices 328.2s with zero non-completions.

**Before implementing, widen the sweep** to all 65 signature-positive views at a 300s cap. The 30s trigger is calibrated on 16 views, and the trigger value is the one parameter that a wider population can move.

## Plan

- [ ] Operator picks among the options above on the CORRECTED evidence, and rules on whether a `statement_timeout`-triggered retry replaces the clamp as the safety net. Option 1 (retire) is no longer the recommendation: it is the worst-priced policy on the line-axis population and leaves a 186s statement in production.
- [ ] Implement the chosen option, with a spec that fails against the current behaviour.
- [ ] Re-run `vsweep.py` from the scratch directory against the change; record before and after totals in this task.
- [ ] Confirm the six clamp-caused timeouts (`20ae2077`, `ae512205`, `195d4ef2`, `294029d4`, `c4050d83`, `3dcbbfef`) complete, and that the 11 clamp-helped views have not regressed past their default-arm times.
- [ ] Hand to the operator for the gated deploy; nothing here auto-deploys.

## Resolved by measurement, 2026-09-23: retired

The clamp is **deleted** in league commit `bf665f4b9`, alongside its plan probe (`should_disable_nested_loops`), its signature module (`libs-server/data-views/nested-loop-clamp-signature.mjs`) and the DSM-exhaustion retry valve that existed only because the clamp forced parallel hash joins. Not deployed — deploys are human-gated.

The three-arm sweep of all 231 saved views behind [[█████████████████████-██████████-███-███████-██████████-███-████-████-███████]] answers the run-then-retry question without needing the 65-view calibration:

| Arm | Total over the 213 views comparable in both arms | Views past the 5s objective |
| --- | --- | --- |
| Default planner | 375,933ms | 10 |
| Shipped clamp arm | **399,605ms** | **16** |

**The clamp is a net loss on the live corpus**, and it pushes six additional views past the service objective. That settles the retire-or-gate question in favour of retire on its own merits, independent of the replacement.

The trigger calibration is moot for a second reason: the replacement reuses neither the signature nor the probe. The signature does not separate the population — it fires on 72 of 228 views, including views that finish in 8ms — so the new mechanism triggers on a measured 8-second budget instead. **The 65-view calibration sweep was not run, deliberately**, per the brief's instruction not to spend it on a mechanism about to be deleted.

Deployed 2026-09-23 in `DEPLOY_ACK_RANGE=46`. The clamp, its plan probe, its signature module and the DSM retry valve are all gone from production.