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-admin-owner+archive@lists.postgresql.org>)
	id 1kyIdJ-0000ET-Bq
	for pgsql-admin@arkaria.postgresql.org; Sat, 09 Jan 2021 18:06:37 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.92)
	(envelope-from <pgsql-admin-owner+archive@lists.postgresql.org>)
	id 1kyIdI-00007f-4d
	for pgsql-admin@arkaria.postgresql.org; Sat, 09 Jan 2021 18:06:36 +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 <bruce@momjian.us>)
	id 1kyIdH-00007X-TT
	for pgsql-admin@lists.postgresql.org; Sat, 09 Jan 2021 18:06:35 +0000
Received: from momjian.us ([72.94.173.45])
	by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <bruce@momjian.us>)
	id 1kyIdB-0005D6-FT
	for pgsql-admin@postgresql.org; Sat, 09 Jan 2021 18:06:35 +0000
Received: from bruce by momjian.us with local (Exim 4.92)
	(envelope-from <bruce@momjian.us>)
	id 1kyId9-0002x7-Fl; Sat, 09 Jan 2021 13:06:27 -0500
Date: Sat, 9 Jan 2021 13:06:27 -0500
From: Bruce Momjian <bruce@momjian.us>
To: dbatoCloud Solution <dbatocloud17@gmail.com>
Cc: Bodo Greif <bodo@me.com>, pgsql-admin@postgresql.org
Subject: Re: I need to complex Query - need big support
Message-ID: <20210109180627.GA19575@momjian.us>
References: 
 <CAEz7P_se27C1JSha5oFth89ad68o5VKzf7O0cNyquL2+UV5PKg@mail.gmail.com>
 <FB61D954-930E-457C-AC9A-59A69B3FBABB@me.com>
 <CAEz7P_ts3OmtcBjWuwBWj+rMxYfr75tQ0MJ+NUTBtWzkuv0T0w@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CAEz7P_ts3OmtcBjWuwBWj+rMxYfr75tQ0MJ+NUTBtWzkuv0T0w@mail.gmail.com>
User-Agent: Mutt/1.10.1 (2018-07-13)
List-Id: <pgsql-admin.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-admin@lists.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-admin>
Precedence: bulk

On Sat, Jan  9, 2021 at 11:30:12PM +0530, dbatoCloud Solution wrote:
> Hi Bodo,
> 
> I have resolved this complex query by doing small changes. What I observed the
> optimizer choosing nested loop join and the storage type is in AWS EBS type. So
> I decided to make random_page_cost=0 also I collected stats + vacuumed as I
> said before plus now the query performing very good and I made it down from 23
> minutes into 3 minutes and 15 sec. 

We usually recommend random_page_cost=1.1 for storage where the random
page cost is similar to the sequential cost (which is 1.0).

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee