On Fri, May 12, 2023 at 8:31 AM Peter J. Holzer <hjp-pgsql@xxxxxx> 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.
|_|_) | |
| | | hjp@xxxxxx | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
