Postgres 13

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?.

select version();


version


PostgreSQL 13.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit


create table lights (
  flat int not null,
  light int not null,
  start timestamp not null,
  end_ timestamp not null,
  on_ int not null
);

insert into lights values
(1, 1,   '01/01/2021 00:00:15', '01/01/2021 00:59:00', 1),
(1, 2,   '01/01/2021 00:00:15', '01/01/2021 01:59:00', 1),
(2, 1,   '01/01/2021 00:00:15', '01/01/2021 00:01:15', 1),
(2, 1,   '01/01/2021 00:02:00', '01/01/2021 01:00:00', 1),
(2, 2,   '01/01/2021 00:00:00', '01/01/2021 08:00:00', 1);
5 rows affected

with times as (
  select flat, start as ts from lights
  union
  select flat, end_ from lights
), all_ivals as (
  select flat, ts as start, 
         lead(ts) over (partition by flat 
                            order by ts) as end_
    from times
)
select a.flat, a.start, a.end_, count(*) as lights_on
  from all_ivals a
       join lights l
         on tsrange(l.start, l.end_, '[)') && tsrange(a.start, a.end_, '[)')
        and l.flat = a.flat
 where a.end_ is not null
 group by a.flat, a.start, a.end_
 order by a.flat, a.start
 ;


flat
start
end_
lights_on


1
2021-01-01 00:00:15
2021-01-01 00:59:00
2

1
2021-01-01 00:59:00
2021-01-01 01:59:00
1

2
2021-01-01 00:00:00
2021-01-01 00:00:15
1

2
2021-01-01 00:00:15
2021-01-01 00:01:15
2

2
2021-01-01 00:01:15
2021-01-01 00:02:00
1

2
2021-01-01 00:02:00
2021-01-01 01:00:00
2

2
2021-01-01 01:00:00
2021-01-01 08:00:00
1

flat	start	end_	lights_on
1	2021-01-01 00:00:15	2021-01-01 00:59:00	2
1	2021-01-01 00:59:00	2021-01-01 01:59:00	1
2	2021-01-01 00:00:00	2021-01-01 00:00:15	1
2	2021-01-01 00:00:15	2021-01-01 00:01:15	2
2	2021-01-01 00:01:15	2021-01-01 00:02:00	1
2	2021-01-01 00:02:00	2021-01-01 01:00:00	2
2	2021-01-01 01:00:00	2021-01-01 08:00:00	1