pg_stat_user_functions 是一个 统计视图，它显示每个被跟踪函数的执行统计信息。
pg_stat_user_functions 在 PostgreSQL 8.4 中添加。
用法
PostgreSQL 仅当 track_functions 被启用时，才会收集信息以供 pg_stat_user_functions 显示。
请注意，如果某个函数没有收集到统计信息（包括统计信息被重置），pg_stat_user_functions 中将不会包含该函数的条目。
可以通过 pg_stat_reset_single_function_counters() 来重置单个函数的统计信息。
按 PostgreSQL 版本定义
pg_stat_user_functions (PostgreSQL 19)
View "pg_catalog.pg_stat_user_functions"
Column | Type | Collation | Nullable | Default
------------+------------------+-----------+----------+---------
funcid | oid | | |
schemaname | name | | |
funcname | name | | |
calls | bigint | | |
total_time | double precision | | |
self_time | double precision | | |
pg_stat_user_functions (PostgreSQL 18)
View "pg_catalog.pg_stat_user_functions"
Column | Type | Collation | Nullable | Default
------------+------------------+-----------+----------+---------
funcid | oid | | |
schemaname | name | | |
funcname | name | | |
calls | bigint | | |
total_time | double precision | | |
self_time | double precision | | |
pg_stat_user_functions (PostgreSQL 17)
View "pg_catalog.pg_stat_user_functions"
Column | Type | Collation | Nullable | Default
------------+------------------+-----------+----------+---------
funcid | oid | | |
schemaname | name | | |
funcname | name | | |
calls | bigint | | |
total_time | double precision | | |
self_time | double precision | | |
pg_stat_user_functions (PostgreSQL 16)
View "pg_catalog.pg_stat_user_functions"
Column | Type | Collation | Nullable | Default
------------+------------------+-----------+----------+---------
funcid | oid | | |
schemaname | name | | |
funcname | name | | |
calls | bigint | | |
total_time | double precision | | |
self_time | double precision | | |
pg_stat_user_functions (PostgreSQL 15)
View "pg_catalog.pg_stat_user_functions"
Column | Type | Collation | Nullable | Default
------------+------------------+-----------+----------+---------
funcid | oid | | |
schemaname | name | | |
funcname | name | | |
calls | bigint | | |
total_time | double precision | | |
self_time | double precision | | |
pg_stat_user_functions (PostgreSQL 14)
View "pg_catalog.pg_stat_user_functions"
Column | Type | Collation | Nullable | Default
------------+------------------+-----------+----------+---------
funcid | oid | | |
schemaname | name | | |
funcname | name | | |
calls | bigint | | |
total_time | double precision | | |
self_time | double precision | | |
pg_stat_user_functions (PostgreSQL 13)
View "pg_catalog.pg_stat_user_functions"
Column | Type | Collation | Nullable | Default
------------+------------------+-----------+----------+---------
funcid | oid | | |
schemaname | name | | |
funcname | name | | |
calls | bigint | | |
total_time | double precision | | |
self_time | double precision | | |
pg_stat_user_functions (PostgreSQL 12)
View "pg_catalog.pg_stat_user_functions"
Column | Type | Collation | Nullable | Default
------------+------------------+-----------+----------+---------
funcid | oid | | |
schemaname | name | | |
funcname | name | | |
calls | bigint | | |
total_time | double precision | | |
self_time | double precision | | |
pg_stat_user_functions (PostgreSQL 11)
View "pg_catalog.pg_stat_user_functions"
Column | Type | Collation | Nullable | Default
------------+------------------+-----------+----------+---------
funcid | oid | | |
schemaname | name | | |
funcname | name | | |
calls | bigint | | |
total_time | double precision | | |
self_time | double precision | | |
pg_stat_user_functions (PostgreSQL 10)
View "pg_catalog.pg_stat_user_functions"
Column | Type | Collation | Nullable | Default
------------+------------------+-----------+----------+---------
funcid | oid | | |
schemaname | name | | |
funcname | name | | |
calls | bigint | | |
total_time | double precision | | |
self_time | double precision | | |
pg_stat_user_functions (PostgreSQL 9.6)
View "pg_catalog.pg_stat_user_functions"
Column | Type | Modifiers
------------+------------------+-----------
funcid | oid |
schemaname | name |
funcname | name |
calls | bigint |
total_time | double precision |
self_time | double precision |
pg_stat_user_functions (PostgreSQL 9.5)
View "pg_catalog.pg_stat_user_functions"
Column | Type | Modifiers
------------+------------------+-----------
funcid | oid |
schemaname | name |
funcname | name |
calls | bigint |
total_time | double precision |
self_time | double precision |
pg_stat_user_functions (PostgreSQL 9.4)
View "pg_catalog.pg_stat_user_functions"
Column | Type | Modifiers
------------+------------------+-----------
funcid | oid |
schemaname | name |
funcname | name |
calls | bigint |
total_time | double precision |
self_time | double precision |
pg_stat_user_functions (PostgreSQL 9.3)
View "pg_catalog.pg_stat_user_functions"
Column | Type | Modifiers
------------+------------------+-----------
funcid | oid |
schemaname | name |
funcname | name |
calls | bigint |
total_time | double precision |
self_time | double precision |
pg_stat_user_functions (PostgreSQL 9.2)
View "pg_catalog.pg_stat_user_functions"
Column | Type | Modifiers
------------+------------------+-----------
funcid | oid |
schemaname | name |
funcname | name |
calls | bigint |
total_time | double precision |
self_time | double precision |
pg_stat_user_functions (PostgreSQL 9.1)
View "pg_catalog.pg_stat_user_functions"
Column | Type | Modifiers
------------+--------+-----------
funcid | oid |
schemaname | name |
funcname | name |
calls | bigint |
total_time | bigint |
self_time | bigint |
pg_stat_user_functions (PostgreSQL 9.0)
View "pg_catalog.pg_stat_user_functions"
Column | Type | Modifiers
------------+--------+-----------
funcid | oid |
schemaname | name |
funcname | name |
calls | bigint |
total_time | bigint |
self_time | bigint |
pg_stat_user_functions (PostgreSQL 8.4)
View "pg_catalog.pg_stat_user_functions"
Column | Type | Modifiers
------------+--------+-----------
funcid | oid |
schemaname | name |
funcname | name |
calls | bigint |
total_time | bigint |
self_time | bigint |
View definition:
SELECT p.oid AS funcid, n.nspname AS schemaname, p.proname AS funcname, pg_stat_get_function_calls(p.oid) AS calls, pg_stat_get_function_time(p.oid) / 1000 AS total_time, pg_stat_get_function_self_time(p.oid) / 1000 AS self_time
FROM pg_proc p
LEFT JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE p.prolang <> 12::oid AND pg_stat_get_function_calls(p.oid) IS NOT NULL;
变更历史
- PostgreSQL 19
- 添加了
stats_reset列 (commit b71bae41)
- 添加了
- PostgreSQL 8.4
- 添加 (commit 93c701ed)
示例
pg_stat_user_functions 的示例输出
postgres=# SELECT * FROM pg_stat_user_functions; funcid | schemaname | funcname | calls | total_time | self_time --------+------------+-----------------------------------+-------+------------+----------- 16406 | hstore | fetchval | 10 | 0.008 | 0.008 16732 | public | calendar_month | 1 | 4.05 | 4.05 16743 | public | get_dir_id_from_path_site | 156 | 44.653 | 22.136 16744 | public | get_dir_path_from_id | 5973 | 465.216 | 465.216 16745 | public | get_object_property_value | 4636 | 111.405 | 111.405 16746 | public | get_object_title | 30904 | 914.304 | 914.304 16747 | public | get_syndicate_child_directory_ids | 4 | 1.527 | 1.527 16749 | public | object_property_value | 2856 | 131.923 | 131.923 16750 | public | olv_best_lang | 55657 | 1150.719 | 1150.719 16764 | public | ts_body_property_value | 1242 | 129.289 | 129.289 16768 | public | user_modules | 6 | 3.053 | 3.053 16769 | public | user_site_access | 36 | 1.555 | 1.555 69853 | public | tr_update_object_access_tracking | 1 | 134.301 | 134.301 (13 rows)
参考资料
- PostgreSQL 文档: pg_stat_user_functions
有用链接
- 等待 8.4 - 函数统计 - depesz 于 2008 年 5 月的博客文章