Re: Planning time is high in Postgres 11.5 Compared with Postgres 10.11

On 16/12/2019 10:58, avinash varma wrote:
> 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.

I cannot reproduce that on my laptop. Planning takes less than 1 ms, and
there is no big difference between server versions. Do you have any
non-default settings in postgresql.conf?

I think you'll need to do more investigation on your end, to figure out
where exactly the time is spent. If you're running on a Linux system,
I'd suggest using 'perf' to capture a trace of the backend functions
where the time is spent:

1. Open a psql session. Run "select pg_backend_pid();" to get the
backend's PID

2. In another terminal, launch "perf record -g -p <pid>".

3. Run the EXPLAIN in a loop:

\timing
do $$
begin
for i in 1..100000 loop
execute $query$
EXPLAIN SELECT kc.id AS rlrightid FROM child kc
WHERE NOT (EXISTS ( SELECT 1 FROM core
WHERE kc.id = core.groupid));
$query$;
end loop;
end;
$$;

4. Quit psql, and run "perf report -g". It should print a detailed
report on which parts of the system the CPU time is spent.

> Also please note that below kind of query will executes million times in
> our regular activities. So which might creating high CPU issue.

Using a prepared statement would be a good idea in that case.

- Heikki