explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CXM0

Settings
# exclusive inclusive rows x rows loops node
1. 0.067 13,118.926 ↑ 1.0 1 1

Limit (cost=147,679.91..216,308.48 rows=1 width=11,337) (actual time=13,118.860..13,118.926 rows=1 loops=1)

2.          

CTE lastactualmarketvalue

3. 0.151 2.864 ↓ 213.3 640 1

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

  • Filter: (lastactualmarketvalueprep.rn = 1)
4. 0.732 2.713 ↑ 1.0 640 1

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

5. 0.256 0.701 ↑ 1.0 640 1

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

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

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

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

SubPlan (forWindowAgg)

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

9. 0.640 0.640 ↑ 1.0 2 640

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

10.          

CTE lastaftertransformationmarketvalue

11. 0.022 0.657 ↓ 85.0 85 1

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

  • Filter: (lastaftertransformationmarketvalueprep.rn = 1)
12. 0.116 0.635 ↑ 1.0 85 1

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

13. 0.047 0.349 ↑ 1.0 85 1

Sort (cost=39.91..40.12 rows=85 width=30) (actual time=0.341..0.349 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
14. 0.302 0.302 ↑ 1.0 85 1

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

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

SubPlan (forWindowAgg)

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

17. 0.085 0.085 ↑ 1.0 2 85

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

18.          

CTE allowedlocationsmodifiedprep

19. 4.978 7.590 ↑ 1.4 1,306 1

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

20.          

SubPlan (forForeign Scan)

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

22. 1.306 1.306 ↑ 1.0 2 1,306

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

23.          

CTE allowedlocationsmodified

24. 0.967 9.169 ↓ 5.2 1,033 1

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

  • Group Key: allowedlocationsmodifiedprep.subframeworkagreemententityid
25. 8.202 8.202 ↑ 1.4 1,306 1

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

26. 0.011 13,118.859 ↑ 2.0 1 1

Nested Loop Left Join (cost=147,228.00..284,485.13 rows=2 width=11,337) (actual time=13,118.859..13,118.859 rows=1 loops=1)

  • Join Filter: (pcdt.codeid = con.constructiondepottypecd)
  • Rows Removed by Join Filter: 4
27. 0.013 13,118.837 ↑ 2.0 1 1

Nested Loop Left Join (cost=147,228.00..284,483.90 rows=2 width=11,111) (actual time=13,118.837..13,118.837 rows=1 loops=1)

28. 0.009 13,118.809 ↑ 2.0 1 1

Nested Loop Left Join (cost=147,227.70..284,483.07 rows=2 width=10,851) (actual time=13,118.809..13,118.809 rows=1 loops=1)

  • Join Filter: (dide.creditfk = c.pkey)
29. 0.150 13,118.627 ↑ 2.0 1 1

Nested Loop Left Join (cost=147,227.70..277,464.56 rows=2 width=10,204) (actual time=13,118.627..13,118.627 rows=1 loops=1)

  • Join Filter: (allowedlocationsmodified.subframeworkagreemententityid = raalbase."SubframeworkAgreementEntityId")
  • Rows Removed by Join Filter: 1033
30. 0.864 13,108.853 ↑ 2.0 1 1

Nested Loop Left Join (cost=147,227.70..277,453.56 rows=2 width=10,192) (actual time=13,108.853..13,108.853 rows=1 loops=1)

  • Join Filter: (allowedlocations.subframeworkagreemententityid = raalbase."SubframeworkAgreementEntityId")
  • Rows Removed by Join Filter: 6351
31. 0.009 13,105.784 ↑ 2.0 1 1

Nested Loop Left Join (cost=147,227.70..277,152.64 rows=2 width=10,172) (actual time=13,105.784..13,105.784 rows=1 loops=1)

32. 0.021 13,105.755 ↑ 2.0 1 1

Nested Loop Left Join (cost=147,227.29..277,151.54 rows=2 width=9,369) (actual time=13,105.755..13,105.755 rows=1 loops=1)

  • Join Filter: (aftertransformmarketvalue.realestatefk = reaf1.realestateentityid)
  • Rows Removed by Join Filter: 85
33. 0.125 13,105.033 ↑ 2.0 1 1

Nested Loop Left Join (cost=147,227.29..277,151.49 rows=2 width=9,331) (actual time=13,105.033..13,105.033 rows=1 loops=1)

  • Join Filter: (actualmarketvalue.realestatefk = reaf1.realestateentityid)
  • Rows Removed by Join Filter: 640
34. 2.352 13,101.709 ↑ 2.0 1 1

Nested Loop Left Join (cost=147,227.29..277,151.32 rows=2 width=9,293) (actual time=13,101.709..13,101.709 rows=1 loops=1)

  • Join Filter: (thirdpartywarr.realestateentityid = reaf1.realestateentityid)
  • Rows Removed by Join Filter: 19530
35. 0.007 13,088.438 ↑ 2.0 1 1

Nested Loop Left Join (cost=147,227.29..275,285.75 rows=2 width=9,214) (actual time=13,088.438..13,088.438 rows=1 loops=1)

  • Join Filter: (c_1.pkey = c.pkey)
36. 0.008 11,250.110 ↑ 2.0 1 1

Nested Loop Left Join (cost=92,255.34..219,189.14 rows=2 width=8,477) (actual time=11,250.109..11,250.110 rows=1 loops=1)

  • Join Filter: (firstwarrantyprep."DossierEntityId" = cd.pkey)
37. 0.009 10,002.009 ↑ 2.0 1 1

Nested Loop Left Join (cost=56,847.44..182,980.42 rows=2 width=7,339) (actual time=10,002.009..10,002.009 rows=1 loops=1)

  • Join Filter: (rn1.loanpartentityid = c.pkey)
38. 0.007 9,438.145 ↑ 2.0 1 1

Nested Loop Left Join (cost=29,354.79..154,712.71 rows=2 width=6,043) (actual time=9,438.145..9,438.145 rows=1 loops=1)

  • Join Filter: (grouped.dossierentityid = cd.pkey)
39. 0.002 0.419 ↑ 2.0 1 1

Nested Loop Left Join (cost=34.58..125,390.72 rows=2 width=4,635) (actual time=0.419..0.419 rows=1 loops=1)

  • Join Filter: (pcpn.codeid = credprov.namecd)
40. 0.003 0.406 ↑ 2.0 1 1

Nested Loop Left Join (cost=34.58..125,341.68 rows=2 width=4,616) (actual time=0.405..0.406 rows=1 loops=1)

  • Join Filter: (credprov.pkey = pc.economicalownerfk)
41. 0.004 0.391 ↑ 2.0 1 1

Nested Loop Left Join (cost=34.58..125,318.02 rows=2 width=4,284) (actual time=0.390..0.391 rows=1 loops=1)

  • Join Filter: (ppnc.codeid = pc.productnamecd)
  • Rows Removed by Join Filter: 15
42. 0.003 0.365 ↑ 2.0 1 1

Nested Loop Left Join (cost=34.58..125,314.63 rows=2 width=4,254) (actual time=0.364..0.365 rows=1 loops=1)

  • Join Filter: (ppf.codeid = pc.productfamilycd)
  • Rows Removed by Join Filter: 1
43. 0.003 0.352 ↑ 2.0 1 1

Nested Loop Left Join (cost=34.58..125,313.50 rows=2 width=4,028) (actual time=0.352..0.352 rows=1 loops=1)

  • Join Filter: (pc_1.pkey = pc.pkey)
44. 0.003 0.331 ↑ 2.0 1 1

Nested Loop Left Join (cost=34.29..112,371.14 rows=2 width=3,988) (actual time=0.331..0.331 rows=1 loops=1)

45. 0.003 0.319 ↑ 2.0 1 1

Nested Loop Left Join (cost=34.00..112,370.26 rows=2 width=3,636) (actual time=0.318..0.319 rows=1 loops=1)

  • Join Filter: (pcs.codeid = c.creditstatuscd)
46. 0.002 0.303 ↑ 2.0 1 1

Nested Loop (cost=34.00..112,367.70 rows=2 width=3,613) (actual time=0.303..0.303 rows=1 loops=1)

  • Join Filter: (av.creditprecomputedfk = c.pkey)
47. 0.002 0.115 ↑ 99,346.0 1 1

Nested Loop Left Join (cost=34.00..103,224.83 rows=99,346 width=2,388) (actual time=0.115..0.115 rows=1 loops=1)

48. 0.004 0.105 ↑ 99,346.0 1 1

Nested Loop (cost=33.70..67,616.42 rows=99,346 width=2,364) (actual time=0.105..0.105 rows=1 loops=1)

49. 0.002 0.091 ↑ 99,346.0 1 1

Merge Left Join (cost=33.41..29,783.28 rows=99,346 width=2,270) (actual time=0.091..0.091 rows=1 loops=1)

  • Merge Cond: (c.pkey = isreasssent.loanpartentityid)
50. 0.002 0.077 ↑ 99,346.0 1 1

Merge Left Join (cost=32.80..25,409.74 rows=99,346 width=2,249) (actual time=0.077..0.077 rows=1 loops=1)

  • Merge Cond: (c.pkey = reassdate.loanpartentityid)
51. 0.001 0.063 ↑ 99,346.0 1 1

Merge Left Join (cost=32.51..24,238.78 rows=99,346 width=2,225) (actual time=0.063..0.063 rows=1 loops=1)

  • Merge Cond: (c.pkey = propdate.loanpartentityid)
52. 0.003 0.050 ↑ 99,346.0 1 1

Merge Left Join (cost=32.22..19,742.82 rows=99,346 width=2,201) (actual time=0.050..0.050 rows=1 loops=1)

  • Merge Cond: (c.pkey = appdate.loanpartentityid)
53. 0.005 0.035 ↑ 99,346.0 1 1

Merge Join (cost=31.56..14,932.72 rows=99,346 width=2,177) (actual time=0.035..0.035 rows=1 loops=1)

  • Merge Cond: (c.pkey = lpb.loanpartentityid)
54. 0.018 0.018 ↑ 99,403.0 1 1

Index Scan using credit_pkey on credit c (cost=0.29..7,587.34 rows=99,403 width=2,141) (actual time=0.018..0.018 rows=1 loops=1)

55. 0.012 0.012 ↑ 99,346.0 1 1

Index Scan using ix_cte_loanpartbalance_loanpartentityid on cte_loanpartbalance lpb (cost=0.29..5,855.54 rows=99,346 width=36) (actual time=0.012..0.012 rows=1 loops=1)

56. 0.012 0.012 ↑ 99,346.0 1 1

Index Scan using ix_cte_applicationdate_loanpartentityid on cte_applicationdate appdate (cost=0.29..3,320.48 rows=99,346 width=24) (actual time=0.012..0.012 rows=1 loops=1)

57. 0.012 0.012 ↑ 92,503.0 1 1

Index Scan using ix_cte_proposaldate_loanpartentityid on cte_proposaldate propdate (cost=0.29..3,091.84 rows=92,503 width=24) (actual time=0.012..0.012 rows=1 loops=1)

58. 0.012 0.012 ↑ 19,836.0 1 1

Index Scan using ix_cte_reassessmentdate_loanpartentityid on cte_reassessmentdate reassdate (cost=0.29..674.83 rows=19,836 width=24) (actual time=0.011..0.012 rows=1 loops=1)

59. 0.012 0.012 ↑ 89,808.0 1 1

Index Scan using ix_cte_isreassessmentsent_loanpartentityid on cte_isreassessmentsent isreasssent (cost=0.29..3,003.41 rows=89,808 width=21) (actual time=0.012..0.012 rows=1 loops=1)

60. 0.010 0.010 ↑ 1.0 1 1

Index Scan using creditdossier_pkey on creditdossier cd (cost=0.29..0.38 rows=1 width=94) (actual time=0.010..0.010 rows=1 loops=1)

  • Index Cond: (pkey = c.creditdossierfk)
61. 0.008 0.008 ↑ 1.0 1 1

Index Scan using ix_cte_dossierbalance_dossierentityid on cte_dossierbalance db (cost=0.29..0.35 rows=1 width=24) (actual time=0.008..0.008 rows=1 loops=1)

  • Index Cond: (dossierentityid = cd.pkey)
62. 0.010 0.186 ↑ 2.0 1 1

Materialize (cost=0.00..6,162.49 rows=2 width=1,225) (actual time=0.186..0.186 rows=1 loops=1)

63. 0.002 0.176 ↑ 2.0 1 1

Nested Loop (cost=0.00..6,162.48 rows=2 width=1,225) (actual time=0.176..0.176 rows=1 loops=1)

  • Join Filter: (av.periodicitycd = pp.codeid)
64. 0.009 0.009 ↑ 1.0 1 1

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

  • Filter: (languageid = 4)
  • Rows Removed by Filter: 3
65. 0.002 0.165 ↑ 24.0 1 1

Nested Loop (cost=0.00..6,160.98 rows=24 width=999) (actual time=0.165..0.165 rows=1 loops=1)

  • Join Filter: (av.amortizationshedulecd = pasc.codeid)
66. 0.005 0.005 ↑ 1.0 1 1

Seq Scan on par_amortizationshedulecaption pasc (cost=0.00..1.35 rows=1 width=226) (actual time=0.005..0.005 rows=1 loops=1)

  • Filter: (languageid = 4)
  • Rows Removed by Filter: 3
67. 0.158 0.158 ↑ 686.0 1 1

Seq Scan on amortizationversion av (cost=0.00..6,151.06 rows=686 width=773) (actual time=0.158..0.158 rows=1 loops=1)

  • Filter: ((isactive)::integer = 1)
68. 0.004 0.013 ↑ 19.0 1 1

Materialize (cost=0.00..2.05 rows=19 width=23) (actual time=0.013..0.013 rows=1 loops=1)

69. 0.009 0.009 ↑ 19.0 1 1

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

  • Filter: (languageid = 4)
  • Rows Removed by Filter: 3
70. 0.009 0.009 ↑ 1.0 1 1

Index Scan using productcopy_pkey on productcopy pc (cost=0.29..0.44 rows=1 width=352) (actual time=0.009..0.009 rows=1 loops=1)

  • Index Cond: (pkey = c.productcopyfk)
71. 0.004 0.018 ↑ 99,350.0 1 1

Materialize (cost=0.29..9,433.23 rows=99,350 width=40) (actual time=0.018..0.018 rows=1 loops=1)

72. 0.004 0.014 ↑ 99,350.0 1 1

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

  • Group Key: pc_1.pkey
73. 0.010 0.010 ↑ 99,350.0 1 1

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

74. 0.006 0.010 ↓ 2.0 2 1

Materialize (cost=0.00..1.10 rows=1 width=226) (actual time=0.009..0.010 rows=2 loops=1)

75. 0.004 0.004 ↓ 2.0 2 1

Seq Scan on par_productfamilycaption ppf (cost=0.00..1.10 rows=1 width=226) (actual time=0.003..0.004 rows=2 loops=1)

  • Filter: (languageid = 4)
  • Rows Removed by Filter: 6
76. 0.006 0.022 ↑ 1.8 16 1

Materialize (cost=0.00..2.60 rows=29 width=30) (actual time=0.011..0.022 rows=16 loops=1)

77. 0.016 0.016 ↑ 1.8 16 1

Seq Scan on par_productnamecaption ppnc (cost=0.00..2.45 rows=29 width=30) (actual time=0.007..0.016 rows=16 loops=1)

  • Filter: (languageid = 4)
  • Rows Removed by Filter: 48
78. 0.003 0.012 ↑ 439.0 1 1

Materialize (cost=0.00..11.59 rows=439 width=332) (actual time=0.012..0.012 rows=1 loops=1)

79. 0.009 0.009 ↑ 439.0 1 1

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

80. 0.003 0.011 ↑ 449.0 1 1

Materialize (cost=0.00..36.70 rows=449 width=19) (actual time=0.011..0.011 rows=1 loops=1)

81. 0.008 0.008 ↑ 449.0 1 1

Seq Scan on par_creditprovidernamecaption pcpn (cost=0.00..34.45 rows=449 width=19) (actual time=0.007..0.008 rows=1 loops=1)

  • Filter: (languageid = 4)
  • Rows Removed by Filter: 3
82. 0.006 9,437.719 ↑ 4.0 1 1

Materialize (cost=29,320.20..29,321.87 rows=4 width=1,408) (actual time=9,437.719..9,437.719 rows=1 loops=1)

83. 9,437.706 9,437.713 ↑ 4.0 1 1

CTE Scan on grouped (cost=29,320.20..29,321.81 rows=4 width=1,408) (actual time=9,437.713..9,437.713 rows=1 loops=1)

84.          

CTE contractors

85. 397.472 5,727.517 ↓ 199.9 156,940 1

WindowAgg (cost=21,605.05..21,734.58 rows=785 width=1,154) (actual time=4,459.400..5,727.517 rows=156,940 loops=1)

86. 414.698 4,545.345 ↓ 199.9 156,940 1

Sort (cost=21,605.05..21,607.01 rows=785 width=664) (actual time=4,459.365..4,545.345 rows=156,940 loops=1)

  • Sort Key: cd_1.pkey, r.roletypecd, r.pkey
  • Sort Method: external merge Disk: 28296kB
87. 107.726 4,130.647 ↓ 199.9 156,940 1

Nested Loop Left Join (cost=9,676.12..21,567.31 rows=785 width=664) (actual time=1,046.199..4,130.647 rows=156,940 loops=1)

88. 221.868 3,395.161 ↓ 199.9 156,940 1

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

89. 89.338 2,545.533 ↓ 199.9 156,940 1

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

  • Hash Cond: (p.partytypecd = ppt.codeid)
90. 92.058 2,456.184 ↓ 199.9 156,940 1

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

  • Hash Cond: (pd.gendercd = par_gendercaption.codeid)
91. 149.591 2,364.116 ↓ 199.9 156,940 1

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

92. 503.273 1,586.765 ↓ 199.9 156,940 1

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

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

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

94. 171.224 1,043.040 ↓ 199.9 156,940 1

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

  • Buckets: 32768 (originally 1024) Batches: 8 (originally 1) Memory Usage: 3841kB
95. 172.354 871.816 ↓ 199.9 156,940 1

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

96. 71.702 71.702 ↓ 199.9 156,940 1

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

  • Filter: ((roletypecd = ANY ('{1,2}'::integer[])) AND ((activerole)::integer = 1))
  • Rows Removed by Filter: 68
97. 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)
98. 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)
99. 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
100. 0.007 0.007 ↓ 3.0 3 1

Seq Scan on par_gendercaption (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
101. 0.002 0.011 ↓ 2.0 2 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
102. 0.009 0.009 ↓ 2.0 2 1

Seq Scan on par_partytypecaption ppt (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
103. 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)
104. 627.760 627.760 ↑ 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.004..0.004 rows=1 loops=156,940)

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

SubPlan (forWindowAgg)

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

107. 470.820 470.820 ↑ 1.0 8 156,940

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

108.          

CTE grouped

109. 0.030 9,437.666 ↑ 4.0 1 1

GroupAggregate (cost=7,584.64..7,585.63 rows=4 width=1,140) (actual time=9,437.666..9,437.666 rows=1 loops=1)

  • Group Key: con_1.dossierentityid
110. 241.971 9,437.636 ↑ 2.0 2 1

Sort (cost=7,584.64..7,584.65 rows=4 width=4,040) (actual time=9,437.635..9,437.636 rows=2 loops=1)

  • Sort Key: con_1.dossierentityid
  • Sort Method: external merge Disk: 24888kB
111. 356.126 9,195.665 ↓ 37,204.8 148,819 1

Hash Right Join (cost=7,254.70..7,584.60 rows=4 width=4,040) (actual time=6,646.961..9,195.665 rows=148,819 loops=1)

  • Hash Cond: (conadd.partydetailfk = con_1.pdpkey)
112. 45.731 2,649.130 ↓ 31,958.6 159,793 1

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

  • Filter: (conadd.rn = 1)
  • Rows Removed by Filter: 28937
113. 2,073.648 2,603.399 ↓ 185.9 188,730 1

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

114. 386.619 529.751 ↓ 185.9 188,730 1

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

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

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

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

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

  • Buckets: 32768 (originally 1024) Batches: 8 (originally 1) Memory Usage: 3841kB
117. 6,034.773 6,034.773 ↓ 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,459.405..6,034.773 rows=148,330 loops=1)

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

SubPlan (forCTE Scan)

119. 0.004 0.007 ↑ 1.0 1 1

Aggregate (cost=0.05..0.06 rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=1)

120. 0.003 0.003 ↑ 1.0 3 1

Values Scan on "*VALUES*_4" (cost=0.00..0.04 rows=3 width=8) (actual time=0.002..0.003 rows=3 loops=1)

121. 0.004 563.855 ↑ 165.0 1 1

Materialize (cost=27,492.65..28,263.18 rows=165 width=1,296) (actual time=563.855..563.855 rows=1 loops=1)

122. 0.032 563.851 ↑ 165.0 1 1

Hash Left Join (cost=27,492.65..28,260.70 rows=165 width=1,296) (actual time=563.851..563.851 rows=1 loops=1)

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

CTE guaranteesprep

124. 93.497 502.725 ↑ 1.1 30,001 1

WindowAgg (cost=20,979.63..25,262.61 rows=32,946 width=324) (actual time=309.609..502.725 rows=30,001 loops=1)

125. 49.937 319.225 ↑ 1.1 30,001 1

Sort (cost=20,979.63..21,061.99 rows=32,946 width=344) (actual time=309.579..319.225 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
126. 13.380 269.288 ↑ 1.1 30,001 1

Hash Left Join (cost=4,652.66..13,327.39 rows=32,946 width=344) (actual time=107.880..269.288 rows=30,001 loops=1)

  • Hash Cond: (coll.guaranteetypecd = gt.codeid)
127. 57.190 255.891 ↑ 1.1 30,001 1

Hash Join (cost=4,651.35..13,149.85 rows=32,946 width=122) (actual time=107.844..255.891 rows=30,001 loops=1)

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

Seq Scan on credit c_3 (cost=0.00..5,991.03 rows=99,403 width=24) (actual time=0.169..91.152 rows=99,346 loops=1)

129. 20.324 107.549 ↑ 1.1 30,001 1

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

  • Buckets: 32768 Batches: 2 Memory Usage: 2414kB
130. 33.516 87.225 ↑ 1.1 30,001 1

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

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

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

132. 18.325 42.576 ↓ 1.0 29,602 1

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

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

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

  • Filter: ((classname)::text = 'Guarantee'::text)
  • Rows Removed by Filter: 31325
134. 0.006 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
135. 0.011 0.011 ↓ 6.0 6 1

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

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

SubPlan (forWindowAgg)

137. 30.001 90.003 ↑ 1.0 1 30,001

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

138. 60.002 60.002 ↑ 1.0 6 30,001

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

139. 0.028 553.214 ↑ 165.0 1 1

Hash Left Join (cost=1,486.69..2,238.12 rows=165 width=1,554) (actual time=553.214..553.214 rows=1 loops=1)

  • Hash Cond: (rn1.entityid = rn3.entityid)
140. 0.017 542.686 ↑ 165.0 1 1

Hash Left Join (cost=743.35..1,489.70 rows=165 width=1,040) (actual time=542.686..542.686 rows=1 loops=1)

  • Hash Cond: (rn1.entityid = rn2.entityid)
141. 309.615 309.615 ↑ 165.0 1 1

CTE Scan on guaranteesprep rn1 (cost=0.00..741.28 rows=165 width=526) (actual time=309.614..309.615 rows=1 loops=1)

  • Filter: (rn = 1)
142. 0.012 233.054 ↑ 55.0 3 1

Hash (cost=741.28..741.28 rows=165 width=518) (actual time=233.054..233.054 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
143. 233.042 233.042 ↑ 55.0 3 1

CTE Scan on guaranteesprep rn2 (cost=0.00..741.28 rows=165 width=518) (actual time=107.410..233.042 rows=3 loops=1)

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

Hash (cost=741.28..741.28 rows=165 width=518) (actual time=10.500..10.500 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
145. 10.499 10.499 ↓ 0.0 0 1

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

  • Filter: (rn = 3)
  • Rows Removed by Filter: 30001
146. 0.000 10.593 ↓ 0.0 0 1

Hash (cost=741.28..741.28 rows=165 width=518) (actual time=10.593..10.593 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
147. 10.593 10.593 ↓ 0.0 0 1

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

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

SubPlan (forHash Left Join)

149. 0.003 0.012 ↑ 1.0 1 1

Aggregate (cost=0.06..0.07 rows=1 width=8) (actual time=0.012..0.012 rows=1 loops=1)

150. 0.009 0.009 ↑ 1.0 4 1

Values Scan on "*VALUES*_6" (cost=0.00..0.05 rows=4 width=8) (actual time=0.007..0.009 rows=4 loops=1)

151. 0.002 1,248.093 ↑ 176.0 1 1

Materialize (cost=35,407.90..36,203.87 rows=176 width=1,138) (actual time=1,248.093..1,248.093 rows=1 loops=1)

152. 1,248.091 1,248.091 ↑ 176.0 1 1

CTE Scan on firstwarrantyprep (cost=35,407.90..36,201.23 rows=176 width=1,138) (actual time=1,248.091..1,248.091 rows=1 loops=1)

  • Filter: ("RN" = 1)
153.          

CTE firstwarrantyprep

154. 0.018 1,248.087 ↑ 35,259.0 1 1

WindowAgg (cost=31,176.82..35,407.90 rows=35,259 width=820) (actual time=1,248.087..1,248.087 rows=1 loops=1)

155. 65.260 1,248.060 ↑ 35,259.0 1 1

Sort (cost=31,176.82..31,264.97 rows=35,259 width=813) (actual time=1,248.059..1,248.060 rows=1 loops=1)

  • Sort Key: c_4.creditdossierfk, coll_1.pkey
  • Sort Method: external merge Disk: 5432kB
156. 10.684 1,182.800 ↓ 1.1 38,207 1

Hash Left Join (cost=6,826.94..15,857.76 rows=35,259 width=813) (actual time=134.572..1,182.800 rows=38,207 loops=1)

  • Hash Cond: (coll_1.warrantysubtypecd = wst.codeid)
157. 12.608 1,172.107 ↓ 1.1 38,207 1

Hash Left Join (cost=6,825.72..15,764.00 rows=35,259 width=595) (actual time=134.554..1,172.107 rows=38,207 loops=1)

  • Hash Cond: (coll_1.warrantytypecd = wit.codeid)
158. 26.216 1,159.489 ↓ 1.1 38,207 1

Hash Left Join (cost=6,824.56..15,653.38 rows=35,259 width=377) (actual time=134.534..1,159.489 rows=38,207 loops=1)

  • Hash Cond: (coll_1.warrantytypecd = wt.codeid)
159. 834.085 1,133.256 ↓ 1.1 38,207 1

Hash Join (cost=6,823.10..15,553.73 rows=35,259 width=159) (actual time=134.498..1,133.256 rows=38,207 loops=1)

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

Seq Scan on credit c_4 (cost=0.00..5,991.03 rows=99,403 width=24) (actual time=0.175..165.090 rows=99,346 loops=1)

161. 21.262 134.081 ↓ 1.1 38,207 1

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

  • Buckets: 32768 Batches: 2 Memory Usage: 2230kB
162. 50.341 112.819 ↓ 1.1 38,207 1

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

  • Hash Cond: (c2c_1.collateralfk = coll_1.pkey)
163. 21.299 21.299 ↓ 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.155..21.299 rows=68,208 loops=1)

164. 16.188 41.179 ↑ 1.0 31,325 1

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

  • Buckets: 32768 Batches: 2 Memory Usage: 1645kB
165. 24.991 24.991 ↑ 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.991 rows=31,325 loops=1)

  • Filter: ((classname)::text = 'Warranty'::text)
  • Rows Removed by Filter: 29602
166. 0.005 0.017 ↓ 9.0 9 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
167. 0.012 0.012 ↓ 9.0 9 1

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

  • Filter: (languageid = 4)
  • Rows Removed by Filter: 27
168. 0.002 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
169. 0.008 0.008 ↓ 3.0 3 1

Seq Scan on par_warrantyinscriptiontypecaption wit (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
170. 0.003 0.009 ↓ 4.0 4 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
171. 0.006 0.006 ↓ 4.0 4 1

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

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

SubPlan (forWindowAgg)

173. 0.005 0.009 ↑ 1.0 1 1

Aggregate (cost=0.09..0.10 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=1)

174. 0.004 0.004 ↑ 1.0 6 1

Values Scan on "*VALUES*_7" (cost=0.00..0.08 rows=6 width=8) (actual time=0.003..0.004 rows=6 loops=1)

175. 0.006 1,838.321 ↑ 91.0 1 1

Materialize (cost=54,971.94..56,094.11 rows=91 width=737) (actual time=1,838.321..1,838.321 rows=1 loops=1)

176. 0.009 1,838.315 ↑ 91.0 1 1

Nested Loop (cost=54,971.94..56,092.75 rows=91 width=737) (actual time=1,838.315..1,838.315 rows=1 loops=1)

177.          

CTE distinctrealestates

178. 702.991 1,600.021 ↓ 2.1 38,394 1

Hash Left Join (cost=14,389.85..30,634.30 rows=18,209 width=409) (actual time=445.602..1,600.021 rows=38,394 loops=1)

  • Hash Cond: (a.addressfk = ad.pkey)
179. 117.221 355.809 ↓ 2.1 38,394 1

Hash Join (cost=7,699.87..14,245.75 rows=18,209 width=153) (actual time=211.385..355.809 rows=38,394 loops=1)

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

Seq Scan on credit c_5 (cost=0.00..5,991.03 rows=99,403 width=20) (actual time=0.127..27.379 rows=99,346 loops=1)

181. 25.793 211.209 ↓ 2.1 38,394 1

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

  • Buckets: 65536 (originally 32768) Batches: 2 (originally 1) Memory Usage: 3838kB
182. 38.844 185.416 ↓ 2.1 38,394 1

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

  • Hash Cond: (c2c_2.collateralfk = col.pkey)
183. 10.537 10.537 ↓ 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.108..10.537 rows=68,208 loops=1)

184. 17.337 136.035 ↓ 1.9 31,464 1

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

  • Buckets: 32768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 3704kB
185. 20.461 118.698 ↓ 1.9 31,464 1

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

  • Hash Cond: (w2a.assetfk = a.pkey)
186. 19.064 83.739 ↓ 1.9 31,464 1

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

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

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

  • Filter: (warranty2assetstatuscd = 1)
188. 13.263 54.799 ↓ 1.9 31,464 1

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

  • Buckets: 32768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 2720kB
189. 18.390 41.536 ↓ 1.9 31,464 1

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

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

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

191. 5.814 17.972 ↑ 1.0 31,325 1

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

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

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

  • Filter: ((classname)::text = 'Warranty'::text)
  • Rows Removed by Filter: 29602
193. 6.055 14.498 ↓ 1.0 31,465 1

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

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

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

  • Filter: ((classname)::text = 'RealEstate'::text)
195. 119.473 234.069 ↓ 186.0 189,381 1

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

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

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

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

SubPlan (forHash Left Join)

198. 76.788 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)

199. 230.364 230.364 ↑ 1.0 14 38,394

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

200.          

CTE realestateaddressesfields

201. 0.080 1,838.271 ↑ 18,209.0 1 1

WindowAgg (cost=15,414.94..24,337.35 rows=18,209 width=542) (actual time=1,838.270..1,838.271 rows=1 loops=1)

202. 71.629 1,838.185 ↑ 18,209.0 1 1

Sort (cost=15,414.94..15,460.46 rows=18,209 width=1,566) (actual time=1,838.185..1,838.185 rows=1 loops=1)

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

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

  • Hash Cond: (dre.countrycd = country.codeid)
204. 49.511 1,736.107 ↓ 2.1 38,394 1

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

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

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

206. 10.316 26.379 ↓ 1.0 31,465 1

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

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

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

  • Filter: ((classname)::text = 'RealEstate'::text)
208. 0.083 0.289 ↑ 1.0 252 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
209. 0.206 0.206 ↑ 1.0 252 1

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

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

SubPlan (forWindowAgg)

211. 0.003 0.006 ↑ 1.0 1 1

Aggregate (cost=0.05..0.06 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=1)

212. 0.003 0.003 ↑ 1.0 3 1

Values Scan on "*VALUES*_9" (cost=0.00..0.04 rows=3 width=8) (actual time=0.002..0.003 rows=3 loops=1)

213. 1,838.276 1,838.276 ↑ 91.0 1 1

CTE Scan on realestateaddressesfields reaf1 (cost=0.00..409.70 rows=91 width=737) (actual time=1,838.276..1,838.276 rows=1 loops=1)

  • Filter: (rn = 1)
214. 0.028 0.028 ↑ 1.0 1 1

Index Only Scan using credit_pkey on credit c_1 (cost=0.29..7.78 rows=1 width=4) (actual time=0.027..0.028 rows=1 loops=1)

  • Index Cond: (pkey = reaf1.loanpartentityid)
  • Heap Fetches: 1
215.          

SubPlan (forNested Loop)

216. 0.001 0.002 ↑ 1.0 1 1

Aggregate (cost=0.01..0.02 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1)

217. 0.001 0.001 ↑ 1.0 1 1

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

218. 4.226 10.919 ↑ 1.6 19,531 1

Materialize (cost=0.00..1,008.01 rows=31,184 width=79) (actual time=0.171..10.919 rows=19,531 loops=1)

219. 6.693 6.693 ↑ 1.6 19,531 1

Seq Scan on cte_thirdpartywarrantycustom thirdpartywarr (cost=0.00..852.09 rows=31,184 width=79) (actual time=0.168..6.693 rows=19,531 loops=1)

  • Filter: (rn = 1)
  • Rows Removed by Filter: 170
220. 3.199 3.199 ↓ 213.3 640 1

CTE Scan on lastactualmarketvalue actualmarketvalue (cost=0.00..0.06 rows=3 width=38) (actual time=0.660..3.199 rows=640 loops=1)

221. 0.701 0.701 ↓ 85.0 85 1

CTE Scan on lastaftertransformationmarketvalue aftertransformmarketvalue (cost=0.00..0.02 rows=1 width=38) (actual time=0.357..0.701 rows=85 loops=1)

222. 0.020 0.020 ↑ 1.0 1 1

Index Scan using ix_runningaccountandloansbaseview__loanpartentityid on runningaccountandloansbaseview_ raalbase (cost=0.42..0.54 rows=1 width=803) (actual time=0.020..0.020 rows=1 loops=1)

  • Index Cond: ("LoanPartEntityId" = c.pkey)
223. 1.335 2.205 ↑ 1.0 6,351 1

Materialize (cost=0.00..126.26 rows=6,351 width=20) (actual time=0.012..2.205 rows=6,351 loops=1)

224. 0.870 0.870 ↑ 1.0 6,351 1

Seq Scan on cte_allowedlocationsstringset allowedlocations (cost=0.00..94.51 rows=6,351 width=20) (actual time=0.011..0.870 rows=6,351 loops=1)

225. 9.624 9.624 ↓ 5.2 1,033 1

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

226. 0.002 0.173 ↑ 3,665.0 1 1

Materialize (cost=0.00..6,917.73 rows=3,665 width=647) (actual time=0.172..0.173 rows=1 loops=1)

227. 0.171 0.171 ↑ 3,665.0 1 1

Seq Scan on directdebit dide (cost=0.00..6,899.40 rows=3,665 width=647) (actual time=0.171..0.171 rows=1 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))
228. 0.015 0.015 ↓ 0.0 0 1

Index Scan using _ix_constructiondepot_creditfk on constructiondepot con (cost=0.29..0.40 rows=1 width=260) (actual time=0.015..0.015 rows=0 loops=1)

  • Index Cond: (creditfk = c.pkey)
229. 0.004 0.011 ↓ 4.0 4 1

Materialize (cost=0.00..1.20 rows=1 width=226) (actual time=0.008..0.011 rows=4 loops=1)

230. 0.007 0.007 ↓ 4.0 4 1

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

  • Filter: (languageid = 4)
  • Rows Removed by Filter: 12
Planning time : 86.963 ms
Execution time : 13,176.483 ms