Detail Page Performance Analysis¶

Where time is spent and where to improve · Date: 2026-04-23

Component: TimeSeriesViewer (/series/{uid}) Audience: Developer implementing improvements

1. Overview¶

When a user navigates to /series/{uid}, the browser makes 8 HTTP requests and the server runs 11+ sequential SQL queries before the page becomes interactive. Measured total time on localhost is 1.5–4 s depending on series size. The four main contributors are:

Multiple HTTP round-trips in sequence (blocking chain)
SQL queries that cannot use indexes
Heavy Python post-processing on the hot path
React rendering a 6,600-line monolithic component synchronously

2. HTTP Request Waterfall¶

2a. Blocking parallel batch (`Promise.allSettled`)¶

These four requests fire simultaneously and the UI waits for all before rendering:

#	Endpoint	Main cost
1	`/series/{uid}/detail`	11 sequential SQL queries — dominant bottleneck
2	`/forecasts/{uid}/origins`	`DISTINCT` scan on `forecasts_by_origin` (large table)
3	`/series/{uid}/method-explanation`	reads `series_characteristics` — fast
4	`/series/{uid}/indirect`	reads `indirect_demand` — usually fast

Total wall time = max(1, 2, 3, 4). In practice endpoint 1 or 2 dominates.

2b. Sequential after the batch resolves¶

/hyperparams/{uid} is awaited — it blocks the setLoading(false) call. It is a trivial query but adds one extra round-trip to the critical path.

2c. Independent `useEffect` (concurrent)¶

/scenario-pipeline/{pid}/baseline/{uid} runs 3 sequential SQL queries in a separate useEffect triggered by activePipelineId. It races the main batch and may finish before or after.

2d. Lazy (fire-and-forget after `loading=false`)¶

/series/{uid}/forecast-convergence — heavy; starts immediately once loading=false is set, consuming server resources while the user is reading the page.
/series/{uid}/parameters — light; fine as-is.

3. SQL Layer — Where Time Is Spent¶

All 11 queries inside /detail execute sequentially on a single connection (no pipelining, no parallel sub-queries). Each is a separate round-trip even though they are in the same cursor block.

Query-by-query breakdown¶

#	Table	Issue	Estimated cost
1	`demand_actuals`	Full scan — no index on `(item_id, site_id)`. pg_mooncake columnar extension may not support B-tree on this column.	Medium–High
2	`item`	`CAST(id AS TEXT) = %s OR xuid = %s` — the `CAST` disables the primary key index.	Low (small table)
3	`location`	Same `CAST` pattern as `item`.	Low (small table)
4	`forecast_results`	`DISTINCT ON (method)` with `ORDER BY method, pipeline_id DESC` — needs composite index `(item_id, site_id, scenario_id, method, pipeline_id DESC)`.	Medium
5	`forecast_results` (causal)	Second query on same table, same session.	Low
6	`series_characteristics`	`ORDER BY pipeline_id DESC LIMIT 1` — needs index `(item_id, site_id, scenario_id, pipeline_id DESC)`.	Low
7	`series_backtest_metrics`	CTE computes `MAX(pipeline_id)` then the outer query re-scans the table. Two-pass over potentially thousands of rows.	High
8	`series_best_methods`	Simple lookup — fine.	Low
9	`demand_corrected`	Filter by `item_id` + `site_id` — needs composite index.	Low
10	`fitted_distributions`	`DISTINCT ON (method)` — needs composite index similar to `forecast_results`.	Low
11	`demand_corrected` COUNT	Duplicate scan of same table as query 9.	Low

Critical missing indexes¶

-- forecast_results: covers both DISTINCT ON queries
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_fr_uid_scen_method_pid
ON scenario.forecast_results (item_id, site_id, scenario_id, method, pipeline_id DESC NULLS LAST);

-- series_backtest_metrics: covers the CTE + outer query in one pass
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_sbm_uid_scen_pid
ON scenario.series_backtest_metrics (item_id, site_id, scenario_id, pipeline_id DESC);

-- forecasts_by_origin: covers both /origins and /forecast-convergence
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_fbo_uid_scen_origin
ON scenario.forecasts_by_origin (item_id, site_id, scenario_id, forecast_origin);

-- fitted_distributions: covers DISTINCT ON in /detail and /distributions
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_fd_uid_scen_method_pid
ON scenario.fitted_distributions (item_id, site_id, scenario_id, method, pipeline_id DESC NULLS LAST);

Fix the CAST anti-pattern¶

Replace:

WHERE CAST(i.id AS TEXT) = %s OR i.xuid = %s

With:

WHERE (i.id = %s::int OR i.xuid = %s)

(use a TRY_CAST or CASE guard if the prefix is not guaranteed numeric)

Eliminate the duplicate demand_corrected scan¶

Queries 9 and 11 both scan demand_corrected for the same item_id/site_id. Combine them into one query that returns both the rows and the COUNT(*).

Collapse the two-pass backtest CTE¶

Replace:

WITH latest_run AS (SELECT MAX(pipeline_id)...)
SELECT ... FROM series_backtest_metrics, latest_run WHERE pipeline_id = latest_run.max_pid

With a single query using a window function or DISTINCT ON, eliminating the double scan.

4. Python Layer — Hot-Path Post-Processing¶

After all SQL queries return, Python does non-trivial work before returning the response:

Work	Cost	Location
`_build_distributions_from_rows`	Medium — 24 horizons × 80 points of `scipy_stats` PDF evaluation in a Python loop. ~1,920 floating-point density calls.	`main.py:_build_distributions_from_rows`
JSONB quantile parsing	Low–Medium — each `forecast_results` row has a large JSONB blob; parsed with `json.loads` + Python dict iteration for every method × 24 horizon values.	`main.py` forecasts loop
`_compute_composite_ranking`	Low — pure Python scoring over metric dicts.	`main.py`
Forecast convergence groupby	High — reads ALL `forecasts_by_origin` rows for the series into pandas (tens of thousands of rows), then does multi-level groupby in Python.	`main.py:get_forecast_convergence`

Improvement: pre-compute density curves¶

Move density curve computation out of the hot path. Options:

Store density points in fitted_distributions.density_json (computed once at distribution-fitting time, read directly)
Or compute lazily: only build distributions when the user expands the ridge-chart panel

Improvement: stream or paginate forecast-convergence¶

The convergence endpoint fetches all rows for a series into pandas. For series with many backtest origins (e.g., 52 weekly origins × 10 methods × 24 steps = 12,480 rows), this is slow. Options:

Filter to best method only by default
Compute server-side with SQL GROUP BY instead of pandas

5. React Layer — Rendering Bottlenecks¶

TimeSeriesViewer.jsx is a 6,676-line single-file component. All panels render in the same React tree.

Issue 1: 52 `useMemo`/`useCallback` calls, all in one component¶

Every state change (forecast visibility toggle, dark mode, panel fold) re-evaluates all 52 memos in sequence. The main chart spec (mainChartSpec) recomputes whenever any forecast-related state changes, producing a fresh Plotly trace array for all methods even when only one method's visibility changed.

Fix: Extract the main chart, backtest panel, ridge chart, and netting overlay into separate child components. Each has its own React.memo boundary so a toggle in the main chart does not re-render the ridge chart.

Issue 2: Plotly renders synchronously on data arrival¶

Three <Plot> instances (main chart, backtest playback, racing bars) are mounted simultaneously when data resolves. Plotly's initial render is expensive (~50–150 ms each). All three fire in the same animation frame.

Fix: Defer non-visible panels. Use IntersectionObserver or a lazy <Suspense> wrapper so Plotly only renders when the panel is scrolled into view. The ridge chart (3D surface) is especially expensive and rarely viewed immediately.

Issue 3: All-series list fetched on new-part fallback¶

When hist is empty (new part / NPI), a GET /series?limit=9999 fires to populate the series dropdown. This is a large response and blocks other activity.

Fix: Use the paginated /series?search=... endpoint instead, triggered only when the user types in the dropdown.

Issue 4: `activePipelineId` `useEffect` triggers a separate `/baseline` call¶

The pipeline baseline request runs in its own useEffect with a separate connection. If activePipelineId changes after initial render (e.g., from URL params resolving), the baseline re-fetches and triggers a second render wave.

Fix: Include pipeline baseline in the main loadData call (one Promise.allSettled group) rather than a separate effect.

6. Connection Management¶

Each endpoint opens a new get_conn() and closes it in finally. During the four-request parallel batch, four connections are opened simultaneously. On a local single-user deployment this is fine; under load or with a connection pool limit it adds contention.

The /detail endpoint runs 11 queries on one connection — correct. But the connection is not reused across the parallel batch. Consider using a proper async connection pool (e.g., asyncpg with databases or SQLAlchemy async) so that idle time between sequential queries within /detail does not hold open a connection unnecessarily.

7. Priority Matrix¶

Color coding: green = High impact or Low effort; amber = Medium; red = High effort. Rows are ordered from highest to lowest combined value.

Improvement	Effort	Impact	Layer
Add composite indexes (4 indexes)	Low	High	SQL
Fix CAST anti-pattern on `item`/`location`	Low	Low	SQL
Combine duplicate `demand_corrected` scans	Low	Low	SQL
Collapse backtest CTE to single pass	Medium	Medium	SQL
Move density computation out of hot path	Medium	Medium	Python
Paginate / SQL-side forecast-convergence	Medium	Medium	Python
Extract TSV panels into separate components	High	High	React
Defer off-screen Plotly panels	Medium	High	React
Move `/baseline` into main `loadData` batch	Low	Medium	React
Replace 9999 series fetch with search API	Low	Medium	React

8. Quick Wins (implement in one session)¶

These can be done without architectural changes:

Add the 4 indexes — run the CREATE INDEX CONCURRENTLY statements from Section 3. No code change required.
Move /hyperparams out of the await chain — change the sequential await api.get('/hyperparams/...') to a fire-and-forget that updates state when it resolves, so setLoading(false) fires sooner.
Move /baseline into Promise.allSettled — remove the separate useEffect for baseline, add it as a 5th entry in the existing Promise.allSettled call in loadData.
Combine demand_corrected queries 9 and 11 — one query, save one round-trip.