Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps
 (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1oAsHt-0008Gm-6c
	for pgsql-performance@arkaria.postgresql.org; Mon, 11 Jul 2022 12:13:17 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.92)
	(envelope-from <pgsql-performance-owner+archive@lists.postgresql.org>)
	id 1oAsHs-0005z7-4K
	for pgsql-performance@arkaria.postgresql.org; Mon, 11 Jul 2022 12:13:16 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <pryzby@telsasoft.com>)
	id 1oAsHr-0005yx-N5
	for pgsql-performance@lists.postgresql.org; Mon, 11 Jul 2022 12:13:15 +0000
Received: from mail-io1-xd34.google.com ([2607:f8b0:4864:20::d34])
	by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128)
	(Exim 4.92)
	(envelope-from <pryzby@telsasoft.com>)
	id 1oAsHn-0005Tr-EQ
	for pgsql-performance@lists.postgresql.org; Mon, 11 Jul 2022 12:13:15 +0000
Received: by mail-io1-xd34.google.com with SMTP id z81so4740009iof.0
        for <pgsql-performance@lists.postgresql.org>;
 Mon, 11 Jul 2022 05:13:10 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
        d=telsasoft-com.20210112.gappssmtp.com; s=20210112;
        h=date:from:to:cc:subject:message-id:references:mime-version
         :content-disposition:in-reply-to:user-agent;
        bh=EUPUSLWK8LTkj0sYpeLlU2xu3wdVJA0UXI9A+tbyTkQ=;
        b=GX1AJhNSCV/inEZgxfhBdU1Ge5eAuPlbHYxdwcj8NZJoCmld+1OzzO0lAeIzl9r7xH
         DOL3R3ceN1OFkLL2gS5GDfNW6+fkNCvs0kJZYAEGhyI6BDSTUz95Wkt/GicYy3b/kKcm
         k/b3EK2xFEd1Z9NjdZYugOT/hZsZ/ZKnxVqqdnYvssbXrGvpe5sa3vPGjDN6C1/GY/on
         J7uBSxaMBN9Z1cIH2t1mQCF6joJywfLK1s4hdNUcmiuZ4dHXAA1O/GxN9YzpJnVswTnk
         8LjMoFs2ncn3svsWWPZ1mBGALHewk7vfWDttCV15mXe4VE2Qg4ActH25Tc7Wx6VCebuJ
         ynUA==
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
        d=1e100.net; s=20210112;
        h=x-gm-message-state:date:from:to:cc:subject:message-id:references
         :mime-version:content-disposition:in-reply-to:user-agent;
        bh=EUPUSLWK8LTkj0sYpeLlU2xu3wdVJA0UXI9A+tbyTkQ=;
        b=2GrHFP50JNOA4iJ+ZsCV722hVT2XBAQL+o33PGQ1a+R5NcCK4doXmDrQguee93NXH9
         nR7NIpI3+gscSaPkbtcwl+COTD33iEfrqekbaPsV5602rSj0KBmD7YnKSJ2YWeKeTa5W
         pwoQG9pSOCmaUUTjerxrsHSNZMX/T/FyDuBHAROjEDSw4qh/qvC66x7JkXfZqtROY6Pz
         XZAfQc2LBFH0v6WdJeDz+pIYPMAU0dFU86syR+TSxR74305cn9PwfcCgHc3n7ji9fW4/
         jy3jo7UcOnCNZE8ZXx2D5jsDIQw82WZTOIhwLqEfMkR7yYbejhVuAqTysk6ZN0h0S8LR
         NnUg==
X-Gm-Message-State: AJIora/0WW91qY79sVCVuR6aL3ITeqykcQZ9MF90rpMuAcQtH6YwL2rC
	wBqzEFG1QmrP+R0CrobgVVHFUw==
X-Google-Smtp-Source: 
 AGRyM1sFOgMP8W1FsAyTMZ9ED24TxOhFOVf09Tds0yrTPIb+FAGBcVtSYJx/nVWvk+O5yIVHs2BOLQ==
X-Received: by 2002:a05:6638:3385:b0:339:ea59:a31f with SMTP id
 h5-20020a056638338500b00339ea59a31fmr9404750jav.55.1657541589374;
        Mon, 11 Jul 2022 05:13:09 -0700 (PDT)
Received: from pryzbyj.telsasoft (charmander.telsasoft.com. [50.244.222.1])
        by smtp.gmail.com with ESMTPSA id
 g12-20020a056602150c00b0067276ff71b4sm3521521iow.44.2022.07.11.05.13.09
        (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128);
        Mon, 11 Jul 2022 05:13:09 -0700 (PDT)
Received: by pryzbyj.telsasoft (Postfix, from userid 1000)
	id 62819801141; Mon, 11 Jul 2022 07:13:08 -0500 (CDT)
Date: Mon, 11 Jul 2022 07:13:08 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: aditya desai <admad123@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Oracle_FDW table performance issue
Message-ID: <20220711121308.GS13040@telsasoft.com>
References: 
 <CAN0SRDHGDsW0A1cyN=4x9sJNLRbRMP27TfvGbHDikcg2ZBNB9A@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAN0SRDHGDsW0A1cyN=4x9sJNLRbRMP27TfvGbHDikcg2ZBNB9A@mail.gmail.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
List-Id: <pgsql-performance.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-performance@lists.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-performance>
Archived-At: 
 <https://www.postgresql.org/message-id/20220711121308.GS13040%40telsasoft.com>
Precedence: bulk

On Mon, Jul 11, 2022 at 05:38:34PM +0530, aditya desai wrote:
> Hi,
> I have one Oracle fdw table which is giving performance issue when joined
> local temp table gives performance issue.
> 
> select * from oracle_fdw_table where transaction_id in ( select
> transaction_id from temp_table) ---- 54 seconds. Seeing HASH SEMI JOIN  in
> EXPLAIN PLAN. temp_table has only 74 records.

You'd have to share the plan

https://wiki.postgresql.org/wiki/Slow_Query_Questions

Do the tables have updated stats ?