锡林郭勒盟网站建设_网站建设公司_Logo设计_seo优化

有一个复杂的CTE查询，原来最后一步直接查询的语句如下

selectt.rn,sfrom(selectrn,s,row_number()over(partitionbyrnorderbyrn)resnfromtwheret.next_pos=0)t,bWHEREresn=1andt.rn=b.rn;...Time:1357.870ms(00:01.358)

想统计最终结果中表t的行数，而不是关联后的行数，于是用下面的查询

selectcount(*)fromtwheret.next_pos=0;-- 2.754秒count-------2843(1row)Time:2754.143ms(00:02.754)

开始以为是row_number()函数过滤的影响，结果并不是，还是比较慢

selectcount(*)from(selectrn,s,row_number()over(partitionbyrnorderbyrn)resnfromtwheret.next_pos=0)t1WHEREresn=1;--2.855秒count-------1000(1row)Time:2855.320ms(00:02.855)

而原来未加count( *)的直接查询只要一半的时间。奇怪的是，对加了表连接的结果再count( *)反而更快，

selectcount(*)from(selectrn,s,row_number()over(partitionbyrnorderbyrn)resnfromtwheret.next_pos=0)t1,bWHEREresn=1andt1.rn=b.rn;--1.239秒count-------1000(1row)Time:1239.115ms(00:01.239)

查看执行计划，慢查询里有一处耗时的操作它的关联条件很长。

Nested Loop (cost=0.00..20184.70 rows=1550 width=142) (actual time=0.038..1595.842 rows=45778.00 loops=1) Join Filter: (substr(CASE CASE WHEN (length(replace(substr(replace(replace((s.b)::text, ' '::text, ''::text), ' '::text, ''::text), hh), '?'::text, ''::text)) > length(replace(substr(replace(replace((s.b)::text, ' '::text, ''::text), ' '::text, ''::text), ss), '?'::text, ''::text))) THEN 0 ELSE 1 END WHEN 0 THEN replace(replace((s.b)::text, ' '::text, ''::text), ' '::text, ''::text) ELSE reverse(replace(replace((s.b)::text, ' '::text, ''::text), ' '::text, ''::text)) END, all_pos.pos, 1) = (all_pos.n)::text)

更快的语句执行计划同样的地方就只有(substr(b_1.b, all_pos.pos, 1) = (all_pos.n)::text)。成本也只有一半

-> Nested Loop (cost=0.00..9316.20 rows=1550 width=84) (actual time=0.006..183.893 rows=45778.00 loops=1) Join Filter: (substr(b_1.b, all_pos.pos, 1) = (all_pos.n)::text)

而上述过滤条件是在另两个子查询中

aas(selectrn,replace(replace(b,chr(10),''),' ','')bfroms,bas(selectrn,caseflagwhen0thenbelsereverse(b)endb,flagfrom(selectrn,b,casewhenlength(replace(substr(b,hh),'?',''))>length(replace(substr(b,ss),'?',''))then0else1endflagfroma)s),

看来这几个子查询都没有实体化，而是展开到了 Nested Loop连接的句子中
给子查询b 加了个MATERIALIZED，变成b as MATERIALIZED (select rn,case flag when ..., 所有的count就都快了。

selectcount(*)fromtwheret.next_pos=0;count-------2843(1row)Time:1249.923ms(00:01.250)selectcount(*)from(selectrn,s,row_number()over(partitionbyrnorderbyrn)resnfromtwheret.next_pos=0)t1WHEREresn=1;count-------1000(1row)Time:1218.167ms(00:01.218)