Please help me to tune this query. I created with basic knowledge. But it is taking long time to run.
WITH
External_Facing_Asset AS
(
SELECT dta.asset_id, string_agg(distinct dt.tag_name, ', ') as External_Facing_Asset
FROM dim_tag dt
JOIN dim_tag_asset dta ON dt.tag_id=dta.tag_id
WHERE dt.tag_name LIKE ‘Externally-facing -assets’
GROUP BY dta.asset_id
),
Business_Criticality AS
(
SELECT dta.asset_id, string_agg(dt.tag_name, ', ') as Business_Criticality
FROM dim_tag dt
JOIN dim_tag_asset dta ON dt.tag_id=dta.tag_id
WHERE dt.tag_name LIKE ‘BC_%’
GROUP BY dta.asset_id
),
owner_tags AS
(
SELECT dta.asset_id, string_agg(dt.tag_name, ', ') as owner_tags
FROM dim_tag dt
JOIN dim_tag_asset dta ON dt.tag_id=dta.tag_id
WHERE dt.tag_type = ‘OWNER’
GROUP BY dta.asset_id
),
vuln_references AS
(
SELECT vulnerability_id, array_to_string(array_agg(reference), ', ') AS references
FROM dim_vulnerability
JOIN dim_vulnerability_reference USING (vulnerability_id)
GROUP BY vulnerability_id
)
SELECT DISTINCT
da.host_name AS “Asset Name”,
da.ip_address AS “Asset IP Address”,
dos.description AS “Asset OS Name”,
ROUND(CAST(dv.cvss_score AS numeric), 0) AS “Vulnerability Severity Level”,
dv.title AS “Vulnerability Title”,
proofAsText(dso.fix) AS “Vulnerability Solution”,
proofAsText(fasvi.proof) AS “Vulnerability Proof”,
dv.nexpose_id AS “Vulnerability CVE IDs”,
substr (ot.owner_tags, strpos (ot.owner_tags,’’) + 1) AS “Asset Owner”,
substr (bc.Business_Criticality, strpos (bc.Business_Criticality,’’) + 1 ) AS “Business Criticality”,
ef.External_Facing_Asset AS “Externally Facing Asset”
FROM fact_asset_vulnerability_instance fasvi
LEFT JOIN Business_Criticality bc USING (asset_id)
LEFT JOIN owner_tags ot USING (asset_id)
LEFT JOIN External_Facing_Asset ef USING (asset_id)
LEFT JOIN dim_vulnerability cs USING (vulnerability_id)
JOIN dim_vulnerability_status dvs USING (status_id)
JOIN vuln_references vr USING (vulnerability_id)
JOIN dim_asset da ON (fasvi.asset_id = da.asset_id)
JOIN dim_vulnerability dv ON (fasvi.vulnerability_id = dv.vulnerability_id)
JOIN dim_asset_operating_system daos ON (fasvi.asset_id = daos.asset_id)
JOIN dim_operating_system dos ON (daos.operating_system_id = dos.operating_system_id)
JOIN dim_asset_group_asset daga ON (fasvi.asset_id = daga.asset_id)
JOIN dim_asset_group dag ON (daga.asset_group_id = dag.asset_group_id)
JOIN dim_asset_vulnerability_best_solution davbs ON (davbs.asset_id = da.asset_id)
JOIN dim_solution dso ON (dso.solution_id = davbs.solution_id)
JOIN dim_vulnerability dve ON dve.vulnerability_id = davbs.vulnerability_id
JOIN fact_asset_vulnerability_finding fasvf ON dve.vulnerability_id = fasvf.vulnerability_id
GROUP BY dso.fix, dv.nexpose_id, dv.title, dv.cvss_score, proofAsText(fasvi.proof), ot.owner_tags, bc.Business_Criticality,
ef.External_Facing_Asset, fasvi.asset_id, da.host_name, da.ip_address, dv.severity, dos.description, dag.name