Postgres 9.6

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 tbl (vendor text, auth text, StartDate Date, EndDate Date);

CREATE TABLE


insert into tbl values
('Vendor 1', '#####', '20170101', '20170228'),
('Vendor 1', '#####', '20170301', '20170415'),
('Vendor 2', '#####', '20170416', '20170531'),
('Vendor 2', '#####', '20170601', '20170712'),
('Vendor 1', '#####', '20170703', '20170930');

INSERT 0 5


select vendor, min(startdate) as startdate, max(enddate) as enddate, grp
from (
      select vendor, auth, startdate, enddate, 
             sum(rst) over (order by vendor, startdate) as grp
      from (
             select vendor, auth, startdate, enddate, 
                    case when coalesce(lag(enddate) over (partition by vendor order by vendor, startdate), startdate) + 1 <> startdate then 1 end rst
             from   tbl
           ) t1
     ) t2
group by grp, vendor
order by startdate


vendor
startdate
enddate
grp


Vendor 1
2017-01-01
2017-04-15
1

Vendor 2
2017-04-16
2017-07-12
3

Vendor 1
2017-07-03
2017-09-30
2



SELECT 3

vendor	startdate	enddate	grp
Vendor 1	2017-01-01	2017-04-15	1
Vendor 2	2017-04-16	2017-07-12	3
Vendor 1	2017-07-03	2017-09-30	2