It was fast when only you used it

Limiting what a request may do is one part of controlling its work. Another is noticing how that work grows. Reading twelve rows may be quick even with a wasteful query; reading the same kind of data after months of use can expose a very different cost.

Speed is not a property of code alone. It belongs to code together with how much data sits under it and how many people ask at once, and two of those three are missing from your laptop. Reading the code can reveal repeated work, but timing the relevant workload tells you how much that work costs.

Fifty-one questions

Take the loop that fills a blog's front page. It asks for fifty articles, and then, for each article, asks who wrote it:

const { rows: articles } = await pool.query(
  'select * from articles order by created_at desc limit 50'
);
for (const article of articles) {
  const { rows } = await pool.query(
    'select name from authors where id = $1', [article.author_id]
  );
  article.author = rows[0];
}

Fifty-one queries go out instead of two. This is the N+1 pattern: one query for the list and N queries for the details. ORMs produce it quietly when a template touches a relation that was loaded lazily.

Each of those queries is cheap on its own. The cost is in the total. Every query pays a round trip to the database plus a little parsing and planning, then waits its turn. On your laptop the database is on the same machine, so the round trip is almost nothing. In production it sits across a network, and under load fifty-one round trips add up to time the visitor spends waiting. Shopping once with a list and going back for every item feel the same when the shop is your own kitchen shelf.

The fix asks for all the authors in one go, either with a join or by passing every id at once:

select id, name from authors where id = any($1);

ORMs offer eager loading for the same purpose.

What an index does and does not promise

An index is a separate, sorted structure that lets the database find rows matching a condition without reading the whole table. It is often described as jumping straight to a row, and that is too strong. Postgres has a query planner, which estimates the cost of several ways to run your query and picks the cheapest one it can see. If your condition matches a large share of the table, reading the whole table in order can beat hopping through an index, and the planner will choose that. With twelve rows, scanning everything is the fastest option there is. So a missing index is not only hidden, it is briefly correct.

The planner relies on statistics about your data, and those only mean something once you have a realistic amount of data.

Make the data, then ask

You do not learn this by pointing a load tool at your production site. You learn it from a copy you built on purpose. In a local database:

create table articles (
  id bigserial primary key,
  author_id int not null,
  title text not null,
  created_at timestamptz not null
);

insert into articles (author_id, title, created_at)
select (random() * 999)::int + 1,
       'article ' || g,
       now() - g * interval '1 minute'
from generate_series(1, 200000) as g;

analyze articles;

explain analyze
select * from articles where author_id = 42
order by created_at desc limit 20;

Read the plan. Then add create index on articles (author_id, created_at desc);, run analyze again and compare the two plans. Next, try a condition that matches half the table and see whether the planner uses the index at all. Shape the synthetic data like the real thing. If one author writes most of the articles in your product, make one author write most of them here too.

While your page runs against that copy, turn on query logging in your ORM or database and count the lines for a single page load. A list request followed by one author query for every row reveals the N+1 pattern. Compare the total time and query count after changing the loading strategy.

Averages hide the users you want

If one request in a hundred takes eight seconds, the average still looks fine. Track the slow end instead, the 95th or 99th percentile. The person waiting eight seconds is often the one carrying the most data, which usually means the one who liked the product enough to fill it.

Repeat the same measurement after changing one variable: row count, skew in the data, or concurrent requests. Keeping the other conditions fixed helps you see which part of the workload changed the result.