Slow Planning Times

From: Saurabh Sehgal <saurabh(dot)r(dot)s(at)gmail(dot)com>
To: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: Slow Planning Times
Date: 2022-04-07 00:26:59
Message-ID: CAB0Jq2KhM6guH6cPtV+upKQ=SSw6m3oXaQO=XnjqzTnyp+KEyw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

I have the following query:

*explain* (*analyze*, costs, timing) *SELECT* rr.* *FROM* rpc rpc

*INNER* *JOIN* rr rr

*ON* rr.uuid = rpc.rr_id

*INNER* *JOIN* rs rs

*ON* rs.r_id = rpc.r_id

*INNER* *JOIN* *role* r

*ON* r.uuid = rs.r_id

*LEFT* *JOIN* spc spc

*ON* spc.rr_id = rpc.rr_id

*WHERE* rs.s_id = 'caa767b8-8371-43a3-aa11-d1dba1893601'

*and* spc.s_id =
'caa767b8-8371-43a3-aa11-d1dba1893601'

*and* spc.rd_id =
'9f33c45a-90c2-4e05-a42e-048ec1f2b2fa'

*AND* rpc.rd_id =
'9f33c45a-90c2-4e05-a42e-048ec1f2b2fa'

*AND* rpc.c_id =
'9fd29fdc-15fd-40bb-b85d-8cfe99734987'

*and* spc.c_id =
'9fd29fdc-15fd-40bb-b85d-8cfe99734987'

*AND* rr.b_id = 'xyz'

*AND* (('GLOBAL' = ' NO_PROJECT_ID + "' ) *OR* (rr.
p_id = 'GLOBAL'))

*AND* spc.permission_type *IS* *null* *and* spc.
is_active = *true*

*AND* rpc.is_active = *true* *AND* rr.is_active =
*true* *AND* rs.is_active = *true* *AND* r.is_active = *true*

I don't think it is super complex. But when I run explain analyze on this I
get the following:

Planning Time: 578.068 ms
Execution Time: 0.113 ms

This is a huge deviation in planning vs. execution times. The explain plan
looks good since the execution time is < 1ms. It doesn't matter though
since the planning time is high. I don't see anything in the explain
analyze output that tells me why the planning time is high. On average, the
tables being joined have 3 indexes/table. How can I debug this?

Been stuck on this for weeks. Any help is appreciated. Thank you!

Saurabh

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Saurabh Sehgal 2022-04-07 00:40:30 Re: Slow Planning Times
Previous Message Rambabu g 2022-04-04 07:47:59 Re: HIGH IO and Less CPU utilization