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: Wed, 23 May 2018 08:43:22 -0500
Message-ID: <20180523134322.GK30060@telsasoft.com> (raw)
In-Reply-To: <1527058866609-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>
<20180522182307.GA9330@telsasoft.com>
<CACh9nsb_voDr57vRrwvhFsCGuT1SzRMWvbQz1wnU5miK1sdWbQ@mail.gmail.com>
<1527058866609-0.post@n3.nabble.com>
On Wed, May 23, 2018 at 12:01:06AM -0700, pavan95 wrote:
> As said, created index on the res_users.res_employee_id and the below link
> is the explain plan result.
>
> Link: https://explain.depesz.com/s/hoct
>
> And the cost of Previous query is 92,129 and the cost of current modified
> query after creating the above said index is 91,462. But good thing is we
Forget the cost - that's postgres *model* of the combined IO+CPU.
If the model is off, that's may cause bad plans and could be looked into
further.
In any case, that index cut your runtime from 75sec to 60sec (in spite of the
modelled cost).
It looks like you resolved the bad estimate on the users table?
> 2. tms_workflow_history:
> Indexes:
> "tms_workflow_history_pkey" PRIMARY KEY, btree (id)
> "curract_state_isfinal_app_idx" btree (current_activity, state, is_final_approver)
> "timesheet_id_group_id_active_idx" btree (timesheet_id, group_id, active)
> "tms_wkf_his_active_is_final_approveridx" btree (active, is_final_approver)
> "tms_wkf_his_group_id_idx" btree (group_id)
> "tms_wkf_his_timesheet_id_idx" btree (timesheet_id)
> "tms_wkf_hist_current_activity_idx" btree (current_activity)
> "tms_wkf_hist_state_idx" btree (state)
> "wsr_id_idx" btree (wsr_id)
How big is the table ? And curract_state_isfinal_app_idx ?
Have these been reindexed (or pg_repacked) recently?
It seems to me that the remaining query optimization is to improve this:
> Bitmap Heap Scan on tms_workflow_history history (cost=193.19..1,090.50 rows=6,041 width=12) (actual time=3.692..15.714 rows=11,351 loops=1)
I think you could consider clustering (or repacking) the table on
curract_state_isfinal_app_idx (but you'll have to judge if that's okay and
won't negatively affect other queries).
But, what's your target runtime ? Improvements here could cut at most 15sec
off the total 60sec. If you're hoping to save more than that, you'll need to
(also) look further than the query:
- 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
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: <20180523134322.GK30060@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