Re: Postgresql OOM

>
>
>
>> 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
>>
>
> Thanks for looking into this. I'm not sure what information would be
> needed to look at the choice of plan.
> The statistics for the join conditions in the query would be:
> join_condition | min_count | max_count | avg_count
> ----------------+-----------+-----------+----------------------------
> snd_tro | 0 | 0 | 0.000000000000000000000000
> rpl_rec_tro | 0 | 2 | 0.99869222814474470477
> rec_tro | 0 | 2 | 0.99869222814474470477
> rpl_snd_tro | 0 | 0 | 0.000000000000000000000000
> r | 0 | 1 | 0.49850916663490161653
>
>
> The relevant columns for the tables are:
> postgres=# \d inputrequest
> Table "public.inputrequest"
> Column | Type | Collation |
> Nullable | Default
>
> --------------------------+-----------------------------+-----------+----------+---------
> input_sequence | bigint | | not
> null |
> msg_type | character varying(8) | | not
> null |
> msg_content | text | | not
> null |
> msg_reference | character varying(35) | |
> |
> originalrequest_id | bigint | |
> |
> receive_time | timestamp without time zone | | not
> null |
> related_output_sequence | bigint | |
> |
> msg_status | character varying(15) | |
> |
>
> Indexes:
> "inputrequest_pkey" PRIMARY KEY, btree (input_sequence)
> "inputrequest_originalrequest_id_idx" btree (originalrequest_id)
>
> postgres=# \d outputrequest
> Table "public.outputrequest"
> Column | Type | Collation |
> Nullable | Default
>
> ------------------------+-----------------------------+-----------+----------+---------
> output_sequence | bigint | | not
> null |
> input_sequence | bigint | |
> |
> msg_type | character varying(8) | |
> |
> msg_content | text | | not
> null |
> msg_reference | character varying(35) | |
> |
> reply_input_sequence | bigint | |
> |
> status | integer | | not
> null |
> related_input_sequence | bigint | |
> |
> Indexes:
> "outputrequest_pkey" PRIMARY KEY, btree (output_sequence)
> "outputrequest_input_sequence_idx" btree (input_sequence)
> "outputrequest_reply_input_sequence_idx" btree (reply_input_sequence)
>
>
I wonder if our choice of primary keys (input_sequence and output_sequence)
has something to do with the skew in the hash bucket distribution. We use
the following format: yyyymmdd????????xx , where ???????? is more or less a
sequence and xx is the node generating the id, i.e. 01,02,etc (with only
one or two values in the dataset).

I wonder if it would be difficult to have an upper limit on the private
memory that can be allocated by one process (or all processes similar to
Oracle's pga_aggregate_limit). I would rather have one query failing with
an error message instead of postgres eating up all memory and swap on the
server.

Best regards,
Radu