On Wed, 2023-05-10 at 16:35 +0200, Marc Millas wrote: > > > postgres 14.2 on Linux redhat > > > > > > temp_file_limit set around 210 GB. > > > > > > a select request with 2 left join have crashed the server (oom killer) after the postgres > > > disk occupation did grow from 15TB to 16 TB. > > > > > > What are the cases where postgres may grow without caring about temp_file_limit ? > > > > That's too little information for a decent answer. > > One obvious answer is: if it is not writing temporary files. > > so here is a little more info: > > QUERY PLAN > --------------------------------------------------------------------------------------------------------------------------- > Unique (cost=72377463163.02..201012533981.80 rows=1021522829864 width=97) > -> Gather Merge (cost=72377463163.02..195904919832.48 rows=1021522829864 width=97) > Workers Planned: 5 > -> Sort (cost=72377462162.94..72888223577.87 rows=204304565973 width=97) > Sort Key: t1.col_ine, (CASE WHEN (t2.col_ibi IS NULL) THEN t3.col_ibi ELSE t2.col_ibi END) > -> Parallel Hash Left Join (cost=604502.76..1276224253.51 rows=204304565973 width=97) > Hash Cond: ((t1.col_ano)::text = (t2.col_ano)::text) > -> Parallel Hash Left Join (cost=300803.38..582295.38 rows=4857277 width=52) > Hash Cond: ((t1.col_ne)::text = (t3.col_ne)::text) > -> Parallel Seq Scan on table_a t1 (cost=0.00..184052.35 rows=2616335 width=53) > -> Parallel Hash (cost=243466.06..243466.06 rows=2965306 width=31) > -> Parallel Seq Scan on table_b t3 (cost=0.00..243466.06 rows=2965306 width=31) > -> Parallel Hash (cost=243466.06..243466.06 rows=2965306 width=34) > -> Parallel Seq Scan on table_b t2 (cost=0.00..243466.06 rows=2965306 width=34) > JIT: > Functions: 19 > Options: Inlining true, Optimization true, Expressions true, Deforming true > (17 rows) Perhaps parallel query drives you OOM. Does the problem also happen if "max_parallel_workers_per_gather" is set to 0? Yours, Laurenz Albe
