postgresql在工作中
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 很详细 很强大
后续跟新。。。。。。。
页:
[1]