PostgreSQL slow queries from missing index on foreign key columns

PostgreSQL queries involving JOINs or WHERE clauses on foreign key columns are unexpectedly slow. EXPLAIN shows sequential scans on large tables even though foreign keys are defined.

PostgreSQL does NOT automatically create indexes on foreign key columns (unlike MySQL). You must create them manually: CREATE INDEX idx_orders_user_id ON orders(user_id). Check for missing FK indexes: SELECT c.conrelid::regclass, a.attname FROM pg_constraint c JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY(c.conkey) WHERE c.contype = 'f' AND NOT EXISTS (SELECT 1 FROM pg_index i WHERE i.indrelid = c.conrelid AND a.attnum = ANY(i.indkey)). Also impacts DELETE performance on parent tables — without the index, Postgres does a sequential scan on the child table to check for references.

Foreign key constraints enforce referential integrity but don't imply access patterns. PostgreSQL leaves indexing decisions to the developer. DELETE cascades are especially slow because Postgres must verify no child rows reference the parent.