Date: Tue, 19 Oct 2021 13:08:03 +0200 (CEST)
From: Andreas Joseph Krogh <andreas@visena.com>
To: aditya desai <admad123@gmail.com>
Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Message-ID: <VisenaEmail.53.2208dc1e4c778c76.17c983b17bb@tc7-visena>
In-Reply-To: 
 <CAN0SRDFqJRjuSxNUrTvCQwKuhGv8r5rUn9WnX65kGSsTSxhoCw@mail.gmail.com>
References: 
 <CAN0SRDHDte88-v4=uhxH1FWU0nt3xBJGTe5hTGj4KvKTtVRDtA@mail.gmail.com>
 <CAN0SRDFqJRjuSxNUrTvCQwKuhGv8r5rUn9WnX65kGSsTSxhoCw@mail.gmail.com>
Subject: Sv: Fwd: Query out of memory
MIME-Version: 1.0
Content-Type: multipart/mixed;
	boundary="----=_Part_1088_1582712640.1634641683223"
Archived-At: 
 <https://www.postgresql.org/message-id/VisenaEmail.53.2208dc1e4c778c76.17c983b17bb%40tc7-visena>
Precedence: bulk

------=_Part_1088_1582712640.1634641683223
Content-Type: multipart/related;
	boundary="----=_Part_1089_704639998.1634641683223"

------=_Part_1089_704639998.1634641683223
Content-Type: multipart/alternative;
	boundary="----=_Part_1090_130129923.1634641683245"

------=_Part_1090_130129923.1634641683245
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable


P=C3=A5 tirsdag 19. oktober 2021 kl. 07:58:46, skrev aditya desai <
admad123@gmail.com <mailto:admad123@gmail.com>>:=20
Sending to a performance group instead of PLPGSQL.


.=20
.=20
Hi,=20
I am running the below query. Table has 21 million records. I get an Out Of=
=20
Memory error after a while.(from both pgadmin and psql). Can someone review=
 DB=20
parameters given below.=20

select t.*,g.column,a.column from=20
gk_staging g, transaction t,account a=20
where=20
g.accountcodeis not null AND=20
g.accountcode::text <> '' AND=20
length(g.accountcode)=3D13 AND=20
g.closeid::text=3Dt.transactionid::text AND=20
subsrting(g.accountcode::text,8)=3Da.mask_code::text=20

Below are system parameters.=20
shared_buffers=3D3GB=20
work_mem=3D2GB=20
effective_cache_size=3D10GB=20
maintenance_work_mem=3D1GB=20
max_connections=3D250=20

I am unable to paste explain plan here due to security concerns.=20

You have not provided schema, explain-output nor the error-message.=20
Without this it's pretty much guess-work...=20


--
 Andreas Joseph Krogh=20
------=_Part_1090_130129923.1634641683245
Content-Type: text/html;charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<div>P=C3=A5 tirsdag 19. oktober 2021 kl. 07:58:46, skrev aditya desai &lt;=
<a href=3D"mailto:admad123@gmail.com">admad123@gmail.com</a>&gt;:</div>

<blockquote style=3D"border-left: 1px solid rgb(204, 204, 204); margin: 0pt=
 0pt 0pt 0.8ex; padding-left: 1ex;">
<div dir=3D"ltr">Sending to a performance group instead of PLPGSQL.<br>
=C2=A0
<div class=3D"gmail_quote">
<div class=3D"gmail_attr" dir=3D"ltr">.</div>

<div class=3D"gmail_attr" dir=3D"ltr">.</div>

<div dir=3D"ltr">Hi,
<div>I am running the below query. Table has 21 million records. I get an O=
ut Of Memory error after a while.(from both pgadmin and psql). Can someone =
review DB parameters given below.</div>

<div>=C2=A0</div>

<div>select t.*,g.column,a.column from</div>

<div>gk_staging g, transaction t,account a</div>

<div>where</div>

<div>g.accountcodeis not null AND</div>

<div>g.accountcode::text &lt;&gt; '' AND</div>

<div>length(g.accountcode)=3D13 AND</div>

<div>g.closeid::text=3Dt.transactionid::text AND</div>

<div>subsrting(g.accountcode::text,8)=3Da.mask_code::text</div>

<div>=C2=A0</div>

<div>Below are system parameters.</div>

<div>shared_buffers=3D3GB</div>

<div>work_mem=3D2GB</div>

<div>effective_cache_size=3D10GB</div>

<div>maintenance_work_mem=3D1GB</div>

<div>max_connections=3D250</div>

<div>=C2=A0</div>

<div>I am unable to paste explain plan here due to security concerns.</div>
</div>
</div>
</div>
</blockquote>

<div>=C2=A0</div>

<div>You have not provided schema, explain-output nor the error-message.</d=
iv>

<div>Without this it's pretty much guess-work...</div>

<div class=3D"visena-email-signature" data-visena-message-id=3D"&lt;VisenaE=
mail.53.2208dc1e4c778c76.17c983b17bb@tc7-visena>">
<div>
<div>=C2=A0</div>

<div>--<br>
Andreas Joseph Krogh</div>
</div>
</div>
------=_Part_1090_130129923.1634641683245--

------=_Part_1089_704639998.1634641683223--

------=_Part_1088_1582712640.1634641683223--