Postgres 18

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 name, concat_ws('-',lower(r),nullif(upper(r)-1,lower(r))) as range
from(select name, range_agg(int8range(index,index+1))as mr
     from dat
     group by 1)_
cross join unnest(mr) as r
order by r;

name
range

null
1-2

foo
3-5

null
6-8

bar
9-10

null
11

foo
12-13

SELECT 6

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

create index idx1_i on dat(index)include(name)with(fillfactor=100);
create index idx2_ni on dat(name,index)with(fillfactor=100);
create index idx3_n on dat(name)include(index)with(fillfactor=100);

setseed

SELECT 1

INSERT 0 49985

CREATE INDEX

CREATE INDEX

CREATE INDEX

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=4084.80..4085.30 rows=200 width=56) (actual time=158.281..161.344 rows=23858.00 loops=1)

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

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

  Sort Method: quicksort  Memory: 1887kB

  Buffers: shared hit=175

  ->  HashAggregate  (cost=4075.15..4077.15 rows=200 width=56) (actual time=136.156..145.729 rows=23858.00 loops=1)

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

        Group Key: sum(a.isgap) OVER w1

        Batches: 1  Memory Usage: 2593kB

        Buffers: shared hit=175

        ->  WindowAgg  (cost=0.40..3075.19 rows=49998 width=24) (actual time=0.045..111.092 rows=49998.00 loops=1)

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

              Window: w1 AS (ORDER BY a.index)

              Storage: Memory  Maximum Storage: 17kB

              Buffers: shared hit=175

              ->  Subquery Scan on a  (cost=0.33..2325.22 rows=49998 width=16) (actual time=0.033..63.455 rows=49998.00 loops=1)

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

                    Buffers: shared hit=175

                    ->  WindowAgg  (cost=0.33..2325.22 rows=49998 width=16) (actual time=0.031..55.347 rows=49998.00 loops=1)

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

                          Window: w1 AS (ORDER BY src.index)

                          Storage: Memory  Maximum Storage: 17kB

                          Buffers: shared hit=175

                          ->  Index Only Scan using idx1_i on public.dat src  (cost=0.29..1450.26 rows=49998 width=12) (actual time=0.018..11.513 rows=49998.00 loops=1)

                                Output: src.index, src.name

                                Heap Fetches: 0

                                Index Searches: 1

                                Buffers: shared hit=175

Planning:

  Buffers: shared hit=18 read=2

Planning Time: 1.035 ms

Execution Time: 163.242 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=2992.89..2994.39 rows=600 width=52) (actual time=98.379..101.984 rows=23858.00 loops=1)

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

  Sort Key: (min(dat.index))

  Sort Method: quicksort  Memory: 1960kB

  Buffers: shared hit=175

  ->  HashAggregate  (cost=2950.20..2965.20 rows=600 width=52) (actual time=68.905..84.986 rows=23858.00 loops=1)

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

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

        Batches: 1  Memory Usage: 2073kB

        Buffers: shared hit=175

        ->  WindowAgg  (cost=0.34..2450.22 rows=49998 width=20) (actual time=0.027..42.085 rows=49998.00 loops=1)

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

              Window: w1 AS (PARTITION BY dat.name ORDER BY dat.index ROWS UNBOUNDED PRECEDING)

              Storage: Memory  Maximum Storage: 17kB

              Buffers: shared hit=175

              ->  Index Only Scan using idx2_ni on public.dat  (cost=0.29..1450.26 rows=49998 width=12) (actual time=0.019..10.417 rows=49998.00 loops=1)

                    Output: dat.name, dat.index

                    Heap Fetches: 0

                    Index Searches: 1

                    Buffers: shared hit=175

Planning:

  Buffers: shared hit=16 read=1

Planning Time: 0.490 ms

Execution Time: 106.105 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=1264.86..1265.61 rows=300 width=36) (actual time=163.639..165.609 rows=23858.00 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: 1794kB

  Buffers: shared hit=251

  ->  ProjectSet  (cost=1250.96..1252.52 rows=300 width=36) (actual time=65.366..83.991 rows=23858.00 loops=1)

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

        Buffers: shared hit=251

        ->  HashAggregate  (cost=1250.96..1251.00 rows=3 width=36) (actual time=65.355..78.816 rows=4.00 loops=1)

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

              Group Key: dat.name

              Batches: 1  Memory Usage: 2649kB

              Buffers: shared hit=251

              ->  Seq Scan on public.dat  (cost=0.00..750.98 rows=49998 width=12) (actual time=0.010..5.172 rows=49998.00 loops=1)

                    Output: dat.name, dat.index

                    Buffers: shared hit=251

Planning:

  Buffers: shared hit=2

Planning Time: 0.178 ms

Execution Time: 167.492 ms

EXPLAIN

create table timings(variant text,duration interval);

do $d$
declare 
  i int; 
  loops int:=20; 
  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

lukasz_szozda_1cte
00:00:00.130293
00:00:00.057382
00:00:00.425913
00:00:02.605853
00:00:00.112374
00:00:00.057382

ranges
00:00:00.222294
00:00:00.101238
00:00:00.968165
00:00:04.445882
00:00:00.211827
00:00:00.101238

gaps_islands_valnik
00:00:00.224358
00:00:00.096932
00:00:00.725021
00:00:04.487155
00:00:00.186465
00:00:00.096932

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
lukasz_szozda_1cte	00:00:00.130293	00:00:00.057382	00:00:00.425913	00:00:02.605853	00:00:00.112374	00:00:00.057382
ranges	00:00:00.222294	00:00:00.101238	00:00:00.968165	00:00:04.445882	00:00:00.211827	00:00:00.101238
gaps_islands_valnik	00:00:00.224358	00:00:00.096932	00:00:00.725021	00:00:04.487155	00:00:00.186465	00:00:00.096932