A query that was 50ms suddenly takes 10 seconds: troubleshoot from evidence

• Differential diagnosis – the query was fast before and is slow now, so something in the environment changed and you must find the specific delta.
• Compare execution plans – run EXPLAIN ANALYZE and compare the current plan to the known-good baseline to see if the planner changed its strategy.
• Stale statistics – after a bulk load, row estimates become wrong and the planner picks a scan over an index; ANALYZE refreshes them.
• Lock contention – a query can spend ten seconds waiting for a lock held by a long transaction, not computing.
• Parameter skew – the same query shape can be fast for one value and slow for another if the cached plan was built for the selective case.
• "Just add an index" is wrong – if the cause is a lock wait or stale statistics, an index does nothing and may add write overhead.

Why does the word "suddenly" matter?

The query and its parameters are the same as yesterday, so the query itself is not the villain. Something in the environment changed, and your job is to find the delta. This is a differential diagnosis.

-- Step 1: capture the current execution plan
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT t.txn_id, t.amount, t.status
FROM transactions t
WHERE t.account_id = 42
  AND t.status = 'PENDING'
ORDER BY t.created_at DESC;

Compare this plan to the baseline you captured when the query was fast. This is why Pankaj's team at National Bank of India stores baseline plans.

How do you compare the execution plan to the baseline?

If yesterday the plan used an index scan and today it uses a sequential scan, the planner changed its mind. Look at estimated versus actual rows.

-- Yesterday (baseline): fast
Index Scan using idx_txn_account_status on transactions
  (cost=0.43..12.5 rows=15 actual time=0.05..0.12 rows=12)

-- Today (regression): slow
Seq Scan on transactions
  (cost=0.00..85000 rows=500000 actual time=0.1..9800 rows=12)
  Filter: (account_id = 42 AND status = 'PENDING')
  Rows Removed by Filter: 4999988

The planner estimated 500000 rows instead of 15, which is a classic sign of stale statistics.

What are stale statistics and how do you fix them?

After a large bulk load, the planner's row estimates become wrong because the statistics tables have not been refreshed. The planner thinks a filter will match many rows and chooses a sequential scan.

-- Check when statistics were last updated
SELECT relname, last_analyze, last_autoanalyze, n_live_tup
FROM pg_stat_user_tables
WHERE relname = 'transactions';

-- Fix: refresh statistics
ANALYZE transactions;

-- Verify: re-run EXPLAIN and confirm the plan changed back
EXPLAIN (ANALYZE, BUFFERS)
SELECT t.txn_id, t.amount, t.status
FROM transactions t
WHERE t.account_id = 42 AND t.status = 'PENDING';

-- After ANALYZE: fast again
Index Scan using idx_txn_account_status on transactions
  (cost=0.43..12.5 rows=12 actual time=0.05..0.11 rows=12)

How do you check for lock contention?

A query can be fast in CPU terms but spend ten seconds waiting for a lock held by a long transaction.

-- PostgreSQL: find blocked queries
SELECT pid, wait_event_type, wait_event, state, query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

-- Find the blocking transaction
SELECT blocked_locks.pid AS blocked_pid,
       blocking_locks.pid AS blocking_pid,
       blocking_activity.query AS blocking_query
FROM pg_locks blocked_locks
JOIN pg_locks blocking_locks
  ON blocking_locks.locktype = blocked_locks.locktype
  AND blocking_locks.relation = blocked_locks.relation
  AND blocking_locks.granted
JOIN pg_stat_activity blocking_activity
  ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

If the time is spent waiting, no index will help. You need to address the blocking transaction.

What is parameter skew and how does it cause regressions?

The same query shape can be fast for one value and slow for another. A cached plan built for a selective value becomes terrible for a non-selective one.

-- Fast: 'SETTLED' matches 1000 rows (selective)
SELECT * FROM transactions WHERE status = 'SETTLED';
-- Plan: Index Scan, 1000 rows, 5ms

-- Slow: 'PENDING' matches 2,000,000 rows during a backlog
SELECT * FROM transactions WHERE status = 'PENDING';
-- Same cached plan: Index Scan on 2M rows, 10 seconds
-- A Seq Scan would actually be faster here

In PostgreSQL, the planner re-plans each statement, but in MySQL with prepared statement caching or connection pooling, a stale plan can be reused.

How do you check for data growth?

A table that silently grew from fifty thousand to fifty million rows changes which plan is optimal.

-- PostgreSQL: check table size over time
SELECT relname,
       n_live_tup AS live_rows,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
WHERE relname = 'transactions';

relname      | live_rows  | total_size
-------------+------------+-----------
transactions | 50000000   | 12 GB

If the table was 500K rows a month ago and is now 50M, the optimal plan has changed.

How do you check for recent schema changes and deploys?

Did someone drop an index last night? Did a migration change a column type and invalidate an index? Did a new ORM query ship?

-- Check if the expected index still exists
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'transactions'
  AND indexname = 'idx_txn_account_status';

-- If empty, the index was dropped!

Correlate the slowdown time with the deploy timeline. At National Bank of India, Rajesh keeps a deploy log with timestamps that can be matched against the regression onset.

Why is "just add an index" the wrong reflex?

Adding an index without diagnosing the root cause is wrong for several reasons.

┌──────────────────────────┬──────────────────────────────────┐
│ Root Cause               │ Why an index does not help       │
├──────────────────────────┼──────────────────────────────────┤
│ Lock contention          │ Query waits, not scans           │
│ Stale statistics         │ Planner may ignore the new index │
│ Dropped index            │ Restore the original, don't guess│
│ Parameter skew           │ Plan choice issue, not missing   │
│ Resource saturation      │ CPU/disk starved, not plan issue │
│ Adding a random index    │ Adds write overhead, may hurt    │
└──────────────────────────┴──────────────────────────────────┘

You fix what changed, not what you imagine.

How do you check resource utilization?

If CPU is saturated, disk is at capacity, or memory pressure forces sorts to spill to disk, the query is slow because the server is starved.

# Quick resource check
top -bn1 | head -5
iostat -x 1 3

-- PostgreSQL: check for disk sorts (work_mem too small)
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM transactions ORDER BY amount DESC LIMIT 100;
-- Look for "Sort Method: external merge Disk" in output

What is the senior troubleshooting checklist?

Pankaj follows this sequence at National Bank of India for every regression.

1. Capture the current plan with EXPLAIN (ANALYZE, BUFFERS).
2. Compare to the baseline plan.
3. Check pg_stat_user_tables.last_analyze for stale statistics.
4. Check pg_stat_activity for lock waits.
5. Check the deploy and migration log for recent changes.
6. Check table size and row count growth.
7. Check server resource utilization (CPU, I/O, memory).
8. Fix the specific thing that changed.
9. Verify the fix restores the original latency.