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

From: Pavan Teja <pavan(dot)postgresdba(at)gmail(dot)com>
To: Justin Pryzby <pryzby(at)telsasoft(dot)com>
Cc: postgres performance list <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Help me in reducing the CPU cost for the high cost query below, as it is hitting production seriously!!
Date: 2018-05-22 18:25:39
Message-ID: CACh9nsb_voDr57vRrwvhFsCGuT1SzRMWvbQz1wnU5miK1sdWbQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Thanks a lot! I will have a look

On Tue, May 22, 2018, 11:53 PM Justin Pryzby <pryzby(at)telsasoft(dot)com> wrote:

> 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
>

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Sand Stone 2018-05-23 04:10:02 Re: dsa_allocate() faliure
Previous Message Justin Pryzby 2018-05-22 18:23:07 Re: Help me in reducing the CPU cost for the high cost query below, as it is hitting production seriously!!