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 table sample(id integer, name varchar(100), timeint time);

insert into sample values(1, 'aaa', '00:00:01');
insert into sample values(1, 'aaa', '00:00:01');
insert into sample values(1, 'aaa', '00:00:02');
insert into sample values(1, 'aaa', '00:00:03');
insert into sample values(1, 'aaa', '00:00:04');
insert into sample values(2, 'bbb', '00:00:01');
insert into sample values(2,'bbb', '00:00:02');
insert into sample values(2, 'bbb', '00:00:03');
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

with recursive s as (
      select s.id, s.name, s.timeint, count(*) as cnt,
             row_number() over (partition by id order by timeint) as seqnum
      from sample s
      group by s.id, s.name, s.timeint
     ),
     cte as (
      select id, timeint, name, seqnum, timeint as start_timeint, cnt
      from s
      where seqnum = 1
      union all
      select s.id, s.timeint, s.name, s.seqnum,
             (case when s.timeint <= cte.start_timeint + interval '1 second'
                   then cte.start_timeint else s.timeint
              end),
             s.cnt
      from cte join
           s
           on s.id = cte.id and s.seqnum = cte.seqnum + 1
     )
select  id, name, start_timeint, sum(cnt)
from cte
group by id, name, start_timeint
order by id, start_timeint




id
name
start_timeint
sum


1
aaa
00:00:01
3

1
aaa
00:00:03
2

2
bbb
00:00:01
2

2
bbb
00:00:03
1


      select distinct s.*, rank() over (partition by id order by timeint) as seqnum
      from sample s
      order by id, timeint


id
name
timeint
seqnum


1
aaa
00:00:01
1

1
aaa
00:00:02
3

1
aaa
00:00:03
4

1
aaa
00:00:04
5

2
bbb
00:00:01
1

2
bbb
00:00:02
2

2
bbb
00:00:03
3

id	name	timeint	seqnum
1	aaa	00:00:01	1
1	aaa	00:00:02	3
1	aaa	00:00:03	4
1	aaa	00:00:04	5
2	bbb	00:00:01	1
2	bbb	00:00:02	2
2	bbb	00:00:03	3