Sunday, November 25, 2018

plpgsql_check can be used as profiler

Today I integrated profiling functionality into plpgsql_check. When you enable profiling, then you don't need configure more.
postgres=# select lineno, avg_time, source from plpgsql_profiler_function_tb('fx(int)');
┌────────┬──────────┬───────────────────────────────────────────────────────────────────┐
│ lineno │ avg_time │                              source                               │
╞════════╪══════════╪═══════════════════════════════════════════════════════════════════╡
│      1 │          │                                                                   │
│      2 │          │ declare result int = 0;                                           │
│      3 │    0.075 │ begin                                                             │
│      4 │    0.202 │   for i in 1..$1 loop                                             │
│      5 │    0.005 │     select result + i into result; select result + i into result; │
│      6 │          │   end loop;                                                       │
│      7 │        0 │   return result;                                                  │
│      8 │          │ end;                                                              │
└────────┴──────────┴───────────────────────────────────────────────────────────────────┘
(9 rows)
In this case, the function profile is stored in session memory, and when session is closed, the profile is lost.

There is possibility to load plpgsql_check by shared_preload_libraries config option. In this case, the profile is stored in shared memory and it is "pseudo" persistent. It is cleaned, when profile reset is required or when PostgreSQL is restarted.

There is another good PLpgSQL profiler. I designed integrated plpgsql_check profiler because I would to collect different data from running time, and I would to use this profiler for calculating test coverage. More, this profiler can be used without any special PostgreSQL configuration, what can be useful for some cases, when there are not a possibility to restart a server.

1 Comments:

At November 25, 2018 at 10:57 PM , Anonymous Aleš Zelený said...

This looks to be a great step forward, nice feature!

 

Post a Comment

Subscribe to Post Comments [Atom]

<< Home