Re: Slow Planning Times

On Wed, Apr 6, 2022 at 5:27 PM Saurabh Sehgal <saurabh(dot)r(dot)s(at)gmail(dot)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!
>
>
The fundamental issue here is that you have basically 12 conditions across
5 tables that need to be evaluated to determine which one of the 1,680
possible join orders is the most efficient. The fact that you have 5
is_active checks and 3 pairs of matching UUID checks seems odd and if you
could reduce those 11 to 4 I suspect you'd get a better planning time.
Though it also may produce an inferior plan...thus consider the following
option:

Assuming the ideal plan shape for your data doesn't change you can read the
following and basically tell the planner to stop trying so hard and just
trust the join order that exists in the query.

https://www.postgresql.org/docs/current/explicit-joins.html

Lastly, if you can leverage prepared statements you can at least amortize
the cost (depending on whether a generic plan performs sufficiently
quickly).

I'll admit I'm no expert at this. I'd probably just follow the
join_collapse_limit advice and move on if it works. Maybe adding a
periodic check to see if anything has changed.
David J.