From: Tom Lane <tgl@sss.pgh.pa.us>
To: Siraj G <tosiraj.g@gmail.com>
cc: Adrian Klaver <adrian.klaver@aklaver.com>,
        pgsql-general@lists.postgresql.org
Subject: Re: Need assistance in converting subqueries to joins
In-reply-to: <CAC5iy63y60Q8xucNEAQNRekJooCbrT_eiEzZp+UwjchG3Q50Tg@mail.gmail.com>
References: <CAC5iy60YRzqRSyC=hAZneURdCTAsRPH313jJzK9gF6-nkvwGPA@mail.gmail.com> <2cf838b6-2e49-42a0-8d8c-98649127dd4b@aklaver.com> <CAC5iy63y60Q8xucNEAQNRekJooCbrT_eiEzZp+UwjchG3Q50Tg@mail.gmail.com>
Comments: In-reply-to Siraj G <tosiraj.g@gmail.com>
	message dated "Fri, 20 Sep 2024 09:58:12 +0530"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2359010.1726807685.1@sss.pgh.pa.us>
Date: Fri, 20 Sep 2024 00:48:05 -0400
Message-ID: <2359011.1726807685@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/2359011.1726807685%40sss.pgh.pa.us>
Precedence: bulk

Siraj G <tosiraj.g@gmail.com> writes:
> Please find below the query in the format and its execution plan:

[ blink... ]  I'm not sure what you are using there, but it is
*not* Postgres.  There are assorted entries in the execution
plan that community Postgres has never heard of, such as

> -> Remove duplicate (P0, IS_SEC_FILT) rows using temporary table
> (weedout)  (cost=2085.53 rows=1988) (actual time=0.321..22600.652
> rows=10298 loops=1)

> -> Single-row index lookup on P0 using IS_PROJ_PK
> (IS_PROJ_GUID=T0.IS_PROJ_GUID, IS_REPOSITORY_ID=R0.REP_ID)  (cost=0.63
> rows=1) (actual time=0.000..0.000 rows=1 loops=50)

Maybe this is RDS, or Aurora, or Greenplum, or one of many other
commercial forks of Postgres?  In any case you'd get more on-point
advice from their support forums than from the PG community.
It looks like this is a fork that has installed its own underlying
table engine, meaning that what we know about performance may not
be terribly relevant.

			regards, tom lane