Re: Postgresql 16.3 Out Of Memory - Mailing list pgsql-general

I did run VACUUM ANALYZE after the upgrade. I've even run it now before getting a new explain plan that is very similar (just the costs differ):

 Gather Merge  (cost=12336145.92..16111570.23 rows=31531596 width=66)
   Workers Planned: 4
   ->  Sort  (cost=12335145.86..12354853.11 rows=7882899 width=66)
         Sort Key: t.msg_status DESC, t.input_sequence
         ->  Parallel Hash Right Join  (cost=9376528.66..11242773.26 rows=7882899 width=66)
               Hash Cond: (snd_tro.reply_input_sequence = t.input_sequence)
               ->  Parallel Seq Scan on tbl_outputrequest snd_tro  (cost=0.00..1431919.45 rows=20057645 width=16)
               ->  Parallel Hash  (cost=9254599.76..9254599.76 rows=5245992 width=58)
                     ->  Parallel Hash Right Join  (cost=7458636.44..9254599.76 rows=5245992 width=58)
                           Hash Cond: (rec_tro.input_sequence = t.input_sequence)
                           ->  Parallel Seq Scan on tbl_outputrequest rec_tro  (cost=0.00..1431919.45 rows=20057645 width=16)
                           ->  Parallel Hash  (cost=7380902.99..7380902.99 rows=3491156 width=50)
                                 ->  Parallel Hash Right Join  (cost=5592677.17..7380902.99 rows=3491156 width=50)
                                       Hash Cond: (rpl_snd_tro.reply_input_sequence = r.input_sequence)
                                       ->  Parallel Seq Scan on tbl_outputrequest rpl_snd_tro  (cost=0.00..1431919.45 rows=20057645 width=16)
                                       ->  Parallel Hash  (cost=5518353.72..5518353.72 rows=3491156 width=42)
                                             ->  Parallel Hash Right Join  (cost=3729209.40..5518353.72 rows=3491156 width=42)
                                                   Hash Cond: (rpl_rec_tro.input_sequence = r.input_sequence)
                                                   ->  Parallel Seq Scan on tbl_outputrequest rpl_rec_tro  (cost=0.00..1431919.45 rows=20057645 width=16)
                                                   ->  Parallel Hash  (cost=3658294.95..3658294.95 rows=3491156 width=34)
                                                         ->  Parallel Hash Right Join  (cost=1883503.35..3658294.95 rows=3491156 width=34)
                                                               Hash Cond: (r.originalrequest_id = t.input_sequence)
                                                               ->  Parallel Seq Scan on tbl_inputrequest r  (cost=0.00..1739752.66 rows=13348166 width=16)
                                                               ->  Parallel Hash  (cost=1839863.91..1839863.91 rows=3491156 width=26)
                                                                     ->  Parallel Seq Scan on tbl_inputrequest t  (cost=0.00..1839863.91 rows=3491156 width=26)
                                                                           Filter: ((receive_time < '2024-05-17 00:00:00'::timestamp without time zone) AND (input_sequence < '202406020168279904'::bigint) AND ((msg_status)::text = ANY ('{COMPLETED,REJECTED}'::text[])))

The query cost is high and it returns a significant number of rows. However it should not consume 64+ GB RAM with the default enable_parallel_hash = 'on' when my shared_buffers is 8GB. The temporary fix I've implemented to get the system working is a change in the application configuration so that the timestamp filter selects fewer rows (3x less), together with setting enable_parallel_hash = 'off'. PostgreSQL service memory usage grows and stays over 20GB even with this setting. I'd like to find out exactly what causes the high memory usage as we have other projects using PostgreSQL and they are scheduled for upgrade from v12.  

My test.sql looks like this (application uses prepared statements, the two set operations are to revert for the current session the already implemented fixes):

prepare my_query (timestamp,bigint) as SELECT  t.input_sequence, rec_tro.output_sequence, r.input_sequence, rpl_rec_tro.output_sequence, rpl_snd_tro.output_sequence, snd_tro.output_sequence, t.msg_type  FROM inputrequest t  LEFT JOIN outputrequest rec_tro ON rec_tro.input_sequence = t.input_sequence LEFT JOIN inputrequest r ON r.originalRequest_id = t.input_sequence   LEFT JOIN outputrequest rpl_rec_tro ON rpl_rec_tro.input_sequence = r.input_sequence  LEFT JOIN outputrequest rpl_snd_tro ON rpl_snd_tro.reply_input_sequence = r.input_sequence  LEFT JOIN outputrequest snd_tro ON snd_tro.reply_input_sequence = t.input_sequence  WHERE t.receive_time < $1 AND t.input_sequence < $2  AND t.msg_status IN ('COMPLETED', 'REJECTED')  ORDER BY t.msg_status DESC, t.input_sequence ;

EXPLAIN  EXECUTE my_query('2024-05-17 00:00:00', 202406020168279904);