explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mDNY

Settings
# exclusive inclusive rows x rows loops node
1. 0.113 42,118.910 ↓ 25.0 50 1

Limit (cost=366,191.65..383,721.24 rows=2 width=4,335) (actual time=27,736.086..42,118.910 rows=50 loops=1)

2.          

CTE loanpartbalance

3. 150.971 905.125 ↑ 1.0 99,346 1

Hash Right Join (cost=15,540.61..35,390.04 rows=99,383 width=63) (actual time=317.928..905.125 rows=99,346 loops=1)

  • Hash Cond: (sp_1."ProductPkey" = c_1_1.productcopyfk)
4. 17.655 138.707 ↓ 1.0 99,353 1

Subquery Scan on sp_1 (cost=0.29..8,159.48 rows=99,350 width=36) (actual time=0.034..138.707 rows=99,353 loops=1)

5. 82.252 121.052 ↓ 1.0 99,353 1

Group (cost=0.29..7,165.98 rows=99,350 width=40) (actual time=0.032..121.052 rows=99,353 loops=1)

  • Group Key: pc_2.pkey
6. 38.800 38.800 ↓ 1.0 99,353 1

Index Scan using productcopy_pkey on productcopy pc_2 (cost=0.29..5,551.54 rows=99,350 width=8) (actual time=0.021..38.800 rows=99,353 loops=1)

7. 53.706 317.409 ↑ 1.0 99,346 1

Hash (cost=13,230.03..13,230.03 rows=99,383 width=59) (actual time=317.409..317.409 rows=99,346 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 2836kB
8. 88.232 263.703 ↑ 1.0 99,346 1

Hash Right Join (cost=7,912.12..13,230.03 rows=99,383 width=59) (actual time=132.971..263.703 rows=99,346 loops=1)

  • Hash Cond: (cb.creditfk = c_1_1.pkey)
9. 42.964 42.964 ↑ 1.0 99,347 1

Seq Scan on creditbalances cb (cost=0.00..2,822.98 rows=99,398 width=35) (actual time=0.131..42.964 rows=99,347 loops=1)

10. 38.564 132.507 ↑ 1.0 99,346 1

Hash (cost=5,989.83..5,989.83 rows=99,383 width=28) (actual time=132.507..132.507 rows=99,346 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 3419kB
11. 93.943 93.943 ↑ 1.0 99,346 1

Seq Scan on credit c_1_1 (cost=0.00..5,989.83 rows=99,383 width=28) (actual time=0.129..93.943 rows=99,346 loops=1)

12.          

SubPlan (forHash Right Join)

13. 198.692 298.038 ↑ 1.0 1 99,346

Aggregate (cost=0.06..0.07 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=99,346)

14. 99.346 99.346 ↑ 1.0 4 99,346

Values Scan on "*VALUES*" (cost=0.00..0.05 rows=4 width=8) (actual time=0.000..0.001 rows=4 loops=99,346)

15.          

CTE dossierbalance

16. 397.126 1,069.014 ↓ 461.9 92,384 1

HashAggregate (cost=3,229.95..3,233.95 rows=200 width=132) (actual time=896.602..1,069.014 rows=92,384 loops=1)

  • Group Key: lpb_1.dossierentityid
17. 671.888 671.888 ↑ 1.0 99,346 1

CTE Scan on loanpartbalance lpb_1 (cost=0.00..1,987.66 rows=99,383 width=90) (actual time=0.001..671.888 rows=99,346 loops=1)

18.          

CTE lastactualmarketvalue

19. 0.142 2.781 ↓ 213.3 640 1

Subquery Scan on lastactualmarketvalueprep (cost=67.02..116.62 rows=3 width=26) (actual time=0.653..2.781 rows=640 loops=1)

  • Filter: (lastactualmarketvalueprep.rn = 1)
20. 0.663 2.639 ↑ 1.0 640 1

WindowAgg (cost=67.02..108.62 rows=640 width=46) (actual time=0.652..2.639 rows=640 loops=1)

21. 0.263 0.696 ↑ 1.0 640 1

Sort (cost=67.02..68.62 rows=640 width=30) (actual time=0.628..0.696 rows=640 loops=1)

  • Sort Key: rev.realestatefk, rev.valuedeterminationdate DESC, rev.created DESC, rev.modified DESC
  • Sort Method: quicksort Memory: 75kB
22. 0.433 0.433 ↑ 1.0 640 1

Seq Scan on realestatevalue rev (cost=0.00..37.19 rows=640 width=30) (actual time=0.014..0.433 rows=640 loops=1)

  • Filter: (realestatevaluetypecd = 1)
  • Rows Removed by Filter: 817
23.          

SubPlan (forWindowAgg)

24. 0.640 1.280 ↑ 1.0 1 640

Aggregate (cost=0.03..0.04 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=640)

25. 0.640 0.640 ↑ 1.0 2 640

Values Scan on "*VALUES*_1" (cost=0.00..0.03 rows=2 width=8) (actual time=0.000..0.001 rows=2 loops=640)

26.          

CTE lastaftertransformationmarketvalue

27. 0.020 0.620 ↓ 85.0 85 1

Subquery Scan on lastaftertransformationmarketvalueprep (cost=39.91..46.50 rows=1 width=26) (actual time=0.340..0.620 rows=85 loops=1)

  • Filter: (lastaftertransformationmarketvalueprep.rn = 1)
28. 0.096 0.600 ↑ 1.0 85 1

WindowAgg (cost=39.91..45.44 rows=85 width=46) (actual time=0.339..0.600 rows=85 loops=1)

29. 0.048 0.334 ↑ 1.0 85 1

Sort (cost=39.91..40.12 rows=85 width=30) (actual time=0.326..0.334 rows=85 loops=1)

  • Sort Key: rev_1.realestatefk, rev_1.valuedeterminationdate DESC, rev_1.created DESC, rev_1.modified DESC
  • Sort Method: quicksort Memory: 31kB
30. 0.286 0.286 ↑ 1.0 85 1

Seq Scan on realestatevalue rev_1 (cost=0.00..37.19 rows=85 width=30) (actual time=0.008..0.286 rows=85 loops=1)

  • Filter: (realestatevaluetypecd = 2)
  • Rows Removed by Filter: 1372
31.          

SubPlan (forWindowAgg)

32. 0.085 0.170 ↑ 1.0 1 85

Aggregate (cost=0.03..0.04 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=85)

33. 0.085 0.085 ↑ 1.0 2 85

Values Scan on "*VALUES*_2" (cost=0.00..0.03 rows=2 width=8) (actual time=0.000..0.001 rows=2 loops=85)

34.          

CTE applicationdate

35. 149.564 907.314 ↓ 1.4 99,346 1

WindowAgg (cost=24,599.27..31,221.56 rows=73,581 width=28) (actual time=439.550..907.314 rows=99,346 loops=1)

36. 129.448 459.712 ↓ 1.4 99,346 1

Sort (cost=24,599.27..24,783.22 rows=73,581 width=44) (actual time=439.518..459.712 rows=99,346 loops=1)

  • Sort Key: ffv.creditfk, ffv.pkey DESC
  • Sort Method: external sort Disk: 3696kB
37. 207.498 330.264 ↓ 1.4 99,346 1

Hash Join (cost=3.24..16,386.83 rows=73,581 width=44) (actual time=0.188..330.264 rows=99,346 loops=1)

  • Hash Cond: (ffv.freefieldconfigfk = ffc.pkey)
38. 122.730 122.730 ↓ 1.0 735,823 1

Seq Scan on freefieldvalue ffv (cost=0.00..14,361.13 rows=735,813 width=32) (actual time=0.135..122.730 rows=735,823 loops=1)

39. 0.007 0.036 ↑ 1.0 9 1

Hash (cost=3.12..3.12 rows=9 width=20) (actual time=0.036..0.036 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
40. 0.029 0.029 ↑ 1.0 9 1

Seq Scan on freefieldconfig ffc (cost=0.00..3.12 rows=9 width=20) (actual time=0.010..0.029 rows=9 loops=1)

  • Filter: (freefieldcd = 2)
  • Rows Removed by Filter: 81
41.          

SubPlan (forWindowAgg)

42. 99.346 298.038 ↑ 1.0 1 99,346

Aggregate (cost=0.06..0.07 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=99,346)

43. 198.692 198.692 ↑ 1.0 4 99,346

Values Scan on "*VALUES*_3" (cost=0.00..0.05 rows=4 width=8) (actual time=0.000..0.002 rows=4 loops=99,346)

44.          

CTE proposaldate

45. 125.850 895.575 ↓ 1.3 92,503 1

WindowAgg (cost=24,599.27..31,221.56 rows=73,581 width=28) (actual time=464.844..895.575 rows=92,503 loops=1)

46. 165.745 492.216 ↓ 1.3 92,503 1

Sort (cost=24,599.27..24,783.22 rows=73,581 width=44) (actual time=464.825..492.216 rows=92,503 loops=1)

  • Sort Key: ffv_1.creditfk, ffv_1.pkey DESC
  • Sort Method: external sort Disk: 3440kB
47. 214.869 326.471 ↓ 1.3 92,503 1

Hash Join (cost=3.24..16,386.83 rows=73,581 width=44) (actual time=0.214..326.471 rows=92,503 loops=1)

  • Hash Cond: (ffv_1.freefieldconfigfk = ffc_1.pkey)
48. 111.566 111.566 ↓ 1.0 735,823 1

Seq Scan on freefieldvalue ffv_1 (cost=0.00..14,361.13 rows=735,813 width=32) (actual time=0.163..111.566 rows=735,823 loops=1)

49. 0.007 0.036 ↑ 1.0 9 1

Hash (cost=3.12..3.12 rows=9 width=20) (actual time=0.036..0.036 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
50. 0.029 0.029 ↑ 1.0 9 1

Seq Scan on freefieldconfig ffc_1 (cost=0.00..3.12 rows=9 width=20) (actual time=0.009..0.029 rows=9 loops=1)

  • Filter: (freefieldcd = 1003)
  • Rows Removed by Filter: 81
51.          

SubPlan (forWindowAgg)

52. 92.503 277.509 ↑ 1.0 1 92,503

Aggregate (cost=0.06..0.07 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=92,503)

53. 185.006 185.006 ↑ 1.0 4 92,503

Values Scan on "*VALUES*_4" (cost=0.00..0.05 rows=4 width=8) (actual time=0.000..0.002 rows=4 loops=92,503)

54.          

CTE reassessmentdate

55. 22.493 386.100 ↑ 3.7 19,836 1

WindowAgg (cost=24,599.27..31,221.56 rows=73,581 width=28) (actual time=302.255..386.100 rows=19,836 loops=1)

56. 13.633 304.099 ↑ 3.7 19,836 1

Sort (cost=24,599.27..24,783.22 rows=73,581 width=44) (actual time=302.231..304.099 rows=19,836 loops=1)

  • Sort Key: ffv_2.creditfk, ffv_2.pkey DESC
  • Sort Method: quicksort Memory: 2318kB
57. 180.391 290.466 ↑ 3.7 19,836 1

Hash Join (cost=3.24..16,386.83 rows=73,581 width=44) (actual time=0.262..290.466 rows=19,836 loops=1)

  • Hash Cond: (ffv_2.freefieldconfigfk = ffc_2.pkey)
58. 110.040 110.040 ↓ 1.0 735,823 1

Seq Scan on freefieldvalue ffv_2 (cost=0.00..14,361.13 rows=735,813 width=32) (actual time=0.167..110.040 rows=735,823 loops=1)

59. 0.006 0.035 ↑ 1.0 9 1

Hash (cost=3.12..3.12 rows=9 width=20) (actual time=0.035..0.035 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
60. 0.029 0.029 ↑ 1.0 9 1

Seq Scan on freefieldconfig ffc_2 (cost=0.00..3.12 rows=9 width=20) (actual time=0.009..0.029 rows=9 loops=1)

  • Filter: (freefieldcd = 3)
  • Rows Removed by Filter: 81
61.          

SubPlan (forWindowAgg)

62. 19.836 59.508 ↑ 1.0 1 19,836

Aggregate (cost=0.06..0.07 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=19,836)

63. 39.672 39.672 ↑ 1.0 4 19,836

Values Scan on "*VALUES*_5" (cost=0.00..0.05 rows=4 width=8) (actual time=0.000..0.002 rows=4 loops=19,836)

64.          

CTE isreassessmentsent

65. 129.915 821.456 ↓ 1.2 89,808 1

WindowAgg (cost=24,599.27..31,221.56 rows=73,581 width=25) (actual time=399.066..821.456 rows=89,808 loops=1)

66. 120.861 422.117 ↓ 1.2 89,808 1

Sort (cost=24,599.27..24,783.22 rows=73,581 width=41) (actual time=399.040..422.117 rows=89,808 loops=1)

  • Sort Key: ffv_3.creditfk, ffv_3.pkey DESC
  • Sort Method: external merge Disk: 3104kB
67. 194.273 301.256 ↓ 1.2 89,808 1

Hash Join (cost=3.24..16,386.83 rows=73,581 width=41) (actual time=0.224..301.256 rows=89,808 loops=1)

  • Hash Cond: (ffv_3.freefieldconfigfk = ffc_3.pkey)
68. 106.937 106.937 ↓ 1.0 735,823 1

Seq Scan on freefieldvalue ffv_3 (cost=0.00..14,361.13 rows=735,813 width=29) (actual time=0.164..106.937 rows=735,823 loops=1)

69. 0.007 0.046 ↑ 1.0 9 1

Hash (cost=3.12..3.12 rows=9 width=20) (actual time=0.046..0.046 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
70. 0.039 0.039 ↑ 1.0 9 1

Seq Scan on freefieldconfig ffc_3 (cost=0.00..3.12 rows=9 width=20) (actual time=0.019..0.039 rows=9 loops=1)

  • Filter: (freefieldcd = 4)
  • Rows Removed by Filter: 81
71.          

SubPlan (forWindowAgg)

72. 179.616 269.424 ↑ 1.0 1 89,808

Aggregate (cost=0.06..0.07 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=89,808)

73. 89.808 89.808 ↑ 1.0 4 89,808

Values Scan on "*VALUES*_6" (cost=0.00..0.05 rows=4 width=8) (actual time=0.000..0.001 rows=4 loops=89,808)

74.          

CTE allowedlocationsmodifiedprep

75. 4.787 7.399 ↑ 1.4 1,306 1

Foreign Scan on dbo_allowedlocation al (cost=100.00..240.27 rows=1,861 width=12) (actual time=4.483..7.399 rows=1,306 loops=1)

76.          

SubPlan (forForeign Scan)

77. 1.306 2.612 ↑ 1.0 1 1,306

Aggregate (cost=0.03..0.04 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=1,306)

78. 1.306 1.306 ↑ 1.0 2 1,306

Values Scan on "*VALUES*_7" (cost=0.00..0.03 rows=2 width=8) (actual time=0.000..0.001 rows=2 loops=1,306)

79.          

CTE allowedlocationsmodified

80. 0.961 8.949 ↓ 5.2 1,033 1

HashAggregate (cost=46.53..48.53 rows=200 width=12) (actual time=8.695..8.949 rows=1,033 loops=1)

  • Group Key: allowedlocationsmodifiedprep.subframeworkagreemententityid
81. 7.988 7.988 ↑ 1.4 1,306 1

CTE Scan on allowedlocationsmodifiedprep (cost=0.00..37.22 rows=1,861 width=12) (actual time=4.485..7.988 rows=1,306 loops=1)

82.          

CTE allowedlocationsstringset

83. 15.521 31.619 ↓ 31.8 6,351 1

GroupAggregate (cost=314.46..991.47 rows=200 width=36) (actual time=13.125..31.619 rows=6,351 loops=1)

  • Group Key: sfa.pkey
84. 2.293 16.098 ↑ 2.8 6,624 1

Merge Left Join (cost=314.46..708.17 rows=18,720 width=40) (actual time=13.108..16.098 rows=6,624 loops=1)

  • Merge Cond: (sfa.pkey = al_1.subframeworkagreementfk)
85. 9.983 9.983 ↓ 2.2 6,351 1

Foreign Scan on dbo_subframeworkagreement sfa (cost=100.00..205.60 rows=2,925 width=4) (actual time=9.407..9.983 rows=6,351 loops=1)

86. 0.684 3.822 ↓ 1.0 1,306 1

Sort (cost=214.46..217.66 rows=1,280 width=40) (actual time=3.696..3.822 rows=1,306 loops=1)

  • Sort Key: al_1.subframeworkagreementfk
  • Sort Method: quicksort Memory: 137kB
87. 3.138 3.138 ↓ 1.0 1,306 1

Foreign Scan on dbo_allowedlocation al_1 (cost=100.00..148.40 rows=1,280 width=40) (actual time=2.852..3.138 rows=1,306 loops=1)

88.          

CTE thirdpartywarrantycustom

89. 33.028 99.630 ↓ 1.0 31,447 1

WindowAgg (cost=7,303.80..7,932.72 rows=31,446 width=284) (actual time=60.057..99.630 rows=31,447 loops=1)

90. 45.595 66.602 ↓ 1.0 31,447 1

Sort (cost=7,303.80..7,382.42 rows=31,446 width=276) (actual time=60.045..66.602 rows=31,447 loops=1)

  • Sort Key: tpw.assetfk, tpw.rank
  • Sort Method: external merge Disk: 2408kB
91. 15.883 21.007 ↓ 1.0 31,447 1

Hash Left Join (cost=1.46..870.19 rows=31,446 width=276) (actual time=0.189..21.007 rows=31,447 loops=1)

  • Hash Cond: (tpw.warrantytypecd = pwt.codeid)
92. 5.104 5.104 ↓ 1.0 31,447 1

Seq Scan on thirdpartywarranty tpw (cost=0.00..776.46 rows=31,446 width=58) (actual time=0.158..5.104 rows=31,447 loops=1)

93. 0.006 0.020 ↓ 9.0 9 1

Hash (cost=1.45..1.45 rows=1 width=222) (actual time=0.020..0.020 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
94. 0.014 0.014 ↓ 9.0 9 1

Seq Scan on par_warrantytypecaption pwt (cost=0.00..1.45 rows=1 width=222) (actual time=0.009..0.014 rows=9 loops=1)

  • Filter: (languageid = 4)
  • Rows Removed by Filter: 27
95. 45.778 42,118.797 ↓ 25.0 50 1

Nested Loop Left Join (cost=193,305.31..210,834.90 rows=2 width=4,335) (actual time=27,736.084..42,118.797 rows=50 loops=1)

  • Join Filter: (allowedlocations.subframeworkagreemententityid = raalbase."SubframeworkAgreementEntityId")
  • Rows Removed by Join Filter: 317550
96. 7.058 41,972.969 ↓ 25.0 50 1

Nested Loop Left Join (cost=193,305.31..210,822.75 rows=2 width=4,686) (actual time=27,701.120..41,972.969 rows=50 loops=1)

  • Join Filter: (allowedlocationsmodified.subframeworkagreemententityid = raalbase."SubframeworkAgreementEntityId")
  • Rows Removed by Join Filter: 51650
97. 0.187 41,948.011 ↓ 25.0 50 1

Nested Loop Left Join (cost=193,305.31..210,811.75 rows=2 width=4,678) (actual time=27,691.177..41,948.011 rows=50 loops=1)

  • Join Filter: (pcdt.codeid = con.constructiondepottypecd)
  • Rows Removed by Join Filter: 46
98. 0.288 41,947.774 ↓ 25.0 50 1

Nested Loop Left Join (cost=193,305.31..210,810.52 rows=2 width=4,464) (actual time=27,691.159..41,947.774 rows=50 loops=1)

99. 886.582 41,946.986 ↓ 25.0 50 1

Nested Loop Left Join (cost=193,305.02..210,809.69 rows=2 width=4,444) (actual time=27,691.145..41,946.986 rows=50 loops=1)

  • Join Filter: (dide.creditfk = c.pkey)
  • Rows Removed by Join Filter: 4861925
100. 0.389 39,904.404 ↓ 25.0 50 1

Nested Loop Left Join (cost=193,305.02..203,791.17 rows=2 width=4,410) (actual time=27,690.978..39,904.404 rows=50 loops=1)

101. 0.697 39,902.959 ↓ 24.0 48 1

Nested Loop Left Join (cost=193,304.60..203,790.07 rows=2 width=3,639) (actual time=27,690.955..39,902.959 rows=48 loops=1)

  • Join Filter: (aftertransformmarketvalue.realestatefk = f3re."RealEstate1EntityId")
  • Rows Removed by Join Filter: 4080
102. 4.285 39,900.918 ↓ 24.0 48 1

Nested Loop Left Join (cost=193,304.60..203,790.02 rows=2 width=3,617) (actual time=27,690.274..39,900.918 rows=48 loops=1)

  • Join Filter: (actualmarketvalue.realestatefk = f3re."RealEstate1EntityId")
  • Rows Removed by Join Filter: 30720
103. 173.788 39,888.617 ↓ 24.0 48 1

Nested Loop Left Join (cost=193,304.60..203,789.85 rows=2 width=3,591) (actual time=27,687.056..39,888.617 rows=48 loops=1)

  • Join Filter: (thirdpartywarr.realestateentityid = f3re."RealEstate1EntityId")
  • Rows Removed by Join Filter: 1496826
104. 521.070 38,914.381 ↓ 24.0 48 1

Nested Loop Left Join (cost=193,304.60..203,076.82 rows=2 width=3,291) (actual time=27,584.523..38,914.381 rows=48 loops=1)

  • Join Filter: (f3re."LoanPartEntityId" = c.pkey)
  • Rows Removed by Join Filter: 1833930
105. 262.947 34,469.599 ↓ 24.0 48 1

Nested Loop Left Join (cost=138,333.93..146,981.48 rows=2 width=2,558) (actual time=25,778.808..34,469.599 rows=48 loops=1)

  • Join Filter: (fw."DossierEntityId" = cd.pkey)
  • Rows Removed by Join Filter: 1503594
106. 330.303 33,349.612 ↓ 24.0 48 1

Nested Loop Left Join (cost=102,927.33..110,774.07 rows=2 width=2,314) (actual time=25,381.712..33,349.612 rows=48 loops=1)

  • Join Filter: (f4g."LoanPartEntityId" = c.pkey)
  • Rows Removed by Join Filter: 1439899
107. 2,217.058 31,789.837 ↓ 24.0 48 1

Nested Loop Left Join (cost=75,435.98..82,507.65 rows=2 width=2,162) (actual time=24,727.262..31,789.837 rows=48 loops=1)

  • Join Filter: (cont."DossierEntityId" = cd.pkey)
  • Rows Removed by Join Filter: 4342092
108. 0.117 14,409.243 ↓ 24.0 48 1

Nested Loop Left Join (cost=46,115.76..53,185.66 rows=2 width=1,146) (actual time=14,406.850..14,409.243 rows=48 loops=1)

  • Join Filter: (credprov.pkey = pc.economicalownerfk)
  • Rows Removed by Join Filter: 13
109. 0.199 14,409.078 ↓ 24.0 48 1

Nested Loop Left Join (cost=46,115.76..53,162.00 rows=2 width=1,134) (actual time=14,406.830..14,409.078 rows=48 loops=1)

  • Join Filter: (ppnc.codeid = pc.productnamecd)
  • Rows Removed by Join Filter: 324
110. 0.140 14,408.783 ↓ 24.0 48 1

Nested Loop Left Join (cost=46,115.76..53,158.61 rows=2 width=1,112) (actual time=14,406.802..14,408.783 rows=48 loops=1)

  • Join Filter: (ppf.codeid = pc.productfamilycd)
  • Rows Removed by Join Filter: 7
111. 0.269 14,408.595 ↓ 24.0 48 1

Nested Loop Left Join (cost=46,115.76..53,157.48 rows=2 width=898) (actual time=14,406.786..14,408.595 rows=48 loops=1)

112. 0.366 14,407.702 ↓ 24.0 48 1

Merge Right Join (cost=46,115.62..53,157.15 rows=2 width=883) (actual time=14,406.762..14,407.702 rows=48 loops=1)

  • Merge Cond: (pc_1.pkey = pc.pkey)
113. 0.156 0.392 ↑ 2,069.8 48 1

Group (cost=0.29..5,799.92 rows=99,350 width=40) (actual time=0.020..0.392 rows=48 loops=1)

  • Group Key: pc_1.pkey
114. 0.236 0.236 ↑ 2,069.8 48 1

Index Scan using productcopy_pkey on productcopy pc_1 (cost=0.29..5,551.54 rows=99,350 width=8) (actual time=0.019..0.236 rows=48 loops=1)

115. 382.551 14,406.944 ↓ 24.0 48 1

Sort (cost=46,115.33..46,115.33 rows=2 width=887) (actual time=14,406.734..14,406.944 rows=48 loops=1)

  • Sort Key: pc.pkey
  • Sort Method: external sort Disk: 33560kB
116. 139.266 14,024.393 ↓ 49,673.0 99,346 1

Nested Loop Left Join (cost=34,214.94..46,115.32 rows=2 width=887) (actual time=6,529.859..14,024.393 rows=99,346 loops=1)

117. 80.896 13,587.089 ↓ 49,673.0 99,346 1

Nested Loop (cost=34,214.65..46,114.43 rows=2 width=859) (actual time=6,529.830..13,587.089 rows=99,346 loops=1)

  • Join Filter: (av.periodicitycd = pp.codeid)
  • Rows Removed by Join Filter: 298038
118. 0.017 0.017 ↓ 4.0 4 1

Seq Scan on par_periodicitycaption pp (cost=0.00..1.20 rows=1 width=222) (actual time=0.007..0.017 rows=4 loops=1)

  • Filter: (languageid = 4)
  • Rows Removed by Filter: 12
119. 987.524 13,506.176 ↓ 4,139.4 99,346 4

Hash Join (cost=34,214.65..46,112.93 rows=24 width=641) (actual time=2,749.576..3,376.544 rows=99,346 loops=4)

  • Hash Cond: (c.pkey = av.creditprecomputedfk)
120. 707.476 9,992.656 ↑ 1.0 99,346 4

Hash Left Join (cost=28,053.37..39,578.73 rows=99,383 width=393) (actual time=2,118.061..2,498.164 rows=99,346 loops=4)

  • Hash Cond: (cd.pkey = db.dossierentityid)
121. 662.512 7,869.776 ↑ 1.0 99,346 4

Hash Join (cost=28,046.87..36,837.05 rows=99,383 width=265) (actual time=1,764.056..1,967.444 rows=99,346 loops=4)

  • Hash Cond: (lpb.loanpartentityid = c.pkey)
122. 469.648 469.648 ↑ 1.0 99,346 4

CTE Scan on loanpartbalance lpb (cost=0.00..1,987.66 rows=99,383 width=98) (actual time=79.490..117.412 rows=99,346 loops=4)

123. 458.264 6,737.616 ↑ 1.0 99,346 4

Hash (cost=24,474.58..24,474.58 rows=99,383 width=167) (actual time=1,684.404..1,684.404 rows=99,346 loops=4)

  • Buckets: 32768 Batches: 8 Memory Usage: 2955kB
124. 618.632 6,279.352 ↑ 1.0 99,346 4

Hash Join (cost=10,902.04..24,474.58 rows=99,383 width=167) (actual time=965.064..1,569.838 rows=99,346 loops=4)

  • Hash Cond: (c.creditdossierfk = cd.pkey)
125. 287.888 5,363.900 ↑ 1.0 99,346 4

Merge Left Join (cost=6,685.32..15,286.97 rows=99,383 width=144) (actual time=890.572..1,340.975 rows=99,346 loops=4)

  • Merge Cond: (c.pkey = isreasssent.loanpartentityid)
126. 164.744 4,015.132 ↑ 1.0 99,346 4

Merge Left Join (cost=5,014.06..13,361.74 rows=99,383 width=135) (actual time=645.720..1,003.783 rows=99,346 loops=4)

  • Merge Cond: (c.pkey = reassdate.loanpartentityid)
127. 257.080 3,437.112 ↑ 1.0 99,346 4

Merge Left Join (cost=3,342.80..11,436.50 rows=99,383 width=123) (actual time=544.466..859.278 rows=99,346 loops=4)

  • Merge Cond: (c.pkey = propdate.loanpartentityid)
128. 657.224 2,021.448 ↑ 1.0 99,346 4

Merge Left Join (cost=1,671.55..9,511.27 rows=99,383 width=111) (actual time=273.381..505.362 rows=99,346 loops=4)

  • Merge Cond: (c.pkey = appdate.loanpartentityid)
129. 180.004 180.004 ↑ 1.0 99,346 4

Index Scan using credit_pkey on credit c (cost=0.29..7,586.04 rows=99,383 width=99) (actual time=0.016..45.001 rows=99,346 loops=4)

130. 187.438 1,184.220 ↓ 270.0 99,346 4

Sort (cost=1,671.26..1,672.18 rows=368 width=16) (actual time=273.356..296.055 rows=99,346 loops=4)

  • Sort Key: appdate.loanpartentityid
  • Sort Method: external sort Disk: 2920kB
131. 996.782 996.782 ↓ 270.0 99,346 1

CTE Scan on applicationdate appdate (cost=0.00..1,655.57 rows=368 width=16) (actual time=439.553..996.782 rows=99,346 loops=1)

  • Filter: (rn = 1)
132. 190.810 1,158.584 ↓ 251.4 92,503 4

Sort (cost=1,671.26..1,672.18 rows=368 width=16) (actual time=271.082..289.646 rows=92,503 loops=4)

  • Sort Key: propdate.loanpartentityid
  • Sort Method: external sort Disk: 2720kB
133. 967.774 967.774 ↓ 251.4 92,503 1

CTE Scan on proposaldate propdate (cost=0.00..1,655.57 rows=368 width=16) (actual time=464.854..967.774 rows=92,503 loops=1)

  • Filter: (rn = 1)
134. 13.394 413.276 ↓ 53.9 19,836 4

Sort (cost=1,671.26..1,672.18 rows=368 width=16) (actual time=101.251..103.319 rows=19,836 loops=4)

  • Sort Key: reassdate.loanpartentityid
  • Sort Method: quicksort Memory: 1698kB
135. 399.882 399.882 ↓ 53.9 19,836 1

CTE Scan on reassessmentdate reassdate (cost=0.00..1,655.57 rows=368 width=16) (actual time=302.259..399.882 rows=19,836 loops=1)

  • Filter: (rn = 1)
136. 169.778 1,060.880 ↓ 244.0 89,808 4

Sort (cost=1,671.26..1,672.18 rows=368 width=13) (actual time=244.848..265.220 rows=89,808 loops=4)

  • Sort Key: isreasssent.loanpartentityid
  • Sort Method: external sort Disk: 2640kB
137. 891.102 891.102 ↓ 244.0 89,808 1

CTE Scan on isreassessmentsent isreasssent (cost=0.00..1,655.57 rows=368 width=13) (actual time=399.070..891.102 rows=89,808 loops=1)

  • Filter: (rn = 1)
138. 150.036 296.820 ↓ 1.0 92,434 4

Hash (cost=2,429.32..2,429.32 rows=92,432 width=27) (actual time=74.205..74.205 rows=92,434 loops=4)

  • Buckets: 65536 Batches: 2 Memory Usage: 3364kB
139. 146.784 146.784 ↓ 1.0 92,434 4

Seq Scan on creditdossier cd (cost=0.00..2,429.32 rows=92,432 width=27) (actual time=0.117..36.696 rows=92,434 loops=4)

140. 162.744 1,415.404 ↓ 461.9 92,384 4

Hash (cost=4.00..4.00 rows=200 width=132) (actual time=353.851..353.851 rows=92,384 loops=4)

  • Buckets: 65536 (originally 1024) Batches: 2 (originally 1) Memory Usage: 3585kB
141. 1,252.660 1,252.660 ↓ 461.9 92,384 4

CTE Scan on dossierbalance db (cost=0.00..4.00 rows=200 width=132) (actual time=224.160..313.165 rows=92,384 loops=4)

142. 244.480 2,525.996 ↓ 4,139.4 99,346 4

Hash (cost=6,160.98..6,160.98 rows=24 width=256) (actual time=631.499..631.499 rows=99,346 loops=4)

  • Buckets: 65536 (originally 1024) Batches: 4 (originally 1) Memory Usage: 3585kB
143. 805.092 2,281.516 ↓ 4,139.4 99,346 4

Nested Loop (cost=0.00..6,160.98 rows=24 width=256) (actual time=0.147..570.379 rows=99,346 loops=4)

  • Join Filter: (av.amortizationshedulecd = pasc.codeid)
  • Rows Removed by Join Filter: 596076
144. 0.068 0.068 ↓ 7.0 7 4

Seq Scan on par_amortizationshedulecaption pasc (cost=0.00..1.35 rows=1 width=222) (actual time=0.007..0.017 rows=7 loops=4)

  • Filter: (languageid = 4)
  • Rows Removed by Filter: 21
145. 1,476.356 1,476.356 ↓ 144.8 99,346 28

Seq Scan on amortizationversion av (cost=0.00..6,151.06 rows=686 width=42) (actual time=0.183..52.727 rows=99,346 loops=28)

  • Filter: ((isactive)::integer = 1)
  • Rows Removed by Filter: 37766
146. 298.038 298.038 ↑ 1.0 1 99,346

Index Scan using productcopy_pkey on productcopy pc (cost=0.29..0.44 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=99,346)

  • Index Cond: (pkey = c.productcopyfk)
147. 0.624 0.624 ↑ 1.0 1 48

Index Scan using par_creditstatuscaption_pkey on par_creditstatuscaption pcs (cost=0.14..0.16 rows=1 width=19) (actual time=0.013..0.013 rows=1 loops=48)

  • Index Cond: ((codeid = c.creditstatuscd) AND (languageid = 4))
148. 0.039 0.048 ↑ 1.0 1 48

Materialize (cost=0.00..1.10 rows=1 width=222) (actual time=0.001..0.001 rows=1 loops=48)

149. 0.009 0.009 ↓ 2.0 2 1

Seq Scan on par_productfamilycaption ppf (cost=0.00..1.10 rows=1 width=222) (actual time=0.008..0.009 rows=2 loops=1)

  • Filter: (languageid = 4)
  • Rows Removed by Filter: 6
150. 0.068 0.096 ↑ 3.6 8 48

Materialize (cost=0.00..2.60 rows=29 width=26) (actual time=0.001..0.002 rows=8 loops=48)

151. 0.028 0.028 ↑ 1.1 26 1

Seq Scan on par_productnamecaption ppnc (cost=0.00..2.45 rows=29 width=26) (actual time=0.008..0.028 rows=26 loops=1)

  • Filter: (languageid = 4)
  • Rows Removed by Filter: 78
152. 0.040 0.048 ↑ 439.0 1 48

Materialize (cost=0.00..11.59 rows=439 width=24) (actual time=0.001..0.001 rows=1 loops=48)

153. 0.008 0.008 ↑ 219.5 2 1

Seq Scan on creditprovider credprov (cost=0.00..9.39 rows=439 width=24) (actual time=0.006..0.008 rows=2 loops=1)

154. 1,576.288 15,163.536 ↓ 22,615.2 90,461 48

Materialize (cost=29,320.22..29,321.89 rows=4 width=1,020) (actual time=215.014..315.907 rows=90,461 loops=48)

155. 46.175 13,587.248 ↓ 23,096.2 92,385 1

Subquery Scan on cont (cost=29,320.22..29,321.87 rows=4 width=1,020) (actual time=10,320.400..13,587.248 rows=92,385 loops=1)

156. 13,356.303 13,541.073 ↓ 23,096.2 92,385 1

CTE Scan on grouped (cost=29,320.22..29,321.83 rows=4 width=1,408) (actual time=10,320.398..13,541.073 rows=92,385 loops=1)

157.          

CTE contractors

158. 434.833 5,646.999 ↓ 199.9 156,940 1

WindowAgg (cost=21,605.06..21,734.59 rows=785 width=1,154) (actual time=4,324.356..5,646.999 rows=156,940 loops=1)

159. 438.465 4,427.466 ↓ 199.9 156,940 1

Sort (cost=21,605.06..21,607.02 rows=785 width=664) (actual time=4,324.323..4,427.466 rows=156,940 loops=1)

  • Sort Key: cd_1.pkey, r.roletypecd, r.pkey
  • Sort Method: external merge Disk: 28296kB
160. 212.247 3,989.001 ↓ 199.9 156,940 1

Nested Loop Left Join (cost=9,676.12..21,567.32 rows=785 width=664) (actual time=1,038.893..3,989.001 rows=156,940 loops=1)

161. 209.680 3,305.934 ↓ 199.9 156,940 1

Nested Loop Left Join (cost=9,675.83..21,268.36 rows=785 width=648) (actual time=1,038.882..3,305.934 rows=156,940 loops=1)

162. 104.804 2,468.494 ↓ 199.9 156,940 1

Hash Left Join (cost=9,675.53..17,017.01 rows=785 width=628) (actual time=1,038.865..2,468.494 rows=156,940 loops=1)

  • Hash Cond: (p.partytypecd = ppt.codeid)
163. 112.548 2,363.681 ↓ 199.9 156,940 1

Hash Left Join (cost=9,674.42..17,012.90 rows=785 width=410) (actual time=1,038.844..2,363.681 rows=156,940 loops=1)

  • Hash Cond: (pd.gendercd = par_gendercaption.codeid)
164. 83.305 2,251.121 ↓ 199.9 156,940 1

Nested Loop Left Join (cost=9,673.26..17,008.97 rows=785 width=196) (actual time=1,038.803..2,251.121 rows=156,940 loops=1)

165. 466.130 1,540.056 ↓ 199.9 156,940 1

Hash Right Join (cost=9,672.84..16,609.06 rows=785 width=196) (actual time=1,038.784..1,540.056 rows=156,940 loops=1)

  • Hash Cond: ((pd.isactivepartydetail)::text = (p.partydetaillinkid)::text)
166. 38.097 38.097 ↓ 1.0 170,431 1

Seq Scan on partydetail pd (cost=0.00..6,289.27 rows=170,427 width=91) (actual time=0.137..38.097 rows=170,431 loops=1)

167. 178.257 1,035.829 ↓ 199.9 156,940 1

Hash (cost=9,663.03..9,663.03 rows=785 width=179) (actual time=1,035.829..1,035.829 rows=156,940 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 8 (originally 1) Memory Usage: 3841kB
168. 163.459 857.572 ↓ 199.9 156,940 1

Nested Loop (cost=0.42..9,663.03 rows=785 width=179) (actual time=0.154..857.572 rows=156,940 loops=1)

169. 66.353 66.353 ↓ 199.9 156,940 1

Seq Scan on role r (cost=0.00..4,711.59 rows=785 width=32) (actual time=0.139..66.353 rows=156,940 loops=1)

  • Filter: ((roletypecd = ANY ('{1,2}'::integer[])) AND ((activerole)::integer = 1))
  • Rows Removed by Filter: 68
170. 627.760 627.760 ↑ 1.0 1 156,940

Index Scan using party_pkey on party p (cost=0.42..6.31 rows=1 width=155) (actual time=0.004..0.004 rows=1 loops=156,940)

  • Index Cond: (pkey = r.partyfk)
171. 627.760 627.760 ↑ 1.0 1 156,940

Index Scan using partydetailregion_pkey on partydetailregion pdr (cost=0.42..0.51 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=156,940)

  • Index Cond: (pd.partydetailregionfk = pkey)
172. 0.004 0.012 ↓ 3.0 3 1

Hash (cost=1.15..1.15 rows=1 width=222) (actual time=0.012..0.012 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
173. 0.008 0.008 ↓ 3.0 3 1

Seq Scan on par_gendercaption (cost=0.00..1.15 rows=1 width=222) (actual time=0.006..0.008 rows=3 loops=1)

  • Filter: (languageid = 4)
  • Rows Removed by Filter: 9
174. 0.004 0.009 ↓ 2.0 2 1

Hash (cost=1.10..1.10 rows=1 width=222) (actual time=0.009..0.009 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
175. 0.005 0.005 ↓ 2.0 2 1

Seq Scan on par_partytypecaption ppt (cost=0.00..1.10 rows=1 width=222) (actual time=0.004..0.005 rows=2 loops=1)

  • Filter: (languageid = 4)
  • Rows Removed by Filter: 6
176. 627.760 627.760 ↑ 1.0 1 156,940

Index Scan using credit_pkey on credit c_2 (cost=0.29..5.42 rows=1 width=24) (actual time=0.004..0.004 rows=1 loops=156,940)

  • Index Cond: (pkey = r.creditfk)
177. 470.820 470.820 ↑ 1.0 1 156,940

Index Scan using creditdossier_pkey on creditdossier cd_1 (cost=0.29..0.38 rows=1 width=20) (actual time=0.003..0.003 rows=1 loops=156,940)

  • Index Cond: (pkey = c_2.creditdossierfk)
178.          

SubPlan (forWindowAgg)

179. 313.880 784.700 ↑ 1.0 1 156,940

Aggregate (cost=0.12..0.13 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=156,940)

180. 470.820 470.820 ↑ 1.0 8 156,940

Values Scan on "*VALUES*_9" (cost=0.00..0.10 rows=8 width=8) (actual time=0.000..0.003 rows=8 loops=156,940)

181.          

CTE grouped

182. 1,038.776 11,464.219 ↓ 23,096.2 92,385 1

GroupAggregate (cost=7,584.64..7,585.63 rows=4 width=1,140) (actual time=10,320.339..11,464.219 rows=92,385 loops=1)

  • Group Key: con_1.dossierentityid
183. 323.588 10,425.443 ↓ 37,204.8 148,819 1

Sort (cost=7,584.64..7,584.65 rows=4 width=4,040) (actual time=10,320.304..10,425.443 rows=148,819 loops=1)

  • Sort Key: con_1.dossierentityid
  • Sort Method: external merge Disk: 24888kB
184. 1,324.971 10,101.855 ↓ 37,204.8 148,819 1

Hash Right Join (cost=7,254.70..7,584.60 rows=4 width=4,040) (actual time=6,546.781..10,101.855 rows=148,819 loops=1)

  • Hash Cond: (conadd.partydetailfk = con_1.pdpkey)
185. 56.339 2,649.912 ↓ 31,958.6 159,793 1

Subquery Scan on conadd (cost=7,236.98..7,566.86 rows=5 width=84) (actual time=408.400..2,649.912 rows=159,793 loops=1)

  • Filter: (conadd.rn = 1)
  • Rows Removed by Filter: 28937
186. 2,106.430 2,593.573 ↓ 185.9 188,730 1

WindowAgg (cost=7,236.98..7,554.17 rows=1,015 width=96) (actual time=408.399..2,593.573 rows=188,730 loops=1)

187. 361.584 487.143 ↓ 185.9 188,730 1

Sort (cost=7,236.98..7,239.52 rows=1,015 width=62) (actual time=408.331..487.143 rows=188,730 loops=1)

  • Sort Key: address.partydetailfk, address.addresstypecd, address.pkey
  • Sort Method: external merge Disk: 15336kB
188. 125.559 125.559 ↓ 185.9 188,730 1

Seq Scan on address (cost=0.00..7,186.30 rows=1,015 width=62) (actual time=0.174..125.559 rows=188,730 loops=1)

  • Filter: ((addresstypecd = ANY ('{1,11}'::integer[])) AND ((isactive)::integer = 1))
  • Rows Removed by Filter: 14892
189. 153.966 6,126.972 ↓ 37,082.5 148,330 1

Hash (cost=17.66..17.66 rows=4 width=3,964) (actual time=6,126.972..6,126.972 rows=148,330 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 8 (originally 1) Memory Usage: 3841kB
190. 5,973.006 5,973.006 ↓ 37,082.5 148,330 1

CTE Scan on contractors con_1 (cost=0.00..17.66 rows=4 width=3,964) (actual time=4,324.361..5,973.006 rows=148,330 loops=1)

  • Filter: (rn = 1)
  • Rows Removed by Filter: 8610
191.          

SubPlan (forCTE Scan)

192. 92.385 184.770 ↑ 1.0 1 92,385

Aggregate (cost=0.05..0.06 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=92,385)

193. 92.385 92.385 ↑ 1.0 3 92,385

Values Scan on "*VALUES*_10" (cost=0.00..0.04 rows=3 width=8) (actual time=0.000..0.001 rows=3 loops=92,385)

194. 319.969 1,229.472 ↓ 181.8 29,998 48

Materialize (cost=27,491.35..28,261.88 rows=165 width=156) (actual time=13.640..25.614 rows=29,998 loops=48)

195. 7.571 909.503 ↓ 181.8 29,998 1

Subquery Scan on f4g (cost=27,491.35..28,261.05 rows=165 width=156) (actual time=654.443..909.503 rows=29,998 loops=1)

196. 58.356 901.932 ↓ 181.8 29,998 1

Hash Left Join (cost=27,491.35..28,259.40 rows=165 width=1,296) (actual time=654.442..901.932 rows=29,998 loops=1)

  • Hash Cond: (rn1.entityid = rn4.entityid)
197.          

CTE guaranteesprep

198. 68.125 537.917 ↑ 1.1 30,001 1

WindowAgg (cost=20,978.33..25,261.31 rows=32,946 width=324) (actual time=339.800..537.917 rows=30,001 loops=1)

199. 63.727 349.788 ↑ 1.1 30,001 1

Sort (cost=20,978.33..21,060.69 rows=32,946 width=344) (actual time=339.767..349.788 rows=30,001 loops=1)

  • Sort Key: c_3.pkey, (CASE WHEN (gt.codeid = 3) THEN 0 ELSE gt.codeid END), coll.pkey
  • Sort Method: external merge Disk: 4432kB
200. 15.109 286.061 ↑ 1.1 30,001 1

Hash Left Join (cost=4,652.66..13,326.09 rows=32,946 width=344) (actual time=107.681..286.061 rows=30,001 loops=1)

  • Hash Cond: (coll.guaranteetypecd = gt.codeid)
201. 74.991 270.935 ↑ 1.1 30,001 1

Hash Join (cost=4,651.35..13,148.55 rows=32,946 width=122) (actual time=107.646..270.935 rows=30,001 loops=1)

  • Hash Cond: (c_3.pkey = c2c.creditfk)
202. 88.719 88.719 ↑ 1.0 99,346 1

Seq Scan on credit c_3 (cost=0.00..5,989.83 rows=99,383 width=24) (actual time=0.155..88.719 rows=99,346 loops=1)

203. 19.277 107.225 ↑ 1.1 30,001 1

Hash (cost=3,724.52..3,724.52 rows=32,946 width=102) (actual time=107.225..107.225 rows=30,001 loops=1)

  • Buckets: 32768 Batches: 2 Memory Usage: 2414kB
204. 34.647 87.948 ↑ 1.1 30,001 1

Hash Join (cost=2,290.43..3,724.52 rows=32,946 width=102) (actual time=42.983..87.948 rows=30,001 loops=1)

  • Hash Cond: (c2c.collateralfk = coll.pkey)
205. 10.499 10.499 ↓ 1.0 68,208 1

Seq Scan on collateral2credit c2c (cost=0.00..1,255.05 rows=68,205 width=24) (actual time=0.146..10.499 rows=68,208 loops=1)

206. 19.022 42.802 ↓ 1.0 29,602 1

Hash (cost=1,922.56..1,922.56 rows=29,429 width=82) (actual time=42.802..42.802 rows=29,602 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 4022kB
207. 23.780 23.780 ↓ 1.0 29,602 1

Seq Scan on collateral coll (cost=0.00..1,922.56 rows=29,429 width=82) (actual time=0.138..23.780 rows=29,602 loops=1)

  • Filter: ((classname)::text = 'Guarantee'::text)
  • Rows Removed by Filter: 31325
208. 0.007 0.017 ↓ 6.0 6 1

Hash (cost=1.30..1.30 rows=1 width=222) (actual time=0.017..0.017 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
209. 0.010 0.010 ↓ 6.0 6 1

Seq Scan on par_guaranteetypecaption gt (cost=0.00..1.30 rows=1 width=222) (actual time=0.007..0.010 rows=6 loops=1)

  • Filter: (languageid = 4)
  • Rows Removed by Filter: 18
210.          

SubPlan (forWindowAgg)

211. 60.002 120.004 ↑ 1.0 1 30,001

Aggregate (cost=0.09..0.10 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=30,001)

212. 60.002 60.002 ↑ 1.0 6 30,001

Values Scan on "*VALUES*_11" (cost=0.00..0.08 rows=6 width=8) (actual time=0.000..0.002 rows=6 loops=30,001)

213. 8.715 713.096 ↓ 181.8 29,998 1

Hash Left Join (cost=1,486.69..2,238.12 rows=165 width=362) (actual time=643.927..713.096 rows=29,998 loops=1)

  • Hash Cond: (rn1.entityid = rn3.entityid)
214. 8.650 693.981 ↓ 181.8 29,998 1

Hash Left Join (cost=743.35..1,489.70 rows=165 width=354) (actual time=633.509..693.981 rows=29,998 loops=1)

  • Hash Cond: (rn1.entityid = rn2.entityid)
215. 391.646 391.646 ↓ 181.8 29,998 1

CTE Scan on guaranteesprep rn1 (cost=0.00..741.28 rows=165 width=346) (actual time=339.805..391.646 rows=29,998 loops=1)

  • Filter: (rn = 1)
  • Rows Removed by Filter: 3
216. 0.011 293.685 ↑ 55.0 3 1

Hash (cost=741.28..741.28 rows=165 width=12) (actual time=293.685..293.685 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
217. 293.674 293.674 ↑ 55.0 3 1

CTE Scan on guaranteesprep rn2 (cost=0.00..741.28 rows=165 width=12) (actual time=164.918..293.674 rows=3 loops=1)

  • Filter: (rn = 2)
  • Rows Removed by Filter: 29998
218. 0.001 10.400 ↓ 0.0 0 1

Hash (cost=741.28..741.28 rows=165 width=12) (actual time=10.400..10.400 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
219. 10.399 10.399 ↓ 0.0 0 1

CTE Scan on guaranteesprep rn3 (cost=0.00..741.28 rows=165 width=12) (actual time=10.399..10.399 rows=0 loops=1)

  • Filter: (rn = 3)
  • Rows Removed by Filter: 30001
220. 0.001 10.488 ↓ 0.0 0 1

Hash (cost=741.28..741.28 rows=165 width=12) (actual time=10.488..10.488 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
221. 10.487 10.487 ↓ 0.0 0 1

CTE Scan on guaranteesprep rn4 (cost=0.00..741.28 rows=165 width=12) (actual time=10.487..10.487 rows=0 loops=1)

  • Filter: (rn = 4)
  • Rows Removed by Filter: 30001
222.          

SubPlan (forHash Left Join)

223. 59.996 119.992 ↑ 1.0 1 29,998

Aggregate (cost=0.06..0.07 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=29,998)

224. 59.996 59.996 ↑ 1.0 4 29,998

Values Scan on "*VALUES*_12" (cost=0.00..0.05 rows=4 width=8) (actual time=0.000..0.002 rows=4 loops=29,998)

225. 142.667 857.040 ↓ 178.0 31,325 48

Materialize (cost=35,406.60..36,202.57 rows=176 width=248) (actual time=8.273..17.855 rows=31,325 loops=48)

226. 6.224 714.373 ↓ 178.0 31,325 1

Subquery Scan on fw (cost=35,406.60..36,201.69 rows=176 width=248) (actual time=397.091..714.373 rows=31,325 loops=1)

227. 708.149 708.149 ↓ 178.0 31,325 1

CTE Scan on firstwarrantyprep (cost=35,406.60..36,199.93 rows=176 width=1,138) (actual time=397.089..708.149 rows=31,325 loops=1)

  • Filter: ("RN" = 1)
  • Rows Removed by Filter: 6882
228.          

CTE firstwarrantyprep

229. 64.733 627.395 ↓ 1.1 38,207 1

WindowAgg (cost=31,175.52..35,406.60 rows=35,259 width=820) (actual time=397.083..627.395 rows=38,207 loops=1)

230. 65.628 409.834 ↓ 1.1 38,207 1

Sort (cost=31,175.52..31,263.67 rows=35,259 width=813) (actual time=397.060..409.834 rows=38,207 loops=1)

  • Sort Key: c_4.creditdossierfk, coll_1.pkey
  • Sort Method: external merge Disk: 5432kB
231. 13.765 344.206 ↓ 1.1 38,207 1

Hash Left Join (cost=6,826.94..15,856.46 rows=35,259 width=813) (actual time=136.146..344.206 rows=38,207 loops=1)

  • Hash Cond: (coll_1.warrantysubtypecd = wst.codeid)
232. 17.416 330.428 ↓ 1.1 38,207 1

Hash Left Join (cost=6,825.72..15,762.70 rows=35,259 width=595) (actual time=136.120..330.428 rows=38,207 loops=1)

  • Hash Cond: (coll_1.warrantytypecd = wit.codeid)
233. 16.581 313.002 ↓ 1.1 38,207 1

Hash Left Join (cost=6,824.56..15,652.08 rows=35,259 width=377) (actual time=136.101..313.002 rows=38,207 loops=1)

  • Hash Cond: (coll_1.warrantytypecd = wt.codeid)
234. 67.079 296.401 ↓ 1.1 38,207 1

Hash Join (cost=6,823.10..15,552.43 rows=35,259 width=159) (actual time=136.053..296.401 rows=38,207 loops=1)

  • Hash Cond: (c_4.pkey = c2c_1.creditfk)
235. 93.676 93.676 ↑ 1.0 99,346 1

Seq Scan on credit c_4 (cost=0.00..5,989.83 rows=99,383 width=24) (actual time=0.174..93.676 rows=99,346 loops=1)

236. 22.690 135.646 ↓ 1.1 38,207 1

Hash (cost=5,658.36..5,658.36 rows=35,259 width=139) (actual time=135.646..135.646 rows=38,207 loops=1)

  • Buckets: 32768 Batches: 2 Memory Usage: 2230kB
237. 50.447 112.956 ↓ 1.1 38,207 1

Hash Join (cost=2,870.26..5,658.36 rows=35,259 width=139) (actual time=41.434..112.956 rows=38,207 loops=1)

  • Hash Cond: (c2c_1.collateralfk = coll_1.pkey)
238. 21.355 21.355 ↓ 1.0 68,208 1

Seq Scan on collateral2credit c2c_1 (cost=0.00..1,255.05 rows=68,205 width=24) (actual time=0.154..21.355 rows=68,208 loops=1)

239. 16.276 41.154 ↑ 1.0 31,325 1

Hash (cost=1,922.56..1,922.56 rows=31,496 width=119) (actual time=41.154..41.154 rows=31,325 loops=1)

  • Buckets: 32768 Batches: 2 Memory Usage: 1645kB
240. 24.878 24.878 ↑ 1.0 31,325 1

Seq Scan on collateral coll_1 (cost=0.00..1,922.56 rows=31,496 width=119) (actual time=0.146..24.878 rows=31,325 loops=1)

  • Filter: ((classname)::text = 'Warranty'::text)
  • Rows Removed by Filter: 29602
241. 0.006 0.020 ↓ 9.0 9 1

Hash (cost=1.45..1.45 rows=1 width=222) (actual time=0.020..0.020 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
242. 0.014 0.014 ↓ 9.0 9 1

Seq Scan on par_warrantytypecaption wt (cost=0.00..1.45 rows=1 width=222) (actual time=0.008..0.014 rows=9 loops=1)

  • Filter: (languageid = 4)
  • Rows Removed by Filter: 27
243. 0.003 0.010 ↓ 3.0 3 1

Hash (cost=1.15..1.15 rows=1 width=222) (actual time=0.010..0.010 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
244. 0.007 0.007 ↓ 3.0 3 1

Seq Scan on par_warrantyinscriptiontypecaption wit (cost=0.00..1.15 rows=1 width=222) (actual time=0.005..0.007 rows=3 loops=1)

  • Filter: (languageid = 4)
  • Rows Removed by Filter: 9
245. 0.004 0.013 ↓ 4.0 4 1

Hash (cost=1.20..1.20 rows=1 width=222) (actual time=0.013..0.013 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
246. 0.009 0.009 ↓ 4.0 4 1

Seq Scan on par_warrantysubtypecaption wst (cost=0.00..1.20 rows=1 width=222) (actual time=0.007..0.009 rows=4 loops=1)

  • Filter: (languageid = 4)
  • Rows Removed by Filter: 12
247.          

SubPlan (forWindowAgg)

248. 76.414 152.828 ↑ 1.0 1 38,207

Aggregate (cost=0.09..0.10 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=38,207)

249. 76.414 76.414 ↑ 1.0 6 38,207

Values Scan on "*VALUES*_13" (cost=0.00..0.08 rows=6 width=8) (actual time=0.000..0.002 rows=6 loops=38,207)

250. 478.461 3,923.712 ↓ 419.9 38,207 48

Materialize (cost=54,970.67..56,092.84 rows=91 width=737) (actual time=37.627..81.744 rows=38,207 loops=48)

251. 15.313 3,445.251 ↓ 419.9 38,207 1

Subquery Scan on f3re (cost=54,970.67..56,092.38 rows=91 width=737) (actual time=1,805.703..3,445.251 rows=38,207 loops=1)

252. 157.296 3,429.938 ↓ 419.9 38,207 1

Nested Loop (cost=54,970.67..56,091.47 rows=91 width=737) (actual time=1,805.701..3,429.938 rows=38,207 loops=1)

253.          

CTE distinctrealestates

254. 638.420 1,585.171 ↓ 2.1 38,394 1

Hash Left Join (cost=14,389.85..30,633.03 rows=18,209 width=409) (actual time=502.736..1,585.171 rows=38,394 loops=1)

  • Hash Cond: (a.addressfk = ad.pkey)
255. 111.601 377.034 ↓ 2.1 38,394 1

Hash Join (cost=7,699.87..14,244.48 rows=18,209 width=153) (actual time=240.040..377.034 rows=38,394 loops=1)

  • Hash Cond: (c_5.pkey = c2c_2.creditfk)
256. 25.742 25.742 ↑ 1.0 99,346 1

Seq Scan on credit c_5 (cost=0.00..5,989.83 rows=99,383 width=20) (actual time=0.169..25.742 rows=99,346 loops=1)

257. 27.833 239.691 ↓ 2.1 38,394 1

Hash (cost=7,472.26..7,472.26 rows=18,209 width=137) (actual time=239.690..239.691 rows=38,394 loops=1)

  • Buckets: 65536 (originally 32768) Batches: 2 (originally 1) Memory Usage: 3838kB
258. 38.917 211.858 ↓ 2.1 38,394 1

Hash Join (cost=5,779.35..7,472.26 rows=18,209 width=137) (actual time=162.048..211.858 rows=38,394 loops=1)

  • Hash Cond: (c2c_2.collateralfk = col.pkey)
259. 11.070 11.070 ↓ 1.0 68,208 1

Seq Scan on collateral2credit c2c_2 (cost=0.00..1,255.05 rows=68,205 width=24) (actual time=0.156..11.070 rows=68,208 loops=1)

260. 18.171 161.871 ↓ 1.9 31,464 1

Hash (cost=5,576.04..5,576.04 rows=16,265 width=125) (actual time=161.871..161.871 rows=31,464 loops=1)

  • Buckets: 32768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 3704kB
261. 19.328 143.700 ↓ 1.9 31,464 1

Hash Join (cost=4,499.41..5,576.04 rows=16,265 width=125) (actual time=95.569..143.700 rows=31,464 loops=1)

  • Hash Cond: (w2a.assetfk = a.pkey)
262. 18.390 96.351 ↓ 1.9 31,464 1

Hash Join (cost=3,242.81..4,276.73 rows=16,265 width=64) (actual time=67.512..96.351 rows=31,464 loops=1)

  • Hash Cond: (w2ash.warranty2assetfk = w2a.pkey)
263. 10.640 10.640 ↓ 1.0 31,464 1

Seq Scan on warranty2assetstatushistory w2ash (cost=0.00..753.29 rows=31,463 width=20) (actual time=0.156..10.640 rows=31,464 loops=1)

  • Filter: (warranty2assetstatuscd = 1)
264. 14.229 67.321 ↓ 1.9 31,464 1

Hash (cost=3,039.49..3,039.49 rows=16,265 width=52) (actual time=67.321..67.321 rows=31,464 loops=1)

  • Buckets: 32768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 2720kB
265. 18.353 53.092 ↓ 1.9 31,464 1

Hash Join (cost=2,316.26..3,039.49 rows=16,265 width=52) (actual time=29.973..53.092 rows=31,464 loops=1)

  • Hash Cond: (w2a.warrantyfk = col.pkey)
266. 4.958 4.958 ↓ 1.0 31,464 1

Seq Scan on warranty2asset w2a (cost=0.00..640.63 rows=31,463 width=32) (actual time=0.148..4.958 rows=31,464 loops=1)

267. 9.448 29.781 ↑ 1.0 31,325 1

Hash (cost=1,922.56..1,922.56 rows=31,496 width=20) (actual time=29.781..29.781 rows=31,325 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1608kB
268. 20.333 20.333 ↑ 1.0 31,325 1

Seq Scan on collateral col (cost=0.00..1,922.56 rows=31,496 width=20) (actual time=0.143..20.333 rows=31,325 loops=1)

  • Filter: ((classname)::text = 'Warranty'::text)
  • Rows Removed by Filter: 29602
269. 11.546 28.021 ↓ 1.0 31,465 1

Hash (cost=863.30..863.30 rows=31,464 width=65) (actual time=28.021..28.021 rows=31,465 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 2224kB
270. 16.475 16.475 ↓ 1.0 31,465 1

Seq Scan on asset a (cost=0.00..863.30 rows=31,464 width=65) (actual time=0.151..16.475 rows=31,465 loops=1)

  • Filter: ((classname)::text = 'RealEstate'::text)
271. 138.313 262.565 ↓ 186.0 189,381 1

Hash (cost=6,677.26..6,677.26 rows=1,018 width=333) (actual time=262.565..262.565 rows=189,381 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 8 (originally 1) Memory Usage: 3585kB
272. 124.252 124.252 ↓ 186.0 189,381 1

Seq Scan on address ad (cost=0.00..6,677.26 rows=1,018 width=333) (actual time=0.162..124.252 rows=189,381 loops=1)

  • Filter: ((isactive)::integer = 1)
  • Rows Removed by Filter: 14241
273.          

SubPlan (forHash Left Join)

274. 115.182 307.152 ↑ 1.0 1 38,394

Aggregate (cost=0.21..0.22 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=38,394)

275. 191.970 191.970 ↑ 1.0 14 38,394

Values Scan on "*VALUES*_14" (cost=0.00..0.18 rows=14 width=8) (actual time=0.000..0.005 rows=14 loops=38,394)

276.          

CTE realestateaddressesfields

277. 1,036.905 2,977.601 ↓ 2.1 38,394 1

WindowAgg (cost=15,414.94..24,337.35 rows=18,209 width=542) (actual time=1,805.659..2,977.601 rows=38,394 loops=1)

278. 89.879 1,825.514 ↓ 2.1 38,394 1

Sort (cost=15,414.94..15,460.46 rows=18,209 width=1,566) (actual time=1,805.528..1,825.514 rows=38,394 loops=1)

  • Sort Key: dre.loanpartentityid, dre.isactive, dre."Rank", a_1.pkey
  • Sort Method: external merge Disk: 4832kB
279. 23.465 1,735.635 ↓ 2.1 38,394 1

Hash Left Join (cost=1,279.35..1,739.94 rows=18,209 width=1,566) (actual time=526.821..1,735.635 rows=38,394 loops=1)

  • Hash Cond: (dre.countrycd = country.codeid)
280. 42.936 1,711.896 ↓ 2.1 38,394 1

Hash Join (cost=1,256.60..1,668.59 rows=18,209 width=1,559) (actual time=526.527..1,711.896 rows=38,394 loops=1)

  • Hash Cond: (dre.realestateentityid = a_1.pkey)
281. 1,645.225 1,645.225 ↓ 2.1 38,394 1

CTE Scan on distinctrealestates dre (cost=0.00..364.18 rows=18,209 width=1,543) (actual time=502.740..1,645.225 rows=38,394 loops=1)

282. 9.414 23.735 ↓ 1.0 31,465 1

Hash (cost=863.30..863.30 rows=31,464 width=20) (actual time=23.735..23.735 rows=31,465 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1854kB
283. 14.321 14.321 ↓ 1.0 31,465 1

Seq Scan on asset a_1 (cost=0.00..863.30 rows=31,464 width=20) (actual time=0.136..14.321 rows=31,465 loops=1)

  • Filter: ((classname)::text = 'RealEstate'::text)
284. 0.077 0.274 ↑ 1.0 252 1

Hash (cost=19.60..19.60 rows=252 width=15) (actual time=0.274..0.274 rows=252 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
285. 0.197 0.197 ↑ 1.0 252 1

Seq Scan on par_countrycaption country (cost=0.00..19.60 rows=252 width=15) (actual time=0.011..0.197 rows=252 loops=1)

  • Filter: (languageid = 4)
  • Rows Removed by Filter: 756
286.          

SubPlan (forWindowAgg)

287. 38.394 115.182 ↑ 1.0 1 38,394

Aggregate (cost=0.05..0.06 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=38,394)

288. 76.788 76.788 ↑ 1.0 3 38,394

Values Scan on "*VALUES*_15" (cost=0.00..0.04 rows=3 width=8) (actual time=0.001..0.002 rows=3 loops=38,394)

289. 3,081.607 3,081.607 ↓ 419.9 38,207 1

CTE Scan on realestateaddressesfields reaf1 (cost=0.00..409.70 rows=91 width=737) (actual time=1,805.665..3,081.607 rows=38,207 loops=1)

  • Filter: (rn = 1)
  • Rows Removed by Filter: 187
290. 152.828 152.828 ↑ 1.0 1 38,207

Index Only Scan using credit_pkey on credit c_1 (cost=0.29..7.78 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=38,207)

  • Index Cond: (pkey = reaf1.loanpartentityid)
  • Heap Fetches: 38207
291.          

SubPlan (forNested Loop)

292. 38.207 38.207 ↑ 1.0 1 38,207

Aggregate (cost=0.01..0.02 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=38,207)

293. 0.000 0.000 ↑ 1.0 1 38,207

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=38,207)

294. 800.448 800.448 ↓ 198.6 31,184 48

CTE Scan on thirdpartywarrantycustom thirdpartywarr (cost=0.00..707.53 rows=157 width=304) (actual time=1.258..16.676 rows=31,184 loops=48)

  • Filter: (rn = 1)
  • Rows Removed by Filter: 263
295. 8.016 8.016 ↓ 213.3 640 48

CTE Scan on lastactualmarketvalue actualmarketvalue (cost=0.00..0.06 rows=3 width=30) (actual time=0.014..0.167 rows=640 loops=48)

296. 1.344 1.344 ↓ 85.0 85 48

CTE Scan on lastaftertransformationmarketvalue aftertransformmarketvalue (cost=0.00..0.02 rows=1 width=30) (actual time=0.007..0.028 rows=85 loops=48)

297. 1.056 1.056 ↑ 1.0 1 48

Index Scan using ix_runningaccountandloansbaseview__loanpartentityid on runningaccountandloansbaseview_ raalbase (cost=0.42..0.54 rows=1 width=775) (actual time=0.019..0.022 rows=1 loops=48)

  • Index Cond: ("LoanPartEntityId" = c.pkey)
298. 1,008.437 1,156.000 ↓ 26.5 97,240 50

Materialize (cost=0.00..6,917.73 rows=3,665 width=38) (actual time=0.010..23.120 rows=97,240 loops=50)

299. 147.563 147.563 ↓ 27.1 99,223 1

Seq Scan on directdebit dide (cost=0.00..6,899.40 rows=3,665 width=38) (actual time=0.160..147.563 rows=99,223 loops=1)

  • Filter: ((startdate < CURRENT_TIMESTAMP) AND (COALESCE((enddate)::timestamp with time zone, CURRENT_TIMESTAMP) >= CURRENT_TIMESTAMP) AND (COALESCE((suspendperiodenddate)::timestamp with time zone, (CURRENT_TIMESTAMP + '1 day'::interval)) > CURRENT_TIMESTAMP) AND (COALESCE((suspendperiodstartdate)::timestamp with time zone, (CURRENT_TIMESTAMP - '1 day'::interval)) < CURRENT_TIMESTAMP))
  • Rows Removed by Filter: 468
300. 0.500 0.500 ↑ 1.0 1 50

Index Scan using _ix_constructiondepot_creditfk on constructiondepot con (cost=0.29..0.40 rows=1 width=24) (actual time=0.008..0.010 rows=1 loops=50)

  • Index Cond: (creditfk = c.pkey)
301. 0.040 0.050 ↓ 2.0 2 50

Materialize (cost=0.00..1.20 rows=1 width=222) (actual time=0.001..0.001 rows=2 loops=50)

302. 0.010 0.010 ↓ 4.0 4 1

Seq Scan on par_constructiondepottypecaption pcdt (cost=0.00..1.20 rows=1 width=222) (actual time=0.007..0.010 rows=4 loops=1)

  • Filter: (languageid = 4)
  • Rows Removed by Filter: 12
303. 17.900 17.900 ↓ 5.2 1,033 50

CTE Scan on allowedlocationsmodified (cost=0.00..4.00 rows=200 width=12) (actual time=0.174..0.358 rows=1,033 loops=50)

304. 99.000 99.000 ↓ 31.8 6,351 50

CTE Scan on allowedlocationsstringset allowedlocations (cost=0.00..4.00 rows=200 width=36) (actual time=0.263..1.980 rows=6,351 loops=50)

305.          

SubPlan (forNested Loop Left Join)

306. 0.400 1.050 ↑ 1.0 1 50

Aggregate (cost=0.42..0.43 rows=1 width=8) (actual time=0.021..0.021 rows=1 loops=50)

307. 0.650 0.650 ↑ 1.0 28 50

Values Scan on "*VALUES*_8" (cost=0.00..0.35 rows=28 width=8) (actual time=0.002..0.013 rows=28 loops=50)

Planning time : 72.751 ms
Execution time : 42,207.120 ms