On Fri, May 12, 2023 at 8:31 AM Peter J. Holzer <hjp-pg...@hjp.at> wrote:
> On 2023-05-11 21:27:57 +0200, Marc Millas wrote: > > the 750000 lines in each tables are not NULLs but '' empty varchar, > which, > > obviously is not the same thing. > > and which perfectly generates 500 billions lines for the left join. > > So, no planner or statistics pbs. apologies for the time wasted. > > No problem. Glad to have solved that puzzle. > > > Back to the initial pb: if, with temp_file_limit positioned to 210 GB, > > I try to run the select * from table_a left join table_b on the col_a > > (which contains the 750000 '' on both tables) > > then postgres do crash, killed by oom, after having taken 1.1 TB of > additional > > disk space. > > My guess is that the amount of parallelism is the problem. > > work_mem is a per-node limit. Even a single process can use a multiple of > work_mem if the query contains nested nodes (which almost every query > does, but most nodes don't need much memory). With 5 parallel workers, > the total consumption will be 5 times that. So to prevent the OOM > condition you would need to reduce work_mem or max_parallel_workers (at > least for this query). > we have more than 100GB RAM and only 1 user, with one request running. work_mem is set to 10MB. for oom to kill due to work_mem it means that for one request with 2 left join, postgres needs more than 10.000 work_mem buffers. to me, it seems difficult to believe. but that postgres may need that RAM space for hashing or whatever other similar purpose seems more probable. no ? > > The description temp_file_limit says "...the maximum amount of disk > space that *a process* can use...". So with 5 workers that's 210*5 = > 1050 GB total. Again, you may want to reduce either temp_file_limit or > max_parallel_workers. > Yes, but if so, we may have had a request canceled due to temp_file limit, which was not the case. > > > to my understanding, before postgres 13, hash aggregate did eat RAM > limitless > > in such circumstances. > > but in 14.2 ?? > > (I know, 14.8 is up...) > > Maybe the older version of postgres didn't use as many workers for that > query (or maybe not parallelize it at all)? > > hp > > -- > _ | Peter J. Holzer | Story must make more sense than reality. > |_|_) | | > | | | h...@hjp.at | -- Charles Stross, "Creative writing > __/ | http://www.hjp.at/ | challenge!" >
