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 cluster_info (cluster_id uuid, version text, date timestamp);
INSERT INTO cluster_info (cluster_id, version, date)
VALUES
('0f4ce21e-0d08-11ec-b209-0242c0a8c004', '6.0.4', '2019-06-19 14:15:05'),
('0f4ce21e-0d08-11ec-b209-0242c0a8c004', NULL , '2019-08-28 12:33:15'), -- unknown version
('0f4ce21e-0d08-11ec-b209-0242c0a8c004', '6.7.3', '2019-10-18 08:05:36'),
('0f4ce21e-0d08-11ec-b209-0242c0a8c004', '6.7.3', '2019-11-28 12:33:15'),
('0f4ce21e-0d08-11ec-b209-0242c0a8c004', '6.7.4', '2020-03-24 08:30:57'),
('0f4ce21e-0d08-11ec-b209-0242c0a8c004', '6.7.4', '2020-07-01 09:01:48'),
('0f4ce21e-0d08-11ec-b209-0242c0a8c004', '6.7.4', '2020-07-31 12:47:44'),
('0f4ce21e-0d08-11ec-b209-0242c0a8c004', '6.7.3', '2020-08-28 12:33:15'), -- downgrade
('0f4ce21e-0d08-11ec-b209-0242c0a8c004', '6.7.9', '2021-06-23 08:57:05');
9 rows affected
SELECT min(version) AS version, min(date), max(date)
FROM (
SELECT version, date
, count(*) FILTER (WHERE step IS NOT FALSE) OVER (ORDER BY date) AS grp
FROM (
SELECT version, date
, lag(version) OVER (ORDER BY date) <> version AS step
FROM cluster_info
WHERE cluster_id = '0f4ce21e-0d08-11ec-b209-0242c0a8c004'
ORDER BY date
) sub1
) sub2
GROUP BY grp;
| version | min | max |
|---|---|---|
| 6.0.4 | 2019-06-19 14:15:05 | 2019-06-19 14:15:05 |
| null | 2019-08-28 12:33:15 | 2019-08-28 12:33:15 |
| 6.7.3 | 2019-10-18 08:05:36 | 2019-11-28 12:33:15 |
| 6.7.4 | 2020-03-24 08:30:57 | 2020-07-31 12:47:44 |
| 6.7.3 | 2020-08-28 12:33:15 | 2020-08-28 12:33:15 |
| 6.7.9 | 2021-06-23 08:57:05 | 2021-06-23 08:57:05 |