PostgreSQL常见问题（FAQ . 二）

ehero

4.6) 为什么我的查询很慢？为什么这些查询没有利用索引？
并非每个查询都会自动使用索引。只有在表的大小超过一个最小值，并且查询只会选中表中较小比例的记录时才会采用索引。 这是因为索引扫描引起的随即磁盘存取可能比直接地读取表（顺序扫描）更慢。

为了判断是否使用索引，PostgreSQL必须获得有关表的统计值。这些统计值可以使用 VACUUM ANALYZE，或 ANALYZE 获得。 使用统计值，优化器知道表中有多少行，就能够更好地判断是否利用索引。 统计值对确定优化的连接顺序和连接方法也很有用。在表的内容发生变化时，应定期进行统计值的更新收集。

索引通常不用于 ORDER BY 或执行连接。对一个大表的一次顺序扫描再做一次排序通常比索引扫描要快。然而，如果将 LIMIT 和 ORDER BY 结合在一起使用的话，通常将会使用索引，因为这时仅返回表中的一小部分记录。

如果你确信PostgreSQL的优化器使用顺序扫描是不正确的，你可以使用SET enable_seqscan TO 'off'指令来关闭顺序扫描， 然后再次运行查询，你就可以看出使用一个索引扫描是否确实要快一些。

当使用通配符操作，例如 LIKE 或 ~ 时，索引只能在特定的情况下使用：

字符串的开始部分必须是普通字符串，也就是说：
LIKE 模式不能以 % 打头。
~ （正则表达式）模式必须以 ^ 打头。
字符串不能以匹配多个字符的模式类打头，例如 [a-e]。
大小写无关的查找，如 ILIKE 和 ~* 等不使用索引，但可以用 4.8 节描述的表达式索引。
在做 initdb 时必须采用缺省的本地设置 C locale，因为系统不可能知道在非C locale情况时下一个最大字符是什么。 在这种情况下，你可以创建一个特殊的text_pattern_ops索引来用于LIKE的索引。
在8.0之前的版本中，除非要查询的数据类型和索引的数据类型相匹配，否则索引经常是未被用到，特别是对int2,int8和数值型的索引。

4.7) 我如何才能看到查询优化器是怎样评估处理我的查询？
参考 EXPLAIN 手册页。

4. 我怎样做正则表达式搜索和大小写无关的正则表达式查找？怎样利用索引进行大小写无关查找？
操作符 ~ 处理正则表达式匹配，而 ~* 处理大小写无关的正则表达式匹配。大小写无关的 LIKE 变种成为 ILIKE。

大小写无关的等式比较通常写做：


    SELECT *

    FROM tab

    WHERE lower(col) = 'abc';

这样将不会使用标准的索引。但是可以创建一个在这种情况下使用的表达式索引:


    CREATE INDEX tabindex ON tab (lower(col));



如果上述索引在创建时加入UNIQUE约束，虽然索引字段自身内容可以存储大小写不限的内容，但如果有UNIQUE约束后，这些内容不能仅仅是大小写不同（否则会造成冲突）。为了保证不发生这种情况，可以使用CHECK约束条件或是触发器在录入时进行限制。

4.9) 在一个查询里，我怎样检测一个字段是否为 NULL ？我如何才能准确排序而不论某字段是否含 NULL 值？
用 IS NULL 和 IS NOT NULL 测试这个字段，具体方法如下：

   SELECT *

   FROM tab

   WHERE col IS NULL;

为了能对含 NULL字段排序，可在 ORDER BY 条件中使用 IS NULL和 IS NOT NULL 修饰符，条件为真 true 将比条件为假false 排在前面，下面的例子就会将含 NULL 的记录排在结果的上面部分：

   SELECT *

   FROM tab

   ORDER BY (col IS NOT NULL)

4.10) 各种字符类型之间有什么不同？
类型 内部名称 说明
VARCHAR(n) varchar 指定了最大长度，变长字符串，不足定义长度的部分不补齐
CHAR(n) bpchar 定长字符串，实际数据不足定义长度时，以空格补齐
TEXT text 没有特别的上限限制（仅受行的最大长度限制）
BYTEA bytea 变长字节序列（使用NULL字符也是允许的）
"char" char 单个字符

在系统表和在一些错误信息里你将看到内部名称。

上面所列的前四种类型是"varlena"（变长）类型（也就是说，开头的四个字节是长度，后面才是数据）。 于是实际占用的空间比声明的大小要多一些。 然而这些类型如定义很长时都可以被压缩存储，因此磁盘空间也可能比预想的要少。

VARCHAR(n) 在存储限制了最大长度的变长字符串是最好的。 TEXT 适用于存储最大可达 1G左右但未定义限制长度的字符串。

CHAR(n) 最适合于存储长度相同的字符串。 CHAR(n)会根据所给定的字段长度以空格补足（不足的字段内容）， 而 VARCHAR(n) 只存储所给定的数据内容。 BYTEA 用于存储二进制数据，尤其是包含 NULL 字节的值。这些类型具有差不多的性能。

4.11.1) 我怎样创建一个序列号或是自动递增的字段？
PostgreSQL 支持 SERIAL 数据类型。（字段定义为SERIAL后）将自动创建一个序列生成器，例如：


   CREATE TABLE person (

      id   SERIAL,

      name TEXT

   );

会自动转换为以下SQL语句：


   CREATE SEQUENCE person_id_seq;

   CREATE TABLE person (

      id   INT4 NOT NULL DEFAULT nextval('person_id_seq'),

      name TEXT

   );

参考 create_sequence 手册页获取关于序列生成器的更多信息。

4.11.2) 我如何获得一个插入的序列号的值？
一种方法是在插入之前先用函数 nextval() 从序列对象里检索出下一个 SERIAL 值，然后再用此值精确地插入。使用 4.11.1 里的例表，可用伪码这样描述：


   new_id = execute("SELECT nextval('person_id_seq')";

   execute("INSERT INTO person (id, name) VALUES (new_id, 'Blaise Pascal')";

这样还能在其他查询中使用存放在 new_id 里的新值（例如，作为参照 person 表的外键）。 注意自动创建的 SEQUENCE 对象的名称将会是 <table>_<serialcolumn>_seq， 这里 table 和 serialcolumn 分别是你的表的名称和你的 SERIAL 字段的名称。

类似的，在 SERIAL 对象缺省插入后你可以用函数 currval() 检索刚赋值的 SERIAL 值，例如：


   execute("INSERT INTO person (name) VALUES ('Blaise Pascal')";

   new_id = execute("SELECT currval('person_id_seq')";

4.11.3) 同时使用 currval() 会导致和其他用户的冲突情况吗？
不会。currval() 返回的是你本次会话进程所赋的值而不是所有用户的当前值。


4.11.4) 为什么不在事务异常中止后重用序列号呢？为什么在序列号字段的取值中存在间断呢？
为了提高并发性，序列号在需要的时候赋予正在运行的事务，并且在事务结束之前不进行锁定， 这就会导致异常中止的事务后，序列号会出现间隔。

4.12) 什么是 OID ？什么是 CTID ？
PostgreSQL 里创建的每一行记录都会获得一个唯一的OID，除非在创建表时使用WITHOUT OIDS选项。 OID创建时会自动生成一个4字节的整数，所有 OID 在相应PostgreSQL服务器中均是唯一的。 然而，它在超过40亿时将溢出， OID此后会出现重复。PostgreSQL 在它的内部系统表里使用 OID 在表之间建立联系。

在用户的数据表中，最好是使用SERIAl来代替OID 因为SERIAL只要保证在单个表中的数值是唯一的就可以了，这样它溢出的可能性就非常小了， SERIAL8可用来保存8字节的序列数值。

CTID 用于标识带着数据块（地址）和（块内）偏移的特定的物理行。 CTID 在记录被更改或重载后发生改变。索引数据使用它们指向物理行。

4.13) 为什么我收到错误信息“ERROR: Memory exhausted in AllocSetAlloc()”？
这很可能是系统的虚拟内存用光了，或者内核对某些资源有较低的限制值。在启动 postmaster 之前试试下面的命令：


   ulimit -d 262144

   limit datasize 256m

取决于你用的 shell，上面命令只有一条能成功，但是它将把你的进程数据段限制设得比较高， 因而也许能让查询完成。这条命令应用于当前进程，以及所有在这条命令运行后创建的子进程。 如果你是在运行SQL客户端时因为后台返回了太多的数据而出现问题，请在运行客户端之前执行上述命令。

4.14) 我如何才能知道所运行的PostgreSQL的版本？
从 psql 里，输入 SELECT version();指令。

4.15) 我如何创建一个缺省值是当前时间的字段？
使用 CURRENT_TIMESTAMP：


   CREATE TABLE test (x int, modtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

4.16) 我怎样进行 outer join （外连接）？
PostgreSQL 采用标准的 SQL 语法支持外连接。这里是两个例子：


   SELECT *

   FROM t1 LEFT OUTER JOIN t2 ON (t1.col = t2.col);

或是


   SELECT *

   FROM t1 LEFT OUTER JOIN t2 USING (col);

这两个等价的查询在 t1.col 和 t2.col 上做连接，并且返回 t1 中所有未连接的行（那些在 t2 中没有匹配的行）。 右[外]连接（RIGHT OUTER JOIN）将返回 t2 中未连接的行。 完全外连接（FULL OUTER JOIN）将返回 t1 和 t2 中未连接的行。 关键字 OUTER 在左[外]连接、右[外]连接和完全[外]连接中是可选的，普通连接被称为内连接（INNER JOIN）。

4.17) 如何使用涉及多个数据库的查询？
没有办法查询当前数据库之外的数据库。 因为PostgreSQL要加载与数据库相关的系统目录（系统表），因此跨数据库的查询如何执行是不定的。

附加增值模块contrib/dblink允许采用函数调用实现跨库查询。当然用户也可以同时连接到不同的数据库执行查询然后在客户端合并结果。

4.1 如何让函数返回多行或多列数据？
在函数中返回数据记录集的功能是很容易使用的，详情参见： http://techdocs.postgresql.org/guides/SetReturningFunctions

4.19) 为什么我在使用PL/PgSQL函数存取临时表时会收到错误信息“relation with OID ##### does not exist”？
PL/PgSQL会缓存函数的脚本内容，由此带来的一个不好的副作用是若一个 PL/PgSQL 函数访问了一个临时表，然后该表被删除并重建了，则再次调用该函数将失败， 因为缓存的函数内容仍然指向旧的临时表。解决的方法是在 PL/PgSQL 中用EXECUTE 对临时表进行访问。这样会保证查询在执行前总会被重新解析。

4.20) 目前有哪些数据复制方案可用？
“复制”只是一个术语，有好几种复制技术可用，每种都有优点和缺点：

主/从复制方式是允许一个主服务器接受读/写的申请，而多个从服务器只能接受读/SELECT查询的申请， 目前最流行且免费的主/从PostgreSQL复制方案是 Slony-I 。

多个主服务器的复制方式允许将读/写的申请发送给多台的计算机，这种方式由于需要在多台服务器之间同步数据变动 可能会带来较严重的性能损失，Pgcluster是目前这种方案 中最好的，而且还可以免费下载。

也有一些商业需付费和基于硬件的数据复制方案，支持上述各种复制模型。

4.21) 为何查询结果显示的表名或列名与我的查询语句中的不同？为何大写状态不能保留？
最常见的原因是在创建表时对表名或是列名使用了双引号“”，当使用了双引号后，表名或列名（称为标识符）存储时是区分 大小写的， 这意谓着你在查询时表名或列名也应使用双引号，一些工具软件，像pgAdmin会在发出创建表的指令时自动地在每个标识符上加双引号。 因此，为了标识符的统一，你应该：

在创建表时避免将标识符使用双引号引起来。
在标识符中只使用小写字母。
（为了与已存在的标识符相同）在查询中使用双引号将标识符引起来。