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.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit


create table cust
(
	customerid int,
	status varchar(10),
	dt date
);

insert into cust values(1001,'Active','2021-01-01');
insert into cust values(1001,'Active','2021-03-01');
insert into cust values(1001,'Inactive','2021-01-30');
insert into cust values(1001,'Inactive','2021-04-01');
insert into cust values(1001,'Active','2021-09-01');
1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

WITH active_status
     AS (SELECT customerid,
                Row_number()
                  OVER (
                    partition BY customerid
                    ORDER BY start_date) id,
                start_date
         FROM   (SELECT customerid,
                        CASE
                          WHEN status = 'Active' THEN dt
                        END "start_date"
                 FROM   cust) x
         WHERE  start_date IS NOT NULL),
     inactive_status
     AS (SELECT customerid,
                Row_number()
                  OVER (
                    partition BY customerid
                    ORDER BY end_date) id,
                end_date
         FROM   (SELECT customerid,
                        CASE
                          WHEN status = 'Inactive' THEN dt
                        END "end_date"
                 FROM   cust) x
         WHERE  end_date IS NOT NULL)
SELECT acta.customerid,
       acta.start_date,
       COALESCE(inacta.end_date, '2099-12-31') end_date
FROM   active_status acta
       LEFT OUTER JOIN inactive_status inacta
                    ON acta.customerid = inacta.customerid
                       AND acta.id = inacta.id
ORDER  BY start_date; 


customerid
start_date
end_date


1001
2021-01-01
2021-01-30

1001
2021-03-01
2021-04-01

1001
2021-09-01
2099-12-31

customerid	start_date	end_date
1001	2021-01-01	2021-01-30
1001	2021-03-01	2021-04-01
1001	2021-09-01	2099-12-31