Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1srUvr-0027y7-Pd for pgsql-general@arkaria.postgresql.org; Fri, 20 Sep 2024 04:07:48 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1srUvp-00E9FE-Rc for pgsql-general@arkaria.postgresql.org; Fri, 20 Sep 2024 04:07:47 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1srUvp-00E9F5-Gt for pgsql-general@lists.postgresql.org; Fri, 20 Sep 2024 04:07:46 +0000 Received: from mail-ed1-x52f.google.com ([2a00:1450:4864:20::52f]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1srUvn-0007d4-D0 for pgsql-general@lists.postgresql.org; Fri, 20 Sep 2024 04:07:46 +0000 Received: by mail-ed1-x52f.google.com with SMTP id 4fb4d7f45d1cf-5c466170bfdso154973a12.1 for ; Thu, 19 Sep 2024 21:07:44 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1726805263; x=1727410063; darn=lists.postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=Bsyp02shEIr+J1NvBH5NmWvM61qHZVAcgh4REsB/peU=; b=IJhsCvSiGGtBNHysNVmNt+fd5yxlv8WU3dWXYOUhvb/gc57cf2OHKnZ4cZEq8oDxb4 tQmauRucDvpKkdgdgNY3zhtq305QmnndA9/Z0WTnApHe4pblz1JSq6HNYBJjld5RmzwX HzsLSiaWIE1BaRZY+RfNeDGuY69u4yIIn7f3k4GkKb3mcWQd2QJ3vS+4d1Drxi6lUa3H oo+Zv+IefvjImWsmzZWH8SW6IZV7GIrebi8DNpNxbIzGlElO2lpstTi+w2M8kjDd94R5 uxXUENnAHhZvUY5zveOm/0eP2q5MsKVRfnIzyt03JnQK7UYoxTeNgUZjd43ArE1zsY8p DUfg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1726805263; x=1727410063; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=Bsyp02shEIr+J1NvBH5NmWvM61qHZVAcgh4REsB/peU=; b=D/MLkY0Unt0J5lPQFpvlUP+DTBBN7fF6n9tcic6jSnBHiPbps7FCTuYuHb0Ddsj11W Fsd/4mpD/bpLGw4K/XlytMFwZGjxEgcdyJ8DSsu+1Fl/2Nn4/+UGIGwyv8iMZNgoL6pD 2Z9B8sFwLrX55PaMYGV2PmXfHy6PcY2Smr8xTAVNbYwMvECMzhoR7dJEglHvVbyksU03 qdGs/WS5PynWrjn6uj44hFVcYUBADHGodn1PHDcorun3nzi/2Yc98dF1Jb/0P0MqR2IK c1j2HqWkwl+A4MyjrzeT03N371JneUvJMnaIzNu1aiZscp76kSQAnI53oL43eCmTivkM GPZw== X-Gm-Message-State: AOJu0YzJDMncz4iMd48UnJ3PSY/lovV3d1sloJqW1tAZbOzz3+0U6jOS SDp7pFe+wd6gkjKV1us8XxBiuxUzjE8ZGKcETKjcc2r8BTlJRvMPvsiKiRqN6yt0Igz10YtJ61f vbYo+ZOUWuHhBXwVhv1N4T1nnX56qBk6O X-Google-Smtp-Source: AGHT+IHBMwQpThyJi8AEtHQQ+FWaM07qyC6YmfQNGYVGEEfYpphRcyicU9OEofAIkubV8eQLf3CQsL8dJ12s4Mw0/6s= X-Received: by 2002:a50:c943:0:b0:5c4:583a:e6de with SMTP id 4fb4d7f45d1cf-5c464a432c2mr1089662a12.20.1726805263243; Thu, 19 Sep 2024 21:07:43 -0700 (PDT) MIME-Version: 1.0 From: Siraj G Date: Fri, 20 Sep 2024 09:37:31 +0530 Message-ID: Subject: Need assistance in converting subqueries to joins To: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000004fe4a10622852ec6" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000004fe4a10622852ec6 Content-Type: text/plain; charset="UTF-8" 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): 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 --0000000000004fe4a10622852ec6 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hello Tech gents!

I am sorry if I am as= king the wrong=C2=A0question to this group, but wanted assistance in conver= ting a query replacing subqueries with joins.=C2=A0

Please find the query below (whose cost is very high):

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_G= UID =3D P0.IS_PROJ_GUID AND P0.IS_PROJ_ID =3D 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_M= D R0 WHERE T0.IS_REPOSITORY_GUID =3D R0.REP_GUID AND T0.IS_PROJ_GUID =3D P0= .IS_PROJ_GUID AND P0.IS_REPOSITORY_ID =3D R0.REP_ID);

Regards
Siraj
--0000000000004fe4a10622852ec6--