Re: Query out of memory

pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed

From: aditya desai <admad123@gmail.com>
To: Geri Wright <geri.w123@gmail.com>
To: pgsql-sql <pgsql-sql@lists.postgresql.org>
Subject: Re: Query out of memory
Date: Tue, 19 Oct 2021 09:37:48 +0530
Message-ID: <CAN0SRDEV9+6Pee-ZqjkSFPU9NsHDSwVRCoQW+=XiCpXtLCKLqw@mail.gmail.com> (raw)
In-Reply-To: <CAKSgRY5ORhh2bR4ANLq0ub0nmLD-AJEkEt9=Y2mKNCp8AYOPog@mail.gmail.com>
References: <CAN0SRDHDte88-v4=uhxH1FWU0nt3xBJGTe5hTGj4KvKTtVRDtA@mail.gmail.com>
	<CAKSgRY5ORhh2bR4ANLq0ub0nmLD-AJEkEt9=Y2mKNCp8AYOPog@mail.gmail.com>

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.
>>
>>

view thread (14+ messages)  latest in thread

Message-ID: <CAN0SRDEV9+6Pee-ZqjkSFPU9NsHDSwVRCoQW+=XiCpXtLCKLqw@mail.gmail.com>
Permalink:  ../CAN0SRDEV9+6Pee-ZqjkSFPU9NsHDSwVRCoQW+=XiCpXtLCKLqw@mail.gmail.com/
Also on:    postgresql.org/message-id/CAN0SRDEV9+6Pee-ZqjkSFPU9NsHDSwVRCoQW+=XiCpXtLCKLqw@mail.gmail.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: admad123@gmail.com, geri.w123@gmail.com, pgsql-sql@lists.postgresql.org
  Subject: Re: Query out of memory
  In-Reply-To: <CAN0SRDEV9+6Pee-ZqjkSFPU9NsHDSwVRCoQW+=XiCpXtLCKLqw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox