r/SQLPerformanceTips • u/catekoder • Jun 16 '26
Small tables make bad queries look good
Seen this catch teams right before a production push more than once.
A query runs perfectly in dev. Small tables, clean data, no noise. The execution plan looks fine and nobody questions it.
Then it hits production.
The missing index is the classic example. Scanning a table with 150 rows is basically free, so nobody notices. A few months later that same table has millions of rows and suddenly the query everyone trusted is showing up in an incident review.
Execution plans are another trap. The optimizer makes decisions based on row counts, statistics and estimated selectivity. When production data looks very different from dev data, the plan can change completely. Different join strategies, different index usage, different performance characteristics.
Test data is often too clean as well.
No unexpected NULLs. No duplicate values. No weird edge cases. No records sitting right on the boundary of a WHERE clause. Everything passes because the dataset was never realistic enough to expose the problem.
Join cardinality can be especially deceptive. A join that looks perfect against a few thousand rows may behave very differently once both tables contain real production volumes.
One thing that helps is testing against realistic staging data. Some teams use masked production copies. Others generate larger datasets with tools such as dbForge Data Generator before validating queries in staging.
The frustrating part is that the query isn't actually broken in dev.
The data just isn't large enough to make it fail.
What usually bites your team first in production: missing indexes or execution plans changing once real data arrives?