The admin dashboard for a logistics SaaS had a "jobs completed this week" panel. Simple query: count rows where status = 'completed' and completed_at falls in the current week. 50K rows in the table at the time.
The query took 6 seconds. The dashboard had 8 similar panels. Total load time: 30+ seconds. The client said "the dashboard is unusable" and they were right.
The fix was one CREATE INDEX statement. Finding the right one took me three days.
Day 1: the obvious guess was wrong
First instinct: add an index on completed_at.
CREATE INDEX idx_jobs_completed_at ON jobs (completed_at);
Ran EXPLAIN ANALYZE. Query time: still 5.8 seconds. The planner wasn't using the index.
Why? Because the query also filtered on status:
SELECT COUNT(*)
FROM jobs
WHERE status = 'completed'
AND completed_at >= '2026-05-19'
AND completed_at < '2026-05-26';
Postgres estimated that the status = 'completed' filter would eliminate 80% of rows, so it chose a sequential scan on the whole table, then filtered by completed_at. The single-column index on completed_at was irrelevant — the planner decided it was faster to scan everything than to hit the index and then filter.
I tried an index on status:
CREATE INDEX idx_jobs_status ON jobs (status);
Query time: 2.1 seconds. Better, but still terrible for a COUNT query on 50K rows.
Day 2: the composite index trap
I read three Stack Overflow threads and a blog post. Everyone said "use a composite index." So I did:
CREATE INDEX idx_jobs_status_completed_at ON jobs (status, completed_at);
Query time: 180ms. Huge improvement. I shipped it.
Two hours later, the client messaged: "the dispatcher view is slow now."
The dispatcher view ran a different query:
SELECT *
FROM jobs
WHERE status IN ('assigned', 'accepted', 'in_progress')
AND zone_id = 'zone_42'
ORDER BY created_at DESC
LIMIT 20;
This query had gotten worse. Before my index change, it was 300ms. Now it was 1.2 seconds. My new composite index was confusing the planner — it was attempting an index scan on (status, completed_at) for a query that didn't filter on completed_at, then falling back to a sort on created_at.
I dropped the index. Dashboard went back to 6 seconds. Dispatcher view went back to 300ms.
Day 3: understanding what Postgres actually needs
I stopped guessing and actually read the EXPLAIN ANALYZE output line by line.
The dashboard query's plan looked like this (simplified):
Seq Scan on jobs (cost=0.00..1847.00 rows=312 width=0)
Filter: ((status = 'completed') AND (completed_at >= '2026-05-19') AND (completed_at < '2026-05-26'))
Rows Removed by Filter: 49688
312 rows expected, 49,688 removed by filter. Postgres was reading every row and throwing away 99.4% of them.
The dispatcher query's plan:
Index Scan using idx_jobs_zone_id on jobs (cost=0.29..847.00 rows=120 width=384)
Filter: (status = ANY ('{assigned,accepted,in_progress}'))
Sort Key: created_at DESC
Already using an index on zone_id. The status filter was a post-index filter. This was fine — 120 rows from the index, filter down to ~20, sort, limit. Fast enough.
The insight: the dashboard and dispatcher queries had different access patterns. The dashboard needed to jump straight to status + date range. The dispatcher needed zone_id first, then status as a filter. One composite index couldn't serve both well.
The fix: a partial index.
CREATE INDEX idx_jobs_completed_week
ON jobs (completed_at)
WHERE status = 'completed';
This index only contains rows where status = 'completed'. For the dashboard query, Postgres jumps straight into a small index (only ~15K of the 50K rows), scans the date range, done. Index size: 40% of a full-table index.
Dashboard query time: 12ms. Dispatcher query: unchanged at 300ms (the partial index is invisible to queries that don't match the WHERE clause).
The other dashboard panels got similar partial indexes:
CREATE INDEX idx_jobs_created_week
ON jobs (created_at)
WHERE status = 'created';
CREATE INDEX idx_jobs_assigned_zone
ON jobs (zone_id, created_at DESC)
WHERE status IN ('assigned', 'accepted');
Total dashboard load time went from 30+ seconds to under 400ms. All eight panels.
What I learned
EXPLAIN ANALYZE is not optional. I wasted Day 1 by guessing instead of reading. The plan tells you exactly what Postgres is doing and why. If you're adding an index without running EXPLAIN ANALYZE first, you're doing performance work with your eyes closed.
Composite indexes are not magic. Column order matters. A composite (status, completed_at) index is useless for queries that don't filter on status first. And it can actively confuse the planner for queries that filter on status but sort on a different column.
Partial indexes are underused. If your table has a status column and most queries filter on a specific status value, a partial index with WHERE status = 'value' is smaller, faster, and doesn't interfere with other queries. It's the right tool when different queries need different access patterns on the same table.
Test the query you're not optimizing. My Day 2 fix made the dashboard fast and broke the dispatcher view. Every index change should be tested against all critical queries, not just the one you're focused on. I keep a file called critical_queries.sql in every project now — the 5–10 queries that matter most, with expected performance baselines.
50K rows is nothing. This table had 50K rows. In a mature product, it would have 5M. The sequential scan that takes 6 seconds at 50K would take 10 minutes at 5M. Indexing problems don't age well — they compound. The right time to fix them is when you first notice the slowdown, not when the table is 100x bigger.
The meta-lesson
Three days for a one-line fix feels embarrassing. But the fix wasn't the line — it was understanding why that specific line worked and the other three didn't. If I'd deployed the composite index from Day 2, it would have been a regression disguised as an optimization.
The expensive part of performance work is never the CREATE INDEX. It's reading the plan, understanding the access patterns, and making sure your fix doesn't break the queries you're not looking at.
If your dashboard is slow and you're not sure why — reach out. Sometimes it's one index. Sometimes it's the query. Sometimes it's the schema. But it's almost never "we need to rewrite everything."