Add indexes on foreign keys and common query filters | BeforeMerge Rules | BeforeMerge

Add indexes on foreign keys and common query filters | BeforeMerge Rules | BeforeMerge

Why This Matters

PostgreSQL creates indexes on PRIMARY KEYs and UNIQUE columns automatically, but NOT on foreign keys.

Good

CREATE TABLE findings (
  id uuid PRIMARY KEY,
  scan_id uuid REFERENCES scans(id),
  rule_id uuid REFERENCES rules(id)
);
 
CREATE INDEX idx_finding_scan ON findings(scan_id);
CREATE INDEX idx_finding_rule ON findings(rule_id);

Bad

CREATE TABLE findings (
  scan_id uuid REFERENCES scans(id),  -- No index!
  rule_id uuid REFERENCES rules(id)   -- No index!
);