From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kenny Bachman <kenny.bachman17@gmail.com>
cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: too high planning time
In-reply-to: 
 <CAC0w7LKjDd16T80CYejmMrht1Ddjcf1P8jg1POdH=RyRxCHLXA@mail.gmail.com>
References: 
 <CAC0w7LKjDd16T80CYejmMrht1Ddjcf1P8jg1POdH=RyRxCHLXA@mail.gmail.com>
Comments: In-reply-to Kenny Bachman <kenny.bachman17@gmail.com>
	message dated "Thu, 02 Feb 2023 12:54:34 +0300"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1536294.1675351465.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Thu, 02 Feb 2023 10:24:25 -0500
Message-ID: <1536295.1675351465@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1536295.1675351465%40sss.pgh.pa.us>
Precedence: bulk

Kenny Bachman <kenny.bachman17@gmail.com> writes:
> EXPLAIN ANALYZE select
>     i."DefinitionId",
> from
>     "T_WF_INSTANCE" i
> where
>     i."InstanceId" =3D 10045683193;

>        QUERY PLAN

> ------------------------------------------------------------------------=
--------------------------------------------------------------------------=
------------------------------
>  Index Scan using
> "T_WF_INSTANCE_InstanceId_ApplicationCd_EntityStatusCd_idx" on
> "T_WF_INSTANCE" i  (cost=3D0.57..2.79 rows=3D1 width=3D34) (actual
> time=3D2.522..2.522 rows=3D1 loops=3D1)
>    Index Cond: ("InstanceId" =3D '10045683193'::bigint)

> * Planning Time: 8460.446 ms Execution Time: 2.616 ms*
> (4 rows)

It's hard to believe that such a simple query could take that
long to plan.  What I'm wondering is if the planner got blocked
on some other session's exclusive lock.  Not a lock on
"T_WF_INSTANCE" itself, because we'd have got that lock during
parsing before the "Planning Time" measurement starts.  But
there's going to be a physical access to the table's index
to determine its tree height, so an ex-lock on the index could
explain this.  Or an ex-lock on catalogs, particularly pg_statistic.
What else is going on in your database when this happens?

			regards, tom lane