explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DIy

Settings
# exclusive inclusive rows x rows loops node
1. 3.563 30,772.349 ↓ 5,000.0 10,000 1

Limit (cost=188,026.02..193,180.38 rows=2 width=3,041) (actual time=5,192.159..30,772.349 rows=10,000 loops=1)

2.          

CTE _cte_loanpartbalance

3. 28.095 434.904 ↑ 11.9 8,372 1

Hash Right Join (cost=15,762.32..28,181.69 rows=99,358 width=63) (actual time=370.650..434.904 rows=8,372 loops=1)

  • Hash Cond: (pc_1.pkey = c_1.productcopyfk)
4. 78.154 98.285 ↑ 11.9 8,372 1

HashAggregate (cost=4,203.88..6,563.44 rows=99,350 width=40) (actual time=86.547..98.285 rows=8,372 loops=1)

  • Group Key: pc_1.pkey
5. 20.131 20.131 ↓ 1.0 99,353 1

Seq Scan on productcopy pc_1 (cost=0.00..3,955.50 rows=99,350 width=8) (actual time=0.137..20.131 rows=99,353 loops=1)

6. 53.515 283.408 ↑ 1.0 99,346 1

Hash (cost=10,316.47..10,316.47 rows=99,358 width=59) (actual time=283.408..283.408 rows=99,346 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 10385kB
7. 78.084 229.893 ↑ 1.0 99,346 1

Hash Right Join (cost=7,232.56..10,316.47 rows=99,358 width=59) (actual time=135.006..229.893 rows=99,346 loops=1)

  • Hash Cond: (cb.creditfk = c_1.pkey)
8. 17.595 17.595 ↑ 1.0 99,347 1

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

9. 40.724 134.214 ↑ 1.0 99,346 1

Hash (cost=5,990.58..5,990.58 rows=99,358 width=28) (actual time=134.214..134.214 rows=99,346 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 6846kB
10. 93.490 93.490 ↑ 1.0 99,346 1

Seq Scan on credit c_1 (cost=0.00..5,990.58 rows=99,358 width=28) (actual time=0.162..93.490 rows=99,346 loops=1)

11.          

SubPlan (forHash Right Join)

12. 8.372 25.116 ↑ 1.0 1 8,372

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

13. 16.744 16.744 ↑ 1.0 4 8,372

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

14.          

CTE _cte_dossierbalance

15. 261.767 357.633 ↓ 1.0 92,384 1

GroupAggregate (cost=10,098.26..13,350.67 rows=88,111 width=132) (actual time=83.727..357.633 rows=92,384 loops=1)

  • Group Key: lpb_1.dossierentityid
16. 65.333 95.866 ↑ 1.0 99,346 1

Sort (cost=10,098.26..10,346.63 rows=99,346 width=24) (actual time=83.695..95.866 rows=99,346 loops=1)

  • Sort Key: lpb_1.dossierentityid
  • Sort Method: quicksort Memory: 10834kB
17. 30.533 30.533 ↑ 1.0 99,346 1

Seq Scan on cte_loanpartbalance lpb_1 (cost=0.00..1,852.46 rows=99,346 width=24) (actual time=0.138..30.533 rows=99,346 loops=1)

18.          

CTE _cte_lastactualmarketvalue

19. 0.146 2.830 ↓ 213.3 640 1

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

  • Filter: (lastactualmarketvalueprep.rn = 1)
20. 0.699 2.684 ↑ 1.0 640 1

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

21. 0.264 0.705 ↑ 1.0 640 1

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

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

Seq Scan on realestatevalue rev (cost=0.00..37.19 rows=640 width=30) (actual time=0.008..0.441 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.021 0.625 ↓ 85.0 85 1

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

  • Filter: (lastaftertransformationmarketvalueprep.rn = 1)
28. 0.099 0.604 ↑ 1.0 85 1

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

29. 0.045 0.335 ↑ 1.0 85 1

Sort (cost=39.91..40.12 rows=85 width=30) (actual time=0.327..0.335 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.290 0.290 ↑ 1.0 85 1

Seq Scan on realestatevalue rev_1 (cost=0.00..37.19 rows=85 width=30) (actual time=0.007..0.290 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.002..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 _cte_applicationdate

35. 136.114 813.135 ↓ 1.4 99,346 1

WindowAgg (cost=22,334.77..28,957.06 rows=73,581 width=28) (actual time=369.141..813.135 rows=99,346 loops=1)

36. 60.994 378.983 ↓ 1.4 99,346 1

Sort (cost=22,334.77..22,518.72 rows=73,581 width=44) (actual time=369.111..378.983 rows=99,346 loops=1)

  • Sort Key: ffv.creditfk, ffv.pkey DESC
  • Sort Method: quicksort Memory: 10834kB
37. 203.363 317.989 ↓ 1.4 99,346 1

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

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

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

39. 0.008 0.037 ↑ 1.0 9 1

Hash (cost=3.12..3.12 rows=9 width=20) (actual time=0.037..0.037 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.009..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 _cte_proposaldate

45. 139.692 801.356 ↓ 1.3 92,503 1

WindowAgg (cost=22,334.77..28,957.06 rows=73,581 width=28) (actual time=361.630..801.356 rows=92,503 loops=1)

46. 71.466 384.155 ↓ 1.3 92,503 1

Sort (cost=22,334.77..22,518.72 rows=73,581 width=44) (actual time=361.599..384.155 rows=92,503 loops=1)

  • Sort Key: ffv_1.creditfk, ffv_1.pkey DESC
  • Sort Method: quicksort Memory: 10299kB
47. 201.485 312.689 ↓ 1.3 92,503 1

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

  • Hash Cond: (ffv_1.freefieldconfigfk = ffc_1.pkey)
48. 111.171 111.171 ↓ 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.139..111.171 rows=735,823 loops=1)

49. 0.006 0.033 ↑ 1.0 9 1

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

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

Seq Scan on freefieldconfig ffc_1 (cost=0.00..3.12 rows=9 width=20) (actual time=0.009..0.027 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 _cte_reassessmentdate

55. 24.668 394.737 ↑ 3.7 19,836 1

WindowAgg (cost=22,334.77..28,957.06 rows=73,581 width=28) (actual time=308.660..394.737 rows=19,836 loops=1)

56. 14.502 310.561 ↑ 3.7 19,836 1

Sort (cost=22,334.77..22,518.72 rows=73,581 width=44) (actual time=308.632..310.561 rows=19,836 loops=1)

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

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

  • Hash Cond: (ffv_2.freefieldconfigfk = ffc_2.pkey)
58. 112.865 112.865 ↓ 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.153..112.865 rows=735,823 loops=1)

59. 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
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.008..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.003..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 _cte_isreassessmentsent

65. 108.429 889.611 ↓ 1.2 89,808 1

WindowAgg (cost=22,334.77..28,957.06 rows=73,581 width=25) (actual time=502.772..889.611 rows=89,808 loops=1)

66. 69.665 511.758 ↓ 1.2 89,808 1

Sort (cost=22,334.77..22,518.72 rows=73,581 width=41) (actual time=502.745..511.758 rows=89,808 loops=1)

  • Sort Key: ffv_3.creditfk, ffv_3.pkey DESC
  • Sort Method: quicksort Memory: 10089kB
67. 276.522 442.093 ↓ 1.2 89,808 1

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

  • Hash Cond: (ffv_3.freefieldconfigfk = ffc_3.pkey)
68. 165.528 165.528 ↓ 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.161..165.528 rows=735,823 loops=1)

69. 0.007 0.043 ↑ 1.0 9 1

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

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

Seq Scan on freefieldconfig ffc_3 (cost=0.00..3.12 rows=9 width=20) (actual time=0.016..0.036 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.002..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 _cte_allowedlocationsmodifiedprep

75. 5.820 7.126 ↑ 1.4 1,306 1

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

76.          

SubPlan (forForeign Scan)

77. 0.000 1.306 ↑ 1.0 1 1,306

Aggregate (cost=0.03..0.04 rows=1 width=8) (actual time=0.001..0.001 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.991 8.724 ↓ 5.2 1,033 1

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

  • Group Key: _cte_allowedlocationsmodifiedprep.subframeworkagreemententityid
81. 7.733 7.733 ↑ 1.4 1,306 1

CTE Scan on _cte_allowedlocationsmodifiedprep (cost=0.00..37.22 rows=1,861 width=12) (actual time=4.264..7.733 rows=1,306 loops=1)

82.          

CTE _cte_allowedlocationsstringset

83. 15.685 32.480 ↓ 31.8 6,351 1

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

  • Group Key: sfa.pkey
84. 2.341 16.795 ↑ 2.8 6,624 1

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

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

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

86. 0.718 4.001 ↓ 1.0 1,306 1

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

  • Sort Key: al_1.subframeworkagreementfk
  • Sort Method: quicksort Memory: 137kB
87. 3.283 3.283 ↓ 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.996..3.283 rows=1,306 loops=1)

88. 159.734 30,768.786 ↓ 5,000.0 10,000 1

Nested Loop Left Join (cost=29,222.03..34,376.39 rows=2 width=3,041) (actual time=5,192.157..30,768.786 rows=10,000 loops=1)

  • Join Filter: (pcdt.codeid = con.constructiondepottypecd)
  • Rows Removed by Join Filter: 10505
89. 20.463 30,449.052 ↓ 5,000.0 10,000 1

Nested Loop Left Join (cost=29,222.03..34,374.01 rows=2 width=2,952) (actual time=5,192.081..30,449.052 rows=10,000 loops=1)

90. 17.608 30,368.589 ↓ 5,000.0 10,000 1

Nested Loop Left Join (cost=29,221.73..34,373.18 rows=2 width=2,932) (actual time=5,192.060..30,368.589 rows=10,000 loops=1)

91. 1,453.288 30,230.981 ↓ 5,000.0 10,000 1

Nested Loop Left Join (cost=29,221.44..34,372.28 rows=2 width=2,898) (actual time=5,192.027..30,230.981 rows=10,000 loops=1)

  • Join Filter: (allowedlocationsmodified.subframeworkagreemententityid = raalbase."SubframeworkAgreementEntityId")
  • Rows Removed by Join Filter: 10330000
92. 9,145.771 27,157.693 ↓ 5,000.0 10,000 1

Nested Loop Left Join (cost=29,221.44..34,361.28 rows=2 width=2,894) (actual time=5,182.736..27,157.693 rows=10,000 loops=1)

  • Join Filter: (allowedlocations.subframeworkagreemententityid = raalbase."SubframeworkAgreementEntityId")
  • Rows Removed by Join Filter: 63510000
93. 21.308 7,621.922 ↓ 5,000.0 10,000 1

Nested Loop Left Join (cost=29,221.44..34,350.28 rows=2 width=2,862) (actual time=5,146.693..7,621.922 rows=10,000 loops=1)

94. 115.977 7,542.052 ↓ 4,183.0 8,366 1

Nested Loop Left Join (cost=29,221.02..34,349.18 rows=2 width=2,091) (actual time=5,146.675..7,542.052 rows=8,366 loops=1)

  • Join Filter: (aftertransformmarketvalue.realestatefk = f3re."RealEstate1EntityId")
  • Rows Removed by Join Filter: 711094
95. 751.017 7,317.317 ↓ 4,183.0 8,366 1

Nested Loop Left Join (cost=29,221.02..34,349.13 rows=2 width=2,069) (actual time=5,145.988..7,317.317 rows=8,366 loops=1)

  • Join Filter: (actualmarketvalue.realestatefk = f3re."RealEstate1EntityId")
  • Rows Removed by Join Filter: 5354125
96. 10.077 5,721.334 ↓ 4,183.0 8,366 1

Nested Loop Left Join (cost=29,221.02..34,348.96 rows=2 width=2,043) (actual time=5,142.721..5,721.334 rows=8,366 loops=1)

97. 15.271 5,686.159 ↓ 4,183.0 8,366 1

Nested Loop Left Join (cost=29,220.74..34,348.22 rows=2 width=2,001) (actual time=5,142.711..5,686.159 rows=8,366 loops=1)

98. 14.231 5,637.424 ↓ 4,183.0 8,366 1

Nested Loop Left Join (cost=29,220.45..34,347.52 rows=2 width=1,594) (actual time=5,142.668..5,637.424 rows=8,366 loops=1)

99. 12.740 5,589.729 ↓ 4,183.0 8,366 1

Nested Loop Left Join (cost=29,220.16..34,346.82 rows=2 width=1,567) (actual time=5,142.656..5,589.729 rows=8,366 loops=1)

100. 27.486 5,543.525 ↓ 4,183.0 8,366 1

Nested Loop Left Join (cost=29,219.87..34,346.14 rows=2 width=1,522) (actual time=5,142.643..5,543.525 rows=8,366 loops=1)

101. 10.163 5,465.843 ↓ 4,183.0 8,366 1

Nested Loop Left Join (cost=29,219.58..34,345.17 rows=2 width=1,146) (actual time=5,142.623..5,465.843 rows=8,366 loops=1)

  • Join Filter: (credprov.pkey = pc.economicalownerfk)
  • Rows Removed by Join Filter: 1746
102. 12.216 5,455.680 ↓ 4,183.0 8,366 1

Nested Loop Left Join (cost=29,219.58..34,321.51 rows=2 width=1,134) (actual time=5,142.607..5,455.680 rows=8,366 loops=1)

  • Join Filter: (ppnc.codeid = pc.productnamecd)
  • Rows Removed by Join Filter: 45760
103. 10.718 5,435.098 ↓ 4,183.0 8,366 1

Nested Loop Left Join (cost=29,219.58..34,318.12 rows=2 width=1,112) (actual time=5,142.590..5,435.098 rows=8,366 loops=1)

  • Join Filter: (ppf.codeid = pc.productfamilycd)
  • Rows Removed by Join Filter: 3516
104. 12.242 5,424.380 ↓ 4,183.0 8,366 1

Nested Loop Left Join (cost=29,219.58..34,316.99 rows=2 width=898) (actual time=5,142.579..5,424.380 rows=8,366 loops=1)

105. 17.261 5,361.942 ↓ 4,183.0 8,366 1

Nested Loop Left Join (cost=29,219.29..34,316.28 rows=2 width=902) (actual time=5,142.557..5,361.942 rows=8,366 loops=1)

106. 10.546 5,294.485 ↓ 4,183.0 8,366 1

Nested Loop Left Join (cost=29,218.99..34,315.40 rows=2 width=874) (actual time=5,142.536..5,294.485 rows=8,366 loops=1)

  • Join Filter: (pcs.codeid = c.creditstatuscd)
  • Rows Removed by Join Filter: 2680
107. 6.176 5,283.939 ↓ 4,183.0 8,366 1

Nested Loop (cost=29,218.99..34,312.83 rows=2 width=859) (actual time=5,142.521..5,283.939 rows=8,366 loops=1)

  • Join Filter: (av.periodicitycd = pp.codeid)
  • Rows Removed by Join Filter: 6
108. 0.007 0.007 ↑ 1.0 1 1

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

  • Filter: (languageid = 4)
  • Rows Removed by Filter: 3
109. 14.530 5,277.756 ↓ 348.8 8,372 1

Hash Join (cost=29,218.99..34,311.33 rows=24 width=641) (actual time=5,142.510..5,277.756 rows=8,372 loops=1)

  • Hash Cond: (c.pkey = av.creditprecomputedfk)
110. 28.036 4,917.207 ↑ 11.9 8,372 1

Hash Join (cost=23,262.96..27,982.47 rows=99,358 width=393) (actual time=4,796.478..4,917.207 rows=8,372 loops=1)

  • Hash Cond: (c.creditdossierfk = cd.pkey)
111. 19.088 4,242.712 ↑ 11.9 8,372 1

Hash Join (cost=16,529.32..19,882.65 rows=99,358 width=242) (actual time=4,149.377..4,242.712 rows=8,372 loops=1)

  • Hash Cond: (lpb.loanpartentityid = c.pkey)
112. 445.571 445.571 ↑ 11.9 8,372 1

CTE Scan on _cte_loanpartbalance lpb (cost=0.00..1,987.16 rows=99,358 width=98) (actual time=370.653..445.571 rows=8,372 loops=1)

113. 85.957 3,778.053 ↑ 1.0 99,346 1

Hash (cost=15,287.35..15,287.35 rows=99,358 width=144) (actual time=3,778.053..3,778.053 rows=99,346 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 19689kB
114. 89.987 3,692.096 ↑ 1.0 99,346 1

Merge Left Join (cost=6,685.32..15,287.35 rows=99,358 width=144) (actual time=3,227.582..3,692.096 rows=99,346 loops=1)

  • Merge Cond: (c.pkey = isreasssent.loanpartentityid)
115. 43.711 2,613.259 ↑ 1.0 99,346 1

Merge Left Join (cost=5,014.06..13,362.18 rows=99,358 width=135) (actual time=2,248.449..2,613.259 rows=99,346 loops=1)

  • Merge Cond: (c.pkey = reassdate.loanpartentityid)
116. 100.745 2,153.949 ↑ 1.0 99,346 1

Merge Left Join (cost=3,342.80..11,437.00 rows=99,358 width=123) (actual time=1,834.807..2,153.949 rows=99,346 loops=1)

  • Merge Cond: (c.pkey = propdate.loanpartentityid)
117. 153.867 1,142.755 ↑ 1.0 99,346 1

Merge Left Join (cost=1,671.55..9,511.83 rows=99,358 width=111) (actual time=932.894..1,142.755 rows=99,346 loops=1)

  • Merge Cond: (c.pkey = appdate.loanpartentityid)
118. 46.649 46.649 ↑ 1.0 99,346 1

Index Scan using credit_pkey on credit c (cost=0.29..7,586.66 rows=99,358 width=99) (actual time=0.020..46.649 rows=99,346 loops=1)

119. 58.699 942.239 ↓ 270.0 99,346 1

Sort (cost=1,671.26..1,672.18 rows=368 width=16) (actual time=932.865..942.239 rows=99,346 loops=1)

  • Sort Key: appdate.loanpartentityid
  • Sort Method: quicksort Memory: 7729kB
120. 883.540 883.540 ↓ 270.0 99,346 1

CTE Scan on _cte_applicationdate appdate (cost=0.00..1,655.57 rows=368 width=16) (actual time=369.144..883.540 rows=99,346 loops=1)

  • Filter: (rn = 1)
121. 36.872 910.449 ↓ 251.4 92,503 1

Sort (cost=1,671.26..1,672.18 rows=368 width=16) (actual time=901.907..910.449 rows=92,503 loops=1)

  • Sort Key: propdate.loanpartentityid
  • Sort Method: quicksort Memory: 7409kB
122. 873.577 873.577 ↓ 251.4 92,503 1

CTE Scan on _cte_proposaldate propdate (cost=0.00..1,655.57 rows=368 width=16) (actual time=361.633..873.577 rows=92,503 loops=1)

  • Filter: (rn = 1)
123. 7.357 415.599 ↓ 53.9 19,836 1

Sort (cost=1,671.26..1,672.18 rows=368 width=16) (actual time=413.638..415.599 rows=19,836 loops=1)

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

CTE Scan on _cte_reassessmentdate reassdate (cost=0.00..1,655.57 rows=368 width=16) (actual time=308.663..408.242 rows=19,836 loops=1)

  • Filter: (rn = 1)
125. 37.284 988.850 ↓ 244.0 89,808 1

Sort (cost=1,671.26..1,672.18 rows=368 width=13) (actual time=979.128..988.850 rows=89,808 loops=1)

  • Sort Key: isreasssent.loanpartentityid
  • Sort Method: quicksort Memory: 7282kB
126. 951.566 951.566 ↓ 244.0 89,808 1

CTE Scan on _cte_isreassessmentsent isreasssent (cost=0.00..1,655.57 rows=368 width=13) (actual time=502.776..951.566 rows=89,808 loops=1)

  • Filter: (rn = 1)
127. 71.699 646.459 ↓ 1.0 92,434 1

Hash (cost=5,578.24..5,578.24 rows=92,432 width=155) (actual time=646.459..646.459 rows=92,434 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 8754kB
128. 84.191 574.760 ↓ 1.0 92,434 1

Hash Right Join (cost=3,584.72..5,578.24 rows=92,432 width=155) (actual time=153.730..574.760 rows=92,434 loops=1)

  • Hash Cond: (db.dossierentityid = cd.pkey)
129. 421.541 421.541 ↓ 1.0 92,384 1

CTE Scan on _cte_dossierbalance db (cost=0.00..1,762.22 rows=88,111 width=132) (actual time=83.731..421.541 rows=92,384 loops=1)

130. 32.839 69.028 ↓ 1.0 92,434 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 6754kB
131. 36.189 36.189 ↓ 1.0 92,434 1

Seq Scan on creditdossier cd (cost=0.00..2,429.32 rows=92,432 width=27) (actual time=0.140..36.189 rows=92,434 loops=1)

132. 119.300 346.019 ↓ 4,139.4 99,346 1

Hash (cost=5,955.73..5,955.73 rows=24 width=256) (actual time=346.019..346.019 rows=99,346 loops=1)

  • Buckets: 131072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 9452kB
133. 121.525 226.719 ↓ 4,139.4 99,346 1

Gather (cost=1,001.36..5,955.73 rows=24 width=256) (actual time=4.890..226.719 rows=99,346 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
134. 53.173 105.194 ↓ 3,311.5 33,115 3

Hash Join (cost=1.36..4,953.33 rows=10 width=256) (actual time=0.275..105.194 rows=33,115 loops=3)

  • Hash Cond: (av.amortizationshedulecd = pasc.codeid)
135. 51.999 51.999 ↓ 115.8 33,115 3

Parallel Seq Scan on amortizationversion av (cost=0.00..4,951.11 rows=286 width=42) (actual time=0.154..51.999 rows=33,115 loops=3)

  • Filter: ((isactive)::integer = 1)
  • Rows Removed by Filter: 12589
136. 0.006 0.022 ↓ 7.0 7 3

Hash (cost=1.35..1.35 rows=1 width=222) (actual time=0.022..0.022 rows=7 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
137. 0.016 0.016 ↓ 7.0 7 3

Seq Scan on par_amortizationshedulecaption pasc (cost=0.00..1.35 rows=1 width=222) (actual time=0.011..0.016 rows=7 loops=3)

  • Filter: (languageid = 4)
  • Rows Removed by Filter: 21
138. 0.000 0.000 ↑ 19.0 1 8,366

Materialize (cost=0.00..2.05 rows=19 width=19) (actual time=0.000..0.000 rows=1 loops=8,366)

139. 0.016 0.016 ↑ 2.4 8 1

Seq Scan on par_creditstatuscaption pcs (cost=0.00..1.95 rows=19 width=19) (actual time=0.009..0.016 rows=8 loops=1)

  • Filter: (languageid = 4)
  • Rows Removed by Filter: 24
140. 50.196 50.196 ↑ 1.0 1 8,366

Index Scan using productcopy_pkey on productcopy pc (cost=0.29..0.44 rows=1 width=32) (actual time=0.006..0.006 rows=1 loops=8,366)

  • Index Cond: (pkey = c.productcopyfk)
141. 50.196 50.196 ↑ 1.0 1 8,366

Index Only Scan using ix_t_specialprincipal_productpkey on t_specialprincipal sp (cost=0.29..0.34 rows=1 width=4) (actual time=0.005..0.006 rows=1 loops=8,366)

  • Index Cond: ("ProductPkey" = pc.pkey)
  • Heap Fetches: 8366
142. 0.000 0.000 ↑ 1.0 1 8,366

Materialize (cost=0.00..1.10 rows=1 width=222) (actual time=0.000..0.000 rows=1 loops=8,366)

143. 0.007 0.007 ↓ 2.0 2 1

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

  • Filter: (languageid = 4)
  • Rows Removed by Filter: 6
144. 8.336 8.366 ↑ 4.8 6 8,366

Materialize (cost=0.00..2.60 rows=29 width=26) (actual time=0.000..0.001 rows=6 loops=8,366)

145. 0.030 0.030 ↑ 1.0 28 1

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

  • Filter: (languageid = 4)
  • Rows Removed by Filter: 84
146. 0.000 0.000 ↑ 439.0 1 8,366

Materialize (cost=0.00..11.59 rows=439 width=24) (actual time=0.000..0.000 rows=1 loops=8,366)

147. 0.014 0.014 ↑ 48.8 9 1

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

148. 50.196 50.196 ↑ 1.0 1 8,366

Index Scan using ix_contractors_dossierentityid on t_contractors cont (cost=0.29..0.48 rows=1 width=380) (actual time=0.005..0.006 rows=1 loops=8,366)

  • Index Cond: ("DossierEntityId" = cd.pkey)
149. 33.464 33.464 ↓ 0.0 0 8,366

Index Scan using _ix_t_first4guaranteesperloanpart_loanpartentityid on t_first4guaranteesperloanpart f4g (cost=0.29..0.33 rows=1 width=49) (actual time=0.004..0.004 rows=0 loops=8,366)

  • Index Cond: ("LoanPartEntityId" = c.pkey)
150. 33.464 33.464 ↓ 0.0 0 8,366

Index Scan using _ix_t_firstwarranty_dossierentityid on t_firstwarranty fw (cost=0.29..0.34 rows=1 width=31) (actual time=0.004..0.004 rows=0 loops=8,366)

  • Index Cond: ("DossierEntityId" = cd.pkey)
151. 33.464 33.464 ↓ 0.0 0 8,366

Index Scan using _ix_t_first3realestatesperloanpart_loanpartentityid on t_first3realestatesperloanpart f3re (cost=0.29..0.34 rows=1 width=411) (actual time=0.004..0.004 rows=0 loops=8,366)

  • Index Cond: ("LoanPartEntityId" = c.pkey)
152. 25.098 25.098 ↓ 0.0 0 8,366

Index Scan using ix_cte_thirdpartywarrantycustom_realestateentityid on cte_thirdpartywarrantycustom thirdpartywarr (cost=0.29..0.36 rows=1 width=46) (actual time=0.002..0.003 rows=0 loops=8,366)

  • Index Cond: (realestateentityid = f3re."RealEstate1EntityId")
153. 844.966 844.966 ↓ 213.3 640 8,366

CTE Scan on _cte_lastactualmarketvalue actualmarketvalue (cost=0.00..0.06 rows=3 width=30) (actual time=0.000..0.101 rows=640 loops=8,366)

154. 108.758 108.758 ↓ 85.0 85 8,366

CTE Scan on lastaftertransformationmarketvalue aftertransformmarketvalue (cost=0.00..0.02 rows=1 width=30) (actual time=0.000..0.013 rows=85 loops=8,366)

155. 58.562 58.562 ↑ 1.0 1 8,366

Index Scan using ix_runningaccountandloansbaseview__loanpartentityid on runningaccountandloansbaseview_ raalbase (cost=0.42..0.54 rows=1 width=775) (actual time=0.006..0.007 rows=1 loops=8,366)

  • Index Cond: ("LoanPartEntityId" = c.pkey)
156. 10,390.000 10,390.000 ↓ 31.8 6,351 10,000

CTE Scan on _cte_allowedlocationsstringset allowedlocations (cost=0.00..4.00 rows=200 width=36) (actual time=0.002..1.039 rows=6,351 loops=10,000)

157. 1,620.000 1,620.000 ↓ 5.2 1,033 10,000

CTE Scan on allowedlocationsmodified (cost=0.00..4.00 rows=200 width=12) (actual time=0.001..0.162 rows=1,033 loops=10,000)

158. 120.000 120.000 ↑ 1.0 1 10,000

Index Scan using _ix_directdebit_creditfk on directdebit dide (cost=0.29..0.44 rows=1 width=38) (actual time=0.011..0.012 rows=1 loops=10,000)

  • Index Cond: (creditfk = c.pkey)
  • 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: 0
159. 60.000 60.000 ↑ 1.0 1 10,000

Index Scan using _ix_constructiondepot_creditfk on constructiondepot con (cost=0.29..0.40 rows=1 width=24) (actual time=0.005..0.006 rows=1 loops=10,000)

  • Index Cond: (creditfk = c.pkey)
160. 0.000 0.000 ↓ 2.0 2 10,000

Materialize (cost=0.00..1.20 rows=1 width=222) (actual time=0.000..0.000 rows=2 loops=10,000)

161. 0.011 0.011 ↓ 4.0 4 1

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

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

SubPlan (forNested Loop Left Join)

163. 50.000 160.000 ↑ 1.0 1 10,000

Aggregate (cost=0.42..0.43 rows=1 width=8) (actual time=0.016..0.016 rows=1 loops=10,000)

164. 110.000 110.000 ↑ 1.0 28 10,000

Values Scan on "*VALUES*_8" (cost=0.00..0.35 rows=28 width=8) (actual time=0.001..0.011 rows=28 loops=10,000)

Planning time : 72.895 ms
Execution time : 30,802.618 ms