I have a table
obj_param_value_log with the following structure:
object_id: int param_id: int date: date value: real
So, it stores values captured on a daily basis for different parameters that are related to objects (m2m relationship). Objects have different sets of params and some object params may have gaps in values for certain dates.
Now, I need to build a query that will find objects matching the following sample criteria:
param1_value as of two days ago > param2_value as of week low and sustained within past week
So, it finds all objects where the value of param1 taken 2 days in the past was greater than the minimum value of param2 calculated over the last 7 days and this condition is true today and was true on each of 6 previous days.
So far, the best query I could come up with is as follows:
select opvl_exist.object_id from obj_param_value_log opvl_exist where (select count(distinct opvl_sust1.date) from obj_param_value_log opvl_sust1 /*count dates from inner join to account for possible gaps in values for either parameter*/ inner join obj_param_value_log opvl_sust2 on opvl_sust1.object_id=opvl_sust2.object_id where opvl_sust1.param_id=2 /*define param1*/ and opvl_sust2.param_id=32 /*define param2*/ and opvl_sust1.date between current_date - integer '2' - integer '6' and current_date - integer '2' /*set a lookup period for param1 from a week and 2 days ago to just 2 days ago*/ and opvl_sust2.date=(select min(opvl_sust_sq.value) from obj_param_value_log opvl_sust_sq where opvl_sust2.object_id=opvl_sust_sq.object_id and opvl_sust_sq.param_id=32 and opvl_sust_sq.date between opvl_sust1.date - integer '4' and opvl_sust1.date + integer '2') /*set a relative lookup period to calc the min value for param2 form 4 days before param1 date to 2 days ahead param1 date*/ and opvl_sust2.date>=current_date - integer '6' - integer '6' /*set a lookup period for param2 from today to a week ago (min value lookup) and another week ago (condition sustained period)*/ and opvl_exist.object_id=opvl_sust1.object_id) = (select count(distinct opvl1.date) from obj_param_value_log opvl1 /*count dates where filter condition is true*/ inner join obj_param_value_log opvl2 on opvl1.object_id=opvl2.object_id where opvl1.param_id=2 /*define param1*/ and opvl2.param_id=32 /*define param2*/ and opvl1.date between current_date - integer '2' - integer '6' and current_date - integer '2' /*set a lookup period for param1 from a week and 2 days ago to just 2 days ago*/ and opvl2.value=(select min(opvl_sq.value) from obj_param_value_log opvl_sq where opvl2.object_id=opvl_sq.object_id and opvl_sq.param_id=32 and opvl_sq.date between opvl1.date - integer '4' and opvl1.date + integer '2') /*set a relative lookup period to calc the min value for param2 form 4 days before param1 date to 2 days ahead param1 date*/ and opvl2.date>=current_date - integer '6' - integer '6' /*set a lookup period for param2 from today to a week ago (min value lookup) and another week ago (condition sustained period)*/ and opvl1.value>opvl2.value /*set condition param1 value > param2 value*/ and opvl_exist.object_id=opvl1.object_id)
The logic behind the query is to compare two sets of dates - the one where there are values for params matching the original condition with the other one where the params and their values meet the condition. If these sets are the same, it means the condition is met for all the relevant dates (i.e. the dates within the lookup range that have the right params with values).
This seems to be finding what I am looking for, but there are serious performance concerns as the query already takes around a minute to run on about 100k rows and I am planning for millions rows.
Looking for both query optimization suggestions and index recommendations. So far I have separate indices on
I am still at the design phase, so if you see a way to significantly improve the performance by revisiting the data structure, I'd be glad to explore this route.
Let me know if you have any questions or would like to get any additional details to help you come up with ideas. Any input is appreciated!
Even though I've got a good answer on how to greatly simplify and allegedly improve performance of my query, I wanted to ask for more DBA input to see if the described business logic is implemented in the most efficient manner and find more options for increasing the performance.
P.S. My own tests revealed that a compound index on (
date) has a positive effect on the overall performance.