溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章主要講解了“怎么使用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擴展函數這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
