explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jxa7

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

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

2.          

CTE _cte_loanpartbalance

3. 23.154 433.600 ↑ 11.9 8,372 1

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

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

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

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

6. 54.186 287.107 ↑ 1.0 99,346 1

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

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

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

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

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

9. 37.100 134.574 ↑ 1.0 99,346 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 6846kB
10. 97.474 97.474 ↑ 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.159..97.474 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. 266.249 358.775 ↓ 1.0 92,384 1

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

  • Group Key: lpb_1.dossierentityid
16. 63.633 92.526 ↑ 1.0 99,346 1

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

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

18.          

CTE _cte_lastactualmarketvalue

19. 0.152 2.903 ↓ 213.3 640 1

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

  • Filter: (lastactualmarketvalueprep.rn = 1)
20. 0.745 2.751 ↑ 1.0 640 1

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

21. 0.274 0.726 ↑ 1.0 640 1

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

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

Seq Scan on realestatevalue rev (cost=0.00..37.19 rows=640 width=30) (actual time=0.023..0.452 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.002..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.755 ↓ 85.0 85 1

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

  • Filter: (lastaftertransformationmarketvalueprep.rn = 1)
28. 0.117 0.735 ↑ 1.0 85 1

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

29. 0.044 0.448 ↑ 1.0 85 1

Sort (cost=39.91..40.12 rows=85 width=30) (actual time=0.440..0.448 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.404 0.404 ↑ 1.0 85 1

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

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

36. 61.538 379.100 ↓ 1.4 99,346 1

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

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

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

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

Seq Scan on freefieldvalue ffv (cost=0.00..14,361.13 rows=735,813 width=32) (actual time=0.138..112.393 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.008..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. 138.836 790.206 ↓ 1.3 92,503 1

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

46. 58.951 373.861 ↓ 1.3 92,503 1

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

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

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

  • Hash Cond: (ffv_1.freefieldconfigfk = ffc_1.pkey)
48. 112.881 112.881 ↓ 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.165..112.881 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.010..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.001..0.002 rows=4 loops=92,503)

54.          

CTE _cte_reassessmentdate

55. 22.681 376.387 ↑ 3.7 19,836 1

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

56. 13.348 294.198 ↑ 3.7 19,836 1

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

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

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

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

59. 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
60. 0.027 0.027 ↑ 1.0 9 1

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

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

SubPlan (forWindowAgg)

62. 39.672 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. 19.836 19.836 ↑ 1.0 4 19,836

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

64.          

CTE _cte_isreassessmentsent

65. 116.405 805.208 ↓ 1.2 89,808 1

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

66. 66.161 419.379 ↓ 1.2 89,808 1

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

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

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

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

69. 0.008 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.030 0.030 ↑ 1.0 9 1

Seq Scan on freefieldconfig ffc_3 (cost=0.00..3.12 rows=9 width=20) (actual time=0.011..0.030 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. 4.951 7.563 ↑ 1.4 1,306 1

Foreign Scan on dbo_allowedlocation al (cost=100.00..240.27 rows=1,861 width=12) (actual time=4.612..7.563 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. 1.060 9.230 ↓ 5.2 1,033 1

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

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

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

82. 135.247 10,995.795 ↓ 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,914.244..10,995.795 rows=10,000 loops=1)

  • Join Filter: (pcdt.codeid = con.constructiondepottypecd)
  • Rows Removed by Join Filter: 10505
83. 21.064 10,690.548 ↓ 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,914.168..10,690.548 rows=10,000 loops=1)

84. 16.642 10,619.484 ↓ 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,914.147..10,619.484 rows=10,000 loops=1)

85. 1,469.832 10,522.842 ↓ 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,914.120..10,522.842 rows=10,000 loops=1)

  • Join Filter: (allowedlocationsmodified.subframeworkagreemententityid = raalbase."SubframeworkAgreementEntityId")
  • Rows Removed by Join Filter: 10330000
86. 17.722 7,393.010 ↓ 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,904.293..7,393.010 rows=10,000 loops=1)

87. 35.846 7,375.288 ↓ 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,904.279..7,375.288 rows=10,000 loops=1)

88. 105.203 7,280.880 ↓ 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,904.258..7,280.880 rows=8,366 loops=1)

  • Join Filter: (aftertransformmarketvalue.realestatefk = f3re."RealEstate1EntityId")
  • Rows Removed by Join Filter: 711094
89. 761.411 7,050.187 ↓ 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,903.442..7,050.187 rows=8,366 loops=1)

  • Join Filter: (actualmarketvalue.realestatefk = f3re."RealEstate1EntityId")
  • Rows Removed by Join Filter: 5354125
90. 15.861 5,427.078 ↓ 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,900.095..5,427.078 rows=8,366 loops=1)

91. 23.066 5,394.485 ↓ 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,900.088..5,394.485 rows=8,366 loops=1)

92. 10.387 5,337.955 ↓ 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,900.076..5,337.955 rows=8,366 loops=1)

93. 10.887 5,294.104 ↓ 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,900.065..5,294.104 rows=8,366 loops=1)

94. 20.691 5,249.753 ↓ 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,900.051..5,249.753 rows=8,366 loops=1)

95. 9.480 5,187.232 ↓ 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,900.034..5,187.232 rows=8,366 loops=1)

  • Join Filter: (credprov.pkey = pc.economicalownerfk)
  • Rows Removed by Join Filter: 1746
96. 10.966 5,177.752 ↓ 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,900.020..5,177.752 rows=8,366 loops=1)

  • Join Filter: (ppnc.codeid = pc.productnamecd)
  • Rows Removed by Join Filter: 45760
97. 9.704 5,158.420 ↓ 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,900.000..5,158.420 rows=8,366 loops=1)

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

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

99. 14.114 5,094.930 ↓ 4,183.0 8,366 1

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

100. 9.609 5,030.620 ↓ 4,183.0 8,366 1

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

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

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

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

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

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

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

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

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

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

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

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

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

107. 111.104 3,673.949 ↑ 1.0 99,346 1

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

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

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

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

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

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

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

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

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

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

113. 40.203 953.471 ↓ 270.0 99,346 1

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

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

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

  • Filter: (rn = 1)
115. 37.686 892.591 ↓ 251.4 92,503 1

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

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

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

  • Filter: (rn = 1)
117. 7.221 396.747 ↓ 53.9 19,836 1

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

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

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

  • Filter: (rn = 1)
119. 45.104 912.848 ↓ 244.0 89,808 1

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

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

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

  • Filter: (rn = 1)
121. 50.572 619.390 ↓ 1.0 92,434 1

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

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

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

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

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

124. 32.303 67.077 ↓ 1.0 92,434 1

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

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

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

126. 64.385 229.744 ↓ 4,139.4 99,346 1

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

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

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

  • Workers Planned: 2
  • Workers Launched: 2
128. 48.009 76.721 ↓ 3,311.5 33,115 3

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

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

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

  • Filter: ((isactive)::integer = 1)
  • Rows Removed by Filter: 12589
130. 0.007 0.023 ↓ 7.0 7 3

Hash (cost=1.35..1.35 rows=1 width=222) (actual time=0.023..0.023 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.012..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.018 0.018 ↑ 2.4 8 1

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

  • Filter: (languageid = 4)
  • Rows Removed by Filter: 24
134. 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)
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.007..0.008 rows=2 loops=1)

  • Filter: (languageid = 4)
  • Rows Removed by Filter: 6
138. 8.335 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.031 0.031 ↑ 1.0 28 1

Seq Scan on par_productnamecaption ppnc (cost=0.00..2.45 rows=29 width=26) (actual time=0.008..0.031 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.015 0.015 ↑ 48.8 9 1

Seq Scan on creditprovider credprov (cost=0.00..9.39 rows=439 width=24) (actual time=0.006..0.015 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.005..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.004..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. 125.490 125.490 ↓ 85.0 85 8,366

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

149. 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)
150. 0.000 0.000 ↓ 0.0 0 10,000

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

  • Index Cond: (subframeworkagreemententityid = raalbase."SubframeworkAgreementEntityId")
151. 1,660.000 1,660.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.166 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.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.006..0.010 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 : 54.239 ms
Execution time : 11,028.886 ms