Postgres 9.5

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 test (
  colony INTEGER,
  date_check date,
  protection text
);

INSERT INTO test
  (colony, date_check, protection)
VALUES
  (7, '2019-10-12', 'Y'),
  (7, '2019-10-13', 'Y'),
  (7, '2019-10-14', 'Y'),
  (7, '2019-10-15', 'Y'),
  (7, '2019-10-16', 'Y'),
  (7, '2019-10-17', 'Y'),
  (7, '2019-10-20', 'N'),
  (7, '2019-10-21', 'N'),
  (7, '2019-10-22', 'N'),
  (7, '2019-10-23', 'Y'),
  (7, '2019-10-24', 'Y'),
  (7, '2019-10-25', 'Y'),
  (7, '2019-10-26', 'Y'),
  (7, '2019-10-27', 'N'),
  (7, '2019-11-01', 'Y'),
  (7, '2019-11-04', 'Y'),
  (7, '2019-11-05', 'N'),
  (2, '2019-10-26', 'N'),
  (2, '2019-10-27', 'N'),
  (2, '2019-11-01', 'Y'),
  (2, '2019-11-03', 'Y'),
  (2, '2019-11-05', 'N') ;
22 rows affected

CREATE TABLE results (
  colony INTEGER,
  date_in date,
  date_out date
);

INSERT INTO results
  (colony, date_in, date_out )
VALUES
(2,'2019-11-01','2019-11-03'),
(7,'2019-10-12','2019-10-17'),
(7,'2019-10-23','2019-10-26'),
(7,'2019-11-01','2019-11-04');

SELECT * from results;
4 rows affected



colony
date_in
date_out


2
2019-11-01
2019-11-03

7
2019-10-12
2019-10-17

7
2019-10-23
2019-10-26

7
2019-11-01
2019-11-04


SELECT distinct colony, min(date_check), max(date_check)
from test
where protection ='Y'
group by colony


colony
min
max


7
2019-10-12
2019-11-04

2
2019-11-01
2019-11-03


WITH cte AS
(
SELECT 
    colony, date_check, -- protection,
    protection = 'Y' AND
     ( LAG(protection) OVER (PARTITION BY colony ORDER BY date_check) = 'N' 
    OR LAG(protection) OVER (PARTITION BY colony ORDER BY date_check) IS NULL 
     )  AS island_start,
    protection = 'Y' AND
     ( LEAD(protection) OVER (PARTITION BY colony ORDER BY date_check) = 'N'
    OR LEAD(protection) OVER (PARTITION BY colony ORDER BY date_check) IS NULL
     )  AS island_end
FROM  test
),
cte2 AS
(
SELECT colony, date_check, 
    (SUM(island_start::int) OVER (PARTITION BY colony ORDER BY date_check) 
     + SUM(island_end::int) OVER (PARTITION BY colony ORDER BY date_check) - 1
    ) / 2 + 1   AS grp
FROM cte
WHERE island_start OR island_end
)
SELECT 
    colony, grp,
    MIN(date_check) AS date_in,
    MAX(date_check) AS date_out
FROM cte2
GROUP BY colony, grp
ORDER by colony, date_in ;


colony
grp
date_in
date_out


2
1
2019-11-01
2019-11-03

7
1
2019-10-12
2019-10-17

7
2
2019-10-23
2019-10-26

7
3
2019-11-01
2019-11-04

colony	date_in	date_out
2	2019-11-01	2019-11-03
7	2019-10-12	2019-10-17
7	2019-10-23	2019-10-26
7	2019-11-01	2019-11-04