Our server has 8 cores and is running a web application(DHIS2) which uses postgres as database.
There is big select query which takes a few hours to execute. (The query is executed from the terminal) When that query is run the cpu utilization of that query's process jumps to a constant 100%.
This hangs the application and the application's page does not even load in the browser. This must be because other postgres processes are waiting for that query's process to comeplete.
BUT, when we have muliple cores in the machine then why utilization of a single core to 100% should stop the rest of the processes from executing?
I am unable to understand the concept of multiple cpus and cores in this context. Does postgres not recognize them? What can be the dependency of a select query on another query?
Could somebody please explain this behaviour and suggest ways in which to manage execution of big queries though some kind of postgres configuration may be?
Postgres Version - 9.6
OS - Ubuntu 16
Database Size - 200GB on disk
DHIS2 Version - 2.30
Query (Calucates outliers) -
select datasets,
max(ou1.name) Country, ou1.organisationunitid as Country__Id ,
max(ou2.name) state, ou2.organisationunitid as State__Id ,
max(ou3.name) Division, ou3.organisationunitid as Division__Id ,
max(ou4.name) District, max(ou4.code) as District__Code, ou4.organisationunitid as District__Id,
max(ou5.name) Block, max(ou5.code) as Block__Code, ou5.organisationunitid as Block__Id,
max(ou6.name) Facility, max(ou6.code) as Facility__Code,ou6.organisationunitid as Facility__Id,
max(ou.name) as outlierfacility,
max(de.name) as dataelement,
max(coc.name) as category,
concat(max(p.startdate),':',max(p.enddate)) as period,
max(pt.name) as frequency,
_dv.value,
u upperbound,
l lowerbound,
mean,
std
from
(
with stats as (
select dv.sourceid,
dv.dataelementid,
dv.categoryoptioncomboid,
dv.attributeoptioncomboid,
array_agg(distinct dv.periodid) as periods,
array_agg(distinct ds.name) as datasets,
avg(dv.value::float) as mean,
stddev(dv.value::float) as std
from datavalue dv
inner join datasetmembers dsm on dsm.dataelementid = dv.dataelementid
inner join dataelement de on de.dataelementid = dsm.dataelementid
inner join dataset ds on ds.datasetid = dsm.datasetid
inner join period pe on pe.periodid = dv.periodid
inner join periodtype pt on pt.periodtypeid = pe.periodtypeid
inner join categoryoptioncombo coc on dv.categoryoptioncomboid = coc.categoryoptioncomboid
inner join _orgunitstructure ous on ous.organisationunitid = dv.sourceid
where pe.startdate between date('2019-04-29') - interval '6 months' and date('2019-04-29') and pt.name='Monthly'
and de.valueType in ('NUMBER','INTEGER')
and ds.uid in ('123qwe123','123ewq123')
group by dv.sourceid,dv.dataelementid,dv.categoryoptioncomboid,dv.attributeoptioncomboid
)
select dv.*,datasets,mean,std,mean+3*std u,mean-3*std l
from datavalue dv
inner join period pe on pe.periodid = dv.periodid
inner join periodtype pt on pt.periodtypeid = pe.periodtypeid
inner join stats on
stats.dataelementid = dv.dataelementid and
stats.sourceid= dv.sourceid and
stats.categoryoptioncomboid = dv.categoryoptioncomboid and
stats.attributeoptioncomboid = dv.attributeoptioncomboid
where dv.periodid = any(periods)
and (dv.value::float > mean+3*std or dv.value::float < mean-3*std)
) _dv
inner join dataelement de on _dv.dataelementid = de.dataelementid
inner join categoryoptioncombo coc on _dv.categoryoptioncomboid = coc.categoryoptioncomboid
inner join _orgunitstructure ous on _dv.sourceid = ous.organisationunitid
inner join organisationunit ou on ou.organisationunitid = ous.organisationunitid
left join organisationunit ou1 on ou1.organisationunitid = ous.idlevel1
left join organisationunit ou2 on ou2.organisationunitid = ous.idlevel2
left join organisationunit ou3 on ou3.organisationunitid = ous.idlevel3
left join organisationunit ou4 on ou4.organisationunitid = ous.idlevel4
left join organisationunit ou5 on ou5.organisationunitid = ous.idlevel5
left join organisationunit ou6 on ou6.organisationunitid = ous.idlevel6
inner join period p on _dv.periodid = p.periodid
inner join periodtype pt on p.periodtypeid = pt.periodtypeid
group by ou1.organisationunitid,
ou2.organisationunitid,
ou3.organisationunitid,
ou4.organisationunitid,
ou5.organisationunitid,
ou6.organisationunitid,
_dv.dataelementid,_dv.sourceid,_dv.categoryoptioncomboid,_dv.attributeoptioncomboid,_dv.periodid,_dv.value,u,l,mean,std,datasets
order by country,state,division,district,block,facility,dataelement,category
