Date: Thu, 24 May 2018 01:06:40 -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: <20180524060639.GQ30060@telsasoft.com>
References: <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>
 <20180523200132.GL30060@telsasoft.com>
 <1527139242201-0.post@n3.nabble.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <1527139242201-0.post@n3.nabble.com>
User-Agent: Mutt/1.5.23 (2014-03-12)
Precedence: bulk

On Wed, May 23, 2018 at 10:20:42PM -0700, pavan95 wrote:
> Hi Justin,
> 
> Please find the below explain plan link.
> 
> Link:  https://explain.depesz.com/s/owE <http://>  

That's explain analyze but explain(analyze,buffers) is better.

Is this on a completely different server than the previous plans ?

This rowcount misestimate appears to be a significant part of the problem:
 Merge Join (cost=228.77..992.11 ROWS=20 width=22) (actual time=4.353..12.439 ROWS=343 loops=1)
    Merge Cond: (history_2.timesheet_id = header_2.id)

You could look at the available stats for that table's column in pg_stats.
Is there an "most common values" list?
Maybe you need to ALTER TABLE .. SET STATISTICS 999 (or some increased value)
and re-analyze ?

You can see these are also taking large component of the query time:

 Bitmap Index Scan on ts_detail_release_no_idx (cost=0.00..33.86 rows=1,259 width=0) (actual time=0.304..0.304 rows=1,331 LOOPS=327)
    Index Cond: ((release_no)::text = 'paid_time_off'::text)
...
 Bitmap Index Scan on ts_detail_release_no_idx (cost=0.00..33.86 rows=1,259 width=0) (actual time=0.304..0.304 rows=1,331 LOOPS=343)
    Index Cond: ((release_no)::text = 'paid_time_off'::text)

I wonder whether it would help to
CREATE INDEX ON tms_timesheet_details(timesheet_header_id) WHERE
((release_no)::text = 'paid_time_off'::text);

In addition to the other settings I asked about, it might be interesting to
SHOW effective_io_concurrency;

You're at the point where I can't reasonably contribute much more.

Justin