Re: Out of Memory errors are frustrating as heck!

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 ...

This can be logically reduced to the following query

SELECT *
FROM tmp_q q
RIGHT OUTER JOIN tmp_r r
USING(documentInternalId, actInternalId);

with the following two tables

CREATE TABLE xtmp_q (
documentinternalid character varying(255),
operationqualifiercode character varying(512),
operationqualifiername character varying(512),
actinternalid character varying(255),
approvalinternalid character varying(255),
approvalnumber character varying(555),
approvalnumbersystem character varying(555),
approvalstatecode character varying(512),
approvalstatecodesystem character varying(512),
approvaleffectivetimelow character varying(512),
approvaleffectivetimehigh character varying(512),
approvalstatuscode character varying(32),
licensecode character varying(512),
agencyid character varying(555),
agencyname text
);

CREATE TABLE tmp_r (
documentinternalid character varying(255),
is_current character(1),
documentid character varying(555),
documenttypecode character varying(512),
subjectroleinternalid character varying(255),
subjectentityinternalid character varying(255),
subjectentityid character varying(555),
subjectentityidroot character varying(555),
subjectentityname character varying,
subjectentitytel text,
subjectentityemail text,
otherentityinternalid character varying(255),
confidentialitycode character varying(512),
actinternalid character varying(255),
operationcode character varying(512),
operationname text,
productitemcode character varying(512),
productinternalid character varying(255)..
);

you can download the data here (URLs just a tiny bit obfuscated):

The small table http:// gusw dot net/tmp_q.gz

The big table is in the form of 9 parts of 20 MB each, http:// gusw dot
net/tmp_r.gz.00, .01, .02, ..., .09, maybe you need only the first part.

Download as many as you have patience to grab, and then import the data
like this:

\copy tmp_q from program 'zcat tmp_q.gz'
\copt tmp_r from program 'cat tmp_r.gz.* |zcat'

The only problem is that I can't test that this actually would trigger
the memory problem, because I can't force the plan to use the right
join, it always reverts to the left join hashing the tmp_q:

-> Hash Left Join (cost=10.25..5601401.19 rows=5505039 width=12118)
Hash Cond: (((r.documentinternalid)::text = (q.documentinternalid)::text) AND ((r.actinternalid)::text = (q.actinternalid)::text))
-> Seq Scan on tmp_r r (cost=0.00..5560089.39 rows=5505039 width=6844)
-> Hash (cost=10.10..10.10 rows=10 width=6306)
-> Seq Scan on tmp_q q (cost=0.00..10.10 rows=10 width=6306)

which is of course much better, but when tmp_q and tmp_r are the results
of complex stuff that the planner can't estimate, then it gets it wrong,
and then the issue gets triggered because we are hashing on the big
tmp_r, not tmp_q.

It would be so nice if there was a way to force a specific plan for
purposes of the testing.  I tried giving false data in pg_class
reltuples and relpages:

foo=# analyze tmp_q;
ANALYZE
foo=# analyze tmp_r;
ANALYZE
foo=# select relname, relpages, reltuples from pg_class where relname in ('tmp_q', 'tmp_r');
relname | relpages | reltuples
---------+----------+-------------
tmp_r | 5505039 | 1.13467e+08
tmp_q | 7 | 236
(2 rows)

foo=# update pg_class set (relpages, reltuples) = (5505039, 1.13467e+08) where relname = 'tmp_q';
UPDATE 1
foo=# update pg_class set (relpages, reltuples) = (7, 236) where relname = 'tmp_r';
UPDATE 1

but that didn't help. Somehow the planner outsmarts every such trick, so
I can't get it to follow my right outer join plan where the big table is
hashed.  I am sure y'all know some way to force it.

regards,
-Gunther