说明

该 postgres_fdw 模块提供了 远程-数据 包装器 postgres_fdw，它能够用于访问一些保存在外部 PostgreSQL 服务器的数据。

该模块提供的功能很大程度上与以前的dblink模块重叠。但是postgres_fdw 提供了更加透明和符合标准的语法来访问远程表，并且能够在某些情况下有更好的性能。

安装extension

cd contrib/postgres_fdw
make install

安装到数据库

1
2
3

test=# CREATE EXTENSION postgres_fdw;
CREATE EXTENSION
test=#

检查是否安装成功

1
2
3
4
5
6
7
8

test=# \dx postgres_fdw;
                             List of installed extensions
     Name     | Version | Schema |                    Description                    
--------------+---------+--------+----------------------------------------------------
 postgres_fdw | 1.0     | public | foreign-data wrapper for remote PostgreSQL servers
(1 row)
 
test=#

创建并查看一个远程服务器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

test=# \dx postgres_fdw;
                             List of installed extensions
     Name     | Version | Schema |                    Description                    
--------------+---------+--------+----------------------------------------------------
 postgres_fdw | 1.0     | public | foreign-data wrapper for remote PostgreSQL servers
(1 row)
 
test=# CREATE SERVER postgres_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '10.0.0.10', port '5432', dbname 'test');
CREATE SERVER
test=# \des;
              List of foreign servers
      Name       |  Owner   | Foreign-data wrapper
-----------------+----------+----------------------
postgres_server | postgres | postgres_fdw
(1 row)
 
test=#

创建一个远程映射用户

1

CREATE USER MAPPING FOR PUBLIC SERVER postgres_server OPTIONS (user 'postgres', password 'yang');

user,password 是远程数据库上的用户名和密码

创建一个远程映射表

1
2
3

test=# CREATE FOREIGN TABLE tcost_foreign (path integer, cost numeric) SERVER postgres_server OPTIONS (schema_name 'public', table_name 'tcost');
CREATE FOREIGN TABLE
test=#

操作

像它在本地表一样操作就可以了。

资料：
[1] 官网

转载自：http://www.ituring.com.cn/article/986
作者：李松峰

发表于 2012-02-04 11:43

译者按：今天在翻译时无意中搜索到StackOverflow中的这篇文章（问答），觉得有必要翻译出来。不仅因为文章本身写得精彩，更重要的是它昭示了一个写文章（特别是技术文章）的重要法则——5W1H。 原文在此 How does database indexing work?（作者：Xenph Yan）

问：随着数据库的增大，既然索引的作用那么重要，有谁能抛开具体的数据库来解释一下索引的工作原理？

答：（我自己来回答这个问题，:o-)）

为什么需要索引

数据在磁盘上是以块的形式存储的。为确保对磁盘操作的原子性，访问数据的时候会一并访问所有数据块。磁盘上的这些数据块与链表类似，即它们都包含一个数据段和一个指针，指针指向下一个节点（数据块）的内存地址，而且它们都不需要连续存储（即逻辑上相邻的数据块在物理上可以相隔很远）。

鉴于很多记录只能做到按一个字段排序，所以要查询某个未经排序的字段，就需要使用线性查找，即要访问N/2个数据块，其中N指的是一个表所涵盖的所有数据块。如果该字段是非键字段（也就是说，不包含唯一值），那么就要搜索整个表空间，即要访问全部N个数据块。

然而，对于经过排序的字段，可以使用二分查找，因此只要访问log2 N个数据块。同样，对于已经排过序的非键字段，只要找到更大的值，也就不用再搜索表中的其他数据块了。这样一来，性能就会有实质性的提升。

什么是索引

索引是对记录按照多个字段进行排序的一种方式。对表中的某个字段建立索引会创建另一种数据结构，其中保存着字段的值，每个值又指向与它相关的记录。这种索引的数据结构是经过排序的，因而可以对其执行二分查找。

索引的缺点是占用额外的磁盘空间。因为索引保存在MyISAM数据库中，所以如果为同一个表中的很多字段都建立索引，那这个文件可能会很快膨胀到文件系统规定的上限。

索引的原理

首先，来看一个示例数据库表的模式：

1
2
3
4
5

字段名              数据类型         在磁盘上的大小
id (Primary key)   Unsigned INT     4 字节
firstName          Char(50)         50 字节
lastName           Char(50)         50 字节
emailAddress       Char(100)        100 字节

注意：这里用char而不用varchar是为了精确地描述数据占用磁盘的大小。这个示例数据库中包含500万行记录，而且没有建立索引。接下来我们就分析针对这个表的两个查询：一个查询使用id（经过排序的键字段），另一个查询使用firstName（未经排序的非键字段）。

示例分析一

对于这个拥有r = 5 000 000条记录的示例数据库，在磁盘上要为每条记录分配 R = 204字节的固定存储空间。这个表保存在MyISAM数据库中，而这个数据库默认的数据库块大小为 B = 1024字节。于是，我们可计算出这个表的分块因数为 bfr = (B/R) = 1024/204 = 5，即磁盘上每个数据块保存5条记录。那么，保存整个表所需的数据块数就是 N = (r/bfr) = 5000000/5 = 1 000 000。

使用线性查找搜索id字段——这个字段是键字段（每个字段的值唯一），需要访问 N/2 ＝ 500 000个数据块才能找到目标值。不过，因为这个字段是经过排序的，所以可以使用二分查找法，而这样平均只需要访问log2 1000000 = 19.93 = 20 个块。显然，这会给性能带来极大的提升。

再来看看firstName字段，这个字段是未经排序的，因此不可能使用二分查找，况且这个字段的值也不是唯一的，所以要从表的开头查找末尾，即要访问 N = 1 000 000个数据块。这种情况通过建立索引就能得到改善。

如果一条索引记录只包含索引字段和一个指向原始记录的指针，那么这条记录肯定要比它所指向的包含更多字段的记录更小。也就是说，索引本身占用的磁盘空间比原来的表更少，因此需要遍历的数据块数也比搜索原来的表更少。以下是firstName字段索引的模式：

1
2
3

字段名         数据类型        在磁盘上的大小
firstName     Char(50)        50 字节
（记录指针）    Special         4 字节

注意：在MySQL中，根据表的大小，指针的大小可能是2、3、4或5字节。

示例分析二

对于这个拥有r = 5 000 000条记录的示例数据库，每条索引记录要占用 R = 54字节磁盘空间，而且同样使用默认的数据块大小 B = 1024字节。那么索引的分块因数就是 bfr = (B/R) = 1024/54 = 18。最终这个表的索引需要占用 N = (r/bfr) = 5000000/18 = 277 778个数据块。

现在，再搜索firstName字段就可以使用索引来提高性能了。对索引使用二分查找，需要访问 log2 277778 = 18.09 = 19个数据块。再加上为找到实际记录的地址还要访问一个数据块，总共要访问 19 + 1 = 20个数据块，这与搜索未索引的表需要访问277 778个数据块相比，不啻于天壤之别。

什么时候用索引

创建索引要额外占用磁盘空间（比如，上面例子中要额外占用277 778个数据块），建立的索引太多可能导致磁盘空间不足。因此，在建立索引时，一定要慎重选择正确的字段。

由于索引只能提高搜索记录中某个匹配字段的速度，因此在执行插入和删除操作的情况下，仅为输出结果而为字段建立索引，就纯粹是浪费磁盘空间和处理时间了；这种情况下不用建立索引。另外，由于二分查找的原因，数据的基数性（cardinality）或唯一性也非常重要。对基数性为2的字段建立索引，会将数据一分为二，而对基数性为1000的字段，则同样会返回大约1000条记录。在这么低的基数性下，索引的效率将减低至线性查找的水平，而查询优化器会在基数性小于记录数的30%时放弃索引，实际上等于索引纯粹只会浪费空间。

查询优化器的原理

查询优化中最核心的问题就是精确估算不同查询计划的成本。优化器在估算查询计划的成本时，会使用一个数学模型，该模型又依赖于对每个查询计划中涉及的最大数据量的基数性（或者叫重数）的估算。而对基数性的估算又依赖于对查询中谓词选择因数（selection factor of predicates）的估算。过去，数据库系统在估算选择性时，要使用每个字段中值的分布情况的详尽统计信息，比如直方图。这种技术对于估算孤立谓词的选择符效果很好。然而，很多查询的谓词是相互关联的，例如 select count(*) from R where R.make='Honda' and R.model='Accord'。查询谓词经常会高度关联（比如，model='Accord'的前提条件是make='Honda'），而估计这种关联的选择性非常困难。查询优化器之所以会选择低劣的查询计划，一方面是因为对基数性估算不准，另一方面就是因为遗漏了很多关联性。而这也是为什么数据库管理员应该经常更新数据库统计信息（特别是在重要的数据加载和卸载之后）的原因。（译自维基百科：http://en.wikipedia.org/wiki/Query_optimizer。）

原文

本文经过原作者Dan Robinson同意翻译。（我在twitter发送推文和他互动）

你知道PostgreSQL支持表的部分索引吗？这使得读取部分索引是非常快速的，几乎没有什么索引开销。如果你想重复地分析那些匹配给定的 WHERE 子句的行数据，那部分索引是最好的索引数据的方式。这使得PostgreSQL非常适合于那种涉及预聚集额外的特别分析的工作流。在这一点上，我将通过一个非常适合使用部分索引的查询优化示例来实践下。

思考一下，具有以下模式的事件表：

1
2
3
4
5
6
7

CREATE TABLE event (
  user_id BIGINT,
  event_id BIGINT,
  time BIGINT NOT NULL,
  data JSON NOT NULL,
  PRIMARY KEY (user_id, event_id)
)

每一个事件都与用户关联，并且有一个ID，一个time，以及一个 JSON 表示事件。
JSON包括页面路径，事件类型（例如：点击，查看页面，提交表单），以及其他任何描述事件的属性。

我们可以使用这张表来保存许多不同的事件，为了我们以后可以分析数据，让我们假设有许多自动跟踪并记录每次点击，查看页面以及表单提交的事件。我们可能想要一个内部的表盘可以显示一些高值指标，例如每周的注册数或者我们的每天收益数。与表盘相关的事件只是这个表的一个小的组成部分——在你的网站上最终购买的点击数占非常小的百分比。但它们被混合在表的其余部分，所以我们的“信噪比”比较低。

我们可能喜欢索引我们的数据来加快表盘的查询[1]。让我们从注册事件开始，我们定义一个表单提交到我们的 /signup 页面。获取9月份第一周的注册数可以这样子写：

1
2
3
4
5
6

SELECT COUNT(*)
FROM event
WHERE
  (data->>'type') = 'submit' AND
  (data->>'path') = '/signup/' AND
  time BETWEEN 1409554800000 AND 1410159600000

在一个1000万个事件数据集里，有3000个是注册事件，并且没有任何索引，这条查询耗时45秒。

在每个单列里建立索引：混合

一个天真地认为提高这个性能的方法是：为每个相关事件的特性：(data->>'type'), (data->>'path'), and time 都创建一个单列索引。

我们使用这三个索引的位图索引扫描得出结果，如果该查询是有选择性的并且相关的索引部分都在内存的这可能会比较快速。的确，这些索引在适当的位置时，该查询在初始化时耗时200ms，随后再耗时20ms在执行合并数据集上——这显著改善了耗时45秒的顺序扫描。

但是这种索引策略有一些非常大的缺点:

• 写开销. 我们需要在每个 插入/更新/删除 操作该表数据时都要写这三个索引的数据[2]。 对于在该例子里需要频繁写数据来说，这可能代价太高了。

• 限制查询结果集。这种策略约束了我们定义高值事件类型的能力。如果我们需要一些比在JSON里其中之一的字段的范围更复杂的查询就无法工作了。 假如我们想要匹配一个正则，或者查看所有以/signup/开头，后面可以接任何字符的页面路径？

• 磁盘使用。在我们测试的数据集合里，该表的大小高达 6660 MB，并且这三个索引一总占用1026MB，为了支持该表，我们需要大幅增加硬盘空间。[3]

进入部分索引

我们只是仅仅分析 0.03% 的组成注册事件的表数据，但是上面这种索引策略索引了所有行。我们希望能够高效地执行查询表的一小部分数据。类似像这种情况，最好的结果是使用部分索引。

如果我们索引一个不相关的列并且限制我们的索引是匹配注册定义事件的，PostgreSQL可以非常容易地确定注册事件的数据行在哪里，并且比在相关字段里建立完整的索引更高效地查询这些数据。特别注意，考虑索引time字段，但仅仅是匹配那些过滤好的注册事件的行。这是：

1
2

CREATE INDEX event_signups ON event (time)
WHERE (data->>'type') = 'submit' AND (data->>'path') = '/signup/'

使用这个索引，我们的执行测试查询初始化耗时200ms，并且紧接耗费2ms来执行，因此，如果我们经常执行这种查询，这会提高性能。更重要的是，部分索引解决了上面提到的三个组合的索引的三个缺点。

• 它仅仅占用 96KB 空间， 这比完整索引所有这三个字段占用1026MB空间提升了10000 倍。

• 当新行匹配我们过滤好的注册事件时，我们才需要写部分索引。由于这里有 0.03% 的注册事件，这种数据分布显著提高了写性能：实际上这是由于部分索引而免费得到的。

• PostgreSQL只是允许那些完全表达式过滤的部分加入。我们定义索引的 WHERE 子句可以使用我们可能在一条查询里使用的任何行过滤表达式。我们使用这方式来匹配更多复杂事件的定义，比如正则表达式，函数结果，或者上面提到的前缀匹配的例子。

避免索引那些谓词结果为布尔值的数据

我见过的另一种方式是试图索引布尔表达式：

1

(data->>'type') = 'submit' AND (data->>'path') = '/signup/'

直接地，将time放在第二个字段，像这样：

1
2

CREATE INDEX event_signup_time ON event
(((data->>'type') = 'submit' AND (data->>'path') = '/signup/'), time)

这比以上两种方式更加糟糕，它产生的结果是PostgreSQL的查询计划不会理解我们的查询例子来约束那些在索引里第一个字段为true的行。这是因为，查询计划不知道 WHERE 子句：

1

WHERE (data->>'type') = 'submit' AND (data->>'path') = '/signup/'

是与下面我们索引的这个字段为必须为true的是相等的。

1

((data->>'type') = 'submit' AND (data->>'path') = '/signup/')

因此， 在我们的扫描里，使用该索引来限制事件的时间范围，但是它无论这个正则表达式是true还是false都会读取所有事件(译者注：这里指的是通过时间范围查出来的所有事件)，加载完毕后才对每一行进行检查条件。[4]

因此，我们就会从磁盘里读取比实际需要的更加多的行，并且还要对每一个结果行执行重要的检查条件。在我们的数据集里，该查询开始执行时耗时25秒，之后再连续执行8秒。事实的真相是，这比仅索引time字段差一点，在执行上是等同的。

部分索引对于预先计算那些通过谓词来匹配表的子集来说是一个非常强大的方式。通过流量判断 #postgresql IRC 并没有被充分利用。相比完全索引，它们允许一个更好的谓词范围。它们明显地更加轻量级，需要更少地写操作以及更少的磁盘空间，特别是那些高选择性的过滤。如果你重复地查询一个表的一小部分行，那么部分索引应该是你的默认策略。

爱上PostgreSQL了吗？还有人可能知道更多关于部分索引的知识？发推给我 @danlovesproofs

对构建使强大的技术变得易用的系统感兴趣？向我们投递留言到 jobs@heapanalytics.com.

• [1] 我们可能使用分表来解决这种情况，将高值事件和低值事件分到不同的子表，但是如果有许多不同的高值事件时这种方式就比较笨拙了，并且我们想要添加一个新类型的高值事件时，每次都需要重新分表。

• [2] 我们可能得到一些通过优化只在堆元组上的免更新，但是，至少在每次的插入和删除时将需要写这三个索引。

• [3] 我们可以索引这三个字段为一个组合索引，例如在：((data->>’type’), (data->>’path’), time)。这占用 755MB 空间，节约了 26% ，但这仍然是非常大的，其他的缺点也一样有。更重要的是，在这些数据上，索引会更少地应用到其他的查询，因此，如果我们正支持一些不同的同值事件，这可能不会节约我们的任何空间。

• [4] 相应的查询计划：

1
2
3
4
5
6
7

                                                  QUERY PLAN                                                  
---------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=820866.05..820866.06 rows=1 width=0)
   ->  Index Scan using event_signup_time on event  (cost=0.43..820865.99 rows=27 width=0)
         Index Cond: (("time" >= 1409554800000::bigint) AND ("time" <= 1410159600000::bigint))
         Filter: (((data ->> 'type'::text) = 'submit'::text) AND ((data ->> 'path'::text) = '/signup/'::text))
(4 rows)

关于排序

表是一个集合的概念，所以除非你显式地指定 order by 来排序，否则数据库是不保证每次返回的结果顺序是一样的。只是保证在没有修改的情况下，返回的数量是相同的。

关于大小写

虽然SQL是不区分大小写的，但对于列名和表名和值可能在不同的数据库里有不同的对待（表名，列名值等可能是大小写敏感的）。

关于 select *

除非你确实需要表中的每一列，否则最好别使用*通配符。虽然使用通配符能让你自己省事，不用明确列出所需列，但检索不需要的列通常会降低检索和应用程序的性能。

关于 distinct

DISTINCT 关键字作用于所有列，不仅仅是跟在其后的那一列。

关于按列排序

通常 order by 子句使用的列将是为显示而选择的列，但实际上并不一定要这样，用非检索的列排序数据是完全合法的。

关于按列排序的顺序

order by 排序的顺序完全按规定进行。即按跟在 order by 后面的指定列顺序来排序，先排第一个列的数据，然后再排序第二个出现的列，第三个等等。

关于排序的位置

order by 列的位置号[, 列的位置号2，列的位置号3 …]。这个列的位置是指select 后跟着的列的位号，数字从1开始，即第一个列的序号为1，以此类推。

注意，这种order by 排序，必须是出现在select 后指定的列的，而不能够是非select出来的列。

升序，降序

ASC，DESC，这些关键字，只应用到直接按位于其前面的列名。默认情况下都是升序。

关于 between and

它的范围是包括开始值，也包括结束值。

关于选择 NULL 值的数据

不能用 where col = NULL，而要用 where col IS NULL

关于 and 和 or 的顺序

and 的优先级高于 or，所以 col1 = 10 or col2 = 11 and col3 = 13，会被RDBMS理解成：
col1 = 10 or （col2 = 11 and col3 = 13）。

自己可以显式加圆括号来按自己指定的逻辑进行。如

（col1 = 10 or col2 = 11） and col3 = 13，这样子括号里的就会优先进行。

所以，复杂点的SQL都建议显式地指定圆括号来指示逻辑顺序。

关于 IN 和 OR，IN的优点

1）IN操作符一般比一组OR操作符执行得更快。
2）IN的最大优点是可以包含其他的SELECT语句，更能动态建立WHERE子句
3）IN比OR的执行顺序更明确（与其他AND等组合时）

关于AVG()函数

AVG()函数只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出。为了获得多个列的平均值，必须使用多个AVG()函数。
而且 AVG() 函数忽略列值为NULL的行。

关于COUNT()函数

使用COUNT(*)对表中行的数目进行计数，不管表列是否包含NULL都会统计。

使用COUNT(col)对表中特定值的行进行计数，但会忽略NULL值 。

关于MAX()，MIN()，SUM()

MAX()函数和MIN()函数和SUM()函数，都会会忽略列值为NULL的行。

关于 COUNT 和 DISTINCT 混合

如果指定列名，则DISTINCT只能用于COUNT(DISTINCT COL)。
但是DISTINCT不能用于COUNT(*)。而且DISTINCT后只能跟列名，不能用于计算或表达式。

关于GROUP BY

1）GROUP BY 子句可以包含任意数目的列，因而可以对分组进行嵌套，更细致地进行数据分组。

2）如果在GROUP BY子句中嵌套了分组，数据将在最后指定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）

3）GROUP BY子句中列出的每一列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。

4）大多SQL实现不允许GROUP BY列带有长度可变的数据类型（如文本或备注型字段）

5）除聚集计算语句外，SELECT语句中的每一列都必须在GROUP BY子句中给出

6）如果分组中包含具有NULL值的行，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。

7）GROUP BY子句必须出现在WHERE子句之后，ORDER BY 子句之前。

关于 WHERE 和 HAVING 的区别

WHERE是分组之前过滤的，HAVING是分组之后过滤的。

关于SELECT语法顺序

1
2
3
4
5
6

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

关于子查询

作为子查询的SELECT语句只能查询单个列。企图检索多个列将返回错误。

关于SELECT子查询

1

select (select xxx from xx) , col from xx2

这种子查询对检索出的每一行都会执行一次。

关于连接表

• 笛卡儿积：如果两个连接的表，没有连接条件的话，检索出的行数，将是第一个表中的行数 x 第二个表中的行数。

• 公共点：所有的连接，第一步都是做笛卡儿积。

交叉连接（cross join）

笛卡儿积的联结，也叫交叉连接(cross join)

等值连接（也叫内连接）两种方式

1
2
3

select a.col, b.col from a, b where a.pid = b.pid;
select a.col, b.col from a inner join b on a.pid = b.pid

但是根据ANSI SQL规范，首选的是第二种。

内连接（基于连接谓词将两张表(如 A 和 B)的列组合在一起，产生新的结果表。）

• 等值连接

相等连接 (equi-join，或 equijoin)，是比较连接(θ连接)的一种特例，它的连接谓词只用了相等比较。使用其他比较操作符(如 <)的不是相等连接

• 自然连接

自然连接比相等连接的进一步特例化。两表做自然连接时，两表中的所有名称相同的列都将被比较，这是隐式的。自然连接得到的结果表中，两表中名称相同的列只出现一次.

• 叉连接

交叉连接(cross join)，又称笛卡尔连接(cartesian join)或叉乘(Product)，它是所有类型的内连接的基础。把表视为行记录的集合，交叉连接即返回这两个集合的笛卡尔积。这其实等价于内连接的链接条件为”永真”，或连接条件不存在.

如果 A 和 B 是两个集合，它们的交叉连接就记为: A × B.

外连接

外连接并不要求连接的两表的每一条记录在对方表中都一条匹配的记录. 连接表保留所有记录 — 甚至这条记录没有匹配的记录也要保留. 外连接可依据连接表保留左表, 右表或全部表的行而进一步分为左外连接, 右外连接和全连接.

• 左外连接

若 A 和 B 两表进行左外连接, 那么结果表中将包含”左表”(即表 A)的所有记录, 即使那些记录在”右表” B 没有符合连接条件的匹配. 这意味着即使 ON 语句在 B 中的匹配项是0条, 连接操作还是会返回一条记录, 只不过这条记录的中来自于 B 的每一列的值都为 NULL. 这意味着左外连接会返回左表的所有记录和右表中匹配记录的组合(如果右表中无匹配记录, 来自于右表的所有列的值设为 NULL). 如果左表的一行在右表中存在多个匹配行, 那么左表的行会复制和右表匹配行一样的数量, 并进行组合生成连接结果.

• 右外连接

右外连接, 亦简称右连接, 它与左外连接完全类似, 只不过是作连接的表的顺序相反而已. 如果 A 表右连接 B 表, 那么”右表” B 中的每一行在连接表中至少会出现一次. 如果 B 表的记录在”左表” A 中未找到匹配行, 连接表中来源于 A 的列的值设为 NULL.

其实可以通过变换表位置来使用左外连接。

• 全连接

全连接是左右外连接的并集. 连接表包含被连接的表的所有记录, 如果缺少匹配的记录, 即以 NULL 填充.

自连接

自连接就是和自身连接.

优先使用自连接，而不是子查询

一般来说，DBMS处理连接远比处理子查询快得多。但应该都试一下，以确定哪一种的性能更好。

连接算法

• 嵌套循环
• 合并连接
• 哈希连接

集合查询：并（union）

在各个select语句之间加上关键字union就可以了。使用union时，默认情况下会自动去除重复的行。可以添加上 union all 来返回所有行，包括重复的。

限制

1）union中的每个查询必须包含相同的列、表达式或聚集函数（顺序并不要求）

2）列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含转换的类型

union中使用order by

在union时，只能使用一条order by 子句，它必须位于最后一条select语句之后。因为，对于结果集来说，不存在用一种方式排序一部分，而又用另一种方式排序另一部分的情况，因此，不允许使用多条order by 。

关于insert没给出列名的情况

如果没有显式给出列名，那么values后各列是对应它在表定义中出现的顺序的。

insert select 语句

1

insert into t (col1, col2) select col11, col2 from t2;

注意，是没有values的哦。

select into语句

SQLite：

1

select * into totable from fromtable;

PostgreSQL，MySQL，Oracle等的方式：

1

create table totable as select * from fromtable;

即，将fromtable表所有数据，复制到totable中。

关于视图

• 视图是不能索引的

• 视图是可以嵌套的（性能低）

• 视图包含的不是数据，而是根据需要检索数据的查询。视图提供了一种封装select语句的层次，可以用来简化数据处理，重新格式化或保护基础数据。

关于存储过程

• 简单（通过封装，将复杂逻辑放在一个单元中）

• 安全（简化表名，列名或业务逻辑等其他内容的变化时的管理）

• 高性能（存储过程是以编译后的形式存储的，所以DBMS处理命令的工作较少，从而提高性能）

关于游标

它是一个存储在DBMS服务器上的数据库查询，它不是一条SELECT语句，而是被该语句检索出来的结果集。

关于主键

• 任意两行的主键值都不相同

• 每行都具有一个主键值（即不允许NULL值）

• 包含主键值的列从不修改或更新（允许DBMS允许，也不要这样子做）

• 主键仁政不能重用。如果从表中删除某一行，其主键值不分配给新行。

唯一约束

• 表可以包含多个唯一约束，但只允许一个主键

• 可以包含NULL值

• 可以修改或更新

• 唯一约束，不能用来定义外键

关于索引

• 索引改善检索的性能，但降低了数据插入、修改和删除的性能。

• 索引数据可能要占用大量存储空间

• 并非所有数据都适合做索引。取值不多的数据，不如具有更多可能值的数据，能通过索引得到那么多的好处。

• 索引用于数据过滤和数据排序。

• 可以在索引中定义多列。这样子使用索引的要求时，必须按定义出现的索的顺序为前缀。

示例表数据如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

test=# select * from tcost ;
 path | cost 
------+-------
  111 |  23.3
  111 |  33.4
  111 |   3.4
  222 |   3.4
  222 |  33.4
  222 | 333.4
   32 |   3.4
   32 |   0.4
   32 |  0.04
(9 rows)
 
test=#

ROWS

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

test=# select path, cost, sum(cost) over (order by cost desc) as sum_cost, sum(cost) over (order by cost desc rows between current row and 2 following ) as row from tcost;
 path | cost  | sum_cost |  row 
------+-------+----------+-------
  222 | 333.4 |    333.4 | 400.2
  111 |  33.4 |    400.2 |  90.1
  222 |  33.4 |    400.2 |  60.1
  111 |  23.3 |    423.5 |  30.1
  111 |   3.4 |    433.7 |  10.2
   32 |   3.4 |    433.7 |   7.2
  222 |   3.4 |    433.7 |  3.84
   32 |   0.4 |    434.1 |  0.44
   32 |  0.04 |   434.14 |  0.04
(9 rows)
 
test=#

可以看到 max2 的值都是由 current row （当前行） and 2 following(紧接着2行）的sum()结果出来的。
即
400.2 = 333.4 + 33.4 + 33.4 得出的。
90.1 = 33.4 + 33.4 + 23.3 得出的。

注意，上面那条SQL没有写 partition by ，那默认情况下就是以整个表来表示窗口化的，即只有一个窗口。现在试着，添加上partition by 语句的结果看看：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

test=# select path, cost, sum(cost) over (order by cost desc) as sum_cost, sum(cost) over (partition by path order by cost desc rows between current row and 2 following ) as row from tcost;
 path | cost  | sum_cost |  row 
------+-------+----------+-------
   32 |   3.4 |    433.7 |  3.84
   32 |   0.4 |    434.1 |  0.44
   32 |  0.04 |   434.14 |  0.04
  111 |  33.4 |    400.2 |  60.1
  111 |  23.3 |    423.5 |  26.7
  111 |   3.4 |    433.7 |   3.4
  222 | 333.4 |    333.4 | 370.2
  222 |  33.4 |    400.2 |  36.8
  222 |   3.4 |    433.7 |   3.4
(9 rows)
 
test=#

当 partition by path时，可以看到，这些ROWS 模式，都是在当前所在的窗口来进行的，并不会跨窗口来进行。
所以，在这里强调一下，ROWS表示的是物理行。

RANGE

看看，当是RANGE时的结果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

test=# select path, cost, sum(cost) over (order by cost desc) as sum_cost, sum(cost) over (order by cost desc range  between current row and  UNBOUNDED  following ) as range from tcost;
 path | cost  | sum_cost | range 
------+-------+----------+--------
  222 | 333.4 |    333.4 | 434.14
  111 |  33.4 |    400.2 | 100.74
  222 |  33.4 |    400.2 | 100.74
  111 |  23.3 |    423.5 |  33.94
  111 |   3.4 |    433.7 |  10.64
   32 |   3.4 |    433.7 |  10.64
  222 |   3.4 |    433.7 |  10.64
   32 |   0.4 |    434.1 |   0.44
   32 |  0.04 |   434.14 |   0.04
(9 rows)
 
test=#

可以看到，RANGE时，相同数据的会被合并到一起再来进行计算，也表明，列中具有相同值的range的值也是相同的，并且结果是它们合并后进行计算后的结果。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

test=# select path, cost, sum(cost) over (order by cost desc) as sum_cost, sum(cost) over (order by cost desc range  between current row and  UNBOUNDED  following ) as range,sum(cost) over (order by cost desc rows between current row and UNBOUNDED  following ) as row from tcost;
 path | cost  | sum_cost | range  |  row  
------+-------+----------+--------+--------
  222 | 333.4 |    333.4 | 434.14 | 434.14
  111 |  33.4 |    400.2 | 100.74 | 100.74
  222 |  33.4 |    400.2 | 100.74 |  67.34
  111 |  23.3 |    423.5 |  33.94 |  33.94
  111 |   3.4 |    433.7 |  10.64 |  10.64
   32 |   3.4 |    433.7 |  10.64 |   7.24
  222 |   3.4 |    433.7 |  10.64 |   3.84
   32 |   0.4 |    434.1 |   0.44 |   0.44
   32 |  0.04 |   434.14 |   0.04 |   0.04
(9 rows)
 
test=#

这里可以非常明显看到RANGE和ROWS的区别。
ROWS：是按物理行来进行区分的
RANGE：是按数值进行逻辑区分的

RANGE 和 ROWS 在PostgreSQL中的语法

1
2

[ RANGE | ROWS ] frame_start
[ RANGE | ROWS ] BETWEEN frame_start AND frame_end

frame_start 和 frame_end可以是：

1
2
3
4
5

UNBOUNDED PRECEDING
value PRECEDING
CURRENT ROW
value FOLLOWING
UNBOUNDED FOLLOWING

特别注意：value PRECEDING和value FOLLOWING 当前只允许ROWS模式。
RANGE模式后面只能接 UNBOUNDED FOLLOWING。

默认的框架选项是RANGE UNBOUNDED PRECEDING，该选项与 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW相同。有ORDER BY， 它设置框架从分区的开始一直到与当前行相同的最后一行。没有ORDER BY， 那么就是当前分区的所有行都包含在框架中，因为所有行都会成为当前行的相同行。

RANGE时，请注意有没有 order by 的区别

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

test=# select path, cost, sum(cost) over (order by cost desc) as sum_cost, sum(cost) over (range  between current row and  UNBOUNDED  following ) as no_order_by_range,sum(cost) over (order by cost desc range between current row and UNBOUNDED  following ) as has_order_by_range from tcost;
 path | cost  | sum_cost | no_order_by_range | has_order_by_range
------+-------+----------+-------------------+--------------------
  222 | 333.4 |    333.4 |            434.14 |             434.14
  111 |  33.4 |    400.2 |            434.14 |             100.74
  222 |  33.4 |    400.2 |            434.14 |             100.74
  111 |  23.3 |    423.5 |            434.14 |              33.94
  111 |   3.4 |    433.7 |            434.14 |              10.64
   32 |   3.4 |    433.7 |            434.14 |              10.64
  222 |   3.4 |    433.7 |            434.14 |              10.64
   32 |   0.4 |    434.1 |            434.14 |               0.44
   32 |  0.04 |   434.14 |            434.14 |               0.04
(9 rows)
 
test=#

没有ORDER BY， 那么就是当前分区的所有行都包含在框架中，因为所有行都会成为当前行的相同行。

ROWS时，请注意有没有 order by 的区别

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

test=# select path, cost, sum(cost) over (order by cost desc) as sum_cost, sum(cost) over (rows  between current row and  UNBOUNDED  following ) as no_order_by_rows,sum(cost) over (order by cost desc rows between current row and UNBOUNDED  following ) as has_order_by_rows from tcost;
 path | cost  | sum_cost | no_order_by_rows | has_order_by_rows
------+-------+----------+------------------+-------------------
  222 | 333.4 |    333.4 |           434.14 |            434.14
  111 |  33.4 |    400.2 |           100.74 |            100.74
  222 |  33.4 |    400.2 |            67.34 |             67.34
  111 |  23.3 |    423.5 |            33.94 |             33.94
  111 |   3.4 |    433.7 |            10.64 |             10.64
   32 |   3.4 |    433.7 |             7.24 |              7.24
  222 |   3.4 |    433.7 |             3.84 |              3.84
   32 |   0.4 |    434.1 |             0.44 |              0.44
   32 |  0.04 |   434.14 |             0.04 |              0.04
(9 rows)
 
test=#

有没有ORDER BY，都是一样的，因为ROWS是按物理分行的，而不是按逻辑分行的。

总结

1
2
3
4
5
6
7

ROWS：是按物理行来进行窗口级别里再次进行范围选择的。
RANGE：是按逻辑行来进行窗口级别里再次进行范围选择的。RANGE时，相同行会被合并成同一条数据再进行计算，相同行窗口计算时的结果也是相同的。
是否是相同行，是根据ORDER BY排序时的结果决定的。
有ORDER BY时：同行是说在ORDER BY排序时不唯一的行。【即具有相同数值的行】
             不同行是说ORDER BY排序时具有不同的数值的行。
没有ORDER BY：那么就是当前分区的所有行都包含在框架中，因为所有行都会成为当前行的相同行。【特别要注意最后一句的意思】

原文地址

我也不知道，也不敢肯定为什么PostgreSQL的logo是一头大象 … 我正试着找出这个答案。

到现在为至，我最好的猜测是因为以下这个事实：在许多文化里，大象是众所周知的，具有非常好的记忆力的，或者至少是一个非常好记忆力的象征…因此，我们可以说：大象是可以非常棒地保存（数据库！）记忆（数据！）…或者换句话说：“大象是永不忘记的”…

这仅仅是我自己认为和大胆猜测的。

不过，我已经找到几个关于PostgreSQL logo原型的有趣的和确定的事情：

PostgreSQL的logo的大象名字叫”Slonik”

“Slonik”意思是”小象”并且名字来自”slony”(复数）或”slon”(单数）其中之一，分别来自俄罗斯单词“слоны”意思是“大象”（复数）或者“大象”（单数）

是不是很有趣？无论PostgreSQL的logo历史是如何的，它是一个非常棒的开源数据库。如果我发现更多关于”Slonik”的资料，我会再来更新的。

注意

1
2
3

参数名：不区分大小写。
参数值：布尔、整数、浮点数、字符串（大小写无关， 枚举参数值是不区分大小写的）
布尔值可以是：on, off, true, false, yes, no, 1, 0 或这些东西的任意清晰无歧义的前缀。

1
2
3

内存单位：kB(千字节), MB(兆字节), GB(吉字节)； 
时间单位：ms(毫秒), s(秒), min(分钟), h(小时), d(天)。 
内存单位中的"千"是1024， 不是1000。

查看参数的默认单位

1

select name, unit from pg_settings;

查看参数允许的枚举

1

select name, enumvals from pg_settings;

设置参数

方式一：在 postgresql.conf设置

方式二：在启动时传递参数：postgres -c log_connections=yes -c log_destination=’syslog’

在 psql 里查看及设置参数

1
2

查看：show postgresql.conf中的参数名;
设置：set postgresql.conf中的参数名 TO 参数值 | set postgresql.conf中的参数名=参数值

昨天在群里看到有同鞋问：如何删除表空间？因为删除的时候，一直报

1
2
3

postgres=# drop tablespace mytmp;
ERROR:  tablespace "mytmp" is not empty
postgres=#

原因

想要删除表空间，那么该表空间必须为空（即没有任何其他的对象会使用到该表空间）才能被删除。

找出有哪些对象使用到了表空间

SQL语句：

1
2
3
4
5
6
7
8
9
10
11
12
13
14

postgres=# SELECT                
  c.relname,
  t.spcname
FROM
  pg_class c
    JOIN pg_tablespace t ON c.reltablespace = t.oid
WHERE
  t.spcname = 'mytmp';
relname | spcname
---------+---------
 tsp     | mytmp
(1 row)
 
postgres=#

删除

找到表空间的使用对象时，这时如果确实要删除表空间，那就要将该表空间下的表对象都删除了最后才能删除表空间。

删除该表空间下的表

1
2
3

postgres=# drop table tsp;
DROP TABLE
postgres=#

删除该表空间

1
2
3

postgres=# drop tablespace mytmp;
DROP TABLESPACE
postgres=#

再检查一次看看

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

postgres=# select * from pg_tablespace where spcname = 'mytmp';
spcname | spcowner | spcacl | spcoptions
---------+----------+--------+------------
(0 rows)
 
postgres=# 
postgres=# SELECT               
postgres-#   c.relname,
postgres-#   t.spcname
postgres-# FROM
postgres-#   pg_class c
postgres-#     JOIN pg_tablespace t ON c.reltablespace = t.oid
postgres-# WHERE
postgres-#   t.spcname = 'mytmp';
relname | spcname
---------+---------
(0 rows)
 
postgres=#

权限

1
2
3

copy 必须要以超级用户来运行
\copy 则不必

文件位置

1
2
3
4
5

copy 的文件必须是在服务器端的位置
\copy 的则是在客户端的位置。
所以，文件的权限方面，copy是以服务器的为准，\copy 是以客户端的为准

例子

DB服务器：10.0.0.10

1
2
3
4
5
6
7

postgres=# \du yang;
           List of roles
 Role name | Attributes | Member of 
-----------+------------+-----------
 yang      |            | {}
postgres=#

客户端（10.0.0.11）连接到DB服务器（10.0.0.10）
客户端运行copy命令（11）：

/tmp/tcopy.txt该文件是在客户端（10.0.0.11)的，DB服务器（10.0.0.10）并不存在该文件。

1
2
3
4

test=> copy tcopy from '/tmp/tcopy.txt';
ERROR:  must be superuser to COPY to or from a file
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.
test=>

它会报告必须要以数据库的超级用户身份才能运行copy命令。

客户端运行\copy命令（11）：

1
2
3

test=> \copy tcopy from '/tmp/tcopy.txt';
COPY 3
test=>

它没有报告必须要以数据库的超级用户来运行，而且也没有报告文件或目录不存在的问题。

在DB（10.0.0.10）服务器端修改用户yang为超级用户:

1
2
3

postgres=# alter role yang superuser ;
ALTER ROLE
postgres=#

然后在客户端再执行看看：

1
2
3

test=> copy tcopy from '/tmp/tcopy.txt';
ERROR:  could not open file "/tmp/tcopy.txt" for reading: No such file or directory
test=>

它现在不报权限了，而是报告文件或目录不存在。这是因为该文件是在客户端的，而copy只会在服务器端定位。

在客户端换成\copy命令看看：

1
2
3

test=> \copy tcopy from '/tmp/tcopy.txt';
COPY 3
test=>

可以发现COPY成功了。

PG对各表的统计信息：pg_stats

资源来源：PostgreSQL 9.3.1 中文文档 —— Chapter 47. 系统表 —— 47.68. pg_stats

成本因子

因为PostgreSQL是基于代价模型来选择最优的执行计划的，而成本因子则是计算代价模型的最重要参数。（代价=CPU代价+IO代价+数据传输[如网络]代价）

在PG9.4默认情况下的成本因子如下：（这些值可以在 postgresql.conf 文件里修改的）

1
2
3
4
5
6
7
8

# - Planner Cost Constants -
#seq_page_cost = 1.0                    # measured on an arbitrary scale。扫描一个数据块（一页）的成本（IO成本）
#random_page_cost = 4.0                 # same scale as above。随机获取一个数据块（一页）的成本（IO成本）
#cpu_tuple_cost = 0.01                  # same scale as above。获取一行数据的CPU成本
#cpu_index_tuple_cost = 0.005           # same scale as above。获取一个索引项的CPU成本
#cpu_operator_cost = 0.0025             # same scale as above。每个操作符的CPU成本
#effective_cache_size = 4GB             #评估操作系统缓存可能使用的内存大小。用于评估索引扫描的开销，大的值倾向使用索引，小的值倾向使用全表扫描。一般设置为“物理内存 - shared buffers - 内核和其他软件占用的内存”。

注意：SSD的随机读和顺序读差别不是太大，这时可以缩小 seq_page_cost 和 random_page_cost 之间的大小。使random_page_cost趋向于seq_page_cost。

关于 effective_cache_size 特别说明一下

资料来源

effective_cache_size用于在Linux操作系统上报告内核缓存的大小，我想强调一下它在postgresql.conf配置里的重要性。
effective_cache_size

不像其他内存那样是设置已经分配好的控制内存，effective_cache_size用于告诉优化器在内核里有多少cache（读缓存）。这对于决定代价高的索引扫描方式是非常重要的。优化器知道 shared_buffers 大小，但是不知道内核缓存大小，从而影响到代价非常高的磁盘访问。

内核缓存大小改变比较频繁，所以，正常地运行一段时间的系统负载，然后使用该内存值去设置 effective_cache_size。这个值不必是非常完美的，仅仅只是粗略地估计还有多少内核内存，相当于是shared buffers的二级缓存。

explain 输出及详解

explain 语法：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

test=# \h explain;
Command:     EXPLAIN
Description: show the execution plan of a statement
Syntax:
EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement
 
where option can be one of:
 
    ANALYZE [ boolean ] 是否真正执行
    VERBOSE [ boolean ] 显示详细信息
    COSTS [ boolean ] 显示代价信息
    BUFFERS [ boolean ] 显示缓存信息
    TIMING [ boolean ] 显示时间信息
    FORMAT { TEXT | XML | JSON | YAML } 输出格式，默认为 text

图解：

注意

看执行计划，是从最底层开始往后看的。即先从 节点1，再到节点2，最后到节点3。
而且，上一级节点的成本，是包含了下一级的成本的。比如：节点2的启动成本和结束成本是已经包含了节点1的启动成本和结束成本的，由此可以得出一个结论：就是上一级节点的启动成本和结束成本永远不会比下一级的小。

再次强调一下，每个节点的估算的总启动或结束成本只是平均每次loops的平均成本，所以最后的总成本还要乘以loops次数。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

test=# explain(analyze, timing, verbose, buffers,costs) select max(sum) from ( select count(*) as sum from tgroup group by point) as t;
                                                               QUERY PLAN                                                                
-----------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=235363.15..235363.16 rows=1 width=8) (actual time=4898.900..4898.900 rows=1 loops=1)
   Output: max((count(*)))
   Buffers: shared hit=12770 read=49578
   ->  HashAggregate  (cost=235363.04..235363.09 rows=5 width=4) (actual time=4898.890..4898.891 rows=5 loops=1)
         Output: count(*), tgroup.point
         Group Key: tgroup.point
         Buffers: shared hit=12770 read=49578
         ->  Seq Scan on public.tgroup  (cost=0.00..177691.36 rows=11534336 width=4) (actual time=0.045..1643.984 rows=11534336 loops=1)
               Output: tgroup.id, tgroup.age, tgroup.point
               Buffers: shared hit=12770 read=49578
 Planning time: 0.170 ms
 Execution time: 4898.982 ms
(12 rows)
Time: 4899.758 ms
test=#

估算成本的计算公式

全表扫描时计算：

公式：total cost = relpages * seq_page_cost + reltuples * cpu_tuple_cost。

1
2
3
4
5
6

test=# select relpages, reltuples from pg_class where relname = 'tgroup';
-[ RECORD 1 ]----------
relpages  | 62348
reltuples | 1.15343e+07
Time: 0.751 ms

1
2
3
4
5

test=# show seq_page_cost ;
-[ RECORD 1 ]-+--
seq_page_cost | 1
Time: 28.848 ms

1
2
3
4
5

test=# show cpu_tuple_cost;
-[ RECORD 1 ]--+-----
cpu_tuple_cost | 0.01
Time: 0.460 ms

如上节点1执行计划，是全表扫描：
177691.36 = 62348 * 1 + 1.15343e+07 * 0.01

1
2
3
4
5
6

test=# select 62348 * 1 + 1.15343e+07 * 0.01;
-[ RECORD 1 ]-------
?column? | 177691.00
Time: 39.815 ms
test=#

这个与结果相符。

全表顺序扫描并过滤，代价公式为:

Cost = seq_scan_cost*relpages + cpu_tuple_cost*reltuples + cpu_operator_cost*reltuples

扫描的方式

1
2
3
4
5
6
7
8
9
10
11

enable_bitmapscan = on
enable_hashagg = on
enable_hashjoin = on
enable_indexscan = on #索引扫描
enable_indexonlyscan = on #只读索引扫描
enable_material = on #物化视图
enable_mergejoin = on
enable_nestloop = on
enable_seqscan = on
enable_sort = on
enable_tidscan = on

虽然我们不能强制指定PostgreSQL按我们写的SQL来执行（无视优化器），但是可以通过改变某些的查询方式代价从而影响PostgreSQL的查询优化器的选择。
这时，我们可以从上面的扫描方式中将其修改为 off（其实不是强制不能以某种方式扫描，将其设置为 off时，只是将该项的扫描代价提高到非常大的值，从而让PostgreSQL尽可能避免使用该方式来进行扫描，但不是绝对的，如果其他的方式比off的代价更大，那PostgreSQL还是会选择代价最小的来执行的），这就为我们提供了非常好的控制我们SQL的扫描方式。