Your Database Query Is Fast in Testing… Why Does It Become Slow in Production?

The Query Took 4 ms Locally and 4 Seconds in Production#

The SQL is identical.

In development:

orders rows:       25,000
concurrent users:  1
execution time:    4 ms

In production:

orders rows:       180,000,000
concurrent users:  800
execution time:    4.2 seconds

The query did not become “randomly slow.” Its environment changed: data volume, distribution, concurrency, cache state, parameters, and competing work.

The right question is not “Why is PostgreSQL slow?” It is:

What is different about the production execution?

Start With the Actual Query and Plan#

Suppose this endpoint lists recent orders for one customer:

SELECT id, status, total, created_at
FROM orders
WHERE customer_id = $1
ORDER BY created_at DESC
LIMIT 50;

Do not begin by adding random indexes. Capture the exact SQL and bind parameters, then inspect its plan safely.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, status, total, created_at
FROM orders
WHERE customer_id = 98231
ORDER BY created_at DESC
LIMIT 50;

You may find:

Seq Scan on orders
Rows Removed by Filter: 179,992,410
Sort Method: external merge
Execution Time: 4182 ms

The test database may have scanned 25,000 cached rows quickly. Production scans and sorts millions.

Difference 1: Dataset Size Changes the Algorithm#

A sequential scan over a tiny table can be correct and fast.

Test table:        2 MB
Production table:  140 GB

The same access pattern now reads far more pages and may spill sorting or hashing to disk.

A matching index could be:

CREATE INDEX CONCURRENTLY idx_orders_customer_created
ON orders (customer_id, created_at DESC);

The column order matters because the query filters by customer_id and then needs rows ordered by created_at.

Indexes have write and storage costs. Validate the important access pattern rather than copying an index from a different query.

Difference 2: Production Data Is Skewed#

Uniform test fixtures hide skew.

Most customers:       5–100 orders
Largest customer:     8,000,000 orders

A plan that is excellent for a typical customer can be poor for the largest tenant.

Parameter-sensitive behavior may look like:

customer_id = small tenant   -> 3 ms
customer_id = huge tenant    -> 2,900 ms

Test representative parameter classes, not one convenient ID.

Difference 3: Statistics and Row Estimates Are Wrong#

The optimizer chooses a plan using statistics.

Compare estimated and actual rows:

estimated rows:  120
actual rows:     3,800,000

Large estimation errors can lead to a poor join order or nested loop over millions of rows.

Check when statistics were refreshed and whether column correlation or skew needs better statistics.

ANALYZE orders;

Do not run maintenance blindly during an incident. First verify that stale or insufficient statistics are actually involved.

Difference 4: The Production Cache Is Not Your Laptop Cache#

Repeated local tests often read the same pages from memory:

first run:   120 ms
second run:    4 ms
third run:     3 ms

The later timings measure a warm cache.

Production may have a much larger working set and many queries competing for shared buffers and operating-system cache. A plan with mostly cached reads can behave very differently when it performs physical I/O.

EXPLAIN (ANALYZE, BUFFERS) helps distinguish buffer hits from reads, but interpret it alongside storage latency and overall workload.

Difference 5: Concurrency Changes Everything#

One query in isolation may be fast. Five hundred copies may not be.

Single execution:       20 ms
Concurrent executions: 500
Database CPU:           95%
Pool waiting:           1,200 requests

Queries compete for:

• CPU
• Buffer cache
• Disk I/O
• Connection slots
• Locks
• Memory for sorts and hashes

Measure pool acquisition time separately from database execution time. Otherwise a 15 ms query can appear as a 2-second database call because it waited 1.985 seconds for a connection.

Difference 6: Locks Make a Fast Query Wait#

An update by itself may take milliseconds but wait seconds for another transaction.

Transaction A updates order 42
       |
       | remains open for 8 seconds
       v
Transaction B updates order 42 ---- waits

Inspect active sessions:

SELECT
  pid,
  state,
  wait_event_type,
  wait_event,
  query_start,
  query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY query_start;

wait_event_type = 'Lock' points to blocking rather than slow computation.

Also look for idle in transaction sessions. They can retain locks and old snapshots long after application work should have finished.

Difference 7: The Query Is Not Actually Identical#

Production ORM behavior may add:

• Extra joins
• Different selected columns
• Pagination count queries
• N+1 relationship loading
• LOWER() or casts around indexed columns
• A wildcard prefix such as LIKE '%term%'

Log or trace the actual SQL and parameters. Comparing a handwritten local query with different generated production SQL produces the wrong diagnosis.

Difference 8: Plan Changes and Prepared Statements#

PostgreSQL can choose between custom and generic plans for prepared statements. A generic plan must work across parameter values, but data can be highly skewed.

Do not disable prepared statements globally as a first reaction. Capture evidence:

• Does performance vary by parameter?
• Is the generic plan different from a representative custom plan?
• Are estimated rows badly wrong?
• Did behavior change after data growth or a deployment?

The fix may be better statistics, query restructuring, separate paths for exceptional tenants, or a targeted configuration decision.

A Practical Investigation Flow#

Confirm slow endpoint
       |
       v
Capture exact SQL + parameters
       |
       v
Separate pool wait from execution time
       |
       v
Inspect plan, estimates, and buffers
       |
       v
Check indexes and table statistics
       |
       v
Check locks and concurrency
       |
       v
Compare recent deployments and data growth
       |
       v
Apply one targeted fix and verify

For a database-wide incident, use the broader checklist in Database CPU Suddenly Reaches 90%: What Do You Check First?.

Fixes and Their Trade-offs#

Production Best Practices#

1. Seed performance environments with realistic volume and skew.
2. Benchmark representative parameters, including large tenants.
3. Capture query and pool timings separately.
4. Inspect plans before adding indexes.
5. Compare estimated rows with actual rows.
6. Monitor locks and long transactions.
7. Track query fingerprints with pg_stat_statements.
8. Review plan behavior after major data growth.
9. Create indexes safely and verify write impact.
10. Re-measure p95 and p99 under concurrent load.

Conclusion#

A query is not “fast” independently of its data and workload.

SQL text
  + parameters
  + data volume and distribution
  + execution plan
  + cache state
  + concurrency and locks
  = production behavior

Local timing proves the query works on local conditions. Production debugging begins when you compare those conditions with the system that is actually slow.

Capture that evidence with an execution plan rather than inferring it from the SQL text. The EXPLAIN ANALYZE guide provides the plan-reading workflow.

References#

• ANALYZE — PostgreSQL
• Using EXPLAIN — PostgreSQL
• PREPARE — PostgreSQL