Your Dashboard Query Is Not Missing a Postgres Index: It Is Fetching 58,412 Rows to Render Twelve

Why does a dashboard query that already uses an index still take two seconds?

Most slow dashboard queries are not missing an index. The plan already uses one, the statement finishes inside the database in 118 ms, and the endpoint wrapped around it takes two seconds because it moves 58,412 rows across the socket to render thirteen of them.

The case I keep meeting is a B2B order dashboard on two tables. orders holds id, tenant_id, created_at and refunded_at. order_items holds one row per line, with order_id, sku_code, quantity and unit_cents. The deployment behind this article carries 4.2 million order_items rows across 380 tenants. A tenant near the median has 11,014 orders in the last 30 days and 58,412 lines behind them, which is 5.30 lines per order. The screen shows four KPI numbers and a table of the top twelve SKUs by revenue. Thirteen rows of output.

Here is the statement the endpoint ran, and the plan Postgres returned for it.

EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, oi.sku_code, oi.quantity * oi.unit_cents AS revenue_cents, o.refunded_at
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.tenant_id = '9f1c8a52-3d47-4c6b-9a08-2f5e7c1b4d33'::uuid
  AND o.created_at >= now() - interval '30 days';

-- Nested Loop  (cost=1.29..8422.55 rows=58120 width=112)
--              (actual time=0.071..118.204 rows=58412 loops=1)
--   ->  Index Scan using orders_tenant_created_idx on orders o
--         (cost=0.43..1619.55 rows=10998 width=24)
--         (actual time=0.041..22.902 rows=11014 loops=1)
--         Index Cond: ((tenant_id = '9f1c8a52-…'::uuid)
--                  AND (created_at >= (now() - '30 days'::interval)))
--   ->  Index Scan using order_items_order_id_idx on order_items oi
--         (cost=0.43..0.55 rows=5 width=38)
--         (actual time=0.002..0.003 rows=5.30 loops=11014)
--         Index Cond: (order_id = o.id)
-- Buffers: shared hit=48213 read=6114
-- Planning Time: 0.386 ms
-- Execution Time: 118.204 ms

Read the plan in order. Both scans are index scans. orders_tenant_created_idx is doing exactly what an index is for: it finds 11,014 rows inside 4.2 million, and its Index Cond covers both the equality and the range. order_items_order_id_idx runs 11,014 times and returns an average of 5.30 rows per loop. Buffers are 48,213 hits against 6,114 reads, so the working set is mostly cached. Execution is 118.204 ms.

Nothing in that plan is wrong, which is the whole problem. It also states the cost that matters before anyone measures a wall clock: 58,412 rows at the top node against width=112. That is 58,412 × 112 bytes, about 6.5 MB, paid to produce a thirteen-row screen. The endpoint's p95 was 2.1 s over a week of production traffic, and the statement accounted for 118 ms of it. The rest was the trip out of the database: 58,412 rows parsed into JavaScript objects, a Map and two Sets built over them, a sort, and a slice of twelve. I did not decompose the remaining two seconds further — I would not trust anyone who claimed to without measuring on the same machine — but removing the transfer removed the latency, which was the attribution I needed.

An over-fetching dashboard is a row-count problem wearing a missing-index problem's clothes: the plan shows an index scan, the statement is fast, and the wall-clock time goes into rows the interface never renders.

It is also a byte budget, and byte budgets hold in the same way on both sides of the network: they hold when something fails a build, which is the argument behind a Core Web Vitals budget that CI refuses to merge past. Six and a half megabytes per dashboard load is a figure a team can write into a file and check, exactly as it writes down route weight.

How do you tell an over-fetch from a missing index?

You compare two numbers: the rows the plan's top node actually returns, and the rows the component renders. Here it is 58,412 against 13, a ratio near 4,500 to 1. My working rule: once the ratio is above roughly 50 to 1, the index list is the wrong place to read. A missing index shows up as a Seq Scan and is obvious; an over-fetch shows up as a healthy-looking index scan that returns everything.

Three tools produce the evidence. EXPLAIN (ANALYZE, BUFFERS) on the exact statement, run against a replica, or inside a transaction you roll back if the statement writes. pg_stat_statements, sorted by rows rather than total_exec_time, because an endpoint returning 58,412 rows tops the row list immediately. And auto_explain with log_min_duration_statement set, so the plan for a slow request lands in the log instead of being reproduced by hand.

The third row is this article. The second row is the same disease caught earlier. The first row is the only one that an index fixes, and it is the one everybody assumes they have.

What does reducing those rows in application code actually cost?

This is the handler behind the plan above. I wrote versions of it for years, and it reads clearly.

import type { Pool } from 'pg';

type ItemRow = {
  orderId: string;
  skuCode: string;
  cents: string; // bigint; node-postgres hands it back as a string
  refunded: Date | null;
};

// The screen renders four KPI numbers and twelve rows. Nothing else is used.
export async function loadDashboard(pool: Pool, tenantId: string): Promise<DashboardPayload> {
  const { rows } = await pool.query<ItemRow>(
    `SELECT o.id AS "orderId", oi.sku_code AS "skuCode",
            oi.quantity * oi.unit_cents AS "cents", o.refunded_at AS "refunded"
       FROM orders o JOIN order_items oi ON oi.order_id = o.id
      WHERE o.tenant_id = $1 AND o.created_at >= now() - interval '30 days'`,
    [tenantId],
  );

  const orders = new Set<string>();
  const refundedOrders = new Set<string>();
  const bySku = new Map<string, bigint>();
  let revenueCents = 0n;

  for (const row of rows) {
    const cents = BigInt(row.cents);
    orders.add(row.orderId);
    if (row.refunded !== null) refundedOrders.add(row.orderId);
    bySku.set(row.skuCode, (bySku.get(row.skuCode) ?? 0n) + cents);
    revenueCents += cents;
  }

  const topSkus = [...bySku].sort(([, a], [, b]) => (b > a ? 1 : b < a ? -1 : 0)).slice(0, 12);
  return { orderCount: orders.size, revenueCents, refundedCount: refundedOrders.size, topSkus };
}

Two details there are symptoms rather than choices. The Set of order ids exists only because the join fanned 11,014 orders into 58,412 rows, so the order count has to be recovered by deduplication — the database already knew that number. And BigInt is needed because bigint columns arrive as strings, which is correct behaviour and one more per-row conversion in the hot path.

The memory is the part that is easy to miss. Assume 200 bytes of heap per parsed row, a fair order of magnitude for a four-field object in V8; 58,412 of them held live for the length of the request is roughly 12 MB per concurrent request. Treat that as a shape rather than a measurement: the endpoint's cost scales with the tenant's order volume, so the largest customer gets the slowest dashboard and the problem grows on its own.

How do you move the reduction into the query without making it unreadable?

Two statements, both parameterised, both returning the rows the screen can actually use.

-- One row for the KPI numbers.
SELECT count(DISTINCT o.id)                              AS order_count,
       coalesce(sum(oi.quantity * oi.unit_cents), 0)     AS revenue_cents,
       count(DISTINCT o.id) FILTER (WHERE o.refunded_at IS NOT NULL) AS refunded_count
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.tenant_id = $1
  AND o.created_at >= now() - interval '30 days';

-- Twelve rows for the table. The 58,412 rows are reduced here, not in Node.
SELECT oi.sku_code,
       sum(oi.quantity)                 AS units,
       sum(oi.quantity * oi.unit_cents) AS revenue_cents
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.tenant_id = $1
  AND o.created_at >= now() - interval '30 days'
GROUP BY oi.sku_code
ORDER BY revenue_cents DESC
LIMIT 12;

FILTER rather than sum(CASE WHEN refunded_at IS NOT NULL THEN 1 ELSE 0 END) is the same plan with less to read, and it keeps the condition beside the aggregate it modifies. count(DISTINCT o.id) does force Postgres to sort or hash 58,412 values before it can count them, which is real work — and still cheaper than serialising the same values, framing them, and taking them apart in a garbage-collected runtime.

I keep these as two statements instead of one query with a lateral join or a UNION ALL, because the KPI query returns a single row and the breakdown needs a top-12 sort. Merging them makes the plan harder to read and the failure modes harder to separate, for one saved round trip. Keep $1 and the now() window as parameters instead of interpolating values, so the plan stays generic and reusable across calls.

The database does slightly more work in the second row, not less: the same pages are read, and a hash aggregate and a top-12 sort are added on top. That is the trade, and it favours the rewrite because the expensive resource was never the scan. Both endpoint figures are my measurements on one deployment over a week of production traffic; the second includes ordinary cold-start variance.

Which indexes are actually worth their write cost?

Once the query returns thirteen rows, the schema question gets small and answers itself.

-- Already present and already used by the plan above. This is not the problem.
CREATE INDEX CONCURRENTLY orders_tenant_created_idx
  ON orders (tenant_id, created_at DESC);

-- INCLUDE cannot be added to an existing index in place, so this is a second
-- index; drop the first in a separate statement once this one is valid.
CREATE INDEX CONCURRENTLY orders_tenant_created_kpi_idx
  ON orders (tenant_id, created_at DESC) INCLUDE (refunded_at);

-- order_id leads because it is the equality predicate in the fan-out join.
CREATE INDEX CONCURRENTLY order_items_order_id_cover_idx
  ON order_items (order_id) INCLUDE (sku_code, quantity, unit_cents);

Three things decide whether those earn their keep. CREATE INDEX CONCURRENTLY cannot run inside a transaction block, so it belongs in its own migration step, and a concurrent build that fails leaves an index marked invalid that still costs writes — check pg_index.indisvalid before trusting one. INCLUDE columns are not search keys; they exist so the aggregate can be answered from the index alone, which happens only when the visibility map says the pages are all-visible, so an index-only scan turns back into heap fetches after a heavy write burst until autovacuum catches up. Re-run the EXPLAIN afterwards and check for Heap Fetches: 0.

The write side is not free either. order_items takes roughly 40,000 inserts a day across all tenants, and every index on it is one more B-tree insertion, one more page that can split, and one more thing vacuum maintains. Two indexes with INCLUDE cost more per insert than one without, which is why I will rewrite a query before adding to an insert path. An index is a subscription paid in writes; buy it when the plan says Seq Scan.

When is the index the answer, and when is this advice wrong?

• The plan says Seq Scan. Then it is a missing index, and everything above this line is a distraction. Add the index first and re-measure.
• The screen genuinely needs rows. A drill-down, an audit view or a CSV export cannot aggregate: the number is 50,000, not twelve. Those need keyset pagination on an indexed ordering column, not LIMIT 12, and an aggregate would be the wrong answer.
• The result set is small. Below a few thousand matching rows, round-trip overhead dominates and one query reads better than two; I only split out the KPI query in the tens of thousands.
• The numbers have to reconcile. An aggregate cannot be audited line by line. If finance ties the dashboard back to the ledger, the aggregate hides a discrepancy instead of showing it, so ship the aggregate for the chart and keep one row-level export path beside it.
• The same window is requested thousands of times a day. If one tenant's 30 days is recomputed 50,000 times a day, the answer is a pre-aggregated rollup table refreshed on a schedule, not a better query. Postgres does the work either way; a rollup table stops it repeating the work.
• The report spans tenants. With no leading tenant_id, you are scanning, and a B-tree you will never use is worse than a partitioned table or a BRIN index on created_at.

The rewrite is also not always available. If a metric depends on a Python model, a currency table fetched over HTTP, or business rules that exist only in TypeScript, then the over-fetch is a symptom of a metric with no single home, and moving the aggregation into Postgres would duplicate that logic rather than centralise it. That is a product decision about where the truth lives, and no index fixes it.

What is the smallest change worth shipping this week?

Run EXPLAIN (ANALYZE, BUFFERS) against the slowest dashboard statement on a replica, read rows at the top node, and divide it by the number of rows the component renders; if the ratio is above fifty, rewrite the query before touching the schema. Write the byte figure next to the endpoint's latency budget so the next person sees the number and not just the query, then re-run the plan to confirm the row count fell to the size of the screen. Only then decide whether any index still needs to exist. The rewrite usually ships in an afternoon, while an index you did not need charges you on every insert from now on.