Re: Postgresql OOM

Radu Radutiu <rradutiu(at)gmail(dot)com> writes:
> The query itself runs fine in a reasonable time with enable_parallel_hash =
> 'off'. I see two problems - one is the wrong execution plan (right after
> running analyze), the second and the most important is the huge memory
> usage (far exceeding work_mem and shared buffers) leading to OOM.
> See https://explain.depesz.com/s/yAqS for the explain plan
> with enable_parallel_hash = 'off.

What it looks like to me is that the join key column has very skewed
statistics, such that a large majority of the tuples end up in the
same hash bucket (probably they even all have identical keys). I think
the memory growth is coming from the executor repeatedly splitting
the buckets in a vain attempt to separate those tuples into multiple
buckets.

The planner should recognize this situation and avoid use of hash
join in such cases, but maybe the statistics aren't reflecting the
problem, or maybe there's something wrong with the logic specific
to parallel hash join. You've not really provided enough information
to diagnose why the poor choice of plan.

regards, tom lane