The Slow Query That Taught Me How Postgres Indexes Actually Work
A report page that used to load in under a second started taking nearly eight, and the only thing that had changed was the underlying orders table growing from a few thousand rows to a few million over several months of real usage. I'd read about indexes plenty of times without ever really needing one badly enough to sit down and understand how they actually worked, and this slow report is what finally forced me to.
Finding the actual slow query first, rather than guessing
Postgres logs slow queries if you configure it to, and turning on log_min_duration_statement at a low threshold surfaced the exact query behind the report page, a filter on order status combined with a date range sort, running against the full orders table on every single request.
Reading the query plan with EXPLAIN ANALYZE
Running EXPLAIN ANALYZE in front of the actual query showed a sequential scan across the entire orders table, meaning Postgres was reading every single row and checking it against the filter conditions rather than jumping directly to the relevant rows.
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE status = 'completed'
ORDER BY created_at DESC
LIMIT 50;
The output showed Seq Scan on orders with a cost and actual time that lined up directly with the eight-second load I was chasing, confirming this specific query was the actual bottleneck rather than something elsewhere in the report page's rendering.
Adding an index, and the immediate, dramatic difference it made
I added a composite index covering both the filtered column and the sorted column together, since the query used both in combination rather than either alone.
CREATE INDEX idx_orders_status_created
ON orders (status, created_at DESC);
Re-running the same EXPLAIN ANALYZE afterward showed the plan switch from a sequential scan to an index scan, and the actual execution time dropped from eight seconds to well under a hundred milliseconds, a genuinely dramatic difference from a single command.
Why I built a composite index instead of two separate ones
My first instinct was to add a separate index on each column individually, but reading further, a composite index covering both columns in the order they're actually used together in the query, filter column first, then sort column, lets Postgres satisfy both the filter and the sort from a single index lookup, rather than needing to combine two separate index results afterward.
A mistake I made indexing a column that didn't actually need it
Encouraged by the first result, I added an index on a low-cardinality boolean column elsewhere in the same table, assuming any index would help, only to find query performance on that column barely changed at all, since a boolean column with only two possible values doesn't narrow down a search meaningfully the way a high-cardinality column like a timestamp or status enum does.
The real cost of adding indexes I hadn't considered
Every index also slows down writes, since Postgres has to update the index structure on every insert or update to the table, and I removed that unnecessary boolean index once I confirmed it wasn't actually helping any real query, avoiding a write-performance cost for genuinely zero read benefit.
Checking for unused indexes across the rest of the database
This whole investigation pushed me to actually audit the rest of our schema, and querying Postgres's own statistics views revealed two more indexes from years earlier that had never been used by a single real query since monitoring began, both removed for the same reason, a write cost with no matching read benefit.
SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;
A partial index, once I noticed most queries only cared about recent rows
Looking closer at how the report page actually got used, nearly every real query filtered for orders from the past ninety days, meaning the full composite index was carrying a genuinely large amount of older, rarely queried data along with it. Adding a partial index scoped specifically to recent rows kept the index itself meaningfully smaller and faster to scan for the actual common case, while the full index still covered the rarer historical queries when someone genuinely needed them.
CREATE INDEX idx_orders_recent_status
ON orders (status, created_at DESC)
WHERE created_at > now() - interval '90 days';
Why I also checked autovacuum settings while I was in there
Reading further into how Postgres actually maintains index performance over time, I learned that heavy write activity without regular vacuuming lets dead row versions accumulate and quietly degrade both table and index performance, even with the right indexes in place. Checking our autovacuum settings against the orders table's actual write volume confirmed they were still at Postgres's conservative defaults, and tuning them slightly more aggressively for this specific high-write table has kept performance consistent in the months since, rather than slowly degrading again the way it originally had.
What I'd tell someone who's never needed to think about indexes
Reading about indexing in the abstract never made it click the way actually watching EXPLAIN ANALYZE switch from a sequential scan to an index scan on my own slow query did. The report page that took eight seconds now loads faster than I can consciously register, and I think about query plans now for any new feature involving a table that might genuinely grow, rather than waiting for a slow report to force the question again.
