To clarify - I have run "vaccum full" and "vacuum analyze" on every single
table involved in the query and the planning times are still around the
same and were not impacted.
On Wed, Apr 6, 2022 at 5:26 PM Saurabh Sehgal <saurabh....@gmail.com> wrote:
>
> 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
>
--
Saurabh Sehgal
E-mail: saurabh....@gmail.com
Phone: 425-269-1324
LinkedIn: https://www.linkedin.com/in/saurabh-s-4367a31/
