Re: Slow Planning Times

[Saurabh Sehgal <saurabh.r.s@xxxxxxxxx>]

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.r.s@xxxxxxxxx> 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.r.s@xxxxxxxxx
Phone:     425-269-1324
LinkedIn: https://www.linkedin.com/in/saurabh-s-4367a31/