Feedback-ID: i76984098:Fastmail
Message-ID: <2cf838b6-2e49-42a0-8d8c-98649127dd4b@aklaver.com>
Date: Thu, 19 Sep 2024 21:19:16 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Need assistance in converting subqueries to joins
To: Siraj G <tosiraj.g@gmail.com>, pgsql-general@lists.postgresql.org
References: <CAC5iy60YRzqRSyC=hAZneURdCTAsRPH313jJzK9gF6-nkvwGPA@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAC5iy60YRzqRSyC=hAZneURdCTAsRPH313jJzK9gF6-nkvwGPA@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/2cf838b6-2e49-42a0-8d8c-98649127dd4b%40aklaver.com>
Precedence: bulk

On 9/19/24 21:07, Siraj G wrote:
> Hello Tech gents!
> 
> I am sorry if I am asking the wrong question to this group, but wanted 
> assistance in converting a query replacing subqueries with joins.
> 
> Please find the query below (whose cost is very high):

Add the output of the EXPLAIN ANALYZE for the query.

> 
> select em_exists_id from IS_SEC_FILT WHERE (IS_SEC_FILT_GUID) NOT IN 
> (SELECT IS_OBJ_GUID FROM TMP_IS_SEC_FILT T0, IS_PROJ P0 WHERE 
> T0.IS_PROJ_GUID = P0.IS_PROJ_GUID AND P0.IS_PROJ_ID = 
> IS_SEC_FILT.IS_PROJ_ID) AND (IS_PROJ_ID) IN (SELECT IS_PROJ_ID FROM 
> IS_PROJ P0, TMP_IS_SEC_FILT T0, EM_MD R0 WHERE T0.IS_REPOSITORY_GUID = 
> R0.REP_GUID AND T0.IS_PROJ_GUID = P0.IS_PROJ_GUID AND 
> P0.IS_REPOSITORY_ID = R0.REP_ID);

For future reference formatting the query here:

https://sqlformat.darold.net/

helps get it into a form that is easier to follow:

SELECT
     em_exists_id
FROM
     IS_SEC_FILT
WHERE (IS_SEC_FILT_GUID)
NOT IN (
     SELECT
         IS_OBJ_GUID
     FROM
         TMP_IS_SEC_FILT T0,
         IS_PROJ P0
     WHERE
         T0.IS_PROJ_GUID = P0.IS_PROJ_GUID
         AND P0.IS_PROJ_ID = IS_SEC_FILT.IS_PROJ_ID)
AND (IS_PROJ_ID) IN (
     SELECT
         IS_PROJ_ID
     FROM
         IS_PROJ P0,
         TMP_IS_SEC_FILT T0,
         EM_MD R0
     WHERE
         T0.IS_REPOSITORY_GUID = R0.REP_GUID
         AND T0.IS_PROJ_GUID = P0.IS_PROJ_GUID
         AND P0.IS_REPOSITORY_ID = R0.REP_ID);


> 
> Regards
> Siraj

-- 
Adrian Klaver
adrian.klaver@aklaver.com