postgreSql相关

文章没涉及太底层的原理，个人把觉得欠缺的和重要的以及发现的一些新鲜用法记录在这篇博客。

标题四和标题五为文章重点，不分先后。文章涉及索引的例子我创建了三百万条数据真实测试才写的。

一！安装

Linux如何安装PG可以参考网上搜的这篇文章：postgresql安装及常见错误处理_jack-zhu的博客-CSDN博客_postgresql安装错误

或者官网这篇：从源代码安装

就是安装时要是报没有readline   ubuntu 执行 apt-get install libreadline6-dev 就行了。 我安装的是9.6版本的PG

安装就不多写了。   .configure安装是可以指定参数安装一些额外的东西的。

二！概念性笔记

在数据库术语里，PostgreSQL使用一种客户端/服务器的模型。一次PostgreSQL会话由下列相关的进程（程序）组成：

• 一个服务器进程，它管理数据库文件、接受来自客户端应用与数据库的联接并且代表客户端在数据库上执行操作。 该数据库服务器程序叫做postgres。

• 那些需要执行数据库操作的用户的客户端（前端）应用。 客户端应用可能本身就是多种多样的：可以是一个面向文本的工具， 也可以是一个图形界面的应用，或者是一个通过访问数据库来显示网页的网页服务器，或者是一个特制的数据库管理工具。 一些客户端应用是和 PostgreSQL发布一起提供的，但绝大部分是用户开发的。

下面是我pg起来的进程。目前没啥连接。因为我根本没有用它。。。

来切换postgresql这个操作系统用户来进行创建数据库试试。因为我们安装时 useradd 的是 postgresql  所以数据库默认用户就是它了。

su - postgresql                               createdb testdb                            dropdb   testdb  就删库跑路了。

然后用psql命令进数据库里面去。就可以写SQL了。

\h    #查看所有的sql关键字
\?     #命令行操作的帮助
\d     #查看当前schema 中所有的表
\q    #退出pg命令行
\d    #schema.table 查看表的结构
\x    #横纵显示切换 
\dT+    #显示扩展类型相关属性及描述
\dx    #显示已安装的扩展插件
\l    #列出所有的数据库
\! hostname    #列出当前的主机名
\timing    #显示执行时间
\c database_name    #切换数据库

三！然后数据库SQL语句跟MySQL贼像，crud如出一辙。

建表用可视化工具就好了，贼方便。比如Navicat

select  *  from test;

delete from test;

insert into test(name,sex) values('test',0);

update test set name = 'zy';

还有这个copy命令。从文件导入到表和导出到文件(例如csv文件) COPY tableName  FROM '/home/user/name.txt';

COPY table_name [ ( column_name [, ...] ) ]
    FROM { 'filename' | PROGRAM 'command' | STDIN }
    [ [ WITH ] ( option [, ...] ) ]

COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
    TO { 'filename' | PROGRAM 'command' | STDOUT }
    [ [ WITH ] ( option [, ...] ) ]

测试一下。才有说服力。别忘了把权限chown给postgresql   然后给文件读写权限。

copy (SELECT * FROM "user") to '/test.csv' (format csv, delimiter ';');

copy user from '/test.csv' (format csv, delimiter ';');  把数据从文件复制回去。这里我将表里数据先删除再测试的。

copy tb_user from '/user.csv' (format csv, delimiter ';');  --user 这个表名不太行，我就换了个名，然后就好使了。数据也是存在的。我就不贴出来了。据说这个做大批量转储贼好用。

还有批量插入的方法。配合on  conflict

insert  into testTable (a,b)  select  a,b from testTable2  on  conflict  do nothing;  这个觉得很好用。正常插入肯定是要查存不存在的，存在就更新，不存在就插入。这个  on  conflict 就是这么玩的。  (上面这个SQL按需求写就行了。)

然后接下来就是连接了。其实跟MySQL也差不多。。。

from A left  outer  join B  on a.bid = b.id     相似的还有   right  outer  join  |   full  outer  join  | inner  join

然后其他的SQL语法跟MySQL差不多。就不赘述了。还有聚集函数，分组，子查询，having等等。

四！PG的一些SQL写法

1. 内连接  也可以不写inner 直接写join
inner 内连接  取交集
SELECT table1.columns, table2.columns  
FROM table1  
INNER JOIN table2  
ON table1.common_filed = table2.common_field

2.左外连接
left outer join 左外连接  返回左表所有行，并附带右表满足条件的行。
SELECT table1.columns, table2.columns  
FROM table1  
LEFT OUTER JOIN table2  
ON table1.common_filed = table2.common_field;

3.右外连接
right outer join 右外连接
SELECT table1.columns, table2.columns  
FROM table1  
RIGHT OUTER JOIN table2  
ON table1.common_filed = table2.common_field;

4.取并集
full outer join 取并集。另外表没有的列就显示空。不去除。
SELECT table1.columns, table2.columns  
FROM table1  
FULL OUTER JOIN table2  
ON table1.common_filed = table2.common_field;

5.销毁表数据
truncate table test;  这种清空的方式也不会重置自增序列，但是可以加参数。

delete from test;   删除数据分为这两种、delete不会重置自增序列。

PostgreSQL 中 TRUNCATE TABLE 用于删除表的数据，但不删除表结构。
也可以用 DROP TABLE 删除表，但是这个命令会连表的结构一起删除，如果想插入数据，需要重新建立这张表。
TRUNCATE TABLE 与 DELETE 具有相同的效果，但是由于它实际上并不扫描表，所以速度更快。 此外，TRUNCATE TABLE 可以立即释放表空间，而不需要后续 VACUUM 操作，这在大型表上非常有用。
PostgreSQL VACUUM 操作用于释放、再利用更新/删除行所占据的磁盘空间。

6.创建自增ID
CREATE SEQUENCE upms_log_id_seq START 1;  设计表时直接选serial就是自增了，会自动执行这个sql 

PG中设置自增ID   选择的类型为 serial  不选这个serial 就默认值设置为 nextval('upms_log_id_seq')

7.修改自增起始
alter sequence upms_log_id_seq restart with 1

8.添加/删除表的唯一性约束
ALTER TABLE table_name
ADD CONSTRAINT uniqueDouble UNIQUE(col1,col2);  //联合唯一索引
唯一约束的话实际上创建了唯一索引，这样做插入操作时想要去重，使用唯一约束的效率会提高很多。

ALTER TABLE table_name DROP COLUMN col1;  

ALTER TABLE table_name ADD CHECK (name = ''); 检查性约束。

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

9. 查索引大小
查指定索引的大小。
select pg_size_pretty(pg_relation_size('test_idx'));

10. show 配置项
show work_mem  等等 查看数据库配置  postgresql.conf 文件中配置。
select * from pg_stat_activity  查连接的详细信息。可以服务器诊断

11. 获取所有的函数信息
SELECT
  pg_proc.proname AS functionName,
  pg_type.typname AS voidType,
  pg_proc.pronargs AS paramNum
FROM  pg_proc  JOIN pg_type
ON (pg_proc.prorettype = pg_type.oid)
WHERE  pronamespace = (SELECT pg_namespace.oid FROM pg_namespace WHERE nspname = 'public')

12. 做数据插入时使用默认值

 INSERT INTO products (num, name, salary) VALUES (1, 'Cheese', DEFAULT); //插入时指定插入默认值。

13. 强制类型转换。cast();函数

select CAST('123' as FLOAT)/3;    --41      cast(expr as type)

14. 查数据库下所有模式以及所有表。
select * from pg_tables

15. 查看当前数据库所有触发器
SELECT * FROM pg_trigger

16. numeric值的计算在可能的情况下会得到准确的结果。
例如加法、减法、乘法。不过，numeric类型上的算术运算比整数类型或者下一节描述的浮点数类型要慢很多。
对于numeric来说指定比例在移植性看来不错，但是不指定比较灵活。就像varchar

17. 除了普通的数字值之外，浮点类型还有几个特殊值：
Infinity     正无穷
-Infinity    负无穷
NaN          不是一个数字

18.序数类型 serial 不是真正的数据类型，是创建唯一标识符而存在的符号。
CREATE TABLE tablename (
    colname SERIAL
);
等价于
CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;
序数是用序列实现的，所以即使没插入成功也会自增，而手动指定id，并不会使序列自增。

19. PG也有枚举类型。
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy'); 定义完枚举类型，建表的时候是可以使用的。CREATE TABLE

20. PG数组类型的玩法
"ids" integer[],
insert into salary_test(name,num,salary,zy_date,ids) values('zy',100,100000,date '2019-10-07','{1,2,3}');

select ids[1] from salary_test 取对应索引下标，奇怪的是下标1为起始。
相应的更新操作也是这么玩的。也可以指定更新 UPDATE salary_test set ids[2] = 100  

21. exists
EXISTS的参数是一个任意的SELECT语句， 或者说子查询。系统对子查询进行运算以判断它是否返回行。如果它至少返回一行，那么EXISTS的结果就为"真"； 如果子查询没有返回行，那么EXISTS的结果是"假"。

22. COALESCE
COALESCE()匹配第一个不为null的值,备选值必须与列的类型一样。

select COALESCE(salary,-1,0) from salary_table where id = 8 

23. 关于日期。

使用比较多的是时间戳 timestamp (默认没时区) 和 date 

24. case when end
SELECT salary, num, 
CASE WHEN num < 10 AND num > 4 
THEN 1 WHEN num > 10 THEN 2 ELSE 3 
END 
FROM salary_table

25. 可以写子查询的表达式
exists ()  in () not in () any () some () all ()

26. 造假数据generate_series(11,20,step)生成数。
insert into tb_user select generate_series(11,20),'zy'||random(),1,'hobby test';  --生成多行记录

27. 所有系统函数(锚点是指向了系统目录信息函数与库表索引相关)
http://www.postgres.cn/docs/9.6/functions-info.html#FUNCTIONS-INFO-CATALOG-TABLE

28. 系统管理函数(锚点指向锁)
http://www.postgres.cn/docs/9.6/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS-TABLE

29. 对于索引来说。

PG一共有六种类型的索引，经比较直接创建B-tree就好了。其余类型的索引使用场景较少。DDL默认使用的就是B-tree

30.当前只有B-tree能声明唯一索引。
不需要手工在唯一列上创建索引，如果那样做也只是重复了自动创建的索引而已。

31.删库
如果库被占用删不了的。查出占用的进程，然后kill -9 掉进程，就可以dropdb testdb了。

32.查看所有锁。
select* from pg_locks 

33.查看表的磁盘使用
SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = 'tablename';
每个页通常都是 8K 字节

34.按年月截取数据库中的数据

date_trunc ('month', zy_date) as zy_month 此时可以用他来分组。  对于一些  按day存储的数据想按月统计很方便。

35.结果集聚合成字符串，并以一个分隔符连接。

string_agg ( zy_NAME || '', '/' ) AS zy_name_res      用/来分割。
 

五！PG一些比较常用的用法

1.上面说过的copy算是一个特性。就不说了。

2. 窗口函数之排名 rank + over 

对于下面这个数据，我想取每个人工资第二高的，试想不使用函数算第一名容易，算这个第二名还是挺麻烦的。使用over和rank函数来计算就很方便了。

select id, name, salary,rank() over(PARTITION by name ORDER BY salary desc nulls last) FROM salary_table;

如上这个rank() over() 函数，其中over 中的partition by col 可以理解为通过某列分组，后面的排序就是按自己想要的排序就行，如上是按薪水降序。将null值放到最后。拿到这个结果集再取排名第几的就很方便了。(窗口函数在select中，注意执行顺序)

还可以使用window 来定义窗口函数，这样能复用了。

3. null与null是不一样的。即便你对某个字段加了唯一约束或者唯一索引，执行数据插入时对这个字段插入非null是可以保证限制的。一旦每次插入的都是null时，唯一限制起不到作用。因为每次插入的null是不一样的。

4. 分表、

CREATE TABLE son_12(CHECK ( in_date >= DATE '2019-12-01' AND in_date <= DATE '2019-12-31' )) INHERITS (parent);

建parent的分表。

父表上的所有检查约束和非空约束都将自动被它的后代所继承。其他类型的约束（唯一、主键和外键约束）则不会被继承。

被子表继承的东西，不能在子表删，子表存在父表不能删。(cascade能删，但是比较危险，鬼知道你都哪里用了)

salary_table是父表名。salart_02是子表名。

DROP TABLE salart_02;   删表。清除旧数据用的。
ALTER TABLE salart_02 NO INHERIT salary_table;  消除子表与父表的分区关系。

在数据量非常大的情况下，做按月分表就可以用以上这种方式。制造check检查性约束。(检查性约束会继承，因此每个表要创建按日期不同的检查性约束。)开启约束排除后，按照检查性约束where时指定筛选的日期就能去掉不在where中的分区，从而减少查询基数) 分区控制在一百以内。

对于分表时设置每个分表的check
((zy_date >= '2019-09-01'::date) AND (zy_date < '2019-10-01'::date))
注意：需要每个分表都设置这个不同日期的check 并且开启SET constraint_exclusion = on/partition; 否则不生效的。我测试了off是不生效的。

分表时父表的索引，约束可以被拷贝到子表。(如下命令父表中的索引与唯一约束会被拷贝到04子表。)
CREATE TABLE salary_04 (like salary_table including indexes) INHERITS (salary_table);

创建表时继承使用inherits关键字，而已有的表继承其他表使用inherit关键字。

5.父子表的关系

向子表插入的数据是在父表可以直接显示出来的，在子表/父表删除这个数据，都会使数据在两张表都不显示。
不是将数据又插入到了父表，而是通过继承关系使子表的数据在父表可见。

only关键字，在查询时加上，会只查对应表。如果数据真实在子表中，父表就查不出来了。

insert into salary_04(name,salary,num,zy_date) values('zzzy',10000,1,date '2019-05-20');

select * from only salary_table where name = 'zzzy'   ----null 不加only可以查出来、
select * from salary_table where name = 'zzzy'

注：相应的drop掉子表后，salary_table 父表中记录也被干掉了。drop table salary_05;

对于插入父表。不设置触发器等操作不会插入到那个子表。而插入子表的数据一定会在父表中显示。
查询查父表指定check的日期检索就可以快速查找，插入直接插入对应的子表中就行了。因为命名一般都是按日期命名的。代码里完全可以动态。

6. SQL执行顺序

大方向 from >where > group by > having > select  > distinct > order by > limit

7. UNION

这个也有用到。UNION 与 UNION ALL 的区别是前者去重，后者不去重。如果肯定没有重复的话使用后者会快一些。

这个是不保证顺序的。去重是按照结果集select出来的列联合去重的。

select name from salary_02 union SELECT name from salary_table ORDER BY name

8. 聚集函数 sum中使用  sum(case name = 'a' then salary else 0 end) 用来统计每个分组下的薪资总和。没有数据返回0

9.对于判断数据库中某一行是否存在(where条件会限定结果集只有一行)
使用select 1 来代替select col 或 select * 
select 1  1是常量不会查询字典表，所以效率要高于*与col

10.distinct 去重
在insert数据时，每次insert进入的null是不一样的。但是distinct去重中的null是相同的。

11.limit  offset 分页时offset越往后越慢。分页时要保证做一个排序。

12. WITH 查询

WITH sel1 as (
    select * from salary_table
), sel2 as (
    select * from sel1 where name = 'a'
)
select * from sel2 where num > 1;

这个就是牛逼版的子查询，可以复用。上面这个例子举得不好，但是语法表达了。这个不需要 ；在最最后加分号就行

13. WITH RECURSIVE 递归

这个递归还是很好用的。先展示一下表数据。下面这个树给你某个pid让你找所有下级或者所有上级、以pid为2为例。递归找所有pid为2的子节点。

                结果集=>               

WITH RECURSIVE sel1 as (
    select * from recursion where pid in (2)
    union all
    select son.* from sel1 as parent inner join recursion as son on son.pid = parent.id 
)
select * from sel1;

下面是向上递归

WITH RECURSIVE sel1 as (
    select * from recursion where id =4
    union all
    select son.* from sel1 as parent 
    inner join recursion as son 
    on parent.pid = son.id
)
select * from sel1;

解释：上面这个SQL---select * from recursion where id =4 最先被执行，结果塞入sel1中然后union all 递归斜体部分。

14. with 中写非查询语句
with sel1 as(
 delete  from salary_table where id>10
 returning *
)
insert into salary_01
SELECT * from sel1;

如下sql结果num=107 但是主查询sel1变成salary_table就查询到的是改动之前的值了。
with sel1 as(
 update salary_table set num = 107 where id = 1
 returning *
)
SELECT * from sel1 where id= 1;

如下sql结果num=107 但是主查询sel1变成salary_table就查询到的是改动之前的值了。
with sel1 as(
 update salary_table set num = 107 where id = 1
 returning *
)
SELECT * from sel1 where id= 1;

15.去重性能比对
select count(distinct col1,col2)
distinct 去重会将去重列全部内容存储在内存hash结构中，hash的key为col,  最后取key就可以了。
select count(1) from xx group by col1,col2;
而group by的方式是先将col排序。而数据库中的group一般使用sort的方法，即数据库会先对col进行排序。而排序的基本理论是，时间复杂为nlogn，空间为1，
然后只要单纯的计数就可以了。优点是空间复杂度小，缺点是要进行一次排序，执行时间会较长。

16.组合索引 (name,sex) name='zy' and sex= 1 可以触发索引，or不可以触发。

EXPLAIN ANALYZE select * from tb_user where name = 'zy1' or name = 'zy0'; 这种是可以触发索引的，触发了两次。将or两端的查询给拆开了。这么看上述的or没触发索引也解释通了。因为sex不能直接被索引。(联合索引的特性)

而就在我给sex又单独创建一个索引后，or两边的name和sex都成功走索引了。
有时候多列索引最好，但是有时更好的选择是创建单独的索引并依赖于索引组合特性。

17.可以创建表达式索引，表达式索引是对表达式的索引，因此使用场景并不是很多。
SELECT * FROM test1 WHERE lower(col1) = 'value';
CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));

18.部分索引。

指的是对某一列或者某些列的一定范围创建索引，只有where查询在这个范围才能用得上这个索引。没感觉咋好用。
CREATE INDEX zy_client_ip_ix ON testTable (client_ip)
WHERE NOT (client_ip > inet '192.168.100.0');

19.like&&ilike
匹配方式有_ % 两种。单个未知和多个未知、
like是区分大小写的。 like 'huawei%' 如果是 huaWei就不会被匹配到。
ilike是不区分大小写的。  ilike 'huawei%' 会匹配到 huaWEI 
like还可以复制表结构。上面那个父子表中就是使用like这个特性了。

20.user表

PG 有一个系统表叫user。  创建表为user后，查询我们自己创建的user 需要  public.user 指定，否则默认是系统表的user.

21.填充一个数据库
(1)可以取消事务的自动提交并将多个insert放到一个事务中，会提高插入效率。 
如果一个行的插入失败则所有值钱插入的行都会被回滚，这样你不会被卡在部分载入的数据中。
使用copy来填充文件内容到数据库是最快的。 插入时移除索引，插入完毕再加索引。(约束索引别这么玩) 

(2)增加maintenance_work_mem 增大这个参数，创建索引和外键会很快速。创建完再改回来。

(3)临时增大max_wal_size 
这是因为向PostgreSQL中载入大量的数据将导致检查点的发生比平常（由checkpoint_timeout配置变量指定）更频繁。无论何时发生一个检查点时，所有脏页都必须被刷写到磁盘上。 通过在批量数据载入时临时增加max_wal_size，所需的检查点数目可以被缩减。

(4)禁用WAL归档和流复制
当使用WAL归档或流复制向一个安装中载入大量数据时，在录入结束后执行一次新的基础备份比处理大量的增量 WAL 数据更快。为了防止载入时记录增量 WAL，通过将wal_level设置为minimal、将archive_mode设置为off以及将max_wal_senders设置为零来禁用归档和流复制。 但需要注意的是，修改这些设置需要重启服务。

22.数据更新较多可以时常重建索引，减少缺页分页。
对于B树索引，一个新建立的索引比更新了多次的索引访问起来要略快， 因为在新建立的索引上，逻辑上相邻的页面通常物理上也相邻（这样的考虑目前并不适用于非B树索引）。仅仅为了提高访问速度也值得定期重索引。

23.对于索引列设置

设置索引列为not null  并且给一个默认值。在创建索引的时候给顺序和null的排名last还是first。

因为给索引列加了排序，索引对于索引列的order by   group by  distinct操作都是可以走索引来加快速度的。

(ps:时间多可能是我在租的服务器部署的PG，网络带宽和IO的因素。)

六！进阶的知识点

0. postgresql的函数(触发器)操作

以ccid字段分表，

CREATE OR REPLACE FUNCTION client_insert_trigger()         ----创建函数。注意这个是创建function
RETURNS TRIGGER AS $$                                      ----定义返回类型为触发器 trigger    $$函数体
DECLARE t_content TEXT;                                    ----声明变量
DECLARE insert_statement TEXT;
BEGIN                                                      ----函数体开始部分
SELECT NEW.ccid INTO t_content;                            -----select  into  对变量进行赋值，这个是可以多值赋值的。
insert_statement := 'INSERT INTO client_' || t_content || ' VALUES($1.*)';           ---postgresql的字符串连接 || 符号
EXECUTE insert_statement USING NEW;                        ----执行sql 使用插入的数据行 NEW
RETURN NULL;   
EXCEPTION                                                  ----异常
WHEN UNDEFINED_TABLE                                       ----未定义表异常。这个是异常建分表的一个操作。
THEN
SELECT NEW.ccid INTO t_content;                                    
EXECUTE                                                                                ----执行下面这个创建分表的sql。并注明继承父表的索引。
'CREATE TABLE IF NOT EXISTS client_' || t_content ||' (CHECK (ccid='''|| t_content ||'''),like client including indexes ) INHERITS (client);';
EXECUTE insert_statement USING NEW;                         ----首次插入没有表。执行刚刚插入失败的数据，重新插入。
RETURN NULL;
END;                                                        ----函数体结束部分
$$                                                          ----$$函数体结束符号
LANGUAGE plpgsql;                                           ----加一个这个

DROP TRIGGER IF EXISTS insert_client_trigger ON client;        ------如果存在这个触发器就删掉他
CREATE TRIGGER insert_client_trigger BEFORE INSERT ON client   ------创建这个触发器 触发条件是往client表插入时触发
FOR EACH ROW                                                   ------对于每一行数据
EXECUTE PROCEDURE client_insert_trigger();                     ------执行 存储过程(函数) 这个就是上面这个定义的函数
 

1.关于事务隔离级别以及脏读，不可重复读，幻读

事务的隔离级别分为1.读未提交，2.读已提交，3.可重复读，4.序列化
1会导致脏读，会导致数据库数据错乱，是不被允许的。
2会导致不可重复读，就是同一个事务两次读取的数据是不一致的，在数据的层面。update触发  行数--结果集是不变的。
如果整个事务读和修改是分开的，那么就不存在这个问题了。 
3会导致幻读，也是同一个事务两次读取的数据是不一致的，是在数据结果集的层面。insert delete触发  并发事务在update修改相同行，本事务就会回滚。
幻读和不可重复读影响差不多。数据都是异常的。    而数据库中数据是正常的，读出来的数据异常。
4序列化因为读出记录不对也是会事务回滚，所以不会出现上述问题。

2.PG的一些重要的系统参数

(0)设置max_worker_processes 设置系统能够支持的后台进程的最大数量。这个参数只能在服务器启动时设置。 默认值为 8。

(1)shared_buffers：这是最重要的参数，postgresql通过shared_buffers和内核和磁盘打交道，因此应该尽量大，让更多的数据缓存在shared_buffers中。
一个合理的shared_buffers开始值是系统内存的 25%。
为了能把对写大量新的或改变的数据的处理分布在一个较长的时间段内， shared_buffers更大的 设置通常要求对max_wal_size也做相应增加。

(2)work_mem： postgresql在执行排序操作时，会根据work_mem的大小决定是否将一个大的结果集拆分为几个小的和 work_mem查不多大小的临时文件。显然拆分的结果是降低了排序的速度。因此增加work_mem有助于提高排序的速度。通常设置为实际RAM的2% -4%，根据需要排序结果集的大小而定，比如81920(80M)
指定在写到临时磁盘文件之前被内部排序操作和哈希表使用的内存量。该值默认为四兆字节（4MB）。注意对于一个复杂查询， 可能会并行运行好几个排序或者哈希操作；每个操作都会被允许使用这个参数指定的内存量，然后才会开始写数据到临时文件。同样，几个正在运行的会话可能并发进行这样的操作。
因此被使用的总内存可能是work_mem值的好几倍，在选择这个值时一定要记住这一点。
ORDER BY、DISTINCT和归并连接都要用到排序操作。哈希连接、基于哈希的聚集以及基于哈希的IN子查询处理中都要用到哈希表。

(3)effective_cache_size：是postgresql能够使用的最大缓存，这个数字对于独立的pgsql服务器而言应该足够大，比如4G的内存，可以设置为3.5G(437500)

(4)maintence_work_mem：
指定在维护性操作（例如VACUUM、CREATE INDEX和ALTER TABLE ADD FOREIGN KEY）中使用的 最大的内存量。其默认值是 64 兆字节（64MB）。因为在一个数据库会话中，一个时刻只有一个这样的操作可以被执行，并且一个数据库安装通常不会有太多这样的操作并发执行， 把这个数值设置得比work_mem大很多是安全的。 更大的设置可以改进清理和恢复数据库转储的性能。

注意当自动清理运行时，可能会分配最高达这个内存的 autovacuum_max_workers倍， 因此要小心不要把该默认值设置得太高。 通过独立地设置autovacuum_work_mem 可能会对控制这种情况有所帮助。

(5)max_connections： 通常，max_connections的目的是防止max_connections * work_mem超出了实际内存大小。比如，如果将work_mem设置为实际内存的2%大小，则在极端情况下，如果有50个查询都有排序要求，而且都使 用2％的内存，则会导致swap的产生，系统性能就会大大降低。当然，如果有4G的内存，同时出现50个如此大的查询的几率应该是很小的。不过，要清楚 max_connections和work_mem的关系。

最后一点：学PG可以使用explain   并时常 explain  analyze  select  通过分析进行SQL优化、

参考PG中文文档：PostgreSQL 9.6.0 手册