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 data (
    dt   DATE,
    val  CHAR
)
INSERT INTO
    data
VALUES
    ('2020-01-01', 'A'),
    ('2020-01-02', 'A'),
    ('2020-01-03', 'B'),
    ('2020-01-04', 'B'),
    ('2020-01-05', 'B'),
    ('2020-01-06', 'C'),
    ('2020-01-07', 'C'),
    ('2020-01-08', 'B'),
    ('2020-01-09', 'A'),
    ('2020-01-10', 'A'),
    ('2020-01-11', 'A')
11 rows affected

WITH
  pre_and_post AS
(
  SELECT
    data.*,
    CASE WHEN  LAG(val) OVER (ORDER BY dt) <> val THEN  LAG(dt) OVER (ORDER BY dt) END   AS prev_end,
    CASE WHEN LEAD(val) OVER (ORDER BY dt) <> val THEN LEAD(dt) OVER (ORDER BY dt) END   AS next_start
  FROM
    data
)
SELECT
  *
FROM
  pre_and_post
ORDER BY
  dt


dt
val
prev_end
next_start


2020-01-01
A
null
null

2020-01-02
A
null
2020-01-03

2020-01-03
B
2020-01-02
null

2020-01-04
B
null
null

2020-01-05
B
null
2020-01-06

2020-01-06
C
2020-01-05
null

2020-01-07
C
null
2020-01-08

2020-01-08
B
2020-01-07
2020-01-09

2020-01-09
A
2020-01-08
null

2020-01-10
A
null
null

2020-01-11
A
null
null


WITH
  pre_and_post AS
(
  SELECT
    data.*,
    CASE WHEN  LAG(val) OVER (ORDER BY dt) <> val THEN  LAG(dt) OVER (ORDER BY dt) END   AS prev_end,
    CASE WHEN LEAD(val) OVER (ORDER BY dt) <> val THEN LEAD(dt) OVER (ORDER BY dt) END   AS next_start
  FROM
    data
),
  gaps_and_islands AS
(
  SELECT
    *,
    ROW_NUMBER() OVER (                 ORDER BY dt)
    -
    ROW_NUMBER() OVER (PARTITION BY val ORDER BY dt)   AS val_group_id
  FROM
    pre_and_post
)
SELECT
  *
FROM
  gaps_and_islands
ORDER BY
  dt


dt
val
prev_end
next_start
val_group_id


2020-01-01
A
null
null
0

2020-01-02
A
null
2020-01-03
0

2020-01-03
B
2020-01-02
null
2

2020-01-04
B
null
null
2

2020-01-05
B
null
2020-01-06
2

2020-01-06
C
2020-01-05
null
5

2020-01-07
C
null
2020-01-08
5

2020-01-08
B
2020-01-07
2020-01-09
4

2020-01-09
A
2020-01-08
null
6

2020-01-10
A
null
null
6

2020-01-11
A
null
null
6


WITH
  pre_and_post AS
(
  SELECT
    data.*,
    CASE WHEN  LAG(val) OVER (ORDER BY dt) <> val THEN  LAG(dt) OVER (ORDER BY dt) END   AS prev_end,
    CASE WHEN LEAD(val) OVER (ORDER BY dt) <> val THEN LEAD(dt) OVER (ORDER BY dt) END   AS next_start
  FROM
    data
),
  gaps_and_islands AS
(
  SELECT
    *,
    ROW_NUMBER() OVER (                 ORDER BY dt)
    -
    ROW_NUMBER() OVER (PARTITION BY val ORDER BY dt)   AS val_group_id
  FROM
    pre_and_post
)
SELECT
  dt,
  val,
  MIN(dt        ) OVER (PARTITION BY val, val_group_id)   AS val_group_start,
  MAX(dt        ) OVER (PARTITION BY val, val_group_id)   AS val_group_end,
  MAX(prev_end  ) OVER (PARTITION BY val, val_group_id)   AS prev_val_group_end,
  MAX(next_start) OVER (PARTITION BY val, val_group_id)   AS next_val_group_start
FROM
  gaps_and_islands
ORDER BY
  dt


dt
val
val_group_start
val_group_end
prev_val_group_end
next_val_group_start


2020-01-01
A
2020-01-01
2020-01-02
null
2020-01-03

2020-01-02
A
2020-01-01
2020-01-02
null
2020-01-03

2020-01-03
B
2020-01-03
2020-01-05
2020-01-02
2020-01-06

2020-01-04
B
2020-01-03
2020-01-05
2020-01-02
2020-01-06

2020-01-05
B
2020-01-03
2020-01-05
2020-01-02
2020-01-06

2020-01-06
C
2020-01-06
2020-01-07
2020-01-05
2020-01-08

2020-01-07
C
2020-01-06
2020-01-07
2020-01-05
2020-01-08

2020-01-08
B
2020-01-08
2020-01-08
2020-01-07
2020-01-09

2020-01-09
A
2020-01-09
2020-01-11
2020-01-08
null

2020-01-10
A
2020-01-09
2020-01-11
2020-01-08
null

2020-01-11
A
2020-01-09
2020-01-11
2020-01-08
null

dt	val	prev_end	next_start
2020-01-01	A	null	null
2020-01-02	A	null	2020-01-03
2020-01-03	B	2020-01-02	null
2020-01-04	B	null	null
2020-01-05	B	null	2020-01-06
2020-01-06	C	2020-01-05	null
2020-01-07	C	null	2020-01-08
2020-01-08	B	2020-01-07	2020-01-09
2020-01-09	A	2020-01-08	null
2020-01-10	A	null	null
2020-01-11	A	null	null