Missing Index on Foreign Key
pg_views: pg_constraint, pg_index, pg_class, pg_attribute
Full detection logic, thresholds, and guided fixes ship in the product.
Book a demo →Overview
Every foreign key constraint implies a join. When Postgres executes that join, it needs to find matching rows in the referencing table quickly. Without an index on the FK column, Postgres falls back to a sequential scan of the entire referencing table for every row in the referenced table—a classic nested loop that scales as O(n×m).
The fix is a single B-tree index on the FK column. Postgres will use it automatically for both the join and for ON DELETE CASCADE enforcement. The index can be created online with CONCURRENTLY, meaning no table lock and no downtime.
This rule fires when a foreign key exists on a column that has no single-column or leading-column composite index covering it. It ignores tables below 1,000 rows where a seq-scan is cheaper.
See this rule in action
Full detection logic, thresholds, and guided fixes ship in the product. The licensed agent runs this check — and all others — continuously against your database, surfaces findings with AI explanations, and surfaces a guided remediation path inside the dashboard.
Book a demo →Common False Positives
- Tables under 1,000 rows — seq-scan is cheaper; insightral excludes these automatically.
- The FK column is already the first column of an existing composite index — the composite index satisfies the join; check pg_index.indkey ordering.
Missing Index on Foreign Key — and 100+ more checks — in the licensed product
Full detection logic, thresholds, and guided fixes ship in the product.
Book a demo →Last updated: 2026-05-13 · View the rule library