Scenario
A backend engineer reports that a customer order lookup query that previously ran in milliseconds is now taking 8–12 seconds. The table has grown from 50,000 to 2 million rows over the past 3 months. No schema changes were made.
How to Identify
Conditions:
- Query running above acceptable threshold in
pg_stat_statements
EXPLAIN output shows Seq Scan on a large table- High I/O visible in
pg_stat_bgwriter (buffers_clean activity)
pg_stat_user_tables shows last_analyze is stale or NULL- Index exists on the filter column but
idx_scan = 0 in pg_stat_user_indexes
Analysis Steps
-- 1. Identify slow queries
SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
-- 2. Run EXPLAIN ANALYZE on the specific query
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE customer_id = 42;
-- 3. Check statistics freshness
SELECT schemaname, relname, last_analyze, last_autoanalyze,
n_live_tup, n_dead_tup
FROM pg_stat_user_tables
WHERE relname = 'orders';
-- 4. Check if an index exists and if it is being used
SELECT indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE relname = 'orders';
-- 5. Check for type mismatch between column and predicate
SELECT attname, atttypid::regtype
FROM pg_attribute
WHERE attrelid = 'orders'::regclass AND attname = 'customer_id';
Pitfalls
- Do not
SET enable_seqscan = off in production — this forces index use even when the planner is correct to use a seq scan (e.g., returning 80% of the table). It hides root causes.
- Do not add an index without checking the write/read ratio first. High-write tables pay index maintenance overhead on every INSERT/UPDATE/DELETE.
- Do not confuse planning time with execution time in EXPLAIN output — a query can plan fast but execute slow, or vice versa.
- Stale statistics can make the plan appear correct when looking at estimated rows — always compare
rows (estimate) vs actual rows in EXPLAIN ANALYZE.
Resolution Approach
A sequential scan on a large table means PostgreSQL reads every row. The fix depends on root cause: