我用的是postgresql,sql中有个province条件,我把province的值更换后,出现了某个值为条件时可以执行成功(速度较快),然后province为其他值时执行失败(长时间执行不出结果),这可能是什么原因?
with first_condition as (
select monitor_point, monitor_info, company, count(distinct(month)) as continuous_month
from craw_day_merge where day>='2020-02-01' and day<= '2020-03-31' and valid_complete < 0.75 and province='安徽'
group by company, monitor_point, monitor_info having sum(exceed_count)=0
and count(distinct(month)) >= '1'
)
select merge.company,
merge.monitor_point, merge.monitor_info, merge.province, first_condition.continuous_month
from craw_day_merge merge inner join first_condition
on merge.day>='2019-02-01' and merge.day<= '2020-03-31' and
merge.monitor_point=first_condition.monitor_point and merge.monitor_info=first_condition.monitor_info and
merge.company=first_condition.company
group by merge.company,
merge.monitor_point, merge.monitor_info, merge.province, first_condition.continuous_month;
###explain + sql可以看到执行过程,注意fliter,recheck index,lossy这些会导致慢查询的因素,换sql写法或者加索引去解决。
如果执行过程没有问题,可以尝试reindex索引或者vaccum这个表。