DDL

约束

数据类型是一种限制能够存储在表中数据类别的方法。但是对于很多应用来说，它们提供的约束太粗糙。例如，一个包含产品价格的列应该只接受正值。但是没有任何一种标准数据类型只接受正值。另一个问题是我们可能需要根据其他列或行来约束一个列中的数据。例如，在一个包含产品信息的表中，对于每个产品编号应该只有一行。

到目前为止，SQL允许我们在列和表上定义约束。约束让我们能够根据我们的愿望来控制表中的数据。如果一个用户试图在一个列中保存违反一个约束的数据，一个错误会被抛出。即便是这个值来自于默认值定义，这个规则也同样适用。

检查约束

一个检查约束是最普通的约束类型。它允许我们指定一个特定列中的值必须要满足一个布尔表达式。例如，为了要求正值的产品价格，我们可以使用：

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0)
);

如你所见，约束定义就和默认值定义一样跟在数据类型之后。默认值和约束之间的顺序没有影响。一个检查约束有关键字CHECK以及其后的包围在圆括号中的表达式组成。检查约束表达式应该涉及到被约束的列，否则该约束也没什么实际意义。

我们也可以给与约束一个独立的名称。这会使得错误消息更为清晰，同时也允许我们在需要更改约束时能引用它。语法为：

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CONSTRAINT positive_price CHECK (price > 0)
);

要指定一个命名的约束，请在约束名称标识符前使用关键词CONSTRAINT，然后把约束定义放在标识符之后（如果没有以这种方式指定一个约束名称，系统将会为我们选择一个）。

一个检查约束也可以引用多个列。例如我们存储一个普通价格和一个打折后的价格，而我们希望保证打折后的价格低于普通价格：

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0),
    discounted_price numeric CHECK (discounted_price > 0),
    CHECK (price > discounted_price)
);

前两个约束看起来很相似。第三个则使用了一种新语法。它并没有依附在一个特定的列，而是作为一个独立的项出现在逗号分隔的列列表中。列定义和这种约束定义可以以混合的顺序出现在列表中。

我们将前两个约束称为列约束，而第三个约束为表约束，因为它独立于任何一个列定义。列约束也可以写成表约束，但反过来不行，因为一个列约束只能引用它所依附的那一个列（Spacture并不强制要求这个规则，但是如果我们希望表定义能够在其他数据库系统中工作，那就应该遵循它）。上述例子也可以写成：

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    CHECK (price > 0),
    discounted_price numeric,
    CHECK (discounted_price > 0),
    CHECK (price > discounted_price)
);

甚至是：

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric CHECK (price > 0),
    discounted_price numeric,
    CHECK (discounted_price > 0 AND price > discounted_price)
);

这只是个人偏好的问题。

表约束也可以用列约束相同的方法来指定名称：

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    CHECK (price > 0),
    discounted_price numeric,
    CHECK (discounted_price > 0),
    CONSTRAINT valid_discount CHECK (price > discounted_price)
);

需要注意的是，一个检查约束在其检查表达式值为真或空值时被满足。因为当任何操作数为空时大部分表达式将计算为空值，所以它们不会阻止被约束列中的控制。为了保证一个列不包含控制，可以使用下一节中的非空约束。

非空约束

一个非空约束仅仅指定一个列中不会有空值。语法例子：

CREATE TABLE products (
    product_no integer NOT NULL,
    name text NOT NULL,
    price numeric
);

一个非空约束总是被写成一个列约束。一个非空约束等价于创建一个检查约束CHECK (column_name IS NOT NULL)，但在Spacture中创建一个显式的非空约束更高效。这种方式创建的非空约束的缺点是我们无法为它给予一个显式的名称。

当然，一个列可以有多于一个的约束，只需要将这些约束一个接一个写出：

CREATE TABLE products (
    product_no integer NOT NULL,
    name text NOT NULL,
    price numeric NOT NULL CHECK (price > 0)
);

约束的顺序没有关系，因为并不需要决定约束被检查的顺序。

NOT NULL约束有一个相反的情况：NULL约束。这并不意味着该列必须为空，进而肯定是无用的。相反，它仅仅选择了列可能为空的默认行为。SQL标准中并不存在NULL约束，因此它不能被用于可移植的应用中（Spacture中加入它是为了和某些其他数据库系统兼容）。但是某些用户喜欢它，因为它使得在一个脚本文件中可以很容易的进行约束切换。例如，初始时我们可以：

CREATE TABLE products (
    product_no integer NULL,
    name text NULL,
    price numeric NULL
);

然后可以在需要的地方插入NOT关键词。

唯一约束

唯一约束保证\在一列中或者一组列中保存的数据在表中所有行间是唯一的。写成一个列约束的语法是：

CREATE TABLE products (
    product_no integer UNIQUE,
    name text,
    price numeric
);

写成一个表约束的语法是：

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric,
    UNIQUE (product_no)
);

要为一组列定义一个唯一约束，把它写作一个表级约束，列名用逗号分隔：

CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    UNIQUE (a, c)
);

这指定这些列的组合值在整个表的范围内是唯一的，但其中任意一列的值并不需要是（一般也不是）唯一的。

我们可以通常的方式为一个唯一索引命名：

CREATE TABLE products (
    product_no integer CONSTRAINT must_be_different UNIQUE,
    name text,
    price numeric
);

增加一个唯一约束会在约束中列出的列或列组上自动创建一个唯一B-tree索引。只覆盖某些行的唯一性限制不能被写为一个唯一约束，但可以通过创建一个唯一的部分索引来强制这种限制。通常，如果表中有超过一行在约束所包括列上的值相同，将会违反唯一约束。但是在这种比较中，两个空值被认为是不同的。这意味着即便存在一个唯一约束，也可以存储多个在至少一个被约束列中包含空值的行。这种行为符合SQL标准，但我们听说一些其他SQL数据库可能不遵循这个规则。所以在开发需要可移植的应用时应注意这一点。

主键

一个主键约束表示可以用作表中行的唯一标识符的一个列或者一组列。这要求那些值都是唯一的并且非空。因此，下面的两个表定义接受相同的数据：

CREATE TABLE products (
    product_no integer UNIQUE NOT NULL,
    name text,
    price numeric
);
 
CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

主键也可以包含多于一个列，其语法和唯一约束相似：

CREATE TABLE example (
    a integer,
    b integer,
    c integer,
    PRIMARY KEY (a, c)
);

增加一个主键将自动在主键中列出的列或列组上创建一个唯一B-tree索引。并且会强制这些列被标记为NOT NULL。

一个表最多只能有一个主键（可以有任意数量的唯一和非空约束，它们可以达到和主键几乎一样的功能，但只能有一个被标识为主键）。关系数据库理论要求每一个表都要有一个主键。但Spacture中并未强制要求这一点，但是最好能够遵循它。

主键对于文档和客户端应用都是有用的。例如，一个允许修改行值的 GUI 应用可能需要知道一个表的主键，以便能唯一地标识行。如果定义了主键，数据库系统也有多种方法来利用主键。例如，主键定义了外键要引用的默认目标列。

外键

一个外键约束指定一列（或一组列）中的值必须匹配出现在另一个表中某些行的值。我们说这维持了两个关联表之间的引用完整性。

例如我们有一个使用过多次的产品表：

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

让我们假设我们还有一个存储这些产品订单的表。我们希望保证订单表中只包含真正存在的产品的订单。因此我们在订单表中定义一个引用产品表的外键约束：

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products (product_no),
    quantity integer
);

现在就不可能创建包含不存在于产品表中的product_no值（非空）的订单。我们说在这种情况下，订单表是引用表而产品表是被引用表。相应地，也有引用和被引用列的说法。

我们也可以把上述命令简写为：

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products,
    quantity integer
);

因为如果缺少列的列表，则被引用表的主键将被用作被引用列。

一个外键也可以约束和引用一组列。照例，它需要被写成表约束的形式。下面是一个例子：

CREATE TABLE t1 (
  a integer PRIMARY KEY,
  b integer,
  c integer,
  FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)
);

当然，被约束列的数量和类型应该匹配被引用列的数量和类型。

按照前面的方式，我们可以为一个外键约束命名。

一个表可以有超过一个的外键约束。这被用于实现表之间的多对多关系。例如我们有关于产品和订单的表，但我们现在希望一个订单能包含多种产品（这在上面的结构中是不允许的）。我们可以使用这种表结构：

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);
 
CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    shipping_address text,
    ...
);
 
CREATE TABLE order_items (
    product_no integer REFERENCES products,
    order_id integer REFERENCES orders,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

注意在最后一个表中主键和外键之间有重叠。

我们知道外键不允许创建与任何产品都不相关的订单。但如果一个产品在一个引用它的订单创建之后被移除会发生什么？SQL允许我们处理这种情况。直观上，我们有几种选项：

不允许删除一个被引用的产品
同时也删除引用产品的订单
其他？为了说明这些，让我们在上面的多对多关系例子中实现下面的策略：当某人希望移除一个仍然被一个订单引用（通过order_items）的产品时，我们阻止它。如果某人移除一个订单，订单项也同时被移除：

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);
 
CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    shipping_address text,
    ...
);
 
CREATE TABLE order_items (
    product_no integer REFERENCES products ON DELETE RESTRICT,
    order_id integer REFERENCES orders ON DELETE CASCADE,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

限制删除或者级联删除是两种最常见的选项。RESTRICT阻止删除一个被引用的行。NO ACTION表示在约束被检察时如果有任何引用行存在，则会抛出一个错误，这是我们没有指定任何东西时的默认行为（这两种选择的本质不同在于NO ACTION允许检查被推迟到事务的最后，而RESTRICT则不会）。

CASCADE指定当一个被引用行被删除后，引用它的行也应该被自动删除。还有其他两种选项：SET NULL和SET DEFAULT。这些将导致在被引用行被删除后，引用行中的引用列被置为空值或它们的默认值。注意这些并不会是我们免于遵守任何约束。例如，如果一个动作指定了SET DEFAULT，但是默认值不满足外键约束，操作将会失败。

与ON DELETE相似，同样有ON UPDATE可以用在一个被引用列被修改（更新）的情况，可选的动作相同。在这种情况下，CASCADE意味着被引用列的更新值应该被复制到引用行中。

正常情况下，如果一个引用行的任意一个引用列都为空，则它不需要满足外键约束。如果在外键定义中加入了MATCH FULL，一个引用行只有在它的所有引用列为空时才不需要满足外键约束（因此空和非空值的混合肯定会导致MATCH FULL约束失败）。如果不希望引用行能够避开外键约束，将引用行声明为NOT NULL。

一个外键所引用的列必须是一个主键或者被唯一约束所限制。这意味着被引用列总是拥有一个索引（位于主键或唯一约束之下的索引），因此在其上进行的一个引用行是否匹配的检查将会很高效。由于从被引用表中DELETE一行或者UPDATE一个被引用列将要求对引用表进行扫描以得到匹配旧值的行，在引用列上建立合适的索引也会大有益处。由于这种做法并不是必须的，而且创建索引也有很多种选择，所以外键约束的定义并不会自动在引用列上创建索引。

排他约束

排他约束保证如果将任何两行的指定列或表达式使用指定操作符进行比较，至少其中一个操作符比较将会返回否或空值。语法是：

CREATE TABLE circles (
    c circle,
    EXCLUDE USING gist (c WITH &&)
);

增加一个排他约束将在约束声明所指定的类型上自动创建索引。

视图

CREATE VIEW myview AS
    SELECT city, temp_lo, temp_hi, prcp, date, location
        FROM weather, cities
        WHERE city = name;
SELECT * FROM myview;

对视图的使用是成就一个好的SQL数据库设计的关键方面。视图允许用户通过始终如一的接口封装表的结构细节，这样可以避免表结构随着应用的进化而改变。

创建视图的sql:

CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW name [ ( column_name [, ...] ) ]
    [ WITH ( view_option_name [= view_option_value] [, ... ] ) ]
    AS query
    [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

CREATE VIEW定义一个查询的视图。该视图不会被物理上物质化。相反，在每一次有查询引用该视图时，视图的查询都会被运行。

CREATE OR REPLACE VIEW与之相似，但是如果已经存在一个同名视图，该视图会被替换。新查询必须产生和现有试图查询相同的列（也就是相同的列序、相同的列名、相同的数据类型），但是它可以在列表的末尾加上额外的列。产生输出列的计算可以完全不同。

如果给定了一个模式名（例如CREATE VIEW myschema.myview…），那么该视图会被创建在指定的模式中。否则，它会被创建在当前模式中。临时视图存在于一个特殊模式中，因此创建临时视图时不能给定一个模式名。视图的名称不能与同一模式中任何其他视图、表、序列、索引或外部表同名。

分区表

分区指的是将逻辑上的一个大表分成一些小的物理上的片。分区有很多益处：

在某些情况下查询性能能够显著提升，特别是当那些访问压力大的行在一个分区或者少数几个分区时。分区可以取代索引的主导列、减小索引尺寸以及使索引中访问压力大的部分更有可能被放在内存中。

当查询或更新访问一个分区的大部分行时，可以通过该分区上的一个顺序扫描来取代分散到整个表上的索引和随机访问，这样可以改善性能。

如果需求计划使用分区设计，可以通过增加或移除分区来完成批量载入和删除。执行ALTER TABLE DETACH PARTITION或者使用DROP TABLE 删除一个单独的分区都远快于一个批量操作。这些命令也完全避免了由批量DELETE造成的VACUUM负载。

很少使用的数据可以被迁移到便宜且较慢的存储介质上。

当一个表非常大时，分区所带来的好处是非常值得的。一个表何种情况下会从分区获益取决于应用，一个经验法则是当表的尺寸超过了数据库服务器物理内存时，分区会为表带来好处。Spacture查询优化器可以利用分区元信息：当查询中含有分区字段相关的查询条件时，Spacture可以自动忽略与查询条件无关的分区，减少对无关数据的访问，从而支持透明的分区忽略功能。

Spacture为以下形式的水平表分区提供了内置支持：

范围分区: 该表被分区到由键列或列集定义的“范围”中，分配给不同分区的值范围之间没有重叠。例如，可以按日期范围进行分区，也可以按特定业务对象的标识符范围进行分区。
列表分区: 表通过明确列出每个分区中出现的键值进行分区。

分区维护

通常当初始定义的表倾向于动态变化时，一组分区会被创建。删除旧的分区并周期性地为新数据增加新分区是很常见的。分区表的一个最重要的优点是可以通过操纵分区结构来使得这种痛苦的任务几乎是自发地完成，而不需要去物理地移除大量的数据。当用户指定下线部分分区时，现有查询可以正常运行，部分分区的缺失不影响其他分区的使用。

移除旧数据的最简单的选项是删除不再需要的分区：

DROP TABLE measurement_y2006m02;

这可以非常快地删除百万级别的记录，因为它不需要逐一地删除记录。不过，请注意，上述命令需要在父表上获取一个ACCESS EXCLUSIVE锁。

另一个经常使用的选项是将分区从被划分的表中移除，但是把它作为一个独立的表保留下来：

ALTER TABLE measurement DETACH PARTITION measurement_y2006m02;

这允许在数据被删除前执行更进一步的操作。例如，这是一个很有用的时机通过COPY、pg_dump或类似的工具来备份数据。这也是进行数据聚集、执行其他数据操作或运行报表的好时机。相似地我们也可以增加新分区来处理新数据。我们可以在被划分的表中创建一个新的空分区：

CREATE TABLE measurement_y2008m02 PARTITION OF measurement
    FOR VALUES FROM ('2008-02-01') TO ('2008-03-01')
    TABLESPACE fasttablespace;

作为一种选择方案，有时创建一个在分区结构之外的新表更方便，并且在以后才将它作为一个合适的分区。这使得数据可以在出现于被划分表中之前被载入、检查和转换：

CREATE TABLE measurement_y2008m02
  (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS)
  TABLESPACE fasttablespace;
 
ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02
   CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' );
 
\copy measurement_y2008m02 from 'measurement_y2008m02'
-- 可能做一些其他数据准备工作
 
ALTER TABLE measurement ATTACH PARTITION measurement_y2008m02
    FOR VALUES FROM ('2008-02-01') TO ('2008-03-01' );

在运行ATTACH PARTITION命令之前，建议在要附加的表上创建一个CHECK约束来描述所需的分区约束。这样，系统将能够跳过扫描来验证隐式分区约束。如果没有这样的约束，将在父表上保存一个ACCESS EXCLUSIVE 锁来扫描该表以验证分区约束。然后可以在ATTACH PARTITION 完成后删除约束，因为它不再是必需的。

DML

插入数据

当一个表被创建后，它不包含数据。在数据库可以有点用之前要做的第一件事就是向里面插入数据。数据在概念上是以每次一行地方式被插入的。你当然可以每次插入多行，但是却没有办法一次插入少于一行的数据。即使你只知道几个列的值，那么你也必须创建一个完整的行。

要创建一个新行，使用INSERT命令。这条命令要求提供表的名字和其中列的值。

CREATE TABLE products (
    product_no integer,
    name text,
    price numeric
);

一个插入一行的命令将是：

INSERT INTO products VALUES (1, 'Cheese', 9.99);

数据的值是按照这些列在表中出现的顺序列出的，并且用逗号分隔。通常，数据的值是文字（常量），但也允许使用标量表达式。

上面的语法的缺点是你必须知道表中列的顺序。要避免这个问题，你也可以显式地列出列。例如，下面的两条命令都有和上文那条命令一样的效果：

INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', 9.99);
INSERT INTO products (name, price, product_no) VALUES ('Cheese', 9.99, 1);

许多用户认为明确列出列的名字是个好习惯。

如果你没有获得所有列的值，那么你可以省略其中的一些。在这种情况下，这些列将被填充为它们的缺省值。例如：

INSERT INTO products (product_no, name) VALUES (1, 'Cheese');
INSERT INTO products VALUES (1, 'Cheese');

第二种形式是Spacture的一个扩展。它从使用给出的值从左开始填充列，有多少个给出的列值就填充多少个列，其他列的将使用缺省值。

为了保持清晰，你也可以显式地要求缺省值，用于单个的列或者用于整个行：

INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', DEFAULT);
INSERT INTO products DEFAULT VALUES;

你可以在一个命令中插入多行：

INSERT INTO products (product_no, name, price) VALUES
    (1, 'Cheese', 9.99),
    (2, 'Bread', 1.99),
    (3, 'Milk', 2.99);

也可以插入查询的结果（可能没有行、一行或多行）：

INSERT INTO products (product_no, name, price)
  SELECT product_no, name, price FROM new_products
    WHERE release_date = 'today';

这提供了用于计算要插入的行的SQL查询机制的全部功能。

更新数据

修改已经存储在数据库中的数据的行为叫做更新。你可以更新单个行，也可以更新表中所有的行，还可以更新其中的一部分行。我们可以独立地更新每个列，而其他的列则不受影响。

要更新现有的行，使用UPDATE命令。这需要提供三部分信息：

表的名字和要更新的列名
列的新值
要更新的是哪（些）行

SQL 通常并不为行提供唯一标识符。因此我们无法总是直接指定需要更新哪一行。但是，我们可以通过指定一个被更新的行必须满足的条件。只有在表里面存在主键的时候（不管你声明它还是不声明它），我们才能可靠地通过选择一个匹配主键的条件来指定一个独立的行。图形化的数据库访问工具就靠这允许我们独立地更新某些行。

例如，这条命令把所有价格为5的产品的价格更新为10：

UPDATE products SET price = 10 WHERE price = 5;

这样做可能导致零行、一行或者更多行被更新。如果我们试图做一个不匹配任何行的更新，那也不算错误。

让我们仔细看看这个命令。首先是关键字UPDATE，然后跟着表名字。和平常一样，表名字也可以是用模式限定的，否则会从路径中查找它。然后是关键字SET，后面跟着列名、一个等号以及新的列值。新的列值可以是任意标量表达式，而不仅仅是常量。例如，如果你想把所有产品的价格提高 10%，你可以用：

UPDATE products SET price = price * 1.10;

如你所见，用于新值的表达式也可以引用行中现有的值。我们还忽略了WHERE子句。如果我们忽略了这个子句，那么就意味着表中的所有行都要被更新。如果出现了WHERE子句，那么只有匹配它后面的条件的行被更新。请注意在SET子句中的等号是一个赋值，而在WHERE子句中的等号是比较，不过这样并不会导致任何歧义。当然WHERE条件不一定非得是等值测试。许多其他操作符也都可以使用。但是表达式必须得出一个布尔结果。

你还可以在一个UPDATE命令中更新更多的列，方法是在SET子句中列出更多赋值。例如：

UPDATE mytable SET a = 5, b = 3, c = 1 WHERE a > 0;

删除数据

到目前为止我们已经解释了如何向表中增加数据以及如何改变数据。剩下的是讨论如何删除不再需要的数据。和前面增加数据一样，你也只能从表中整行整行地删除数据。在前面的一节里我们解释了 SQL 不提供直接访问单个行的方法。因此，删除行只能是通过指定被删除行必须匹配的条件进行。如果你在表上有一个主键，那么你可以指定准确的行。但是你也可以删除匹配条件的一组行，或者你可以一次从表中删除所有的行。

可以使用DELETE命令删除行，它的语法和UPDATE命令非常类似。例如，要从产品表中删除所有价格为 10 的产品，使用：

DELETE FROM products WHERE price = 10;

如果你只是写：

DELETE FROM products;

那么表中所有行都会被删除.

从修改的行中返回数据

有时在修改行的操作过程中获取数据很有用。INSERT、 UPDATE和DELETE命令都有一个支持这个的可选的 RETURNING子句。使用RETURNING 可以避免执行额外的数据库查询来收集数据，并且在否则难以可靠地识别修改的行时尤其有用。

所允许的RETURNING子句的内容与SELECT命令的输出列表相同。它可以包含命令的目标表的列名，或者包含使用这些列的值表达式。一个常见的简写是RETURNING *，它按顺序选择目标表的所有列。

在INSERT中，可用于RETURNING的数据是插入的行。这在琐碎的插入中并不是很有用，因为它只会重复客户端提供的数据。但依赖于计算出的默认值时可以非常方便。例如，当使用 serial列来提供唯一标识符时，RETURNING可以返回分配给新行的ID：

CREATE TABLE users (firstname text, lastname text, id serial primary key);
INSERT INTO users (firstname, lastname) VALUES ('Joe', 'Cool') RETURNING id;

RETURNING子句对于INSERT … SELECT也非常有用。

在UPDATE中，可用于RETURNING的数据是被修改行的新内容。例如：

UPDATE products SET price = price * 1.10
  WHERE price <= 99.99
  RETURNING name, price AS new_price;

在DELETE中，可用于RETURNING的数据是删除行的内容。例如：

DELETE FROM products
  WHERE obsoletion_date = 'today'
  RETURNING *;

如果目标表上有触发器，可用于RETURNING 的数据是被触发器修改的行。因此，检查由触发器计算的列是 RETURNING的另一个常见用例。

索引

基本介绍

假设我们有一个如下的表：

CREATE TABLE test1 (
    id integer,
    content varchar
);

而应用发出很多以下形式的查询：

SELECT content FROM test1 WHERE id = constant;

在没有事前准备的情况下，系统不得不扫描整个test1表，一行一行地去找到所有匹配的项。如果test1中有很多行但是只有一小部分行（可能是0或者1）需要被该查询返回，这显然是一种低效的方式。但是如果系统被指示维护一个在id列上的索引，它就能使用一种更有效的方式来定位匹配行。例如，它可能仅仅需要遍历一棵搜索树的几层而已。

类似的方法也被用于大部分非小说书籍中：经常被读者查找的术语和概念被收集在一个字母序索引中放在书籍的末尾。感兴趣的读者可以相对快地扫描索引并跳到合适的页而不需要阅读整本书来寻找感兴趣的材料。正如作者的任务是准备好读者可能会查找的术语一样，数据库程序员也需要预见哪些索引会有用。

正如前面讨论的，下列命令可以用来在id列上创建一个索引：

CREATE INDEX test1_id_index ON test1 (id);

索引的名字test1_id_index可以自由选择，但我们最好选择一个能让我们想起该索引用途的名字。

为了移除一个索引，可以使用DROP INDEX命令。索引可以随时被创建或删除。

一旦一个索引被创建，就不再需要进一步的干预：系统会在表更新时更新索引，而且会在它觉得使用索引比顺序扫描表效率更高时使用索引。但我们可能需要定期地运行ANALYZE命令来更新统计信息以便查询规划器能做出正确的决定。

索引也会使带有搜索条件的UPDATE和DELETE命令受益。此外索引还可以在连接搜索中使用。因此，一个定义在连接条件列上的索引可以显著地提高连接查询的速度。

在一个大表上创建一个索引会耗费很长的时间。默认情况下，Spacture允许在索引创建时并行地进行读（SELECT命令），但写（INSERT、UPDATE和DELETE）则会被阻塞直到索引创建完成。在生产环境中这通常是不可接受的。在创建索引时允许并行的写是可能的，但是有些警告需要注意，更多信息可以参考并发构建索引。

一个索引被创建后，系统必须保持它与表同步。这增加了数据操作的负担。因此哪些很少或从不在查询中使用的索引应该被移除。

索引类型

Spacture提供了多种索引类型： B-tree（B树）、Hash、GiST、SP-GiST 、GIN 和 BRIN。每一种索引类型使用了一种不同的算法来适应不同类型的查询。默认情况下， CREATE INDEX命令创建适合于大部分情况的B-tree 索引。

B-tree可以在可排序数据上的处理等值和范围查询。特别地，Spacture的查询规划器会在任何一种涉及到以下操作符的已索引列上考虑使用B-tree索引：

<
<=
=
>=
>

将这些操作符组合起来，例如BETWEEN和IN，也可以用B-tree索引搜索实现。同样，在索引列上的IS NULL或IS NOT NULL条件也可以在B-tree索引中使用。

优化器也会将B-tree索引用于涉及到模式匹配操作符LIKE和~ 的查询，前提是如果模式是一个常量且被固定在字符串的开头—例如：col LIKE 'foo%'或者col ~ '^foo', 但在col LIKE '%bar’上则不会。但是，如果我们的数据库没有使用C区域设置，我们需要创建一个具有特殊操作符类的索引来支持模式匹配查询，参见下面的第 11.9 节。同样可以将B-tree索引用于ILIKE和~*，但仅当模式以非字母字符开始，即不受大小写转换影响的字符。

B-tree索引也可以用于检索排序数据。这并不会总是比简单扫描和排序更快，但是总是有用的。

Hash索引只能处理简单等值比较。不论何时当一个索引列涉及到一个使用了=操作符的比较时，查询规划器将考虑使用一个Hash索引。下面的命令将创建一个Hash索引：

CREATE INDEX name ON table USING HASH (column);

GiST索引并不是一种单独的索引，而是可以用于实现很多不同索引策略的基础设施。相应地，可以使用一个GiST索引的特定操作符根据索引策略（操作符类）而变化。作为一个例子，Spacture的标准捐献包中包括了用于多种二维几何数据类型的GiST操作符类，它用来支持使用下列操作符的索引化查询：

<<
&<
&>
>>
<<|
&<|
|&>
|>>
@>
<@
~=
&&

GiST索引也有能力优化“最近邻”搜索，例如：

SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10;

它将找到离给定目标点最近的10个位置。能够支持这种查询的能力同样取决于被使用的特定操作符类。

和GiST相似，SP-GiST索引为支持多种搜索提供了一种基础结构。SP-GiST 允许实现众多不同的非平衡的基于磁盘的数据结构，例如四叉树、k-d树和radix树。作为一个例子，Spacture的标准捐献包中包含了一个用于二维点的SP-GiST操作符类，它用于支持使用下列操作符的索引化查询：

<<
>>
~=
<@
<^
>^

GIN 索引是“倒排索引”，它适合于包含多个组成值的数据值，例如数组。倒排索引中为每一个组成值都包含一个单独的项，它可以高效地处理测试指定组成值是否存在的查询。同时，Spacture通过GIN实现全文索引，支持全文检索的使用场景，加速文本搜索。

与 GiST 和 SP-GiST相似， GIN 可以支持多种不同的用户定义的索引策略，并且可以与一个 GIN 索引配合使用的特定操作符取决于索引策略。作为一个例子，Spacture的标准贡献包中包含了一个用于数组的GIN操作符类，它用于支持使用下列操作符的索引化查询：

<@
@>
=
&&

BRIN 索引（块范围索引的缩写）存储有关存放在一个表的连续物理块范围上的值摘要信息。与 GiST、SP-GiST 和 GIN 相似，BRIN 可以支持很多种不同的索引策略，并且可以与一个 BRIN 索引配合使用的特定操作符取决于索引策略。对于具有线性排序顺序的数据类型，被索引的数据对应于每个块范围的列中值的最小值和最大值，使用这些操作符来支持用到索引的查询：

<
<=
=
>=
>

表达式索引

一个索引列并不一定是底层表的一个列，也可以是从表的一列或多列计算而来的一个函数或者标量表达式。这种特性对于根据计算结果快速获取表中内容是有用的。同时，基于表达式的方式，Spacture对于函数索引也提供了较好的支持。

例如，一种进行大小写不敏感比较的常用方法是使用lower函数：

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

这种查询可以利用一个建立在lower(col1)函数结果之上的索引：

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

如果我们将该索引声明为UNIQUE，它将阻止创建在col1值上只有大小写不同的行。

另外一个例子，如果我们经常进行如下的查询：

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

那么值得创建一个这样的索引：

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

正如第二个例子所示，CREATE INDEX命令的语法通常要求在被索引的表达式周围书写圆括号。而如第一个例子所示，当表达式只是一个函数调用时可以省略掉圆括号。

索引表达式的维护代价较为昂贵，因为在每一个行被插入或更新时都得为它重新计算相应的表达式。然而，索引表达式在进行索引搜索时却不需要重新计算，因为它们的结果已经被存储在索引中了。在上面两个例子中，系统将会发现查询的条件是WHERE indexedcolumn = 'constant'，因此查询的速度将等同于其他简单索引查询。因此，表达式索引对于检索速度远比插入和更新速度重要的情况非常有用。

触发器

基本介绍

一个触发器声明了当执行一种特定类型的操作时数据库应该自动执行一个特殊的函数。触发器可以被附加到表（分区或不分区）、视图和外部表。

在表和外部表上，触发器可以被定义为在 INSERT、UPDATE或 DELETE操作之前或之后被执行，可以为每个SQL语句被执行一次或者为每个修改的行被执行一次。UPDATE 触发器可以进一步地设置为只针对UPDATE 语句的SET子句的特定列出发。触发器也可以被 TRUNCATE语句触发。如果一个触发器事件发生，触发器函数会在适当的事件被调用来处理该事件。

在视图上，触发器可以被定义来取代INSERT、UPDATE或 DELETE操作的执行。INSTEAD OF触发器对视图中需要被修改的每一行触发一次。触发器函数的职责是对底层的基本表执行必要的修改，并且在合适的时候返回被修改的行以便显示在视图中。视图上的触发器也可以被定义为对每个SQL语句执行一次，在INSERT\UPDATE或DELETE操作之前或之后。

触发器函数必须在触发器本身被创建之前被定义好。触发器函数必须被定义成一个没有参数的函数，并且返回类型为trigger（触发器函数通过一个特殊传递的TriggerData结构作为其输入，而不是以普通函数参数的形式）。

一旦一个合适的触发器函数被创建，就可以使用CREATE TRIGGER建立触发器。同一个触发器函数可以被用于多个触发器。

Spacture同时提供每行的触发器和每语句的触发器。对于一个每行的触发器，对于触发触发器的语句所修改的每一行都会调用一次触发器函数。相反，一个每语句的触发器对于其触发语句只被调用一次，而不管该语句影响了多少行。特别地，一个不影响任何行的语句仍然会导致任何可用每语句的触发器的执行。这两类触发器有时也分别被称作行级触发器和语句级触发器。 TRUNCATE上的触发器只能定义在语句级，而不是每行定义。

触发器也可以根据它们是否在操作之前、之后触发，或者被触发来取代操作来分类。它们分别指BEFORE触发器、 AFTER 触发器以及INSTEAD OF触发器。语句级BEFORE 触发器在语句开始做任何事情之前被触发，而语句级AFTER触发器则在语句做完所有事情之后被触发。这些触发器类型可以被定义在表、视图或外部表上。行级BEFORE触发器在每一个行被操作之前被触发，而行级AFTER触发器在语句结束之后被触发（但在任何语句级AFTER触发器之前）。这些触发器类型只能被定义在非分区表和外部表上，不能定义在视图上。INSTEAD OF 触发器只能被定义在视图上，并且只能定义为行级；它们立刻为视图中每一个被标识为需要被操作的行触发。

在继承或分区层次结构中定位父表的语句不会导致受影响的子表的语句级触发器被触发；只有父表的语句级触发器被触发。但是，任何受影响的子表的行级触发器都将被触发。

如果一个INSERT包含ON CONFLICT DO UPDATE子句并且引用了EXCLUDED列，有可能行级 BEFORE INSERT触发器和行级 BEFORE UPDATE触发器的效果可能会以一种对于被更新行最终状态透明的方式被应用。不过，对于要执行的两种集合的行级 BEFORE触发器都不需要有EXCLUDED列引用。当同时有行级 BEFORE INSERT和 BEFORE UPDATE触发器修改被插入/ 更新的行时（即使修改或多或少都具有同等效力，但如果它们也不是幂等的，这可能会有问题），应该考虑可能出现的意料之外的结果。注意在指定了 ON CONFLICT DO UPDATE时，不管有没有行被 UPDATE影响（并且不管是否采用了其他 UPDATE路径），语句级 UPDATE都将被执行。一个带有 ON CONFLICT DO UPDATE子句的INSERT 将首先执行语句级BEFORE INSERT，然后执行语句级BEFORE UPDATE触发器，接着是语句级AFTER UPDATE触发器，最后是语句级AFTER INSERT触发器。

被语句级触发器调用的触发器函数应该总是返回NULL。根据行级触发器的选择，被其调用的触发器函数可以返回一个表行（类型HeapTuple的一个值）给执行器。在一个操作前触发的行级触发器有下列选择：

它可以返回NULL来跳过对当前行的操作。这指示执行器不要执行调用触发器的行级操作（对一个特定表行的插入、修改或删除）。

仅对行级INSERT和UPDATE触发器来说，被返回的行称为将要被插入的行或者替代将被更新的行。这允许触发器函数修改将要被插入或更新的行。

一个无意导致任何这些行为的行级BEFORE触发器必须小心地它的结果，使之和被传入的行一样（即，INSERT和UPDATE触发器的NEW行，DELETE触发器的OLD行）。

一个行级INSTEAD OF触发器可以返回NULL来指示它没有修改任何来自于视图底层基表的数据，也可以返回被传入的视图行（INSERT和UPDATE操作的NEW行，或者DELETE操作的OLD行）。一个非空返回值被用于标志触发器在视图中执行了必须的数据修改。这将会导致被命令修改的行计数被增加。对于INSERT和UPDATE操作，触发器可能会在返回NEW行之前对其进行修改。这将会改变INSERT RETURNING或UPDATE RETURNING返回的数据，并在视图无法正确地显示提供给它的相同数据时有用。

对于在一个操作之后触发的行级触发器，返回值会被忽略，因此它们可以返回NULL。

如果为同一个关系上的同一事件定义了超过一个触发器，它们将按照其名称的字母表顺序被触发。在BEFORE和INSTEAD OF触发器的情况下，每一个触发器返回的可能被修改的行将成为下一个触发器的输入。如果任何一个BEFORE或INSTEAD OF触发器返回NULL，该操作将在该行上被禁用并且对于该行不会触发后续的触发器。

一个触发器定义也能指定一个布尔的WHEN条件，它将被测试来看该触发器是否应该被触发。在行级触发器中，WHEN条件可以检查该行的旧列值和/或新列值（语句级触发器也能有WHEN条件，但是该特性对它们不太有用）。在一个BEFORE触发器中，WHEN条件只是在该函数被或者将被执行前计算，因此使用WHEN条件与在该触发器函数的开始测试相同的条件没有本质区别。不过，在一个AFTER触发器中，WHEN条件只是在行更新发生之后被计算，并且它决定在语句的末尾一个事件是否被排队来触发该触发器。因此当一个AFTER触发器的WHEN不返回真时，在语句的末尾没有必要将一个事件进行排队，也没有必要重新取出该行。如果触发器只对少数行触发，这可以使得修改很多行的语句明显加快。INSTEAD OF触发器不支持WHEN条件。

通常，行级BEFORE被用来检查或修改即将被插入或更新的数据。例如，一个BEFORE触发器可以被用来把当前时间插入到一个timestamp列中，或者检查该行的两个元素之间是否一致。行级AFTER触发器大多数被用来将更新传播到其他表，或者针对其他表进行一致性检查。进行这种工作分工的原因是，一个AFTER触发器可以肯定它看到的是该行的最终值，而一个BEFORE触发器则不能，因为还可能有其他BEFORE触发器在它之后触发。如果你不知道让一个触发器是BEFORE或AFTER，则BEFORE形式更加有效，因为关于该操作的信息直到语句的末尾都不需要被保存。

如果一个触发器函数执行 SQL 命令，则这些命令可能会再次引发触发器。这就是所谓的级联触发器。对于级联的层数没有直接的限制。级联有可能会导致对同一个触发器的递归调用。例如，一个INSERT触发器可能执行一个向同一个表插入一个额外行的命令，这就导致该INSERT触发器被再次引发。所以在这种情形下，触发器程序员应该负责避免无限递归。

在定义一个触发器时，可以为它指定参数。在触发器定义中包括参数的目的是允许具有相似需求的不同触发器调用同一个函数。例如，可能有一个一般性的触发器函数，它需要两个列名作为参数，一个放当前用户而另一个放当前时间戳。在正确编写的情况下，这个触发器函数应该独立于它所触发的表。因此同一个函数可以被用于具有适当列的任意表上的INSERT事件，这样做的用途之一是可以自动追踪一个交易表中记录的创建。如果被定义成一个UPDATE触发器，它也可以被用来追踪最新的更新事件。

每一种支持触发器的编程语言都有自己的方法来让触发器输入数据对触发器函数可用。这种输入数据包括触发器事件的类型（如INSERT或UPDATE）以及被列在CREATE TRIGGER中的任何参数。对于一个行级触发器，输入数据还包括用于INSERT和UPDATE触发器的NEW行，和/或用于UPDATE和DELETE触发器的OLD行。

默认的，语句级触发器没有任何方法检查被语句修改的单个行。但是AFTER STATEMENT触发器可以请求创建转换表以使受影响的行集可用于触发器。AFTER ROW触发器也可以请求转换表，以便他们可以看到表中的全部更改以及它们当前正在触发的单个行中的更改。再次检查转换表的方法取决于正在使用的编程语言，但典型方法是使转换表像只读临时表一样，可以通过触发器函数内发出的SQL命令访问。

数据改变的可见性

如果你在你的触发器函数中执行 SQL 命令，并且这些命令会访问触发器所在的表，那么你需要注意数据可见性规则。因为这些规则决定了这些 SQL 命令是否将能看见引发触发器的数据改变。简单地：

语句级触发器遵循简单的可见性规则：一个语句所作的改变对于语句级 BEFORE触发器都不可见，而所有修改对于语句级 AFTER触发器都是可见的。
导致触发器被引发的数据更改（插入、更新或删除）自然对于在一个行级BEFORE触发器中执行的 SQL 命令不可见，因为它还没有发生。
但是，在一个行级BEFORE触发器中执行的 SQL 命令将会看见之前在同一个外层命令中所作的数据更改的效果。这里需要小心，因为这些更改时间的顺序通常是不可预测的，一个影响多行的 SQL 命令可能以任何顺序访问这些行。
类似地，一个行级INSTEAD OF触发器将会看见之前在同一个外层命令中INSTEAD OF触发器引发所作的数据更改。
当一个行级AFTER触发器被引发时，所有由外层命令所作的数据更改已经完成，并且对于该被调用的触发器函数是可见的。

如果你的触发器函数使用任何一种标准过程语言编写的，那么只有在该函数被声明为VOLATILE时上述陈述才适用。被声明为STABLE或IMMUTABLE的函数在任何情况下将不能看到由调用命令所作出的更改。

扩展性

类型系统

Spacture数据类型被划分为基础类型、组合类型、域和伪类型。

基础类型

基础类型是那些被实现在SQL语言层面之下的类型（通常用一种底层语言，如 C），例如int4。它们通常对应于常说的抽象数据类型。Spacture只能通过由用户提供的函数在这类类型上操作，并且只能理解到用户描述这种类型行为的程度。基础类型会被进一步划分为标量和数组类型。对于每一种标量类型，会自动创建一个相应的数组类型，它能够保存该标量类型的可变尺寸的数组。

组合类型

只要用户创建一个表，就会创建组合类型或者行类型。也可以使用CREATE TYPE来定义一个没有关联表的“stand-alone”组合类型。一个组合类型只是一个具有相关域名称的类型列表。一个组合类型的值是一个行或者域值记录。用户可以访问来自SQL查询的组成域。

域

一个域是基于一种特定基础类型的，并且出于很多目的它可以与其基础类型互换。不过，一个域能够具有约束来限制它的合法值于其底层基础类型允许值的一个子集。

可以使用SQL命令CREATE DOMAIN创建域。

伪类型

有一些用于特殊目的“伪类型”。伪类型不能作为表列或者组合类型的属性出现，但是它们能被用于声明函数的参数和结果类型。这在类型系统中提供了一种机制来标识函数的特殊分类。

多态类型

特别让人感兴趣的五种伪类型是anyelement、anyarray、anynonarray、anyenum以及anyrange，它们被统称为多态类型。任何使用这些类型声明的函数被称作是一个多态函数。通过使用根据一次特定调用实际传递的数据类型所决定的相关数据类型，一个多态函数能够在多种不同数据类型上操作。

多态参数和结果是相互关联的，并且它们在解析调用多态函数的查询时被决定到一种特定的数据类型。每一个被声明为anyelement的位置（参数或返回值）被允许具有任意特定的实际数据类型，但是在任何给定的查询中它们必须全部是相同的实际类型。每一个被声明为anyarray的位置可以有任意数组数据类型，但是相似地，它们必须全部具有相同类型。并且类似地，被声明为anyrange的位置必须是全部是相同的范围类型。此外，如果有位置被声明为anyarray并且其他位置被声明为anyelement，anyarray位置中的实际数组类型必须是一个数组，该数组的元素都是出现在anyelement位置的同一种类型。相似地，如果有位置被声明为anyrange并且其他位置被声明为anyelement，anyrange位置的实际范围类型必须是一个范围，该范围的子类型是出现在anyelement位置的同一种类型。anynonarray被当做和anyelement相同，但是增加了额外的约束要求实际类型不能是一种数组类型。anyenum被当做和anyelement相同，但是增加了额外的约束要求实际类型必须是一种枚举类型。

因此，当使用一种多态类型声明了多于一个参数位置，有效效果是只有实际参数类型的某些组合才被允许。例如，一个被声明为equal(anyelement, anyelement)的函数将要求任意两个输入值，只要它们是同一种数据类型。

当一个函数的返回值被声明为多态类型时，必须至少有一个参数位置也是多态的，并且作为该参数提供的实际数据类型决定了该调用的实际结果类型。例如，如果还没有一种数组下标机制，我们可以定义一个函数来实现下标：subscript(anyarray, integer) returns anyelement。这个声明约束了实际的第一个参数是一种数组类型，并且允许解析器从实际的第一个参数类型推断正确的结果类型。另一个例子是一个被声明为f(anyarray) returns anyenum的函数将只接受枚举类型的数组。

注意anynonarray和anyenum并不表示独立的类型变量，它们是和anyelement相同的类型，只是有一个额外的约束。例如，将一个函数声明为f(anyelement, anyenum)等效于把它声明为f(anyenum, anyenum)：两种实际参数必须是相同的枚举类型。

一个可变函数（可以有可变数量的参数）能够是多态的：这可以通过声明其最后一个参数为VARIADIC anyarray来实现。为了匹配和决定实际结果类型的参数，这样一种函数的行为和写了合适数量的anynonarray参数是一样的。

自定义数据类型

Spacture支持用户自定义类型，用户可通过编写C/SQL等多种方式实现自定义类型。此外，用户可以编写和自定义类型相关的函数，包括普通函数、聚合函数等。

用户定义的函数

Spacture提供四种函数：

查询语言函数（用SQL编写的函数）
过程语言函数（即存储过程。例如用PL/pgSQL或PL/Tcl编写的过程函数） *内部函数
C 语言函数

每一类函数可以采用基本类型、组合类型或者它们的组合作为参数。此外，每一类函数可以返回一个基本类型或一个组合类型。函数也能被定义成返回基本类型或组合类型值的集合。

很多类函数可以接受或者返回特定的伪类型（例如，多态类型），但是可用的功能会变化。Spacture通过自定义函数支持存储过程。

在Spacture里面，通常查询是从对应数据源拉取数据，并放到CN端计算。当需要拉取的数据量比较大时，会严重影响计算效率。计算下推的原理就是把一部分计算放到Worker端进行，Worker计算完再把结果返回给CN，在一些场景下（比如聚合）可以有效减少Worker与CN之间的数据传输，从而提升计算效率。

目前支持SQL标准聚合函数下推(min/max/count/sum/avg)，以及用户插件里自定义函数下推(用户需要提供Worker端和CN端具体执行udf, 以及函数的执行语义).

性能优化

使用Explain

Spacture提供了基于代价的查询优化技术，通过标准SQL的Explain命令，可以查看每个查询的完整执行计划。Spacture为每个收到查询产生一个查询计划。选择正确的计划来匹配查询结构和数据的属性对于好的性能来说绝对是最关键的，因此系统包含了一个复杂的规划器来尝试选择好的计划。你可以使用EXPLAIN命令察看规划器为任何查询生成的查询计划。阅读查询计划是一门艺术，它要求一些经验来掌握，但是本节只试图覆盖一些基础。

你应该能够在自己尝试这些例子时得到相似的结果，但是你的估计代价和行计数可能会小幅变化，因为ANALYZE的统计信息是随机采样而不是精确值，并且代价也与平台有某种程度的相关性。

这些例子使用EXPLAIN的默认“text”输出格式，这种格式紧凑并且便于人类阅读。如果你想把EXPLAIN的输出交给一个程序做进一步分析，你应该使用它的某种机器可读的输出格式（XML、JSON 或 YAML）。

EXPLAIN基础

查询计划的结构是一个计划结点的树。最底层的结点是扫描结点：它们从表中返回未经处理的行。不同的表访问模式有不同的扫描结点类型：顺序扫描、索引扫描、位图索引扫描。也还有不是表的行来源，例如VALUES子句和FROM中返回集合的函数，它们有自己的结点类型。如果查询需要连接、聚集、排序、或者在未经处理的行上的其它操作，那么就会在扫描结点之上有其它额外的结点来执行这些操作。并且，做这些操作通常都有多种方法，因此在这些位置也有可能出现不同的结点类型。 EXPLAIN给计划树中每个结点都输出一行，显示基本的结点类型和计划器为该计划结点的执行所做的开销估计。第一行（最上层的结点）是对该计划的总执行开销的估计；计划器试图最小化的就是这个数字。

这里是一个简单的例子，只是用来显示输出看起来是什么样的：

EXPLAIN SELECT * FROM tenk1;
                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..458.00 rows=10000 width=244)

由于这个查询没有WHERE子句，它必须扫描表中的所有行，因此计划器只能选择使用一个简单的顺序扫描计划。被包含在圆括号中的数字是（从左至右）：

估计的启动开销。在输出阶段可以开始之前消耗的时间，例如在一个排序结点里执行排序的时间。

估计的总开销。这个估计值基于的假设是计划结点会被运行到完成，即所有可用的行都被检索。不过实际上一个结点的父结点可能很快停止读所有可用的行（见下面的LIMIT例子）。

这个计划结点输出行数的估计值。同样，也假定该结点能运行到完成。

预计这个计划结点输出的行平均宽度（以字节计算）。

开销是用规划器的开销参数所决定的捏造单位来衡量的。传统上以取磁盘页面为单位来度量开销；也就是seq_page_cost将被按照习惯设为1.0，其它开销参数将相对于它来设置。本节的例子都假定这些参数使用默认值。

有一点很重要：一个上层结点的开销包括它的所有子结点的开销。还有一点也很重要：这个开销只反映规划器关心的东西。特别是这个开销没有考虑结果行传递给客户端所花费的时间，这个时间可能是实际花费时间中的一个重要因素；但是它被规划器忽略了，因为它无法通过修改计划来改变（我们相信，每个正确的计划都将输出同样的行集）。

行数值有一些小技巧，因为它不是计划结点处理或扫描过的行数，而是该结点发出的行数。这通常比被扫描的行数少一些，因为有些被扫描的行会被应用于此结点上的任意WHERE子句条件过滤掉。理想中顶层的行估计会接近于查询实际返回、更新、删除的行数。

现在让我们修改查询并增加一个WHERE条件：

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 7000;
                         QUERY PLAN
------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..483.00 rows=7001 width=244)
   Filter: (unique1 < 7000)

请注意EXPLAIN输出显示WHERE子句被当做一个“过滤器”条件附加到顺序扫描计划结点。这意味着该计划结点为它扫描的每一行检查该条件，并且只输出通过该条件的行。因为WHERE子句的存在，估计的输出行数降低了。不过，扫描仍将必须访问所有 10000 行，因此开销没有被降低；实际上开销还有所上升（准确来说，上升了 10000 * cpu_operator_cost）以反映检查WHERE条件所花费的额外 CPU 时间。

这条查询实际选择的行数是 7000，但是估计的行数只是个近似值。如果你尝试重复这个试验，那么你很可能得到略有不同的估计。此外，这个估计会在每次ANALYZE命令之后改变，因为ANALYZE生成的统计数据是从该表中随机采样计算的。

现在，让我们把条件变得更严格：

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;
                                  QUERY PLAN
------------------------------------------------------------------------------
 Bitmap Heap Scan on tenk1  (cost=5.07..229.20 rows=101 width=244)
   Recheck Cond: (unique1 < 100)
   ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..5.04 rows=101 width=0)
         Index Cond: (unique1 < 100)

这里，规划器决定使用一个两步的计划：子计划结点访问访问一个索引来找出匹配索引条件的行的位置，然后上层计划结点实际地从表中取出那些行。独立地抓取行比顺序地读取它们的开销高很多，但是不是所有的表页面都被访问，这么做实际上仍然比一次顺序扫描开销要少（使用两层计划的原因是因为上层规划结点把索引标识出来的行位置在读取之前按照物理位置排序，这样可以最小化单独抓取的开销。结点名称里面提到的“位图”是执行该排序的机制）。

函数

Spacture为内建的数据类型提供了大量的函数和操作符。用户也可以定义它们自己的函数和操作符。psql命令\df和\do可以分别被用于显示所有可用的函数和操作符的列表。

数学函数

函数	返回类型	描述	例子	结果
abs(x)	(和输入相同)	绝对值	abs(-17.4)	17.4
cbrt(dp)	dp	立方根	cbrt(27.0)	3
ceil(dp or numeric)	(和输入相同)	不小于参数的最小整数	ceil(-42.8)	-42
ceiling(dp or numeric)	(和输入相同)	不小于参数的最小整数（ceil的别名）	ceiling(-95.3)	-95
degrees(dp)	dp	把弧度转为角度	degrees(0.5)	28.6478897565412
div(y numeric, x numeric)	div(y numeric, x numeric)	y/x的整数商	div(9,4)	2
exp(dp or numeric)	(和输入相同)	指数	exp(1.0)	2.71828182845905
floor(dp or numeric)	(和输入相同)	不大于参数的最大整数	floor(-42.8)	-43
ln(dp or numeric)	(和输入相同)	自然对数	ln(2.0)	0.693147180559945
log(dp or numeric)	(和输入相同)	以10为底的对数	log(100.0)	2
log(b numeric, x numeric)	numeric	以b为底的对数	log(2.0, 64.0)	6.0000000000
mod(y, x)	（和参数类型相同）	y/x的余数	mod(9,4)	1
pi()	dp	“π”常数	pi()	3.14159265358979
power(a dp, b dp)	dp	求a的b次幂	power(9.0, 3.0)	729
power(a numeric, b numeric)	numeric	求a的b次幂	power(9.0, 3.0)	729
radians(dp)	dp	把角度转为弧度	radians(45.0)	0.785398163397448
round(dp or numeric)	(和输入相同)	圆整为最接近的整数	round(42.4)	42
round(v numeric, s int)	numeric	圆整为s位小数数字	round(42.4382, 2)	42.44
scale(numeric)	integer	参数的精度（小数点后的位数）	scale(8.41)	2
sign(dp or numeric)	(和输入相同)	参数的符号（-1, 0, +1）	sign(-8.4)	-1
sqrt(dp or numeric)	(和输入相同)	平方根	sqrt(2.0)	1.4142135623731
trunc(dp or numeric)	(和输入相同)	截断（向零靠近）	trunc(42.8)	42
trunc(v numeric, s int)	numeric	截断为s位小数位置的数字	trunc(42.4382, 2)	42.43
width_bucket(operand dp, b1 dp, b2 dp, count int)	int	返回一个桶，这个桶是在一个有count个桶，上界为b1，下界为b2的柱图中operand将被赋予的那个桶。为外部范围输入返回0或者count+1	width_bucket(5.35, 0.024, 10.06, 5)	3
width_bucket(operand numeric, b1 numeric, b2 numeric, count int)	int	返回一个桶，这个桶是在一个有count个桶，上界为b1，下界为b2的柱图中operand将被赋予的那个桶；为范围外的输入返回0或者count+1	width_bucket(5.35, 0.024, 10.06, 5)	3
width_bucket(operand anyelement, thresholds anyarray)	int	返回一个桶，它是给定数组列出桶的下限operand将被赋予的那个桶，为了输入低于第一下界返回0；thresholds数组必须被存储，首先最小值，或者获取意想不到的结果	width_bucket(now(), array['yesterday', 'today', 'tomorrow']::timestamptz[])	2

函数

返回类型

描述

例子

结果

abs(x)

(和输入相同)

绝对值

abs(-17.4)

17.4

cbrt(dp)

立方根

cbrt(27.0)

ceil(dp or numeric)

(和输入相同)

不小于参数的最小整数

ceil(-42.8)

-42

ceiling(dp or numeric)

(和输入相同)

不小于参数的最小整数（ceil的别名）

ceiling(-95.3)

-95

degrees(dp)

把弧度转为角度

degrees(0.5)

28.6478897565412

div(y numeric, x numeric)

y/x的整数商

div(9,4)

exp(dp or numeric)

(和输入相同)

指数

exp(1.0)

2.71828182845905

floor(dp or numeric)

(和输入相同)

不大于参数的最大整数

floor(-42.8)

-43

ln(dp or numeric)

(和输入相同)

自然对数

ln(2.0)

0.693147180559945

log(dp or numeric)

(和输入相同)

以10为底的对数

log(100.0)

log(b numeric, x numeric)

numeric

以b为底的对数

log(2.0, 64.0)

6.0000000000

mod(y, x)

（和参数类型相同）

y/x的余数

mod(9,4)

pi()

“π”常数

pi()

3.14159265358979

power(a dp, b dp)

求a的b次幂

power(9.0, 3.0)

729

power(a numeric, b numeric)

numeric

求a的b次幂

power(9.0, 3.0)

729

radians(dp)

把角度转为弧度

radians(45.0)

0.785398163397448

round(dp or numeric)

(和输入相同)

圆整为最接近的整数

round(42.4)

round(v numeric, s int)

numeric

圆整为s位小数数字

round(42.4382, 2)

42.44

scale(numeric)

integer

参数的精度（小数点后的位数）

scale(8.41)

sign(dp or numeric)

(和输入相同)

参数的符号（-1, 0, +1）

sign(-8.4)

-1

sqrt(dp or numeric)

(和输入相同)

平方根

sqrt(2.0)

1.4142135623731

trunc(dp or numeric)

(和输入相同)

截断（向零靠近）

trunc(42.8)

trunc(v numeric, s int)

numeric

截断为s位小数位置的数字

trunc(42.4382, 2)

42.43

width_bucket(operand dp, b1 dp, b2 dp, count int)

int

返回一个桶，这个桶是在一个有count个桶，上界为b1，下界为b2的柱图中operand将被赋予的那个桶。为外部范围输入返回0或者count+1

width_bucket(5.35, 0.024, 10.06, 5)

width_bucket(operand numeric, b1 numeric, b2 numeric, count int)

int

返回一个桶，这个桶是在一个有count个桶，上界为b1，下界为b2的柱图中operand将被赋予的那个桶；为范围外的输入返回0或者count+1

width_bucket(5.35, 0.024, 10.06, 5)

width_bucket(operand anyelement, thresholds anyarray)

int

返回一个桶，它是给定数组列出桶的下限operand将被赋予的那个桶，为了输入低于第一下界返回0；thresholds数组必须被存储，首先最小值，或者获取意想不到的结果

width_bucket(now(), array['yesterday', 'today', 'tomorrow']::timestamptz[])