共计 5575 个字符，预计需要花费 14 分钟才能阅读完成。
在详解 PostgreSQL 执行打算之前，须要先晓得执行打算由来。咱们 PostgreSQL 数据库任何查问都会通过语法和语义解析，生成查问表达式树，也就是罕用查问数，解析器它会去解析咱们的语法，分析器会把语法对应对象进行开展，通过重写器对规定进行重写，最初生成查问数。
依据查问树执行器通过查问再进行预处理，找出最小代价门路，最终创立出打算树。再把查问打算交由执行器进行执行。最终执行实现它会把后果返回给前端利用。这些操作都是在每个连贯对应 Backend 过程去进行解决。执行器在执行时，会去拜访共享内存，内存没有数据，则从磁盘读取。最终将查问的后果缓存在数据库中，逐渐输入给用户过程。
过程会波及到例如 Work memory、temp buffer 等过程级内存，能够通过咱们的 Explain 命令来查看执行打算，对不合理的资源进行调整，进步 SQL 执行效率。在 SQL 后面加上 Explain，就能够间接看到执行打算。不论是在 pgadmin 还是其它工具都能够简略进行查看，也能够通过咱们的 psql 去查看。
咱们的执行打算有几个特点，首先查问布局是以布局为节点的树形构造，以查问的一些门路作为树形构造，树最底层节点是扫描节点，它是去扫描表中原始行数。不同表也有不同扫描类型，比方程序扫描或索引扫描、位图索引扫描。也有非表列源，比如说 Values 子句。还有查问，可能须要关联、聚合、排序以便操作，同时也会在扫描节点上减少节点进行操作提醒以及耗费。Expain 输入总是以每个树节点显示一行，内容是根本节点类型和执行节点的耗费评估。可能会呈现同级别节点，从汇总行节点缩进显示其它属性。第一行个别都是咱们汇总的耗费，这个值是越小越好。
在看一个执行打算，咱们创立一个测试表，插入 1 万条数据做剖析后，能够看到它的执行打算，这个执行打算很简略，全面扫描它只有一行。执行打算咱们从左到右去看，先是评估开始的耗费，这里因为没有别的步骤，所以这个步骤是从 0 开始，而后是一个总耗费评估。
Rows 是输入的行数，它是一个评估后果；而后是每一行的均匀字节数，这是一个评估后果，这个评估后果依赖于 pg_stats 和 pg_statistic 统计信息。
那么咱们怎么去看执行打算呢？就是下级节点的耗费，其中蕴含了其子节点的耗费，这个耗费值反映在布局器评估这个操作须要的代价。个别这个耗费不包含将数据传输到客户端，只是在数据库后盾的执行代价。评估的行数不是执行和扫描节点查问的节点数量，而是返回的数量。同时耗费它不是一个秒的，它是咱们布局器的一个参数。Cost 是形容一个执行打算代价是多少，而不是具体工夫。
代价评估的一些基准值个别会关注哪几个参数呢？seq_page_cost，即扫描一个块须要的耗费，咱们默认为是 1，而随机扫描 random_page 咱们默认为是 4，这个在优化的环节须要进行优化，比如说当初应用 SSD，随机页的拜访效率必定比其它的磁盘更快，而这里值就能够改为 1。另外就是 cpu_tuple_cost，咱们 CPU 去扫描一个块里具体行数，一行大略 0.01 的耗费。索引是 cpu_index_tuple_cost，0.005 的耗费。
举个例子，新建 Test 表有一万行，它调配了 94 个页。而依据刚刚执行打算能够大略估算耗费：磁盘页乘程序扫描的 Cost，加上扫描行数。这个值就是 94 个页乘以 1，加上 1 万行乘以 0.01 的耗费就是 194。
那什么时候去更新 pg_class 以及 pg_stat_user_tables 的统计信息？它分为两个局部，一部分次要还是通过 analyze 以及局部 DDL 语句去触发更新统计信息。所以执行打算精确与否和统计信息也很有关联。这里加上条件，比如说 Where Id 小于 1000，会去减少一个筛选条件。这样扫描的同时它会去减少损耗，比方扫描的行数不变，然而减少了 CPU 的计算比拟工夫，就变为 219。
执行打算最底层是表的扫描，而扫描又分为两种形式，全表扫描以及索引扫描。全表扫描顾名思义去整个表上扫描。就算是有些表加了索引，它也不肯定会走索引扫描，如果说满足条件的数据集比拟大，索引扫描代价比全表扫描更大，它就会走全表扫描。如后面所说，扫描全表，这个时候从新扫描，会先走索引，再走对应的块，这个代价会比走全表扫描更慢。
另一个问题是索引扫描 Index Scan。在下面的测试表对查问列建一个索引，举例查问条件是小于 1000 这个值，cost 缩小还不够直观，如果条件是小于 10 之类小数据量查问，索引成果更好，间接走 Index Scan。但如果查问条件筛选率不够高，查问会先走索引扫描，再从新扫描行，扫描后他会去判断每一个行的条件，Cost 可能相应就变更高。在优化的时候，尤其要去关注这一点，肯定要关注索引的筛选率。
索引扫描里还有一个 Index Only Scan，也就是投影列、查问条件都在索引外面，它就会走一个 Index Only Scan，不会再去读其它具体的行值，扫描完索引之后就返回，效率十分高。
还有一种扫描形式是位图扫描，在 PG 里没有位图索引，然而它是有位图扫描的，个别是在 on、and 或 in 子句外面去走。举个例子，下面查问 ID 小于 1000，同时 ID 要大于 9000，这时候它会先做两次索引扫描。扫描时它不会去读具体数据，会先去做一个 Bitmap Scan，之后咱们的条件是 Or，会先做一个汇集后再去做 Check，看一下具体实现形式。它是先去启动工夫两个 Bitmap Scan 总和，因为是具体扫描会有扫描时间，所以这个组合会破费大量工夫。同时 Index Scan 输入的是 Tuple，先扫描索引块，失去对应 ctid 再去扫描具体数据。如果一次只读一条索引项而后去判断行是否满足条件，一个 PAGE 能够屡次拜访。而 Bitmap Scan 会去输入所有满足条件的索引项，而后组合到一起做 or 等操作，最初才交给上一个节点 Bitmap Heap Scan 去扫描具体数据，因为会先去依据索引扫描的物理数据进行排序，一次性将块中满足条件索引项数据取出来。这样能够说一个块，一次扫描就扫描完了，能够设想这个效率是十分高的。
在底层的数据扫描完之后会去做表连贯。连贯形式个别在两表关联的时候才有连贯可能。个别简略说自然选择、左连贯、右连贯等等。但具体的到数据库的执行打算里个别次要有 hash join、nested loop、merge join。
Hash Join，它是以 Hash 形式来进行表连贯，首先它确定是两个表里的大小，应用小表去建设 Hash map，去扫描大表比拟 Hash 值获取最终查问后果。咱们示例中建设另外一张表 Test1，并建一个索引进行两张表关联查问，当他们的 T1 的 ID 小于 10，它 Info 相等，做一个关联查问。首先开始的时候，因为两个表大小一样，一张有索引，一张没有，会优先选择有索引的表去做一个 Hash 桶，另外一张表进行一个循环比拟 Hash 值。如果说变一下条件把 Test1 表删除一部分数据，优化器会以 Test1 去做一个 Hash 表，Test 表在下面去做驱动。
做一个简略梳理。Hash 连贯是在做大数据连贯时十分有用的形式，就是在两个大表进行 join。那么这里也是为什么 PG 在和 MySQL 比的时候，说它的剖析能力要强一点的起因，因为咱们的 Hash join 反对十分好。另外当初 MySQL 曾经反对 Hash 了，然而还不是那么欠缺。
Hash 它有个问题，如果 Hash 的小表也比拟大，Hash 表的后果十分大，你的内存放不下，这时就可能会写到你的磁盘中去，就会导致性能急剧下降。在这个时候就要进步 work_mem。hash join 的工夫耗费是什么？咱们的外层 Cost 申请，加上内层一个申请就能够了。
另外一个连贯形式就是 Nested Loop 循环扫描，在这个扫描上写了两个循环去扫描。个别在优化的时候，特地是用 PG 数据库，要去重点看 Nested Loop 是不是正当。那么什么时候用 Nested Loop 呢？就是小表和大表进行关联的时候，小表作为驱动表，那大表作为上面的内层表会比拟正当。
首先它会确定一个驱动表，另外是一个内层的表，驱动表每一行与它外面那张表进行一个查问，一个嵌套循环查问比拟，代价十分高。就比方每次都是外层的表，乘以里面的条件耗费，这一看就比拟大了。
像这种状况，每次扫描时，外层的表每次在驱动时它会去扫描层内层的表，这样效率非常低。而如果内层的表它后果集是绝对固定的，那么就能够扫描一次把它做一个物化，下次再循环比拟的时就不必再去查问外面的表，相似于 Hash join。Hash join 是做什么的呢？它后面也是一个 Loop，只是把内存的表建设一个 Hash 表，这样去扫描就会快很多。Materialize 就是这么一个优化的方向，这个也依赖于咱们的 work_mem。
最初一种连贯形式叫 Merge join，次要针对于数据量不是特地大的状况下，而且两个表如果构造类似，做好排序，这时反而会比散列连贯会好一点。示例中原来是走了一个 Nested Loop，咱们把索引删除，它就去走了 Merge join。个别对于这种数值比拟效率还能够，因为排序数值效率是高一点。如果是字符串一类，走 Merge join 效率会更低。
看一下具体的实现，它是先将两个表进行一个排序。Id 1 等于 1 先比拟完后，再去比拟 Id 等于 2 时，就不会再去比拟 Id1 等于 1 的地位块，会间接从另外一张表的 2 开始去比拟。
做一个简略比拟，Hash join 是将一个小表做为一个内存表做 Hash 运算，将列数据依据 hash 值放到 Hash 行列表中，再从另外一张表去抽取记录做 Hash 运算找到匹配的值，个别是小表做 Hash 表。
Nested Loop 是一张表读取数据，拜访另外一张表做匹配。Nested Loop 在关联表比拟小的时候效率最高。小表做驱动，比方这个表只有百来行，而大表很大，循环 100 次查问，大表会进行索引扫描，绝对会快很多。
Merge join 如果数据做好了排序，而且是数字类型排序，Merge join 可能反而比 Hash 要快。但一般来说如果数据量比拟大，Hash 根本会比 Merge join 更快。
另外是关联相干参数个别以 Enable 结尾。刚刚那几种连贯 Nested Loop、Merge join、Hash join、Bitmap Scan 都是能够去管制的，参数能够是 session 级别管制。
查看执行打算首先是看扫描形式和连贯形式，不管再怎么简单，都是通过这两个进行组合。个别是看它在扫描和关联是不是正当的。这两个判断之后，再去看它的条件是不是正当，或需不需要改写。有了执行打算之外，在看具体执行工夫，就要加上 Explain Analyze 来看具体执行工夫。这里有一个不一样的点，在这里有了一个理论执行工夫，这个工夫是实在工夫。能够很准确晓得每一步破费工夫。
在 Analyze 之外，还有一些其它参数，能够通过 \H Explain 的形式去查看具体的语法，有 verbos 显示具体执行日志，还有 Cost 耗费、Settings 显示非凡设置，buffers 内存的一些分配情况。wal、Timing 工夫，Summary，format 输入的格局 TXT 或者 xml、json。如果加上，它的显示信息会多很多。次要是 buffers 比拟有用，显示说你申请了多少，当初多少磁盘块是要命中，多少是进行读取的。在第二次查问的时候，它的磁盘读取会变少，第一次读取是 94 块，第二次 50 块块。
除了上述内容，还有一个日志参数。咱们的 log_planner_stats 能够输入你的执行打算到日志文件中，Oracle 的执行打算是从表里去看，而咱们 PG 是没有的。那么怎么办？能够通过一些参数去管制，导到日志里来。就目前这个日志它是输出到运行日志里的，没有独自去进行记录。当然这个也是咱们优化的一个方向。
通过设置这些参数，把这里日志打印进去，显示出执行打算，语法分析、语义剖析、重写，这几个阶段它会显示进去。如果开启了执行打算状态，会把这些进行打印。
最初看执行打算之外，从执行打算去反推 SQL 优化方向。从最底层一个扫描去动手，要尽量走索引扫描。另外索引扫描这里有很多形式，就是看它是否是正当索引，要看类型是不是抉择正当的。比方数字类型、字符串类型，咱们选用 gin 索引，还是一些 btree 索引。PG 默认是 btree 索引，但 btree 索引不是所有类型和操作符都会实用。另外还须要缩小不必要的索引、防止单条 SQL 插入，要单条变为批量进行插入。
后面说执行计划表连贯类型是不是正确正当，另外要从 SQL 自身进行动手，咱们目标是为了缩小它的耗费。如果 SQL 语句比较复杂，而扫描类型曾经无奈改变，那这时只能去改写 SQL 语句，尽量减少嵌套，缩小子查问。还能够通过物化视图长期表，去做 SQL 拆分。
尽量把 in 语法用 Exits 形式做连贯。另外还要留神一些类型的转换失真，在扫描时，如果它能够走索引扫描，后果走了全面扫描，可能是转换失真了，比如说一个 in 类型，后果输出是一个字符串类型，它有可能会转换失败，只能走全面扫描，不能索引。
另外从数据库参数来动手，就须要准确的统计信息，咱们在生成执行打算时，可能 autovacuum 没有去执行，也可能统计信息落后，那么执行打算就是谬误的。这时候就要对应表作为一个 analyze。
最初就是干预执行打算，干预执行打算有两种形式，除了后面的 enable 几个参数，咱们的 pg_hint_plan 插件也能够做一些 Hint 管制。还有一些新参数调整，例如调整 work_mem、temp_buffers、shared_buffers 等参数。还有一些连接池的应用，咱们操作系统参数、硬件的性能参数调整等等。
其实往往数据库优化，除了这些以外，还有咱们去看操作系统的一些硬件性能，比方 CPU 是不是 Performance 模式，磁盘调度形式是不是最优的，网卡 Bond 模式等其余参数。
