Re: Help me in reducing the CPU cost for the high cost query below,as it is hitting production seriously!!

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,331LOOPS=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,331LOOPS=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