Postgres Pro
Downloads
Home   >   mailing lists

Re: Postgresql OOM - Mailing list pgsql-hackers

From Pantelis Theodosiou
Subject Re: Postgresql OOM
Date
Msg-id CAE3TBxxfzF3GpUz_tKF=+8fe0-9+C6M8Y01nnK8FpEFLtfe2WA@mail.gmail.com
Whole thread Raw
In response to Postgresql OOM  (Radu Radutiu <rradutiu@gmail.com>)
Responses Re: Postgresql OOM
Re: Postgresql OOM
List pgsql-hackers
Tree view


On Thu, Jun 6, 2024 at 1:25 PM Radu Radutiu <rradutiu@gmail.com> wrote:
Hello all,

I have a query that forces an out of memory error, where the OS will kill the postgresql process.
The query plan (run immediately after a vacuum analyze) is at https://explain.depesz.com/s/ITQI#html .

... 

Any idea on how to identify the problem? I can reproduce it on demand. Should I report it pgsql-bugs?

Best regards,
Radu

I am not qualified to answer on the OOM issue but why are you joining the same table (outputrequest) 4 times (using an identical join condition)?
This essentially does a cross join, if an input_sequence value has say, 1000 related rows in outputrequest, you will be getting 1000^4 rows in the result set.

  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

pgsql-hackers by date:

Previous
From: Julien Tachoires
Date:
Subject: Re: Compress ReorderBuffer spill files using LZ4
Next
From: Bertrand Drouvot
Date:
Subject: Re: How about using dirty snapshots to locate dependent objects?