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?.

with x(data, towar, stan1, stan2, różnica) as
(values
('2020-01-01'::date,'towar1','10'::int,'9'::int,'1'::int),
('2020-01-02','towar1','11','9','2'),
('2020-01-03','towar1','15','14','1'),
('2020-01-04','towar1','12','12','0'),
('2020-01-05','towar1','10','10','0'),
('2020-01-06','towar1','10','8','2'),
('2020-01-07','towar1','5','5','0'),
('2020-01-08','towar1','4','4','0'),
('2020-01-09','towar1','3','3','0'),
('2020-01-10','towar1','1','1','0'),

('2020-01-03','towar2','15','14','1'),
('2020-01-04','towar2','12','12','0'),
('2020-01-05','towar2','10','10','0'),
('2020-01-06','towar2','10','8','2'),
('2020-01-07','towar2','5','5','0'),
('2020-01-08','towar2','4','4','0'),
('2020-01-07','towar2','5','3','2'),
('2020-01-08','towar2','4','4','0'),

('2020-01-07','towar3','5','5','0'),
('2020-01-08','towar3','8','8','0'),
('2020-01-07','towar3','5','3','2'),
('2020-01-08','towar3','4','3','1')
)

SELECT 
    towar, 
    różnica,
    MIN(data) AS start_date, 
    MAX(data) AS end_date,
    DATE_PART('day', AGE(MAX(data), MIN(data))) + 1 as length
FROM (
    SELECT
        data, 
        towar, 
        różnica,
        DATE_PART('day', AGE(data, '1970-01-01')) - ROW_NUMBER() OVER(PARTITION BY towar, różnica ORDER BY data) AS period_id
    FROM (
        SELECT DISTINCT 
            data,
            towar,
            różnica 
        FROM x
    ) as temp_1
) as temp_2
GROUP BY towar, różnica, period_id
ORDER BY towar, MIN(data);

towar	różnica	start_date	end_date	length
towar1	1	2020-01-01	2020-01-01	1
towar1	2	2020-01-02	2020-01-02	1
towar1	1	2020-01-03	2020-01-03	1
towar1	0	2020-01-04	2020-01-05	2
towar1	2	2020-01-06	2020-01-06	1
towar1	0	2020-01-07	2020-01-10	4
towar2	1	2020-01-03	2020-01-03	1
towar2	0	2020-01-04	2020-01-05	2
towar2	2	2020-01-06	2020-01-07	2
towar2	0	2020-01-07	2020-01-08	2
towar3	2	2020-01-07	2020-01-07	1
towar3	0	2020-01-07	2020-01-08	2
towar3	1	2020-01-08	2020-01-08	1