MIME-Version: 1.0
References: 
 <CAN0SRDHDte88-v4=uhxH1FWU0nt3xBJGTe5hTGj4KvKTtVRDtA@mail.gmail.com>
 <CAKSgRY5ORhh2bR4ANLq0ub0nmLD-AJEkEt9=Y2mKNCp8AYOPog@mail.gmail.com>
In-Reply-To: 
 <CAKSgRY5ORhh2bR4ANLq0ub0nmLD-AJEkEt9=Y2mKNCp8AYOPog@mail.gmail.com>
From: aditya desai <admad123@gmail.com>
Date: Tue, 19 Oct 2021 09:37:48 +0530
Message-ID: 
 <CAN0SRDEV9+6Pee-ZqjkSFPU9NsHDSwVRCoQW+=XiCpXtLCKLqw@mail.gmail.com>
Subject: Re: Query out of memory
To: Geri Wright <geri.w123@gmail.com>,
 pgsql-sql <pgsql-sql@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000009f4ec705ceacceda"
Archived-At: 
 <https://www.postgresql.org/message-id/CAN0SRDEV9%2B6Pee-ZqjkSFPU9NsHDSwVRCoQW%2B%3DXiCpXtLCKLqw%40mail.gmail.com>
Precedence: bulk

--0000000000009f4ec705ceacceda
Content-Type: text/plain; charset="UTF-8"

This is how I received a query from the App Team. They have migrated from
Oracle to Postgres. I see in Oracle where it is working fine and also has
the same joins. Some of the developers increased the work_mem. I will try
and tone it down. Will get back to you. Thanks.

On Tue, Oct 19, 2021 at 1:28 AM Geri Wright <geri.w123@gmail.com> wrote:

> Hi,
> It looks like you have Cartesian joins in the query.   Try updating your
> where clause to include
>
> And g.columnname = t.columnname
> And t.columnname2 = a.columnname2
>
> On Mon, Oct 18, 2021, 12:43 PM aditya desai <admad123@gmail.com> wrote:
>
>> Hi,
>> I am running the below query. Table has 21 million records. I get an Out
>> Of Memory error after a while.(from both pgadmin and psql). Can someone
>> review DB parameters given below.
>>
>> select t.*,g.column,a.column from
>> gk_staging g, transaction t,account a
>> where
>> g.accountcodeis not null AND
>> g.accountcode::text <> '' AND
>> length(g.accountcode)=13 AND
>> g.closeid::text=t.transactionid::text AND
>> subsrting(g.accountcode::text,8)=a.mask_code::text
>>
>> Below are system parameters.
>> shared_buffers=3GB
>> work_mem=2GB
>> effective_cache_size=10GB
>> maintenance_work_mem=1GB
>> max_connections=250
>>
>> I am unable to paste explain plan here due to security concerns.
>>
>> Regards,
>> Aditya.
>>
>>

--0000000000009f4ec705ceacceda
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">This is how I received a query from the App Team. They hav=
e migrated from Oracle to Postgres. I see in Oracle where it is working fin=
e and also has the same joins. Some of the developers increased the work_me=
m. I will try and tone it down. Will get back to you. Thanks.</div><br><div=
 class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Tue, Oct 19=
, 2021 at 1:28 AM Geri Wright &lt;<a href=3D"mailto:geri.w123@gmail.com">ge=
ri.w123@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote"=
 style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);p=
adding-left:1ex"><div dir=3D"auto">Hi,<div dir=3D"auto">It looks like you h=
ave Cartesian joins in the query.=C2=A0 =C2=A0Try updating your where claus=
e to include<div dir=3D"auto"><br></div><div dir=3D"auto">And g.columnname =
=3D t.columnname=C2=A0</div><div dir=3D"auto">And t.columnname2 =3D a.colum=
nname2</div></div></div><br><div class=3D"gmail_quote"><div dir=3D"ltr" cla=
ss=3D"gmail_attr">On Mon, Oct 18, 2021, 12:43 PM aditya desai &lt;<a href=
=3D"mailto:admad123@gmail.com" target=3D"_blank">admad123@gmail.com</a>&gt;=
 wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px =
0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=
=3D"ltr">Hi,<br><div>I am running the below query. Table has 21 million rec=
ords. I get an Out Of Memory error after a while.(from both pgadmin and psq=
l). Can someone review DB parameters given below.</div><div><br></div><div>=
select t.*,g.column,a.column from</div><div>gk_staging g, transaction t,acc=
ount a</div><div>where</div><div>g.accountcodeis not null AND</div><div>g.a=
ccountcode::text &lt;&gt; &#39;&#39; AND</div><div>length(g.accountcode)=3D=
13 AND</div><div>g.closeid::text=3Dt.transactionid::text AND</div><div>subs=
rting(g.accountcode::text,8)=3Da.mask_code::text</div><div><br></div><div>B=
elow are system parameters.</div><div>shared_buffers=3D3GB</div><div>work_m=
em=3D2GB</div><div>effective_cache_size=3D10GB</div><div>maintenance_work_m=
em=3D1GB</div><div>max_connections=3D250</div><div><br></div><div>I am unab=
le to paste explain plan here due to security concerns.</div><div><br></div=
><div>Regards,</div><div>Aditya.</div><div><br></div></div>
</blockquote></div>
</blockquote></div>

--0000000000009f4ec705ceacceda--