Skip to main content
In this lesson we walk through a common DevOps / backend interview scenario: A developer writes a query that joins four tables. It takes thirty seconds to return results. The proposed fix? Upgrade the database to a bigger instance. Is that the right move? Almost never. A slow query is typically a code, schema, or plan problem — not a RAM or CPU problem. Upgrading from 8 GB to 32 GB might reduce the time from thirty seconds to twenty‑five seconds, but you still have a fundamentally broken query and ongoing cost. Before making any changes, inspect the query plan and the application behavior: that’s where the real cause usually reveals itself.
The image illustrates a conceptual problem-solving approach, highlighting that issues are almost never hardware-related, and often stem from code problems. It suggests that upgrading from 8GB to 32GB might be a common, but potentially misguided, solution.

​
Common root causes

Most slow queries fall into a few predictable categories:
  • Missing or wrong indexes
  • Application-layer N+1 query patterns
  • Inefficient joins and SELECT * usage
  • Outdated statistics, bad planner estimates, or inappropriate join orders

​
Missing indexes

This is the number-one culprit. Without an index, a database may scan millions of rows instead of reading a handful — like flipping through every page of a phone book instead of using the alphabetical tabs. A single well-placed index can turn 30 seconds into tens of milliseconds.
The image displays text about a costly query issue, emphasizing that the query is still problematic, and highlights "missing indexes" as a usual suspect.

​
The N+1 problem

This is an application-layer issue. Example pattern:
  • Your code fetches 100 users.
  • Then it runs a separate query for each user’s orders.
  • Result: 101 round trips (1 + 100) when a single join or a batched query could return everything in one round trip.
N+1 often does not show up in a standalone SQL review because the extra queries are generated in application loops or from lazy-loaded relationships.

​
Bad joins and SELECT *

A multi-table join isn’t inherently slow. The problem arises when:
  • You join on non-indexed columns, causing full scans.
  • You fetch every column with SELECT * when you only need a few fields (wasted I/O and CPU).
  • The planner chooses an expensive join order or hash join because statistics are stale.
Best practice: select only the columns you need and join on indexed keys.

​
What to do (practical checklist)

Always run the query planner/executor to see what the database is actually doing before making changes.

​
Step 1 — Run EXPLAIN / EXPLAIN ANALYZE

This is non-negotiable. Use the RDBMS’s explain facility to see the chosen plan and actual runtime stats.
  • PostgreSQL: EXPLAIN ANALYZE <query>; (add BUFFERS for I/O stats)
  • MySQL: EXPLAIN FORMAT=JSON <query>; and consider ANALYZE TABLE to refresh stats
  • SQL Server: Use the Actual Execution Plan
Look for: full table scans, large row estimates, ignored indexes, expensive sorts, or repeated nested loops. Example (PostgreSQL):

​
Step 2 — Add the right indexes

  • Index columns used in WHERE, JOIN, GROUP BY, and ORDER BY.
  • Use composite indexes for multi-column filters. Create them in the column order the query uses (leading-column matters).
  • Avoid indexing every column — unnecessary indexes increase write cost and storage.
  • Consider covering indexes if the index can satisfy the entire query without touching the table.

​
Step 3 — Fix the application layer

  • Replace lazy-loaded per-row queries with eager loading or explicit joins.
  • Batch requests (e.g., WHERE id IN (...)) instead of issuing one query per item.
  • Implement pagination or streaming for large result sets rather than reading everything into memory.

​
When to consider additional hardware

Only after you’ve exhausted query, schema, and configuration fixes should you consider scaling or specialized systems.
  • Use read replicas to offload reporting/read-heavy workloads — they protect the primary but don’t fix a bad query.
  • For heavy analytics over very large datasets, consider OLAP systems (e.g., Redshift, BigQuery, ClickHouse).
  • Tune database configuration (work_mem, shared_buffers, effective_cache_size, etc.) and ensure statistics are current (ANALYZE / autovacuum working).
Do not use larger instances to hide slow queries. Upgrading hardware without addressing the root cause is expensive and temporary.

​
Quick reference table

​
Summary

The pattern is consistent: diagnose first, then fix the root cause. Use the planner and application traces to find whether the issue is an index, an application loop, a bad join, or stale statistics. Throwing hardware at a bad query is like putting a bigger engine on a car with a clogged fuel line — it wastes money and delays the inevitable fix.

Watch Video