Re: Planning time is high in Postgres 11.5 Compared with Postgres10.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