Date: Wed, 23 May 2018 15:01:32 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: pavan95 <pavan.postgresdba@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Help me in reducing the CPU cost for the high cost query below,
 as it is hitting production seriously!!
Message-ID: <20180523200132.GL30060@telsasoft.com>
References: <1526883356469-0.post@n3.nabble.com>
 <1526917693149-0.post@n3.nabble.com>
 <1526985179479-0.post@n3.nabble.com>
 <20180522103906.GF30060@telsasoft.com>
 <1526986304012-0.post@n3.nabble.com>
 <20180522182307.GA9330@telsasoft.com>
 <CACh9nsb_voDr57vRrwvhFsCGuT1SzRMWvbQz1wnU5miK1sdWbQ@mail.gmail.com>
 <1527058866609-0.post@n3.nabble.com>
 <20180523134322.GK30060@telsasoft.com>
 <1527084198212-0.post@n3.nabble.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <1527084198212-0.post@n3.nabble.com>
User-Agent: Mutt/1.5.23 (2014-03-12)
Precedence: bulk

On Wed, May 23, 2018 at 07:03:18AM -0700, pavan95 wrote:
> Please find the explain plan which got increased again vastly. Is this
> because of the increase in rows?
> 
> Link :  https://explain.depesz.com/s/Ifr <http://>  

That's explain without "analyze", so not very useful.

There's handful of questions:

On Wed, May 23, 2018 at 08:43:22AM -0500, Justin Pryzby wrote:
>  - postgres parameters: what are shared_buffers, work_mem, effective_cache_size ?
>    + https://wiki.postgresql.org/wiki/Server_Configuration
>  - are there other DBs/applications running on the server/VM ?
>  - kernel tuning (readahead, VM parameters, write cache, scheduler, THP, etc)
>  - server hardware (what OS? storage? RAM? filesystem?)
>  - how does the storage perform outside of postgres?
>    + something like this: /usr/sbin/bonnie++ -f -n0 -x4 -d /var/lib/pgsql

Justin