微信群：数据库Hacker, 已超200人，现在无法通过扫描直接加入。需要入群的朋友，请直接微信联系我(个人微信：_iihero)，标上您的全名_数据库Hacker作为备注即可。
16.1、示例(Demo)数据库
(在我的vm：sean-rh1.openstack.eu-n1-1.cloud 上进行实操 version: psql (PostgreSQL) 14.5)
本书前几部分中的示例都是基于只有少量行的简单表。本部分和后续部分处理查询执行，这在这方面要求更高:我们需要具有更多行数的相关表。我没有为每个例子发明一个新的数据集，而是使用了一个现有的演示数据库，它演示了[俄罗斯的航空客运流量](Demonstration Database : Postgres Professional[1])。我们将使用2017年8月15日创建的更大的一个。要安装此版本，必须从归档中[2]提取包含数据库副本的文件，并在psql中运行该文件。
在开发这个演示数据库时，我们试图使它的模式足够简单，不需要额外的解释就可以理解;同时，我们希望它足够复杂，以便能够编写有意义的查询。数据库中充满了真实的数据，这使得示例更加全面，使用起来应该很有趣。
在这里，我只简单介绍一下主要的数据库对象;如果您想回顾整个模式，可以查看脚注中引用的完整描述。
主要实体是一个预订(映射到预订表)。一次预订可以包括几个乘客，每个人都有一个单独的电子票(票)。旅客不构成独立的实体;为了实验的目的，我们假设所有的乘客都是独一无二的。
每张机票包括一个或多个航班段(映射到ticket_flights表)。在两种情况下，一张机票可以有多个航段:要么是往返机票，要么是联程机票。尽管在模式中没有相应的约束，但是预订中的所有机票都假设具有相同的航班段。
每个航班从一个机场飞往另一个机场。相同航班号的航班，起飞地点和目的地相同，但起飞日期不同。
航线视图基于航班表;它显示不依赖于特定航班日期的路线信息。
在办理登机手续时，每个乘客都会得到一张带有座位号的登机牌(boarding_pass)。旅客只有将该航班包含在机票中，才能办理登机手续。飞行座位的组合必须是唯一的，所以不可能为同一个座位颁发两个登机牌。
飞机上的座位数量和座位在不同舱位之间的分配取决于执行飞行的飞机的特定型号。假设每个飞机型号只能有一种舱室配置。
一些表有代理主键，而另一些表使用自然主键(其中一些是复合主键)。它仅仅是为了演示目的而做的，绝不是一个可以遵循的示例。
演示数据库可以被认为是一个真实系统的转储:它包含过去某个特定时间所获得的数据的快照。要显示这个时间，可以调用booking .now()函数。在实际中需要now()函数的演示查询中使用此函数。
机场、城市和飞机型号的名称存储在airports_data和aircrafts_data表中;它们以英语和俄语两种语言提供。为了构建本章的示例，我通常会查询实体关系图中显示的机场和飞机视图;这些视图根据预订选择以语言输出。Lang参数值。不过，一些基表的名称仍然可以出现在查询计划中。
16.2、简单查询协议
客户机-服务器协议[3]的一个简单版本支持查询执行: 它将查询的文本发送到服务器，并在响应中获得完整的执行结果，不管它包含多少行发送到服务器的查询要经过几个阶段: 解析、转换、计划，然后执行。
Parsing(解析)
首先，PG必须要将查询文本进行解析，以懂得将要执行的内容。
Lexical and syntactic analyisis: (词法和语法分析)。Lexer(词法分析程序)会将查询文本拆分成多个词位(Lexemes) (如关键词，字符串字面量string literals, 数值字面量 numeric literals)，而解析器会根据SQL语法对这些词位进行验证。PostgreSQL会借用标准的解析工具，比如：Flex和Bison对它们进行解析处理。
解析过的查询在后端的内存里会表现为一个抽象的语法树(syntax tree)。
例如，我们看下下边的这个查询：
SELECT schemaname, tablename
FROM pg_tables
WHERE tableowner = 'postgres'
ORDER BY tablename;
Lexer程序会分离出5个关键字、5个标识符、一个字符串面值、三个字母词位（逗号、等号、分号）。解析器使用这些词位来构建整颗解析树，在后边会以非常简单的形式展示出来。三个节点旁边的题注指定了各部分的查询：
一个更模糊的缩写RTE表示的是Range Table Entry。PostgreSQL源码使用术语范围表来表示表、子查询、连接结果，换句话说，指的是可以被SQL操作符处理的任何行的集合。
Semantic analysis(语义分析)
语义分析的目的是数据库是否包含查询需要用到的表名、以及其它对象的名字，以及用户是否有权限访问这些对象。语义分析需要的所有对象都存储在系统编目(catalog)当中。
接收到解析树之后，语义分析器会进一步重构，这个过程包括添加对特定的数据库对象、数据类型以及其它信息的引用。
如果你启用了参数：debug_print_parse，你就能在服务器日志里头看到完整的解析树，虽然没有多大实践意义。
Transformation(转换)
在下一阶段，查询将被转换（或重写)。
PostgreSQL内核使用转换有几个目的。其中一个目的 是在解析树中将视图的名字替换成子树中与这个视图基本查询对应的名字。
另一个场景就是使用转换用于底层的安全实现[4]。
V.14中递归查询的SEARCH和CYCLE语句同样在这个阶段进行了转换。
上这的例子中，pg_tables是一个视图；如果我们将定义放在查询文本中，它看起来就是像是这样：
SELECT schemaname, tablename
FROM (
-- pg_tables
SELECT n.nspname AS schemaname,
c.relname AS tablename,
pg_get_userbyid(c.relowner) AS tableowner,
...
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
WHERE c.relkind = ANY (ARRAY['r'::char, 'p'::char])
)
WHERE tableowner = 'postgres'
ORDER BY tablename;
但是服务器并不这个查询文本；所有的操作都在解析树上执行。这个示例显示了转换树的一个简化版本。(通过启用参数debug_print_rewritten，你可以在服务器日志中看到它的完整版本)。
解析树能够反映查询的语法结构，但是它分辨不出各个操作的执行顺序。
PostgreSQL同样支持对转换进行定制，用户可以通过“重写规则系统[5]”来进行转换定制。
规则系统支持被宣布为Postgres发展[6]的主要目标之一;当规则第一次实施时，它仍然是一个学术项目，但从那以后它们被重新设计了很多次。规则系统是一种非常强大的机制，但是很难理解和调试。甚至有人提议从PostgreSQL中完全删除这些规则，但这个想法并没有得到一致的支持。在大多数情况下，使用触发器比使用规则更安全、更容易。
Planning
SQL是一种声明式语言：查询指定要取什么数据，但是不会指定如何去取数据。
任何查询都会有若干个执行路径。解析树中的每个操作可以有几种不同的路径完成：例如，可以通过读取整个表（也可以过滤掉冗余的结果）来获取结果，或者通过索引扫描来找到需要的行。数据集总是进行配对连接，这样，在连接的顺序上就有很多种选择。另外，有很多种不同的连接算法：例如，执行器可以扫描第一个数据集，然后在其它数据集中搜索匹配的数据行，或者两个数据集首先进行排序，然后再合并到一起。对每种算法，我们都可以找到优于对方的使用场景。
优化和非优化计划的执行时间也会因为数量级的不同而不同，因为优化解析后的查询的计划器是系统中最复杂的组件。
计划树。执行计划也可以用一颗树来表示，但是它的节点处理的是数据上的物理操作，而不是逻辑操作。
您如果喜欢浏览整个计划树，您可以将它输出到服务器日志文件里，通过开启参数：debug_print_plan。在实际使用当中，通过EXPLAIN命令就足够了。
下面的例子高亮显示了树中的主节点。它实际上也是EXPLAIN命令显示的输出。
目前，我们可以专注于下边两点：
该树只包含三个查询表中的两个:计划器发现其中一个表不需要检索结果，并将其从计划树中删除。 对于树的每个节点，计划器提供估计的成本和预计要处理的行数。
EXPLAIN SELECT schemaname, tablename
FROM pg_tables
WHERE tableowner = 'postgres'
ORDER BY tablename;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Sort (cost=21.03..21.04 rows=1 width=128)
Sort Key: c.relname
-> Nested Loop Left Join (cost=0.00..21.02 rows=1 width=128)
Join Filter: (n.oid = c.relnamespace)
-> Seq Scan on pg_class c (cost=0.00..19.93 rows=1 width=72)
Filter: ((relkind = ANY ('{r,p}'::"char"[])) AND (pg_get_userbyid(relowner) = 'postgres'::name))
-> Seq Scan on pg_namespace n (cost=0.00..1.04 rows=4 width=68)
(7 rows)
查询计划中显示的Seq Scan节点对应于读取表，而Nested Loop节点表示连接操作。
计划搜索. PG使用基于成本的优化器；它审查潜在的计划并估计执行计划所需的资源(例如I/O操作或CPU周期)。这种估计被规范化为数值，称为计划成本。在所有考虑的方案中，选择成本最低的方案。
问题是潜在可用计划的数量随着连接表的数量呈指数级增长，因此不可能考虑所有计划——即使是相对简单的查询。通常使用动态计划算法结合一些启发式方法来缩小搜索范围。它允许计划器在可接受的时间内为具有大量表的查询找到数学上精确的解决方案。
精确的解决方案并不能保证所选择的计划确实是最优的，因为计划者使用简化的数学模型，并且可能缺乏可靠的输入数据。
管理连接的顺序 查询的结构可以在一定程度上限制搜索范围(冒着错过最佳计划的风险)。
常用表表达式(CTE) 和主查询可以分别优化;为了保证这种行为，您可以指定MATERIALIZED子句。 在非sql函数中运行的子查询总是单独优化的。(SQL函数有时可以内联到主查询中。) 如果您设置了join_collapse_limit参数并在查询中使用显式连接子句，则某些连接的顺序将由查询语法结构定义; from_collapse_limit参数对子查询具有相同的效果。
后一点可能需要解释一下。让我们看一下没有为FROM子句中列出的表指定任何显式连接的查询。
SELECT ...
FROM a, b, c, d, e
WHERE ...
在这里，计划器必须考虑所有可能的连接对。该查询由解析树的以下部分表示(如图所示)。
在下一个例子中，JOIN会由JOIN子句定义一个特定的结构 ：
SELECT ...
FROM a, b JOIN c ON ..., d, e
WHERE ...
解析树反映了这种结构 ：
计划器通常会将连接树扁平化，使其看起来与第一个示例中的树类似。该算法递归遍历树，并用其元素的平面列表替换每个JOINXPR节点。
但是，只有当生成的平面列表没有更多时，才会执行这种折叠，而不是join_collapse_limit元素。在本例中，如果join_collapse_limit值小于5，则节点不会被折叠。
对于计划器而言，它有下面几种含义：
表B必须 与表C进行连接操作（或者反过来，表C必须 与表B进行连接操作；连接的顺序并不受限制 ） 表A、D、E以及B和C连接的结果也可以以任何顺序进行连接。
如果将join_collapse_limit参数设置为1，则显式定义的JOIN子句的顺序则被保留。
对于FULL Outer Join操作，不管参数join_collapse_limit设置成何值，它们永远不会发生折叠。（PAGE 298）
参数from_collapse_limit控制着子查询 是否以类似的方式扁平化。尽管子查询不像JOIN操作那样，在解析树的层次时还是非常相似的。
下边是一个简单查询 ：
SELECT ...
FROM a,
(
SELECT ... FROM b, c WHERE ...
) bc,
d, e
WHERE ...
它对应的连接树显示如下图。唯一的不同是这颗树包含了FROMEXPR节点，而不是JOINEXPR节点。
遗传查询优化
一旦扁平化了，这颗树在同一层将会有很多元素。要么是表，要么是连接结果，这些都需要单进行优化。计划时间将指数级的依赖于参与连接的数据集的个数，因此，它的增长可能会超出所有合理的限制。
如果那个geqo参数被启用，同一级的元素个数超过了geqo_threshold的值[7]，计划器将使用遗传算法对查询进行优化。这个算法比动态计划算法要快，但是它不能保证发现的计划会被优化。因此经验法则是通过减少需要优化的元素数量来避免使用遗传算法。
遗传算法有几个可配置的参数[8]，但我不打算在这里介绍它们。
