---
title: Attach data-view display columns after the LIMIT, not before it
type: task
status: planned
description: >-
  The generated statement is a star join whose spine costs about 1% of the total; the builder
  attaches every display column to the full grain before the LIMIT, so view 6a03fc43 spends 186s
  computing columns for 28,041 rows it then discards to return 500. Cost is linear in carried rows,
  and every planner-level alternative is measured and refuted.
base_uri: user:task/league/data-views/attach-display-columns-after-the-limit.md
created_at: '2026-09-23T17:48:29.304Z'
entity_id: da5462c4-0f43-4c14-a08f-9b3301dc8bbb
observations:
  - ████████████████████████████████████████████████
owner_identity_uri: █████████████████████████
priority: High
public_read: true
relations:
  - relates [[user:task/league/data-views/retire-or-gate-the-nested-loop-clamp.md]]
  - relates [[█████████████████████-██████████████-████-████-███████████-███████████]]
  - relates [[████████████████████████-████-██████-████-██████████████]]
tags:
  - user:tag/league-xo-football.md
  - user:tag/software-task.md
updated_at: '2026-09-23T18:24:45.256Z'
visibility_analyzed_at: '2026-11-05T23:48:43.812Z'
---

## Overview

The generated data-view statement is a **star join**: a cheap spine that defines the row grain, plus one `LEFT JOIN` spoke per display column. The spine is about 1% of the cost. Everything else is spoke attachment — and the builder attaches every spoke to the **full grain** before applying `limit 500`.

Measured on host `league` 2026-09-23, across the 16 saved views carrying the line-axis spine, every spine resolves in **32-492ms** against full-statement times of 0.3s to 186.0s.

The worst statement in the corpus, saved view `6a03fc43`:

| | |
| --- | --- |
| Spine, including its filter join and the line CTE | **493ms**, 28,541 rows |
| Full statement, four display spokes attached to all 28,541 | **185,991ms** |
| Rows the user actually receives | **500** |

**Cost is linear in the number of spine rows carried through the spokes.** Restricting the same statement to a subset of players measured 15.4s at 25 players, 70.1s at 100, and 186.6s at all 292 — about 0.64s per player, straight-line.

So the statement spends 99.7% of its time computing columns for 28,041 rows that are discarded by the `LIMIT`.

## Why this is the only lever left

Every planner-level alternative has been measured and refuted on this corpus. They redistribute which operator pays for the fan-out; none of them reduce it.

| Lever | Result |
| --- | --- |
| Default planner | 267.5s over the 16 views, 2 non-completions |
| `enable_nestloop = off` (the shipped clamp) | 359.6s, 2 non-completions |
| `enable_nestloop = off` + `enable_mergejoin = off` | 546.2s, 6 non-completions |
| `join_collapse_limit` 8 to 16 | Plan cost identical to the unit on the three worst views |
| Single-relation spine equikey rewrite | 276.1s against 268.3s — inert |

Join reordering cannot help because **a star has no join order to discover**. The cost is the fan-out at each spoke, not the sequence of the spokes.

## The change

Resolve the page first, then attach:

1. Build the spine, plus only those spokes referenced by the outer `WHERE` or `ORDER BY`.
2. Apply the filter, the sort, `count(*) over ()` for `total_count`, and the `LIMIT`.
3. Attach the remaining display-only spokes to the surviving page rows.

No planner settings, no schema change, no semantic change. Identical rows, in the same order.

**Expected on `6a03fc43`:** four spokes attached to 500 rows rather than 28,541, against a 493ms spine — low single-digit seconds rather than 186s.

## Preconditions, stated as design work rather than as blockers

- **`total_count` must still count the pre-limit set.** It is available from the spine, which is why this stays cheap — but it must be computed in the page CTE, not after the limit.
- **Filter and sort columns must attach early.** All 15 views examined carry an outer `WHERE` referencing a spoke, so at least one spoke always attaches before the limit. Typically one of four to seven.
- **The outer `GROUP BY` currently lists spoke columns**, so the builder must establish that the grain is fixed by the spine before it can group on spine columns alone in the page CTE. This is the real design work and the thing to settle first.
- **Sorting on a spoke aggregate** forces that spoke early too; the win shrinks toward zero as more columns are filtered or sorted on, and is largest for the wide display-heavy views that dominate the slow population.

## Relationship to the clamp decision

Independent and sequenced behind it. The clamp lane is immediate relief on a bad operator choice; this removes the work that makes the operator choice matter at all. If the page is 500 rows, no plan over it is slow, and the clamp question becomes moot.

## Plan

- [ ] Settle the grain question: prove from the row-grain registry that the spine fixes the grain, so the page CTE can group on spine columns alone.
- [ ] Classify each spoke at build time as page-determining (referenced by `WHERE` or `ORDER BY`) or display-only.
- [ ] Emit the two-stage shape, with `total_count` computed in the page CTE.
- [ ] Verify result identity across all 228 saved views — same rows, same order, md5 of the sorted result set per view, against the current builder.
- [ ] Re-measure the 16 line-axis views and the six slowest views overall; record before and after here.
- [ ] Hand to the operator for the gated deploy.