Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table dat as SELECT *
FROM unnest(ARRAY[
        NULL, NULL,
        'foo', 'foo', 'foo',
        NULL, NULL, NULL,
        'bar', 'bar',
        NULL,
        'foo',
        'foo'
    ]) WITH ORDINALITY _(name, index);

SELECT 13

select name,unnest(r)as range
from(select name,range_agg(int8range(index,index+1))as r
     from dat
     group by 1)_
order by 2;

name
range

null
[1,3)

foo
[3,6)

null
[6,9)

bar
[9,11)

null
[11,12)

foo
[12,14)

SELECT 6

select setseed(.42);
insert into dat
select nullif(.6>random(),false) as name
     , n as index
from generate_series(16,3e4)n;

setseed

SELECT 1

INSERT 0 29985

vacuum analyze dat;

VACUUM

prepare gaps_islands_valnik as 
SELECT groupNum,coalesce(min(name),'null') name,min(index) startI,max(index) endI
FROM(
  SELECT *
    ,sum(isGap)OVER(order by index) groupNum
  FROM(
    SELECT *
      ,CASE WHEN name IS DISTINCT FROM lag(name)OVER(order by index) THEN 1 ELSE 0 END isGap
    FROM dat as src
  )a
)b
GROUP BY groupNum
ORDER BY groupNum;

explain analyze verbose execute gaps_islands_valnik;

PREPARE

QUERY PLAN

Sort  (cost=4641.23..4641.73 rows=200 width=56) (actual time=689.590..691.037 rows=14286 loops=1)

  Output: (sum(a.isgap) OVER (?)), (COALESCE(min(a.name), 'null'::text)), (min(a.index)), (max(a.index))

  Sort Key: (sum(a.isgap) OVER (?))

  Sort Method: quicksort  Memory: 1389kB

  ->  HashAggregate  (cost=4631.59..4633.59 rows=200 width=56) (actual time=561.047..680.951 rows=14286 loops=1)

        Output: (sum(a.isgap) OVER (?)), COALESCE(min(a.name), 'null'::text), min(a.index), max(a.index)

        Group Key: sum(a.isgap) OVER (?)

        Batches: 1  Memory Usage: 2849kB

        ->  WindowAgg  (cost=2681.72..4031.63 rows=29998 width=24) (actual time=8.748..283.533 rows=29998 loops=1)

              Output: a.name, a.index, NULL::integer, sum(a.isgap) OVER (?)

              ->  Subquery Scan on a  (cost=2681.72..3581.66 rows=29998 width=16) (actual time=8.738..36.075 rows=29998 loops=1)

                    Output: a.index, a.name, a.isgap

                    ->  WindowAgg  (cost=2681.72..3281.68 rows=29998 width=16) (actual time=8.737..31.543 rows=29998 loops=1)

                          Output: src.name, src.index, CASE WHEN (src.name IS DISTINCT FROM lag(src.name) OVER (?)) THEN 1 ELSE 0 END

                          ->  Sort  (cost=2681.72..2756.71 rows=29998 width=12) (actual time=8.710..11.618 rows=29998 loops=1)

                                Output: src.index, src.name

                                Sort Key: src.index

                                Sort Method: quicksort  Memory: 2315kB

                                ->  Seq Scan on public.dat src  (cost=0.00..450.98 rows=29998 width=12) (actual time=0.012..4.011 rows=29998 loops=1)

                                      Output: src.index, src.name

Planning Time: 0.239 ms

Execution Time: 691.709 ms

EXPLAIN

prepare lukasz_szozda_1cte as
with src as (
  select*,index-row_number()over w1 as grp
  from dat
  window w1 as(partition by name order by index))
select name
     , min(index) || '-' || max(index) as range
   --, concat_ws('-',min(index),nullif(max(index),min(index))) as range
from src
group by name, grp
order by min(index);

explain analyze verbose execute lukasz_szozda_1cte; 

PREPARE

QUERY PLAN

Sort  (cost=3999.32..4000.82 rows=600 width=52) (actual time=682.492..683.873 rows=14286 loops=1)

  Output: dat.name, ((((min(dat.index))::text || '-'::text) || (max(dat.index))::text)), (min(dat.index)), ((dat.index - row_number() OVER (?)))

  Sort Key: (min(dat.index))

  Sort Method: quicksort  Memory: 1424kB

  ->  HashAggregate  (cost=3956.63..3971.63 rows=600 width=52) (actual time=598.622..606.029 rows=14286 loops=1)

        Output: dat.name, (((min(dat.index))::text || '-'::text) || (max(dat.index))::text), min(dat.index), ((dat.index - row_number() OVER (?)))

        Group Key: dat.name, (dat.index - row_number() OVER (?))

        Batches: 1  Memory Usage: 2321kB

        ->  WindowAgg  (cost=2681.72..3356.67 rows=29998 width=20) (actual time=358.105..586.771 rows=29998 loops=1)

              Output: dat.name, dat.index, (dat.index - row_number() OVER (?))

              ->  Sort  (cost=2681.72..2756.71 rows=29998 width=12) (actual time=358.088..429.099 rows=29998 loops=1)

                    Output: dat.name, dat.index

                    Sort Key: dat.name, dat.index

                    Sort Method: quicksort  Memory: 2315kB

                    ->  Seq Scan on public.dat  (cost=0.00..450.98 rows=29998 width=12) (actual time=0.045..3.011 rows=29998 loops=1)

                          Output: dat.name, dat.index

Planning Time: 0.301 ms

Execution Time: 685.597 ms

EXPLAIN

prepare ranges as
select name,unnest(r)as range
from(select name,range_agg(int8range(index,index+1))as r
     from dat
     group by 1)_
order by 2;

explain analyze verbose execute ranges;

PREPARE

QUERY PLAN

Sort  (cost=764.89..765.64 rows=300 width=36) (actual time=429.208..498.180 rows=14286 loops=1)

  Output: dat.name, (unnest((range_agg(int8range(dat.index, (dat.index + 1))))))

  Sort Key: (unnest((range_agg(int8range(dat.index, (dat.index + 1))))))

  Sort Method: quicksort  Memory: 1333kB

  ->  ProjectSet  (cost=750.96..752.55 rows=300 width=36) (actual time=157.392..235.723 rows=14286 loops=1)

        Output: dat.name, unnest((range_agg(int8range(dat.index, (dat.index + 1)))))

        ->  HashAggregate  (cost=750.96..751.00 rows=3 width=36) (actual time=157.383..233.188 rows=4 loops=1)

              Output: dat.name, range_agg(int8range(dat.index, (dat.index + 1)))

              Group Key: dat.name

              Batches: 1  Memory Usage: 1985kB

              ->  Seq Scan on public.dat  (cost=0.00..450.98 rows=29998 width=12) (actual time=0.013..2.817 rows=29998 loops=1)

                    Output: dat.name, dat.index

Planning Time: 0.117 ms

Execution Time: 499.318 ms

EXPLAIN

create table timings(variant text,duration interval);

do $d$
declare 
  i int; 
  loops int:=7; 
  started timestamp; 
  finished timestamp;
  variant text; 
  variants text[]:='{gaps_islands_valnik,lukasz_szozda_1cte,ranges}';
begin
   for i in 1..loops loop
     foreach variant in array variants loop
       started:=clock_timestamp();
       execute format('execute %s;',variant);
       finished:=clock_timestamp();
       insert into timings values(variant,finished-started);
     end loop;
   end loop;
end $d$;

select variant
      ,avg(duration) 
      ,min(duration) 
      ,max(duration) 
      ,sum(duration) 
      ,make_interval(secs=>stddev(extract(epoch from duration))) as stddev
      ,mode()within group(order by duration) as mode
from timings group by 1 order by 2;

CREATE TABLE

DO

variant
avg
min
max
sum
stddev
mode

ranges
00:00:00.513552
00:00:00.406988
00:00:00.639515
00:00:03.594861
00:00:00.077809
00:00:00.406988

gaps_islands_valnik
00:00:00.598761
00:00:00.484624
00:00:00.872527
00:00:04.191326
00:00:00.131729
00:00:00.484624

lukasz_szozda_1cte
00:00:00.717282
00:00:00.484675
00:00:01.146719
00:00:05.020975
00:00:00.223619
00:00:00.484675

SELECT 3
name	range
null	[1,3)
foo	[3,6)
null	[6,9)
bar	[9,11)
null	[11,12)
foo	[12,14)
variant	avg	min	max	sum	stddev	mode
ranges	00:00:00.513552	00:00:00.406988	00:00:00.639515	00:00:03.594861	00:00:00.077809	00:00:00.406988
gaps_islands_valnik	00:00:00.598761	00:00:00.484624	00:00:00.872527	00:00:04.191326	00:00:00.131729	00:00:00.484624
lukasz_szozda_1cte	00:00:00.717282	00:00:00.484675	00:00:01.146719	00:00:05.020975	00:00:00.223619	00:00:00.484675