Your LINQ is fast; your query plan isn't

You can write a flawless LINQ query and still ship a slow one. The query has no N+1, it pages in the database, it pulls exactly the columns it needs, it reads beautifully. And it does a sequential scan over four million rows every time it runs, because the database has no fast way to find the rows you asked for. The LINQ is fast. The query plan isn't, and those are two different things that a code review almost never separates.

This is the layer below LINQ. By the time your Where becomes SQL, the work is handed to the query planner, and the planner decides how to get your rows: walk an index, or read the entire table top to bottom. That decision is where most "the query is fine but the endpoint is slow" mysteries actually live.

Seq Scan vs Index Scan, in one picture

Take a lookup that runs on every login:

var user = await _db.Users
    .FirstOrDefaultAsync(u => u.Email == email);

The SQL is exactly what you'd hope for:

SELECT u.id, u.email, u.password_hash
FROM users AS u
WHERE u.email = @__email_0
LIMIT 1;

Nothing to improve in the query. But ask the database how it intends to run it (in Postgres, EXPLAIN ANALYZE) and you get one of two answers. Without an index on email:

Seq Scan on users  (cost=0.00..78921.00 rows=1 width=68)
  Filter: (email = '...')
  Rows Removed by Filter: 3999999
  Execution Time: 412.503 ms

Read the damning line: Rows Removed by Filter: 3,999,999. The database looked at every row in the table, kept one, and threw away four million. 412 ms for a single email lookup, and it gets linearly worse as the table grows.

Add the index:

CREATE INDEX ix_users_email ON users (email);

Index Scan using ix_users_email on users  (cost=0.42..8.44 rows=1 width=68)
  Index Cond: (email = '...')
  Execution Time: 0.061 ms

Now it's an Index Scan: a B-tree descent straight to the row, no scanning, no discarding. 412 ms to 0.061 ms on identical LINQ. The query never changed. The plan did, and the plan is what your users feel.

Here is the same choice in its general shape, on a ten-million-row table rather than our four-million-row one:

Sequential scan

10,000,000

rows touched

912 ms reads the table

Index lookup

3

pages touched

0.06 ms reads the tree

The C# tells you what rows you want. The query plan tells you what the database had to read to find them. A "fast query" with a Seq Scan is a fast description of a slow operation.

The subtler killer: non-sargable predicates

The index above is the easy case: there wasn't one, you added one, done. The trap that catches experienced developers is when the index exists and the database ignores it anyway, because of how the predicate is written.

A predicate is sargable (Search ARGument able) when the database can use an index to satisfy it. Wrap the indexed column in a function and it stops being sargable, because the index stores the raw column values, not the transformed ones. Case-insensitive email matching is the classic offender:

var user = await _db.Users
    .FirstOrDefaultAsync(u => u.Email.ToLower() == email.ToLower());

That translates to a function applied to the column:

SELECT u.id, u.email, u.password_hash
FROM users AS u
WHERE lower(u.email) = lower(@__email_0)
LIMIT 1;

Your ix_users_email index is built on email, not on lower(email), so the planner can't use it. Back to a Seq Scan, except now it's worse: it computes lower() on every one of the four million rows before comparing. The index is sitting right there, fully built, and the query walks straight past it.

Seq Scan on users  (cost=0.00..91204.00 rows=1 width=68)
  Filter: (lower(email) = '...')
  Rows Removed by Filter: 3999999

There are two honest fixes.

Match the index to the query with an expression index, when you genuinely need case-insensitive matching:

CREATE INDEX ix_users_email_lower ON users (lower(email));

Now WHERE lower(u.email) = lower(...) is sargable again, because the index stores exactly the expression the predicate uses, and you're back to a sub-millisecond Index Scan.

Or make the predicate sargable by not transforming the column at all. If you normalize emails to lowercase on write (store them already-lowered), the lookup is a plain equality on the raw column and your original index just works:

var normalized = email.ToLowerInvariant();
var user = await _db.Users
    .FirstOrDefaultAsync(u => u.Email == normalized);

The same pattern catches you everywhere: WHERE date(created_at) = @d (wrap the value in a range instead), WHERE email LIKE '%foo' (a leading wildcard can't use a B-tree), WHERE id + 0 = @id. Any function on the indexed column moves the work off the index and onto every row.

Why "it works on my machine" is the whole problem

Here is the reason query-plan bugs survive all the way to production. On a seeded test database with 500 rows, a Seq Scan and an Index Scan are both instant. Reading 500 rows top to bottom takes microseconds. Your test passes, your local page loads snappily, the PR merges. The plan was wrong the entire time; the table was just too small for "wrong" to cost anything. The bug doesn't exist until the table has a few million rows, and by then it's a production incident, not a code review comment.

You cannot see this from the LINQ. You cannot see it from the test result. You can only see it by asking the database what it actually did, and most developers never run EXPLAIN against their own queries because they were never in the habit and nothing forced them to start.

That habit is exactly what reps are for. Some of Katabench's database puzzles don't just check your output and count your queries; they grade the query plan itself, with a constraint like "this lookup must use an index, no sequential scan allowed." A solution that returns the right rows by scanning the whole table fails the puzzle, the same way it would fail in production at scale. You can read how that grading works if you want the specifics. The point is that you practice writing sargable predicates and indexing for your access patterns before there are four million rows on the line.

Getting good at this is mostly about building one reflex: after you write the LINQ, ask what plan it produces, and don't trust "it's fast" until you've seen an Index Scan instead of a Seq Scan. The database track on the Pro plan is built to drill exactly that reflex, with a real Postgres telling you the truth every time you submit.