数据库优化
1. 优化准则和方法
优化指标
- 响应时间：衡量数据库系统与用户交互需要多久时间能够发出响应
- 吞吐量：衡量在单位时间里可以完成的数据库任务
2. SQL 处理过程
当PostgreSQL的后台服务进程postgres接收到客户端的查询语句后，首先将其传递到查询分析模块，进行词法、语法和语义分析。对于建表、创建用户、备份等将其分配到功能性命令处理模块；对于SELECT、INSERT、DELETE、UPDATE则将其构建查询树，然后交给查询重写模块。重写重写模块收到查询树后，按照该查询所涉及的规则和视图对查询树进行重写，生成新的查询树。生成路径模块依据重写过的查询树，考虑关系的访问方式、连接方式和连接顺序等问题，采用动态规划算法或遗传算法，生成最优的表连接路径。最后，由最优路径生成可执行计划，并将其传递到查询执行模块执行。
查询优化的核心时生成路径和生成计划两个模块。在整个查询执行过程中，表连接操作的开销最大，查询优化处理的问题焦点在于如何计算最优的表连接路径。
2.1 建立连接
PostgreSQL以一种简单的“一用户一进程”的客户端/服务器模型实现。在该模型中，一个客户端进程仅连接到一个服务器进程。由于我们无法预先知道会有多少连接被建立，我们必须使用一个主进程在每次连接请求时生产一个新的服务器进程。该主进程被称为postgres，它在一个特定的TCP/IP端口监听进入的连接。当一个连接请求被监测到时，postgres会产生一个新的服务器进程。服务器进程之间通过信号和共享内存通信，以保证并发数据访问时的数据完整性。
一旦一个连接被建立，客户端进程就能发送一个查询给后台进程。查询被以纯文本传送，即在前端（客户端）不做任何分析。服务器会分析查询，创建一个执行计划，然后执行之并通过已建立的连接向客户端返回检索到的行。
2.2 查询分析器(parser)
查询分析包括词法分析、语法分析和语义分析三个部分。用户输入的SQL命令作为字符串传递给查询分析器，对其进行词法分析和语法分析生成分析树，然后进行语义分析得到查询树。
####### 查询分析过程主要函数调用关系
对于用户的SQL命令，统一由exec_sample_query函数处理，它调用函数pg_parse_query完成词法和语法分析并产生分析树，接下来调用pg_analyze_and_rewrite函数逐个对分析树进行语义分析和重写；在pg_analyze_and_rewrite函数中调用parse_analyzer进行语义分析并创建查询树，函数pg_rewrite_query负责对查询树进行重写。
- exec_sample_query调用函数pg_parse_query进入词法和语法分析的主体处理过程，然后函数pg_parse_query调用词法和语法分析的入口函数raw_parse生成分析树
- 函数pg_parse_query返回分析树给外部函数
- exec_simple_query接着调用函数pg_analyze_and_rewrite进行语义分析和查询重写。首先调用函数parse_analyze进行语义分析并生成查询树，之后将查询树传递给函数pg_rewrite_query进行查询重写
分析器阶段由两部分组成：
- 分析器定义在
gram.y和scan.l中，它使用Unix工具bison和flex构建。 - 转换处理将对分析器返回的数据结构进行修改和增加。
分析器
分析器必须检查查询字符串（以纯文本形式到达）是否为合法语法。如果语法正确将建立一个分析树并返回之，否则将返回一个错误。 语法分析器和词法分析器使用著名的Unix工具bison和flex实现。
词法分析器定义在文件scan.l中，并负责识别标识符、SQL关键词等。对于找到的每一个关键词或标识符将生成一个记号并返回给语法分析器。
语法分析器定义在gram.y文件中，它由一组语法规则 和动作，动作将在规则被触发时被执行。动作的代码（实际上是C代码）将被用于构建分析树。
程序flex把文件scan.l转换成C源文件scan.c， 程序bison把文件gram.y转换为gram.c。在这些转换结束后，一个正规的C编译器就可以用于创建分析器。绝不要对生成的C文件做任何修改，因为每次flex或bison被调用都会重写它们。
转换处理
分析阶段根据SQL的语法结构的固定规则创建一个分析树。它不会在系统目录做任何查找，这样它不可能了解所请求的操作的详细语义。在分析器完成之后，转换处理接手分析器返还的树，并进行语义解释来理解该查询引用了哪些表、函数和操作符。用于表示该信息的数据结构被称为查询树。
将原始分析从语义分析中分离出来的原因是系统目录的查找只能在一个事务中完成，而我们不希望在收到一个查询字符串时立即开始一个事务。原始分析阶段足以识别事务控制命令（BEGIN、ROLLBACK等），并且这些可以在没有任何进一步分析之前正确地被执行。一旦我们知道我们正在处理一个确切的查询（例如SELECT或UPDATE），就可以开始一个事务（如果我们还不在其中）。只有到这时转换处理才能被调用。
2.3 重写器(Rewriter)
在完成语义分析步骤得到查询树之后，对查询树进行查询重写处理。查询重写的入口函数pg_rewrite_query，它在pg_analyze之后被pg_analyze_and_rewrite调用，且pg_rewrite_query的参数就是pg_analyze的返回值（查询树）。查询重写模块使用规则系统判断来进行查询重写，如果查询树中某个目标被定义了转换规则，则该转换规则会被用来重写查询树。
规则系统（更准确地说是查询重写规则系统）
规则系统的实现是一种称为查询重写的技术，必要时根据pg_rules系统目录中存储的规则转换查询树。重写器的输入和输出都是查询树。规则动作也被做为查询树存储在系统目录pg_rewrite中(SELECT oid, rulename, ev_class, ev_type FROM pg_rewrite;)。
规则系统把查询修改为需要考虑规则，并且然后把修改过的查询传递给查询规划器进行规划和执行。
规则系统位于分析器和规划器之间。它用分析器的输出（即一个查询树）和用户定义的重写规则（也是查询树，不过带有一些额外信息），以查询树作为结果输出。
查询树组成
- 命令类型：说明是哪一种命令（
SELECT、INSERT、UPDATE、DELETE）产生了该查询树 - 范围表：范围表是被使用在该查询中的关系的列表。在一个
SELECT语句中，范围表是在关键词FROM后面给出的关系。每一个范围表项标识一个表或视图，并且说明在该查询的其他部分要以哪个名称调用它。 - 结果关系：指向范围表的索引，它标识了该查询的结果应该去哪个关系。
- 目标列表：一个表达式的列表，它定义了查询的结果。目标列表中的每一个项所包含的表达式可以是一个常量值、一个指向范围表中关系的列的变量、一个参数或一个由函数调用、常量、变量、操作符等构成的表达式树。
- 条件：查询的条件是一个表达式，它很像包含在目标列表项中的表达式。这个表达式的结果值是一个布尔值，它说明对最终结果行的操作（
INSERT、UPDATE、DELETE或SELECT）是否应该被执行。它对应于一个SQL语句的WHERE子句。 - 连接树：查询的连接树展示了
FROM子句的结构。
视图和规则系统
PostgreSQL中的视图是通过规则系统来实现的。
-- 创建视图
CREATE VIEW myview AS SELECT * FROM mytab;
-- 等价命令：
CREATE TABLE myview (same column list as mytab);
CREATE RULE "_RETURN" AS ON SELECT TO myview DO INSTEAD
SELECT * FROM mytab;
####### SELECT规则
规则ON SELECT被应用于所有查询作为最后一步，即使给出的是一条INSERT、UPDATE或DELETE命令。一个ON SELECT规则中只能有一个动作， 而且它必须是一个无条件的INSTEAD的SELECT动作。 这个限制是为了令规则足够安全，以便普通用户也可以打开它们，并且它限制ON SELECT规则使之行为类似视图。
-- 创建一个函数min用于返回2个整数中的较小者
CREATE FUNCTION min(integer, integer) RETURNS integer AS $$
SELECT CASE WHEN $1 < $2 THEN $1 ELSE $2 END
$$ LANGUAGE SQL STRICT;
-- 创建基表
CREATE TABLE shoe_data (
shoename text, -- 主键
sh_avail integer, -- 可用的双数
slcolor text, -- 首选的鞋带颜色
slminlen real, -- 最小鞋带长度
slmaxlen real, -- 最大鞋带长度
slunit text -- 长度单位
);
CREATE TABLE shoelace_data (
sl_name text, -- 主键
sl_avail integer, -- 可用的双数
sl_color text, -- 鞋带颜色
sl_len real, -- 鞋带长度
sl_unit text -- 长度单位
);
CREATE TABLE unit (
un_name text, -- 主键
un_fact real -- 转换到厘米的参数
);
-- 创建视图
CREATE VIEW shoe AS
SELECT sh.shoename,
sh.sh_avail,
sh.slcolor,
sh.slminlen,
sh.slminlen * un.un_fact AS slminlen_cm,
sh.slmaxlen,
sh.slmaxlen * un.un_fact AS slmaxlen_cm,
sh.slunit
FROM shoe_data sh, unit un
WHERE sh.slunit = un.un_name;
CREATE VIEW shoelace AS
SELECT s.sl_name,
s.sl_avail,
s.sl_color,
s.sl_len,
s.sl_unit,
s.sl_len * u.un_fact AS sl_len_cm
FROM shoelace_data s, unit u
WHERE s.sl_unit = u.un_name;
CREATE VIEW shoe_ready AS
SELECT rsh.shoename,
rsh.sh_avail,
rsl.sl_name,
rsl.sl_avail,
min(rsh.sh_avail, rsl.sl_avail) AS total_avail
FROM shoe rsh, shoelace rsl
WHERE rsl.sl_color = rsh.slcolor
AND rsl.sl_len_cm >= rsh.slminlen_cm
AND rsl.sl_len_cm <= rsh.slmaxlen_cm;
-- 元命令查看视图
dv[+]
/*
创建shoelace视图的CREATE VIEW命令（也是最简单的一个）将创建一个shoelace关系和一个pg_rewrite项， 这个pg_rewrite项说明有一个重写规则，只要一个查询的范围表中引用了关系shoelace，就必须应用它。
*/
-- 插入表数据
INSERT INTO unit VALUES ('cm', 1.0);
INSERT INTO unit VALUES ('m', 100.0);
INSERT INTO unit VALUES ('inch', 2.54);
INSERT INTO shoe_data VALUES ('sh1', 2, 'black', 70.0, 90.0, 'cm');
INSERT INTO shoe_data VALUES ('sh2', 0, 'black', 30.0, 40.0, 'inch');
INSERT INTO shoe_data VALUES ('sh3', 4, 'brown', 50.0, 65.0, 'cm');
INSERT INTO shoe_data VALUES ('sh4', 3, 'brown', 40.0, 50.0, 'inch');
INSERT INTO shoelace_data VALUES ('sl1', 5, 'black', 80.0, 'cm');
INSERT INTO shoelace_data VALUES ('sl2', 6, 'black', 100.0, 'cm');
INSERT INTO shoelace_data VALUES ('sl3', 0, 'black', 35.0 , 'inch');
INSERT INTO shoelace_data VALUES ('sl4', 8, 'black', 40.0 , 'inch');
INSERT INTO shoelace_data VALUES ('sl5', 4, 'brown', 1.0 , 'm');
INSERT INTO shoelace_data VALUES ('sl6', 0, 'brown', 0.9 , 'm');
INSERT INTO shoelace_data VALUES ('sl7', 7, 'brown', 60 , 'cm');
INSERT INTO shoelace_data VALUES ('sl8', 1, 'brown', 40 , 'inch');
SELECT * FROM shoelace;
当客户端发送SQL语句SELECT * FROM shoelace，它会被分析器解释并生成下面的查询树
SELECT shoelace.sl_name, shoelace.sl_avail,
shoelace.sl_color, shoelace.sl_len,
shoelace.sl_unit, shoelace.sl_len_cm
FROM shoelace shoelace;
然后交给规则系统，规则系统遍历范围表
SELECT s.sl_name, s.sl_avail,
s.sl_color, s.sl_len, s.sl_unit,
s.sl_len * u.un_fact AS sl_len_cm
FROM shoelace old, shoelace new,
shoelace_data s, unit u
WHERE s.sl_unit = u.un_name;
重写器简单地创建一个子查询范围表项，它包含规则的动作的查询树，然后用这个范围表记录取代原来引用视图的那个。作为结果的重写后的查询树几乎与你键入的那个一样
SELECT shoelace.sl_name, shoelace.sl_avail,
shoelace.sl_color, shoelace.sl_len,
shoelace.sl_unit, shoelace.sl_len_cm
FROM (SELECT s.sl_name,
s.sl_avail,
s.sl_color,
s.sl_len,
s.sl_unit,
s.sl_len * u.un_fact AS sl_len_cm
FROM shoelace_data s, unit u
WHERE s.sl_unit = u.un_name) shoelace;
输入查询树和输出查询树区别：子查询的范围表有两个额外的项shoelace old和shoelace new。这些项并不直接参与到查询中，因为它们没有被子查询的连接树或者目标列表引用。重写器用它们存储最初出现在引用视图的范围表项中表达的访问权限检查信息。以这种方式，执行器仍然会检查该用户是否有访问视图的正确权限，尽管在重写后的查询中没有对视图的直接使用。
在这个例子中，没有用于shoelace_data或unit的重写规则，所以重写结束并且上面得到的就是给规划器的最终结果。
现在我们想写一个查询，它找出目前在店里哪些鞋子有匹配的（颜色和长度）鞋带并且完全匹配的鞋带双数大于等于二。
SELECT * FROM shoe_ready WHERE total_avail >= 2;
shoename | sh_avail | sl_name | sl_avail | total_avail
----------+----------+---------+----------+-------------
sh1 | 2 | sl1 | 5 | 2
sh3 | 4 | sl7 | 7 | 4
(2 rows)
这词解析器的输出是查询树：
SELECT shoe_ready.shoename, shoe_ready.sh_avail,
shoe_ready.sl_name, shoe_ready.sl_avail,
shoe_ready.total_avail
FROM shoe_ready shoe_ready
WHERE shoe_ready.total_avail >= 2;
第一个被应用的规则将是用于shoe_ready的规则并且它会导致查询树：
SELECT shoe_ready.shoename, shoe_ready.sh_avail,
shoe_ready.sl_name, shoe_ready.sl_avail,
shoe_ready.total_avail
FROM (SELECT rsh.shoename,
rsh.sh_avail,
rsl.sl_name,
rsl.sl_avail,
min(rsh.sh_avail, rsl.sl_avail) AS total_avail
FROM shoe rsh, shoelace rsl
WHERE rsl.sl_color = rsh.slcolor
AND rsl.sl_len_cm >= rsh.slminlen_cm
AND rsl.sl_len_cm <= rsh.slmaxlen_cm) shoe_ready
WHERE shoe_ready.total_avail >= 2;
相似地，用于shoe和shoelace的规则被替换到子查询的范围表中，得到一个三层的最终查询树：
SELECT shoe_ready.shoename, shoe_ready.sh_avail,
shoe_ready.sl_name, shoe_ready.sl_avail,
shoe_ready.total_avail
FROM (SELECT rsh.shoename,
rsh.sh_avail,
rsl.sl_name,
rsl.sl_avail,
min(rsh.sh_avail, rsl.sl_avail) AS total_avail
FROM (SELECT sh.shoename,
sh.sh_avail,
sh.slcolor,
sh.slminlen,
sh.slminlen * un.un_fact AS slminlen_cm,
sh.slmaxlen,
sh.slmaxlen * un.un_fact AS slmaxlen_cm,
sh.slunit
FROM shoe_data sh, unit un
WHERE sh.slunit = un.un_name) rsh,
(SELECT s.sl_name,
s.sl_avail,
s.sl_color,
s.sl_len,
s.sl_unit,
s.sl_len * u.un_fact AS sl_len_cm
FROM shoelace_data s, unit u
WHERE s.sl_unit = u.un_name) rsl
WHERE rsl.sl_color = rsh.slcolor
AND rsl.sl_len_cm >= rsh.slminlen_cm
AND rsl.sl_len_cm <= rsh.slmaxlen_cm) shoe_ready
WHERE shoe_ready.total_avail > 2;
最后规划器会把这个树折叠成一个两层查询树： 最下层的SELECT命令将被“提升”到中间的SELECT中，因为没有必要分别处理它们。但是中间的SELECT仍然和顶层的分开，因为它包含聚集函数。如果我们把它们也提升，它将改变顶层SELECT的行为，这不是我们想要的。不过，折叠查询树是一种优化，重写系统不需要关心它。
####### 非SELECT语句中的视图规则
有两个查询树的细节在上面的视图规则的描述中没有涉及。它们是命令类型和结果关系。实际上，视图规则不需要命令类型，但是结果关系可能会影响查询重写器工作的方式，因为如果结果关系是一个视图，我们需要采取特殊的措施。
一个SELECT的查询树和其它命令的查询树之间很少的几处不同。显然，它们有不同的命令类型并且对于SELECT之外的命令，结果关系指向结果将进入的范围表项。其它所有东西都完全相同。所以如果有两个表t1和t2分别有列a和b，下面两个语句的查询树：
SELECT t2.b FROM t1, t2 WHERE t1.a = t2.a;
UPDATE t1 SET b = t2.b FROM t2 WHERE t1.a = t2.a;
几乎是一样的。特别是：
- 范围表包含表
t1和t2的项。 - 目标列表包含一个变量，该变量指向表
t2的范围表项的列b。 - 条件表达式比较两个范围表项的列
a以寻找相等。 - 连接树展示了
t1和t2之间的一次简单连接。
结果是，两个查询树生成相似的执行计划：它们都是两个表的连接。 对于UPDATE语句，规划器把t1缺失的列加到目标列并且最终查询树读起来是：
UPDATE t1 SET a = t1.a, b = t2.b FROM t2 WHERE t1.a = t2.a;
因此在连接上运行的执行器将产生完全相同的结果集：
SELECT t1.a, t2.b FROM t1, t2 WHERE t1.a = t2.a;
但是在UPDATE中有个小问题：执行器计划中执行连接的部分不关心连接的结果的含义。它只是产生一个行的结果集。一个是SELECT命令而另一个是由执行器中的更高层处理的UPDATE命令，在那里执行器知道这是一个UPDATE，并且它知道这个结果应该进入表t1。但是这里的哪些行必须被新行替换呢？
要解决这个问题，在UPDATE和DELETE语句的目标列表里面增加了另外一个项：当前元组 ID（CTID）。这是一个系统列，它包含行所在的文件块编号和在块中的位置。在已知表的情况下，CTID可以被用来检索要被更新的t1的原始行。在添加CTID到目标列之后，该查询实际看起来像：
SELECT t1.a, t2.b, t1.ctid FROM t1, t2 WHERE t1.a = t2.a;
现在，另一个PostgreSQL的细节进入到这个阶段了。表中的旧行还没有被覆盖，这就是为什么ROLLBACK很快的原因。在一个UPDATE中，新的结果行被插入到表中（在剥除CTID之后），并且把CTID指向的旧行的行头部中的cmax和xmax项设置为当前命令计数器和当前事务 ID 。这样旧的行就被隐藏起来，并且在事务提交之后 vacuum 清理器就可以最终移除死亡的行。
知道了所有这些，我们就可以用完全相同的方式简单地把视图规则应用到任意命令中。没有任何区别。
2.4 规划器(Planner)
规划器从重写器接收查询树，并生成最佳的执行计划树， 执行器可以最有效地处理该树pg_hint_plan扩展插件用来查看SQL的执行计划。规划器用于选择一种代价最小的执行方案（也成为执行计划）。
规划器处理流程
- 预处理：对查询树进一步改造（提升子链接和子查询）
- 生成路径：接收到改造后的查询树后，采用动态规划算法或遗存算法，生成最优的链接路径和候选的路劲链表
- 生成计划：用得到的最优路径，生成基本计划树，然后添加group by等子句所对应的计划节点形成完整计划树
检查一个查询的每一种可能的执行方式会耗费非常多的时间和内存空间。特别是当查询涉及到大量连接操作时。PostgreSQL使用了一种遗传查询优化器 （GEQO)算法选择一个合理的查询计划。
生成可能的计划
规划器/优化器从扫描查询中用到的每一个单独的关系（表）开始生成计划。可能的计划根据每一个关系上可用的索引决定。在一个关系上总是有执行一个顺序扫描的可能，因此一个顺序扫描计划总是会被创建。假设在一个关系上定义有一个索引（例如一个B-tree索引）并且查询包含限制relation.attribute OPR constant。如果relation.attribute正好匹配该B-tree索引的键并且OPR是该索引的操作符类之一，另一个使用B-tree索引扫描该索引的计划将被创建。如果还有索引存在且查询中的限制正好匹配一个索引的键，其他计划也会被考虑。如果有索引的顺序能匹配ORDER BY子句（如果有）或者对于归并连接有用（见下文），也会为该索引创建索引扫描计划。
如果查询需要连接两个或更多关系，在所有扫描单个关系的可能计划都被找到后，连接计划将会被考虑。三种可用的连接策略是：
- 嵌套循环连接: 对左关系找到的每一行都要扫描右关系一次。这种策略最容易实现但是可能非常耗时（但是，如果右关系可以通过索引扫描，这将是一个不错的策略。因为可以用左关系当前行的值来作为右关系上索引扫描的键）。
- 归并连接：在连接开始之前，每一个关系都按照连接属性排好序。然后两个关系会被并行扫描，匹配的行被整合成连接行。由于这种连接中每个关系只被扫描一次，因此它很具有吸引力。它所要求的排序可以通过一个显式的排序步骤得到，或使用一个连接键上的索引按适当顺序扫描关系得到。
- 哈希连接：右关系先被扫描并且被载入到一个哈希表，使用连接属性作为哈希键。接下来左关系被扫描，扫描中找到的每一行的连接属性值被用作哈希键在哈希表中查找匹配的行。
当查询涉及两个以上的关系时，最终结果必须由一个连接步骤树构成，每个连接步骤有两个输入。规划器会检查不同可能的连接序列来找到代价最小的那一个。
如果查询是用的关系数少于geqo_threshold（默认值是12），将使用一次接近穷举的搜索来查找最好的连接顺序。如果任何两个关系在WHERE条件中存在一个相应的连接子句（即存在类似于where rel1.attr1=rel2.attr2的限制），规划器会有限考虑它们之间的连接。没有任何连接子句的连接对只有在别无选择时才会被考虑，即一个关系没有任何可用的对于其他关系的连接子句。对规划器所考虑的每一个连接对会生成所有可能的计划，其中代价（被估计为）最低的一个将被选择。
当连接关系数超过geqo_threshold时，连接序列将考虑通过启发式方法来确定，详见第 59 章。否则处理将和前面相同。
成品计划树包含基本关系的顺序或索引扫描，外加所需的嵌套循环、归并或哈希连接节点，以及任何所需的辅助步骤，例如排序节点或聚集函数计算节点。这些节点中的大部分具有执行选择（丢弃不符合指定布尔条件的行）和投影（根据指定列值计算派生列，即标量表达式的计算）的能力。规划器的职责之一就是在计划树最合适的节点上附加来自于子句的选择条件和需要的输出表达式。
规划器如何使用统计信息
####### 行估计例子
使用PostgreSQL回归测试数据库中的表
从一个很简单的查询开始：
EXPLAIN SELECT * FROM tenk1;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=244)
规划器如何判断tenk1的势在第 14.2 节中介绍，但为了完整还会在这里重复介绍。行数或页数是从pg_class中查出来的：
SELECT relpages, reltuples FROM pg_class WHERE relname = 'tenk1';
relpages | reltuples
----------+-----------
358 | 10000
这些数字是在表上的最后一次VACUUM或ANALYZE以来的当前值。 之后，规划器取出该表中实际的当前页数（这个操作的开销很小，不需要扫描全表）。 如果与relpages不同，则对reltuples 进行相应的缩放以得到一个当前的行数估计。在上面的例子中， relpages的值是最新的， 因此行估计与reltuples相同。
换一个在WHERE子句中带有范围条件的例子：
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 1000;
QUERY PLAN
--------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=24.06..394.64 rows=1007 width=244)
Recheck Cond: (unique1 < 1000)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..23.80 rows=1007 width=0)
Index Cond: (unique1 < 1000)
规划器检查WHERE子句条件，并在pg_operator中查找<操作符的选择度函数。这被保持在oprrest列中， 并且在这个例子中的项是scalarltsel。 scalarltsel函数从pg_statistic为unique1检索直方图。 对于手工查询来说，查看更简单的pg_stats视图会更方便：
SELECT histogram_bounds FROM pg_stats
WHERE tablename='tenk1' AND attname='unique1';
histogram_bounds
------------------------------------------------------
{0,993,1997,3050,4040,5036,5957,7057,8029,9016,9995}
然后，把直方图里面被“< 1000”占据的部分找出来。这就是选择度。直方图把范围分隔成等频的桶， 所以我们要做的只是把我们的值所在的桶找出来，然后计数其中的部分*以及所有*该值之前的部分。值 1000 很明显在第二个桶（970-1943）中。假设每个桶中的值是线性分布，那么就可以计算出选择度：
selectivity = (1 + (1000 - bucket[2].min)/(bucket[2].max - bucket[2].min))/num_buckets
= (1 + (1000 - 993)/(1997 - 993))/10
= 0.100697
也就是一整个桶加上第二个桶的线性部分，除以桶数。那么估计的行数现在可以用选择度乘以tenk1的势来计算：
rows = rel_cardinality * selectivity
= 10000 * 0.100697
= 1007 (rounding off)
然后让我们考虑一个在WHERE子句有等于条件的例子：
EXPLAIN SELECT * FROM tenk1 WHERE stringu1 = 'CRAAAA';
QUERY PLAN
----------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..483.00 rows=30 width=244)
Filter: (stringu1 = 'CRAAAA'::name)
规划器还是检查WHERE子句条件，并为=查找选择度函数（这次是eqsel）。对于等值估计而言，直方图是没用的；相反，最常见值（MCV）列表可以用来决定选择度。让我们来看一下 MCV，以及一些额外的后面用得上的列：
SELECT null_frac, n_distinct, most_common_vals, most_common_freqs FROM pg_stats
WHERE tablename='tenk1' AND attname='stringu1';
null_frac | 0
n_distinct | 676
most_common_vals | {EJAAAA,BBAAAA,CRAAAA,FCAAAA,FEAAAA,GSAAAA,JOAAAA,MCAAAA,NAAAAA,WGAAAA}
most_common_freqs | {0.00333333,0.003,0.003,0.003,0.003,0.003,0.003,0.003,0.003,0.003}
因为CRAAAA出现在 MCV 列表中，那么选择度只是最常见频度（MCF）列表中的一个对应项：
selectivity = mcf[3]
= 0.003
像之前一样，行数的估计只是和前面一样用tenk1的势乘以选择度：
rows = 10000 * 0.003
= 30
现在看看同样的查询，但是常量不在MCV列表中：
EXPLAIN SELECT * FROM tenk1 WHERE stringu1 = 'xxx';
QUERY PLAN
----------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..483.00 rows=15 width=244)
Filter: (stringu1 = 'xxx'::name)
这是完全不同的一个问题：当值不在MCV列表中时， 如何估计选择度。解决方法是利用该值不在列表中的事实，结合所有MCV出现的频率的知识：
selectivity = (1 - sum(mvf))/(num_distinct - num_mcv)
= (1 - (0.00333333 + 0.003 + 0.003 + 0.003 + 0.003 + 0.003 +
0.003 + 0.003 + 0.003 + 0.003))/(676 - 10)
= 0.0014559
也就是，把所有MCV的频度加起来并从 1 减去，然后除以其他可区分值的个数。这相当于假设不是 MCV 中的列的部分均匀分布在所有其他可区分值上。 需要注意的是，这里没有空值，因此我们不需要担心这些（否则需要从分子中减去空值的部分）。估计的行数然后按照常规计算：
rows = 10000 * 0.0014559
= 15 (rounding off)
之前带有unique1 < 1000的例子是scalarltsel实际工作的过度简化。 现在我们已经看过了使用 MCV 的例子，可以增加一些具体细节了。 这个例子到目前为止是正确的，因为unique1是一个唯一列，它没有 MCV（显然， 没有一个值能比其他值更通用）。对一个非唯一列而言，通常会有直方图和 MCV 列表， 并且直方图不包括由 MCV 表示的那部分列。之所以这样做是因为可以得到更精确的估计。在这种情况下，scalarltsel直接应用条件（如“< 1000”）到 MCV 列表中的每个值，并且把那些条件判断为真的 MCV 的频度加起来。这对表中是 MCV 的那一部分给出了准确的选择度估计。然后以上述同样的方式使用直方图估计表中不是 MCV 的那部分的选择度，并且组合这两个数字来估计总的选择读。例如，考虑：
EXPLAIN SELECT * FROM tenk1 WHERE stringu1 < 'IAAAAA';
QUERY PLAN
------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..483.00 rows=3077 width=244)
Filter: (stringu1 < 'IAAAAA'::name)
我们已看到stringu1的 MCV 信息，这里是它的直方图：
SELECT histogram_bounds FROM pg_stats
WHERE tablename='tenk1' AND attname='stringu1';
histogram_bounds
--------------------------------------------------------------------------------
{AAAAAA,CQAAAA,FRAAAA,IBAAAA,KRAAAA,NFAAAA,PSAAAA,SGAAAA,VAAAAA,XLAAAA,ZZAAAA}
检查 MCV 列表，我们发现前 6 项满足条件stringu1 < 'IAAAAA'，而最后 4 项不满足， 所以 MCV 部分的选择度是：
selectivity = sum(relevant mvfs)
= 0.00333333 + 0.003 + 0.003 + 0.003 + 0.003 + 0.003
= 0.01833333
累加所有的 MCF 也告诉我们由 MCV 表示的群体中的比例是 0.03033333，并且因此由直方图表示的 比例是 0.96966667（同样，没有空值，否则我们在这里必须排除它们）。我们可以看到值IAAAAA差不多落在第三个直方图桶的结尾。通过使用一些关于不同字符频率的相当漂亮的假设，规划器对小于IAAAAA的直方图群体部分得到估计值 0.298387。我们然后组合 MCV 和非 MCV 群体的估计：
selectivity = mcv_selectivity + histogram_selectivity * histogram_fraction
= 0.01833333 + 0.298387 * 0.96966667
= 0.307669
rows = 10000 * 0.307669
= 3077 (rounding off)
在这个特别的例子中，来自 MCV 列表的纠正相当小，因为列分布实际上很平坦（统计显示这些特殊值比其它值更常见的原因大部分是由于抽样误差）。 在更典型的情况下某些值显著地比其它的更常见，这种复杂的处理过程有助于提高准确度，因为那些最常见值的选择度可以被准确地找到。
现在考虑一个WHERE子句中带有多个条件的情况：
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 1000 AND stringu1 = 'xxx';
QUERY PLAN
--------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=23.80..396.91 rows=1 width=244)
Recheck Cond: (unique1 < 1000)
Filter: (stringu1 = 'xxx'::name)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..23.80 rows=1007 width=0)
Index Cond: (unique1 < 1000)
规划器假定这两个条件是独立的，因此子句各自的选择度可以被乘在一起：
selectivity = selectivity(unique1 < 1000) * selectivity(stringu1 = 'xxx')
= 0.100697 * 0.0014559
= 0.0001466
rows = 10000 * 0.0001466
= 1 (rounding off)
需要注意的是，从位图索引扫描中返回的估计行数只反映和索引一起使用的条件； 这一点很重要，因为它会影响后续取堆元组的代价估计。
最后我们将检查一个涉及连接的查询：
EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 50 AND t1.unique2 = t2.unique2;
QUERY PLAN
--------------------------------------------------------------------------------------
Nested Loop (cost=4.64..456.23 rows=50 width=488)
-> Bitmap Heap Scan on tenk1 t1 (cost=4.64..142.17 rows=50 width=244)
Recheck Cond: (unique1 < 50)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..4.63 rows=50 width=0)
Index Cond: (unique1 < 50)
-> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.00..6.27 rows=1 width=244)
Index Cond: (unique2 = t1.unique2)
在tenk1上的限制unique1 < 50在嵌套循环连接之前被计算。它的处理类似之前的那个范围查询例子。但是这次值 50 落在unique1直方图的第一个桶内：
selectivity = (0 + (50 - bucket[1].min)/(bucket[1].max - bucket[1].min))/num_buckets
= (0 + (50 - 0)/(993 - 0))/10
= 0.005035
rows = 10000 * 0.005035
= 50 (rounding off)
连接的限制是t2.unique2 = t1.unique2。操作符是我们熟悉的=，然而选择度函数是从pg_operator的oprjoin列获得的，并且是eqjoinsel。eqjoinsel为tenk2和tenk1查找统计信息：
SELECT tablename, null_frac,n_distinct, most_common_vals FROM pg_stats
WHERE tablename IN ('tenk1', 'tenk2') AND attname='unique2';
tablename | null_frac | n_distinct | most_common_vals
-----------+-----------+------------+------------------
tenk1 | 0 | -1 |
tenk2 | 0 | -1 |
在这种情况中，没有unique2的MCV信息，因为所有值看上去都是唯一的，因此我们可以为关系和它们的空值部分使用一个只依赖可区分值数目的算法：
selectivity = (1 - null_frac1) * (1 - null_frac2) * min(1/num_distinct1, 1/num_distinct2)
= (1 - 0) * (1 - 0) / max(10000, 10000)
= 0.0001
也就是说，从 1 中减去每个表的空值部分，并且除以可区分值的最大数目。连接可能发出的行数的计算是：嵌套循环里的两个输入值的笛卡尔积的势乘以选择度：
rows = (outer_cardinality * inner_cardinality) * selectivity
= (50 * 10000) * 0.0001
= 50
这里有两列的 MCV 列表，eqjoinsel将使用 MCV 列表的直接比较来决定在由 MCV 表示的列群体部分中的连接选择度。群体剩下部分的估计遵循这里展示的相同方法。
需要注意的是，我们把inner_cardinality显示为 10000，也就是未修改的tenk2尺寸。它可能出现于EXPLAIN输出检查，连接行的估计来自 50 * 1，即由 outer 行数乘以由tenk2上每个 inner 索引扫描的估计行数。但是这不是那种情况： 连接关系尺寸的估计在任何特定的连接计划被考虑之前进行。如果一切顺利，那么两种方式估计的连接尺寸将产生 大概同样的答案，但是由于舍入误差和其它因素它们有时差别显著。
如果对更进一步的细节感兴趣，一个表的尺寸（在任何WHERE子句之前）的估计在src/backend/optimizer/util/plancat.c中完成。子句选择度的一般逻辑在src/backend/optimizer/path/clausesel.c中。操作符相关的选择度函数大部分可以在src/backend/utils/adt/selfuncs.c中找到。
####### 多变量统计例子
- 函数依赖
多元相关性可以用一个非常简单的数据集来演示 — 一个有两列的表，它们都包含相同的值：
CREATE TABLE t (a INT, b INT);
INSERT INTO t SELECT i % 100, i % 100 FROM generate_series(1, 10000) s(i);
ANALYZE t;
如第 14.2 节所述，规划人员可以使用从 pg_class获取的页面和行数来确定 t的基数：
SELECT relpages, reltuples FROM pg_class WHERE relname = 't';
relpages | reltuples
----------+-----------
45 | 10000
他的数据分布非常简单；每列中只有100个不同的值，均匀分布。
以下示例显示了在a列上估计WHERE条件的结果：
EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a = 1;
QUERY PLAN
-------------------------------------------------------------------------------
Seq Scan on t (cost=0.00..170.00 rows=100 width=8) (actual rows=100 loops=1)
Filter: (a = 1)
Rows Removed by Filter: 9900
规划器检查条件并确定该子句的选择性为1％。通过比较这个估计值和实际的行数， 我们可以看到估计值非常准确（事实上，是因为表格非常小）。 更改WHERE条件以使用b列，将生成一个完全相同的计划。 但是观察一下，如果我们在两列上应用相同的条件，将它们用 AND结合起来会发生什么：
EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a = 1 AND b = 1;
QUERY PLAN
-----------------------------------------------------------------------------
Seq Scan on t (cost=0.00..195.00 rows=1 width=8) (actual rows=100 loops=1)
Filter: ((a = 1) AND (b = 1))
Rows Removed by Filter: 9900
规划器分别估算每个条件的选择性，达到与上述相同的1％估计值。 然后它假定条件是独立的，因此它乘以它们的选择性，产生最终选择性估计值仅为0.01％。 这是一个明显的低估，因为符合条件（100）的实际行数要高于两个数量级。
通过创建一个指示ANALYZE 计算两列上的函数依赖性多变量统计信息的统计对象，可以解决此问题。
CREATE STATISTICS stts (dependencies) ON a, b FROM t;
ANALYZE t;
EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM t WHERE a = 1 AND b = 1;
QUERY PLAN
-------------------------------------------------------------------------------
Seq Scan on t (cost=0.00..195.00 rows=100 width=8) (actual rows=100 loops=1)
Filter: ((a = 1) AND (b = 1))
Rows Removed by Filter: 9900
- N个不同变量的计数
估计多列集合的基数时会出现类似的问题，例如由GROUP BY 子句生成的组的数量。当GROUP BY列出单个列时， n个不同估计值（作为HashAggregate节点返回的估计行数可见）非常准确：
EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT(*) FROM t GROUP BY a;
QUERY PLAN
-----------------------------------------------------------------------------------------
HashAggregate (cost=195.00..196.00 rows=100 width=12) (actual rows=100 loops=1)
Group Key: a
-> Seq Scan on t (cost=0.00..145.00 rows=10000 width=4) (actual rows=10000 loops=1)
但是，如果没有多变量统计信息，那么在GROUP BY 中包含两列的查询中的组数量估计值将在下面的示例中偏离一个数量级：
EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT(*) FROM t GROUP BY a, b;
QUERY PLAN
--------------------------------------------------------------------------------------------
HashAggregate (cost=220.00..230.00 rows=1000 width=16) (actual rows=100 loops=1)
Group Key: a, b
-> Seq Scan on t (cost=0.00..145.00 rows=10000 width=8) (actual rows=10000 loops=1)
通过重新定义统计对象以包括两列的n个不同值的计数，估计得到了很大改进：
DROP STATISTICS stts;
CREATE STATISTICS stts (dependencies, ndistinct) ON a, b FROM t;
ANALYZE t;
EXPLAIN (ANALYZE, TIMING OFF) SELECT COUNT(*) FROM t GROUP BY a, b;
QUERY PLAN
--------------------------------------------------------------------------------------------
HashAggregate (cost=220.00..221.00 rows=100 width=16) (actual rows=100 loops=1)
Group Key: a, b
-> Seq Scan on t (cost=0.00..145.00 rows=10000 width=8) (actual rows=10000 loops=1)
规划器统计和安全
对表pg_statistic的访问仅限于超级用户， 以便普通用户无法从中了解其他用户的表的内容。 一些选择性估算函数将使用用户提供的操作符（出现在查询中的操作符或相关操作符） 来分析所存储的统计。例如，为了确定存储的最常用值是否适用， 选择性估计器将必须运行适当的= 运算符来将查询中的常量与存储的值进行比较。因此， pg_statistic中的数据可能会传递给用户定义的运算符。 适当制作的操作符可以故意泄漏传递的操作数（例如，通过记录它们或将它们写入不同的表）， 或者通过在错误消息中显示它们的值来意外泄漏它们，无论哪种情况都可能暴露 pg_statistic中的数据给一个不应该能够看到它的用户。
为了防止这种情况，以下内容适用于所有内置的选择性估计函数。在规划查询时， 为了能够使用存储的统计信息，当前用户必须在表或相关列上具有 SELECT权限，或者使用的操作符必须是 LEAKPROOF（更准确地说，是操作符所基于的函数）。 如果不是，那么选择性估计器将表现得好像没有统计数据可用， 并且规划器将继续进行默认或回退假设。
如果用户对表或列没有所需的权限，那么在很多情况下，查询最终会收到权限被拒绝的错误， 在这种情况下，这种机制在实践中是不可见的。但是，如果用户正在从安全屏障视图读取数据， 则规划器可能希望检查用户无法访问的基础表的统计数据。在这种情况下， 操作符应该是防漏的，否则统计数据将不会被使用。没有直接的反馈意见， 除非计划可能是次优的。如果有人怀疑是这种情况， 可以尝试将查询作为更有特权的用户来运行，以查看是否产生了不同的计划。
此限制仅适用于规划器需要对pg_statistic 中的一个或多个值执行用户定义的运算符的情况。因此，无论访问权限如何， 规划器都可以使用通用的统计信息，例如列中空值的比例或列中不同值的数量。
可能对用户定义的操作符进行统计操作的第三方扩展中包含的选择性估计函数， 应遵循相同的安全规则。
2.5 执行器(Executor)
执行器接手规划器/优化器创建的计划，并递归地处理之以抽取所需的行集。这本质上是一种需求拉动的管道机制。每次一个计划节点被调用时，它必须交付一个或多个行，或者报告已经完成了行的交付。
计划节点
- 控制节点(Control Node)：
- 扫描节点(Scan Node)：
- 物化节点(Materialization Node)：
- 连接节点(Join Node)：
参数
影响查询优化器选择计划参数
| 参数名称 | 说明 |
|---|---|
| enable_bitmapscan | 是否选择位图扫描 |
| enable_hashagg | 是否选择hash聚合 |
| enable_hashjoin | 是否选择hash连接 |
| enable_indexscan | 是否选择索引扫描 |
| enable_indexonlyscan | 是否选择 |
| enable_material | |
| enable_mergejoin | 多表连接时，是否选择merge连接 |
| enable_nestloop | 是否选择嵌套连接 |
| enable_parallel_append | |
| enable_seqscan | 是否选择全表扫描 |
| enable_sort | 是否使用明确的排序 |
| enable_tidscan | 是否选择位图扫描 |
| enable_partitionwise_join | |
| enable_partitionwise_aggregate | |
| enable_parallel_hash | |
| enable_partition_pruning |
COST基准值参数
默认情况下，以顺序扫描一个数据块的开销作为基准单位。即基准参数（seq_page_cost）默认值为1.0，其它开销的基准参数参照它来设置。
| 参数名称 | 说明 |
|---|---|
| seq_page_cost | 一次顺序扫描一个数据块页面的开销，默认为1.0 |
| random_page_cost | 随机访问一个数据块页面的开销，默认值为4.0 |
| cpu_tuple_cost | 处理一个数据行的开销，默认值0.01 |
| cpu_index_tuple_cost | 处理一个索引行的开销，默认值0.005 |
| cpu_operator_cost | 执行一个操作符或函数的开销，默认值0.0025 |
| parallel_tuple_cost | |
| parallel_setup_cost | |
| effective_cache_size | 一次索引扫描中可用的磁盘缓冲区的有效大小 |
工具
EXPLAIN工具
explain命令可以用来察看规划器生成的查询计划。
查询计划的结构是一个计划结点的树。EXPLAIN给计划树中每个结点都输出一行，显示基本的结点类型和计划器为该计划结点的执行所做的开销估计。 第一行（最上层的结点）是对该计划的总执行开销的估计。一个上层结点的开销包括它的所有子结点的开销。
####### 命令语法
EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement
where option can be one of:
ANALYZE [ boolean ]
VERBOSE [ boolean ]
COSTS [ boolean ]
SETTINGS [ boolean ]
BUFFERS [ boolean ]
TIMING [ boolean ]
SUMMARY [ boolean ]
FORMAT { TEXT | XML | JSON | YAML }
VERBOSE：显示计划的附加信息，默认为FALSECOSTS：显示每个计划节点的成本和总成本，以及估计行数和每行宽度。默认TRUEBUFFERS：显示关于缓冲区（共享块、本地块、临时块读和写的块数）使用的信息。该参数只能和ANALYZE参数一起使用。上层节点包含其所有子节点使用的块数。- 共享块：包含表和索引中使用的磁盘块
- 本地块：包含临时表和临时索引中使用的磁盘块
- 临时块：包含在排序和物化计划中使用的磁盘块
FORMAT：指定输出格式
####### 示例说明执行计划
09:07:54 [local]:5432 dev@devdb=> EXPLAIN SELECT * FROM tbl_mvcc;
QUERY PLAN
------------------------------------------------------------
Seq Scan on tbl_mvcc (cost=0.00..32.60 rows=2260 width=8)
(1 row)
09:08:12 [local]:5432 dev@devdb=>
09:08:12 [local]:5432 dev@devdb=> EXPLAIN(ANALYZE true,buffers true) SELECT * FROM tbl_mvcc;
QUERY PLAN
------------------------------------------------------------------------------------------------------
Seq Scan on tbl_mvcc (cost=0.00..32.60 rows=2260 width=8) (actual time=0.270..0.271 rows=4 loops=1)
Buffers: shared read=1 dirtied=1
Planning Time: 0.060 ms
Execution Time: 0.389 ms
(4 rows)
09:29:44 [local]:5432 dev@devdb=>
####### 结果说明
-
Seq Scan on tbl_mvcc：表示顺序扫描tbl_mvcc表，顺序扫描即全表扫描
-
cost=0.00..32.60 rows=2260 width=8cost=0.00..32.60：cost后面2个数字，中间由..分割，第一个数字表示启动的成本（即返回第一行需要的cost值）；第二个数字表示返回所有的数据的成本rows=2260：表示返回的记录行数量width=8：表示每行平均宽度，单位字节
-
cost的代价值说明
- 顺序扫描一个数据块，cost值定为1
- 随机扫描一个数据块，cost值定为4
- 处理一个数据行的CPU，cost值为0.01
- 处理一个索引行的CPU，cost值为0.005
- 每个操作符的CPU，cost值为0.0025
-
Buffers：shared read=1：表示共享内存读取块的数量dirtied=1：表示
####### 执行类型
-
全表扫描|顺序扫描(Seq Scan)：将表的所有数据块从头到尾读一遍，然后从数据块中找到符号条件的数据块
-
索引扫描(Index Scan)：在索引中找到数据行的物理位置，然后再到表的数据块中把相应的数据读出来
-
位图扫描(Bitmap Index Scan -> Bitmap Heap Scan)：索引的一种方式，把满足条件的行或块在内存中建一个位图，扫描完索引后，再根据位图到表的数据文件把数据读取出来
-
条件过滤(Filter)：一般时在WHERE上过滤条件
-
嵌套循环(Nestloop Join)：内表被外表驱动，外表返回的每一行数据都要在内表中检索到匹配的行。注意：外表（驱动表）选择小的表，内表（被驱动表）的连接字段要有索引，否则性能会很慢。
-
Hash连接(Hash Join)：优化器用2个表中较小的表，并利用连接键在内存中建立散列表，然后扫描较大的表并探测散列表，找出与散列表匹配的行
-
合并连接（
Merge Join）：如果源数据上有索引或结果已经排好序，在执行排序合并连接时不需要排序，此时合并连接的性能优于Hash Join
auto_explain
auto_explain模块提供了一种方式来自动记录慢速语句的执行计划，而不需要手工运行EXPLAIN。有利于跟踪未被优化的查询。
####### 配置方法
- SQL命令配置
postgres=## LOAD 'auto_explain';
postgres=## SET auto_explain.log_min_duration = 0;
postgres=## SET auto_explain.log_analyze = true;
postgres=## SELECT count(*)
FROM pg_class, pg_index
WHERE oid = indrelid AND indisunique;
- 修改配置文件
## vi $PGDATA/postgresql.conf
session_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '3s'
3. 遗传查询优化器
在所有关系操作符中，最难于处理和优化的是连接。可能的查询计划数目以查询中连接数量的指数增长。对各种各样处理独立连接的连接方法（如PostgreSQL中的嵌套循环、哈希连接、归并连接）和多种关系访问方法的indexes（如PostgreSQL中的 B 树、哈希、GiST 和 GIN）的支持也进一步加重了优化的负担。
通常的PostgreSQL查询优化器会执行一次在可选策略空间上的近似穷举搜索。这个算法最早由 IBM 的系统 R 数据库引入，它能产生接近最优的连接顺序，但是当查询中的连接数增长到很大时，该算法需要大量的时间和内存空间。这使得普通的PostgreSQL查询优化器不适合需要连接大量表的查询。
德国弗莱堡的矿业大学自动控制学院在使用PostgreSQL作为电力网格维护决策支持知识系统的后端时遇到了一些问题。DBMS 需要为知识系统中的推理机器处理大量连接查询。这些查询中的连接数不可能用普通的查询优化器来处理。
遗传算法以一种更有效率的方式为涉及大量连接的查询解决连接顺序问题。
3.1 遗传算法（Genetic Algorithm, GA）
遗传算法 ( GA , Genetic Algorithm ) ,也称进化算法 。 遗传算法是受达尔文的生物进化论的启发,借鉴生物进化过程而提出的一种启发式搜索算法。
3.2 PostgreSQL 中的遗传查询优化（GEQO）
GEQO模块把查询优化问题当做著名的货郎担问题（TSP）来处理。可能的查询计划被编码为整数的串。每一个串表示从查询中一个关系到下一个关系的连接顺序。例如，连接树
/
/ 2
/ 3
4 1
被编码为整数串 '4-1-3-2'，它表示首先连接关系 '4' 和 '1'，然后连接 '3'，最后连接 '2'。这里 1、2、3、4 是PostgreSQL优化器中的关系 ID。
货郎担问题提法：有n个城市，用1，2，…，n表示，城i,j之间的距离为dij，有一个货郎从城1出发到其他城市一次且仅一次，最后回到城市1，怎样选择行走路线使总路程最短？
PostgreSQL中GEQO实现的特点有：
- 一种稳态 GA（在种群中替换适应度最差的个体，而不是整代替换）的使用允许对改进的查询计划快速收敛。这对在合理时间内处理查询是最重要的；
- 边重组杂交的使用特别适合于通过GA为TSP的解决方案保持低丢边率；
- 遗传操作符变异被废弃，这样不需要修补机制来产生合法的TSP旅行。
GEQO模块的一部分是从 D. Whitley 的遗传算法中改编而来。
GEQO模块允许PostgreSQL查询优化器支持通过非穷举搜索高效地处理大量连接的查询。
用GEQO产生可能的计划
GEQO规划处理使用标准的规划器代码来产生用于扫描个体关系的计划。然后使用遗传方法发展连接计划。如上所示，每一个候选连接计划被表示为一个连接基本关系的序列。在初始阶段，GEQO代码简单地随机产生某些可能的连接序列。对于被考虑的每一个连接序列，标准规划器代码被调用来估算使用该序列执行查询的代价（对于连接序列的每一步，所有三种连接策略都被考虑；并且所有初始决定的关系扫描计划都可用。估计的代价是这些可能性中最低的那个。）。具有较低估计代价的连接序列被认为比具有较高代价的“更适合”。遗传算法会丢弃最不适应的候选。然后通过组合更适合的候选的基因来产生新的候选 — 即使从已知代价低的连接序列随机选择片段来创建用于考虑的新序列。这个处理将被重复，直到已经考虑的连接序列的数量达到一个预设值。然后在搜索中任何时候找到的最好的一个将被用来产生最终的计划。
由于在初始种群选择和后续最佳候选的“变异”过程中都采用了随机选择，所以这种处理天生就是非确定性的。要避免被选中计划发生出乎意料的改变，每次 GEQO 算法的运行都会使用当前[geqo_seed](控制 GEQO 使用的随机数生成器的初始值，随机数生成器用于在连接顺序搜索空间中选择随机路径。该值可以从 0 （默认值）到 1。变化该值会改变被探索的连接路径集合，并且可能导致找到一个更好或更差的路径。)参数设置来重启它的随机数生成器。只要geqo_seed以及其他 GEQO 参数保持固定（以及其他规划器输入，如统计信息），对一个给定的查询将产生相同的计划。要试验不同的搜索路径，可以尝试改变geqo_seed。
4. 统计信息的收集
postgres: stats collector进程专门负责收集统计信息。收集的统计信息用于查询优化时的代价估算。表和索引的行数，块数等统计信息记录在系统表pg_class中，其它的统计信息主要在系统表pg_statistic中。
收集统计信息
手工运行analyze命令收集统计信息，结果存储到pg_statistic系统表中。autovacuum默认打开，它自动分析表并收集表的统计信息。
ANALYZE [ ( option [, ...] ) ] [ table_and_columns [, ...] ]
ANALYZE [ VERBOSE ] [ table_and_columns [, ...] ]
where option can be one of:
VERBOSE [ boolean ]
SKIP_LOCKED [ boolean ]
and table_and_columns is:
table_name [ ( column_name [, ...] ) ]
连接池及数据库高速缓存
pgbouncer
PgBouncer作为PostgreSQL数据库的连接池中间件。与其他存在于PostgreSQL的连接池中间件不同，PgBouncer仅作为一个连接池和代理层为PostgreSQL和应用之间提供服务。
连接池方式
数据库连接池在Pgbouncer中包括会话连接池、事务连接池、语句连接池三种方式
- 会话连接池：当客户端连接时，服务器连接将在其保持连接的整个过程中分配给它。当客户端断开连接时，服务器连接将重新放入池中。
- 事务连接池：
- 语句连接池：
数据库共享缓存(shared_buffers)与操作系统缓存(os cache)
Postgresql采用数据库采用实例shared buffers和操作系统cache双缓存(effective_cache_size)的工作模式。
缓存作为数据库的一个核心组件，shared_buffers决定了数据库实例层面的可用内存，而文件系统缓存的大小是effective_cache_size决定的，effective_cache_size不仅是缓存经常访问的数据，它同时帮助优化器确定实际存在多少缓存，指导优化器生成最佳执行计划。
work_mem、maintenance_work_mem和其他本地内存组件都不是shared buffers的一部分
推荐配置
- shared buffers（建议值是25%系统内存）
- effective_cache_size（建议值是50%系统内存）
-- 检查当前配置
show shared_buffers;
select name,setting,unit,current_setting(name) from pg_settings where name ='shared_buffers';
数据流向
pg_prewarm 模块
pg_prewarm模块可以方便的把相关的数据加载到系统的缓存或者是数据库的缓存中。（数据预热）
用法
sf+ pg_prewarm
CREATE OR REPLACE FUNCTION public.pg_prewarm(regclass, mode text DEFAULT 'buffer'::text, fork text DEFAULT 'main'::text, first_block bigint DEFAULT NULL::bigint, last_block bigint DEFAULT NULL::bigint)
RETURNS bigint
LANGUAGE c
PARALLEL SAFE
1 AS '$libdir/pg_prewarm', $function$pg_prewarm$function$
第一个参数是 预热的relation
第二个参数是 要使用的预热方法
第三个参数是 relation fork 被预热
第四个参数是 预热的第一个块号
第五个参数是 预热的最后一个块号
返回值是prewarm块的数量。
示例
-- 将表添加到缓存
select pg_prewarm('textdb','main','buffer',null,null);
查看缓存数据内容
查看缓存内的信息
- bufferid : 缓存id编号
relname：表名
usagecount： 访问级数
isdirty： 脏页
relfork ： 在缓存中的缓存的种类 main表示缓冲关系表
fsm：空闲空间可映射
vm：可视化映射相关文件
relblocknumber： 缓冲块号
-- 查看缓存内的信息
select bufferid,
(select c.relname from pg_class c where pg_relation_filenode(c.oid) = b.relfilenode) relname,
case relforknumber
when 0 then 'main'
when 1 then 'fsm'
when 2 then 'vm'
end relfork,
relblocknumber,
isdirty,
usagecount
from pg_buffercache b
where b.reldatabase in (0,(select oid from pg_database where datname=current_database()))
and b.usagecount is not null;
数据库层（pg_buffercache）
pg_buffercache插件从shared_buffers收集信息并将其放在pg_buffercache对象中，实现实时查看共享缓冲区中的数据的功能。
-- 检查共享缓冲区的内容
SELECT c.relname
, pg_size_pretty(count(*) * 8192) as buffered
, round(100.0 * count(*) / ( SELECT setting FROM pg_settings WHERE name='shared_buffers')::integer,1) AS buffers_percent
, round(100.0 * count(*) * 8192 / pg_relation_size(c.oid),1) AS percent_of_relation
FROM pg_class c
INNER JOIN pg_buffercache b ON b.relfilenode = c.relfilenode
INNER JOIN pg_database d ON (b.reldatabase = d.oid AND d.datname = current_database())
WHERE pg_relation_size(c.oid) > 0
GROUP BY c.oid, c.relname
ORDER BY 3 DESC
LIMIT 10;
操作系统层(cache)
需要安装pgfincore包，它用于加载数据到OS的Cache中和查看操作系统级别缓存的数据内容。
安装插件工具包
# 1. 操作系统层安装软件
git clone git://git.postgresql.org/git/pgfincore.git
cd pgfincore
make clean
make
make install
# 2. 数据库层安装插件
Now connect to PG and run below command
postgres=# CREATE EXTENSION pgfincore;
postgres-# dx
select * from pg_extension;
查看插件中的函数
SELECT proname, prosrc
FROM pg_catalog.pg_namespace n
JOIN pg_catalog.pg_proc p
ON pronamespace = n.oid
WHERE nspname = 'public';
添加数据到cache
select * from pgfadvise_willneed('table_name');
将数据移出内存cache
select * from pgfadvise_dontneed('table_name');
查看OS中Cache中数据
-- 检查操作系统级别的缓冲区
select c.relname,pg_size_pretty(count(*) * 8192) as pg_buffered,
round(100.0 * count(*) /
(select setting
from pg_settings
where name='shared_buffers')::integer,1)
as pgbuffer_percent,
round(100.0*count(*)*8192 / pg_table_size(c.oid),1) as percent_of_relation,
( select round( sum(pages_mem) * 4 /1024,0 )
from pgfincore(c.relname::text) )
as os_cache_MB ,
round(100 * (
select sum(pages_mem)*4096
from pgfincore(c.relname::text) )/ pg_table_size(c.oid),1)
as os_cache_percent_of_relation,
pg_size_pretty(pg_table_size(c.oid)) as rel_size
from pg_class c
inner join pg_buffercache b on b.relfilenode=c.relfilenode
inner join pg_database d on (b.reldatabase=d.oid and d.datname=current_database()
and c.relnamespace=(select oid from pg_namespace where nspname='public'))
group by c.oid,c.relname
order by 3 desc limit 30;
-- 输出结果
relname |pg_buffered|pgbuffer_per|per_of_relation|os_cache_mb|os_cache_per_of_relation|rel_size
---------+-----------+------------+---------------+-----------+------------------------+--------
emp | 4091 MB | 99.9 | 49.3 | 7643 | 92.1 | 8301 MB
- pg_buffered 表示PostgreSQL缓冲缓存中缓冲了多少数据，如：4091MB
- pgbuffer_percent 表示pg_buffered/total_buffer_size *100， 如：99.9
- percent_of_relation 表示pg_buffered/total_relation_size * 100，如：49.3
- os_cache_mb 表示在OS中缓存了多少关系，如：7643
- os_cache_per_of_relation 表示os_cache_mb/total_relation_size * 100，如：92.1
在这里，emp表有8301 MB大小，92%的数据在操作系统缓存中，同时49.3%的数据在共享缓冲区中，也就是大约50%的数据是冗余的。
统计信息
类型
- 性能计数器和系统表：用来描述数据库的负载或者数据库对象使用情况
- 它们由 stats collector 进程来实时收集更新信息
- 计算对磁盘块和单行项中的表和索引的访问次数
- 跟踪每个表中的总行数，以及关于vacuum的信息，并分析每个表的操作
- 记录基于sql语句执行的代价信息
- 它们由 stats collector 进程来实时收集更新信息
- 统计信息直方图：用来描述数据的分布，为优化器生成执行计划提供依据
- 后台进程（autovacuum launcher）会在特定的情况下触发统计信息的更新
- 手动执行analyze table语句触发统计信息更新
系统表和视图
系统表
| 系统表名称 | 说明 |
|---|---|
| pg_aggregate | 聚集函数 |
| pg_am | 索引访问方法 |
| pg_amop | 访问方法操作符 |
| pg_amproc | 访问方法支持过程 |
| pg_attrdef | 字段缺省值 |
| pg_attribute | 表的列（”属性”，”字段”） |
| pg_authid | 认证标识符（角色） |
| pg_auth_members | 认证标识符成员关系 |
| pg_autovacuum | 每个关系一个的自动清理配置参数 |
| pg_cast | 转换（数据类型转换） |
| pg_class | 表，索引，序列，视图（”关系”） |
| pg_constraint | 检查约束，唯一约束，主键约束，外键约束 |
| pg_conversion | 编码转换信息 |
| pg_database | 本集群内的数据库 |
| pg_depend | 数据库对象之间的依赖性 |
| pg_description | 数据库对象的描述或注释 |
| pg_index | 附加的索引信息 |
| pg_inherits | 表继承层次 |
| pg_language | 用于写函数的语言 |
| pg_largeobject | 大对象 |
| pg_listener | 异步通知 |
| pg_namespace | 模式 |
| pg_opclass | 索引访问方法操作符表 |
| pg_operator | 操作符 |
| pg_pltemplate | 过程语言使用的模板数据 |
| pg_proc | 函数和过程 |
| pg_rewrite | 查询重写规则 |
| pg_shdepend | 在共享对象上的依赖性 |
| pg_statistic | 优化器统计 |
| pg_tablespace | 这个数据库集群里面的表空间 |
| pg_trigger | 触发器 |
| pg_type | 数据类型 |
视图
| 视图名称 | 说明 |
|---|---|
| pg_group | 数据库用户的组 |
| pg_indexes | 索引 --根据pg_index、pg_class、pg_namespace、pg_tablespace来查具体索引的schemaname、tablename、indexname、tablespace、indexdef信息 |
| pg_locks | 当前持有的锁 |
| pg_prepared_xacts | 目前准备好的事务 |
| pg_roles | 数据库角色 |
| pg_rules | 规则 |
| pg_settings | 参数设置 |
| pg_shadow | 数据库用户 |
| pg_stats | 规划器统计 |
| pg_tables | 表 |
| pg_user | 数据库用户 |
| pg_views | 视图 |
统计数据库状态信息视图
| 视图名称 | 说明 |
|---|---|
| pg_stat_activity | 查看当前活动会话状态的视图 |
| pg_stat_bgwriter | 只有一行数据，显示集群内后台写的相关情况，记录一些checkpoint ,buffer 的信息 |
| pg_stat_archiver | 数据库归档信息 |
| pg_stat_replication | 记录复制的相关信息，包括复制用的用户名，复制类型，同步状态 等 |
| 对象级别的统计信息 | |
| pg_stat_database | 显示集群内数据库信息的视图 |
| pg_stat_all_tables | 记录当前数据库中所有表的统计信息，包括（toast表） |
| pg_stat_all_indexes | 记录当前数据库中所有的索引的使用情况 |
| pg_stat_sys_indexes | 记录当前数据库中所有系统表的索引的使用情况 |
| pg_stat_user_indexes | 记录当前数据库中所有用户表的索引的使用情况 |
| pg_stat_database_conflicts | 每个数据库一行数据，记录数据库里面冲突信息，记录由于冲突而导致被取消掉是查询语句的次数 |
| pg_stat_sys_tables | 与 pg_stat_all_tables相似，不过只是记录了系统表的统计信息 |
| pg_stat_user_tables | 与 pg_stat_all_tables相似，不过只是记录了用户自己建的表统计信息 |
| pg_stat_xact_all_tables | 与 pg_stat_all_tables相似，记录所有表在当前会话中的统计信息，仅统计当前会话发生在表上的统计统计信息 |
| pg_stat_xact_sys_tables | 与 pg_stat_sys_tables相似，记录系统表表在当前会话中的统计信息，仅统计当前会话发生在表上的统计统计信息 |
| pg_stat_xact_user_tables | 与 pg_stat_user_tables相似，记录系统表表在当前会话中的统计信息，仅统计当前会话发生在表上的统计统计信息 |
| IO 级统计信息 | |
| pg_statio_all_tables | 记录当前数据库中所有表的IO信息，包括（toast表），包括堆栈块的读取数，堆栈块命中的次数，索引块读取数，索引块命中的次数等 |
| pg_statio_sys_tables | 与 pg_statio_all_tables相似,只是记录系统表IO的信息 |
| pg_statio_user_tables | 与 pg_statio_all_tables相似,只是记录用户表IO的信息 |
| pg_statio_all_indexes | 记录当前数据库中所有索引的IO信息，其中数值idx_blks_read 跟 idx_blks_hit 与 pg_statio_all_tables 中索引的读取跟命中是一致的 |
| pg_statio_sys_indexes | 与 pg_statio_all_indexes 类似，只是记录系统表的索引的IO信息 |
| pg_statio_user_indexes | 与 pg_statio_user_indexes 类似，只是记录用户表的索引的IO信息 |
| pg_statio_all_sequences | 记录当前库中所有序列的读取数跟命中数 |
| pg_statio_sys_sequences | 与 pg_statio_all_sequences 类似，只是展示系统建立序列的IO信息而已 |
| pg_statio_user_sequences | 与 pg_statio_all_sequences 类似，只是展示用户建立序列的IO信息而已 |
| 对数据库内函数的调用次数 | |
| pg_stat_user_functions | 记录用户创建的函数的统计信息,只有开启了 track_functions = all ，才会有数据 |
| pg_stat_xact_user_functions | 显示当前会话中所使用的函数的统计信息 |
直方图
直方图描述了目标列的数据分布情况。
数据库负载类统计信息
库级别信息摘要
pg_stat_database 用来描述描述"库"级别的摘要信息，包括库名，当前库事务提交次数，回滚次数，读写次数，死锁等等信息
select
datid,
datname,
numbackends,
xact_commit,
xact_rollback,
blks_read,
blks_hit,
tup_returned,
tup_inserted,
tup_fetched,
tup_updated,
deadlocks,
stats_reset
from pg_catalog.pg_stat_database
where datname = 'postgres';
表级别的信息摘要
由pg_stat_user_tables来描述某个具体的表中的信息，包括增删查改的次数，数据行等摘要信息。
这些信息可以衡量一个表的冷热程度，活跃性，以及体量以及一些analyze时间相关的信息
select
*
from pg_catalog.pg_stat_user_tables
where schemaname = 'myschema' and relname = 'tb1'
;
-- pg_class 来描述表的物理存储信息，包括数据行数，数据页的个数
select * from pg_class where relname = 'tb1';
数据分布统计信息
pg_stats 表
pg_stats用来描述一个表中所有的字段的数据分布信息，为执行计划决策提供依据。
select * from pg_stats where tablename = 'tb1';
pg_stat系统表的详细描述
| 名字 | 类型 | 引用 | 描述 |
|---|---|---|---|
| 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[] | 该字段中值的不同非空元素值的统计直方图，跟着不同非空元素的平均值。（标量类型为空。） |
pg_statistic表
pg_statistic是基于pg_stats的视图，以更加友好以及可读的方式展现统计信息，普通用户可以访问
select * from pg_statistic ;
更新统计信息
手动更新(analyze)
analyze table_name;
select * from pg_catalog.pt_stat_user_tables
where schemaname = 'myschema' and relname = 'tb1';
自动更新(vacuum)
开启自动更新
-- 默认打开
select current_setting('autovacuum');
自动更新统计信息的阈值
- autovacuum_vacuum_threshold
- autovacuum_vacuum_scale_factor
自动更新触发条件：
表上增删改的行数 >= autovacuum_vacuum_scale_factor* reltuples(表上记录数) + autovacuum_vacuum_threshold
select current_setting('autovacuum_vacuum_threshold');
select current_setting('autovacuum_vacuum_scale_factor');
采样范围
-
default_statistics_target参数，该参数默认值是100。范围是1~10000
-
采样容量：采用 300*default_statistics_target=30000 作为采样的样本默认容量
查看最后一个更新后的统计信息
pg_stat_all_tables表存储了所有表的最后一次更新历史信息（last_analyza），以及最后一次更新之后数据发生的变化情况(n_mod_since_analyze)
select * from pg_stat_all_tables where relname = 'tb1';
对比analyze和vacuum
autovcuum lancher进程中会定期的执行autovcuum ，Analyze是autovcuum其中的一步，会主动被触发。Vacuum是Analyze的超集，Vacuum包含一系列的清理、表的重建、以及表的统计信息更新，换句话说就是，vacuum包含但不限于analyze table来更新统计信息。
IO分析
系统IO负载
数据库IO负载
数据文件IO
WAL IO
对象IO负载
表IO
索引IO
序列IO
存储过程IO
等待事件分析
总体
-- 查询等待时间
with t_wait as
(select a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a,transactionid,b.query,b.xact_start,b.query_start,b.usename,b.datname from pg_locks a,pg_stat_activity b where a.pid=b.pid and not a.granted),
t_run as
(select a.mode,a.locktype,a.database,a.relation,a.page,a.tuple,a.classid,a.objid,a.objsubid,a.pid,a.virtualtransaction,a.virtualxid,a,transactionid,b.query, b.xact_start,b.query_start,b.usename,b.datname from pg_locks a,pg_stat_activity b where a.pid=b.pid and a.granted)
select r.locktype,r.mode,r.usename r_user,r.datname r_db,r.relation::regclass,r.pid r_pid,r.xact_start r_xact_start,r.query_start r_query_start,r.query r_query,
w.usename w_user,w.datname w_db,w.pid w_pid,w.xact_start w_xact_start,w.query_start w_query_start,w.query w_query
from t_wait w,t_run r where
r.locktype is not distinct from w.locktype and
r.database is not distinct from w.database and
r.relation is not distinct from w.relation and
r.page is not distinct from w.page and
r.tuple is not distinct from w.tuple and
r.classid is not distinct from w.classid and
r.objid is not distinct from w.objid and
r.objsubid is not distinct from w.objsubid
order by r.xact_start;
IO类
IPC类
LOCK 类
LWLock类
client类
Extension 类
热块等待
会话分析
活跃会话
-- 查看当前数据库连接数
select count(*), usename from pg_stat_activity group by usename;
登陆频率
会话客户端与用户分布
并发
锁分析
锁冲突和等待
-- 查询表是否存在锁
select a.locktype,a.database,a.pid,a.mode,a.relation,b.relname
from pg_locks a
join pg_class b on a.relation = b.oid
where upper(b.relname) = 'TABLE_NAME';
-- 查询表锁及操作的SQL语句
select a.locktype,a.database,a.pid,a.mode,a.relation,b.relname,c.usename,c.current_query,c.query_start,c.client_addr
from pg_locks a
join pg_class b on a.relation = b.oid
join pg_stat_activity c on a.pid = c.procpid
where upper(b.relname) = 'TABLE_NAME';
-- 查询所有数据库,及其所占空间大小
select pg_database.datname, pg_database_size(pg_database.datname) AS size from pg_database;
-- 查询存在锁的数据表
select a.locktype,a.database,a.pid,a.mode,a.relation,b.relname -- ,sa.*
from pg_locks a
join pg_class b on a.relation = b.oid
inner join pg_stat_activity sa on a.pid=sa.procpid;
-- 查询某个表内,状态为lock的锁及关联的查询语句
select a.locktype,a.database,a.pid,a.mode,a.relation,b.relname -- ,sa.*
from pg_locks a
join pg_class b on a.relation = b.oid
inner join pg_stat_activity sa on a.pid=sa.procpid
where a.database=382790774 and sa.waiting_reason='lock'
order by sa.query_start;
死锁
长事务
SQL分析
使用pg_stat_statements插件
top SQL
IO高SQL
-- 单次IO
select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time)/calls desc limit 20;
-- 累计
select userid::regrole, dbid, query from pg_stat_statements order by (blk_read_time+blk_write_time) desc limit 20;
执行次数高SQL
执行时间长SQL
-- 平均时间
select userid::regrole, dbid, query from pg_stat_statements order by mean_time desc limit 20;
-- 累计
select userid::regrole, dbid, query from pg_stat_statements order by total_time desc limit 20;
-- 时快时慢
select userid::regrole, dbid, query from pg_stat_statements order by stddev_time desc limit 20;
-- 执行时间
select round((100 * total_exec_time / sum(total_exec_time) over ())::numeric , 2 ) percent ,
round(total_exec_time::numeric , 2 ) as etime,
calls ,
round(mean_exec_time::numeric , 2 ) as m_etime,
substring(query,1,40)
from pg_stat_statements
order by 1 desc
limit 10 ;
扫描行数高SQL
select * from pg_stat_user_tables where n_live_tup > 100000 and seq_scan > 0 order by seq_tup_read desc limit 20;
seq read 为全表扫描
内存占用高SQL
-- shared memory
select userid::regrole, dbid, query from pg_stat_statements order by (shared_blks_hit+shared_blks_dirtied) desc limit 20;
-- temp memory
select userid::regrole, dbid, query from pg_stat_statements order by temp_blks_written desc limit 20;
全表扫描的SQL
--seq_scan sequential scans发生的次数
--seq_tup_read seq_scan过程中读取的tuples总数
select schemaname, relname, seq_scan, seq_tup_read,
seq_tup_read/seq_scan as avg , idx_scan
from pg_stat_user_tables
where seq_scan > 0
order by seq_tup_read desc limit 5 ;
慢SQL
SQL执行计划
代价估算
select name,setting from pg_settings ps
where ps.name in ('seq_page_cost','random_page_cost','cpu_tuple_cost','cpu_index_tuple_cost','cpu_operator_cost');
- seq_page_cost:执行计划中一次顺序访问一个数据块页面的开销，默认1.0
random_page_cost:随机访问一个数据块页面的开销，默认4.0
cpu_tuple_cost:执行计划中，处理一条数据行的开销，默认0.01
cpu_idex_tuple_cost:处理一条索引行的开销，默认0.005
cpu_operator_cost:执行一个操作符或函数的开销，默认0.0025
effective_cache_size:执行计划中在一次索引扫描中可用的磁盘缓冲区的有效大小。默认128MB
计算代价
total_cost = seq_page_cost * relpages + cpu_tuple_cost * reltuples
数据扫描方式
https://www.cnblogs.com/wy123/p/13388228.html#top
Seq Scan （全表顺序扫描）
Index Only Scan ()
按索引顺序扫描，根据VM文件的BIT位判断是否需要回表扫描。
Index Scan (按索引顺序扫描，并回表)
Bitmap Index Scan+Bitmap Heap Scan
按索引取得的BLOCKID排序，然后根据BLOCKID顺序回表扫描，然后再根据条件过滤掉不符合条件的记录。
这种扫描方法，主要解决了离散数据(索引字段的逻辑顺序与记录的实际存储顺序非常离散的情况)，需要大量离散回表扫描的情况。
Hash Join
Nested Loop (嵌套循环。其中一个表扫描一次，另一个表则循环多次)
Merge Join ()
Merge Join，需要两个JOIN的表的KEY都是先排好顺序的，如果有索引没有排序过程。Merge Join两个表都只扫描一次。
explain 分析执行计划
用法
postgres=# 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 ]
SUMMARY [ boolean ]
FORMAT { TEXT | XML | JSON | YAML }
- ANALYZE，执行命令并且显示实际的运行时间和其他统计信息。这个参数默认被设置为FALSE。
- VERBOSE，显示关于计划的额外信息。特别是：计划树中每个结点的输出列列表、模式限定的表和函数名、总是把表达式中的变量标上它们的范围表别名，以及总是打印统计信息被显示的每个触发器的名称。这个参数默认被设置为FALSE。
- COSTS，包括每一个计划结点的估计启动和总代价，以及估计的行数和每行的宽度。这个参数默认被设置为TRUE。
- BUFFERS，包括缓冲区使用的信息。特别是：共享块命中、读取、标记为脏和写入的次数、本地块命中、读取、标记为脏和写入的次数、以及临时块读取和写入的次数。只有当ANALYZE也被启用时，这个参数才能使用。它的默认被设置为FALSE。
- TIMING，在输出中包括实际启动时间以及在每个结点中花掉的时间。只有当ANALYZE也被启用时，这个参数才能使用。它的默认被设置为TRUE。
- SUMMARY，在查询计划之后包含摘要信息（例如，总计的时间信息）。当使用ANALYZE 时默认包含摘要信息，但默认情况下不包含摘要信息，但可以使用此选项启用摘要信息。 使用EXPLAIN EXECUTE中的计划时间包括从缓存中获取计划所需的时间 以及重新计划所需的时间（如有必要）。
- FORMAT，指定输出格式，可以是 TEXT、XML、JSON 或者 YAML。非文本输出包含和文本输出格式相同的信息，但是更容易被程序解析。这个参数默认被设置为TEXT。
- statement，你想查看其执行计划的任何SELECT、INSERT、UPDATE、DELETE、VALUES、EXECUTE、DECLARE、CREATE TABLE AS或者CREATE MATERIALIZED VIEW AS语句。
字段输出说明
explain
SELECT tu.user_id,
tu.user_first_name::text || tu.user_last_name::text AS user_name,
tr.role_id,
tr.role_name
FROM t_base_user tu,
t_base_role tr,
t_base_user_role tur
WHERE 1 = 1
AND tu.user_id = tur.user_id
AND tr.role_id = tur.role_id
AND tu.user_status = '1'
AND tr.role_status = '1'
ORDER BY tr.role_id,
tu.user_id;
- cost=0.00..22.32，0.00代表启动成本，22.32代表返回所有数据的成本。
- rows=1032：表示返回多少行。
- width=56，表示每行平均宽度。
- actual time=0.060..1.167，实际花费的时间。
- loops=1，循环的次数
- Output，输出的字段名
- Buffers，缓冲命中数
- shared read，代表数据来自disk(磁盘)而并非cache(缓存)，当再次执行sql，会发现变成shared hit，说明数据已经在cache中
- Planning Time，生成执行计划的时间
- Execution Time，执行执行计划的时间
| 关键字 | 说明 | 示例 |
|---|---|---|
查看执行计划
--在不需要真正执行sql时，需把analyze去掉
explain analyze select ... ;
--在不需要真正执行sql时，需把analyze去掉
explain (analyze,verbose,buffers) select ... ;