- 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!!
Categories
Recent Blog
- Do you really need a DBA?
- Running VACUUM FULL in Non-blocking Mode
- Connection Scaling
- PSQL Helper: Managing Connections and Simplifying Queries
- Vacuum Those MVs!
- Working With Repmgr: Using Other 3rd Party Tools for Setting up a Standby
- Working with Amazon Aurora PostgreSQL: dag, standby rebooted again!
- Working with Amazon Aurora PostgreSQL: what happened to the stats?
- How to set application_name for psql command line utility?
- Is there a limit on number of partitions handled by Postgres?