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?.
with t as (
select '{
"RouteId": "90679754-89f5-48d7-99e1-5192bf0becf9",
"Started": "2019-11-20T21:24:33.7294486Z",
"RouteName": "ProcessRequestsAndPublishResponse",
"MachineName": "5CG8134NJW-LA",
"ChildProfiles": [
{
"ApiMethod": "ProcessApiRequest",
"ExecuteType": null,
"DurationMilliseconds": 2521.4
},
{
"ApiMethod": "PublishShipViaToQueue",
"ExecuteType": null,
"DurationMilliseconds": 0.6
}
],
"DataBaseTimings": null,
"DurationMilliseconds": 2522.6
}'::jsonb js
)
select
js ->> 'RouteName' RouteName,
xs."ApiMethod" Metrics,
xs."DurationMilliseconds" "Time",
js ->> 'DurationMilliseconds' TotalDuration
from t
cross join lateral jsonb_to_recordset( js -> 'ChildProfiles')
as xs("ApiMethod" text, "DurationMilliseconds" numeric)
| routename | metrics | Time | totalduration |
|---|---|---|---|
| ProcessRequestsAndPublishResponse | ProcessApiRequest | 2521.4 | 2522.6 |
| ProcessRequestsAndPublishResponse | PublishShipViaToQueue | 0.6 | 2522.6 |