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

CREATE TABLE test (d DATE, k CHAR(2), v INT)
INSERT INTO test VALUES
( '2019-01-02', 'S1',   20     ),
( '2019-02-04', 'S1',   30     ),
( '2019-03-10', 'S2',   15     ),
( '2019-04-07', 'S1',   0      ),
( '2019-04-13', 'S2',   35     ),
( '2019-04-19', 'S1',   10     ),
( '2019-05-01', 'S1',   30     ),
( '2019-05-15', 'S1',   40     ),
( '2019-06-21', 'S1',   0      )
9 rows affected

WITH cte AS (
SELECT *, COUNT(CASE WHEN v=0 THEN 0 END) OVER (PARTITION BY k ORDER BY d DESC) r
FROM test
)
SELECT k AS key, MIN(d) AS "date", MAX(d) AS date_of_zero
FROM cte
WHERE r > 0
GROUP BY k, r
ORDER BY 1, 2


key
date
date_of_zero


S1
2019-01-02
2019-04-07

S1
2019-04-19
2019-06-21