Replace an N+1 pattern with a LATERAL join
Fetching the latest child row per parent by scanning all children and de-duplicating is wasteful. A LATERAL subquery seeks each parent's row directly, 600,000 buffers drops to 200,309.
Problem
What you're actually looking at
The symptom as it shows up on a real server.
"Latest row per group" implemented as DISTINCT over a full child scan reads every child row to keep one per parent. It is the SQL cousin of the N+1 query: a lot of work to return a little data.
Meridian wants each consignment's most recent parcel scan. The naive DISTINCT ON walks all 600,000 scans; a LATERAL join instead seeks the top row per consignment.
Simple terms
You want the most recent child row for each parent, the latest scan for each parcel. The naive way reads every child row and then throws all but one per parent away, which is an enormous amount of wasted reading. A LATERAL join flips it around: for each parent, seek straight to its single latest row. Same answer, a fraction of the work.
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.