explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MUaO

Settings
# exclusive inclusive rows x rows loops node
1. 3.311 10,890.636 ↓ 5,000.0 10,000 1

Limit (cost=187,034.83..192,178.53 rows=2 width=3,025) (actual time=4,892.708..10,890.636 rows=10,000 loops=1)

2.          

CTE _cte_loanpartbalance

3. 24.342 422.906 ↑ 11.9 8,372 1

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

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

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

  • Group Key: pc_1.pkey
5. 19.530 19.530 ↓ 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.136..19.530 rows=99,353 loops=1)

6. 53.031 277.469 ↑ 1.0 99,346 1

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

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

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

  • Hash Cond: (cb.creditfk = c_1.pkey)
8. 17.576 17.576 ↑ 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.576 rows=99,347 loops=1)

9. 37.270 128.909 ↑ 1.0 99,346 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 6846kB
10. 91.639 91.639 ↑ 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.155..91.639 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. 252.433 380.035 ↓ 1.0 92,384 1

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

  • Group Key: lpb_1.dossierentityid
16. 80.595 127.602 ↑ 1.0 99,346 1

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

  • Sort Key: lpb_1.dossierentityid
  • Sort Method: quicksort Memory: 10834kB
17. 47.007 47.007 ↑ 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.142..47.007 rows=99,346 loops=1)

18.          

CTE _cte_lastactualmarketvalue

19. 0.138 2.761 ↓ 213.3 640 1

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

  • Filter: (lastactualmarketvalueprep.rn = 1)
20. 0.658 2.623 ↑ 1.0 640 1

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

21. 0.252 0.685 ↑ 1.0 640 1

Sort (cost=67.02..68.62 rows=640 width=30) (actual time=0.630..0.685 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.008..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.021 0.633 ↓ 85.0 85 1

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

  • Filter: (lastaftertransformationmarketvalueprep.rn = 1)
28. 0.102 0.612 ↑ 1.0 85 1

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

29. 0.049 0.340 ↑ 1.0 85 1

Sort (cost=39.91..40.12 rows=85 width=30) (actual time=0.333..0.340 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.291 0.291 ↑ 1.0 85 1

Seq Scan on realestatevalue rev_1 (cost=0.00..37.19 rows=85 width=30) (actual time=0.009..0.291 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. 115.283 810.242 ↓ 1.4 99,346 1

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

36. 63.782 396.921 ↓ 1.4 99,346 1

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

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

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

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

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

39. 0.009 0.038 ↑ 1.0 9 1

Hash (cost=3.12..3.12 rows=9 width=20) (actual time=0.038..0.038 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.002..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. 118.860 856.027 ↓ 1.3 92,503 1

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

46. 66.261 459.658 ↓ 1.3 92,503 1

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

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

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

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

49. 0.007 0.034 ↑ 1.0 9 1

Hash (cost=3.12..3.12 rows=9 width=20) (actual time=0.034..0.034 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.010..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. 28.275 405.669 ↑ 3.7 19,836 1

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

56. 14.105 317.886 ↑ 3.7 19,836 1

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

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

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

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

59. 0.009 0.038 ↑ 1.0 9 1

Hash (cost=3.12..3.12 rows=9 width=20) (actual time=0.038..0.038 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. 150.086 796.551 ↓ 1.2 89,808 1

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

66. 63.615 377.041 ↓ 1.2 89,808 1

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

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

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

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

69. 0.007 0.038 ↑ 1.0 9 1

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

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

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

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

SubPlan (forWindowAgg)

72. 89.808 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. 179.616 179.616 ↑ 1.0 4 89,808

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

74.          

CTE _cte_allowedlocationsmodifiedprep

75. 5.763 7.069 ↑ 1.4 1,306 1

Foreign Scan on dbo_allowedlocation al (cost=100.00..240.27 rows=1,861 width=12) (actual time=4.279..7.069 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. 1.010 8.658 ↓ 5.2 1,033 1

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

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

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

82. 144.741 10,887.325 ↓ 5,000.0 10,000 1

Nested Loop Left Join (cost=29,222.31..34,366.02 rows=2 width=3,025) (actual time=4,892.707..10,887.325 rows=10,000 loops=1)

  • Join Filter: (pcdt.codeid = con.constructiondepottypecd)
  • Rows Removed by Join Filter: 10505
83. 18.664 10,572.584 ↓ 5,000.0 10,000 1

Nested Loop Left Join (cost=29,222.31..34,363.63 rows=2 width=2,936) (actual time=4,892.628..10,572.584 rows=10,000 loops=1)

84. 15.350 10,503.920 ↓ 5,000.0 10,000 1

Nested Loop Left Join (cost=29,222.02..34,362.80 rows=2 width=2,916) (actual time=4,892.608..10,503.920 rows=10,000 loops=1)

85. 1,426.500 10,408.570 ↓ 5,000.0 10,000 1

Nested Loop Left Join (cost=29,221.72..34,361.91 rows=2 width=2,882) (actual time=4,892.581..10,408.570 rows=10,000 loops=1)

  • Join Filter: (allowedlocationsmodified.subframeworkagreemententityid = raalbase."SubframeworkAgreementEntityId")
  • Rows Removed by Join Filter: 10330000
86. 11.547 7,342.070 ↓ 5,000.0 10,000 1

Nested Loop Left Join (cost=29,221.72..34,350.91 rows=2 width=2,878) (actual time=4,883.367..7,342.070 rows=10,000 loops=1)

87. 24.122 7,320.523 ↓ 5,000.0 10,000 1

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

88. 109.249 7,246.205 ↓ 4,183.0 8,366 1

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

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

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

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

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

91. 11.268 5,355.869 ↓ 4,183.0 8,366 1

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

92. 10.166 5,311.137 ↓ 4,183.0 8,366 1

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

93. 8.345 5,267.507 ↓ 4,183.0 8,366 1

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

94. 16.917 5,225.698 ↓ 4,183.0 8,366 1

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

95. 9.297 5,166.951 ↓ 4,183.0 8,366 1

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

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

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

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

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

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

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

99. 20.372 5,072.665 ↓ 4,183.0 8,366 1

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

100. 10.269 5,010.463 ↓ 4,183.0 8,366 1

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

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

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

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

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

  • Filter: (languageid = 4)
  • Rows Removed by Filter: 3
103. 15.650 4,994.511 ↓ 348.8 8,372 1

Hash Join (cost=29,218.99..34,311.33 rows=24 width=641) (actual time=4,879.291..4,994.511 rows=8,372 loops=1)

  • Hash Cond: (c.pkey = av.creditprecomputedfk)
104. 14.710 4,743.499 ↑ 11.9 8,372 1

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

  • Hash Cond: (c.creditdossierfk = cd.pkey)
105. 16.898 4,092.922 ↑ 11.9 8,372 1

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

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

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

107. 84.806 3,643.178 ↑ 1.0 99,346 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 19689kB
108. 67.267 3,558.372 ↑ 1.0 99,346 1

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

  • Merge Cond: (c.pkey = isreasssent.loanpartentityid)
109. 42.377 2,587.441 ↑ 1.0 99,346 1

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

  • Merge Cond: (c.pkey = reassdate.loanpartentityid)
110. 62.032 2,117.041 ↑ 1.0 99,346 1

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

  • Merge Cond: (c.pkey = propdate.loanpartentityid)
111. 151.504 1,100.558 ↑ 1.0 99,346 1

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

  • Merge Cond: (c.pkey = appdate.loanpartentityid)
112. 36.963 36.963 ↑ 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.014..36.963 rows=99,346 loops=1)

113. 37.511 912.091 ↓ 270.0 99,346 1

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

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

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

  • Filter: (rn = 1)
115. 35.210 954.451 ↓ 251.4 92,503 1

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

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

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

  • Filter: (rn = 1)
117. 8.344 428.023 ↓ 53.9 19,836 1

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

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

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

  • Filter: (rn = 1)
119. 36.604 903.664 ↓ 244.0 89,808 1

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

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

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

  • Filter: (rn = 1)
121. 48.667 635.867 ↓ 1.0 92,434 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 8754kB
122. 68.808 587.200 ↓ 1.0 92,434 1

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

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

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

124. 33.338 75.011 ↓ 1.0 92,434 1

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

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

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

126. 60.661 235.362 ↓ 4,139.4 99,346 1

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

  • Buckets: 131072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 9452kB
127. 111.893 174.701 ↓ 4,139.4 99,346 1

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

  • Workers Planned: 2
  • Workers Launched: 2
128. 38.267 62.808 ↓ 3,311.5 33,115 3

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

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

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

  • Filter: ((isactive)::integer = 1)
  • Rows Removed by Filter: 12589
130. 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
131. 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
132. 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)

133. 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.010..0.016 rows=8 loops=1)

  • Filter: (languageid = 4)
  • Rows Removed by Filter: 24
134. 41.830 41.830 ↑ 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.005..0.005 rows=1 loops=8,366)

  • Index Cond: (pkey = c.productcopyfk)
135. 41.830 41.830 ↑ 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.005 rows=1 loops=8,366)

  • Index Cond: ("ProductPkey" = pc.pkey)
  • Heap Fetches: 8366
136. 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)

137. 0.008 0.008 ↓ 2.0 2 1

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

  • Filter: (languageid = 4)
  • Rows Removed by Filter: 6
138. 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)

139. 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.007..0.030 rows=28 loops=1)

  • Filter: (languageid = 4)
  • Rows Removed by Filter: 84
140. 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)

141. 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)

142. 41.830 41.830 ↑ 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.004..0.005 rows=1 loops=8,366)

  • Index Cond: ("DossierEntityId" = cd.pkey)
143. 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.003..0.004 rows=0 loops=8,366)

  • Index Cond: ("LoanPartEntityId" = c.pkey)
144. 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)
145. 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)
146. 16.732 16.732 ↓ 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.002 rows=0 loops=8,366)

  • Index Cond: (realestateentityid = f3re."RealEstate1EntityId")
147. 861.698 861.698 ↓ 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.103 rows=640 loops=8,366)

148. 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)

149. 50.196 50.196 ↑ 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.006 rows=1 loops=8,366)

  • Index Cond: ("LoanPartEntityId" = c.pkey)
150. 10.000 10.000 ↓ 0.0 0 10,000

Index Scan using ix_cte_allowedlocationsstringset_loanpartentityid on cte_allowedlocationsstringset allowedlocations (cost=0.28..0.30 rows=1 width=20) (actual time=0.001..0.001 rows=0 loops=10,000)

  • Index Cond: (subframeworkagreemententityid = raalbase."SubframeworkAgreementEntityId")
151. 1,640.000 1,640.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.164 rows=1,033 loops=10,000)

152. 80.000 80.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.007..0.008 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
153. 50.000 50.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.004..0.005 rows=1 loops=10,000)

  • Index Cond: (creditfk = c.pkey)
154. 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)

155. 0.009 0.009 ↓ 4.0 4 1

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

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

SubPlan (forNested Loop Left Join)

157. 50.000 170.000 ↑ 1.0 1 10,000

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

158. 120.000 120.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.012 rows=28 loops=10,000)

Planning time : 56.422 ms
Execution time : 10,919.653 ms