Choose the right join: fix a nested loop on a misestimate
Point-lookup joins want a nested loop over an index; missing that index forces a hash join that scans both tables. Adding the join-key index takes the join from 36 ms to 0.03 ms.
Problem
What you're actually looking at
The symptom as it shows up on a real server.
The planner picks a join strategy from its cost model. Without an index on the join key, a query that fetches a single matching row cannot use a cheap nested loop and falls back to hashing the whole inner table.
A Meridian query joins consignments to a lookup by reference and returns three rows. With no index on the join key the planner hashes a full parallel scan of the big table instead of seeking three index entries.
Simple terms
When you join two tables PostgreSQL picks a strategy. If it can look the matching row up directly through an index, it uses a cheap nested loop: walk one side, seek straight to the other. Without that index it has no choice but to read the whole second table into memory and hash it, a lot of work when you only wanted a few rows back. Adding an index on the join key lets it seek instead of scan.
Full runbook for this incident
- The full identify checklist, the exact signals that tell you it's this incident
- Every diagnostic query; lab output is attached only to the steps we actually captured
- The resolution path and the pitfalls that make it worse
- Mitigation steps to stop it recurring, plus a verify-you're-done query
Card required. Cancel before day 7 and you are not charged.
More in this category
Other Query performance runbooks
Neighbouring incidents that share the same diagnostic surface.
Need the full procedure?
Pro runbooks finish the incident path
Free runbooks teach the shape. Pro opens the full step transcript, edge cases, and prevention depth.