When engineers investigate SQL queries, they normally think of index scans as a fast and efficient step in the query’s execution plan. When executed correctly, they fetch only the relevant rows from your table as opposed to sequential scans that read the entire table, reducing latency and query costs. However, just because an execution plan uses an index scan doesn’t mean that the scan is fast or performant.
In this blog, we’ll unravel a case of an expensive index scan that Datadog was using to query a PostgreSQL table in our production environment. We’ll walk you through why the execution plan was inefficient and slow despite being an index scan, and how we used a targeted index to cut average query latency from 300 ms to 38 μs.
We’ll also cover updates to Datadog Database Monitoring (DBM) that automatically detect suboptimal index scans across PostgreSQL and other databases. DBM flags these issues and recommends a fix so you can surface and resolve similar patterns without manual investigation.
Understanding the PostgreSQL table and its composite index
Our PostgreSQL database includes a recommendations table that stores customer recommendations, such as long-running queries, missing indexes, query regressions, and more.
