Re: Out of Memory errors are frustrating as heck!

On Fri, Apr 19, 2019 at 11:34:54PM -0400, Gunther wrote:
> On 4/19/2019 17:01, Justin Pryzby wrote:
> >Were you able to reproduce the issue in some minimized way ? Like after
> >joining fewer tables or changing to join with fewer join conditions ?
> >
> >On Thu, Apr 18, 2019 at 05:21:28PM +0200, Tomas Vondra wrote:
> >>It would be possible to do at least one of these two things:
>
> Thanks, and sorry for my pushyness. Yes, I have pin pointed the HashJoin,
> and I have created the two tables involved.
>
> The data distribution of the join keys, they are all essentially UUIDs and
> essentially random.
>
> I am sharing this data with you. However, only someone who can actually
> control the planner can use it to reproduce the problem. I have tried but
> not succeeded. But I am sure the problem is reproduced by this material.
>
> Here is the part of the plan that generates this massive number of calls to
>
> -> Hash Right Join (cost=4255031.53..5530808.71 rows=34619 width=1197)
> Hash Cond: (((q.documentinternalid)::text = (documentinformationsubject.documentinternalid)::text) AND ((r.targetinternalid)::text = (documentinformationsubject.actinternalid)::text))
> -> Hash Right Join (cost=1341541.37..2612134.36 rows=13 width=341)
> Hash Cond: (((documentinformationsubject_2.documentinternalid)::text = (q.documentinternalid)::text) AND ((documentinformationsubject_2.actinternalid)::text = (q.actinternalid)::text))
> ... let's call this tmp_q ...
> -> Hash (cost=2908913.87..2908913.87 rows=34619 width=930)
> -> Gather Merge (cost=2892141.40..2908913.87 rows=34619 width=930)
> ... let's call this tmp_r ...

Would you send basic stats for these ?
q.documentinternalid, documentinformationsubject.documentinternalid, r.targetinternalid, documentinformationsubject.actinternalid

Like from this query
https://wiki.postgresql.org/wiki/Slow_Query_Questions#Statistics:_n_distinct.2C_MCV.2C_histogram

Justin