postgresql 性能优化
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了postgresql 性能优化，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含10042字，纯文字阅读大概需要15分钟。
内容图文
order_date,2 order_source,3 SUM(commodity_num) num,4 SUM(actual_charge) charge5 FROM (6 SELECT to_char(oc.create_date, ‘yyyyMMdd‘) AS order_date,7 (CASE8 WHEN oo.event_type = ‘ONLINE_COMMODITY_ORDER‘ THEN9 ‘线上‘10 ELSE11 ‘线下‘12 END) order_source,13 oc.commodity_num,14 oc.actual_charge actual_charge15 FROM ord.ord_commodity_hb_2017 AS oc, ord.ord_order_hb_2017 AS oo16 WHERE oc.order_id = oo.order_id17 AND oc.op_type = 3 -- 3个值 ，3->5000 大概1/20的数据18 AND oc.create_date BETWEEN ‘2017-02-05‘ AND ‘2017-12-07‘ -- 无用19 AND oc.corp_org_id = 106 -- 无用20 AND oo.trade_state = 11 -- 3个值 11 --> 71万行，一半数据21 AND oo.event_type IN (values(‘ONLINE_COMMODITY_ORDER‘),22 (‘USER_CANCEL‘),23 (‘USER_COMMODITY_UPDATE‘)) -- 大概1/10 数据24 ORDER BY oc.create_date -- 如果业务不强制，最好去掉排序，如果不能去掉，最好等过滤数据量到尽量小时再排序25 ) T26GROUP BY order_date, order_source; 下面默认以postgresql为例：
一、排序:
1. 尽量避免2. 排序的数据量尽量少，并保证在内存里完成排序。
（至于具体什么数据量能在内存中完成排序，不同数据库有不同的配置：
oracle是sort_area_size；
postgresql是work_mem (integer)，单位是KB，默认值是4MB。
mysql是sort_buffer_size 注意：该参数对应的分配内存是每连接独占！
）
二、索引：
1. 过滤的数据量比较少，一般来说<20%,应该走索引。20%-40% 可能走索引也可能不走索引。> 40% ，基本不走索引(会全表扫描)2. 保证值的数据类型和字段数据类型要一直。
3. 对索引的字段进行计算时，必须在运算符右侧进行计算。也就是 to_char(oc.create_date, ‘yyyyMMdd‘)是没用的
4. 表字段之间关联，尽量给相关字段上添加索引。
5. 复合索引，遵从最左前缀的原则,即最左优先。（单独右侧字段查询没有索引的）
三、连接查询方式：
1、hash join 放内存里进行关联。
适用于结果集比较大的情况。
比如都是200000数据
2、nest loop
从结果1 逐行取出，然后与结果集2进行匹配。
适用于两个结果集，其中一个数据量远大于另外一个时。
结果集一：1000
结果集二：1000000
四、多表联查时：
在多表联查时，需要考虑连接顺序问题。1、当postgresql中进行查询时，如果多表是通过逗号，而不是join连接，那么连接顺序是多表的笛卡尔积中取最优的。如果有太多输入的表， PostgreSQL规划器将从穷举搜索切换为基因概率搜索，以减少可能性数目(样本空间)。基因搜索花的时间少， 但是并不一定能找到最好的规划。
2、对于JOIN，
LEFT JOIN / RIGHT JOIN 会一定程度上指定连接顺序，但是还是会在某种程度上重新排列：
FULL JOIN 完全强制连接顺序。 如果要强制规划器遵循准确的JOIN连接顺序，我们可以把运行时参数join_collapse_limit设置为 1
五、PostgreSQL提供了一些性能调优的功能：
优化思路： 0、为每个表执行 ANALYZE <table>。然后分析 EXPLAIN (ANALYZE，BUFFERS) sql。
1、对于多表查询，查看每张表数据，然后改进连接顺序。
2、先查找那部分是重点语句，比如上面SQL，外面的嵌套层对于优化来说没有意义，可以去掉。
3、查看语句中，where等条件子句，每个字段能过滤的效率。找出可优化处。
比如oc.order_id = oo.order_id是关联条件，需要加索引
oc.op_type = 3 能过滤出1/20的数据，
oo.event_type IN (...) 能过滤出1/10的数据，
这两个是优化的重点，也就是实现确保op_type与event_type已经加了索引，其次确保索引用到了。
优化方案：
a) 整体优化：
1、使用EXPLAINEXPLAIN命令可以查看执行计划，这个方法是我们最主要的调试工具。
2、及时更新执行计划中使用的统计信息
由于统计信息不是每次操作数据库都进行更新的，一般是在 VACUUM 、 ANALYZE 、 CREATE INDEX等DDL执行的时候会更新统计信息，
因此执行计划所用的统计信息很有可能比较旧。 这样执行计划的分析结果可能误差会变大。
以下是表tenk1的相关的一部分统计信息。
SELECT relname, relkind, reltuples, relpages
FROM pg_class
WHERE relname LIKE ‘tenk1%‘;
relname | relkind | reltuples | relpages
----------------------+---------+-----------+----------
tenk1 | r | 10000 | 358
tenk1_hundred | i | 10000 | 30
tenk1_thous_tenthous | i | 10000 | 30
tenk1_unique1 | i | 10000 | 30
tenk1_unique2 | i | 10000 | 30
(5 rows)SELECT relname, relkind, reltuples, relpages2FROM pg_class3WHERE relname LIKE ‘tenk1%‘;4 relname | relkind | reltuples | relpages6----------------------+---------+-----------+----------7 tenk1 | r | 10000 | 3588 tenk1_hundred | i | 10000 | 309 tenk1_thous_tenthous | i | 10000 | 3010 tenk1_unique1 | i | 10000 | 3011 tenk1_unique2 | i | 10000 | 3012(5 rows)其中 relkind是类型，r是自身表，i是索引index；reltuples是项目数；relpages是所占硬盘的块数。估计成本通过 （磁盘页面读取【relpages】*seq_page_cost）+（行扫描【reltuples】*cpu_tuple_cost）计算。默认情况下， seq_page_cost是1.0，cpu_tuple_cost是0.01。
| 名字 | 类型 | 描述 |
|---|---|---|
| relpages | int4 | 以页(大小为BLCKSZ)的此表在磁盘上的形式的大小。 它只是规划器用的一个近似值，是由VACUUM,ANALYZE 和几个 DDL 命令，比如CREATE INDEX更新。 |
| reltuples | float4 | 表中行的数目。只是规划器使用的一个估计值，由VACUUM,ANALYZE 和几个 DDL 命令，比如CREATE INDEX更新。 |
3、使用临时表（with）对于数据量大，且无法有效优化时，可以使用临时表来过滤数据，降低数据数量级。
4、对于会影响结果的分析，可以使用 begin;...rollback;来回滚。
b) 查询优化：
1、明确用join来关联表，确保连接顺序一般写法：SELECT * FROM a, b, c WHERE a.id = b.id AND b.ref = c.id;
如果明确用join的话，执行时候执行计划相对容易控制一些。
例子：
SELECT * FROM a CROSS JOIN b CROSS JOIN c WHERE a.id = b.id AND b.ref = c.id;
SELECT * FROM a JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);
c) 插入更新优化
1、关闭自动提交（autocommit=false）如果有多条数据库插入或更新等，最好关闭自动提交，这样能提高效率
2、多次插入数据用copy命令更高效
我们有的处理中要对同一张表执行很多次insert操作。这个时候我们用copy命令更有效率。因为insert一次，其相关的index都要做一次，比较花费时间。
3、临时删除index【具体可以查看Navicat表数据生成sql的语句，就是先删再建的】
有时候我们在备份和重新导入数据的时候，如果数据量很大的话，要好几个小时才能完成。这个时候可以先把index删除掉。导入后再建index。
4、外键关联的删除
如果表的有外键的话，每次操作都没去check外键整合性。因此比较慢。数据导入后再建立外键也是一种选择。
d) 修改参数：
| 选项 | 默认值 | 说明 | 是否优化 | 原因 |
| max_connections | 100 | 允许客户端连接的最大数目 | 否 | 因为在测试的过程中，100个连接已经足够 |
| fsync | on | 强制把数据同步更新到磁盘 | 是 | 因为系统的IO压力很大，为了更好的测试其他配置的影响，把改参数改为off |
| shared_buffers | 24MB | 决定有多少内存可以被PostgreSQL用于缓存数据（推荐内存的1/4) | 是 | 在IO压力很大的情况下，提高该值可以减少IO |
| work_mem | 1MB | 使内部排序和一些复杂的查询都在这个buffer中完成 | 是 | 有助提高排序等操作的速度，并且减低IO |
| effective_cache_size | 128MB | 优化器假设一个查询可以用的最大内存，和shared_buffers无关（推荐内存的1/2) | 是 | 设置稍大，优化器更倾向使用索引扫描而不是顺序扫描 |
| maintenance_work_mem | 16MB | 这里定义的内存只是被VACUUM等耗费资源较多的命令调用时使用 | 是 | 把该值调大，能加快命令的执行 |
| wal_buffer | 768kB | 日志缓存区的大小 | 是 | 可以降低IO，如果遇上比较多的并发短事务，应该和commit_delay一起用 |
| checkpoint_segments | 3 | 设置wal log的最大数量数（一个log的大小为16M） | 是 | 默认的48M的缓存是一个严重的瓶颈，基本上都要设置为10以上 |
| checkpoint_completion_target | 0.5 | 表示checkpoint的完成时间要在两个checkpoint间隔时间的N%内完成 | 是 | 能降低平均写入的开销 |
| commit_delay | 0 | 事务提交后，日志写到wal log上到wal_buffer写入到磁盘的时间间隔。需要配合commit_sibling | 是 | 能够一次写入多个事务，减少IO，提高性能 |
| commit_siblings | 5 | 设置触发commit_delay的并发事务数，根据并发事务多少来配置 | 是 | 减少IO，提高性能 |
| autovacuum_naptime | 1min | 下一次vacuum任务的时间 | 是 | 提高这个间隔时间，使他不是太频繁 |
| autovacuum_analyze_threshold | 50 | 与autovacuum_analyze_scale_factor配合使用，来决定是否analyze | 是 | 使analyze的频率符合实际 |
| autovacuum_analyze_scale_factor | 0.1 | 当update,insert,delete的tuples数量超过autovacuum_analyze_scale_factor*table_size+autovacuum_analyze_threshold时，进行analyze。 | 是 | 使analyze的频率符合实际 |
下面介绍几个我认为重要的：1、增加maintenance_work_mem参数大小
增加这个参数可以提升CREATE INDEX和ALTER TABLE ADD FOREIGN KEY的执行效率。
2、增加checkpoint_segments参数的大小
增加这个参数可以提升大量数据导入时候的速度。
3、设置archive_mode无效
这个参数设置为无效的时候，能够提升以下的操作的速度
?CREATE TABLE AS SELECT
?CREATE INDEX
?ALTER TABLE SET TABLESPACE
?CLUSTER等。
4、autovacuum相关参数
autovacuum：默认为on，表示是否开起autovacuum。默认开起。特别的，当需要冻结xid时，尽管此值为off，PG也会进行vacuum。
autovacuum_naptime：下一次vacuum的时间，默认1min。 这个naptime会被vacuum launcher分配到每个DB上。autovacuum_naptime/num of db。
log_autovacuum_min_duration：记录autovacuum动作到日志文件，当vacuum动作超过此值时。 “-1”表示不记录。“0”表示每次都记录。
autovacuum_max_workers：最大同时运行的worker数量，不包含launcher本身。
autovacuum_work_mem ：每个worker可使用的最大内存数。autovacuum_vacuum_threshold ：默认50。与autovacuum_vacuum_scale_factor配合使用， autovacuum_vacuum_scale_factor默认值为20%。当update,delete的tuples数量超过autovacuum_vacuum_scale_factor*table_size+autovacuum_vacuum_threshold时，进行vacuum。如果要使vacuum工作勤奋点，则将此值改小。 autovacuum_analyze_threshold ：默认50。与autovacuum_analyze_scale_factor配合使用。autovacuum_analyze_scale_factor ：默认10%。当update,insert,delete的tuples数量超过autovacuum_analyze_scale_factor*table_size+autovacuum_analyze_threshold时，进行analyze。 autovacuum_freeze_max_age：200 million。离下一次进行xid冻结的最大事务数。 autovacuum_multixact_freeze_max_age：400 million。离下一次进行xid冻结的最大事务数。
autovacuum_vacuum_cost_delay ：如果为-1，取vacuum_cost_delay值。 autovacuum_vacuum_cost_limit ：如果为-1，到vacuum_cost_limit的值，这个值是所有worker的累加值。
postgresql 性能优化
标签：避免 table 穷举 图片 launch cursor 客户 star rda
本文系统来源：https://www.cnblogs.com/churao/p/8494324.html
内容总结
以上是互联网集市为您收集整理的postgresql 性能优化全部内容，希望文章能够帮你解决postgresql 性能优化所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】