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!!
Date: Thu, 24 May 2018 01:06:40 -0500
Message-ID: <20180524060639.GQ30060@telsasoft.com> (raw)
In-Reply-To: <1527139242201-0.post@n3.nabble.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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pryzby@telsasoft.com, pavan.postgresdba@gmail.com
Subject: Re: Help me in reducing the CPU cost for the high cost query below, as it is hitting production seriously!!
In-Reply-To: <20180524060639.GQ30060@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox