🗄️database / postgresql

PostgreSQL Sequential Scan on 67M Rows

junior⏱ 15 min🏢 Fintech startup (daily financial reporting)

PostgreSQLEXPLAIN ANALYZEIndexSlow Querypg_stat_user_tables

Tech Stack

PostgreSQL 15
Python 3.11
SQLAlchemy
Celery
Redis

🚨

Incident Scenario

The daily financial report job has always run at 03:00 UTC and finished in under 1 minute.
This morning it ran for 47 minutes before timing out. No code was deployed in the last 2 weeks.
The report covers all completed orders from the current year.
Your DBA is on vacation. Find the cause and fix it without downtime.

🔍 Investigation Artifacts

Reveal artifacts one by one. Each clue brings you closer to the root cause.

📋

Clue #1 · log

Slow Query Log

💡 Note the query structure — what columns are used in WHERE and JOIN?

🔍

Clue #2 · query

EXPLAIN ANALYZE Output

💡 Look for "Seq Scan" — is it scanning the whole table or using an index?

🔍

Clue #3 · query

Table Stats + Index Inventory

💡 What indexes exist on the orders table? What is the row count compared to 6 months ago?

🔍

Clue #4 · query

Fix: Index Creation + Verification

💡 How do you add an index to a 67M-row production table without locking it?