数据库►PostgreSQL

PostgreSQL中代替MySQL的内存表方法

今天在群里发现有人问PostgreSQL里，有没有类似MySQL的内存表（Memory引擎）。

其实在PostgreSQL里，就只有一种表：堆表。不像MySQL那种提供那么多种的表类型（这个是由引擎来决定的，比如InnoDB的是索引组织表等）。

替代方法

1） 使用RAM Disk。创建一个表空间到Ram Disk上，然后建表的时候指定表空间到该 Ram Disk即可。

2） 如果需要创建那种全局可见的临时表，建表时可以使用UNLOGGED选项来创建表。这种表性能比一般的快。

实战使用 Ram Disk 作为“临时表”

步骤1：创建一个Ram Disk

1
sudo mount tmpfs /home/postgres/ramdisk/ -t tmpfs

步骤2：更改拥有者为Postgres

1
sudo chown postgres:postgres /home/postgres/ -R

步骤3：创建Ram的表空间

1
create tablespace ramdis location '/home/postgres/ramdisk';

步骤4：创建一个表，并指定表空间到Ram Disk的表空间

1
2
3
test=# create table ram (id serial, name varchar(40), age int) TABLESPACE ramdis;
CREATE TABLE
test=#

注意

有人担心重启电脑后，Ram disk 的表空间不存在了，会不会影响PostgreSQL服务器的启动。这其实是不用担心的，本人测试过，关闭PostgreSQL，然后删除Ram Disk 的表空间目录，然后再启动PostgreSQL，完全是没问题的。

不过，创建在该表空间的表就不能操作了。典型的错误提示如下：

1
2
3
test=# select * from ram;
ERROR:  could not open file "pg_tblspc/16718/PG_9.4_201409291/16389/16729": No such file or directory
test=#

删除该表就OK了。

数据库►PostgreSQL

使用PostgreSQL无限递归 SELECT 评论系统

假设有个评论系统，要求支持无限层级的回复，就像一棵树那样

1
2
3
4
5
       文章
     /      \
    /        \
评论1     评论2
....

注意可以有任意个子树以及做任意个叶子

大意的表结构

1
2
3
4
5
6
7
8
create table comments (
    comment_id serial primary key,
    parent_id bigint,
    bug_id bigint not null,
    author varchar(20) not null,
    comment text not null,
    foreign key (parent_id) references comments(comment_id)
);
1
2
3
4
5
6
7
8
9
10
11
test=# select * from comments;
 comment_id | parent_id | bug_id | author |       comment      
------------+-----------+--------+--------+---------------------
          1 |           |      1 | Fran   | 这个bug的成因是什么
          2 |         1 |      1 | Ollie  | 我觉得是一个空指针
          3 |         2 |      1 | Fran   | 不，我查过了
          4 |         1 |      1 | Kukla  | 我们需要查无效输入
          5 |         4 |      1 | Ollie  | 是的，那是个问题
          6 |         4 |      1 | Fran   | 好，查一下吧
          7 |         6 |      1 | Kukla  | 解决了
(7 rows)

SQL语句

利用递归查询，可以查某篇文章评论组成的树结构。其中 depth是树的深度，显示的时候，按已经排序好的层次及相应的父结点显示出来就可以了。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
test=# with RECURSIVE  commenttree (comment_id, bug_id, parent_id, author, comment, depth) as (select comment_id, bug_id, parent_id, author, comment , 0 as depth from comments where parent_id is null union all select c.comment_id, c.bug_id, c.parent_id, c.author, c.comment, ct.depth+1 as depth from commenttree as ct join comments as c on (ct.comment_id = c.parent_id)) select * from commenttree where bug_id = 1;;
 comment_id | bug_id | parent_id | author |       comment       | depth
------------+--------+-----------+--------+---------------------+-------
          1 |      1 |           | Fran   | 这个bug的成因是什么 |     0
          2 |      1 |         1 | Ollie  | 我觉得是一个空指针  |     1
          4 |      1 |         1 | Kukla  | 我们需要查无效输入  |     1
          3 |      1 |         2 | Fran   | 不，我查过了        |     2
          5 |      1 |         4 | Ollie  | 是的，那是个问题    |     2
          6 |      1 |         4 | Fran   | 好，查一下吧        |     2
          7 |      1 |         6 | Kukla  | 解决了              |     3
(7 rows)
 
test=#

注意PostgreSQL里，必须加 RECURSIVE 才能支持递归。

内容来源资料：
[1]《SQL反模式》

数据库►PostgreSQL

PostgreSQL使用 postgres_fdw 进行跨库操作

说明

该 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] 官网

数据库►PostgreSQL

[翻译]使用部分索引来加速PostgreSQL

原文

本文经过原作者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)
数据库►PostgreSQL

PostgreSQL窗口函数中 ROWS 和 RANGE 模式的区别

示例表数据如下：

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

[翻译]为什么PostgreSQL的logo是一个大象？

原文地址

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

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

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

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

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

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

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

数据库►PostgreSQL

PostgreSQL查看及设置参数、单位、描述等信息

注意

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中的参数名=参数值
数据库►PostgreSQL

PostgreSQL删除表空间

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

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=#
数据库►PostgreSQL

PostgreSQL中 copy 和 \copy 的区别

权限

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成功了。

数据库►PostgreSQL

PostgreSQL执行计划和成本因子详解

PG对各表的统计信息：pg_stats

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

名字 类型 引用 描述
schemaname name pg_namespace.nspname 包含此表的模式名字
tablename name pg_class.relname 表的名字
attname name pg_attribute.attname 这一行描述的字段的名字
inherited bool 如果为真，那么这行包含继承的子字段，不只是指定表的值。
null_frac real 记录中字段为空的百分比
avg_width integer 字段记录以字节记的平均宽度
n_distinct real 如果大于零，就是在字段中独立数值的估计数目。如果小于零， 就是独立数值的数目被行数除的负数。用负数形式是因为ANALYZE 认为独立数值的数目是随着表增长而增长； 正数的形式用于在字段看上去好像有固定的可能值数目的情况下。比如， -1 表示一个唯一字段，独立数值的个数和行数相同。
most_common_vals anyarray 一个字段里最常用数值的列表。如果看上去没有啥数值比其它更常见，则为 null
most_common_freqs real[] 一个最常用数值的频率的列表，也就是说，每个出现的次数除以行数。 如果most_common_vals是 null ，则为 null。
histogram_bounds anyarray 一个数值的列表，它把字段的数值分成几组大致相同热门的组。 如果在most_common_vals里有数值，则在这个饼图的计算中省略。 如果字段数据类型没有<操作符或者most_common_vals 列表代表了整个分布性，则这个字段为 null。
correlation real 统计与字段值的物理行序和逻辑行序有关。它的范围从 -1 到 +1 。 在数值接近 -1 或者 +1 的时候，在字段上的索引扫描将被认为比它接近零的时候开销更少， 因为减少了对磁盘的随机访问。如果字段数据类型没有<操作符，那么这个字段为null。
most_common_elems anyarray 经常在字段值中出现的非空元素值的列表。（标量类型为空。）
most_common_elem_freqs real[] 最常见元素值的频率列表，也就是，至少包含一个给定值的实例的行的分数。 每个元素频率跟着两到三个附加的值；它们是在每个元素频率之前的最小和最大值， 还有可选择的null元素的频率。（当most_common_elems 为null时，为null）
elem_count_histogram real[] 该字段中值的不同非空元素值的统计直方图，跟着不同非空元素的平均值。（标量类型为空。）

成本因子

因为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的扫描方式。