行式数据库PostgreSQL 9.04版本评测-技术开发专区

行式数据库PostgreSQL 9.04版本评测

作者：卢涛编辑：董建伟 2011-06-22 00:00 IT168网站原创

　　【IT168 专稿】PostgreSQL 是一种对象-关系型数据库管理系统(ORDBMS)，功能强大，特性丰富的自由软件数据库系统。这个起源于伯克利(BSD)的数据库研究计划目前已经成为一项国际开发项目，并且有非常广泛的用户。在灵活的BSD风格许可证下发行。

　　PostgreSQL的前身是BSD的始于1977年的Ingres项目，这个项目是Michael Stonebraker教授领导的，在1982 年，Michael Stonebraker离开伯克利商业化了Ingres，Ingres是一个非关系型的数据库,数据关系需要由用户有意识地维护，在1985年，Michael Stonebraker回到伯克利开始一个后-Ingres(post-Ingres)的项目，目的是解决Ingres中，同时也是1980年代的数据库系统中的主要问题：数据关系维护的问题。这就是 Postgres 的开端。1989年6月发布了版本1，1990年6月发布了版本2，1991年发布了版本3。在版本3里改进了规则系统，增加了多存储系统支持以及改进了查询引擎。在1993年，特性需求急剧增加;在做了一些代码清理、发布了版本4之后，Postgres 项目正式终止。

　　Postgres 并没有因为 Postgres 项目的终止而停止发展，而是仍然在各种场合被人们所使用，在1994年，两名伯克利的研究生，来自中国香港的 Andrew Yu 和 Jolly Chen，向 Postgres 里增加了现代的 SQL 语言的支持。而原先Postgres用的是 QUEL/Postquel 语言， 1992年SQL 语言的国际标准发布，当时称为 SQL2，但是人们常称为 SQL92。两位研究生是用 bison 和 flex 工具的组合，把Postgres 的Postquel查询语言替换成了 SQL92，然后将Postgres改名为 Postgres95，并将其发布到了互联网上，随后的 1996 年，加拿大的 Marc Fournier提供了第一个非大学的开发服务器平台，然后 Bruce Momjian(美国)和 Vadim B. Mikheev(俄国)开始修改以及稳定伯克利发布的代码，并于1996年8月发布了第一个开源版本。

　　随后，这些开发者把项目名称改变为 PostgreSQL，以对应 SQL 的支持，并且把 PostgreSQL 的版本号重新放到了原先 Postgres 项目的顺序中去，从 6.0 开始(Postgres 本身到4.2，Postgres95 算5.0)。7.x版本增加了预写入日志的功能，优化了In查询等，8.x版本提升了EXISTS 和NOT EXISTS性能，增加了并行恢复、分析函数和递归With查询等。9.x版本增加了对SQL:2008标准的支持，在数据恢复性能、分析函数等方面加强。

　　目前，PostgreSQL 的稳定版本到了9.0.4，同时还支持8.x版本的更新，在RDBMS的各种特性方面，已经接近于“无可指摘”的境界了。而目前 PostgreSQL 的开发是围绕在更易用的 SQL 语言扩展、SQL 标准的兼容(比如SQL2003、XML/Xquery的支持)、扩展工具、外围工具等方面。

　　一些商业公司在PostgreSQL代码的基础上，添加了Oracle兼容性和集群等增强功能，发行了各种企业级商业数据库，著名的有EnterpriseDB 和Greenplum，请读者自行查阅相关资料，这里不再赘述。

　　一、数据库安装

　　PostgreSQL在其官方网站http://www.postgresql.org/download/上提供了各种版本软件的下载连接，官方主要发布源代码版本，而编译好的二进制发行版由认证的第3方公司提供，RPM版本的下载连接是http://www.openscg.org/se/postgresql/packages.jsp ，提供32位和64位版本，我们选择64位rpm版进行测试。

　　PostgreSQL网站http://www.postgresql.org/docs/9.0/interactive/index.html提供了文档在线浏览。http://www.postgresql.org/docs/也有部分其它语言的文档，但没有简体中文版，国内PostgreSQL的志愿者在http://www.pgsqldb.org/提供了8.x的中文文档，便于熟悉中文的用户学习。如果要学习最新的9.0版本，还是建议下载http://www.postgresql.org/files/documentation/pdf/9.0/postgresql-9.0-A4.pdf，制作精美，具备搜索功能。

　　本次测试基于 Intel Xeon 7550*8的 PC服务器上用 VMWare VSphere 4.1管理的虚拟机，虚拟机的逻辑 CPU个数是 8，内存 100GB，存储为 8个 300GB SAS本地磁盘，采用一块 512M缓存 RAID卡，按 RAID5方式组成磁盘阵列。操作系统采用和 RHEL 5相同的核心级别的 RedFlag Asian Linux Sever 3.0 x64。因此选用的安装文件是RHEL5 (x86, 64-bit), RPM Package版本，postgres-9.0.4-1.x86_64.openscg.rpm，总大小大约16MB。

　　安装步骤如下：

　　1.安装前准备工作

　　文档中介绍安装PostgreSQL前，首先要创建postgres用户和组，但作为一款广受欢迎的软件，很多Linux发行版中都默认安装了PostgreSQL, RedFlag Linux也不例外，因此，建用户的步骤可以忽略。

　　先来查看一下，系统中已经安装的版本：

-bash-3.2$ rpm -qa|grep post

postfix-pflogsumm-2.3.3-2.9AXS3

postgresql-tcl-8.1.18-2.1.1AXS3

postgresql-libs-8.1.18-2.1.1AXS3

postgresql-docs-8.1.18-2.1.1AXS3

postgresql-devel-8.1.18-2.1.1AXS3

postfix-2.3.3-2.9AXS3

postgresql-pl-8.1.18-2.1.1AXS3

postgresql-odbc-08.01.0200-3.1

postgresql-devel-8.1.18-2.1.1AXS3

freeradius-postgresql-1.1.3-1.6.AXS3

postgresql-contrib-8.1.18-2.1.1AXS3

postgresql-server-8.1.18-2.1.1AXS3

postgresql-8.1.18-2.1.1AXS3

postgresql-python-8.1.18-2.1.1AXS3

postgresql-jdbc-8.1.407-1jpp.4

postgresql-libs-8.1.18-2.1.1AXS3

　　可见，系统预安装的是8.1版本。将下载来的安装文件上传到Linux服务器。然后不卸载旧版PostgreSQL，直接用rpm -ivh命令安装，安装成功了，但是需要注意：/usr/bin上的postgres和psql命令是连接到旧版的，如果要用9.0版，需要指定文件的路径。

-bash-3.2$ cd /user1

-bash-3.2$ ll p*rpm

-rw-r--r-- 1 root root 16397430 04-24 08:16 postgres-9.0.4-1.x86_64.openscg.rpm [root@redflag11012501 user1]# rpm -ivh *rpm

Preparing... ########################################### [100%]

1 postgres-9.0 ########################################### [100%]

　　Rpm文件安装完成，下一步是用initdb命令初始化数据库。

[root@redflag11012501 user1]# su - postgres

-bash-3.2$ cd /opt

-bash-3.2$ ls post*

9.0

-bash-3.2$ cd post*/9.0

-bash-3.2$ pwd

/opt/postgres/9.0

-bash-3.2$ cd bin

-bash-3.2$ ./initdb --encoding=utf8 -D /user1/postgresql/data

The files belonging to this database system will be owned by user "postgres".

This user must also own the server process.

The database cluster will be initialized with locale zh_CN.GB18030.

initdb: encoding mismatch

The encoding you selected (UTF8) and the encoding that the

selected locale uses (GB18030) do not match. This would lead to

misbehavior in various character string processing functions.

Rerun initdb and either do not specify an encoding explicitly,

or choose a matching combination.

-bash-3.2$

　　initdb命令提示由于字符集冲突，或者不指定字符集，或者使用匹配的字符集。我们设定LANG环境变量为C,即英语，重新运行initdb命令，这次字符集验证通过，但缺少创建目录的权限，使用root用户改变目录的属主，接下来就顺利执行了。

-bash-3.2$ export LANG=C

-bash-3.2$ ./initdb --encoding=utf8 -D /user1/postgresql/data

The files belonging to this database system will be owned by user "postgres".

This user must also own the server process.

The database cluster will be initialized with locale C.

The default text search configuration will be set to "english".

creating directory /user1/postgresql/data ... initdb: could not create directory "/user1/postgresql": Permission denied

-bash-3.2$ su -

Password:

[root@redflag11012501 ~]# mkdir /user1/postgresql

[root@redflag11012501 ~]# chown postgres:dba /user1/postgresql

[root@redflag11012501 ~]# exit

logout

-bash-3.2$ ./initdb --encoding=utf8 -D /user1/postgresql/data

The files belonging to this database system will be owned by user "postgres".

This user must also own the server process.

The database cluster will be initialized with locale C.

The default text search configuration will be set to "english".

creating directory /user1/postgresql/data ... ok

creating subdirectories ... ok

selecting default max_connections ... 100

selecting default shared_buffers ... 32MB

creating configuration files ... ok

creating template1 database in /user1/postgresql/data/base/1 ... ok

initializing pg_authid ... ok

initializing dependencies ... ok

creating system views ... ok

loading system objects' descriptions ... ok

creating conversions ... ok

creating dictionaries ... ok

setting privileges on built-in objects ... ok

creating information schema ... ok

loading PL/pgSQL server-side language ... ok

vacuuming database template1 ... ok

copying template1 to template0 ... ok

copying template1 to postgres ... ok

WARNING: enabling "trust" authentication for local connections

You can change this by editing pg_hba.conf or using the -A option the

next time you run initdb.

Success. You can now start the database server using:

./postgres -D /user1/postgresql/data

./pg_ctl -D /user1/postgresql/data -l logfile start

　　数据库初始化后，才可以启动postgresql数据库服务器的后台服务，注意postgresql和pg_ctl命令也需要指定路径。接下来创建我们测试用的数据库pgdb。用psql命令测试数据库创建成功，执行默认的不带路径的psql调用的是旧版软件，系统给出警告信息，一些\开始的命令不能正确运行，如果用带路径的psql,则不会出现警告。

-bash-3.2$ ./postgres -D /user1/postgresql/data &

[1] 27388

-bash-3.2$ LOG: database system was shut down at 2011-04-24 12:57:09 CST

LOG: autovacuum launcher started

LOG: database system is ready to accept connections

-bash-3.2$ createdb pgdb

CREATE DATABASE

-bash-3.2$ psql pgdb

Welcome to psql 8.1.18 (server 9.0.4), the PostgreSQL interactive terminal.

Type: \copyright for distribution terms

\h for help with SQL commands

\? for help with psql commands

\g or terminate with semicolon to execute query

\q to quit

WARNING: You are connected to a server with major version 9.0,

but your psql client is major version 8.1. Some backslash commands,

such as \d, might not work properly.

-bash-3.2$ ./psql pgdb

psql (9.0.4)

Type "help" for help.

　　至此，安装过程结束。

　　二、PostgreSQL功能简介

　　1. 几个概念

　　数据库集群

　　在你做任何事情之前，你必须初始化磁盘上的数据库的存储区间。这称为数据库集群。一个数据库集群是一系列数据库的集合，这些数据库可以通过单个数据库服务器的实例管理。在初始化后，一个数据库集群将包含一个叫template1的数据库。这个数据库将作为随后创建的数据库的模版;在实际工作中不应该使用这个库。

　　用文件系统的术语来说，一个数据库集群将是一个目录，所有数据都将存放在这个目录中。我们把它称做数据目录或数据区。在哪里存放数据完全是你的选择，我们没有缺省值，尽管 /usr/local/pgsql/data 或 /var/lib/pgsql/data 这样的目录很常用。要初始化一个数据库集群，可以使用命令 initdb。

　　注意这里的数据库集群和其他数据库管理系统的集群不同，它是单个实例，运行在一台服务器中。

　　数据库用户

　　数据库用户从概念上与操作系统用户是完全无关的。在实际使用中把它们对应起来可能比较方便，但不是必须的。数据库用户名在整个数据库集群中是全局的(而不是每个库不同)。要创建一个用户，使用 SQL 命令 CREATE USER：

　　CREATE USER name;

　　name 遵循 SQL 标识的规则：要么完全没有特殊字符，要么用双引号引起。要删除一个现有用户，使用类似的命令 DROP USER：

　　DROP USER name;

　　为了能初创数据库系统，新建立的数据库总是包含一个预定义的用户。这个用户将有固定的 ID 1，并且缺省时(除非在运行 initdb 时更改过)他将和初始化该数据库集群的用户有相同的名称。通常，这个用户叫postgres。为了创建更多用户，你必须首先以这个初始用户身份联接。

　　组

　　和 Unix 里一样，组是一个逻辑上对用户分组，进而简化权限管理的方法：权限可以赋予整个组，也可以对整个组撤除。要创建一个组，使用 SQL 命令 CREATE GROUP：

　　CREATE GROUP name;

　　要向组中增加用户或删除用户，用 SQL 命令 ALTER GROUP：

　　ALTER GROUP name ADD USER uname1, ... ;

　　ALTER GROUP name DROP USER uname1, ... ;

　　要删除一个阻，使用 DROP GROUP：

　　DROP GROUP name;

　　这样只会删除组，不删除成员用户。

　　模式

　　一个数据库包含一个或多个命名的模式，模式又包含表。模式还包含其它命名的对象，包括数据类型，函数，以及操作符。同一个对象名可以在不同的模式里使用而不会导致冲突;比如，schema1 和 myschema 都可以包含叫做 mytable 的表。和数据库不同，模式不是严格分离的：一个用户可以访问他所连接的数据库中的任意模式中的对象，只要他有权限。

　　模式的用途：

　　允许多个用户使用一个数据库而不会干扰其它用户。

　　把数据库对象组织成逻辑组，让它们更便于管理。

　　第三方的应用可以放在不同的模式中，这样它们就不会和其它对象的名字冲突。

　　模式类似于操作系统层次的目录，只不过模式不能嵌套。

　　PostgreSQL是一款面向对象数据库，支持表的继承(INHERITS)、还有一些独有的运算符和数据类型，比如::类型转换符，数组类型，这里不一一介绍，请参阅文档。

　　2.psql工具

　　psql是PostgreSQL提供的命令行工具，是DBA访问数据库的非常好的工具，既包括SQL的解析和执行，也包括执行各种PostgreSQL的内部命令。它包括单行命令方式和交互方式。通常都是采用交互方式，在需要批处理执行时用单行命令方式。

　　psql完整的命令行参数选项如下：

-bash-3.2$ ./psql --help

psql is the PostgreSQL interactive terminal.

用法:

psql [选项]... [数据库名字 [用户名称]]

一般选项:

-d 数据库名声明要联接的数据库名(缺省: "postgres")

-c 命令只运行一条命令(SQL 或者内部)然后退出

-f 文件名执行来自文件的命令, 然后退出

-l 列出所有可用数据库, 然后退出

-v NAME=VALUE 设置 psql 变量 NAME 的值为 VALUE

-X 不要读取启动文件 (~/.psqlrc)

--help 显示此帮助信息, 然后退出

--version 显示版本信息, 然后退出

输入和输出选项:

-a 回显所有来自脚本的输入

-e 回显发送给服务器的命令

-E 显示内部命令生成的查询

-q 安静运行 (没有信息, 只有查询输出)

-o 文件名向文件(或者 |管道)发送查询结果

-n 关闭增强的命令行编辑功能 (readline)

-s 单步模式 (核实每个查询)

-S 单行模式 (一行结束同时也标志着 SQL 命令结束)

-L FILENAME 发送会话日志到文件

输出格式选项:

-A 非对齐的表输出模式(-P format=unaligned)

-H HTML 表输出模式 (-P format=html)

-t 只打印行 (-P tuples_onle)

-T 文本设置 HTML 表标记属性 (表宽, 边宽) (-P tableattr=)

-x 打开扩展表输出 (-P expended)

-P VAR[=ARG] 把打印选项 VAR 设置为 ARG (见 \pset 命令)

-F 字串设置域分隔符 (缺省: "|") (-P fieldsep=)

-R 字串设置记录分隔符 (缺省: 新行) (-P recordsep=)

联接选项:

-h 主机名声明数据库服务器主机或套接字目录 (缺省: "本地套接字")

-p 端口数据库服务器端口 (缺省: 5432)

-U 名字数据库用户名称 (缺省: "postgres")

-W 提示输入口令 (将自动发生)

更多的相关信息, 在 psql 里键入 "\?" (用于内部命令) 或者 "\help"

(用于 SQL 命令), 或者参考 PostgreSQL 文档里的 psql 节

向 <pgsql-bugs@postgresql.org> 汇报臭虫.

　　进入psql命令行环境，可以使用的psql 内部\命令如下：

pgdb=# \?

一般选项

\c[onnect] [数据库名|- [用户名称]]

联接到新的数据库 (当前为 "pgdb")

\copyright 显示 PostgreSQL 用法和发布信息

\encoding [编码]

显示或设置客户端编码

\h [名字] SQL 命令的语法帮助, 用 * 可以看所有命令的帮助

\q 退出 psql

\set [名字 [值]]

设置内部变量, 如果没有参数就列出所有

\timing 查询计时开关切换 (目前是关闭)

\unset 名字取消(删除)内部变量

\! [命令] 在 shell 里执行命令或者开始一个交互的 shell

查询缓冲区选项

\e [文件名] 用一个外部编辑器编辑当前查询缓冲区或者文件

\g [文件名] 向服务器发送 SQL 命令 (并且把结果写到文件或者 |管道)

\p 显示当前查询缓冲区的内容

\r 重置 (清理) 查询缓冲区

\s [文件名] 打印历史或者将其保存到文件

\w [文件名] 将查询缓冲区写出到文件

输入/输出选项

\echo [字串] 向标准输出写出文本

\i 文件名执行来自文件的命令

\o [文件名] 向文件或者 |管道发送所有查询结果

\qecho [字串]

向查询输出流写出文本 (见 \o)

信息选项

\d [名字] 描述表, 索引, 序列, 或者视图

\d{t|i|s|v|S} [模式] (加 "+" 获取更多信息)

列出表/索引/序列/视图/系统表

\da [模式] 列出聚集函数

\db [模式] 列出表空间 (加 "+" 获取更多的信息)

\dc [模式] 列出编码转换

\dC 列出类型转换

\dd [模式] 显示目标的注释

\dD [模式] 列出域

\df [模式] 列出函数 (加 "+" 获取更多的信息)

\dg [模式] 列出组

\dn [模式] 列出模式 (加 "+" 获取更多的信息)

\do [名字] 列出操作符

\dl 列出大对象, 和 \lo_list 一样

\dp [模式] 列出表, 视图, 序列的访问权限

\dT [模式] 列出数据类型 (加 "+" 获取更多的信息)

\du [模式] 列出用户

\l 列出所有数据库 (加 "+" 获取更多的信息)

\z [模式] 列出表, 视图, 序列的访问权限 (和 \dp 一样)

格式选项

\a 在非对齐和对齐的输出模式之间切换

\C [字串] 设置表标题, 如果参数空则取消标题

\f [字串] 为非对齐查询输出显示或设置域分隔符

\H 在 HTML 输出模式之间切换 (当前是关闭)

\pset NAME [VALUE]

set table output option

(NAME := {format|border|expanded|fieldsep|footer|null|

numericlocale|recordsep|tuples_only|title|tableattr|pager})

\t 只显示行 (当前是关闭)

\T [字串] 设置 HTML <表> 标记属性, 如果没有参数就取消设置

\x 在扩展输出之间切换 (目前是关闭)

拷贝, 大对象选项

\copy ... 执行 SQL COPY, 数据流指向客户端主机

\lo_export LOBOID FILE

\lo_import FILE [COMMENT]

\lo_list

\lo_unlink LOBOID 大对象操作

　　进入psql命令行环境，可以使用的SQL命令如下：

pgdb=# \help

可用帮助:

ABORT CREATE LANGUAGE DROP VIEW

ALTER AGGREGATE CREATE OPERATOR CLASS END

ALTER CONVERSION CREATE OPERATOR EXECUTE

ALTER DATABASE CREATE ROLE EXPLAIN

ALTER DOMAIN CREATE RULE FETCH

ALTER FUNCTION CREATE SCHEMA GRANT

ALTER GROUP CREATE SEQUENCE INSERT

ALTER INDEX CREATE TABLE LISTEN

ALTER LANGUAGE CREATE TABLE AS LOAD

ALTER OPERATOR CLASS CREATE TABLESPACE LOCK

ALTER OPERATOR CREATE TRIGGER MOVE

ALTER ROLE CREATE TYPE NOTIFY

ALTER SCHEMA CREATE USER PREPARE

ALTER SEQUENCE CREATE VIEW PREPARE TRANSACTION

ALTER TABLE DEALLOCATE REINDEX

ALTER TABLESPACE DECLARE RELEASE SAVEPOINT

ALTER TRIGGER DELETE RESET

ALTER TYPE DROP AGGREGATE REVOKE

ALTER USER DROP CAST ROLLBACK

ANALYZE DROP CONVERSION ROLLBACK PREPARED

BEGIN DROP DATABASE ROLLBACK TO SAVEPOINT

CHECKPOINT DROP DOMAIN SAVEPOINT

CLOSE DROP FUNCTION SELECT

CLUSTER DROP GROUP SELECT INTO

COMMENT DROP INDEX SET

COMMIT DROP LANGUAGE SET CONSTRAINTS

COMMIT PREPARED DROP OPERATOR CLASS SET ROLE

COPY DROP OPERATOR SET SESSION AUTHORIZATION

CREATE AGGREGATE DROP ROLE SET TRANSACTION

CREATE CAST DROP RULE SHOW

CREATE CONSTRAINT TRIGGER DROP SCHEMA START TRANSACTION

CREATE CONVERSION DROP SEQUENCE TRUNCATE

CREATE DATABASE DROP TABLE UNLISTEN

CREATE DOMAIN DROP TABLESPACE UPDATE

CREATE FUNCTION DROP TRIGGER VACUUM

CREATE GROUP DROP TYPE

CREATE INDEX DROP USER

　　比如，要获得SQL命令create database的帮助，则输出如下信息：

pgdb=# \help create database

命令: CREATE DATABASE

描述: 创建一个新的数据库

语法:

CREATE DATABASE name

[ [ WITH ] [ OWNER [=] dbowner ]

[ TEMPLATE [=] template ]

[ ENCODING [=] encoding ]

[ TABLESPACE [=] tablespace ]

[ CONNECTION LIMIT [=] connlimit ] ]

pgdb=#

　　三、TPCH测试

　　和前几次测试一样，主要测试数据加载和查询性能。

　　1.准备工作

　　构造测试环境，首先建立一个名为pgdb的数据库，这一步前面安装阶段已经执行过了。然后用psql连接pgdb数据库执行创建表的脚本。\i 命令用于执行一个脚本文件。\d命令显示当前数据库的表。

pgdb=# \i /user1/postgresql/dss.ddl

CREATE TABLE

pgdb=# \d

List of relations

Schema | Name | Type | Owner

--------+----------+-------+----------

public | customer | table | postgres

public | lineitem | table | postgres

public | nation | table | postgres

public | orders | table | postgres

public | part | table | postgres

public | partsupp | table | postgres

public | region | table | postgres

public | supplier | table | postgres...

　　psql有个有用的选项-E,可以显示\命令的真实SQL，如果通过其他应用程序访问数据库，无法用到psql的命令，那么可以利用这个功能查出SQL语句，然后用SQL查询。例如，显示\d命令的SQL语句：

-bash-3.2$ psql -d pgdb -E

pgdb=# \d

********* QUERY **********

SELECT n.nspname as "Schema",

c.relname as "Name",

CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",

r.rolname as "Owner"

FROM pg_catalog.pg_class c

JOIN pg_catalog.pg_roles r ON r.oid = c.relowner

LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace

WHERE c.relkind IN ('r','v','S','')

AND n.nspname NOT IN ('pg_catalog', 'pg_toast')

AND pg_catalog.pg_table_is_visible(c.oid)

ORDER BY 1,2;

**************************

　　2.数据加载

　　PostgreSQL的大容量文本数据文件导入/导出命令是COPY，COPY 在 PostgreSQL表和标准文件系统文件之间交换数据。 COPY TO 把一个表的所有内容都拷贝到一个文件，而 COPY FROM 从一个文件里拷贝数据到一个表里，也可以把数据附加到表中已经存在的内容里，可以有效地减少导入日志的产生，达到高速导入的目的。它的命令行格式是:

COPY tablename [ ( column [, ...] ) ]

FROM { 'filename' | STDIN }

[ [ WITH ]

[ BINARY ]

[ OIDS ]

[ DELIMITER [ AS ] 'delimiter' ]

[ NULL [ AS ] 'null string' ]

[ CSV [ HEADER ]

[ QUOTE [ AS ] 'quote' ]

[ ESCAPE [ AS ] 'escape' ]

[ FORCE NOT NULL column [, ...] ]

　　通常用到的是文本格式，只要指定表名、文件名和列分隔符。下面举例说明。

　　Tpch的数据以tbl.gz格式保存，因为copy命令支持命名管道，而又不支持在最后一列后加列分隔符，所以用命名管道同时解压缩和替换分隔符。

-bash-3.2$ ls -l /user1/app/oradata/tpch2/*tbl.gz

-rw-r--r-- 1 oracle oinstall 90373097 Feb 2 19:10 /user1/app/oradata/tpch2/customer.tbl.gz

-rw-r--r-- 1 oracle oinstall 2304949340 Feb 2 19:17 /user1/app/oradata/tpch2/lineitem.tbl.gz

-rw-r--r-- 1 oracle oinstall 498102977 Feb 2 19:19 /user1/app/oradata/tpch2/orders.tbl.gz

-rw-r--r-- 1 oracle oinstall 287463641 Feb 2 19:20 /user1/app/oradata/tpch2/partsupp.tbl.gz

-rw-r--r-- 1 oracle oinstall 270 Feb 2 19:20 /user1/app/oradata/tpch2/region.tbl.gz

-rw-r--r-- 1 oracle oinstall 5657118 Feb 2 19:20 /user1/app/oradata/tpch2/supplier.tbl.gz

-bash-3.2$ pwd

/opt/postgres/9.0/bin

-bash-3.2$ gzip --stdout -d /user1/app/oradata/tpch2/region.tbl.gz

0|AFRICA|lar deposits. blithely final packages cajole. regular waters are final requests. regular accounts are according to |

1|AMERICA|hs use ironic, even requests. s|

2|ASIA|ges. thinly even pinto beans ca|

3|EUROPE|ly final courts cajole furiously final excuse|

4|MIDDLE EAST|uickly special accounts cajole carefully blithely close requests. carefully final asymptotes haggle furiousl|

--用sed命令删除行尾|

-bash-3.2$ gzip --stdout -d /user1/app/oradata/tpch2/region.tbl.gz |sed 's/|\r/\r/' > /user1/daa &

[2] 29069

-bash-3.2$ psql pgdb

pgdb=# \timing

Timing is on.

pgdb=# COPY region FROM '/user1/daa' WITH DELIMITER AS '|';

COPY 5

Time: 3.085 ms

pgdb=# select * from region;

r_regionkey | r_name | r_comment

-------------+---------------------------+----------------------------------------------

0 | AFRICA | lar deposits. blithely final packages cajole. regular waters are final requests. regular accounts are according to

1 | AMERICA | hs use ironic, even requests. s

2 | ASIA | ges. thinly even pinto beans ca

3 | EUROPE | ly final courts cajole furiously final excuse

4 | MIDDLE EAST | uickly special accounts cajole carefully blithely close requests. carefully final asymptotes haggle furiousl

(5 rows)

Time: 1.574 ms

　　8个表用copy from命令导入的时间分别是12秒、556秒、100秒、46秒、16秒、1秒、0秒、0秒，总时间731秒。

　　所有表格加载完成后，用VACUUM ANALYZE命令收集统计信息。

pgdb=# VACUUM ANALYZE;

VACUUM

Time: 76961.460 ms

　　导入过程中，系统提示检查点发生太频繁，建议修改配置参数，但修改后该提示仍然存在。

pgdb=# copy partsupp FROM '/user1/daa' WITH DELIMITER AS '|';

LOG: checkpoints are occurring too frequently (1 second apart)

HINT: Consider increasing the configuration parameter "checkpoint_segments".

LOG: checkpoints are occurring too frequently (2 seconds apart)

HINT: Consider increasing the configuration parameter "checkpoint_segments".

-bash-3.2$ ./pg_ctl -D /user1/postgresql/data stop

LOG: received smart shutdown request

waiting for server to shut down....LOG: autovacuum launcher shutting down

LOG: shutting down

LOG: database system is shut down

done

server stopped

[1]+ Done ./postgres -D /user1/postgresql/data

-bash-3.2$ vi postgresql.conf

# - Checkpoints -

#checkpoint_segments = 30 # in logfile segments, min 1, 16MB each

#checkpoint_timeout = 5min # range 30s-1h

#checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0

#checkpoint_warning = 30s # 0 disables

　　3.测试数据和查询语句的产生步骤

　　主要步骤参照本系列第一篇文章《Oracle 11g R2企业版评测》。然后需要针对PostgreSQL的特性作修改。

　　PostgreSQLr对SQL语句的特殊要求只有1点：

　　日期间隔的表达式。不支持interval 'n' year/month等SQL 92写法，要改为interval 'n year'和interval 'n month'。

　　将修改完成后的22个查询语句保存为mssql_tpch.sql文件。同时在文件开始处加入\timing

　　以显示SQL运行时间。

　　4.数据压缩测试

　　PostgreSQL的压缩功能和其他数据库有所区别。数据库并没有显式的表压缩命令。只有一个TOAST技术与压缩有关。

　　因为PostgreSQL的页面大小是固定的(通常是8Kb)，并且不允许元组跨越多个页面，因此不可能直接存储非常大的字段值。在　PostgreSQL 7.1　之前，代码里有一个硬限制，限制了一个表中一个数据行存储的数据的总大小为刚好略小于一个页面。从版本 7.1 以及以后的版本开始，这个限制被克服了，方法是允许大的字段值被压缩和/或打碎成多个物理行。这些事情对用户都是透明的，只是在后端代码上有一些小的影响。这个技术的简称是TOAST(超尺寸字段存储技术-The Oversized-Attribute Storage Technique)。

　　TOAST技术只有在变长的且记录长度可能超过1个页面时才会启用，对于tpch测试数据，最长的记录不超过1KB,因此不存在这种可能性。

　　5.数据查询

　　由于原始没有添加主键和外键的表执行查询的速度相当慢，比如第2个查询，基本不可能在可接受的时间内获得结果，因此，不进行原始表的测试，只测试添加主键和外键后的结果。由于PostgreSQL在添加外键时并不创建相应列上的索引，而索引对查询影响较大，故分别测试只添加外键和添加外键列上的索引二种情况。

　　添加主键和外键的步骤如下：

pgdb=# \i /user1/postgresql/data/dss.ri

psql:/user1/postgresql/data/dss.ri:18: NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "region_pkey" for table "region"

ALTER TABLE

Time: 92.402 ms

psql:/user1/postgresql/data/dss.ri:22: NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "nation_pkey" for table "nation"

ALTER TABLE

Time: 1.972 ms

psql:/user1/postgresql/data/dss.ri:31: NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "part_pkey" for table "part"

ALTER TABLE

Time: 3782.712 ms

psql:/user1/postgresql/data/dss.ri:37: NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "supplier_pkey" for table "supplier"

ALTER TABLE

Time: 111.763 ms

psql:/user1/postgresql/data/dss.ri:46: NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "partsupp_pkey" for table "partsupp"

ALTER TABLE

Time: 15464.902 ms

psql:/user1/postgresql/data/dss.ri:52: NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "customer_pkey" for table "customer"

ALTER TABLE

Time: 2647.225 ms

psql:/user1/postgresql/data/dss.ri:61: NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "lineitem_pkey" for table "lineitem"

ALTER TABLE

Time: 111591.310 ms

psql:/user1/postgresql/data/dss.ri:67: NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "orders_pkey" for table "orders"

ALTER TABLE

Time: 25732.725 ms

pgdb=# VACUUM ANALYZE;

VACUUM

Time: 5025.876 ms

…

--原始脚本创建外键约束的SQL格式不符合PostgreSQL的要求，修改后重新执行

pgdb=# ALTER TABLE NATION

pgdb-# ADD CONSTRAINT NATION_FK1 FOREIGN KEY (N_REGIONKEY) references REGION;

ALTER TABLE

Time: 52.767 ms

pgdb=# ALTER TABLE SUPPLIER

pgdb-# ADD CONSTRAINT SUPPLIER_FK1 FOREIGN KEY (S_NATIONKEY) references NATION;

ALTER TABLE

Time: 48.209 ms

pgdb=# ALTER TABLE CUSTOMER

pgdb-# ADD CONSTRAINT CUSTOMER_FK1 FOREIGN KEY (C_NATIONKEY) references NATION;

ALTER TABLE

Time: 700.381 ms

pgdb=# ALTER TABLE PARTSUPP

pgdb-# ADD CONSTRAINT PARTSUPP_FK2 FOREIGN KEY (PS_PARTKEY) references PART;

ALTER TABLE

Time: 8117.485 ms

pgdb=# ALTER TABLE ORDERS

pgdb-# ADD CONSTRAINT ORDERS_FK1 FOREIGN KEY (O_CUSTKEY) references CUSTOMER;

ALTER TABLE

Time: 20213.284 ms

pgdb=# ALTER TABLE LINEITEM

pgdb-# ADD CONSTRAINT LINEITEM_FK1 FOREIGN KEY (L_ORDERKEY) references ORDERS;

ALTER TABLE

Time: 60656.277 ms

pgdb=# ALTER TABLE LINEITEM

pgdb-# ADD CONSTRAINT LINEITEM_FK2 FOREIGN KEY (L_PARTKEY,L_SUPPKEY) references

pgdb-# PARTSUPP;

ALTER TABLE

Time: 204054.318 ms

pgdb=# VACUUM ANALYZE;

VACUUM

Time: 5153.792 ms

　　尽管创建了主键，还是有2个查询，第17和第20个查询语句无法在可接受的时间内执行完成，为了得到全部查询结果，我们在主键列上创建索引。

　　创建索引的语句如下：

pgdb=# create index supplier_fk1 on supplier(s_nationkey);

CREATE INDEX

时间: 241.663 ms

pgdb=# create index customer_fk1 on customer(c_nationkey);

CREATE INDEX

时间: 4671.995 ms

pgdb=# create index partsupp_fk1 on partsupp(ps_suppkey);

CREATE INDEX

时间: 20420.121 ms

pgdb=#

pgdb=# create index partsupp_fk2 on partsupp(ps_partkey);

CREATE INDEX

时间: 14487.640 ms

pgdb=# create index orders_fk1 on orders(o_custkey);

CREATE INDEX

时间: 38558.701 ms

pgdb=# drop index orders_custkey;

DROP INDEX

时间: 98.454 ms

pgdb=# create index lineitem_fk1 on lineitem(l_orderkey);

CREATE INDEX

时间: 103888.944 ms

pgdb=# create index lineitem_fk2 on lineitem(l_partkey,l_suppkey);

CREATE INDEX

时间: 187944.419 ms

pgdb=# \di

List of relations

Schema | Name | Type | Owner | Table

--------+---------------+-------+----------+----------

public | customer_fk1 | index | postgres | customer

public | customer_pkey | index | postgres | customer

public | lineitem_fk1 | index | postgres | lineitem

public | lineitem_fk2 | index | postgres | lineitem

public | lineitem_pkey | index | postgres | lineitem

public | nation_pkey | index | postgres | nation

public | orders_fk1 | index | postgres | orders

public | orders_pkey | index | postgres | orders

public | part_pkey | index | postgres | part

public | partsupp_fk1 | index | postgres | partsupp

public | partsupp_fk2 | index | postgres | partsupp

public | partsupp_pkey | index | postgres | partsupp

public | region_pkey | index | postgres | region

public | supplier_fk1 | index | postgres | supplier

public | supplier_pkey | index | postgres | supplier

(15 rows)

　　可以用9.0版新引入的pg_relation_size函数获得某个表的空间占用，代替了以往版本的pg_relation_size,后者也依然可以使用。

select pg_table_size('customer');

pg_table_size

---------------

296615936

(1 row)

pgdb=# SELECT nspname || '.' || relname AS "relation",

pgdb-# pg_size_pretty(pg_relation_size(C.oid)) AS "size"

pgdb-# FROM pg_class C

pgdb-# LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)

pgdb-# WHERE nspname NOT IN ('pg_catalog', 'information_schema')

pgdb-# ORDER BY pg_relation_size(C.oid) DESC

pgdb-# ;

relation | size

-------------------------------+------------

public.lineitem | 9257 MB

public.orders | 2093 MB

public.partsupp | 1385 MB

public.lineitem_fk2 | 1285 MB

public.lineitem_fk1 | 1285 MB

public.lineitem_pkey | 1285 MB

public.part | 325 MB

public.orders_fk1 | 321 MB

public.orders_pkey | 321 MB

public.customer | 283 MB

public.partsupp_fk1 | 171 MB

public.partsupp_pkey | 171 MB

public.partsupp_fk2 | 171 MB

public.part_pkey | 43 MB

public.customer_fk1 | 32 MB

public.customer_pkey | 32 MB

public.supplier | 18 MB

public.supplier_fk1 | 2208 kB

public.supplier_pkey | 2208 kB

pg_toast.pg_toast_2618 | 208 kB

pg_toast.pg_toast_2619 | 80 kB

pg_toast.pg_toast_2618_index | 16 kB

public.nation_pkey | 16 kB

public.region_pkey | 16 kB

pg_toast.pg_toast_2619_index | 16 kB

　　利用前面第一步产生的查询脚本，执行测试的命令行如下,查询结果输出到不同的log文件，以便于比较,分别在创建索引前后执行，注意在更改表存储和索引类型后，更新统计信息。

-bash-3.2$ cd /opt/postgres/9.0/bin

-bash-3.2$ date;./psql -d pgdb -f /user1/postgresql/data/q_a.sql -o /user1/postgresql/data/q_a.log;date

2011年 06月 16日星期四 06:37:25 CST

Timing is on.

Time: 381708.096 ms

Time: 13593.186 ms

Time: 51092.852 ms

Time: 11243.724 ms

Time: 60982.194 ms

Time: 25267.713 ms

Time: 46285.189 ms

Time: 13351.568 ms

Time: 197846.585 ms

Time: 49444.851 ms

Time: 6856.866 ms

Time: 36848.254 ms

Time: 48407.148 ms

Time: 25035.739 ms

Time: 4.456 ms

Time: 53304.458 ms

Time: 1.491 ms

Time: 18002.088 ms

Time: 3930.280 ms

Time: 97068.386 ms

Time: 37516.387 ms

Time: 7486.682 ms

Time: 143849.900 ms

Time: 5600.030 ms

2011年 06月 16日星期四 06:59:40 CST

　　从上述日志中可以看出，添加索引后，PostgreSQL对tpch的22个查询大约在22分钟可以完成，没有需要特别长时间才能完成的测试，最长的第一个查询也不过6分钟多。

　　下面是各组查询测试结果，取实际运行时间。

3.测试数据和查询语句的产生步骤
▲表1 TPC-H scale=10各种压缩和索引数据的测试对比，单位：毫秒

　　从表1可知，如果没有索引，大部分查询都能正常执行，加上索引后，大部分查询的用时差别不大，但字体加粗的查询则有明显的提高，基本上没有查询时间恶化的情况。这说明PostgreSQL的查询优化器还是比较强大的，从下一节执行计划的输出中可以看出，它采用基于成本的优化器技术，并且用到了hash join等高效的方法。

　　6.性能调整和优化

　　性能调整和优化，涵盖表结构设计、存储方式设计、查询设计、参数调整等方面，前文介绍的压缩和并行都是简单的参数调整手段，如果是实际的查询，而不是基准测试，我们就需要充分利用PostgreSQL数据库的功能，针对每个查询单独优化。

　　1.执行计划的查看

　　要利用改写查询语句提高执行效率，首先要了解现有的执行计划。

　　执行计划的查看，使用explain命令后跟需要查看计划的查询语句。以第17个查询为例，原始写法和修改写法分别采用相关子查询和表连接方式。

pgdb=# explain -- 17

pgdb-# select

pgdb-# sum(l_extendedprice) / 7.0 as avg_yearly

pgdb-# from

pgdb-# lineitem,

pgdb-# part

pgdb-# where

pgdb-# p_partkey = l_partkey

pgdb-# and p_brand = 'Brand#23'

pgdb-# and p_container = 'MED CAN'

pgdb-# and l_quantity < (

pgdb(# select

pgdb(# 0.2 * avg(l_quantity)

pgdb(# from

pgdb(# lineitem

pgdb(# where

pgdb(# l_partkey = p_partkey

pgdb(# ) limit 10;

QUERY PLAN

----------------------------------------------------------------------------------------------------------

Limit (cost=9090485.90..9090485.91 rows=1 width=10)

-> Aggregate (cost=9090485.90..9090485.91 rows=1 width=10)

-> Nested Loop (cost=0.00..9090431.30 rows=21839 width=10)

Join Filter: (public.lineitem.l_quantity < (SubPlan 1))

-> Seq Scan on part (cost=0.00..71608.29 rows=2081 width=4)

Filter: ((p_brand = 'Brand#23'::bpchar) AND (p_container = 'MED CAN'::bpchar))

-> Index Scan using lineitem_fk2 on lineitem (cost=0.00..134.05 rows=31 width=21)

Index Cond: (public.lineitem.l_partkey = part.p_partkey)

SubPlan 1

-> Aggregate (cost=135.45..135.46 rows=1 width=7)

-> Index Scan using lineitem_fk2 on lineitem (cost=0.00..135.37 rows=31 width=7)

Index Cond: (l_partkey = $0)

(12 行)

时间: 3.414 ms

pgdb=# explain -- 17c

pgdb-# select

pgdb-# sum(l_extendedprice) / 7.0 as avg_yearly

pgdb-# from (select l_extendedprice, l_partkey ,l_quantity

pgdb(# from

pgdb(# lineitem,

pgdb(# part

pgdb(# where

pgdb(# p_partkey = l_partkey

pgdb(# and p_brand = 'Brand#23'

pgdb(# and p_container = 'MED CAN'

pgdb(# )b

pgdb-# ,(select l_partkey,0.2 * avg(l_quantity) p2avg_l_quantity

pgdb(# from

pgdb(# (select l_partkey,l_quantity from

pgdb(# lineitem

pgdb(# where

pgdb(# l_partkey in (select p_partkey from part where p_brand = 'Brand#23'and p_container = 'MED CAN')

pgdb(# )a

pgdb(# group by l_partkey

pgdb(# )c

pgdb-# where b.l_partkey=c.l_partkey and b.l_quantity <p2avg_l_quantity

pgdb-# ;

QUERY PLAN

----------------------------------------------------------------------------------------------------------------------

Aggregate (cost=746887.13..746887.14 rows=1 width=10)

-> Hash Join (cost=710378.97..745168.26 rows=687545 width=10)

Hash Cond: (public.lineitem.l_partkey = public.part.p_partkey)

Join Filter: (public.lineitem.l_quantity < ((0.2 * avg(public.lineitem.l_quantity))))

-> GroupAggregate (cost=357749.59..359223.70 rows=65516 width=11)

-> Sort (cost=357749.59..357913.38 rows=65516 width=11)

Sort Key: public.lineitem.l_partkey

-> Nested Loop (cost=71613.49..351388.45 rows=65516 width=11)

-> HashAggregate (cost=71613.49..71634.30 rows=2081 width=4)

-> Seq Scan on part (cost=0.00..71608.29 rows=2081 width=4)

Filter: ((p_brand = 'Brand#23'::bpchar) AND (p_container = 'MED CAN'::bpchar))

-> Index Scan using lineitem_fk2 on lineitem (cost=0.00..134.05 rows=31 width=11)

Index Cond: (public.lineitem.l_partkey = public.part.p_partkey)

-> Hash (cost=351362.44..351362.44 rows=65516 width=25)

-> Nested Loop (cost=0.00..351362.44 rows=65516 width=25)

-> Seq Scan on part (cost=0.00..71608.29 rows=2081 width=4)

Filter: ((p_brand = 'Brand#23'::bpchar) AND (p_container = 'MED CAN'::bpchar))

-> Index Scan using lineitem_fk2 on lineitem (cost=0.00..134.05 rows=31 width=21)

Index Cond: (public.lineitem.l_partkey = public.part.p_partkey)

(19 行)

时间: 2.887 ms

　　EXPLAIN命令较快地给出了执行计划。从估计的执行成本来看，第一个语句的成本比第二个语句要大，实际执行的结果，第2个语句大约2.6秒，比第1个语句的4秒提高了不少。

　　注意这个命令只是显示SQL语句的执行计划，并不实际执行该语句。另外，explain analyse命令实际执行查询，并显示每个节点实际的查询时间，但由于添加了计时器负载，这种查看计划的方式比正常执行时间会长。

pgdb=# explain analyze select

pgdb-# sum(l_extendedprice) / 7.0 as avg_yearly

pgdb-# from

pgdb-# lineitem,

pgdb-# part

pgdb-# where

pgdb-# p_partkey = l_partkey

pgdb-# and p_brand = 'Brand#23'

pgdb-# and p_container = 'MED CAN'

pgdb-# and l_quantity < (

pgdb(# select

pgdb(# 0.2 * avg(l_quantity)

pgdb(# from

pgdb(# lineitem

pgdb(# where

pgdb(# l_partkey = p_partkey

pgdb(# ) limit 10;

QUERY PLAN

-------------------------------------------------------------------------------------------------------------- Limit (cost=9021151.04..9021151.05 rows=1 width=10) (actual time=4464.833..4464.834 rows=1 loops=1)

-> Aggregate (cost=9021151.04..9021151.05 rows=1 width=10) (actual time=4464.831..4464.831 rows=1 loops=1)

-> Nested Loop (cost=0.00..9021098.49 rows=21020 width=10) (actual time=2.090..4461.326 rows=5387 loops=1)

Join Filter: (public.lineitem.l_quantity < (SubPlan 1))

-> Seq Scan on part (cost=0.00..71607.18 rows=2065 width=4) (actual time=0.290..702.496 rows=2031 loops=1)

Filter: ((p_brand = 'Brand#23'::bpchar) AND (p_container = 'MED CAN'::bpchar))

-> Index Scan using lineitem_fk2 on lineitem (cost=0.00..134.05 rows=31 width=21) (actual time=0.031..0.048 rows=30 loops=2031)

Index Cond: (public.lineitem.l_partkey = part.p_partkey)

SubPlan 1

-> Aggregate (cost=135.45..135.46 rows=1 width=7) (actual time=0.058..0.059 rows=1 loops=61007)

-> Index Scan using lineitem_fk2 on lineitem (cost=0.00..135.37 rows=31 width=7) (actual time=0.004..0.027 rows=31 loops=61007)

Index Cond: (l_partkey = $0)

Total runtime: 4465.003 ms

(13 rows)

Time: 4469.102 ms

　　PostgreSQL提供了一些影响执行计划的开关，比如enable_nestloop，可以启用或禁用nested loop，但这些开关的使用要非常小心，有时候虽然把nested loop改为了其他关联，但导致了全表扫描，那么查询性能非但不能提高，还会下降，比如第17个查询，从估计的执行成本来看，比设置前要大一些，实际执行的结果，比设置前多用了10倍多的时间。

pgdb=# set enable_nestloop=off;

SET

Time: 0.418 ms

pgdb=# explain select

pgdb-# sum(l_extendedprice) / 7.0 as avg_yearly

pgdb-# from

pgdb-# lineitem,

pgdb-# part

pgdb-# where

pgdb-# p_partkey = l_partkey

pgdb-# and p_brand = 'Brand#23'

pgdb-# and p_container = 'MED CAN'

pgdb-# and l_quantity < (

pgdb(# select

pgdb(# 0.2 * avg(l_quantity)

pgdb(# from

pgdb(# lineitem

pgdb(# where

pgdb(# l_partkey = p_partkey

pgdb(# ) limit 10;

QUERY PLAN

----------------------------------------------------------------------------------------------------------

Limit (cost=10372631.10..10372631.12 rows=1 width=10)

-> Aggregate (cost=10372631.10..10372631.12 rows=1 width=10)

-> Hash Join (cost=71632.14..10372580.56 rows=20216 width=10)

Hash Cond: (public.lineitem.l_partkey = part.p_partkey)

Join Filter: (public.lineitem.l_quantity < (SubPlan 1))

-> Seq Scan on lineitem (cost=0.00..1784768.72 rows=59988572 width=21)

-> Hash (cost=71607.32..71607.32 rows=1986 width=4)

-> Seq Scan on part (cost=0.00..71607.32 rows=1986 width=4)

Filter: ((p_brand = 'Brand#23'::bpchar) AND (p_container = 'MED CAN'::bpchar))

SubPlan 1

-> Aggregate (cost=135.45..135.46 rows=1 width=7)

-> Index Scan using lineitem_fk2 on lineitem (cost=0.00..135.37 rows=31 width=7)

Index Cond: (l_partkey = $0)

(13 rows)

Time: 5.140 ms

--实际的执行结果：

QUERY PLAN

-----------------------------------------------------------------------------------------------------

Limit (cost=10372631.10..10372631.12 rows=1 width=10) (actual time=50432.309..50432.311 rows=1 loops=1)

-> Aggregate (cost=10372631.10..10372631.12 rows=1 width=10) (actual time=50432.307..50432.308 rows=1 loops=1)

-> Hash Join (cost=71632.14..10372580.56 rows=20216 width=10) (actual time=767.304..50425.289 rows=5387 loops=1)

Hash Cond: (public.lineitem.l_partkey = part.p_partkey)

Join Filter: (public.lineitem.l_quantity < (SubPlan 1))

-> Seq Scan on lineitem (cost=0.00..1784768.72 rows=59988572 width=21) (actual time=0.047..15139.633 rows=59986052 loops=1)

-> Hash (cost=71607.32..71607.32 rows=1986 width=4) (actual time=761.069..761.069 rows=2031 loops=1)

Buckets: 1024 Batches: 1 Memory Usage: 72kB

-> Seq Scan on part (cost=0.00..71607.32 rows=1986 width=4) (actual time=0.364..758.448 rows=2031 loops=1)

Filter: ((p_brand = 'Brand#23'::bpchar) AND (p_container = 'MED CAN'::bpchar))

SubPlan 1

-> Aggregate (cost=135.45..135.46 rows=1 width=7) (actual time=0.347..0.347 rows=1 loops=61007)

-> Index Scan using lineitem_fk2 on lineitem (cost=0.00..135.37 rows=31 width=7) (actual time=0.034..0.304 rows=31 loops=61007)

Index Cond: (l_partkey = $0)

Total runtime: 50432.499 ms

(15 rows)

Time: 50434.793 ms

pgdb=#

　　2.统计信息收集和管理

　　正确的统计信息对查询优化器得出较好的执行计划有十分重要的影响，在大量插入或更新数据以后，包括对表进行rebuild后，需要重新收集统计信息。前面已经使用了VACUUM ANALYZE命令。

　　这个命令其实是VACUUM命令的一种形式，表示垃圾收集以分析一个数据库，VACUUM命令语法如下：

VACUUM [ FULL | FREEZE ] [ VERBOSE ] [ table ]

VACUUM [ FULL | FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ]

　　VACUUM 回收已删除记录占据的存储空间。在一般的 PostgreSQL 操作里，那些已经 DELETE 的记录或者被 UPDATE 过后过时的记录是没有从它们所属的表中物理删除的; 在完成 VACUUM 之前它们仍然存在。因此我们有必须周期地运行 VACUUM，特别是在常更新的表上。

　　如果没有参数，VACUUM 处理当前数据库里每个表，如果有参数，VACUUM 只处理那个表。

　　VACUUM ANALYZE 先执行一个 VACUUM 然后是给每个选定的表执行一个 ANALYZE。对于日常维护脚本而言，这是一个很方便的组合。

　　简单的 VACUUM (没有FULL)只是简单地回收空间并且令其可以再次使用。这种形式的命令可以和对表的普通读写并行操作，因为没有请求排他锁。VACUUM FULL 执行更广泛的处理，包括跨块移动记录，以便把表压缩到最少的磁盘块数目里。这种形式要慢许多并且在处理的时候需要在表上施加一个排它锁。

　　下面再介绍另一个ANALYZE命令，可以收集指定表的统计信息。语法如下：

ANALYZE [ VERBOSE ] [ table [ (column [, ...] ) ] ]

　　ANALYZE 收集有关 PostgreSQL 表的内容的统计，然后把结果保存在系统表 pg_statistic 里。随后，查询优化器就可以使用这些统计帮助判断查询的最有效的规划。

　　如果没有参数，ANALYZE 检查在当前数据库里的所有表。如果有参数，ANALYZE 只检查那个表。还可以给出一列字段名字，这个时候只收集那些字段的统计信息。

　　比如，对part表收集统计信息。

pgdb=# analyze part;

ANALYZE

Time: 667.500 ms

　　周期性地运行 ANALYZE，或者在对表的大部分内容做了更改之后马上运行它是个好习惯，准确的统计信息将帮助优化器选择最合适的查询规划，并因此而改善查询处理的速度。一种比较经常采用的策略是每天在低负荷的时候运行一次 VACUUM 和 ANALYZE。

　　和 VACUUM FULL 不同的是， ANALYZE 只需要在目标表上有一个读取锁，因此它可以和表上的其它活动并行地运行。

　　收集的统计信息通常包括一个每字段最常用数值的列表以及一个包线图，显示每个字段里数据的近似分布。

　　对于大表，ANALYZE 采集表内容的一个随机的抽样做统计，而不是检查每一行。这样就保证了即使是在很大的表上，我们也只需要很少的一些时间就可以完成分析。不过，要注意的是统计只是近似的结果，在很小的概率的情况下，这个不确定的行为会导致查询优化器在不同 ANALYZE 之间选择不同的查询规划。为了避免这个问题，可以提高 ANALYZE 收集的统计数量。

　　PostgreSQL还有很多功能，如分析函数、递归with查询，已经超出了tpc-h测试的范围，留给读者测试。

　　四、小结

　　经过这次测试，我们对PostgreSQL数据库已经有了初步的印象，安装比较简便，安装包也体积较小，但功能还是很强大，对SQL标准的支持较好，总体性能也比较好，比如数据文件导入和查询。其次，对于TPC-H这种分析型查询，添加主外键约束后能产生较好的执行计划。优化器产生执行计划的速度也比较快。提供了跨平台的安装包，便于各类用户使用。第3方厂商提供了一个图形化的管理工具pgadmin，某些平台的安装包也集成了该工具，便于PostgreSQL数据库管理和开发。

　　存在的问题，首先，不知是否是免费开源数据库的通病，不支持单个查询并行执行，要充分利用硬件资源的只能依赖多个连接并发处理。其次，虽然号称是非常先进的开源数据库，仍缺少数据压缩功能，分区功能也比较薄弱。再有，数据库的一些概念和功能比较独特，比如集群、用户组和继承，以及一些特殊的操作符，需要花一定时间学习，如果用到了，会导致应用可移植性略差。最后，文档和支持，PostgreSQL的文档较少，相关的书籍也较少，性能优化的难度较高。

　　总的来说，PostgreSQL是一个功能强大，性能优良，运行稳定的免费数据库，适合于各种OLTP系统，但对于批处理的分析类系统，在硬件资源利用方面还有待提高。