Postgres pg_stat_user_functions: Measuring What Your PL/pgSQL Code Actually Costs

Most application code that runs against a Postgres database is written in the application language and the database executes the SQL it produces. A smaller but persistent fraction of work runs inside the database itself as PL/pgSQL functions: triggers, complex validation, batch ETL transforms, audit-trail generation. When those functions become slow, the diagnostic story is harder than for raw SQL. EXPLAIN ANALYZE does not descend into function bodies by default. pg_stat_statements records the call site, not the inner work. The function feels like a black box because the standard diagnostic tools were not designed to see inside it.

pg_stat_user_functions is the view that closes the gap. It records per-function call counts, total execution time, and self time, which is total time minus the time spent in functions called by this function. The view is empty by default because PL/pgSQL function tracking is off in the standard configuration; turning it on costs a small amount of overhead per call and produces enough information to find the functions that dominate the workload. The view is one of the few places where database introspection looks more like application profiling than like SQL diagnostics.

What the view exposes

The view has six columns. funcid is the OID of the function, schemaname and funcname identify it textually, calls is the cumulative number of times the function has been called since the last reset, total_time is the cumulative wall-clock time spent inside the function including nested calls measured in milliseconds, self_time is total_time minus time spent in functions this function called. The total-minus-self distinction lets you separate the function's own work from the work it delegates to nested function calls, which matters when the workload includes function composition patterns common in PL/pgSQL.

The view returns one row per function that has been called since tracking was enabled, scoped to user-defined functions in user schemas. System catalog functions and extension functions are filtered out. The view is per-database; cross-database aggregation requires query-per-database. The counts are cumulative until pg_stat_reset() is called or the cluster restarts, which makes the view useful for accumulating workload samples over hours or days rather than for instantaneous snapshots.

The configuration knob

The track_functions parameter controls whether per-function statistics are collected. The default value is none, which means no functions are tracked and pg_stat_user_functions returns no rows. The two non-default values are pl, which tracks PL/pgSQL functions and other procedural languages, and all, which additionally tracks SQL functions. The pl setting is the right default for most workloads because SQL functions are usually visible through pg_stat_statements via the queries they contain, while PL/pgSQL function bodies are opaque without explicit tracking.

The overhead of tracking is small but non-zero. Each function call records two timestamps and updates the in-memory stats counters at function entry and exit. The cost per call is on the order of microseconds, which is negligible for functions that do real work but visible for functions called millions of times per second. The overhead is bounded by call count rather than function complexity, so cheap functions called frequently pay the overhead proportionally more than expensive functions called rarely. The configuration change is a sighup-reload parameter, not a restart parameter, which makes it easy to turn on for a diagnostic window and back off after.

The diagnostic queries

The first diagnostic query is the top-N functions by total time, which identifies where PL/pgSQL execution time is concentrated. The query selects schemaname, funcname, calls, total_time, mean_time computed as total_time divided by calls, and self_time, ordered by total_time descending. The output usually concentrates 80-90 percent of total function execution time in a handful of functions, which is the standard Pareto pattern. The functions that dominate are the candidates for optimization.

The second diagnostic query is mean time per call, which identifies functions that are slow individually rather than functions that are slow because they are called often. The same source data sorted by mean_time descending produces a different list: rarely-called expensive batch functions instead of frequently-called cheap functions. The two lists usually do not overlap completely, and the right interpretation depends on the question being asked. Total time identifies the functions consuming the most server CPU; mean time identifies the functions producing the most latency per invocation.

The third diagnostic query is the self-time-to-total-time ratio, which identifies functions whose own work is small compared to nested function calls. A function with self_time close to total_time does most of its work directly; a function with self_time much smaller than total_time is mostly a wrapper around other functions. The wrapper case is interesting because the function visible in the view is not where the actual work happens, and optimization needs to descend into the nested functions to find where the time is being spent.

The wrap-and-measure pattern

The view tracks PL/pgSQL function execution, but a large fraction of database work happens in raw SQL statements that are not inside any function. The wrap-and-measure pattern provides a way to bring raw SQL under the view's coverage temporarily. The technique is to write a thin PL/pgSQL wrapper function that executes a single SQL statement and returns the result. The function appears in the view; the underlying SQL does not. Wrapping a candidate query in a temporary function for the duration of a diagnostic window gives total time and call count attribution that pg_stat_statements does not provide at the level of named query intent.

The pattern is not appropriate for production code because the wrapper adds overhead and breaks query plan reuse. It is appropriate for a diagnostic window where the goal is to understand which named operations consume what fraction of the workload. The wrapped functions are dropped at the end of the diagnostic window and the production code reverts to direct SQL. The transient nature of the wrapping is what makes the pattern useful: it is a measurement technique, not an architectural choice.

What the view does not show

The view does not show per-call timing, which would be useful for understanding the variance of function execution time but would generate too much data to store cheaply. Per-call timing requires explicit instrumentation inside the function body, such as a logging statement that records the elapsed time at function exit. The wrapper-plus-logging pattern can produce per-call distribution data for functions where the variance matters.

The view does not show what the function did internally, including which SQL statements it executed and how long each one took. The auto_explain extension provides that data when configured to log nested statements, but the output is verbose and requires log-aggregation infrastructure to be useful. The combination of pg_stat_user_functions for function-level attribution and auto_explain for statement-level detail covers most diagnostic needs at the cost of more configuration complexity.

The view does not show standby execution. The statistics counters are not replicated to standbys, and the standby view shows only functions executed on the standby itself. This is the right behavior because function execution patterns on the standby are usually different from the primary, but it does mean that pg_stat_user_functions does not give a complete cluster-wide picture.

The configuration adjacents

The track_io_timing parameter is related but separate. It controls whether per-statement I/O timing is collected, which appears in pg_stat_statements rather than in pg_stat_user_functions. The two parameters are usually turned on together because the diagnostic story is more complete when both are available, but they have independent overhead profiles. track_io_timing can have substantial overhead on systems with slow gettime calls, which means it should be benchmarked before being enabled in production. track_functions = pl has minimal overhead on any reasonable system.

The track_activity_query_size parameter affects how much of each query is captured in pg_stat_activity, which is loosely related but does not affect function tracking directly. The log_min_duration_statement parameter is also related as a way to log slow queries for separate analysis, but functions whose total time is dominated by many fast statements do not produce log entries even when individual function calls take a long time. The right diagnostic strategy combines several mechanisms rather than relying on any one of them.

What this view tells us about Postgres design

The existence of pg_stat_user_functions as a distinct view, rather than per-function entries in pg_stat_statements, reflects the architectural separation between SQL execution and PL/pgSQL execution. SQL statements are parsed and executed by the executor, with timing instrumentation baked into the executor itself. PL/pgSQL functions are executed by the PL/pgSQL interpreter, which is a separate subsystem with its own instrumentation. The two views are populated by different code paths and have different overhead profiles because the underlying execution mechanisms are different.

The opt-in default for function tracking reflects the conservative-by-default Postgres philosophy: features that have non-zero cost are off until the operator decides the cost is worth paying. The default is calibrated for the common case where PL/pgSQL is a minority of the workload and the overhead would be unnecessary. Workloads that lean heavily on PL/pgSQL need to opt in to the tracking and pay the small overhead in exchange for the visibility.

The SQLite analog

All four of our products run on SQLite, which does not have stored procedures in the PL/pgSQL sense. The closest analog is application-side functions invoked by the application code and timed by application instrumentation. The diagnostic story is therefore handled at the application layer rather than the database layer: the FastAPI middleware records per-route latency, and any expensive computation inside a route handler is visible through that mechanism.

The eventual Postgres migration will bring some of the validation and audit-trail logic into PL/pgSQL functions if the migration includes triggers and stored procedures. The plan is to enable track_functions = pl from launch and to monitor the top-time functions weekly during the first months of operation. The expectation is that a few functions will dominate the time and become candidates for refactoring or for moving back to application-side execution. The diagnostic discipline is the same as for query monitoring: look at the workload, identify the concentrations, decide whether the concentrations are acceptable or actionable.

The takeaway

pg_stat_user_functions is one of the diagnostic views that becomes essential the moment you start using PL/pgSQL substantially and is irrelevant before that point. The configuration knob is small and the cost is small. The diagnostic queries are simple and the output is interpretable. The wrap-and-measure pattern extends the view's coverage to raw SQL for diagnostic windows. The combination provides function-level workload attribution that is hard to get any other way. Postgres has decades of operational experience baked into views like this one, and most of the value comes from knowing they exist and what they show.

Our products: DocuMint (PDF invoice generation API), CronPing (cron job monitoring with status pages), FlagBit (feature flags API for modern teams), and WebhookVault (webhook capture and replay) put these patterns into production.