POSTGRESQL SQL 优化，不建立索引，不调整参数，不修改SQL的另类方式

最新推荐文章于 2024-08-01 15:13:14 发布

Austindatabases

最新推荐文章于 2024-08-01 15:13:14 发布

阅读量325

点赞数 1

文章标签： postgresql 数据库 sql

本文链接：https://blog.csdn.net/liuhuayang/article/details/128878155

版权

开头还是介绍一下群，如果感兴趣polardb ,mongodb ,mysql ,postgresql ,redis 等有问题，有需求都可以加群群内有各大数据库行业大咖，CTO，软件架构师，软件开发大佬，可以解决你的问题。加群请联系 liuaustin3. （快满了，的准备开另一个群了）

在MYSQL 中很少听说过自建统计信息，实际上在其他数据库中，创建统计信息的方式和需求都是有的，尤其处理复杂SQL的数据库产品， POSTGRESQL 是可以对统计信息进行有方式的设计和自建的，支持的版本必须从 11 开始，所有想要有这个功能，你的最低版本是 POSTGRESQL 11.

那么首先我们的提出为什么我们需要一个扩展统计信息的方式来进行相关的工作，需求在哪里。一般情况下的查询是不需要这样的扩展，而有一些大表，特殊的查询的确有一个更有效的数据收集对于数据查询是更有利的。

实际上在我们的认知里面，一般对语句的优化都是要建立对应的索引的，而我们大多忘记了另一个问题就是查询中一些查询因为统计信息与查询的数据的方式不匹配，导致即使有索引也对于查询是无效的。

我们用下面的例子来简单说明一下

我们建立一个表，并且灌入数据，这些数据库是时序性的数据，同时

create table test_t (time_d timestamp,value_d numeric DEFAULT random());

insert into test_t (time_d) SELECT * FROM generate_series('2022-01-01', '2023-06-30', '5 second'::interval);

create statistics test_t_day on (date_trunc('day',time_d)) from test_t;

analyze test_t;

explain analyze SELECT date_trunc('day', time_d) as days, count(*) FROM test_t GROUP BY 1;

drop statistics test_t_day;

explain analyze SELECT date_trunc('day', time_d) as days, count(*) FROM test_t GROUP BY 1;

从上面的截图可以看出在使用了 create statistics 后，查询的执行计划和没有建立 create statistics 的执行计划是截然不同的。使用了 create statistics 后整体的执行的计划变为了并行的方式并且gather merge 中的聚合的行数变为了 1090行，而不使用特殊的统计分析，则执行计划消耗的内存和ROW 都和全表扫描是一致的。在没有任何索引的情况下，执行计划在有效的统计信息的情况下，时间缩减了一半。

但在有些情况下，这样的方式也有一些问题，比如在这张表中增加一个主键，我们在看整体的效果是否有变化。在有主键的情况下，也是可以达到与上面没有主键一样的效果的。

除此以外，数据表中的条件之间有关联的情况下，也可以在统计分析中入手，进行一些统计分析中的特殊的操作。

create table test_t (id serial primary key, age int, ages int);

insert into test_t (age,ages) SELECT i/100, i/500 FROM generate_series(1,2000000) s(i);

create statistics test_t_s (dependencies) on age,ages from test_t;

analyze test_t;

explain analyze select * from test_t where age = 1 and ages = 0 limit 1;

drop statistics test_t_s ;

explain analyze select * from test_t where age = 1 and ages = 0 limit 1;

在这个例子中，查询的提速更加明显，提升了300多倍的速度。

这里例子中就是利用了联合统计方式，将查询中有关的两个字段，进行了联合的统计分析，在联合的统计分析中，可以获取到两个字段之间的关系，在这个数据集合里面，数据是有规律的，我们可以用一个SQL来分析出这样的规律。

每一种数值都是100个，而如果不使用create statistics 那么统计分析将不会关心这两个字段的关系，因为两个值之间是有对应关系的，如果不使用这样的方式，则是每个字段自己来进行统计分析，在查询中，这样的统计分析无法给查询带来任何的收益。

这里重要的问题在于当进行聚合的过程中，一般会产生两种结果
1 hash 聚合
2 group 聚合

如果你的work_mem 足够大的情况下，可以使用HASH 聚合，但是如果work_mem 不足够的情况下，则就需要将所有的行进行group 聚合，那么这样的情况下就会设计到使用磁盘来弥补内存的不足。

比如我们在举一个例子，这个表中的数据，需要经常分组，进行distinct 类似的统计的语句执行。