explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tp3b

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 29,857.397 ↓ 7.0 7 1

Limit (cost=132,624.63..162,760.67 rows=1 width=1,384) (actual time=13,530.601..29,857.397 rows=7 loops=1)

2.          

CTE loanpartstatushistory

3. 0.005 42.673 ↑ 1.0 10 1

Limit (cost=1,420.39..1,421.04 rows=10 width=73) (actual time=42.629..42.673 rows=10 loops=1)

4. 0.045 42.668 ↑ 1,449.8 10 1

WindowAgg (cost=1,420.39..2,362.76 rows=14,498 width=73) (actual time=42.628..42.668 rows=10 loops=1)

5. 27.085 42.603 ↑ 1,449.8 10 1

Sort (cost=1,420.39..1,456.63 rows=14,498 width=73) (actual time=42.602..42.603 rows=10 loops=1)

  • Sort Key: loanpartstatushistory_1.creditfk, loanpartstatushistory_1.creditstatuscd, loanpartstatushistory_1.statuschangedate, loanpartstatushistory_1.pkey
  • Sort Method: quicksort Memory: 1945kB
6. 5.045 15.518 ↓ 1.0 14,499 1

Hash Left Join (cost=4.25..418.32 rows=14,498 width=73) (actual time=0.082..15.518 rows=14,499 loops=1)

  • Hash Cond: (loanpartstatushistory_1.creditsubstatuscd = loanpartsubstatus.codeid)
7. 7.927 10.446 ↓ 1.0 14,499 1

Hash Left Join (cost=2.19..373.71 rows=14,498 width=51) (actual time=0.047..10.446 rows=14,499 loops=1)

  • Hash Cond: (loanpartstatushistory_1.creditstatuscd = loanpartstatus_1.codeid)
8. 2.488 2.488 ↓ 1.0 14,499 1

Seq Scan on creditstatushistory loanpartstatushistory_1 (cost=0.00..324.98 rows=14,498 width=36) (actual time=0.009..2.488 rows=14,499 loops=1)

9. 0.011 0.031 ↑ 1.0 19 1

Hash (cost=1.95..1.95 rows=19 width=19) (actual time=0.031..0.031 rows=19 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
10. 0.020 0.020 ↑ 1.0 19 1

Seq Scan on par_creditstatuscaption loanpartstatus_1 (cost=0.00..1.95 rows=19 width=19) (actual time=0.007..0.020 rows=19 loops=1)

  • Filter: (languageid = 4)
  • Rows Removed by Filter: 57
11. 0.008 0.027 ↑ 1.0 17 1

Hash (cost=1.85..1.85 rows=17 width=26) (actual time=0.027..0.027 rows=17 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
12. 0.019 0.019 ↑ 1.0 17 1

Seq Scan on par_creditsubstatuscaption loanpartsubstatus (cost=0.00..1.85 rows=17 width=26) (actual time=0.009..0.019 rows=17 loops=1)

  • Filter: (languageid = 4)
  • Rows Removed by Filter: 51
13.          

SubPlan (forWindowAgg)

14. 0.010 0.020 ↑ 1.0 1 10

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

15. 0.010 0.010 ↑ 1.0 2 10

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

16.          

CTE dunningdossierprep

17. 0.010 150.705 ↑ 1.0 10 1

Limit (cost=6,243.70..6,244.90 rows=10 width=139) (actual time=150.644..150.705 rows=10 loops=1)

18. 0.032 150.695 ↑ 525.0 10 1

WindowAgg (cost=6,243.70..6,873.70 rows=5,250 width=139) (actual time=150.642..150.695 rows=10 loops=1)

19. 3.089 150.623 ↑ 525.0 10 1

Sort (cost=6,243.70..6,256.82 rows=5,250 width=143) (actual time=150.622..150.623 rows=10 loops=1)

  • Sort Key: cd_1_1.pkey, (CASE WHEN ((cpi.pkey IS NULL) AND (dd_1.pkey IS NOT NULL)) THEN 1 ELSE 2 END)
  • Sort Method: quicksort Memory: 997kB
20. 2.797 147.534 ↑ 1.5 3,390 1

Hash Right Join (cost=5,021.05..5,919.30 rows=5,250 width=143) (actual time=113.339..147.534 rows=3,390 loops=1)

  • Hash Cond: (ds.dunningdossierfk = dd_1.pkey)
21. 11.031 110.676 ↓ 3.1 3,390 1

Hash Left Join (cost=4,316.72..5,027.84 rows=1,077 width=47) (actual time=79.267..110.676 rows=3,390 loops=1)

  • Hash Cond: ((ds.mainreference)::text = (cd_1_1.externalreference)::text)
22. 21.273 21.273 ↓ 3.1 3,390 1

Foreign Scan on dbo_debtsource ds (cost=100.00..142.31 rows=1,077 width=52) (actual time=0.812..21.273 rows=3,390 loops=1)

23. 39.855 78.372 ↓ 1.0 92,433 1

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

  • Buckets: 65536 Batches: 2 Memory Usage: 3167kB
24. 38.517 38.517 ↓ 1.0 92,433 1

Seq Scan on creditdossier cd_1_1 (cost=0.00..2,429.32 rows=92,432 width=27) (actual time=0.141..38.517 rows=92,433 loops=1)

25. 1.498 34.061 ↓ 3.5 3,378 1

Hash (cost=692.14..692.14 rows=975 width=96) (actual time=34.061..34.061 rows=3,378 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 363kB
26. 1.518 32.563 ↓ 3.5 3,378 1

Hash Left Join (cost=440.52..692.14 rows=975 width=96) (actual time=5.451..32.563 rows=3,378 loops=1)

  • Hash Cond: (dd_1.statuscd = cpi.codetablevalue)
27. 27.328 27.328 ↓ 3.5 3,378 1

Foreign Scan (cost=100.00..320.12 rows=975 width=92) (actual time=1.716..27.328 rows=3,378 loops=1)

  • Relations: ((closecm.dbo_dunningdossier dd_1) LEFT JOIN (closecm.dbo_par_dossierstatuscaption pds)) LEFT JOIN (closecm.dbo_par_dossierstatuscaption dunningdossierstatus)
28. 0.004 3.717 ↑ 171.0 1 1

Hash (cost=338.38..338.38 rows=171 width=8) (actual time=3.716..3.717 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
29. 0.222 3.713 ↑ 171.0 1 1

Hash Join (cost=247.05..338.38 rows=171 width=8) (actual time=3.400..3.713 rows=1 loops=1)

  • Hash Cond: (cpi.codetableparameterfk = cp.pkey)
30. 2.982 2.982 ↑ 2.3 996 1

Foreign Scan on dbo_codetableparameteritem cpi (cost=100.00..178.25 rows=2,275 width=12) (actual time=0.470..2.982 rows=996 loops=1)

31. 0.004 0.509 ↑ 15.0 1 1

Hash (cost=146.86..146.86 rows=15 width=4) (actual time=0.509..0.509 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
32. 0.505 0.505 ↑ 15.0 1 1

Foreign Scan on dbo_codetableparameter cp (cost=100.00..146.86 rows=15 width=4) (actual time=0.504..0.505 rows=1 loops=1)

33.          

SubPlan (forWindowAgg)

34. 0.010 0.040 ↑ 1.0 1 10

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

35. 0.030 0.030 ↑ 1.0 6 10

Values Scan on "*VALUES*_1" (cost=0.00..0.08 rows=6 width=8) (actual time=0.001..0.003 rows=6 loops=10)

36. 0.093 29,857.390 ↓ 7.0 7 1

Nested Loop Left Join (cost=124,958.70..155,094.73 rows=1 width=1,384) (actual time=13,530.599..29,857.390 rows=7 loops=1)

  • Join Filter: (dd.dossierentityid = c.creditdossierfk)
  • Rows Removed by Join Filter: 70
37. 0.038 29,706.461 ↓ 7.0 7 1

Nested Loop Left Join (cost=124,958.70..155,094.30 rows=1 width=1,327) (actual time=13,379.846..29,706.461 rows=7 loops=1)

38. 84.783 29,706.395 ↓ 7.0 7 1

Nested Loop Left Join (cost=124,958.40..155,093.86 rows=1 width=1,311) (actual time=13,379.836..29,706.395 rows=7 loops=1)

  • Join Filter: (pc_2.pkey = c.productcopyfk)
  • Rows Removed by Join Filter: 695450
39. 204.133 28,800.365 ↓ 7.0 7 1

Nested Loop Left Join (cost=124,958.11..145,692.51 rows=1 width=1,279) (actual time=13,379.669..28,800.365 rows=7 loops=1)

  • Join Filter: (cb.creditfk = c.pkey)
  • Rows Removed by Join Filter: 695408
40. 31.222 28,474.950 ↓ 7.0 7 1

Nested Loop Left Join (cost=124,958.11..141,629.27 rows=1 width=1,249) (actual time=13,379.466..28,474.950 rows=7 loops=1)

  • Join Filter: (cd_1.pkey = cd.pkey)
  • Rows Removed by Join Filter: 219268
41. 115.536 24,096.861 ↓ 7.0 7 1

Nested Loop Left Join (cost=67,107.61..82,670.42 rows=1 width=1,028) (actual time=10,070.773..24,096.861 rows=7 loops=1)

  • Join Filter: (grouped.dossierentityid = cd.pkey)
  • Rows Removed by Join Filter: 646681
42. 0.022 1,484.459 ↓ 7.0 7 1

Nested Loop (cost=37,787.38..53,348.49 rows=1 width=500) (actual time=674.523..1,484.459 rows=7 loops=1)

  • Join Filter: (ar.creditproviderfk = pcpn.codeid)
  • Rows Removed by Join Filter: 3
43. 131.047 1,484.388 ↓ 7.0 7 1

Nested Loop (cost=37,787.38..53,308.43 rows=1 width=493) (actual time=674.513..1,484.388 rows=7 loops=1)

  • Join Filter: (c.pkey = ar.creditfk)
  • Rows Removed by Join Filter: 695408
44. 0.039 674.985 ↓ 7.0 7 1

Nested Loop Left Join (cost=37,787.38..43,612.40 rows=1 width=493) (actual time=674.242..674.985 rows=7 loops=1)

45. 0.068 674.855 ↓ 7.0 7 1

Merge Left Join (cost=37,787.24..43,612.24 rows=1 width=478) (actual time=674.220..674.855 rows=7 loops=1)

  • Merge Cond: (cd.pkey = c_1.creditdossierfk)
46. 0.031 42.807 ↓ 7.0 7 1

Sort (cost=8.93..8.93 rows=1 width=318) (actual time=42.798..42.807 rows=7 loops=1)

  • Sort Key: cd.pkey
  • Sort Method: quicksort Memory: 26kB
47. 0.007 42.776 ↓ 7.0 7 1

Nested Loop Left Join (cost=0.58..8.92 rows=1 width=318) (actual time=42.668..42.776 rows=7 loops=1)

48. 0.019 42.741 ↓ 7.0 7 1

Nested Loop (cost=0.29..8.54 rows=1 width=291) (actual time=42.656..42.741 rows=7 loops=1)

49. 42.687 42.687 ↓ 7.0 7 1

CTE Scan on loanpartstatushistory (cost=0.00..0.22 rows=1 width=242) (actual time=42.633..42.687 rows=7 loops=1)

  • Filter: (rn = 1)
  • Rows Removed by Filter: 3
50. 0.035 0.035 ↑ 1.0 1 7

Index Scan using credit_pkey on credit c (cost=0.29..8.31 rows=1 width=49) (actual time=0.005..0.005 rows=1 loops=7)

  • Index Cond: (pkey = loanpartstatushistory.loanpartentityid)
51. 0.028 0.028 ↑ 1.0 1 7

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

  • Index Cond: (pkey = c.creditdossierfk)
52. 0.898 631.980 ↑ 316.7 279 1

GroupAggregate (cost=37,778.31..42,498.83 rows=88,357 width=164) (actual time=631.024..631.980 rows=279 loops=1)

  • Group Key: c_1.creditdossierfk
53. 84.521 631.082 ↑ 341.5 291 1

Sort (cost=37,778.31..38,026.72 rows=99,363 width=54) (actual time=631.003..631.082 rows=291 loops=1)

  • Sort Key: c_1.creditdossierfk
  • Sort Method: external merge Disk: 4120kB
54. 93.457 546.561 ↑ 1.0 99,344 1

Hash Right Join (cost=14,373.23..26,132.48 rows=99,363 width=54) (actual time=315.857..546.561 rows=99,344 loops=1)

  • Hash Cond: (sp."ProductPkey" = c_1.productcopyfk)
55. 18.135 138.294 ↓ 1.0 99,351 1

Subquery Scan on sp (cost=0.29..8,159.48 rows=99,350 width=36) (actual time=0.033..138.294 rows=99,351 loops=1)

56. 81.573 120.159 ↓ 1.0 99,351 1

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

  • Group Key: pc_1.pkey
57. 38.586 38.586 ↓ 1.0 99,351 1

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

58. 44.980 314.810 ↑ 1.0 99,344 1

Hash (cost=12,450.90..12,450.90 rows=99,363 width=26) (actual time=314.810..314.810 rows=99,344 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 3396kB
59. 89.309 269.830 ↑ 1.0 99,344 1

Hash Right Join (cost=7,716.67..12,450.90 rows=99,363 width=26) (actual time=135.627..269.830 rows=99,344 loops=1)

  • Hash Cond: (cb_1.creditfk = c_1.pkey)
60. 45.755 45.755 ↓ 1.0 99,345 1

Seq Scan on creditbalances cb_1 (cost=0.00..2,821.44 rows=99,344 width=22) (actual time=0.156..45.755 rows=99,345 loops=1)

61. 39.673 134.766 ↑ 1.0 99,344 1

Hash (cost=5,988.63..5,988.63 rows=99,363 width=12) (actual time=134.766..134.766 rows=99,344 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 3157kB
62. 95.093 95.093 ↑ 1.0 99,344 1

Seq Scan on credit c_1 (cost=0.00..5,988.63 rows=99,363 width=12) (actual time=0.165..95.093 rows=99,344 loops=1)

63. 0.091 0.091 ↑ 1.0 1 7

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

  • Index Cond: ((codeid = c.creditstatuscd) AND (languageid = 4))
64. 678.356 678.356 ↓ 203.2 99,345 7

Seq Scan on adminrole ar (cost=0.00..9,689.92 rows=489 width=8) (actual time=0.141..96.908 rows=99,345 loops=7)

  • Filter: ((adminroletypecd = 2) AND ((activerole)::integer = 1))
  • Rows Removed by Filter: 206197
65. 0.049 0.049 ↑ 449.0 1 7

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

  • Filter: (languageid = 4)
  • Rows Removed by Filter: 4
66. 21,203.490 22,496.866 ↓ 23,096.0 92,384 7

CTE Scan on grouped (cost=29,320.23..29,321.84 rows=4 width=1,408) (actual time=1,341.280..3,213.838 rows=92,384 loops=7)

67.          

CTE contractors

68. 425.352 5,584.452 ↓ 199.9 156,938 1

WindowAgg (cost=21,605.07..21,734.60 rows=785 width=1,154) (actual time=4,286.499..5,584.452 rows=156,938 loops=1)

69. 416.919 4,374.410 ↓ 199.9 156,938 1

Sort (cost=21,605.07..21,607.04 rows=785 width=664) (actual time=4,286.472..4,374.410 rows=156,938 loops=1)

  • Sort Key: cd_2.pkey, r.roletypecd, r.pkey
  • Sort Method: external merge Disk: 28296kB
70. 185.860 3,957.491 ↓ 199.9 156,938 1

Nested Loop Left Join (cost=9,676.12..21,567.33 rows=785 width=664) (actual time=1,113.647..3,957.491 rows=156,938 loops=1)

71. 188.462 3,300.817 ↓ 199.9 156,938 1

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

72. 84.400 2,484.603 ↓ 199.9 156,938 1

Hash Left Join (cost=9,675.53..17,017.01 rows=785 width=628) (actual time=1,113.609..2,484.603 rows=156,938 loops=1)

  • Hash Cond: (p.partytypecd = ppt.codeid)
73. 88.577 2,400.196 ↓ 199.9 156,938 1

Hash Left Join (cost=9,674.42..17,012.90 rows=785 width=410) (actual time=1,113.588..2,400.196 rows=156,938 loops=1)

  • Hash Cond: (pd.gendercd = par_gendercaption.codeid)
74. 206.804 2,311.605 ↓ 199.9 156,938 1

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

75. 484.297 1,633.987 ↓ 199.9 156,938 1

Hash Right Join (cost=9,672.84..16,609.06 rows=785 width=196) (actual time=1,113.532..1,633.987 rows=156,938 loops=1)

  • Hash Cond: ((pd.isactivepartydetail)::text = (p.partydetaillinkid)::text)
76. 39.169 39.169 ↓ 1.0 170,429 1

Seq Scan on partydetail pd (cost=0.00..6,289.27 rows=170,427 width=91) (actual time=0.128..39.169 rows=170,429 loops=1)

77. 183.769 1,110.521 ↓ 199.9 156,938 1

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

  • Buckets: 32768 (originally 1024) Batches: 8 (originally 1) Memory Usage: 3841kB
78. 223.616 926.752 ↓ 199.9 156,938 1

Nested Loop (cost=0.42..9,663.03 rows=785 width=179) (actual time=0.161..926.752 rows=156,938 loops=1)

79. 75.384 75.384 ↓ 199.9 156,938 1

Seq Scan on role r (cost=0.00..4,711.59 rows=785 width=32) (actual time=0.147..75.384 rows=156,938 loops=1)

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

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,938)

  • Index Cond: (pkey = r.partyfk)
81. 470.814 470.814 ↑ 1.0 1 156,938

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
83. 0.010 0.010 ↓ 3.0 3 1

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

  • Filter: (languageid = 4)
  • Rows Removed by Filter: 9
84. 0.002 0.007 ↓ 2.0 2 1

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

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

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

  • Filter: (languageid = 4)
  • Rows Removed by Filter: 6
86. 627.752 627.752 ↑ 1.0 1 156,938

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,938)

  • Index Cond: (pkey = r.creditfk)
87. 470.814 470.814 ↑ 1.0 1 156,938

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

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

SubPlan (forWindowAgg)

89. 313.876 784.690 ↑ 1.0 1 156,938

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

90. 470.814 470.814 ↑ 1.0 8 156,938

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

91.          

CTE grouped

92. 999.077 10,476.142 ↓ 23,096.0 92,384 1

GroupAggregate (cost=7,584.64..7,585.63 rows=4 width=1,140) (actual time=9,388.468..10,476.142 rows=92,384 loops=1)

  • Group Key: con.dossierentityid
93. 327.464 9,477.065 ↓ 37,204.5 148,818 1

Sort (cost=7,584.64..7,584.65 rows=4 width=4,040) (actual time=9,388.434..9,477.065 rows=148,818 loops=1)

  • Sort Key: con.dossierentityid
  • Sort Method: external merge Disk: 24888kB
94. 344.403 9,149.601 ↓ 37,204.5 148,818 1

Hash Right Join (cost=7,254.70..7,584.60 rows=4 width=4,040) (actual time=6,610.065..9,149.601 rows=148,818 loops=1)

  • Hash Cond: (conadd.partydetailfk = con.pdpkey)
95. 46.580 2,729.176 ↓ 31,958.4 159,792 1

Subquery Scan on conadd (cost=7,236.98..7,566.86 rows=5 width=84) (actual time=523.439..2,729.176 rows=159,792 loops=1)

  • Filter: (conadd.rn = 1)
  • Rows Removed by Filter: 28937
96. 2,085.374 2,682.596 ↓ 185.9 188,729 1

WindowAgg (cost=7,236.98..7,554.17 rows=1,015 width=96) (actual time=523.438..2,682.596 rows=188,729 loops=1)

97. 464.385 597.222 ↓ 185.9 188,729 1

Sort (cost=7,236.98..7,239.52 rows=1,015 width=62) (actual time=523.364..597.222 rows=188,729 loops=1)

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

Seq Scan on address (cost=0.00..7,186.30 rows=1,015 width=62) (actual time=0.168..132.837 rows=188,729 loops=1)

  • Filter: ((addresstypecd = ANY ('{1,11}'::integer[])) AND ((isactive)::integer = 1))
  • Rows Removed by Filter: 14890
99. 152.517 6,076.022 ↓ 37,082.2 148,329 1

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

  • Buckets: 32768 (originally 1024) Batches: 8 (originally 1) Memory Usage: 3841kB
100. 5,923.505 5,923.505 ↓ 37,082.2 148,329 1

CTE Scan on contractors con (cost=0.00..17.66 rows=4 width=3,964) (actual time=4,286.504..5,923.505 rows=148,329 loops=1)

  • Filter: (rn = 1)
  • Rows Removed by Filter: 8609
101.          

SubPlan (forCTE Scan)

102. 646.688 1,293.376 ↑ 1.0 1 646,688

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

103. 646.688 646.688 ↑ 1.0 3 646,688

Values Scan on "*VALUES*_4" (cost=0.00..0.04 rows=3 width=8) (actual time=0.000..0.001 rows=3 loops=646,688)

104. 456.491 4,346.867 ↓ 344.2 31,324 7

Nested Loop (cost=57,850.50..58,956.80 rows=91 width=737) (actual time=279.511..620.981 rows=31,324 loops=7)

105.          

CTE distinctrealestates

106. 661.099 1,688.817 ↓ 2.1 38,392 1

Hash Right Join (cost=17,772.00..34,423.31 rows=18,209 width=409) (actual time=697.118..1,688.817 rows=38,392 loops=1)

  • Hash Cond: (ad.pkey = a.addressfk)
107. 88.226 88.226 ↓ 186.0 189,379 1

Seq Scan on address ad (cost=0.00..6,677.26 rows=1,018 width=333) (actual time=0.174..88.226 rows=189,379 loops=1)

  • Filter: ((isactive)::integer = 1)
  • Rows Removed by Filter: 14240
108. 27.984 593.964 ↓ 2.1 38,392 1

Hash (cost=17,544.38..17,544.38 rows=18,209 width=169) (actual time=593.964..593.964 rows=38,392 loops=1)

  • Buckets: 32768 (originally 32768) Batches: 2 (originally 1) Memory Usage: 3841kB
109. 66.728 565.980 ↓ 2.1 38,392 1

Hash Join (cost=14,470.81..17,544.38 rows=18,209 width=169) (actual time=482.942..565.980 rows=38,392 loops=1)

  • Hash Cond: (cd_3.pkey = c_3.creditdossierfk)
110. 16.551 16.551 ↓ 1.0 92,433 1

Seq Scan on creditdossier cd_3 (cost=0.00..2,429.32 rows=92,432 width=20) (actual time=0.140..16.551 rows=92,433 loops=1)

111. 27.098 482.701 ↓ 2.1 38,392 1

Hash (cost=14,243.20..14,243.20 rows=18,209 width=153) (actual time=482.701..482.701 rows=38,392 loops=1)

  • Buckets: 32768 (originally 32768) Batches: 2 (originally 1) Memory Usage: 3841kB
112. 118.525 455.603 ↓ 2.1 38,392 1

Hash Join (cost=7,699.87..14,243.20 rows=18,209 width=153) (actual time=311.198..455.603 rows=38,392 loops=1)

  • Hash Cond: (c_3.pkey = c2c.creditfk)
113. 26.067 26.067 ↑ 1.0 99,344 1

Seq Scan on credit c_3 (cost=0.00..5,988.63 rows=99,363 width=24) (actual time=0.139..26.067 rows=99,344 loops=1)

114. 24.427 311.011 ↓ 2.1 38,392 1

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

  • Buckets: 65536 (originally 32768) Batches: 2 (originally 1) Memory Usage: 3838kB
115. 39.241 286.584 ↓ 2.1 38,392 1

Hash Join (cost=5,779.35..7,472.26 rows=18,209 width=137) (actual time=238.958..286.584 rows=38,392 loops=1)

  • Hash Cond: (c2c.collateralfk = col.pkey)
116. 10.547 10.547 ↑ 1.0 68,205 1

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

117. 17.173 236.796 ↓ 1.9 31,463 1

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

  • Buckets: 32768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 3704kB
118. 19.710 219.623 ↓ 1.9 31,463 1

Hash Join (cost=4,499.41..5,576.04 rows=16,265 width=125) (actual time=171.791..219.623 rows=31,463 loops=1)

  • Hash Cond: (w2a.assetfk = a.pkey)
119. 18.685 119.063 ↓ 1.9 31,463 1

Hash Join (cost=3,242.81..4,276.73 rows=16,265 width=64) (actual time=90.811..119.063 rows=31,463 loops=1)

  • Hash Cond: (w2ash.warranty2assetfk = w2a.pkey)
120. 9.812 9.812 ↑ 1.0 31,463 1

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

  • Filter: (warranty2assetstatuscd = 1)
121. 13.476 90.566 ↓ 1.9 31,463 1

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

  • Buckets: 32768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 2720kB
122. 21.787 77.090 ↓ 1.9 31,463 1

Hash Join (cost=2,316.26..3,039.49 rows=16,265 width=52) (actual time=45.683..77.090 rows=31,463 loops=1)

  • Hash Cond: (w2a.warrantyfk = col.pkey)
123. 9.934 9.934 ↑ 1.0 31,463 1

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

124. 18.748 45.369 ↑ 1.0 31,324 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1608kB
125. 26.621 26.621 ↑ 1.0 31,324 1

Seq Scan on collateral col (cost=0.00..1,922.56 rows=31,496 width=20) (actual time=0.135..26.621 rows=31,324 loops=1)

  • Filter: ((classname)::text = 'Warranty'::text)
  • Rows Removed by Filter: 29601
126. 19.672 80.850 ↑ 1.0 31,464 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 2224kB
127. 61.178 61.178 ↑ 1.0 31,464 1

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

  • Filter: ((classname)::text = 'RealEstate'::text)
128.          

SubPlan (forHash Right Join)

129. 115.176 345.528 ↑ 1.0 1 38,392

Aggregate (cost=0.24..0.25 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=38,392)

130. 230.352 230.352 ↑ 1.0 16 38,392

Values Scan on "*VALUES*_5" (cost=0.00..0.20 rows=16 width=8) (actual time=0.000..0.006 rows=16 loops=38,392)

131.          

CTE realestateaddressesfields

132. 953.082 3,007.709 ↓ 2.1 38,392 1

WindowAgg (cost=15,414.94..23,426.90 rows=18,209 width=542) (actual time=1,956.370..3,007.709 rows=38,392 loops=1)

133. 125.375 1,977.843 ↓ 2.1 38,392 1

Sort (cost=15,414.94..15,460.46 rows=18,209 width=1,566) (actual time=1,956.297..1,977.843 rows=38,392 loops=1)

  • Sort Key: dre.dossierentityid, dre.isactive, dre."Rank", a_1.pkey
  • Sort Method: external merge Disk: 4840kB
134. 22.208 1,852.468 ↓ 2.1 38,392 1

Hash Left Join (cost=1,279.35..1,739.94 rows=18,209 width=1,566) (actual time=722.158..1,852.468 rows=38,392 loops=1)

  • Hash Cond: (dre.countrycd = country.codeid)
135. 55.571 1,829.976 ↓ 2.1 38,392 1

Hash Join (cost=1,256.60..1,668.59 rows=18,209 width=1,559) (actual time=721.863..1,829.976 rows=38,392 loops=1)

  • Hash Cond: (dre.realestateentityid = a_1.pkey)
136. 1,749.725 1,749.725 ↓ 2.1 38,392 1

CTE Scan on distinctrealestates dre (cost=0.00..364.18 rows=18,209 width=1,543) (actual time=697.122..1,749.725 rows=38,392 loops=1)

137. 9.764 24.680 ↑ 1.0 31,464 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1854kB
138. 14.916 14.916 ↑ 1.0 31,464 1

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

  • Filter: ((classname)::text = 'RealEstate'::text)
139. 0.082 0.284 ↑ 1.0 252 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
140. 0.202 0.202 ↑ 1.0 252 1

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

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

SubPlan (forWindowAgg)

142. 38.392 76.784 ↑ 1.0 1 38,392

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

143. 38.392 38.392 ↑ 1.0 3 38,392

Values Scan on "*VALUES*_6" (cost=0.00..0.04 rows=3 width=8) (actual time=0.000..0.001 rows=3 loops=38,392)

144. 3,232.572 3,232.572 ↓ 344.2 31,324 7

CTE Scan on realestateaddressesfields reaf1 (cost=0.00..409.70 rows=91 width=229) (actual time=279.491..461.796 rows=31,324 loops=7)

  • Filter: (rn = 1)
  • Rows Removed by Filter: 7068
145. 657.804 657.804 ↑ 1.0 1 219,268

Index Only Scan using creditdossier_pkey on creditdossier cd_1 (cost=0.29..7.65 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=219,268)

  • Index Cond: (pkey = reaf1.dossierentityid)
  • Heap Fetches: 219268
146. 121.282 121.282 ↓ 1.0 99,345 7

Seq Scan on creditbalances cb (cost=0.00..2,821.44 rows=99,344 width=38) (actual time=0.147..17.326 rows=99,345 loops=7)

147. 563.178 821.247 ↓ 1.0 99,351 7

Group (cost=0.29..7,165.98 rows=99,350 width=40) (actual time=0.016..117.321 rows=99,351 loops=7)

  • Group Key: pc_2.pkey
148. 258.069 258.069 ↓ 1.0 99,351 7

Index Scan using productcopy_pkey on productcopy pc_2 (cost=0.29..5,551.54 rows=99,350 width=8) (actual time=0.013..36.867 rows=99,351 loops=7)

149. 0.028 0.028 ↑ 1.0 1 7

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

  • Index Cond: (pkey = c.productcopyfk)
150. 150.759 150.759 ↓ 10.0 10 7

CTE Scan on dunningdossierprep dd (cost=0.00..0.22 rows=1 width=112) (actual time=21.523..21.537 rows=10 loops=7)

  • Filter: (rn = 1)
151.          

SubPlan (forNested Loop Left Join)

152. 0.028 0.077 ↑ 1.0 1 7

Aggregate (cost=0.17..0.18 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=7)

153. 0.049 0.049 ↑ 1.0 11 7

Values Scan on "*VALUES*_2" (cost=0.00..0.14 rows=11 width=8) (actual time=0.003..0.007 rows=11 loops=7)

Planning time : 24.342 ms
Execution time : 29,904.215 ms