PostgreSQL 优化器入门

课程亮点

• 数据库内核专家通俗解读 PgSQL 优化器
• 无需阅读分析 PgSQL 源码即可快速掌握
• 优化器经典实现/数据库从业人员核心技能

专家推荐

“这门课从 SQL 示例入手，深入浅出地介绍了 PostgreSQL 优化器技术内幕，让读者能够快速熟悉 PostgreSQL 优化器，对 SQL 优化有非常好的指导作用，值得推荐！”

——谭峰，网名 francs，《PostgreSQL 实战》作者之一，《PostgreSQL 9 Administration Cookbook（第2版）》译者之一

“我相信这一课程能够为对数据库优化器有兴趣却又对其复杂性望而却步的读者指点迷津。”

——林文，Pivotal 资深开发工程师

“国内研究数据库优化器这一领域的人很少，希望本课程能带领广大读者进入这个广阔而精彩的世界。”

——李茂增，华为高斯数据库 SQL 优化专家

“本课程内容用诙谐幽默的语言，将晦涩难懂的数据库查询优化器娓娓道来，是 PostgreSQL 及其他数据库从业者值得一读的好书！”

——张文升，PostgreSQL 社区核心成员，《PostgreSQL 实战》作者

课程背景

作为数据库的从业者，如果对优化器不够了解，便如同猛虎没有了利爪，苍鹰没有了翅膀，在使用数据库的过程中往往心有余而力不足。PostgreSQL 是世界上最先进的开源关系数据库，而 PgSQL 优化器被广泛认为教科书级实现。

本课程从数据库一线开发人员的角度出发，通过实打实的案例，结合外在的系统表信息、参数信息、执行计划信息反向把 PgSQL 查询优化器的原理深入浅出、透彻地讲解明白。

作者介绍

张树杰，Pivotal 资深开发工程师、数据库内核专家。目前从事 Greenplum 数据库的内核开发工作。拥有超过 13 年的 IT 从业经验，多年从事国产数据库内核开发工作，对数据库内核各个方面均有涉猎，近些年尤其专注于研究对分布式数据库的查询优化、查询执行的改进工作。著有《PostgreSQL 技术内幕：查询优化深度探索》一书。

课程大纲

适宜人群

• 数据库开发从业人员与学生
• 数据库优化器研究爱好者
• 数据库内核研发人员

课程内容

开篇词：翻过数据库优化器这座山峰

大家好，我是张树杰，是一名数据库内核开发者。我在 2018 年 6 月出版了《PostgreSQL 技术内幕：查询优化深度探索》一书，这本书对 PostgreSQL 优化器的源代码进行了详尽的分析，但也有一些朋友向我抱怨：“你只顾自己源码分析得 High，考虑过我们的感受吗？”是的，除了 PostgreSQL 的内核开发者，广阔天地间还有更多 PostgreSQL 的使用者以及其他数据库使用者。如果我们切换一个角度，从使用者的角度出发，是否能够把 PostgreSQL 的优化器解释清楚呢？于是我写了这个课程，相信跟随这个课程，大家可以翻过数据库优化器这座山峰。

课程背景

PostgreSQL 号称世界上最先进的开源关系数据库，它的优化器虽然比不上商业数据库的优化器那样复杂，但对于大部分用户来说，已经比较晦涩难懂。如果搞一个投票来评选数据库中最难以理解的模块，那么非优化器莫属。在使用 PostgreSQL 数据库的过程中，你可能会遇到下面这些问题：

• 在你遇到一个比较糟糕的执行计划时，你是否有能力对其进行改造？
• 当你遭遇一个莫名的慢查询时，你是否能够通过优化实现方法提升性能？
• 当你创建的索引不为优化器所用时，你是否清楚地知道优化器的选择习性？
• 你是否想通过等价改写一个 SQL 语句来改变执行计划，那等价改写 SQL 语句是否隐藏着某些规则？

优化器是数据库的大脑。作为数据库的从业者，你是否想知道数据库的大脑在思考些什么？反之，如果对优化器不够了解，便如同猛虎没有了利爪、苍鹰没有了翅膀，在使用数据库的过程中往往心有余而力不足。因此今天我们明知山有虎，偏向虎山行，拿出愚公移山的精神，把优化器的知识消化掉。

针对数据库从业人员的不同，我想对优化器的理解大致可以分成以下 3 个层次。

• 层次一：粗浅了解，比如知道优化器分为逻辑优化和物理优化，了解一些逻辑优化的方法，知道执行计划的来源，能看懂优化器产生的执行计划。
• 层次二：详细了解，在粗浅了解的基础上，能够根据自己对优化器的了解，调整出优化器“喜爱”的 SQL 语句，并且对于产生的执行计划的优劣一目了然，知其然更知其所以然。
• 层次三：深度了解，需要对优化器的每个细节有清楚的认知，在我们写出一个 SQL 语句之后，可以庖丁解牛式地在脑海中浮现出语句在优化器中的优化过程，清楚地知道每个细节的实现过程。

要想达到第一个层次只需要阅读一些基础理论即可，这种了解对于实际应用的意义不大；而要想达到第三个层次则需要细致地解读 PostgreSQL 优化器的源代码，这个过程又过于“艰辛”。因此，本课程的目标是使大家达到第二个层次：不分析数据库内核的源代码，从数据库使用者的角度出发，结合外在的系统表信息、参数信息、执行计划信息反向把 PostgreSQL 查询优化器的原理讲清楚。

课程框架

本课程内容划分为 5 大部分，共计 25 篇，覆盖了 PostgreSQL 优化器的所有重要知识点。我们通过介绍各种系统表信息、参数信息、执行计划信息，从而引出这些信息背后的优化器理论。

导读部分

万丈高楼平地起，这部分内容主要介绍了查询优化的一些基本概念。通过小明、大明和牛二哥对话的方式，将查询优化器的基础理论、基本流程、优化规则融入其中。对优化器不甚了解的同学能够快速进入第一个层次，从而为后面的学习打好基础。

第一部分（第 01 ~ 03 课）：准备工作

工欲善其事、必先利其器。要想知道优化器怎么优化，就需要知道在优化之前，我们给优化器提供了什么。于是，第 01 课通过一个 SQL 示例来分析这个 SQL 语句的执行流程，从而能让读者清楚地知道 SQL 语句的执行过程。另外，查看 SQL 语句的执行计划是数据库从业人员必备的技能之一，我们不但对执行计划的查看进行了说明，还对执行计划背后隐藏的理论进行了说明。这些在第 02 课和第 03 课进行了说明，有了这些知识，就可以很方便地对优化器进行解读了。

第二部分（第 04 ~ 11 课）：逻辑优化部分

逻辑优化也叫基于规则的优化，它主要优化的方式是检查查询树。如果查询树满足既定的优化规则，那么就按照规则对查询树进行改造。PostgreSQL 的优化规则虽然比较多，但是比较重要的有以下规则：子查询提升、表达式预处理、外连接消除、谓词下推、连接顺序交换和等价类推理等。我们在这一部分对这些规则进行了统一的说明。

第三部分（第 12 ~ 18 课）：物理优化部分

物理优化中最重要的就是代价计算的部分。为了更好地加深理解代价，我们先是解读了统计信息的内容，根据统计信息可以计算查询数据的选择率，而统计信息和选择率是代价计算的基石。有了这些信息之后，我们尝试对扫描路径、连接路径、Non-SPJ 路径进行代价计算，这样就能让读者了解代价计算的具体过程了。在代价计算之后，我们对路径的搜索算法——动态规划方法和遗传算法进行了说明。

第四部分（第 19 ~ 22 课）：查询执行的部分

执行计划在生成之后到底是如何执行的？这部分我们列举了一部分执行算子的执行过程中的关键点，这些关键点或者是优化措施，或者是实现的细节。理解这些执行算子的执行过程，有助于去理解执行算子的代价计算的流程。对于 Greenplum 数据库中的分布式执行计划，我们也尝试作出了说明。

课程特色

优化器是数据库从业人员必须熟练掌握的内容，而目前单独针对优化器的课程少之又少。在通常情况下，它可能在一本书中只能占到一个很小的章节，这些只能让读者对优化器有一个粗浅的了解。另外，如《PostgreSQL 技术内幕：查询优化深度探索》这样专业剖析 PostgreSQL 优化器源代码的书，对于数据库的“使用者”而言又过于繁琐了。因此本课程致力于不分析 PostgreSQL 的源代码，从一个 SQL 语句的执行开始，逐步分析优化器中涉及的各种优化原则。从参数、系统表、执行计划开始说明，逐步由表及里、由外及内，把 PostgreSQL 优化器背后隐藏的优化思想一一列举出来，最终做到深入浅出解读 PostgreSQL 优化器。

课程寄语

本课程的写作目的是让数据库从业人员对数据库的优化器有一个比较详尽的了解。我希望大家在学习的过程中不但能掌握优化器中的各种优化规则，更能举一反三，在工作中，结合自己学到的优化器知识轻松应对各种优化问题。最后祝大家学习愉快，轻松翻过优化器这座山峰！

点击了解更多《PostgreSQL 优化器入门》

导读：逻辑优化篇

这部分课程致力于让读者达到对数据库优化器理解的第二个层次：详细了解。

愿上层楼骋远目，勿在浮沙筑高台，在开始学习第二个层次的内容之前，让我们先来复习一下第一个层次的内容。为了使对优化器分析的过程更为形象生动，接下来我们跟着小明、大明和牛二哥一起来探讨一下 PostgreSQL 查询优化器的一些基础知识。对这块内容已经了如指掌的朋友可以跳过导读，直接开始后面内容的学习。

查询优化器的基本原理

小明与大明

小明考上了北清大学的计算机研究生，今年学校开了数据库原理课。小明对查询优化的内容不是很理解，虽然已经使出了洪荒之力，仍觉得部分原理有些晦涩难懂，于是打算问一下自己的哥哥大明。

大明是一位资深的数据库内核开发老码农，对 Greenplum/HAWQ 数据库有多年的内核开发经验，眼镜片上的圈圈像年轮一样见证着大明十多年的从业经历。知道小明要来问问题，大明有点紧张，虽然自己做数据库内核好多年了，但是对优化器研究不甚深入，如果被小明这样的小菜鸟问倒就尴尬了。于是大明只好临时抱佛脚，拿出了好多年不看的《数据库系统实现》啃了起来。

小明的问题

小明的第一个问题：“为什么数据库要进行查询优化？”

大明推了推鼻梁上的眼镜，慢条斯理地说：“不止是数据库要进行优化，基本上所有的编程语言在编译的时候都要优化。比如，你在编译 C 语言的时候，可以通过编译选项 -o 来指定进行哪个级别的优化，只是查询数据库的查询优化和 C 语言的优化还有些区别。”

“有哪些区别呢？” 大明停顿了一下，凝视着小明，仿佛期望小明能给出答案，或是给小明腾挪出足够思考的空间。三、五秒之后，大明自答道：“C 语言是过程化语言，已经指定好了需要执行的每一个步骤；但 SQL 是描述性语言，只指定了 WHAT，而没有指定 HOW。这样它的优化空间就大了，你说是不是？”

小明点了点头说：“对，也就是说条条大路通罗马，它比过程语言的选择更多，是不是这样？” 大明笑道：“孺子可教也。虽然我们知道它的优化空间大，但具体如何优化呢？”

说着大明将身子向沙发一靠，翘上二郎腿继续说：“通常来说分成两个层面，一个是基于规则的优化，另一个是基于代价的优化。基于规则的优化也可以叫逻辑优化（或者规则优化），基于代价的优化也可以叫物理优化（或者代价优化）。”

小明的第二个问题：“为什么要进行这样的区分呢？优化就优化嘛，何必还分什么规则和代价呢？”

“分层不分层不是重点，有些优化器层次分得清楚些，有些优化器层次分得就不那么清楚，都只是优化手段而已。”大明感到有点心虚，再这么问下去恐怕要被问住，于是试图引开话题：“我们继续说说 SQL 语言吧，我们说它是一种介于关系演算和关系代数之间的语言，关系演算和关系代数你看过吧？”

小明想了想，好像上课时老师说过关系代数，但没有说关系演算，于是说：“接触过一点，但不是特别明白。”大明得意地说：“关系演算是纯描述性的语言，而关系代数呢，则包含了一些基本的关系操作，SQL 主要借鉴的是关系演算，也包含了关系代数的一部分特点。”

大明看小明有点懵，顿了一下继续说道：“上课的时候老师有没有说过关系代数的基本操作？”小明想了一下说：“好像说了，有投影、选择、连接、并集、差集这几个。”大明点点头说：“对，还有一个叫重命名的，一共 6 个基本操作。另外，结合实际应用在这些基本操作之上又扩展出了外连接、半连接、聚集操作、分组操作等。”

大明继续说道：“SQL 语句虽然是描述性的，但是我们可以把它转化成一个关系代数表达式。而关系代数中呢，又有一些等价的规则，这样我们就能结合这些等价规则对关系代数表达