postgresql例子的简单介绍

怎样用postgresql建表，建数据库

PostgreSQL的CREATE TABLE语句是用来在任何指定的的数据库中创建一个新表。 yiibai.com

语法

CREATE TABLE语句的基本语法如下：

CREATE TABLE table_name(

column1 datatype,

column2 datatype,

column3 datatype,

.....

columnN datatype,

PRIMARY KEY( one or more columns )

);

CREATE TABLE是告诉数据库系统关键字，创建一个新的表。独特的名称或标识如下表CREATE TABLE语句。当前数据库中的表最初是空的，并且将所拥有的用户发出的命令。

然后在括号内来定义每一列的列表，在表中是什么样的数据类型。其语法变得更清晰，下面的例子。

实例

下面是一个例子，它创建了一个公司ID作为主键的表和NOT NULL的约束显示这些字段不能为NULL，同时创建该表的记录：

CREATE TABLE COMPANY(

ID INT PRIMARY KEY NOT NULL,

NAME TEXT NOT NULL,

AGE INT NOT NULL,

ADDRESS CHAR(50),

SALARY REAL

);

让我们创建一个表，在随后的章节中，我们将在练习中使用：

CREATE TABLE DEPARTMENT(

ID INT PRIMARY KEY NOT NULL,

DEPT CHAR(50) NOT NULL,

EMP_ID INT NOT NULL

);

可以验证已成功创建使用\d命令，将用于列出了附加的数据库中的所有表。

testdb-# \d

以上PostgreSQL的表会产生以下结果：

List of relations

Schema | Name | Type | Owner

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

public | company | table | postgres

public | department | table | postgres

(2 rows)

使用\d表名来描述每个表如下所示：

testdb-# \d company

以上PostgreSQL的表会产生以下结果：

Table "public.company"

Column | Type | Modifiers

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

id | integer | not null

name | text | not null

age | integer | not null

address | character(50) |

salary | real |

join_date | date |

Indexes:

"company_pkey" PRIMARY KEY, btree (id)

相关问答

Q1: PostgreSQL全文检索简介

PostgreSQL自带有一个简易的全文检索引擎，可以实现小规模数据量的全文检索功能。本文我们将引导介绍一下这个功能，对于小数据量的搜索这个功能是足够使用的，而无需搭建额外的ES等重量级的全文检索服务器。

PG的全文检索操作符是 @@ ，当一个 tsvector (文档)和 tsquery (条件)匹配时返回 true ，并且前后顺序无影响:

和普通的SQL查询一样，只要在 WHERE 条件中使用这个符号就代表使用全文检索条件筛选文档了。如:

@@ 操作符支持隐式转换，对于 text 类型可以无需强类型转换( ::tsvector 或 to_tsvector(config_name, text) )，所以这个操作符实际支持的参数类型是这样的:

tsquery 查询条件并不是简单的正则，而是一组搜索术语，使用并且使用布尔操作符 （AND）、 | （OR）和 ! （NOT）来组合它们，还有短语搜索操作符 - （FOLLOWED BY）。更详细的语法参见 此文档 。

此外，PostgreSQL还提供了两个相对简化的版本 plainto_tsquery 和 phraseto_tsquery 。

plainto_tsquery ( plainto_tsquery([ config regconfig, ] querytext text) returns tsquery )用户将未格式化的 text 经过分词之后，插入 符号转为 tsquery :

phraseto_tsquery ( phraseto_tsquery([ config regconfig, ] querytext text) returns tsquery )行为和 plainto_tsquery 行为类似，但是分词之后不是插入 而是 - (FOLLOWED BY):

使用索引可以加快全文检索的速度。对于全文检索来说，可选的索引类型是 GIN (通用倒排索引)和 GIST (通用搜索树)，官方文档更推荐使用 GIN索引 。创建一个 GIN 索引的范例:

也可以是一个连接列:

还可以单独创建一个 tsvector 列，为这个列创建索引:

除了普通的 ORDER BY 条件之外，PostgreSQL为全文检索提供了两个可选的排序函数 ts_rank([ weights float4[], ] vector tsvector, query tsquery [, normalization integer ]) returns float4 和 ts_rank_cd([ weights float4[], ] vector tsvector, query tsquery [, normalization integer ]) returns float4 ，以便实现基于 权重 的排序。

此外，对于PostgreSQL 9.6以上的版本还可以使用 RUM index 排序。(注意，这个是扩展，默认不包含)。

PostgreSQL默认的分词字典中并不包含中文分词字典，因此我们必须手工引入。目前一个比较好的项目是 zhparser ，同时这个插件也是阿里云的RDS默认包含的。安装和启用没什么好说的。值得一提的是分词配置参数。

在 CREATE EXTENSION 之后，必须配置分词参数才能正确进行分词和查找，否则什么都查不到。官方文档提供的一个配置策略是:

n,v,a,i,e,l 这几个字母分别表示一种token策略，只启用了这几种token mapping，其余则被屏蔽。具体支持的参数和含义可以用 \dFp+ zhparser 显示:

WITH simple 表示词典使用的是内置的simple词典，即仅做小写转换。根据需要可以灵活定义词典和token映射，以实现屏蔽词和同义词归并等功能。

比如我们看下面这个例子:

可以看到 江淮 这个词组在查询的时候被忽略了，我们启用 j (abbreviation,简称)再看看结果:

所以实际使用中要设置合理的token types，过少将导致搜索结果不准确，过多将导致性能下降。此外，还有一些诸如 短词复合: zhparser.multi_short = f 这一类的控制分词结果的选项，根据实际使用酌情开启。

Q2: postgresql 建立索引

一、索引的类型：

PostgreSQL提供了多种索引类型：B-Tree、Hash、GiST和GIN，由于它们使用了不同的算法，因此每种索引类型都有其适合的查询类型，缺省时，CREATE INDEX命令将创建B-Tree索引。

1. B-Tree:

CREATE TABLE test1 (

id integer,

content varchar

);

CREATE INDEX test1_id_index ON test1 (id);

B-Tree索引主要用于等于和范围查询，特别是当索引列包含操作符" 、=和"作为查询条件时，PostgreSQL的查询规划器都会考虑使用B-Tree索引。在使用BETWEEN、IN、IS NULL和IS NOT NULL的查询中，PostgreSQL也可以使用B-Tree索引。然而对于基于模式匹配操作符的查询，如LIKE、ILIKE、~和 ~*，仅当模式存在一个常量，且该常量位于模式字符串的开头时，如col LIKE 'foo%'或col ~ '^foo'，索引才会生效，否则将会执行全表扫描，如：col LIKE '%bar'。

2. Hash：

CREATE INDEX name ON table USING hash (column);

散列(Hash)索引只能处理简单的等于比较。当索引列使用等于操作符进行比较时，查询规划器会考虑使用散列索引。

这里需要额外说明的是，PostgreSQL散列索引的性能不比B-Tree索引强，但是散列索引的尺寸和构造时间则更差。另外，由于散列索引操作目前没有记录WAL日志，因此一旦发生了数据库崩溃，我们将不得不用REINDEX重建散列索引。

3. GiST：

GiST索引不是一种单独的索引类型，而是一种架构，可以在该架构上实现很多不同的索引策略。从而可以使GiST索引根据不同的索引策略，而使用特定的操作符类型。

4. GIN：

GIN索引是反转索引，它可以处理包含多个键的值(比如数组)。与GiST类似，GIN同样支持用户定义的索引策略，从而可以使GIN索引根据不同的索引策略，而使用特定的操作符类型。作为示例，PostgreSQL的标准发布中包含了用于一维数组的GIN操作符类型，如：、=、等。

二、复合索引：

PostgreSQL中的索引可以定义在数据表的多个字段上，如：

CREATE TABLE test2 (

major int,

minor int,

name varchar

}

CREATE INDEX test2_mm_idx ON test2 (major, minor);

1. B-Tree类型的复合索引：

在B-Tree类型的复合索引中，该索引字段的任意子集均可用于查询条件，不过，只有当复合索引中的第一个索引字段(最左边)被包含其中时，才可以获得最高效率。

2. GiST类型的复合索引：

在GiST类型的复合索引中，只有当第一个索引字段被包含在查询条件中时，才能决定该查询会扫描多少索引数据，而其他索引字段上的条件只是会限制索引返回的条目。假如第一个索引字段上的大多数数据都有相同的键值，那么此时应用GiST索引就会比较低效。

3. GIN类型的复合索引：

与B-Tree和GiST索引不同的是，GIN复合索引不会受到查询条件中使用了哪些索引字段子集的影响，无论是哪种组合，都会得到相同的效率。

使用复合索引应该谨慎。在大多数情况下，单一字段上的索引就已经足够了，并且还节约时间和空间。除非表的使用模式非常固定，否则超过三个字段的索引几乎没什么用处。

三、组合多个索引：

PostgreSQL可以在查询时组合多个索引(包括同一索引的多次使用)，来处理单个索引扫描不能实现的场合。与此同时，系统还可以在多个索引扫描之间组成AND和OR的条件。比如，一个类似WHERE x = 42 OR x = 47 OR x = 53 OR x = 99的查询，可以被分解成四个独立的基于x字段索引的扫描，每个扫描使用一个查询子句，之后再将这些扫描结果OR在一起并生成最终的结果。另外一个例子是，如果我们在x和y上分别存在独立的索引，那么一个类似WHERE x = 5 AND y = 6的查询，就会分别基于这两个字段的索引进行扫描，之后再将各自扫描的结果进行AND操作并生成最终的结果行。

为了组合多个索引，系统扫描每个需要的索引，然后在内存里组织一个BITMAP，它将给出索引扫描出的数据在数据表中的物理位置。然后，再根据查询的需要，把这些位图进行AND或者OR的操作并得出最终的BITMAP。最后，检索数据表并返回数据行。表的数据行是按照物理顺序进行访问的，因为这是位图的布局，这就意味着任何原来的索引的排序都将消失。如果查询中有ORDER BY子句，那么还将会有一个额外的排序步骤。因为这个原因，以及每个额外的索引扫描都会增加额外的时间，这样规划器有时候就会选择使用简单的索引扫描，即使有多个索引可用也会如此。

四、唯一索引：

CREATE UNIQUE INDEX name ON table (column [, ...]);

五、表达式索引：

表达式索引主要用于在查询条件中存在基于某个字段的函数或表达式的结果与其他值进行比较的情况，如：

SELECT * FROM test1 WHERE lower(col1) = 'value';

此时，如果我们仅仅是在col1字段上建立索引，那么该查询在执行时一定不会使用该索引，而是直接进行全表扫描。如果该表的数据量较大，那么执行该查询也将会需要很长时间。解决该问题的办法非常简单，在test1表上建立基于col1字段的表达式索引，如：

CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));

SELECT * FROM people WHERE (first_name || ' ' || last_name) = 'John Smith';

和上面的例子一样，尽管我们可能会为first_name和last_name分别创建独立索引，或者是基于这两个字段的复合索引，在执行该查询语句时，这些索引均不会被使用，该查询能够使用的索引只有我们下面创建的表达式索引。

CREATE INDEX people_names ON people ((first_name || ' ' || last_name));

CREATE INDEX命令的语法通常要求在索引表达式周围书写圆括弧，就像我们在第二个例子里显示的那样。如果表达式只是一个函数调用，那么可以省略，就像我们在第一个例子里显示的那样。

从索引维护的角度来看，索引表达式要相对低效一些，因为在插入数据或者更新数据的时候，都必须为该行计算表达式的结果，并将该结果直接存储到索引里。然而在查询时，PostgreSQL就会把它们看做WHERE idxcol = 'constant'，因此搜索的速度等效于基于简单索引的查询。通常而言，我们只是应该在检索速度比插入和更新速度更重要的场景下使用表达式索引。

六、部分索引：

部分索引(partial index)是建立在一个表的子集上的索引，而该子集是由一个条件表达式定义的(叫做部分索引的谓词)。该索引只包含表中那些满足这个谓词的行。

由于不是在所有的情况下都需要更新索引，因此部分索引会提高数据插入和数据更新的效率。然而又因为部分索引比普通索引要小，因此可以更好的提高确实需要索引部分的查询效率。见以下三个示例：

1. 索引字段和谓词条件字段一致：

CREATE INDEX access_log_client_ip_ix ON access_log(client_ip)

WHERE NOT (client_ip inet '192.168.100.0' AND client_ip inet '192.168.100.255');

下面的查询将会用到该部分索引：

SELECT * FROM access_log WHERE url = '/index.html' AND client_ip = inet '212.78.10.32';

下面的查询将不会用该部分索引：

一个不能使用这个索引的查询可以是

SELECT * FROM access_log WHERE client_ip = inet '192.168.100.23';

2. 索引字段和谓词条件字段不一致：

PostgreSQL支持带任意谓词的部分索引，唯一的约束是谓词的字段也要来自于同样的数据表。注意，如果你希望你的查询语句能够用到部分索引，那么就要求该查询语句的条件部分必须和部分索引的谓词完全匹配。 准确说，只有在PostgreSQL能够识别出该查询的WHERE条件在数学上涵盖了该索引的谓词时，这个部分索引才能被用于该查询。

CREATE INDEX orders_unbilled_index ON orders(order_nr) WHERE billed is not true;

下面的查询一定会用到该部分索引：

SELECT * FROM orders WHERE billed is not true AND order_nr 10000;

那么对于如下查询呢？

SELECT * FROM orders WHERE billed is not true AND amount 5000.00;

这个查询将不像上面那个查询这么高效，毕竟查询的条件语句中没有用到索引字段，然而查询条件"billed is not true"却和部分索引的谓词完全匹配，因此PostgreSQL将扫描整个索引。这样只有在索引数据相对较少的情况下，该查询才能更有效一些。

下面的查询将不会用到部分索引。

SELECT * FROM orders WHERE order_nr = 3501;

3. 数据表子集的唯一性约束：

CREATE TABLE tests (

subject text,

target text,

success boolean,

...

);

CREATE UNIQUE INDEX tests_success_constraint ON tests(subject, target) WHERE success;

该部分索引将只会对success字段值为true的数据进行唯一性约束。在实际的应用中，如果成功的数据较少，而不成功的数据较多时，该实现方法将会非常高效。

七、检查索引的使用：

见以下四条建议：

1. 总是先运行ANALYZE。

该命令将会收集表中数值分布状况的统计。在估算一个查询返回的行数时需要这个信息，而规划器则需要这个行数以便给每个可能的查询规划赋予真实的开销值。如果缺乏任何真实的统计信息，那么就会使用一些缺省数值，这样肯定是不准确的。因此，如果还没有运行ANALYZE就检查一个索引的使用状况，那将会是一次失败的检查。

2. 使用真实的数据做实验。

用测试数据填充数据表，那么该表的索引将只会基于测试数据来评估该如何使用索引，而不是对所有的数据都如此使用。比如从100000行中选1000行，规划器可能会考虑使用索引，那么如果从100行中选1行就很难说也会使用索引了。因为100行的数据很可能是存储在一个磁盘页面中，然而没有任何查询规划能比通过顺序访问一个磁盘页面更加高效了。与此同时，在模拟测试数据时也要注意，如果这些数据是非常相似的数据、完全随机的数据，或按照排序顺序插入的数据，都会令统计信息偏离实际数据应该具有的特征。

3. 如果索引没有得到使用，那么在测试中强制它的使用也许会有些价值。有一些运行时参数可以关闭各种各样的查询规划。

4. 强制使用索引用法将会导致两种可能：一是系统选择是正确的，使用索引实际上并不合适，二是查询计划的开销计算并不能反映现实情况。这样你就应该对使用和不使用索引的查询进行计时，这个时候EXPLAIN ANALYZE命令就很有用了。

Q3: postgresql 创建数据库问题

1、使用CREATE DATABASE

该命令将创建一个数据库PostgreSQL的shell提示符，但你应该有适当的权限来创建数据库。默认情况下，创建新的数据库将通过克隆标准系统数据库template1。

语法：

CREATE DATABASE语句的基本语法如下：

CREATE DATABASE dbname;

其中dbname是要创建的数据库的名称。

例子：

下面是一个简单的例子，这将创建testdb 在PostgreSQL模式：

postgres=# CREATE DATABASE testdb;

postgres-#

2、使用createdb的命令

PostgreSQL命令行可执行createdb是是SQL命令CREATE DATABASE一个包装器。此命令和SQL命令CREATE DATABASE之间唯一的区别是，前者可以直接在命令行中运行，它允许的注释被添加到数据库中，全部在一个命令。

语法：

createdb语法如下所示：

createdb [option...] [dbname [description]]

参数

下表列出了参数及它们的描述。

参数名称            描述

dbname    The name of a database to create.  

description    Specifies a comment to be associated with the newly created database.  

options    command-line arguments which createdb accepts.  

选项

下表列出了命令行参数CREATEDB接收：

选项                            描述

-D tablespace         Specifies the default tablespace for the database.  

-e                     Echo the commands that createdb generates and sends to the server.  

-E encoding    Specifies the character encoding scheme to be used in this database.  

-l locale    Specifies the locale to be used in this database.  

-T template    Specifies the template database from which to build this database.  

--help    Show help about dropdb command line arguments, and exit.  

-h host    Specifies the host name of the machine on which the server is running.  

-p port    Specifies the TCP port or the local Unix domain socket file extension on which the server is listening for connections.  

-U username    User name to connect as.  

-w    Never issue a password prompt.  

-W    Force createdb to prompt for a password before connecting to a database.  

打开命令提示符，然后去是PostgreSQL安装所在的目录。进入到bin目录，执行下面的命令创建一个数据库。

createdb -h localhost -p 5432 -U postgress testdb

password ******

上面的命令会提示Postgres的默认的PostgreSQL管理用户的密码，以便提供密码和继续创建新的数据库。

一旦创建数据库时可以使用上述方法，可以检查它在列表中的数据库使用l即反斜线el命令如下：

postgres-# l

                            List of databases

  Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges  

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

postgres  | postgres | UTF8     | C       | C     |

template0 | postgres | UTF8     | C       | C     | =c/postgres          +

          |          |          |         |       | postgres=CTc/postgres

template1 | postgres | UTF8     | C       | C     | =c/postgres          +

          |          |          |         |       | postgres=CTc/postgres

testdb    | postgres | UTF8     | C       | C     |

(4 rows)

postgres-#

Q4: 如何提高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命令，这样可以保证规划器得到基于该表的最新数据统计。换句话说，如果没有统计数据或者统计数据太过陈旧，那么规划器很可能会选择一个较差的查询规划，从而导致查询效率过于低下。