postgresql-with as处理子查询速度慢的问题_with as和子查询效率

在数据库查询过程中，有时候我们会遇到一些对聚合函数的结果进行过滤的情况，如果这个聚合函数本身又是个子查询获得的结果，这个时候再把它作为 一个where 条件语句的话，会导致查询效率极其的差。而通过with as 语法先把它“暂存”一下速度则会快很多。

基本用例如下：

1 我想做一个错题统计，先把错题ID和错题数查询出来

select 
	info.answerexeid AS exeid,//习题ID
	(select count	( T.answerexeid ) AS count from	w008_answer_paper_info T 
	where T.answerexeid = info.answerexeid and  T.iscorrect = 20 ) as wrongcount//错题数 
from
	w008_answer_paper_header h
	join w008_answer_paper_info info on h.ID = info.answerheaderid
	join w008_exercise_stem_info e on info.answerexeid = e.id 
	join w008_execrise_info i on info.answerexeid = i.exerciseid 
where
	h.userlongid = 2507032645091840 
	and info.iscorrect = 20

数据量不大，查询毫无压力

2 如果我想统计错误数大于12的情况，因为这个错题数是查询之后得到的结果，所以一般的做法是把当前查询结果作为一个“表”，再次查询，这次效率就慢了很多

2.1 没有where 条件

select t1.wrongcount
from
(select 
	info.answerexeid AS exeid,
	(select count	( T.answerexeid ) AS count from	w008_answer_paper_info T 
	where T.answerexeid = info.answerexeid and  T.iscorrect = 20 ) as wrongcount 
from
	w008_answer_paper_header h
	join w008_answer_paper_info info on h.ID = info.answerheaderid
	join w008_exercise_stem_info e on info.answerexeid = e.id 
	join w008_execrise_info i on info.answerexeid = i.exerciseid 
where
	h.userlongid = 2507032645091840 
	and info.iscorrect = 20
	)t1

执行结果：

2.2 加上过滤条件，这一次执行时间在30s左右，这样的数据量，这个速度就太难以接受了

select t1.wrongcount
from
(select 
	info.answerexeid AS exeid,
	(select count	( T.answerexeid ) AS count from	w008_answer_paper_info T 
	where T.answerexeid = info.answerexeid and  T.iscorrect = 20 ) as wrongcount 
from
	w008_answer_paper_header h
	join w008_answer_paper_info info on h.ID = info.answerheaderid
	join w008_exercise_stem_info e on info.answerexeid = e.id 
	join w008_execrise_info i on info.answerexeid = i.exerciseid 
where
	h.userlongid = 2507032645091840 
	and info.iscorrect = 20
	)t1
	where t1.wrongcount>12

执行结果：

 

3 改用with as 语法，耗时不到0.1秒，明显快了很多。

with	wronttab AS ( SELECT answerexeid, COUNT ( answerexeid ) AS wrongcount 
        FROM w008_answer_paper_info T WHERE iscorrect = '20' GROUP BY answerexeid )  
select 
	info.answerexeid AS exeid,
	(select count	( T.answerexeid ) AS count from	w008_answer_paper_info T 
	where T.answerexeid = info.answerexeid and  T.iscorrect = 20 ) as wrongcount 
from
	w008_answer_paper_header h
	join w008_answer_paper_info info on h.ID = info.answerheaderid
	join w008_exercise_stem_info e on info.answerexeid = e.id 
	join w008_execrise_info i on info.answerexeid = i.exerciseid 
	JOIN wronttab ON wronttab.answerexeid = info.answerexeid  
where
	h.userlongid = 2507032645091840 
	and info.iscorrect = 20
	and wronttab.wrongcount>12

执行结果：

其实with as  语法说白了，就是建了一个临时表，起到了视图的作用。避免子查询因嵌套太多导致性能慢的问题。