I am querying some data from a view using
copy (select * from tbl where id in (n) order by 1) command (the table's in the view are ~600GB, almost all the columns included in the view) and it generates around 200Gb of temp files since my current
work_mem is with default configuration. I know I must change few params to avoid temporary files. But I am confused on below points.
If the query's result size is >
work_mem, will it generate more temp files / use disks?
Is it true that
work_memwont support more than 2Gb?
My RAM is 128Gb and SSD storage, how much
work_memshould I need to use this for this query? (I will create a separate user to query this table, and alter the user's
Is there any way to cancel the query before if it reaches out of space error? (I could limit by enabling max temp file limit. But I am not sure how the temp file size will be and how many files will be generated for a specific query)
(i.e., finding the more temp file/huge result(>10G) generating query, trigger
pg_cancel_backend for that before it crashes the server)