Database query path

Core details

Repeatable workflow

1. Find query text — slow query log, pg_stat_statements (Postgres) or vendor equivalent.
2. EXPLAIN (ANALYZE) — estimated vs actual rows; huge gap ⇒ stale statistics or bad parameter sniffing class issues.
3. Access path — seq scan vs index vs bitmap; why did the optimizer choose it?
4. Locks / waits — pg_locks, wait events—low CPU + high latency often waiting, not “slow query.”
5. Replica vs primary — read-your-writes and lag; intermittent “bugs” may be stale replica.

Write-path awareness

Indexes speed reads and cost writes. Hot updates on wide indexes → bloat and contention.

Understanding

“Add an index” without plan proof is index theater. Often the fix is narrower SELECT, batching, keyset pagination, or fresh stats—not more B-trees.

Use the diagnostic map below to keep the investigation honest: start from one measured query, split plan errors from wait states and replica lag, change one thing, then verify latency and write-path cost.

Senior understanding

Deep relational + caching narrative: /databases and Indexing & query tuning.

Diagram

See also

• /databases/topics/indexing-query-tuning
• Profiling services & async