From: | Kenny Bachman <kenny(dot)bachman17(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org> |
Subject: | Re: too high planning time |
Date: | 2023-02-02 18:21:54 |
Message-ID: | CAC0w7LKLV5XV=dW-9Y4g2Tezq+aBnSa5Gz6GKrHf_gdf1kG7Gg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
In fact, when I run the query for the second time, it returns very fast
results. But after a while the problem reoccurs.
Not just for these queries, but almost all queries in the database are
slowed down this way.
Is it normal to have a lock on the catalog or system tables? What should I
do when this happens on pg_statistic or other catalogs?
thanks a lot for your comments and help
Kenny
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, 2 Şub 2023 Per, 18:24 tarihinde şunu yazdı:
> Kenny Bachman <kenny(dot)bachman17(at)gmail(dot)com> writes:
> > EXPLAIN ANALYZE select
> > i."DefinitionId",
> > from
> > "T_WF_INSTANCE" i
> > where
> > i."InstanceId" = 10045683193;
>
> > QUERY PLAN
>
> >
> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> > Index Scan using
> > "T_WF_INSTANCE_InstanceId_ApplicationCd_EntityStatusCd_idx" on
> > "T_WF_INSTANCE" i (cost=0.57..2.79 rows=1 width=34) (actual
> > time=2.522..2.522 rows=1 loops=1)
> > Index Cond: ("InstanceId" = '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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Ron | 2023-02-02 18:57:04 | Re: too high planning time |
Previous Message | Holger Jakobs | 2023-02-02 16:42:02 | Re: Postgres Monitoring |