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 tbl (personID int, status int, unixtime int);
INSERT INTO tbl VALUES
(1, 2, 213214)
, (6, 7, 213215) -- added to demonstrate that ...
, (6, 7, 213216) -- ... different personID does not interrupt island
, (1, 2, 213325)
, (1, 2, 213326)
, (1, 2, 213327)
, (1, 2, 213328)
, (1, 3, 214330)
, (1, 3, 214331)
, (1, 3, 214332)
, (1, 2, 324543)
;
CREATE TABLE
INSERT 0 11
SELECT *
FROM (
SELECT *
, lag(status) OVER w IS DISTINCT FROM status AS partition_start
, lead(status) OVER w IS DISTINCT FROM status AS partition_end
FROM tbl
WINDOW w AS (PARTITION BY personID ORDER BY unixtime)
) sub
WHERE (partition_start OR partition_end)
ORDER BY personID, unixtime;
| personid | status | unixtime | partition_start | partition_end |
|---|---|---|---|---|
| 1 | 2 | 213214 | t | f |
| 1 | 2 | 213328 | f | t |
| 1 | 3 | 214330 | t | f |
| 1 | 3 | 214332 | f | t |
| 1 | 2 | 324543 | t | t |
| 6 | 7 | 213215 | t | f |
| 6 | 7 | 213216 | f | t |
SELECT 7