explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hXBe

Settings
# exclusive inclusive rows x rows loops node
1. 52,519.862 52,519.862 ↑ 5.7 602,951 1

CTE Scan on reshaping_from_dax (cost=1,356,166.08..1,424,798.34 rows=3,431,613 width=252) (actual time=49,371.859..52,519.862 rows=602,951 loops=1)

2.          

CTE cte1

3. 1,735.093 15,240.401 ↓ 11.0 603,037 1

HashAggregate (cost=8,638.92..9,187.22 rows=54,830 width=76) (actual time=14,987.207..15,240.401 rows=603,037 loops=1)

  • Group Key: ad.assetdatasourceid, ct1.assetid, ct1."Inspection Date", ct1."Severity Code", ct1."Condition Code
4. 810.703 13,505.308 ↓ 78.1 4,283,190 1

Hash Join (cost=2,247.77..7,953.54 rows=54,830 width=76) (actual time=9,616.865..13,505.308 rows=4,283,190 loops=1)

  • Hash Cond: (ad.assetdatasourceid = ads.assetdatasourceid)
5. 590.872 12,677.229 ↓ 74.7 4,283,190 1

Nested Loop (cost=0.57..4,943.00 rows=57,344 width=76) (actual time=9,599.404..12,677.229 rows=4,283,190 loops=1)

6. 9,674.257 9,674.257 ↓ 603.0 603,025 1

Function Scan on crosstab ct1 (cost=0.00..10.00 rows=1,000 width=72) (actual time=9,599.379..9,674.257 rows=603,025 loops=1)

7. 2,412.100 2,412.100 ↑ 8.1 7 603,025

Index Only Scan using assetdata_uidx on assetdata ad (cost=0.56..4.36 rows=57 width=8) (actual time=0.003..0.004 rows=7 loops=603,025)

  • Index Cond: (assetid = ct1.assetid)
  • Filter: (assetdatasourceid <> 3000003)
  • Rows Removed by Filter: 0
  • Heap Fetches: 131,823
8. 5.990 17.376 ↑ 1.0 50,237 1

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

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,279kB
9. 11.386 11.386 ↑ 1.0 50,237 1

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

  • Filter: (assetdatasourcetypeid = 1)
  • Rows Removed by Filter: 2,336
10.          

CTE cte2

11. 5,987.195 25,333.073 ↓ 3.1 42,133 1

HashAggregate (cost=18,052.21..18,393.69 rows=13,659 width=228) (actual time=25,313.464..25,333.073 rows=42,133 loops=1)

  • 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. 5,793.876 19,345.878 ↓ 519.2 7,092,410 1

Hash Join (cost=2,250.07..17,437.56 rows=13,659 width=228) (actual time=8,623.952..19,345.878 rows=7,092,410 loops=1)

  • Hash Cond: (ad_1.assetdatasourceid = ads_1.assetdatasourceid)
13. 1,174.330 13,533.695 ↓ 591.8 8,453,619 1

Nested Loop (cost=2.87..14,795.31 rows=14,285 width=272) (actual time=8,605.512..13,533.695 rows=8,453,619 loops=1)

14. 91.198 9,783.725 ↓ 129.4 128,782 1

Nested Loop Left Join (cost=2.29..12,303.85 rows=995 width=268) (actual time=8,605.500..9,783.725 rows=128,782 loops=1)

15. 50.348 9,397.407 ↓ 74.2 73,780 1

Nested Loop Left Join (cost=1.72..8,955.53 rows=995 width=260) (actual time=8,605.491..9,397.407 rows=73,780 loops=1)

16. 29.767 9,125.719 ↓ 74.2 73,780 1

Nested Loop Left Join (cost=1.15..6,161.93 rows=995 width=252) (actual time=8,605.482..9,125.719 rows=73,780 loops=1)

17. 32.483 8,870.542 ↓ 45.3 45,082 1

Nested Loop Left Join (cost=0.57..2,813.61 rows=995 width=244) (actual time=8,605.469..8,870.542 rows=45,082 loops=1)

18. 8,627.434 8,627.434 ↓ 42.3 42,125 1

Function Scan on crosstab ct2 (cost=0.00..20.00 rows=995 width=236) (actual time=8,605.442..8,627.434 rows=42,125 loops=1)

  • Filter: ((split_part(assetdatasourceid_assetid, '-'::text, 1))::integer <> 3000003)
19. 210.625 210.625 ↑ 1.0 1 42,125

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

  • 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[]))
  • Rows Removed by Filter: 0
20. 225.410 225.410 ↓ 2.0 2 45,082

Index Scan using assetdata_uidx on assetdata crit2 (cost=0.57..3.36 rows=1 width=12) (actual time=0.004..0.005 rows=2 loops=45,082)

  • Index Cond: ((assetid = (split_part(ct2.assetdatasourceid_assetid, '-'::text, 2))::integer) AND (assetdatatypeid = 380))
  • Filter: (assetdatasourceid <> ALL ('{3000003,999,1087}'::integer[]))
  • Rows Removed by Filter: 0
21. 221.340 221.340 ↑ 1.0 1 73,780

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

  • 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[]))
  • Rows Removed by Filter: 0
22. 295.120 295.120 ↓ 2.0 2 73,780

Index Scan using assetdata_uidx on assetdata plen2 (cost=0.57..3.36 rows=1 width=12) (actual time=0.003..0.004 rows=2 loops=73,780)

  • Index Cond: ((assetid = (split_part(ct2.assetdatasourceid_assetid, '-'::text, 2))::integer) AND (assetdatatypeid = 417))
  • Filter: (assetdatasourceid <> ALL ('{3000003,999,1087}'::integer[]))
  • Rows Removed by Filter: 0
23. 2,575.640 2,575.640 ↓ 4.7 66 128,782

Index Only Scan using assetdata_uidx on assetdata ad_1 (cost=0.57..2.36 rows=14 width=8) (actual time=0.004..0.020 rows=66 loops=128,782)

  • Index Cond: ((assetid = (split_part(ct2.assetdatasourceid_assetid, '-'::text, 2))::integer) AND (scenariocomponentid = 1,635))
  • Heap Fetches: 1,987,545
24. 6.442 18.307 ↑ 1.0 50,237 1

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

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,279kB
25. 11.865 11.865 ↑ 1.0 50,237 1

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

  • Filter: (assetdatasourcetypeid = 1)
  • Rows Removed by Filter: 2,336
26.          

CTE cte5

27. 7.463 1,152.212 ↑ 2,968.7 7,972 1

Nested Loop (cost=18,906.75..19,466.69 rows=23,666,298 width=76) (actual time=517.057..1,152.212 rows=7,972 loops=1)

28. 4.358 506.989 ↓ 39.9 7,972 1

HashAggregate (cost=18,906.19..18,908.19 rows=200 width=4) (actual time=504.170..506.989 rows=7,972 loops=1)

  • Group Key: max(assetdata_1.assetdataid)
29. 4.734 502.631 ↑ 2.2 7,972 1

HashAggregate (cost=18,505.06..18,683.34 rows=17,828 width=8) (actual time=501.052..502.631 rows=7,972 loops=1)

  • Group Key: assetdata_1.assetid
30. 497.897 497.897 ↑ 2.3 7,993 1

Index Scan using assetdata_idx2 on assetdata assetdata_1 (cost=0.56..18,414.96 rows=18,020 width=8) (actual time=0.786..497.897 rows=7,993 loops=1)

  • Index Cond: (assetdatatypeid = 377)
31. 637.760 637.760 ↑ 1.0 1 7,972

Index Scan using assetdata_pk on assetdata (cost=0.56..2.78 rows=1 width=68) (actual time=0.079..0.080 rows=1 loops=7,972)

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

CTE original_sproc

33. 1,417.419 51,398.977 ↑ 5.7 602,951 1

Hash Right Join (cost=91,635.41..1,206,170.10 rows=3,431,613 width=227) (actual time=49,371.848..51,398.977 rows=602,951 loops=1)

  • Hash Cond: (cte5.assetid = cte2.assetid)
34. 1,156.378 1,156.378 ↑ 2,968.7 7,972 1

CTE Scan on cte5 (cost=0.00..473,325.96 rows=23,666,298 width=12) (actual time=517.061..1,156.378 rows=7,972 loops=1)

35. 262.007 48,825.180 ↓ 20,791.4 602,951 1

Hash (cost=91,635.04..91,635.04 rows=29 width=307) (actual time=48,825.180..48,825.180 rows=602,951 loops=1)

  • Buckets: 131,072 (originally 1024) Batches: 8 (originally 1) Memory Usage: 19,948kB
36. 4,036.914 48,563.173 ↓ 20,791.4 602,951 1

Nested Loop Left Join (cost=91,338.00..91,635.04 rows=29 width=307) (actual time=42,925.021..48,563.173 rows=602,951 loops=1)

  • Join Filter: (ado.value = ((cte1."Condition Code")::integer)::double precision)
  • Rows Removed by Join Filter: 22,912,163
37. 215.820 43,320.357 ↓ 20,791.4 602,951 1

Merge Join (cost=91,338.00..91,597.17 rows=29 width=296) (actual time=42,924.988..43,320.357 rows=602,951 loops=1)

  • Merge Cond: ((ar.relatedassetid = cte2.assetid) AND (cte1.assetdatasourceid = cte2.assetdatasourceid))
38. 493.842 17,671.511 ↓ 28.9 603,037 1

Sort (cost=90,126.61..90,178.76 rows=20,858 width=80) (actual time=17,542.012..17,671.511 rows=603,037 loops=1)

  • Sort Key: ar.relatedassetid, cte1.assetdatasourceid
  • Sort Method: external merge Disk: 19,032kB
39. 0.000 17,177.669 ↓ 28.9 603,037 1

Nested Loop (cost=0.56..88,630.23 rows=20,858 width=80) (actual time=14,987.222..17,177.669 rows=603,037 loops=1)

40. 15,411.174 15,411.174 ↓ 11.0 603,037 1

CTE Scan on cte1 (cost=0.00..1,096.60 rows=54,830 width=76) (actual time=14,987.209..15,411.174 rows=603,037 loops=1)

41. 1,809.111 1,809.111 ↑ 1.0 1 603,037

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

  • Index Cond: ((assetid = cte1.assetid) AND (assetrelationshiptypeid = 31))
  • Heap Fetches: 10,361
42. 71.282 25,433.026 ↓ 44.5 607,501 1

Sort (cost=1,211.39..1,245.53 rows=13,659 width=224) (actual time=25,382.969..25,433.026 rows=607,501 loops=1)

  • Sort Key: cte2.assetid, cte2.assetdatasourceid
  • Sort Method: quicksort Memory: 12,413kB
43. 25,361.744 25,361.744 ↓ 3.1 42,133 1

CTE Scan on cte2 (cost=0.00..273.18 rows=13,659 width=224) (actual time=25,313.468..25,361.744 rows=42,133 loops=1)

44. 1,205.830 1,205.902 ↓ 1.0 39 602,951

Materialize (cost=0.00..13.18 rows=38 width=19) (actual time=0.000..0.002 rows=39 loops=602,951)

45. 0.072 0.072 ↓ 1.0 39 1

Seq Scan on assetdataoption ado (cost=0.00..12.99 rows=38 width=19) (actual time=0.018..0.072 rows=39 loops=1)

  • Filter: (assetdataoptionsetid = 9)
  • Rows Removed by Filter: 598
46.          

CTE reshaping_from_dax

47. 52,047.424 52,047.424 ↑ 5.7 602,951 1

CTE Scan on original_sproc (cost=0.00..102,948.39 rows=3,431,613 width=252) (actual time=49,371.856..52,047.424 rows=602,951 loops=1)

Planning time : 3.204 ms
Execution time : 52,597.357 ms