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