怎么使用PostgreSQL扩展函数

这篇文章主要讲解了“怎么使用PostgreSQL扩展函数”,文中的讲解内容简单清晰,易于学习与理解,下面请大家跟着小编的思路慢慢深入,一起来研究和学习“怎么使用PostgreSQL扩展函数”吧!

例子

. ./env11.sh  
  
git clone https://github.com/MasaoFujii/pg_cheat_funcs  
  
cd pg_cheat_funcs/  
  
USE_PGXS=1 make  
  
USE_PGXS=1 make install
postgres=# create extension pg_cheat_funcs ;  
CREATE EXTENSION

1、打印内存上下文

postgres=# select * from pg_stat_get_memory_context();  
           name           |       parent       | level | total_bytes | total_nblocks | free_bytes | free_chunks | used_bytes   
--------------------------+--------------------+-------+-------------+---------------+------------+-------------+------------  
 TopMemoryContext         |                    |     0 |      312552 |            11 |      40520 |          21 |     272032  
 dynahash                 | TopMemoryContext   |     1 |        8192 |             1 |       1456 |           0 |       6736  
 TopTransactionContext    | TopMemoryContext   |     1 |        8192 |             1 |       7744 |           1 |        448  
 PL/pgSQL function        | TopMemoryContext   |     1 |       16384 |             2 |       7176 |           1 |       9208  
 dynahash                 | TopMemoryContext   |     1 |        8192 |             1 |        560 |           0 |       7632  
 dynahash                 | TopMemoryContext   |     1 |        8192 |             1 |        560 |           0 |       7632  
 dynahash                 | TopMemoryContext   |     1 |       24528 |             2 |       2624 |           0 |      21904  
 dynahash                 | TopMemoryContext   |     1 |        8192 |             1 |       2096 |           0 |       6096  
 dynahash                 | TopMemoryContext   |     1 |        8192 |             1 |       1584 |           0 |       6608  
 dynahash                 | TopMemoryContext   |     1 |       24576 |             2 |      10760 |           3 |      13816  
 RowDescriptionContext    | TopMemoryContext   |     1 |        8192 |             1 |       6896 |           0 |       1296  
 MessageContext           | TopMemoryContext   |     1 |       32768 |             3 |      10904 |           1 |      21864  
 dynahash                 | TopMemoryContext   |     1 |        8192 |             1 |        560 |           0 |       7632  
 dynahash                 | TopMemoryContext   |     1 |       32768 |             3 |      16832 |           8 |      15936  
 TransactionAbortContext  | TopMemoryContext   |     1 |       32768 |             1 |      32512 |           0 |        256  
 dynahash                 | TopMemoryContext   |     1 |        8192 |             1 |        560 |           0 |       7632  
 TopPortalContext         | TopMemoryContext   |     1 |        8192 |             1 |       7664 |           0 |        528  
 PortalContext            | TopPortalContext   |     2 |        1024 |             1 |        592 |           0 |        432  
 ExecutorState            | PortalContext      |     3 |       49208 |             4 |      15576 |           3 |      33632  
 printtup                 | ExecutorState      |     4 |        8192 |             1 |       7936 |           0 |        256  
 Table function arguments | ExecutorState      |     4 |        8192 |             1 |       7936 |           0 |        256  
 ExprContext              | ExecutorState      |     4 |        8192 |             1 |       4536 |           0 |       3656  
 dynahash                 | TopMemoryContext   |     1 |       16384 |             2 |       3512 |           2 |      12872  
 CacheMemoryContext       | TopMemoryContext   |     1 |      524288 |             7 |      20960 |          26 |     503328  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        624 |           1 |       1424  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        624 |           1 |       1424  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        624 |           1 |       1424  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        624 |           1 |       1424  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        680 |           1 |       1368  
 EventTriggerCache        | CacheMemoryContext |     2 |        8192 |             1 |       7936 |           2 |        256  
 dynahash                 | EventTriggerCache  |     3 |        8192 |             1 |       2624 |           0 |       5568  
 index info               | CacheMemoryContext |     2 |        1024 |             1 |         48 |           0 |        976  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        952 |           1 |       1096  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        952 |           1 |       1096  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        952 |           1 |       1096  
 relation rules           | CacheMemoryContext |     2 |       16384 |             5 |       7352 |           0 |       9032  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        648 |           2 |       1400  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        704 |           3 |       1344  
 index info               | CacheMemoryContext |     2 |        1024 |             1 |         16 |           0 |       1008  
 index info               | CacheMemoryContext |     2 |        1024 |             1 |         48 |           0 |        976  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        680 |           2 |       1368  
 index info               | CacheMemoryContext |     2 |        1024 |             1 |         48 |           0 |        976  
 index info               | CacheMemoryContext |     2 |        1024 |             1 |         48 |           0 |        976  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        592 |           3 |       1456  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        680 |           2 |       1368  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        952 |           1 |       1096  
 index info               | CacheMemoryContext |     2 |        1024 |             1 |         48 |           0 |        976  
 index info               | CacheMemoryContext |     2 |        1024 |             1 |         48 |           0 |        976  
 index info               | CacheMemoryContext |     2 |        3072 |             2 |       1136 |           2 |       1936  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        952 |           1 |       1096  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        760 |           2 |       1288  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        704 |           3 |       1344  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        952 |           1 |       1096  
 index info               | CacheMemoryContext |     2 |        1024 |             1 |         16 |           0 |       1008  
 index info               | CacheMemoryContext |     2 |        1024 |             1 |         48 |           0 |        976  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        760 |           2 |       1288  
 index info               | CacheMemoryContext |     2 |        1024 |             1 |         48 |           0 |        976  
 index info               | CacheMemoryContext |     2 |        1024 |             1 |         48 |           0 |        976  
 index info               | CacheMemoryContext |     2 |        1024 |             1 |         48 |           0 |        976  
 index info               | CacheMemoryContext |     2 |        3072 |             2 |       1136 |           2 |       1936  
 index info               | CacheMemoryContext |     2 |        3072 |             2 |       1136 |           2 |       1936  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        760 |           2 |       1288  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        760 |           2 |       1288  
 index info               | CacheMemoryContext |     2 |        1024 |             1 |         48 |           0 |        976  
 index info               | CacheMemoryContext |     2 |        1024 |             1 |         48 |           0 |        976  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        704 |           3 |       1344  
 index info               | CacheMemoryContext |     2 |        1024 |             1 |         48 |           0 |        976  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        952 |           1 |       1096  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        952 |           1 |       1096  
 index info               | CacheMemoryContext |     2 |        1024 |             1 |         48 |           0 |        976  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        680 |           2 |       1368  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        760 |           2 |       1288  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        760 |           2 |       1288  
 index info               | CacheMemoryContext |     2 |        1024 |             1 |         48 |           0 |        976  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        728 |           1 |       1320  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        952 |           1 |       1096  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        952 |           1 |       1096  
 index info               | CacheMemoryContext |     2 |        1024 |             1 |         48 |           0 |        976  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        624 |           2 |       1424  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        672 |           3 |       1376  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        704 |           3 |       1344  
 index info               | CacheMemoryContext |     2 |        1024 |             1 |         48 |           0 |        976  
 index info               | CacheMemoryContext |     2 |        1024 |             1 |         48 |           0 |        976  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        760 |           2 |       1288  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        680 |           2 |       1368  
 index info               | CacheMemoryContext |     2 |        1024 |             1 |         48 |           0 |        976  
 index info               | CacheMemoryContext |     2 |        1024 |             1 |         48 |           0 |        976  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        952 |           1 |       1096  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        624 |           2 |       1424  
 index info               | CacheMemoryContext |     2 |        1024 |             1 |         16 |           0 |       1008  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        760 |           2 |       1288  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        680 |           2 |       1368  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        952 |           1 |       1096  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        952 |           1 |       1096  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        952 |           1 |       1096  
 index info               | CacheMemoryContext |     2 |        3072 |             2 |       1136 |           2 |       1936  
 index info               | CacheMemoryContext |     2 |        1024 |             1 |         48 |           0 |        976  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        760 |           2 |       1288  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        952 |           1 |       1096  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        728 |           1 |       1320  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        728 |           1 |       1320  
 index info               | CacheMemoryContext |     2 |        1024 |             1 |         48 |           0 |        976  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        680 |           2 |       1368  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        952 |           1 |       1096  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        728 |           1 |       1320  
 index info               | CacheMemoryContext |     2 |        1024 |             1 |         48 |           0 |        976  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        952 |           1 |       1096  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        704 |           3 |       1344  
 index info               | CacheMemoryContext |     2 |        1024 |             1 |         16 |           0 |       1008  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        952 |           1 |       1096  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        760 |           2 |       1288  
 index info               | CacheMemoryContext |     2 |        1024 |             1 |         16 |           0 |       1008  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        728 |           1 |       1320  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        952 |           1 |       1096  
 index info               | CacheMemoryContext |     2 |        2048 |             2 |        952 |           1 |       1096  
 WAL record construction  | TopMemoryContext   |     1 |       49768 |             2 |       6368 |           0 |      43400  
 dynahash                 | TopMemoryContext   |     1 |        8192 |             1 |       2624 |           0 |       5568  
 MdSmgr                   | TopMemoryContext   |     1 |        8192 |             1 |       7432 |           0 |        760  
 dynahash                 | TopMemoryContext   |     1 |        8192 |             1 |        560 |           0 |       7632  
 dynahash                 | TopMemoryContext   |     1 |      104120 |             2 |       2624 |           0 |     101496  
 ErrorContext             | TopMemoryContext   |     1 |        8192 |             1 |       7936 |           0 |        256  
(121 rows)

2、文本压缩

  postgres=# select pglz_compress(repeat(md5(random()::text),1024));                                                                                                                                                      pglz_compress                                                                                                        
-------------------------------------------------------------------------------------------------------------------------------------  
 /x00800040006135663437636166003162353066626137006637356161363639003532316233336139ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f  
20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f2  
0ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f  
20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ffff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20ff0f20e7  
(1 row)

3、解压缩

pglz_decompress(bytea)  
  
postgres=# select pglz_compress(repeat(md5(random()::text),8));  
                                       pglz_compress                                          
--------------------------------------------------------------------------------------------  
 /x00010040003236666162656631003262323262636230006462656638616330003536343161383937010f20ce  
(1 row)  
  
postgres=# select pglz_decompress('/x00010040003236666162656631003262323262636230006462656638616330003536343161383937010f20ce');  
             pglz_decompress      
------------------------------------------------------------------------------  
 26fabef12b22bcb0dbef8ac05641a89726fabef12b22bcb0dbef8ac05641a89726fabef12b22bcb0dbef8ac05641a89726fabef12b22bcb0dbef8ac05641a89726fabef12b22bcb0dbef8ac05641a89726fabef12b22bcb0dbef8ac05641a89726fabef12b22bcb0dbef8ac05641a89726fabef12b22  
bcb0dbef8ac05641a897  
(1 row)

4、查看绑定变量QUERY的PLAN COSE。

postgres=# prepare a (name) as select * from pg_class where relname=$1;
PREPARE
postgres=# select * from pg_cached_plan_source('a');
 generic_cost | total_custom_cost | num_custom_plans | force_generic | force_custom 
--------------+-------------------+------------------+---------------+--------------
           -1 |                 0 |                0 | f             | f
(1 row)
postgres=# execute a('abc');
 relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks | relhaso
ids | relhasrules | relhastriggers | relhassubclass | relrowsecurity | relforcerowsecurity | relispopulated | relreplident | relispartition | relrewrite | relfrozenxid | relminmxid | relacl | reloptions | relpartbound 
---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+----------------+---------+----------+-----------+--------
----+-------------+----------------+----------------+----------------+---------------------+----------------+--------------+----------------+------------+--------------+------------+--------+------------+--------------
(0 rows)
postgres=# select * from pg_cached_plan_source('a');
 generic_cost | total_custom_cost | num_custom_plans | force_generic | force_custom 
--------------+-------------------+------------------+---------------+--------------
           -1 |  4.61208554676785 |                1 | f             | f
(1 row)
postgres=# execute a('abc');
 relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks | relhaso
ids | relhasrules | relhastriggers | relhassubclass | relrowsecurity | relforcerowsecurity | relispopulated | relreplident | relispartition | relrewrite | relfrozenxid | relminmxid | relacl | reloptions | relpartbound 
---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+----------------+---------+----------+-----------+--------
----+-------------+----------------+----------------+----------------+---------------------+----------------+--------------+----------------+------------+--------------+------------+--------+------------+--------------
(0 rows)
postgres=# select * from pg_cached_plan_source('a');
 generic_cost | total_custom_cost | num_custom_plans | force_generic | force_custom 
--------------+-------------------+------------------+---------------+--------------
           -1 |  9.22417109353571 |                2 | f             | f
(1 row)

感谢各位的阅读,以上就是“怎么使用PostgreSQL扩展函数”的内容了,经过本文的学习后,相信大家对怎么使用PostgreSQL扩展函数这一问题有了更深刻的体会,具体使用情况还需要大家实践验证。这里是亿速云,小编将为大家推送更多相关知识点的文章,欢迎关注!

原创文章,作者:carmelaweatherly,如若转载,请注明出处:https://blog.ytso.com/205093.html

(0)
上一篇 2021年11月29日
下一篇 2021年11月29日

相关推荐

发表回复

登录后才能评论