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

Engineers at Datadog encountered an unexpectedly high-latency index scan in a PostgreSQL table, despite using an index scan that is typically efficient. The issue was traced to a mismatch between the column order of the composite index and the query's filtering predicates, which caused unnecessary disk I/O and high node costs. By creating a targeted index that matched the query's predicate order, engineers reduced the query latency from over 300 ms to 38 μs, demonstrating the importance of aligning index structures with query patterns. Datadog's Database Monitoring (DBM) tool now includes enhancements to automatically detect and suggest fixes for suboptimal index scans across databases, helping users identify and resolve inefficiencies without manual investigation.