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: Tue, 22 May 2018 13:23:07 -0500
Message-ID: <20180522182307.GA9330@telsasoft.com> (raw)
In-Reply-To: <1526986304012-0.post@n3.nabble.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>
On Tue, May 22, 2018 at 03:51:44AM -0700, pavan95 wrote:
> Please find the output of explain(analyze,buffers) for the whole query in
> the below link.
> Seq Scan on res_users users (cost=750.92..1,836.69 rows=249 width=15) (actual time=3.962..17.544 rows=67 loops=1)
Not sure but would you try creating an index on:
res_users.res_employee_id
> Seq Scan on res_users user1 (cost=0.00..58.03 rows=1,303 width=15) (actual time=0.002..0.002 rows=1 loops=1)
Also the planner's estimate for table:res_users is off by 1300x..so you should
probably vacuum analyze it then recheck. I don't think we know what version
postgres you have, but last week's patch releases include a fix which may be
relevant (reltuples including dead tuples).
Also I don't know the definition of this table or its indices:
tms_workflow_history
..but it looks like an additional or modified index or maybe clustering the
table on existing index might help (active? is_final_approver?)
Or maybe this should be 3 separate indices rather than composite index?
Perhaps some of those could be BRIN indices, depending on postgres version
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: <20180522182307.GA9330@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