explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KN8

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

CTE Scan on reshaping_from_dax (cost=1,356,166.08..1,424,798.34 rows=3,431,613 width=252) (actual rows= loops=)

2.          

CTE cte1

3. 0.000 0.000 ↓ 0.0

HashAggregate (cost=8,638.92..9,187.22 rows=54,830 width=76) (actual rows= loops=)

  • Group Key: ad.assetdatasourceid, ct1.assetid, ct1."Inspection Date", ct1."Severity Code", ct1."Condition Code
4. 0.000 0.000 ↓ 0.0

Hash Join (cost=2,247.77..7,953.54 rows=54,830 width=76) (actual rows= loops=)

  • Hash Cond: (ad.assetdatasourceid = ads.assetdatasourceid)
5. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..4,943.00 rows=57,344 width=76) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Function Scan on crosstab ct1 (cost=0.00..10.00 rows=1,000 width=72) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Index Only Scan using assetdata_uidx on assetdata ad (cost=0.56..4.36 rows=57 width=8) (actual rows= loops=)

  • Index Cond: (assetid = ct1.assetid)
  • Filter: (assetdatasourceid <> 3000003)
8. 0.000 0.000 ↓ 0.0

Hash (cost=1,604.83..1,604.83 rows=51,390 width=4) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Seq Scan on assetdatasource ads (cost=0.00..1,604.83 rows=51,390 width=4) (actual rows= loops=)

  • Filter: (assetdatasourcetypeid = 1)
10.          

CTE cte2

11. 0.000 0.000 ↓ 0.0

HashAggregate (cost=18,052.21..18,393.69 rows=13,659 width=228) (actual rows= loops=)

  • Group Key: (split_part(ct2.assetdatasourceid_assetid, '-'::text, 1))::integer, (split_part(ct2.assetdatasourceid_assetid, '-'::text, 2))::integer, ct2."Diameter", ct2."Inspection Date", ct2."CCTV - StructuralMeanGrade", ct2."Observed Length", ct2."CCTV - StructuralPeakGrade", ct2."CCTV - ServiceGrade", ct2."CCTV - ServicePeakGrade", ct2."CCTV - ServiceMeanGrade", ct2."CCTV - StructuralMeanGradeAdj", ct2."CCTV - StructuralPeakGradeAdj", ct2."CCTV - ServicePeakGradeAdj", ct2."CCTV - ServiceMeanGradeAdj", COALESCE(crit1.value, crit2.value), COALESCE(plen1.value, plen2.value), ct2.contractor, ct2.video
12. 0.000 0.000 ↓ 0.0

Hash Join (cost=2,250.07..17,437.56 rows=13,659 width=228) (actual rows= loops=)

  • Hash Cond: (ad_1.assetdatasourceid = ads_1.assetdatasourceid)
13. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.87..14,795.31 rows=14,285 width=272) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2.29..12,303.85 rows=995 width=268) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.72..8,955.53 rows=995 width=260) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.15..6,161.93 rows=995 width=252) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.57..2,813.61 rows=995 width=244) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Function Scan on crosstab ct2 (cost=0.00..20.00 rows=995 width=236) (actual rows= loops=)

  • Filter: ((split_part(assetdatasourceid_assetid, '-'::text, 1))::integer <> 3000003)
19. 0.000 0.000 ↓ 0.0

Index Scan using assetdata_uidx on assetdata crit1 (cost=0.57..2.80 rows=1 width=12) (actual rows= loops=)

  • Index Cond: ((assetid = (split_part(ct2.assetdatasourceid_assetid, '-'::text, 2))::integer) AND (scenariocomponentid = 1,635) AND (assetdatatypeid = 380))
  • Filter: (assetdatasourceid <> ALL ('{3000003,999,1087}'::integer[]))
20. 0.000 0.000 ↓ 0.0

Index Scan using assetdata_uidx on assetdata crit2 (cost=0.57..3.36 rows=1 width=12) (actual rows= loops=)

  • Index Cond: ((assetid = (split_part(ct2.assetdatasourceid_assetid, '-'::text, 2))::integer) AND (assetdatatypeid = 380))
  • Filter: (assetdatasourceid <> ALL ('{3000003,999,1087}'::integer[]))
21. 0.000 0.000 ↓ 0.0

Index Scan using assetdata_uidx on assetdata plen1 (cost=0.57..2.80 rows=1 width=12) (actual rows= loops=)

  • Index Cond: ((assetid = (split_part(ct2.assetdatasourceid_assetid, '-'::text, 2))::integer) AND (scenariocomponentid = 1,635) AND (assetdatatypeid = 417))
  • Filter: (assetdatasourceid <> ALL ('{3000003,999,1087}'::integer[]))
22. 0.000 0.000 ↓ 0.0

Index Scan using assetdata_uidx on assetdata plen2 (cost=0.57..3.36 rows=1 width=12) (actual rows= loops=)

  • Index Cond: ((assetid = (split_part(ct2.assetdatasourceid_assetid, '-'::text, 2))::integer) AND (assetdatatypeid = 417))
  • Filter: (assetdatasourceid <> ALL ('{3000003,999,1087}'::integer[]))
23. 0.000 0.000 ↓ 0.0

Index Only Scan using assetdata_uidx on assetdata ad_1 (cost=0.57..2.36 rows=14 width=8) (actual rows= loops=)

  • Index Cond: ((assetid = (split_part(ct2.assetdatasourceid_assetid, '-'::text, 2))::integer) AND (scenariocomponentid = 1,635))
24. 0.000 0.000 ↓ 0.0

Hash (cost=1,604.83..1,604.83 rows=51,390 width=4) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Seq Scan on assetdatasource ads_1 (cost=0.00..1,604.83 rows=51,390 width=4) (actual rows= loops=)

  • Filter: (assetdatasourcetypeid = 1)
26.          

CTE cte5

27. 0.000 0.000 ↓ 0.0

Nested Loop (cost=18,906.75..19,466.69 rows=23,666,298 width=76) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

HashAggregate (cost=18,906.19..18,908.19 rows=200 width=4) (actual rows= loops=)

  • Group Key: max(assetdata_1.assetdataid)
29. 0.000 0.000 ↓ 0.0

HashAggregate (cost=18,505.06..18,683.34 rows=17,828 width=8) (actual rows= loops=)

  • Group Key: assetdata_1.assetid
30. 0.000 0.000 ↓ 0.0

Index Scan using assetdata_idx2 on assetdata assetdata_1 (cost=0.56..18,414.96 rows=18,020 width=8) (actual rows= loops=)

  • Index Cond: (assetdatatypeid = 377)
31. 0.000 0.000 ↓ 0.0

Index Scan using assetdata_pk on assetdata (cost=0.56..2.78 rows=1 width=68) (actual rows= loops=)

  • Index Cond: (assetdataid = (max(assetdata_1.assetdataid)))
32.          

CTE original_sproc

33. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=91,635.41..1,206,170.10 rows=3,431,613 width=227) (actual rows= loops=)

  • Hash Cond: (cte5.assetid = cte2.assetid)
34. 0.000 0.000 ↓ 0.0

CTE Scan on cte5 (cost=0.00..473,325.96 rows=23,666,298 width=12) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Hash (cost=91,635.04..91,635.04 rows=29 width=307) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=91,338.00..91,635.04 rows=29 width=307) (actual rows= loops=)

  • Join Filter: (ado.value = ((cte1."Condition Code")::integer)::double precision)
37. 0.000 0.000 ↓ 0.0

Merge Join (cost=91,338.00..91,597.17 rows=29 width=296) (actual rows= loops=)

  • Merge Cond: ((ar.relatedassetid = cte2.assetid) AND (cte1.assetdatasourceid = cte2.assetdatasourceid))
38. 0.000 0.000 ↓ 0.0

Sort (cost=90,126.61..90,178.76 rows=20,858 width=80) (actual rows= loops=)

  • Sort Key: ar.relatedassetid, cte1.assetdatasourceid
39. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.56..88,630.23 rows=20,858 width=80) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

CTE Scan on cte1 (cost=0.00..1,096.60 rows=54,830 width=76) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Index Only Scan using assetrelationship_unique_constraint on assetrelationship ar (cost=0.56..1.59 rows=1 width=8) (actual rows= loops=)

  • Index Cond: ((assetid = cte1.assetid) AND (assetrelationshiptypeid = 31))
42. 0.000 0.000 ↓ 0.0

Sort (cost=1,211.39..1,245.53 rows=13,659 width=224) (actual rows= loops=)

  • Sort Key: cte2.assetid, cte2.assetdatasourceid
43. 0.000 0.000 ↓ 0.0

CTE Scan on cte2 (cost=0.00..273.18 rows=13,659 width=224) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..13.18 rows=38 width=19) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Seq Scan on assetdataoption ado (cost=0.00..12.99 rows=38 width=19) (actual rows= loops=)

  • Filter: (assetdataoptionsetid = 9)
46.          

CTE reshaping_from_dax

47. 0.000 0.000 ↓ 0.0

CTE Scan on original_sproc (cost=0.00..102,948.39 rows=3,431,613 width=252) (actual rows= loops=)