BufFileRead
Short Description
Waiting for a read from a buffered file.
Additional Information​
Buffered files in PostgreSQL are primarily temporary files. Temporary files are used for sorts and hashes. BufFileRead
is seen when these in memory buffers have been filled and the data that was spilled to disk is being re-read. Usually this is caused by your SQL queries.
How to reduce this wait​
Large waits on BufFileRead
can indicate that your work_mem
setting is too small to capture most of your sorts and hashes in memory and are being spilled to disk.
- Ensure your sorts largely occur in memory rather than writing temp files by using
explain analyze
on your query and examining the plan output looking forSort Method: external merge Disk:
to get the size of the sort. - Bear in mind that work_mem applies to each session therefore server memory can get used up very quickly
- The AWS page for BufFileRead and BufFileWrite waits has a great information. Look for ORDER BY, GROUP BY, DISTINCT, Window functions, CTAS, and Materialized view refreshes causing spills to disk.