I have a jsonb column in a table with the following model:
{
"framework": {
"name": "framework name",
"sections": [
{
"controls": [
{
"name": "control name",
"accounts": [
{
"rules": [
{
"id": 841,
"name": "rule name",
"entities": [
{
"id": "entity id",
...
}
]
}
]
}
]
}
]
}
]
}
}
I have a query that requires traversing the above-nested structure and perform filter based on specific values. For that purpose I have used cross join lateral multiple times on the extracted values of the jsonb_array_elements() function. Following is the sample sql query.
SELECT
r.rule_sets_id AS ruleSetId,
ARRAY_AGG(distinct CAST(account->>'accountId' AS text)) AS accountIdsInJsonArray,
COUNT(distinct control ->> 'name') AS enabledControls,
COUNT(distinct rule ->> 'id') AS enabledRules
FROM reports r
CROSS JOIN LATERAL jsonb_array_elements(result->'framework'->'sections') AS section
CROSS JOIN LATERAL jsonb_array_elements(section->'controls') AS control
CROSS JOIN LATERAL jsonb_array_elements(control->'accounts') AS account
CROSS JOIN LATERAL jsonb_array_elements(account->'rules') AS rule
CROSS JOIN LATERAL jsonb_array_elements(rule->'entities') AS entity
INNER JOIN(
SELECT rule_sets_id, max(modified_on) modified_on
FROM reports
WHERE workspace_id = :workspaceId
AND status = 'COMPLETED'
GROUP BY rule_sets_id
) r1 ON r.modified_on = r1.modified_on AND r.rule_sets_id = r1.rule_sets_id
WHERE r.workspace_id = :workspaceId AND r.status = 'COMPLETED'
and account ->> 'accountId' in ('123456789012')
and account ->> 'dataSource' in ('AWS')
GROUP BY r.rule_sets_id;
Now for the above query I want to enhance the performance of the same by creating required indexes for the jsonb column. Also, there is currently index on columns workspace_id
, status
, rule_sets_id
on the table reports
.
Following is the link where I have shared the explain analyze output of the above sql query: https://explain.dalibo.com/plan/66139bc286gbh19c
What can I do to improve performance?
account->>'accountId'
guaranteed to be unique for everycontrol ->> 'name'
? If so you can remove theDISTINCT
from the aggregates and instead aggregate on two levels. Same forrule ->> 'id'
are there always unique values in differentaccount
s?@>
operator.