虚拟索引hypopg
该扩展有助于了解特定索引是否可以提高问题查询的性能。虚拟索引并不是真实存在的索引，因此不耗费CPU、磁盘或其他资源，可以有效验证索引是否有效。
创建表并插入测试数据。
CREATE TABLE hypo (id integer, val text) ;
INSERT INTO hypo SELECT i, 'line ' || i FROM generate_series(1, 100000) i ;
VACUUM ANALYZE hypo ;
查看当前表没有任何索引的执行计划，并使用了普通顺序扫描。
EXPLAIN SELECT val FROM hypo WHERE id = 1;
QUERY PLAN
--------------------------------------------------------
Seq Scan on hypo (cost=0.00..1791.00 rows=1 width=14)
Filter: (id = 1)
(2 rows)
用hypopg_create_index()函数创建一个虚拟索引。
SELECT * FROM hypopg_create_index('CREATE INDEX ON hypo (id)') ;
indexrelid | indexname
------------+----------------------
18284 | <18284>btree_hypo_id
(1 row)
再次EXPLAIN查看是否会用到这个btree索引。
EXPLAIN SELECT val FROM hypo WHERE id = 1;
QUERY PLAN
----------------------------------------------------------------------------------
Index Scan using <18284>btree_hypo_id on hypo (cost=0.04..8.06 rows=1 width=10)
Index Cond: (id = 1)
(2 rows)
检查虚拟索引并未实际使用到，对现环境没有影响。
EXPLAIN ANALYZE SELECT val FROM hypo WHERE id = 1;
QUERY PLAN
---------------------------------------------------------------------------------------------------
Seq Scan on hypo (cost=0.00..1791.00 rows=1 width=10) (actual time=0.046..46.390 rows=1 loops=1)
Filter: (id = 1)
Rows Removed by Filter: 99999
Planning time: 0.160 ms
Execution time: 46.460 ms
(5 rows)
orafce
orafce英文全称Oracle’s compatibility functions and packages。可以在PostgreSQL上使用Oracle的特殊函数和包，并且兼容Oracle的部分语法、数据类型、函数、字典表等。
通过orafce可以和Oracle兼容的功能。
数据类型
| Item | Overview |
|---|---|
| VARCHAR2 | 变长字符数据类型 |
| NVARCHAR2 | 变长国家字符数据类型 |
| DATE | 存储日期和时间的数据类型 |
SQL 查询
| Item | Overview |
|---|---|
| DUAL table | 系统提供的虚拟表 |
SQL 函数
数学运算函数
| Item | Overview |
|---|---|
| BITAND | 执行位与操作 |
| COSH | 计算一个数字的双曲余弦 |
| SINH | 计算一个数字的双曲正弦 |
| TANH | 计算一个数字的双曲正切 |
字符串函数
| Item | Overview |
|---|---|
| INSTR | 返回子字符串在字符串中的位置 |
| LENGTH | 以字符数为单位返回字符串的长度 |
| LENGTHB | 以字节数为单位返回字符串的长度 |
| LPAD | 用字符序列左填充字符串到指定长度 |
| LTRIM | 从字符串的开头删除指定的字符 |
| NLSSORT | 返回一个字节字符串，用于根据区域设置对语言排序序列中的字符串进行排序 |
| REGEXP_COUNT | 在字符串中搜索正则表达式，并返回匹配次数 |
| REGEXP_INSTR | 返回字符串中模式匹配所在的起始或结束位置 |
| REGEXP_LIKE | 判断字符串是否符合正则表达式的规则 |
| REGEXP_SUBSTR | 返回与函数调用中指定的模式匹配的字符串 |
| REGEXP_REPLACE | 替换匹配POSIX正则表达式的子字符串 |
| RPAD | 用字符序列将字符串右填充到指定长度 |
| RTRIM | 从字符串的末尾删除指定字符 |
| SUBSTR | 使用指定位置和长度的字符提取字符串的一部分 |
| SUBSTRB | 使用字节来指定位置和长度提取字符串的一部分 |
日期/时间函数
| Item | Overview |
|---|---|
| ADD_MONTHS | 为日期添加月份 |
| DBTIMEZONE | 返回数据库时区的值 |
| LAST_DAY | 返回指定日期所在月份的最后一天 |
| MONTHS_BETWEEN | 返回两个日期之间的月数 |
| NEXT_DAY | 用于计算给定日期的下一天 |
| ROUND | 对日期进行四舍五入处理 |
| SESSIONTIMEZONE | 返回会话的时区 |
| SYSDATE | 返回系统日期 |
| TRUNC | 截断日期 |
数据类型格式化函数
| Item | Overview |
|---|---|
| TO_CHAR | 将值转换为字符串 |
| TO_DATE | 按照指定的格式将字符串转换为日期 |
| TO_MULTI_BYTE | 将单字节字符串转换为多字节字符串 |
| TO_NUMBER | 按照指定的格式将值转换为数字 |
| TO_SINGLE_BYTE | 将多字节字符串转换为单字节字符串 |
条件表达式
| Item | Overview |
|---|---|
| DECODE | 比较值，如果它们匹配，则返回相应的值 |
| GREATEST | 返回一个或多个表达式列表中的最大值 |
| LEAST | 返回一个或多个表达式列表中的最小值 |
| LNNVL | 计算值是否为false或未知 |
| NANVL | 当值不是数字(NaN)时返回替代值 |
| NVL | 当值为NULL时返回替代值 |
| NVL2 | 根据值是否为NULL返回替代值 |
聚合函数
| Item | Overview |
|---|---|
| LISTAGG | 将指定列的值按照指定的分隔符组合成一个字符串 |
| MEDIAN | 计算一组值的中位数 |
返回内部信息的函数
| Item | Overview |
|---|---|
| DUMP | 返回值的内部信息 |
SQL 操作
| Item | Overview |
|---|---|
| Datetime operator | DATE类型的日期时间操作符 |
程序包
| Item | Overview |
|---|---|
| DBMS_ALERT | 向多个会话发送警报 |
| DBMS_ASSERT | 验证输入值的属性 |
| DBMS_OUTPUT | 向客户端发送消息 |
| DBMS_PIPE | 创建用于会话间通信的管道 |
| DBMS_RANDOM | 生成随机数 |
| DBMS_UTILITY | 提供各种实用程序 |
| UTL_FILE | 用于操作系统的文本文件操作 |
pg_top
pg_top是PostgreSQL的“top”。与top类似可以监视PostgreSQL进程。还可以查看进程当前正在运行的SQL语句。另外还可以：
查看进程当前正在运行的SQL语句。
查看当前运行的SELECT语句的查询计划。
进程持有的视图锁。
查看每个进程的I/O统计信息。
查看下行节点的复制统计信息。
可以监控到主机的负载、CPU、内存、SWAP、PG的进程数（总数、活跃数，空闲数等），同样还能显示具体的进程信息（PID、DB用户名、资源占用情况等）。
“Q”键并输入PID可以查看当前执行的SQL。
“E”键（EXPLAIN）或A键（EXPLAIN ANALYZE），然后再输入具体的PID，可以查看进程当前运行SQL的执行计划。
‘L’键并输入PID可以查看当前数据库LOCK信息。
plprofiler
查找PL/pgSQL函数和存储过程中的性能问题可能很困难，尤其是在代码嵌套的情况下。通过系统视图或扩展视图(如pg_stat_activity或pg_stat_statements)来辅助分析函数和存过中的性能问题也是有所局限。
plprofiler扩展可用于快速识别最耗时的函数，然后向下钻取查找其中的单个语句，并生成html报表。报表以火焰图形式展示函数调用堆栈、耗时占比，还可以查看函数中每个SQL的位置、执行次数、最长执行时间、总时间等。
创建扩展后，使用plprofiler调用函数，从后端收集本地数据来创建HTML报告。
plprofiler run --command "SELECT tpcb(1, 2, 3, -42)" --output tpcb-test1.html
使用plprofiler生成的报表示例。
在页面顶部突出的是两个函数tpcb_fetch_balance()和它的调用者tpcb_upd_accounts()。可以看到它实际上占了PL/pgSQL函数内部总执行时间的99%以上。
为了进一步研究这个函数，报告中单击(show)链接显示详细信息，我们可以看到函数的源代码和每一行所花费的执行时间。
PGTune工具
PGTune可以根据给定硬件配置的最大性能计算PostgreSQL配置。对于初学者来说可以快速地来配置数据库参数。但它不是PostgreSQL优化设置的灵丹妙药。许多设置不仅取决于硬件配置，还取决于数据库的大小、客户端的数量和查询的复杂性。只有考虑到所有这些参数，才能对数据库进行最佳配置。
点击“生成”按钮后将计算结果。
源代码下载：
https://hypopg.readthedocs.io/en/rel1_stable/usage.html
https://github.com/orafce/orafce
https://pgtune.leopard.in.ua
https://github.com/markwkm/pg_top
https://github.com/bigsql/plprofiler
本文分享自微信公众号 - 开源软件联盟PostgreSQL分会（kaiyuanlianmeng）。
如有侵权，请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。
