Re: Postgresql OOM - Mailing list pgsql-hackers

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)