Postgres 16

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 logs_bl_sj_timerange
(   bundesland text,
    startdate  timestamp,
    enddate    timestamp
);
insert into logs_bl_sj_timerange (bundesland, startdate, enddate)
values ('Hessen', '2015-02-26 16:22:21', '2015-02-26 16:31:31'),
       ('Hessen', '2015-10-20 22:34:54', '2015-10-20 22:35:03'),
       ('Bremen', '2015-10-20 22:35:50', '2015-10-20 22:37:03'),
       ('Hessen', '2015-02-26 11:22:21', '2015-02-26 16:31:31'),
       ('Hessen', '2015-10-20 21:34:54', '2015-10-20 22:35:03'),
       ('Bremen', '2015-10-21 22:35:50', '2015-10-21 22:37:03')
returning *;

alter table logs_bl_sj_timerange 
  add column time_range tsrange generated always as
   (tsrange(startdate,enddate,'[)')) stored,
  add column id bigint generated by default as identity primary key;

CREATE TABLE




bundesland
startdate
enddate


Hessen
2015-02-26 16:22:21
2015-02-26 16:31:31

Hessen
2015-10-20 22:34:54
2015-10-20 22:35:03

Bremen
2015-10-20 22:35:50
2015-10-20 22:37:03

Hessen
2015-02-26 11:22:21
2015-02-26 16:31:31

Hessen
2015-10-20 21:34:54
2015-10-20 22:35:03

Bremen
2015-10-21 22:35:50
2015-10-21 22:37:03



INSERT 0 6



ALTER TABLE


--OP
SELECT bundesland, 
       startdate, 
       COUNT(*) 
         FILTER(WHERE time_range @> startdate::timestamp) 
         OVER(PARTITION BY bundesland)
FROM logs_bl_sj_timerange
order by 1,2,3


bundesland
startdate
count


Bremen
2015-10-20 22:35:50
2

Bremen
2015-10-21 22:35:50
2

Hessen
2015-02-26 11:22:21
4

Hessen
2015-02-26 16:22:21
4

Hessen
2015-10-20 21:34:54
4

Hessen
2015-10-20 22:34:54
4



SELECT 6


--self-join
select a.*,count(*)
from logs_bl_sj_timerange a
join logs_bl_sj_timerange b 
  on a.bundesland=b.bundesland
 and b.time_range @> a.startdate
group by a.id
order by 1,2,3;


bundesland
startdate
enddate
time_range
id
count


Bremen
2015-10-20 22:35:50
2015-10-20 22:37:03
["2015-10-20 22:35:50","2015-10-20 22:37:03")
3
1

Bremen
2015-10-21 22:35:50
2015-10-21 22:37:03
["2015-10-21 22:35:50","2015-10-21 22:37:03")
6
1

Hessen
2015-02-26 11:22:21
2015-02-26 16:31:31
["2015-02-26 11:22:21","2015-02-26 16:31:31")
4
1

Hessen
2015-02-26 16:22:21
2015-02-26 16:31:31
["2015-02-26 16:22:21","2015-02-26 16:31:31")
1
2

Hessen
2015-10-20 21:34:54
2015-10-20 22:35:03
["2015-10-20 21:34:54","2015-10-20 22:35:03")
5
1

Hessen
2015-10-20 22:34:54
2015-10-20 22:35:03
["2015-10-20 22:34:54","2015-10-20 22:35:03")
2
2



SELECT 6


--correlated scalar subquery
select *,(select count(*)
          from logs_bl_sj_timerange s2
          where s1.bundesland = s2.bundesland
            and s1.startdate >= s2.startdate
            and s1.startdate < s2.enddate)
from logs_bl_sj_timerange s1
order by 1,2,3;


bundesland
startdate
enddate
time_range
id
count


Bremen
2015-10-20 22:35:50
2015-10-20 22:37:03
["2015-10-20 22:35:50","2015-10-20 22:37:03")
3
1

Bremen
2015-10-21 22:35:50
2015-10-21 22:37:03
["2015-10-21 22:35:50","2015-10-21 22:37:03")
6
1

Hessen
2015-02-26 11:22:21
2015-02-26 16:31:31
["2015-02-26 11:22:21","2015-02-26 16:31:31")
4
1

Hessen
2015-02-26 16:22:21
2015-02-26 16:31:31
["2015-02-26 16:22:21","2015-02-26 16:31:31")
1
2

Hessen
2015-10-20 21:34:54
2015-10-20 22:35:03
["2015-10-20 21:34:54","2015-10-20 22:35:03")
5
1

Hessen
2015-10-20 22:34:54
2015-10-20 22:35:03
["2015-10-20 22:34:54","2015-10-20 22:35:03")
2
2



SELECT 6


--Cetin Basoz
--https://stackoverflow.com/a/78341505/5298879
select *
from logs_bl_sj_timerange s1,
     lateral (select count(*)
              from logs_bl_sj_timerange s2
              where s1.bundesland = s2.bundesland
                and s1.startdate >= s2.startdate
                and s1.startdate < s2.enddate) as cnt
order by 1,2,3;


bundesland
startdate
enddate
time_range
id
count


Bremen
2015-10-20 22:35:50
2015-10-20 22:37:03
["2015-10-20 22:35:50","2015-10-20 22:37:03")
3
1

Bremen
2015-10-21 22:35:50
2015-10-21 22:37:03
["2015-10-21 22:35:50","2015-10-21 22:37:03")
6
1

Hessen
2015-02-26 11:22:21
2015-02-26 16:31:31
["2015-02-26 11:22:21","2015-02-26 16:31:31")
4
1

Hessen
2015-02-26 16:22:21
2015-02-26 16:31:31
["2015-02-26 16:22:21","2015-02-26 16:31:31")
1
2

Hessen
2015-10-20 21:34:54
2015-10-20 22:35:03
["2015-10-20 21:34:54","2015-10-20 22:35:03")
5
1

Hessen
2015-10-20 22:34:54
2015-10-20 22:35:03
["2015-10-20 22:34:54","2015-10-20 22:35:03")
2
2



SELECT 6

bundesland	startdate	enddate
Hessen	2015-02-26 16:22:21	2015-02-26 16:31:31
Hessen	2015-10-20 22:34:54	2015-10-20 22:35:03
Bremen	2015-10-20 22:35:50	2015-10-20 22:37:03
Hessen	2015-02-26 11:22:21	2015-02-26 16:31:31
Hessen	2015-10-20 21:34:54	2015-10-20 22:35:03
Bremen	2015-10-21 22:35:50	2015-10-21 22:37:03