A developer on your team writes a query that joins four tables and takes 30 seconds. Their proposed fix: upgrade the database to a bigger instance. Whether this happens in a standup or as an interview question, it tests the same thing, whether you understand what makes a query slow or just throw money at whatever hurts. A 30-second query is almost always a code problem, and hardware barely dents code problems. Rather than argue the point, this post proves it on a real database you can spin up in a minute.

Watch it happen: a million rows, one index

Start a throwaway PostgreSQL with the statistics extension loaded, and open a shell into it:

docker run -d --name pgdemo -e POSTGRES_PASSWORD=demo postgres:16 \
  -c shared_preload_libraries=pg_stat_statements
docker exec -it pgdemo psql -U postgres

Build a customers table and a million orders spread across them:

CREATE EXTENSION pg_stat_statements;
CREATE TABLE customers (id int PRIMARY KEY, name text);
INSERT INTO customers SELECT g, 'customer ' || g FROM generate_series(1, 100000) g;
CREATE TABLE orders (id serial PRIMARY KEY, customer_id int NOT NULL,
                     amount numeric(10,2), created_at timestamptz);
INSERT INTO orders (customer_id, amount, created_at)
SELECT (random()*99999)::int + 1, (random()*500)::numeric(10,2),
       now() - (random()*365) * interval '1 day'
FROM generate_series(1, 1000000);
ANALYZE customers; ANALYZE orders;

Now ask the database to show its work on the most ordinary query there is, one customer's orders:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 4242;

 Gather  (cost=1000.00..12579.43 rows=11 width=22) (actual time=11.008..46.710 rows=10 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on orders  (cost=0.00..11578.33 rows=5 width=22) (actual time=13.857..38.107 rows=3 loops=3)
         Filter: (customer_id = 4242)
         Rows Removed by Filter: 333330
 Planning Time: 0.285 ms
 Execution Time: 46.786 ms

Read the two lines that matter. Parallel Seq Scan means the database read the whole table, and Rows Removed by Filter: 333330 across three workers means it examined roughly a million rows to keep ten. That is the phone book read page by page. One index later:

CREATE INDEX idx_orders_customer_id ON orders (customer_id);
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 4242;

 Bitmap Heap Scan on orders  (cost=4.51..47.28 rows=11 width=22) (actual time=0.042..0.095 rows=10 loops=1)
   Recheck Cond: (customer_id = 4242)
   Heap Blocks: exact=10
   ->  Bitmap Index Scan on idx_orders_customer_id  (cost=0.00..4.51 rows=11 width=0) (actual time=0.029..0.029 rows=10 loops=1)
         Index Cond: (customer_id = 4242)
 Planning Time: 0.391 ms
 Execution Time: 0.162 ms

From 46.8 ms to 0.162 ms, about 290 times faster, and Heap Blocks: exact=10 shows why: it touched ten blocks instead of the table. A million rows on a laptop costs only 47 ms to scan, which is exactly the trap. Grow the table to a few hundred million rows and join it to three others without indexes, and that same plan is the 30-second query. A bigger instance scans the same million rows slightly faster; the index stops scanning them at all.

The N+1 problem, counted

The second classic cause never shows up in a single query. Application code fetches 100 customers, then loops and fetches each one's orders, usually because an ORM lazily loaded a relationship. pg_stat_statements counts what actually hit the database. After running that loop for 100 customers, and then the equivalent single join:

 calls | total_ms |                                 query
-------+----------+------------------------------------------------------------------------
   100 |    11.55 | SELECT id, amount FROM orders WHERE customer_id = $1
     1 |     2.83 | SELECT c.id, o.id AS order_id, o.amount FROM customers c JOIN orders o

One hundred round trips versus one, and the loop was four times slower even measured only as server execution time, before adding a hundred network hops. Each individual query is fast, which is why nobody notices until they count.

What to look for in a plan

• Seq Scan on a large table with a Filter and a big Rows Removed by Filter: a missing index on the filtered column.
• A Nested Loop whose inner side is scanned thousands of times (loops= in the thousands): a join on an unindexed column.
• Estimated rows= wildly different from actual ... rows=: stale statistics; run ANALYZE before you change anything else.
• SELECT * dragging every column through the plan when the code uses three: width matters almost as much as the scan.

The fix order

Run EXPLAIN ANALYZE first, always; you cannot fix what you cannot see. Add indexes on the columns in your WHERE, join conditions, and ORDER BY, as composite indexes matching how you actually filter, and not on everything, since every index taxes every write. Fix the application layer: eager loading instead of lazy, batched lookups instead of loops. And if the workload genuinely is analytics across millions of rows, add a read replica, not to make the query faster but to keep it from starving the primary your users depend on.

The takeaway

Understand the problem, then fix it. The plan above turned a full scan into ten block reads with one line of SQL, and no instance type on any price list does that. Bigger hardware belongs at the end of the list, after the plans are sane and the load is real. Putting it under a bad query is a bigger engine on a car with square wheels.

FAQs