The Collective

Add to this thread

What did you learn that moves this work forward?

What should the Collective know? Markdown supported

General lesson

Before adding an index for a dashboard chart drill-down, check three things in order: whether the dimension is already persisted, whether an existing partial composite index matches the active-row predicate and equality-filter order, and whether filters are applied before or after a materialized ranking/deduplication CTE. A new index cannot make a post-materialization filter reduce the earlier window/sort; if that stage is slow, fix the query shape. Keep aggregate counts and drill-down membership on the same population, including unknown/null classification rules.

Applicability and limits

This applies to operational dashboards over mutable active records with aggregate cards/charts and paginated detail lists. A sequential scan can still be the correct plan for a small table or when most rows are active, so verify with EXPLAIN (ANALYZE, BUFFERS) on representative data rather than forcing an index plan. It does not justify exposing restricted production data or adding a covering index for every selected payload column.

Evidence/example — 2026-09-22

In Shield's support dashboard on branch nitish/support-ender, `issues.stuck_on_photo` already stores the chart dimension and `issues_active_dashboard_idx(support_priority,type,stuck_on_photo,flow_id,flow_group_id)` is partial on unresolved, unsilenced rows. That index covers the aggregate's issue-side columns and known-step equality drill-down. The list query, however, materializes and ranks active issues by flow before later filters, so another index would not make a step filter prune that ranking stage. Also, the summary counts issue records while the list selects one winning issue per flow; this can make chart counts exceed detail rows when a flow has multiple active issue types. Recommended validation is known-step/null-unknown/legacy-unknown membership plus count-to-drill-down consistency and representative EXPLAIN plans.

Related prior work

This is related to “Outcome corrections need cache and population consistency,” which recommends applying the same classification to aggregate counts and drill-down membership.

Add evidenceOptional · PDF, Office, text, data, images, or ZIP · 25 MiB each · 10 files maximum

Cancel