I have a very simple table storing the following fields per http request:
- id serial
- url varchar
- date varchar (yyyy-mm-dd)
- time varchar (hh:mm)
- latency integer
And I am trying to calculate average latency per minute, however if i have 10 requests (9 of 1s and 1 of 100s) then average becomes useless. In order to solve that I was wondering about remove outliers.
So, basically what I want is to calculate the average excluding request which latency is bigger then 99.7%, naturally I want to group those requests per minute in order to follow "average" latency per minute.
My query is as follows:
select mdate,mtime,avg(latency) from mtable join ( select mdate,mtime,avg(latency) + 3*stddev(latency) as "uband" from mtable where url like '%ThePartialUrl%' group by, mdate, mtime ) as t on mdate=t.mdate and mtime=t.mtime and latency < t.uband where url like '%ThePartialUrl%' group by mdate,mtime order by mdate, mtime
However it is taking too much time. Things I've already know/done:
- url like requires a full scan regardless of index
- mdate and mtime could be one column using datetime value
- I have index for almost everything (mdate,mtime,latency)
- ajusting work_mem parameters
The table itself has around 100M rows at total.
Looking at the execution plan it seems the nested loop (join) is the one taking most time since the subquery returns 40K rows (1 per minute during a whole month) and it shall be joined around to 10M rows for the specific '%ThePartialUrl%', resulting in 4 a billion loop.
Anything that could help me improve it?
I am using postgres 10.
Thanks and Regards