0

I have this this function which takes in an array of tsranges, and convert them into one array with tsranges, where each range contains all overlapping ranges from the input.

Version: 12

https://dbfiddle.uk/?rdbms=postgres_12&fiddle=0a9fc4e8657c8758e76122ade64a662a

create or replace function range_arr_merge(arr tsrange[]) returns tsrange[]
  language plpgsql
as 
$$
DECLARE
 cluster_array tsrange[]; 
 value tsrange;
 intermediate_cluster_range tsrange;
 counter integer := 0;
BEGIN
  FOREACH value IN ARRAY $1
  LOOP
    if array_length(cluster_array,1) = 0 then
     raise notice 'first iteration';
     cluster_array :=  array_append(cluster_array,value);
     counter := counter + 1;    
    end if;
    
    -- Check if current tsrange and recorded tsrange have points in common
    if value && cluster_array[counter] OR lower(value) = upper(cluster_array[counter]) then 
      intermediate_cluster_range := tsrange(lower(cluster_array[counter]), upper(value));
      cluster_array[counter] := intermediate_cluster_range;
    else
        cluster_array :=  array_append(cluster_array,value);
        counter := counter + 1; 
    end if; 


  END LOOP;
  return cluster_array;
END;
$$;

The problem with this function is that this is slow, it takes me 5 minutes to run this on my full dataset, and that is not optimal.

Is there any way I can optimize this?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.