Scenario: a query slows down as the table reaches 30 million rows Intermediate

Nobody touched this endpoint for 18 months; only the table kept growing. You go from symptom to query, from query to plan, then to the index. Every plan in the game was run for real on PostgreSQL 17 against a 30-million-row table, only trimmed for readability.

• If you pick wrong, read the explanation and Choose again. Only the first choice at each step counts toward the score.
• Before choosing, look closely at rows, Rows Removed by Filter and Buffers in the plan.

Scenario

The "My orders" API: 20 ms at launch, 4 seconds after 18 months

You are the on-call backend dev. The orders table has grown to 30 million rows and the "My orders" endpoint keeps getting slower. Every choice immediately shows its consequences on the metrics.

Mean query time3,800 ms

Max throughput4 req/s

Buffers read (worst request)265,600

DB cost800 $/month

Step 1/4 · What is slow?

At launch, the "My orders" screen returned in 20 ms. 18 months later, the orders table has 30 million rows and this screen takes about 4 seconds. Database CPU is always high, and many other endpoints are slowing down too.

You do not yet know exactly which SQL statement is the culprit.

orders

30 million rows, 2.7 GB

GET /me/orders

~4 seconds

DB

4 vCPU, CPU always high

What do you do?

AUpgrade the DB from 4 to 8 vCPU first, investigate laterBCheck pg_stat_statements, sorted by total_exec_timeSELECT query, calls, total_exec_time, mean_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;CSet log_min_duration_statement = 1000 and read the slow logDCache the "My orders" response in Redis with a 60-second TTL

When you finish, read Reading EXPLAIN and EXPLAIN ANALYZE for a four-step process to read an unfamiliar plan.