① 如何提高postgresql查询性能
一、使用EXPLAIN：
PostgreSQL为每个查询都生成一个查询规划，因为选择正确的查询路径对性能的影响是极为关键的。PostgreSQL本身已经包含了一个规划器用于寻找最优规划，我们可以通过使用EXPLAIN命令来查看规划器为每个查询生成的查询规划。
PostgreSQL中生成的查询规划是由1到n个规划节点构成的规划树，其中最底层的节点为表扫描节点，用于从数据表中返回检索出的数据行。然而，不同
的扫描节点类型代表着不同的表访问模式，如：顺序扫描、索引扫描，以及位图索引扫描等。如果查询仍然需要连接、聚集、排序，或者是对原始行的其它操作，那
么就会在扫描节点"之上"有其它额外的节点。并且这些操作通常都有多种方法，因此在这些位置也有可能出现不同的节点类型。EXPLAIN将为规划树中的每
个节点都输出一行信息，显示基本的节点类型和规划器为执行这个规划节点计算出的预计开销值。第一行(最上层的节点)是对该规划的总执行开销的预计，这个数
值就是规划器试图最小化的数值。
这里有一个简单的例子，如下：
复制代码 代码如下:
EXPLAIN SELECT * FROM tenk1;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=244)
EXPLAIN引用的数据是：
1). 预计的启动开销(在输出扫描开始之前消耗的时间，比如在一个排序节点里做排续的时间)。
2). 预计的总开销。
3). 预计的该规划节点输出的行数。
4). 预计的该规划节点的行平均宽度(单位：字节)。
这里开销(cost)的计算单位是磁盘页面的存取数量，如1.0将表示一次顺序的磁盘页面读取。其中上层节点的开销将包括其所有子节点的开销。这里的输出
行数(rows)并不是规划节点处理/扫描的行数，通常会更少一些。一般而言，顶层的行预计数量会更接近于查询实际返回的行数。
现在我们执行下面基于系统表的查询：
复制代码 代码如下:
SELECT relpages, reltuples FROM pg_class WHERE relname = 'tenk1';
从查询结果中可以看出tenk1表占有358个磁盘页面和10000条记录，然而为了计算cost的值，我们仍然需要知道另外一个系统参数值。
复制代码 代码如下:
postgres=# show cpu_tuple_cost;
cpu_tuple_cost
----------------
0.01
(1 row)
cost = 358(磁盘页面数) + 10000(行数) * 0.01(cpu_tuple_cost系统参数值)
下面我们再来看一个带有WHERE条件的查询规划。
复制代码 代码如下:
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 7000;
QUERY PLAN
------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..483.00 rows=7033 width=244)
Filter: (unique1 < 7000)
EXPLAIN的输出显示，WHERE子句被当作一个"filter"应用，这表示该规划节点将扫描表中的每一行数据，之后再判定它们是否符合过滤的条
件，最后仅输出通过过滤条件的行数。这里由于WHERE子句的存在，预计的输出行数减少了。即便如此，扫描仍将访问所有10000行数据，因此开销并没有
真正降低，实际上它还增加了一些因数据过滤而产生的额外CPU开销。
上面的数据只是一个预计数字，即使是在每次执行ANALYZE命令之后也会随之改变，因为ANALYZE生成的统计数据是通过从该表中随机抽取的样本计算的。
如果我们将上面查询的条件设置的更为严格一些的话，将会得到不同的查询规划，如：
复制代码 代码如下:
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;
QUERY PLAN
------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=2.37..232.35 rows=106 width=244)
Recheck Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0)
Index Cond: (unique1 < 100)
这里，规划器决定使用两步规划，最内层的规划节点访问一个索引，找出匹配索引条件的行的位置，然后上层规划节点再从表里读取这些行。单独地读取数据行比顺
序地读取它们的开销要高很多，但是因为并非访问该表的所有磁盘页面，因此该方法的开销仍然比一次顺序扫描的开销要少。这里使用两层规划的原因是因为上层规
划节点把通过索引检索出来的行的物理位置先进行排序，这样可以最小化单独读取磁盘页面的开销。节点名称里面提到的"位图(bitmap)"是进行排序的机
制。
现在我们还可以将WHERE的条件设置的更加严格，如：
复制代码 代码如下:
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 3;
QUERY PLAN
------------------------------------------------------------------------------
Index Scan using tenk1_unique1 on tenk1 (cost=0.00..10.00 rows=2 width=244)
Index Cond: (unique1 < 3)
在该SQL中，表的数据行是以索引的顺序来读取的，这样就会令读取它们的开销变得更大，然而事实上这里将要获取的行数却少得可怜，因此没有必要在基于行的物理位置进行排序了。
现在我们需要向WHERE子句增加另外一个条件，如：
复制代码 代码如下:
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 3 AND stringu1 = 'xxx';
QUERY PLAN
------------------------------------------------------------------------------
Index Scan using tenk1_unique1 on tenk1 (cost=0.00..10.01 rows=1 width=244)
Index Cond: (unique1 < 3)
Filter: (stringu1 = 'xxx'::name)
新增的过滤条件stringu1 = 'xxx'只是减少了预计输出的行数，但是并没有减少实际开销，因为我们仍然需要访问相同数量的数据行。而该条件并没有作为一个索引条件，而是被当成对索引结果的过滤条件来看待。
如果WHERE条件里有多个字段存在索引，那么规划器可能会使用索引的AND或OR的组合，如：
复制代码 代码如下:
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000;
QUERY PLAN
-------------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=11.27..49.11 rows=11 width=244)
Recheck Cond: ((unique1 < 100) AND (unique2 > 9000))
-> BitmapAnd (cost=11.27..11.27 rows=11 width=0)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0)
Index Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique2 (cost=0.00..8.65 rows=1042 width=0)
Index Cond: (unique2 > 9000)
这样的结果将会导致访问两个索引，与只使用一个索引，而把另外一个条件只当作过滤器相比，这个方法未必是更优。
现在让我们来看一下基于索引字段进行表连接的查询规划，如：
复制代码 代码如下:
EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
QUERY PLAN
--------------------------------------------------------------------------------------
Nested Loop (cost=2.37..553.11 rows=106 width=488)
-> Bitmap Heap Scan on tenk1 t1 (cost=2.37..232.35 rows=106 width=244)
Recheck Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0)
Index Cond: (unique1 < 100)
-> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.00..3.01 rows=1 width=244)
Index Cond: ("outer".unique2 = t2.unique2)
从查询规划中可以看出(Nested
Loop)该查询语句使用了嵌套循环。外层的扫描是一个位图索引，因此其开销与行计数和之前查询的开销是相同的，这是因为条件unique1 <
100发挥了作用。 这个时候t1.unique2 =
t2.unique2条件子句还没有产生什么作用，因此它不会影响外层扫描的行计数。然而对于内层扫描而言，当前外层扫描的数据行将被插入到内层索引扫描
中，并生成类似的条件t2.unique2 = constant。所以，内层扫描将得到和EXPLAIN SELECT * FROM tenk2
WHERE unique2 = 42一样的计划和开销。最后，以外层扫描的开销为基础设置循环节点的开销，再加上每个外层行的一个迭代(这里是 106
* 3.01)，以及连接处理需要的一点点CPU时间。
如果不想使用嵌套循环的方式来规划上面的查询，那么我们可以通过执行以下系统设置，以关闭嵌套循环，如：
复制代码 代码如下:
SET enable_nestloop = off;
EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
QUERY PLAN
------------------------------------------------------------------------------------------
Hash Join (cost=232.61..741.67 rows=106 width=488)
Hash Cond: ("outer".unique2 = "inner".unique2)
-> Seq Scan on tenk2 t2 (cost=0.00..458.00 rows=10000 width=244)
-> Hash (cost=232.35..232.35 rows=106 width=244)
-> Bitmap Heap Scan on tenk1 t1 (cost=2.37..232.35 rows=106 width=244)
Recheck Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0)
Index Cond: (unique1 < 100)
这个规划仍然试图用同样的索引扫描从tenk1里面取出符合要求的100行，并把它们存储在内存中的散列(哈希)表里，然后对tenk2做一次全表顺序扫
描，并为每一条tenk2中的记录查询散列(哈希)表，寻找可能匹配t1.unique2 =
t2.unique2的行。读取tenk1和建立散列表是此散列联接的全部启动开销，因为我们在开始读取tenk2之前不可能获得任何输出行。
此外，我们还可以用EXPLAIN ANALYZE命令检查规划器预估值的准确性。这个命令将先执行该查询，然后显示每个规划节点内实际运行时间，以及单纯EXPLAIN命令显示的预计开销，如：
复制代码 代码如下:
EXPLAIN ANALYZE SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=2.37..553.11 rows=106 width=488) (actual time=1.392..12.700 rows=100 loops=1)
-> Bitmap Heap Scan on tenk1 t1 (cost=2.37..232.35 rows=106 width=244) (actual time=0.878..2.367 rows=100 loops=1)
Recheck Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37
rows=106 width=0) (actual time=0.546..0.546 rows=100 loops=1)
Index Cond: (unique1 < 100)
-> Index Scan using tenk2_unique2 on tenk2 t2
(cost=0.00..3.01 rows=1 width=244) (actual time=0.067..0.078 rows=1
loops=100)
Index Cond: ("outer".unique2 = t2.unique2)
Total runtime: 14.452 ms
注意"actual time"数值是以真实时间的毫秒来计算的，而"cost"预估值是以磁盘页面读取数量来计算的，所以它们很可能是不一致的。然而我们需要关注的只是两组数据的比值是否一致。
在一些查询规划里，一个子规划节点很可能会运行多次，如之前的嵌套循环规划，内层的索引扫描会为每个外层行执行一次。在这种情况下，"loops"将报告
该节点执行的总次数，而显示的实际时间和行数目则是每次执行的平均值。这么做的原因是令这些真实数值与开销预计显示的数值更具可比性。如果想获得该节点所
花费的时间总数，计算方式是用该值乘以"loops"值。
EXPLAIN ANALYZE显示的"Total runtime"包括执行器启动和关闭的时间，以及结果行处理的时间，但是它并不包括分析、重写或者规划的时间。
如果EXPLAIN命令仅能用于测试环境，而不能用于真实环境，那它就什么用都没有。比如，在一个数据较少的表上执行EXPLAIN，它不能适用于数量很
多的大表，因为规划器的开销计算不是线性的，因此它很可能对大些或者小些的表选择不同的规划。一个极端的例子是一个只占据一个磁盘页面的表，在这样的表
上，不管它有没有索引可以使用，你几乎都总是得到顺序扫描规划。规划器知道不管在任何情况下它都要进行一个磁盘页面的读取，所以再增加几个磁盘页面读取用
以查找索引是毫无意义的。
二、批量数据插入：
有以下几种方法用于优化数据的批量插入。
1. 关闭自动提交：
在批量插入数据时，如果每条数据都被自动提交，当中途出现系统故障时，不仅不能保障本次批量插入的数据一致性，而且由于有多次提交操作的发生，整个插入效
率也会受到很大的打击。解决方法是，关闭系统的自动提交，并且在插入开始之前，显示的执行begin
transaction命令，在全部插入操作完成之后再执行commit命令提交所有的插入操作。
2. 使用COPY：
使用COPY在一条命令里装载所有记录，而不是一系列的INSERT命令。COPY命令是为装载数量巨大的数据行优化过的，它不像INSERT命令那样灵
活，但是在装载大量数据时，系统开销也要少很多。因为COPY是单条命令，因此在填充表的时就没有必要关闭自动提交了。
3. 删除索引：
如果你正在装载一个新创建的表，最快的方法是创建表，用COPY批量装载，然后创建表需要的任何索引。因为在已存在数据的表上创建索引比维护逐行增加要快。当然在缺少索引期间，其它有关该表的查询操作的性能将会受到一定的影响，唯一性约束也有可能遭到破坏。
4. 删除外键约束：
和索引一样，"批量地"检查外键约束比一行行检查更加高效。因此，我们可以先删除外键约束，装载数据，然后在重建约束。
5. 增大maintenance_work_mem：
在装载大量数据时，临时增大maintenance_work_mem系统变量的值可以改进性能。这个系统参数可以提高CREATE
INDEX命令和ALTER TABLE ADD FOREIGN KEY命令的执行效率，但是它不会对COPY操作本身产生多大的影响。
6. 增大checkpoint_segments：
临时增大checkpoint_segments系统变量的值也可以提高大量数据装载的效率。这是因为在向PostgreSQL装载大量数据时，将会导致
检查点操作(由系统变量checkpoint_timeout声明)比平时更加频繁的发生。在每次检查点发生时，所有的脏数据都必须flush到磁盘上。
通过提高checkpoint_segments变量的值，可以有效的减少检查点的数目。
7. 事后运行ANALYZE：
在增加或者更新了大量数据之后，应该立即运行ANALYZE命令，这样可以保证规划器得到基于该表的最新数据统计。换句话说，如果没有统计数据或者统计数据太过陈旧，那么规划器很可能会选择一个较差的查询规划，从而导致查询效率过于低下。
② 在postgresql中如何从数据库中检索出所有的表名
1、查看当前数据库中所有的模式。
③ 怎么将postgresql安装目录bin加入到环境变量中的"path"变量中，具体点，谢谢
右击"我的电脑"-->"高级"选项卡-->"环境变量"，在环境变量的弹出窗口的下面，是系统环境变量，针对登陆到这个系统的所有用户，上面则是用户环境变量，针对当前用户有效，看你个人需求，我一般设置的都是系统环境变量。
在"系统变量"列表中，选择变量名为Path的项，点击"编辑"，在编辑窗口的"变量值"输入项的最前面输入你的路径，记得以分号结束，点"确定"-->"确定"-->"确定"，就可以完成环境变量设置了
④ postgresql存储过程 怎么实现无返回值
PostgreSQL 存储过程定义格式如下：
■结构 PL/pgSQL是一种块结构的语言，比较方便的是用pgAdmin III新建Function，填入一些参数就可以了。
基本上是这样的：
CREATE OR REPLACE FUNCTION 函数名(参数1，[整型 int4, 整型数组 _int4, ...]) RETURNS 返回值类型 AS $BODY$ DECLARE 变量声明 BEGIN 函数体 END; $BODY$ LANGUAGE ‘plpgsql’ VOLATILE;
■变量类型 除了postgresql内置的变量类型外，常用的还有 RECORD ，表示一条记录。
■赋值 赋值和Pascal有点像：“变量 := 表达式;” 有些奇怪的是连接字符串的是“||”，比如 sql := ‘SELECT * FROM’ || table || ‘WHERE …’;
■判断 判断又和VB有些像： IF 条件 THEN … ELSEIF 条件 THEN … ELSE … END IF;
■循环 循环有好几种写法： WHILE expression LOOP statements END LOOP; 还有常用的一种是：（从1循环到9可以写成FOR i IN 1..9 LOOP） FOR name IN [ REVERSE ] expression .. expression LOOP statements END LOOP;
■其他 还有几个常用的函数： SELECT INTO record …; 表示将select的结果赋给record变量（RECORD类型） PERFORM query; 表示执行query并丢弃结果 EXECUTE sql; 表示执行sql语句，这条可以动态执行sql语句（特别是由参数传入构造sql语句的时候特别有用）
--简单的例子：
例1：无返回值
CREATE OR REPLACE FUNCTION 函数名称( 参数1,参数2,...)
AS
$BODY$
DECLARE --定义
BEGIN
INSERT INTO "表名" VALUES(参数1，参数2，...);
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE; -- 最后别忘了这个。
例2：有返回值
CREATE OR REPLACE FUNCTION 函数名称(deptcode VARCHAR(20) ,deptname VARCHAR(60) ,pycode VARCHAR(60),isenabled CHAR(1))
RETURNS BOOLEAN --返回值，布尔类型
AS
$body$
DECLARE
deptcode VARCHAR(20);
deptname VARCHAR(60);
pycode VARCHAR(60);
isenabled CHAR(1);
BEGIN
UPDATE "deptDict" SET deptcode=deptcode,deptname=deptname,pycode=pycode,isenabled=isenabled,updatedhisdatetime=CURRENT_TIMESTAMP
WHERE deptcode=deptcode;
RETURN TRUE;
END
$body$
LANGUAGE 'plpgsql' VOLATILE;
最后再加上如何执行这个存储过程（函数）
-- 执行存储过程方法1
SELECT * FROM 函数名称(参数1，参数2，...)
-- 执行存储过程方法2
SELECT 函数名称('0参数1，参数2，...)
⑤ 如何修改postgresql数据库字符集
要想打开自动字符集转换功能，你必须告诉 PostgreSQL 你想在客户端使用的字符集（编码）。你可以用好几种方法实现这个目的。
用 psql 里的 /encoding 命令。 /encoding 允许你动态修改客户端编码。 比如，把编码改变为 SJIS，键入：
/encoding SJIS
使用 libpq 函数。 /encoding 在做此用途的时候实际上是调用 PQsetClientEncoding()。
int PQsetClientEncoding(PGconn *conn, const char *encoding);
这里 conn 与后端的联接，而 encoding 是你想用的编码。如果编码设置成功它返回 0，否则返回 -1。本次联接的当前编码可以用下面函数显示：
int PQclientEncoding(const PGconn *conn);
请注意它只返回编码 ID，而不是象 EUC_JP 这样的编码符号字串。 要把编码 ID 转换为编码符号，你可以用：
char *pg_encoding_to_char(int encoding_id);
使用 SET client_encoding TO。 可以用 SQL 命令设置客户端编码：
SET CLIENT_ENCODING TO 'value';
你还可以把 SQL 语法里的 SET NAMES用于这个目的：
SET NAMES 'value';
查询当前客户端编码：
SHOW client_encoding;
返回缺省编码：
RESET client_encoding;
使用 PGCLIENTENCODING。 如果在客户端的环境里定义了 PGCLIENTENCODING 环境变量， 那么在与服务器进行联接时将自动选择客户端编码。 （这个编码随后可以用上面谈到的任何其它方法覆盖。）
使用client_encoding配置变量。 如果在 postgresql.conf 里设置了 client_encoding 变量， 那么在与服务器建立了联接之后，这个客户端编码将自动选定。（这个设置随后可以被上面提到 的其他方法覆盖。）
假如无法进行特定的字符转换 — 比如， 你选的服务器编码是EUC_JP， 客户端是LATIN1，那么有些日文字符不能转换成LATIN1。这时， 不能用LATIN1字符集表示的字母将被转换成圆括号包围的十六进制，像，(826C) 这样。
如果客户端字符集定义成了 SQL_ASCII，那么编码转换会被关闭， 不管服务器的字符集是什么都一样。和服务器一样，除非你的工作环境全部是 ASCII 数据， 否则使用 SQL_ASCII 是不明智的。