Planning time is high in Postgres 11.5 Compared with Postgres 10.11
| От | avinash varma |
|---|---|
| Тема | Planning time is high in Postgres 11.5 Compared with Postgres 10.11 |
| Дата | |
| Msg-id | CADaHE9H6kW7h2nseztsuti56_t4bEhtV=u=nXY=gnYsiLU1-oA@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: Planning time is high in Postgres 11.5 Compared with Postgres10.11
(Heikki Linnakangas <hlinnaka@iki.fi>)
|
| Список | pgsql-bugs |
Hi Team,
We are planning upgrade our applications to Postgresql11.5 from Postgresql10.11.
We have performed load test on 11.5 and observed high cpu utilization in db server when compared with 10.11. On further investigation we observed that below query is taking high planning time(highlighted in yellow) in 11.5 and higher versions.
Also please note that below kind of query will executes million times in our regular activities. So which might creating high CPU issue.
Can you please help in resolving this issue. Also please let us know fix if you have already for this.
Notes:
1) All configuration parameters are identical in both 10.11 and 11.5(postgresql.conf is same).
2) Data is same in both versions.
3) Hardware of both versions are same.
4) we have executed explain analyze multiple times on the same session.
5) We are seeing this issue after performing vacuum full analyze after db restore to postgresql 11.5
Below is the test case which we performed to reproduce the issue.
Kindly let us know if you need any other information.
(
id bigint NOT NULL,
childid bigint NOT NULL
)
WITH (
OIDS = FALSE
)
TABLESPACE "PostDB";
CREATE INDEX child_index1 ON public.child USING btree (childid) TABLESPACE "PostDB";
CREATE UNIQUE INDEX child_primary ON public.child USING btree(id, childid) TABLESPACE "PostDB";
CREATE TABLE public.core
(
groupid bigint NOT NULL,
rightid bigint NOT NULL
)
WITH (
OIDS = FALSE
)
TABLESPACE "PostDB";
CREATE UNIQUE INDEX core_idx1 ON public.core USING btree (groupid, rightid) TABLESPACE "PostDB";
vacuum analyze child;
vacuum analyze core;
Postgres 10.5
=================
PostDB11=# explain analyze SELECT --DISTINCT kc.childid AS rlid,
PostDB11-# kc.id AS rlrightid--,
PostDB11-# -- 0 AS rlproxytype
PostDB11-# FROM child kc
PostDB11-# WHERE NOT (EXISTS ( SELECT 1
PostDB11(# FROM core
PostDB11(# WHERE kc.id = core.groupid));
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Hash Anti Join (cost=51.62..116.08 rows=925 width=8) (actual time=0.007..0.007 rows=0 loops=1)
Hash Cond: (kc.id = core.groupid)
-> Seq Scan on child kc (cost=0.00..28.50 rows=1850 width=8) (actual time=0.006..0.006 rows=0 loops=1)
-> Hash (cost=28.50..28.50 rows=1850 width=8) (never executed)
-> Seq Scan on core (cost=0.00..28.50 rows=1850 width=8) (never executed)
Planning time: 0.183 ms
Execution time: 0.041 ms
(7 rows)
Postgres 11.5, or 12.1
=================
PostDB11=# explain analyze SELECT --DISTINCT kc.childid AS rlid,
PostDB11-# kc.id AS rlrightid--,
PostDB11-# -- 0 AS rlproxytype
PostDB11-# FROM child kc
PostDB11-# WHERE NOT (EXISTS ( SELECT 1
PostDB11(# FROM core
PostDB11(# WHERE kc.id = core.groupid));
QUERY PLAN
------------------------------------------------------------------------------------------------------------
Hash Anti Join (cost=51.62..102.61 rows=1736 width=8) (actual time=0.005..0.005 rows=0 loops=1)
Hash Cond: (kc.id = core.groupid)
-> Seq Scan on child kc (cost=0.00..28.50 rows=1850 width=8) (actual time=0.005..0.005 rows=0 loops=1)
-> Hash (cost=28.50..28.50 rows=1850 width=8) (never executed)
-> Seq Scan on core (cost=0.00..28.50 rows=1850 width=8) (never executed)
Planning Time: 10.229 ms
Execution Time: 0.024 ms
Thanks & Regards,
Avinash.
В списке pgsql-bugs по дате отправления:
Предыдущее
От: Alexander LakhinДата:
Сообщение: Re: BUG #16161: pg_ctl stop fails sometimes (on Windows)
Следующее
От: Heikki LinnakangasДата:
Сообщение: Re: BUG #16162: create index using gist_trgm_ops leads to panic