postgresql数据库常见优化参数
配置参数调优
配置参数调优用于修改pg工作参数，一般在安装完数据库软件以后应立即进行着手调整，该参数文件名称为postgresql.conf，参数内容修改后需要重启数据库软件才能生效。
如果不确定每一个参数的用途或者想偷懒，可以使用PG在线配置参数优化工具进行配置：PGTune - calculate configuration for PostgreSQL
连接数量
该参数用于控制最大连接到PG数据库的客户端连接总数，默认安装的pg最大连接参数为100，需要根据服务器内存大小及内存配置参数进行调整，如调整为1000。
max_connections = 1000
work_mem
该参数用于为单条SQL执行分配最大内存，当内存不够用时会出现写硬盘临时文件的情况，但是需要注意一条SQL内如果有多个order by话，每一个order by单独按照work_mem配置值占用内存。
默认情况下work_mem配置为4M内存，当涉及到较大数据量的查询、排序等操作时4M内存不够用时就会出现写硬盘临时数据的情况，磁盘IO一般都比较慢，可以适度调大该参数，具体调整为多少取决于SQL的复杂程度，建议设置work_mem为16M以上的数值，同时配合log_temp_files配置参数观察SQL查询是否经常出现写硬盘临时文件的情况。
postgres=# EXPLAIN ANALYZE SELECT id, name FROM people_warm ORDER BY id;
QUERY PLAN
Gather Merge (cost=5019047.44..9880499.52 rows=41666680 width=37) (actual time=567717.158..594007.752 rows=50000000 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=5018047.42..5070130.77 rows=20833340 width=37) (actual time=566715.013..570783.692 rows=16666667 loops=3)
Sort Key: id
Sort Method: external merge Disk: 767080kB #注意看这里,work_mem内存不够用了,查询计划选择了使用磁盘写临时文件的方式进行了排序操作
Worker 0: Sort Method: external merge Disk: 765288kB
Worker 1: Sort Method: external merge Disk: 767304kB
-> Parallel Seq Scan on people_warm (cost=0.00..776515.40 rows=20833340 width=37) (actual time=0.055..383585.941 rows=16666667 loops=3)
Planning Time: 0.047 ms
Execution Time: 597556.456 ms
建议配置值：
work_mem=16MB
shared_buffers
shared_buffers里面一般用于缓存表、索引、执行计划等数据，数据库引擎查找数据时会优先在shared_buffers里面进行查找，建议配置为物理内存的1/4大小。
shared_buffers=20GB
effective_cache_size
该参数不真实占用物理内存，属于查询优化器的一个估算参考值，用于确定查询数据时是走索引还是顺序扫描全表数据，一般配置为物理内存大小的1/2大小。
effective_cache_size=40GB
max_worker_processes
改参数用于控制最大并发查询数量，默认值为8，建议调整为128或其他值。
max_worker_processes=128
maintenance_work_mem
该参数用于指明数据库创建索引或autovacuum(自动清理统计)等管理维护进程的可用内存上限。
maintenance_work_mem=2GB
checkpoint_completion_target
简单说该参数用于控制数据多久刷新写盘，写盘太频繁的话会降低系统IO效率，具体细节可以参考PgSQL · 特性分析 · 谈谈checkpoint的调度 (taobao.org)
checkpoint_completion_target=0.7
wal_buffers
该参数默认为shared_buffers的值除以32，即取其32分之1，如果自动计算结果小于16M，可以直接配置为16MB
wal_buffers = 16MB
max_wal_size
该参数用于控制pg里面的wal日志最大占用多大磁盘空间。wal日志是用于数据回滚或主从流复制的关键核心，甚至pg对表里面的数据进行更改以后，保存在shared_buffers里面的脏页在事务提交后不是实时写盘，而是先确保写wal成功以后再定期同步真实表数据的。在出现异常时pg会根据wal日志进行回滚操作。该参数默认为1G，可以适度调大该参数到8G，关于该参数的介绍资料:
max_wal_size = 8GB
慢SQL记录
log_statement = all #需设置跟踪所有语句，否则只能跟踪出错信息
log_min_duration_statement = 10000 #milliseconds,记录执行10秒及以上的语句
运维检查
查看当前任务
SELECT
procpid,
start,
now() - start AS lap,
current_query
FROM
(SELECT
backendid,
pg_stat_get_backend_pid(S.backendid) AS procpid,
pg_stat_get_backend_activity_start(S.backendid) AS start,
pg_stat_get_backend_activity(S.backendid) AS current_query
FROM
(SELECT pg_stat_get_backend_idset() AS backendid) AS S
) AS S
WHERE
current_query <> ''
ORDER BY lap DESC;
PG_TOP
pgtop需要单独安装，可以下载与服务器CPU指令集对应的安装包手动进行安装，使用时切到postgres用户，然后再运行pg_top即可。
查看磁盘IO繁忙程度
先安装sysstat软件包，然后iostat -x 1 查看IO使用率。
查看执行计划
EXPLAIN ANALYZE 你的查询SQL
以下举例说明执行计划如何查看
explain ANALYZE with recursive tbl_result as (
select dept_id from admin_dept where dept_id=13
union all
select t2.dept_id from tbl_result t1 join admin_dept t2 on t1.dept_id=t2.parent_id
)
select count(0) as zs from admin_employee a inner join tbl_result b on a.main_dept_id=b.dept_id
得到结果如下：
屏幕最下方的Execution Time :60.772ms说明整个查询执行耗时60毫秒，屏幕顶部的(cost=18980.91..18980.92 rows=1 width=8) 中的第一个数值18980.91代表返回第一条数据时的开销成本总和，第二个数值18980.92代表返回所有数据的成本开销，这个成本开销的具体计算权重可以看这里：
- Planner Cost Constants -
#seq_page_cost = 1.0 # measured on an arbitrary scale。扫描一个数据块（一页）的成本（IO成本）
#random_page_cost = 4.0 # same scale as above。随机获取一个数据块（一页）的成本（IO成本）
#cpu_tuple_cost = 0.01 # same scale as above。获取一行数据的CPU成本
#cpu_index_tuple_cost = 0.005 # same scale as above。获取一个索引项的CPU成本
#cpu_operator_cost = 0.0025 # same scale as above。每个操作符的CPU成本
actual time是真实执行查询的时间开销，单位毫秒，两个数值代表该任务的开始和结束时间，如 -> Nested Loop (cost=0.43..4874.91 rows=35082 width=0) (actual time=0.037..52.871 rows=65737 loops=1) 说明开始时间是0.037毫秒,执行完该任务是52.871毫秒，因为整个查询耗时60.772毫秒，而这一步就耗时了52.871-0.037=52.834毫秒，如果有优化空间的话就可以优先考虑优化该部分的SQL内容。
