Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE OR REPLACE FUNCTION array_sort (ANYARRAY)
RETURNS ANYARRAY LANGUAGE SQL
AS $$
SELECT ARRAY(
    SELECT $1[s.i] AS "foo"
    FROM
        generate_series(array_lower($1,1), array_upper($1,1)) AS s(i)
    ORDER BY foo
);
$$;
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;
$$;
CREATE TABLE DOB (date tsrange );
INSERT into DOB (date) values 
(tsrange('1994-03-27 00:00:00','1994-10-30 00:00:00')), 
(tsrange('1994-10-30 00:00:00','1995-03-26 00:00:00')), 
(tsrange('1990-01-01 00:00:00','1990-01-02 00:00:00')), 
(tsrange('1996-03-31 00:00:00','1996-10-27 00:00:00'))
returning *;


date


["1994-03-27 00:00:00","1994-10-30 00:00:00")

["1994-10-30 00:00:00","1995-03-26 00:00:00")

["1990-01-01 00:00:00","1990-01-02 00:00:00")

["1996-03-31 00:00:00","1996-10-27 00:00:00")


select range_arr_merge(array_sort(array_agg(date))) from DOB


range_arr_merge


{"["1990-01-01 00:00:00","1990-01-02 00:00:00")","["1994-03-27 00:00:00","1995-03-26 00:00:00")","["1996-03-31 00:00:00","1996-10-27 00:00:00")"}