Postgres 15

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 IF NOT EXISTS changes (
  entity_id TEXT NOT NULL,
  column_id TEXT NOT NULL,
  value JSONB NOT NULL,
  updated_at TIMESTAMP NOT NULL
);

CREATE TABLE


INSERT INTO changes VALUES ('1', 'height', to_jsonb(140), '01-01-2021 00:00:00'::TIMESTAMP);
INSERT INTO changes VALUES ('1', 'weight', to_jsonb(30), '01-01-2021 00:00:00'::TIMESTAMP);
INSERT INTO changes VALUES ('1', 'height', to_jsonb(145), '01-02-2021 00:00:00'::TIMESTAMP);
INSERT INTO changes VALUES ('1', 'weight', to_jsonb(34),'01-03-2021 00:00:00'::TIMESTAMP);
INSERT INTO changes VALUES ('1', 'weight', to_jsonb(140), '01-04-2021 00:00:00'::TIMESTAMP);


INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1


SELECT
  entity_id,
  coalesce(height, lag(height, 1, null) over (partition by entity_id order by updated_at)) as height,
  coalesce(weight, lag(weight, 1, null) over (partition by entity_id order by updated_at)) as weight,
  updated_at
FROM (
    SELECT entity_id, MAX(CASE WHEN column_id = 'height' THEN value::text END) AS height
                , MAX(CASE WHEN column_id = 'weight' THEN value::text END) AS weight
                , updated_at
    FROM changes
    GROUP BY entity_id, updated_at
) as changes;


entity_id
height
weight
updated_at


1
140
30
2021-01-01 00:00:00

1
145
30
2021-02-01 00:00:00

1
145
34
2021-03-01 00:00:00

1
null
140
2021-04-01 00:00:00



SELECT 4


select * from changes order by entity_id, updated_at


entity_id
column_id
value
updated_at


1
height
140
2021-01-01 00:00:00

1
weight
30
2021-01-01 00:00:00

1
height
145
2021-02-01 00:00:00

1
weight
34
2021-03-01 00:00:00

1
weight
140
2021-04-01 00:00:00



SELECT 5


select entity_id, updated_at,
    max(height) over(partition by entity_id, grp_height) height,
    max(weight) over(partition by entity_id, grp_weight) weight
from (
    select c.*,
        count(height) over (partition by entity_id order by updated_at) grp_height,
        count(weight) over (partition by entity_id order by updated_at) grp_weight
    from (
        select entity_id, updated_at,
            max(value::text) filter(where column_id = 'height') height,
            max(value::text) filter(where column_id = 'weight') weight
        from changes
        group by entity_id, updated_at
    ) c
) c
order by entity_id, updated_at


entity_id
updated_at
height
weight


1
2021-01-01 00:00:00
140
30

1
2021-02-01 00:00:00
145
30

1
2021-03-01 00:00:00
145
34

1
2021-04-01 00:00:00
145
140



SELECT 4

entity_id	height	weight	updated_at
1	140	30	2021-01-01 00:00:00
1	145	30	2021-02-01 00:00:00
1	145	34	2021-03-01 00:00:00
1	null	140	2021-04-01 00:00:00

entity_id	column_id	value	updated_at
1	height	140	2021-01-01 00:00:00
1	weight	30	2021-01-01 00:00:00
1	height	145	2021-02-01 00:00:00
1	weight	34	2021-03-01 00:00:00
1	weight	140	2021-04-01 00:00:00