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 events (Eventid int, start_date date,  end_date date,    City text);
insert into events
values
  (1, date '2015-02-01', date '2015-02-04', 'Chennai'),
  (2, date '2015-02-02', date '2015-02-05', 'Chennai'),
  (4, date '2015-02-04', date '2015-02-06', 'Mumbai'),
  (3, date '2015-02-03', date '2015-02-07', 'Chennai'),
  (6, date '2015-02-08', date '2015-02-10', 'Kolkata'),
  (5, date '2015-02-06', date '2015-02-09', 'Mumbai'),
  (7, date '2015-02-10', date '2015-02-11', 'Kolkata');
7 rows affected

with diffs as (
  select eventid, start_date, end_date, city,
         daterange(start_date, end_date, '[]') * lead(daterange(start_date, end_date, '[]')) over (partition by city order by start_date) as overlap
  from events
)
select eventid, start_date, end_date, city,
       upper(overlap) - lower(overlap) as overlapping_days
from diffs
order by city, start_date;


eventid
start_date
end_date
city
overlapping_days


1
2015-02-01
2015-02-04
Chennai
3

2
2015-02-02
2015-02-05
Chennai
3

3
2015-02-03
2015-02-07
Chennai
null

6
2015-02-08
2015-02-10
Kolkata
1

7
2015-02-10
2015-02-11
Kolkata
null

4
2015-02-04
2015-02-06
Mumbai
1

5
2015-02-06
2015-02-09
Mumbai
null

eventid	start_date	end_date	city	overlapping_days
1	2015-02-01	2015-02-04	Chennai	3
2	2015-02-02	2015-02-05	Chennai	3
3	2015-02-03	2015-02-07	Chennai	null
6	2015-02-08	2015-02-10	Kolkata	1
7	2015-02-10	2015-02-11	Kolkata	null
4	2015-02-04	2015-02-06	Mumbai	1
5	2015-02-06	2015-02-09	Mumbai	null