the missing index that cost us 47 seconds

We use Directus as a headless CMS at Dembrane. Recently made some changes to our processing_status table, adding constraints, tightening up foreign keys. Standard schema hygiene. Then everything got slow.

Not “hmm, this feels sluggish” slow. 47.5 seconds for a single query slow.

the symptom

Wrote a quick Python script to test query times across our main collections. Most came back under a second. But anything touching processing_status was crawling. Directus admin panel timing out. API responses hanging. Users noticing.

The query was simple. A left join between project and processing_status on project_id. Nothing exotic.

finding it

Ran EXPLAIN (ANALYZE, BUFFERS) on the join and immediately saw it: PostgreSQL was doing a full sequential scan on processing_status for every row in project. The query planner expected 2.6 million rows but was only actually reading 7.

The processing_status table had foreign keys to project, conversation, conversation_chunk, and project_analysis_run. We’d added all those constraints during our schema cleanup. But never added indexes on the foreign key columns.

In PostgreSQL, foreign key constraints don’t automatically create indexes. One of those things you know but forget in practice.

the fix

CREATE INDEX idx_processing_status_project_id
    ON processing_status(project_id);

CREATE INDEX idx_processing_status_conversation_chunk_id
    ON processing_status(conversation_chunk_id);

CREATE INDEX idx_processing_status_project_analysis_run_id
    ON processing_status(project_analysis_run_id);

After creating the indexes, same query went from 47.5 seconds to 0.726 milliseconds. 65,000x improvement from three CREATE INDEX statements.

aftermath

Row estimates were wildly off too. PostgreSQL thought there were millions of rows when there were actually seven. Ran ANALYZE on the affected tables to update statistics:

ANALYZE processing_status;
ANALYZE project;
ANALYZE conversation;
ANALYZE conversation_chunk;

Tried VACUUM ANALYZE first but that doesn’t work inside a transaction (which our SQL client was running). ANALYZE alone was enough, fixed the row estimates and the query planner started making sane decisions.

going forward

When you add foreign key constraints to an existing table, add the corresponding indexes in the same migration. PostgreSQL won’t do it for you. Easy to miss because the constraint creation succeeds silently. You only notice when queries start doing full table scans instead of index lookups.

We now have a monitoring view that tracks table sizes, index sizes, and row counts:

CREATE OR REPLACE VIEW performance_metrics AS
SELECT
    'processing_status_size' as metric,
    pg_size_pretty(pg_total_relation_size('processing_status')) as value
UNION ALL
SELECT
    'index_size',
    pg_size_pretty(sum(pg_relation_size(indexrelid)))
FROM pg_index
WHERE indrelid = 'processing_status'::regclass;

65,000x. Three indexes. That was the whole fix.