关于WITH AS在Pgsql中简单的优化测试，性能提升效果显著

背景

某公司业务管理系统，对于电子竞价的报表查询优化。

环境

数据库管理：Navicat for PostgreSQL 12企业版
项目：SringBoot架构

准备

测试数据，联查数据

优化

旧sql结构

SELECT 
	--几十个字段加各种运算 
FROM
	(
		SELECT 
			--几十个字段加各种运算 
		FROM
		(
			(
				SELECT 
			--几十个字段加各种运算 
				FROM '某表'
				LEFT JOIN  (
					SELECT 
						--几十个字段加各种运算 
					FROM  '某表'
						LEFT JOIN  (
							SELECT 
							--几十个字段加各种运算 
							FROM  '某表'
						)  as ‘关键查询1’
						.......
				)	as ‘关键查询3’
				.......
			) as ‘关键查询2’
			.......
			LEFT JOIN  (
				SELECT 
				--几十个字段加各种运算 
				FROM  '某表'
			)
			UNION
			(
				SELECT 
			--几十个字段加各种运算 
				FROM '某表'
				LEFT JOIN  (
					SELECT 
						--几十个字段加各种运算 
					FROM  '某表'
						LEFT JOIN  (
							SELECT 
							--几十个字段加各种运算 
							FROM  '某表'
						)  as ‘关键查询1’
						......
				)	as ‘关键查询5’
				......
			) as ‘关键查询4’
			.......
			LEFT JOIN  (
				SELECT 
				--几十个字段加各种运算 
				FROM  '某表'
			)
		)
	)
LEFT JOIN  (
	SELECT 
	--几十个字段加各种运算 
	FROM  '某表'
)
ORDER BY 
--几十个字段

测试数据实测结果83.102S，确实有点慢

先用EXPLAIN查看执行流程与扫描过程，这里就不放图了

分析：Hash Join (cost=14370.08…24125.00 rows=17 width=697)，预估启动代价（找到第一个符合条件的结果的代价）和总代价，以及预估行数和每行宽度
通过执行流程我们不难发现，‘关键查询1’的查询在整体的执行流程中被重复扫描，而且消耗性能巨大

优化后的sql结构

WITH ‘关键查询1_提取’ AS (
 	SELECT 
 	--几十个字段加各种运算 
 	FROM  '某表'
)
SELECT 
 --几十个字段加各种运算 
FROM
 (
 	SELECT 
 		--几十个字段加各种运算 
 	FROM
 	(
 		(
 			SELECT 
 		--几十个字段加各种运算 
 			FROM '某表'
 			LEFT JOIN  (
 				SELECT 
 					--几十个字段加各种运算 
 				FROM  '某表'
 					LEFT JOIN  (
 						SELECT 
 						*
 						FROM  '关键查询1_提取'
 					)  as ‘关键查询1’
 					.......
 			)	as ‘关键查询3’
 			.......
 		) as ‘关键查询2’
 		.......
 		LEFT JOIN  (
 			SELECT 
 			--几十个字段加各种运算 
 			FROM  '某表'
 		)
 		UNION
 		(
 			SELECT 
 		--几十个字段加各种运算 
 			FROM '某表'
 			LEFT JOIN  (
 				SELECT 
 					--几十个字段加各种运算 
 				FROM  '某表'
 					LEFT JOIN  (
 						SELECT 
 						*
 						FROM  '关键查询1_提取'
 					)  as ‘关键查询1’
 					......
 			)	as ‘关键查询5’
 			......
 		) as ‘关键查询4’
 		.......
 		LEFT JOIN  (
 			SELECT 
 			--几十个字段加各种运算 
 			FROM  '某表'
 		)
 	)
 )
LEFT JOIN  (
 SELECT 
 --几十个字段加各种运算 
 FROM  '某表'
)
ORDER BY 
--几十个字段

新sql测试数据实测结果0.378S，查询效率大幅度提升

结语

当然了，这个sql真实情况要比上文提到的结构要复杂一下，包括分组，运算等等的操作，这里只是做一个对于WITH AS的sql片段提取跟其查询效率性能提升的测试。
尤其是对于UNION ,UNION ALL 里面含有复杂嵌套查询的sql来说，WITH AS对于查询性能，以及sql的可读性都有很大的提升。
本人对于sql优化也只是浅层次的，只是在项目中尝试出的结果，更加深层次的分析正在学习中。

关于EXPLAIN 语法

在PostgreSQL 中，EXPLAIN 命令可以输出SQL 语句的查询计划，具体语法如下：

EXPLAIN [ ( option [, …] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

where option can be one of:

ANALYZE [ boolean ]
VERBOSE [ boolean ]
COSTS [ boolean ]
BUFFERS [ boolean ]
TIMING [ boolean ]
SUMMARY [ boolean ]
FORMAT { TEXT | XML | JSON | YAML }

其中：

ANALYZE

选项为TRUE 会实际执行SQL，并获得相应的查询计划，默认为FALSE。如果优化一些修改数据的SQL 需要真实的执行但是不能影响现有的数据，可以放在一个事务中，分析完成后可以直接回滚。

VERBOSE

选项为TRUE 会显示查询计划的附加信息，默认为FALSE。附加信息包括查询计划中每个节点（后面具体解释节点的含义）输出的列（Output），表的SCHEMA 信息，函数的SCHEMA 信息，表达式中列所属表的别名，被触发的触发器名称等。

COSTS

选项为TRUE 会显示每个计划节点的预估启动代价（找到第一个符合条件的结果的代价）和总代价，以及预估行数和每行宽度，默认为TRUE。

BUFFERS

选项为TRUE 会显示关于缓存的使用信息，默认为FALSE。该参数只能与ANALYZE 参数一起使用。缓冲区信息包括共享块（常规表或者索引块）、本地块（临时表或者索引块）和临时块（排序或者哈希等涉及到的短期存在的数据块）的命中块数，更新块数，挤出块数。

TIMING

选项为TRUE 会显示每个计划节点的实际启动时间和总的执行时间，默认为TRUE。该参数只能与ANALYZE 参数一起使用。因为对于一些系统来说，获取系统时间需要比较大的代价，如果只需要准确的返回行数，而不需要准确的时间，可以把该参数关闭。

SUMMARY

选项为TRUE 会在查询计划后面输出总结信息，例如查询计划生成的时间和查询计划执行的时间。当ANALYZE 选项打开时，它默认为TRUE。

FORMAT

指定输出格式，默认为TEXT。各个格式输出的内容都是相同的，其中XML | JSON | YAML 更有利于我们通过程序解析SQL 语句的查询计划，为了更有利于阅读，我们下文的例子都是使用TEXT 格式的输出结果。