Not all index scans are equal: How we cut query latency by over 99%

Just because your query uses an index scan doesn't mean you're in the clear. We recently tracked down a query that was taking 800ms despite hitting an index, and the fix dropped it to under 5ms. The culprit wasn't a missing index or a full table scan—it was predicate alignment, something most query analyzers won't flag as a problem.

The query was straightforward: filtering a large table by user_id and timestamp range. We had a composite index on (user_id, created_at), which should have been perfect. The explain plan showed an index scan, not a sequential scan, so conventional wisdom said we were done. But the query was still slow, and under load it was causing cascading timeouts.

The issue comes down to how composite indexes actually work. A B-tree index on (user_id, created_at) is sorted first by user_id, then by created_at within each user_id. When you query for a specific user_id and a timestamp range, the database can seek directly to that user's entries and scan sequentially through the relevant timestamps. That's efficient.

But our query was filtering on created_at first, then user_id. The database still used the index, but it had to scan through every user_id to find matching timestamps, then filter for the specific user. With millions of rows and thousands of users, this meant reading massive portions of the index even though we only wanted a handful of rows. The index scan was real, but it was doing far more work than necessary.

This is where column ordering in composite indexes matters viscerally. An index on (created_at, user_id) would have let the database seek to the timestamp range first, then filter by user. We created that index, and latency dropped from 800ms to 4ms. The query plan still showed an index scan both before and after—the difference was invisible to basic explain output.

Detecting this pattern requires looking beyond whether an index is used. You need to examine rows scanned versus rows returned. In Postgres, pg_stat_statements shows this as rows returned by the query versus shared_blks_read, which indicates how much data was actually touched. A large discrepancy is your signal. In MySQL, the Handler_read_next counter spikes when you're scanning large index segments inefficiently.

Database monitoring tools that track query performance over time make this easier to spot. We use Datadog's DBM, which surfaces the ratio of rows examined to rows returned automatically. Queries with ratios above 100:1 are worth investigating, especially if they're in hot paths. This metric isn't new—it's been available in performance schema and system catalogs forever—but having it surfaced proactively changes how quickly you catch these issues.

The broader lesson is that index effectiveness depends on access patterns, not just existence. An index on (A, B) is fundamentally different from one on (B, A), and your query predicates need to align with the index column order to get efficient seeks rather than broad scans. This isn't academic—we've found similar patterns in half a dozen queries since, each time seeing 95%+ latency reductions from reordering index columns to match actual query patterns.

Check your slow queries for high rows-examined ratios even when indexes are present. That's where the real performance wins hide.