Optimizing a query comparing inner joins of a table to itself

Question

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 object_id, param_id and date.

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!

Additional information

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 (object_id, param_id, date) has a positive effect on the overall performance.

Answer 1

This is not an attempt at a different implementation of the same logic. This is merely an attempt at simplifying (and, hopefully, speeding up) the existing query.

What I have noticed is that the two correlated subqueries that are being compared have just one difference. The second subquery has an extra condition in its where clause, namely this one:

and opvl1.value>opvl2.value

Given that this is the only difference, it is possible to calculate both count(distinct) results using just one such subquery. You just need to move that extra condition inside the second count(), like this:

count(distinct case when opvl1.value>opvl2.value then opvl1.date end)

The comparison itself would happen in the subquery's select clause:

select ... /* main query */
where
  (
    select
      count(distinct opvl1.date) =
      count(distinct case when opvl1.value>opvl2.value then opvl1.date end)
    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 opvl_exist.object_id=opvl1.object_id
  )
;

There may still be room for further improvement but this could be a start.

Answer 2

Upon experimenting with differenet implementation options and trying various indices, I believe I've landed on a decent solution that proved its performance on ~10mil rows:

select opj_dict.object_id from obj_dict where exists (
select 1 from from obj_param_value_log opvl_exist
where opvl_exist.param_id=2 /*limit the scope of the exist query to param1*/
and opvl_exist.date between current_date - integer '2' - integer '6' and current_date - integer '2' /*limit the scope of the exist query to the lookup period of param1*/
group by opvl_exist.object_id
having count(distinct opvl_exist.date) = (
select count(distinct opvl1.date)
from obj_param_value_log opvl1
inner join obj_param_value_log opvl2 on opvl1.object_id=opvl2.object_id
where opvl1.param_id=2 /*set param1*/
and   opvl2.param_id=32 /*set 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
and opvl_exist.object_id=opvl1.object_id)
and obj_dict.object_id=opvl_exist.object_id)

This works under 1s upon creating the following indices on obj_param_value_log (the column order is important):

• create index date_objid_paramid on obj_param_value_log (date, object_id, param_id);
• create index objid_paramid_date_val on obj_param_value_log (object_id, param_id, date, value);

This final query turned to be a hard one for me, so hope my efforts on solving this help someone finding themselves in a similar struggle.