POSTGRESQL 通过explain buffer 来标准化优化指标 与 发现衰减的通道

在POSTGRESQL 语句的优化中，基于语句的复杂度，快速进行语句的优化是一个比较重要的难点。从语句的复杂度到分析难度上，要将一个复杂的语句能快速的找到语句执行的矛盾点是一件复杂的事情。

但如果抓住核心，优化SQL 也可以变得简单，其中一个关键点就是减少IO操作，SQL 的执行中，IO的操作是一个星命点，IO 在一个SQL的查询中越高，说明这个SQL 消耗的资源越大，这主要是由以下几个问题来决定的。

1  查询中IO 高，原因之一是需要处理的数据并不在内存系统中，而是要通过CPU 来调用磁盘的数据页面，并加载到内存中在进行正常的数据处理，磁盘的操作即使通过SSD 方式，相对于内存来说，也是缓慢的。

2  查询中更多的CPU 并没有进入到SQL的执行和运算中，而是进入了IO 调用的环节，数据持续的进出内存，导致CPU 工作在这个CPU资源消耗在最不值得的地方。

那么问题来了，如何在查询中发现IO 操作，这里可以通过explain 命令里面的buffers 来发现这个问题。

我们创建一个测试表，来模拟如何通过buffers 来快速找出语句中的问题

create table t1 as

select id::int8, round(random() * 100000)::int8 as num

from generate_series(1, 10000000) as id;

创建两张表，同时针对同样的两张表，一个添加查询中的相关索引，一个不添加相关的索引。

我们通过下图可以清晰的看到，两个表添加索引和不添加索引，在命中索引后的cost 的状态。

但实际上，我们也只能从 cost 的方面来对查询的进行分析，

但这样的部分，我们很难有一些准确的指标的说明，此时我们可以通过buffers 参数来完成相关的在查询中指标化的标定。

54171 * 8 /1024 = 432MB 

1002 * 8/1024 = 7.8MB

从上面的内存的BUFFER HIT 就可以明确的知晓，两种查询中所在buffer hit中的消耗的问题，这个数字比cost 的数字，在一些对数据库不是很理解的人士面前，解释查询的消耗将变得更加的容易。

如果觉得这样的方式还不明确化，我们还可以通过下面的方式持续对一些差的SQL 进行锚定。

我们将两个表的表的大小进行展示，我们稍微的改变一下，在t1 中建立另外的索引，然后我们在看我们的查询执行的计划中的buffer 的情况。 其中buffers 变为了18

54171 * 8 /1024 = 432MB 

1002 * 8/1024 = 7.8MB

18*8/1024 = 0.14MB

那么在经历了三次优化后，我们可以通过buffers 中给出buffer hit 直接运算后，获知查询后除时间评判标准后，的另一个标准， buffers hit。

实际上从时间的维度看，我们的整体的查询时间也是有消减的。这里可能有同学在问，最后一个索引建立后，为什么打破了传统索引优化，而获得了更低的查询消耗。2列字段索引 ，比单字段索引在这里更有优势，而按照普通的优化思维模式，针对条件建立对应的索引，已经是终极索引优化的完美情况了。

下图中我们对表1建立了，create index idx_t1_number_id on t1 (num,id)；而为什么 num 和  num id 相比较，num id 的索引在这里面更有效呢？

主要还是PG 中的数据存储的问题，在PG 中数据存储并不是有序的，并且数据存储在页面中尤其是UPDATE 后的状态是乱序的，那么如果你在查询中对数据库产生顺序查询的方式，则所搜的页面，比带有主键的方式在查询时，获取的页面要多。

所以基于以上的思路，在查询范围查找中，如果在PG查询中带有主键，往往要比不带主键的情况下，扫描的页面要更少。

甚至如果能懂得里面的一些更深层次的含义，可以在每次的SQL 查询中，获得某些PG 表性能衰减的踪迹，并且很快的制定标准，并在合适的时候对表进行更及时的VACUUM 操作。

以上的想法也是我在写这篇文字时，突发奇想感悟出来的，所以其实这篇的题目应该在加一条，通过buffers 参数，可以在每次查询中，对比历史查询的数据，感知POSTGRESQL 数据表的性能衰减情况。