Diagnose detoasting overhead on wide rows
The same 5,000-row index scan took 0.887 ms selecting three narrow columns and 120.265 ms once it touched the TOASTed body. The plan shape is identical; only the buffer count gives it away, 70 buffers against 20,098.
Problem
What you're actually looking at
The symptom as it shows up on a real server.
Large values are stored out of line in a TOAST table and fetched only when a query actually reads the column. That fetch does not appear as a node in the plan. It shows up as an unexplained jump in buffer counts and execution time on a scan that otherwise looks fine, which makes it easy to blame the index instead.
consignment_documents holds 20,000 scanned paperwork rows. A listing endpoint that used to be quick got slow after someone added the document body to the SELECT list.
Simple terms
PostgreSQL stores oversized column values, a scanned document, a big block of text, in a separate side table called TOAST, and only fetches them when a query actually asks for that column. That fetch is never shown as its own step in the plan, so the query looks identical whether or not it touches the heavy column. The only tell is the buffer count jumping. Leave the big column out of the SELECT unless you truly need it and the scan stays fast.
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.