Postgres 11

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 table1
    ("goal_id" int, "goal_owner" int, "goal_create_at" int, "goal_metrics" json)
;
    
INSERT INTO table1
    ("goal_id", "goal_owner", "goal_create_at", "goal_metrics")
VALUES
    (69660, 148191, 1566576355, '[{"metric_owner": "148191", "metric_create_at": "1566576374", "metric_target": "10"}, {"metric_owner": "148191", "metric_create_at": "1566576403", "metric_target": "1"}, {"metric_owner": "148191", "metric_create_at": "1566576428", "metric_target": "3"}, {"metric_owner": "148191", "metric_create_at": "1566576450", "metric_target": "3"}]'),
    (68443, 146270, 1565911160, '[{"metric_owner": "144534", "metric_create_at": "1565911175", "metric_target": "1"}, {"metric_owner": "144352", "metric_create_at": "1565911191", "metric_target": "1"}, {"metric_owner": "144352", "metric_create_at": "1565911212", "metric_target": "15"}, {"metric_owner": "146270", "metric_create_at": "1565911353", "metric_target": "23"}]'),
    (68440, 146270, 1565910356, '[{"metric_owner": "144061", "metric_create_at": "1565910380", "metric_target": "3"}, {"metric_owner": "144061", "metric_create_at": "1565910462", "metric_target": "0"}, {"metric_owner": "144534", "metric_create_at": "1565910523", "metric_target": "1"}, {"metric_owner": "143866", "metric_create_at": "1565911422", "metric_target": "6"}]'),
    (68442, 146270, 1565910746, '[{"metric_owner": "143866", "metric_create_at": "1565910863", "metric_target": "9"}, {"metric_owner": "143866", "metric_create_at": "1565910881", "metric_target": "1"}, {"metric_owner": "144534", "metric_create_at": "1565910905", "metric_target": "1"}, {"metric_owner": "146270", "metric_create_at": "1565910927", "metric_target": "1"}, {"metric_owner": "144534", "metric_create_at": "1565910956", "metric_target": "1"}]')
;
4 rows affected

SELECT
    goal_id,
    (elems ->> 'metric_owner')::int AS metric_owner,
    (elems ->> 'metric_create_at')::int AS metric_create_at,
    (elems ->> 'metric_target')::int AS metric_target
FROM
    table1,
    json_array_elements(goal_metrics) as elems


goal_id
metric_owner
metric_create_at
metric_target


69660
148191
1566576374
10

69660
148191
1566576403
1

69660
148191
1566576428
3

69660
148191
1566576450
3

68443
144534
1565911175
1

68443
144352
1565911191
1

68443
144352
1565911212
15

68443
146270
1565911353
23

68440
144061
1565910380
3

68440
144061
1565910462
0

68440
144534
1565910523
1

68440
143866
1565911422
6

68442
143866
1565910863
9

68442
143866
1565910881
1

68442
144534
1565910905
1

68442
146270
1565910927
1

68442
144534
1565910956
1


SELECT
    goal_id,
    elems.*
FROM
    table1,
    json_to_recordset(goal_metrics) AS elems(metric_owner int, metric_create_at int,metric_target int)


goal_id
metric_owner
metric_create_at
metric_target


69660
148191
1566576374
10

69660
148191
1566576403
1

69660
148191
1566576428
3

69660
148191
1566576450
3

68443
144534
1565911175
1

68443
144352
1565911191
1

68443
144352
1565911212
15

68443
146270
1565911353
23

68440
144061
1565910380
3

68440
144061
1565910462
0

68440
144534
1565910523
1

68440
143866
1565911422
6

68442
143866
1565910863
9

68442
143866
1565910881
1

68442
144534
1565910905
1

68442
146270
1565910927
1

68442
144534
1565910956
1


CREATE TYPE metrics_type 
    AS (metric_owner int, metric_create_at int,metric_target int);

SELECT
    goal_id,
    elems.*
FROM
    table1,
    json_populate_recordset(null::metrics_type, goal_metrics) AS elems


goal_id
metric_owner
metric_create_at
metric_target


69660
148191
1566576374
10

69660
148191
1566576403
1

69660
148191
1566576428
3

69660
148191
1566576450
3

68443
144534
1565911175
1

68443
144352
1565911191
1

68443
144352
1565911212
15

68443
146270
1565911353
23

68440
144061
1565910380
3

68440
144061
1565910462
0

68440
144534
1565910523
1

68440
143866
1565911422
6

68442
143866
1565910863
9

68442
143866
1565910881
1

68442
144534
1565910905
1

68442
146270
1565910927
1

68442
144534
1565910956
1

goal_id	metric_owner	metric_create_at	metric_target
69660	148191	1566576374	10
69660	148191	1566576403	1
69660	148191	1566576428	3
69660	148191	1566576450	3
68443	144534	1565911175	1
68443	144352	1565911191	1
68443	144352	1565911212	15
68443	146270	1565911353	23
68440	144061	1565910380	3
68440	144061	1565910462	0
68440	144534	1565910523	1
68440	143866	1565911422	6
68442	143866	1565910863	9
68442	143866	1565910881	1
68442	144534	1565910905	1
68442	146270	1565910927	1
68442	144534	1565910956	1