On Mon, Oct 07, 2019 at 01:52:41PM -0700, dangal wrote:
Hello everyone, I have a question to see if you can help me, I have set work_mem in 100 MB but I have the following in the pgbadger Queries generating the most temporary files (N) Count Total size Min size Max size Avg size 58 3.24 GiB 57.15 MiB 57.15 MiB 57.15 MiB How could it be that if you require 57 MB, 100 MB will not reach ? thanks a lot
The on-disk and in-memory representations are not the same, and the on-disk one is often significantly more efficient. Data that needs 57MB temporary file may beed ~150MB memory to perform in-memory sort, for example. Yet another reason may be that e.g. hash join splits the memory into batches, and each one has to fit into work_mem. And whevener we hit the limit, we double the number of batches, i.e. we cut the batch size in half. Thus the size is somewhere between 50 and 100MB, with ~75MB on average. Furthermore, we also estimate the batch size before running the query, so we may start with unnecessarily many batches, pushing the average batch size down even more. FWIW it's somewhat suspicious you have 58 temp files with almost no variability in size. That seems as if a single query was executed repeatedly. Maybe try looking into what query that is. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
