close

Menu

Contact Info

Folow us on social

PostgreSQL : Track functions to tune

  • Denish Patel
  • 9th November 2010
  • postgresql

Starting from PostgreSQL 8.4, there is a valuable addition in PostgreSQL features list: Per function statistics. Prior to this feature , there wasn’t an easy way to find out most time consuming functions in the database.

For time spent on single call, it’s easy to find out by executing function manually but it was too cumbersome and in some cases impossible to find out total time spent by function for the given time period. Enabling full logging and aggregating duration for each call by reading large log files was the only way to get that statistic. Now, you can find this details by querying a single view pg_stat_user_functions ! Awesome!
To enable function tracking you need to enable “track_functions” postgresql.conf parameter. The default is none, which disables function statistics tracking. Specify pl to track only procedural-language functions, all to also track SQL and C language functions.

#track_functions = none # none, pl, all

Once you have track_functions enabled in your database, you can get details by querying pg_stat_user_functions view:

select * from pg_stat_user_functions;


It provides following details:

funcid
schemaname
funcname
calls
total_time
self_time

Self time is the amount of time spent in the function itself, total time includes the time spent in functions it called. Time values are in milliseconds.

Here is the easy solution to track and find candidate that requires tuning!!

Leave a comment

Your email address will not be published.