postgresql在工作中

killerxf

sql语句15个常用的语句：
　　1.select>　　2.selecr pg_database_size('数据库名')
　　3.select pg_size_pretty(pg_total_relation_size('表名‘))
　　4.\d 表名
　　5.create index name on table using index_type(colum)
　　6.rollback,commit
　　7.explain query
　　8.explain analyze query
　　9.insert into number(num) values (generate_series(1,1000))
　　10.select count(*) from table ,select count(col_name) from table,
　　select count(distinct 列名) from table
　　11.slect MAX(num) from number_table where num
　　12.select MIN(col_name) from number_table where num >(select MIN(num) from number_table)
　　13.select typname,typlen from pg_type where typtype = 'b'
　　14.\g output_file,select * from table_name,\g
　　15.加密：select crypt（'sathiys',gen_salt('md5'));
　　16.insert into WU(a1,b1) select generate_series(1,1000000),'aa';
　　17.select name,setting,short_desc from pg_settings where name like '%scan';
　　参考：http://www.zlovezl.cn/articles/15-advanced-postgresql-commands-with-examples/
　　工作上：
　　给了40多个函数，每个都跑一遍，找出运行时间过长的，并把进行优化，
　　前提：用的pg 8.2版本，函数内查询的数据量比较大，所以排除索引方法
　　gld_pdms_wula 运行要100多分钟 优化后20分钟
　　方法：分别测试函数内每个查询语句，找到花费时间最多的那段代码，吧代码拿出来放在sql编辑器里，对查询的字段和表连接分别进行explain，查看执行计划
　　，可以对代码进行分层explain，比如查询语句中有5个表连接，你可以查看最上面两个表的explain中的cost，依次连接称下的表，找到代码里
　　cost最大的那段。我发现where中连接一个日历表，cost最大，那我就查看为什呢要连接这个表，需要表的什么字段，看看能不能不要这个表用其他的字段代替，结果发现是可以的，于是就拿掉这个表，结果优化成功
　　gld_wula_analyze 运行需要12分钟 优化后3分钟：同样的查找代码的方法，找到where中的子查询cost最大，查看子查询的优化方法，吧子查询部分拿出来重新写个
　　查询语句，符合业务逻辑，行数也是一样，优化成功。
　　还有几个表没有优化成功，那些表运行需要6-8分钟，因为代码很短，查询的数据量又大，查询用到的表又大，全表扫描必定占用时间，代码上也没有可以的优化的，主要是要符合业务逻辑，优化失败。
　　这些过程中用到的知识：
　　1.刚开始需要学习pg的执行计划
　　http://my.oschina.net/Kenyon/blog/75757 很详细
　　http://blog.itpub.net/24484596/viewspace-730958/ 很直观
　　http://my.oschina.net/congqian/blog/135872
　　http://jackwxh.blog.iyunv.com/2850597/1311341
　　强制修改计划
　　http://blog.csdn.net/hantiannan/article/details/4517192
　　看执行计划优化
　　http://www.iyunv.com/database/201306/217900.html
　　2.首先想到的优化方法是建立索引（有时候查询语句走索引未必比全表扫描的快）
　　http://blog.csdn.net/vernonzheng/article/details/8079207 很详细
　　http://blog.iyunv.com/uid-7655508-id-3637972.html 介绍不走的索引原因及改进
　　http://dreamer-yzy.github.io/2015/01/17/-翻译-高效使用PostgreSQL索引/ 一个pgDBA的博客
　　http://francs3.blog.163.com/blog/static/4057672720125218561360/ 函数索引和联合索引
　　错误：一股脑的将where中用到的列都建立索引，结果没有任何效果，查看explain 优化器根本不走索引。
　　其中我问过公司DBA，知道pg的autovacuum是关闭的，这样会导致当一个表insert，delete过多数据时，表的信息不能及时跟新反馈给pg的优化器，
　　可以使用分析表：analyze table_name,于是对那些函数使用这个办法，失败，查询的数据量比较大，即使数据量只有10% 也不走，到现在我也不知道原因，只能归咎于公司的gp架构了。
　　（gp就是greenplum，分布式数据库，有主节点（master），分节点，表的建立要有分布键DISTRIBUTED BY(列名)）
　　3.走了一个弯路，之后就从代码上下手
　　(1)http://www.jasongj.com/2015/03/15/count_distinct/   Count distinct 优化
　　(2)http://www.jasongj.com/2015/03/07/Join1/#Query_1_Test_1：_查询优化器自动选择Nested_Loop，耗时784-845_ms  不等连接
　　(3)http://www.cnblogs.com/mchina/archive/2013/04/09/2973427.html 分区表的应用，很好有实例
　　http://blog.iyunv.com/uid-24774106-id-3887099.html 发现gp架构不需要对表建立分区,(猜测分布键的原因)
　　4.没有可优化返回看看pg的sql知识
　　http://blog.csdn.net/cdnight/article/category/2128135 一个人博客
　　http://www.jsjtt.com/shujuku/postgresql/15.html，http://cuijiemin.iyunv.com/blog/940927 Postgresql 数据类型转换
　　http://www.cnblogs.com/mchina/archive/2013/04/15/3010418.html 日期
　　5.PostgreSQL的经典语句（转）
　　http://jeff1987.blog.iyunv.com/910685/271142 很全很强大
　　6.最后看一些pg的性能
　　http://my.oschina.net/121002123/blog/124118
　　7.Postgresql 专业博客
　　http://blog.163.com/digoal@126/blog/static/163877040201221382150858/那海蓝蓝
　　http://www.cnblogs.com/stephen-liu74/archive/2012/06/06/2312759.html
　　http://blog.163.com/li_hx/blog/static/183991413201310634345275
　　http://my.oschina.net/Kenyon/blog/54376
　　http://blog.163.com/digoal@126/ 德哥 灰常不错
　　Postgresql 专业博客总结
　　http://francs3.blog.163.com/blog/static/405767272014017341219/ 很好很强大
　　8.MYSQL 和postgresql 的区别
　　http://www.postgresql.pw/thread-13443-1-2.html 很详细 很强大
　　后续跟新。。。。。。。