Fix Slow SQL: N+1 Queries, Missing Indexes, and Bad Cardinality Estimates

Recognize an N+1 query pattern

An endpoint loads a page of parent rows, then performs one child lookup for each parent. The code may look clean, but query count grows with result size: one query for parents plus N child queries. Under production latency, repeated round trips often dominate the database's actual execution time.

Measure query count and cumulative duration for a representative request. ORM debug logs, tracing spans, and database statement metrics can reveal repeated normalized SQL. Avoid logging raw parameters that contain secrets or personal data. Fix the access pattern with a join, eager/batch loading, or a separate bulk query, while checking that the resulting join does not multiply parent rows unexpectedly.

Diagnose before adding an index

Capture the exact SQL and representative parameter values, then inspect the execution plan using the target engine's tools: EXPLAIN (ANALYZE, BUFFERS) in PostgreSQL, EXPLAIN or EXPLAIN ANALYZE where supported in MySQL, and actual plan statistics such as DBMS_XPLAN in Oracle. Some analysis commands execute the query; use care with write statements and production load.

Compare estimated and actual row counts, access paths, join order, sort operations, and buffer reads. If estimates are far off, check statistics, skew, and correlated predicates before forcing an index. If the query returns a large fraction of a table, a sequential scan may be correct.

Design an index around the query

Indexes help particular predicates and orderings; they are not generic speed switches. Composite index order matters. A common pattern filtering by tenant and then sorting by creation time may benefit from (tenant_id, created_at, id), while a query filtering only by another column may not use that path efficiently.

Every index has write, storage, and maintenance cost. Check whether an existing index already has the needed leading columns, whether a covering index would reduce table lookups, and whether the query is selective enough to benefit. Validate with actual plans and representative data after changing one thing at a time.

Fix and verify the user-facing path

Reduce repeated round trips, keep result cardinality correct, and preserve pagination order with a unique tie-breaker. Compare request latency and query count before and after. Add a test or budget assertion for the query pattern where practical, because an ORM refactor can reintroduce N+1 behavior without changing endpoint output.