首页 » PostgreSQL/GaussDB » Script: 在PostgreSQL查找包含”VOLATILE” Function 的Views
Script: 在PostgreSQL查找包含”VOLATILE” Function 的Views
前两篇在《VIEW dependencies in Oracle、MySQL、PostGreSQL(数据库比较系列十一)》《Alert : PostgreSQL inline Subquery or View 包含volatile functions 阻止谓词推进(Predicate PushDown)》记录了因为view 包含Volatile Function导致的性能问题,通常,PostgreSQL 函数可以按如下方式标记:VOLATILE、STABLE、IMMUTABLE , 而这也是算最被广泛忽视的性能问题点,这篇文档整理个SQL列出所有包含”VOLATILE” Function 的View, 判断是否可以修改稳定性属性。
直接调用
select v.oid , relname , relnamespace::regnamespace::name , ref_fun.proname,provolatile,prokind from pg_class as v join pg_rewrite rwr on v.oid=rwr.ev_class join pg_depend dep on rwr.oid=dep.objid join pg_proc ref_fun on dep.refobjid = ref_fun.oid where v.relkind='v' --and relname='v1' and dep.deptype = 'n' and ref_fun.provolatile='v' ; highgo-# ; oid | relname | relnamespace | proname | provolatile | prokind -------+----------------+--------------+----------------------+-------------+--------- 13902 | v_lic_baseinfo | public | get_lic_baseinfo | v | f 13906 | v_lic_funcinfo | public | get_lic_funcinfo | v | f 16639 | db_pipes | dbms_pipe | __list_pipes | v | f 17370 | pg_buffercache | public | pg_buffercache_pages | v | f 26561 | v1 | public | f2 | v | f (5 rows)
间接调用
WITH RECURSIVE views AS ( select v.oid::regclass AS view , v.oid ,dep.refobjid::regclass as refname, relnamespace::regnamespace as namespacename, ref_fun.oid foid, ref_fun.proname,ref_fun.provolatile,ref_fun.prokind, 1 level from pg_class as v join pg_rewrite rwr on v.oid=rwr.ev_class join pg_depend dep on rwr.oid=dep.objid left join pg_proc ref_fun on dep.refobjid = ref_fun.oid where v.relkind='v' and dep.deptype = 'n' and ref_fun.provolatile='v' UNION -- add the views that depend on these SELECT v.oid::regclass AS view,v.oid ,dep.refobjid::regclass as refname, relnamespace::regnamespace namespacename , ref_fun.oid , ref_fun.proname,ref_fun.provolatile,ref_fun.prokind, views.level + 1 FROM views JOIN pg_depend dep ON dep.refobjid = views.view join pg_rewrite rwr on rwr.oid=dep.objid join pg_class as v on v.oid=rwr.ev_class left join pg_proc ref_fun on views.foid = ref_fun.oid where v.oid <> views.view ) SELECT view,oid,namespacename,refname,proname,level FROM views; view | oid | namespacename | refname | proname | level --------------------+-------+---------------+---------+----------------------+------- v_lic_baseinfo | 13902 | public | 13900 | get_lic_baseinfo | 1 v_lic_funcinfo | 13906 | public | 13901 | get_lic_funcinfo | 1 dbms_pipe.db_pipes | 16639 | dbms_pipe | 16638 | __list_pipes | 1 pg_buffercache | 17370 | public | 17369 | pg_buffercache_pages | 1 v1 | 26561 | public | 26550 | f2 | 1 v2 | 26583 | public | v1 | f2 | 2 (6 rows)
如果您正在编写自己的过程 – 请不要忘记适当标记您的函数。否则,函数可能会被过于频繁地调用,这对性能不利。正确定义你的函数是一个非常重要且简单任务。
目前这篇文章还没有评论(Rss)