explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pBL7

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 29,566.058 ↓ 7.0 7 1

Limit (cost=132,624.49..162,762.69 rows=1 width=1,384) (actual time=13,107.696..29,566.058 rows=7 loops=1)

2.          

CTE loanpartstatushistory

3. 0.006 25.270 ↑ 1.0 10 1

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

4. 0.046 25.264 ↑ 1,449.8 10 1

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

5. 10.409 25.198 ↑ 1,449.8 10 1

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

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

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

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

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

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

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

9. 0.008 0.028 ↑ 1.0 19 1

Hash (cost=1.95..1.95 rows=19 width=19) (actual time=0.028..0.028 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.009 0.026 ↑ 1.0 17 1

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

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

Seq Scan on par_creditsubstatuscaption loanpartsubstatus (cost=0.00..1.85 rows=17 width=26) (actual time=0.007..0.017 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.009 155.184 ↑ 1.0 10 1

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

18. 0.034 155.175 ↑ 525.0 10 1

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

19. 3.229 155.101 ↑ 525.0 10 1

Sort (cost=6,243.70..6,256.82 rows=5,250 width=143) (actual time=155.100..155.101 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.425 151.872 ↑ 1.5 3,390 1

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

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

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

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

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

23. 43.412 80.270 ↓ 1.0 92,433 1

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

  • Buckets: 65536 Batches: 2 Memory Usage: 3167kB
24. 36.858 36.858 ↓ 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.154..36.858 rows=92,433 loops=1)

25. 1.537 35.827 ↓ 3.5 3,378 1

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

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

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

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

Foreign Scan (cost=100.00..320.12 rows=975 width=92) (actual time=1.725..28.735 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.963 ↑ 171.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
29. 0.224 3.959 ↑ 171.0 1 1

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

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

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

31. 0.004 0.540 ↑ 15.0 1 1

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

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

Foreign Scan on dbo_codetableparameter cp (cost=100.00..146.86 rows=15 width=4) (actual time=0.535..0.536 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.097 29,566.049 ↓ 7.0 7 1

Nested Loop Left Join (cost=124,958.55..155,096.76 rows=1 width=1,384) (actual time=13,107.695..29,566.049 rows=7 loops=1)

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

Nested Loop Left Join (cost=124,958.55..155,096.33 rows=1 width=1,327) (actual time=12,952.462..29,410.622 rows=7 loops=1)

38. 78.890 29,410.553 ↓ 7.0 7 1

Nested Loop Left Join (cost=124,958.26..155,095.89 rows=1 width=1,311) (actual time=12,952.451..29,410.553 rows=7 loops=1)

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

Nested Loop Left Join (cost=124,957.97..145,694.53 rows=1 width=1,279) (actual time=12,952.280..28,466.274 rows=7 loops=1)

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

Nested Loop Left Join (cost=124,957.97..141,631.29 rows=1 width=1,249) (actual time=12,952.061..28,124.932 rows=7 loops=1)

  • Join Filter: (cd_1.pkey = cd.pkey)
  • Rows Removed by Join Filter: 219268
41. 104.688 23,886.215 ↓ 7.0 7 1

Nested Loop Left Join (cost=67,107.47..82,672.44 rows=1 width=1,028) (actual time=9,760.995..23,886.215 rows=7 loops=1)

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

Nested Loop Left Join (cost=37,787.24..53,350.51 rows=1 width=500) (actual time=648.683..1,490.020 rows=7 loops=1)

  • Join Filter: (loanpartstatus.codeid = c.creditstatuscd)
  • Rows Removed by Join Filter: 49
43. 0.019 1,489.928 ↓ 7.0 7 1

Nested Loop (cost=37,787.24..53,348.33 rows=1 width=485) (actual time=648.669..1,489.928 rows=7 loops=1)

  • Join Filter: (ar.creditproviderfk = pcpn.codeid)
  • Rows Removed by Join Filter: 3
44. 122.279 1,489.860 ↓ 7.0 7 1

Nested Loop (cost=37,787.24..53,308.26 rows=1 width=478) (actual time=648.659..1,489.860 rows=7 loops=1)

  • Join Filter: (c.pkey = ar.creditfk)
  • Rows Removed by Join Filter: 695408
45. 0.074 649.059 ↓ 7.0 7 1

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

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

Sort (cost=8.93..8.93 rows=1 width=318) (actual time=25.404..25.414 rows=7 loops=1)

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

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

48. 0.015 25.344 ↓ 7.0 7 1

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

49. 25.287 25.287 ↓ 7.0 7 1

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

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

Index Scan using credit_pkey on credit c (cost=0.29..8.31 rows=1 width=49) (actual time=0.006..0.006 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.909 623.571 ↑ 316.7 279 1

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

  • Group Key: c_1.creditdossierfk
53. 85.881 622.662 ↑ 341.5 291 1

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

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

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

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

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

56. 77.893 114.504 ↓ 1.0 99,351 1

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

  • Group Key: pc_1.pkey
57. 36.611 36.611 ↓ 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.017..36.611 rows=99,351 loops=1)

58. 47.045 313.177 ↑ 1.0 99,344 1

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

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

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

  • Hash Cond: (cb_1.creditfk = c_1.pkey)
60. 42.994 42.994 ↓ 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.134..42.994 rows=99,345 loops=1)

61. 35.552 130.220 ↑ 1.0 99,344 1

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

  • Buckets: 131072 Batches: 2 Memory Usage: 3157kB
62. 94.668 94.668 ↑ 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.150..94.668 rows=99,344 loops=1)

63. 718.522 718.522 ↓ 203.2 99,345 7

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

  • Filter: ((adminroletypecd = 2) AND ((activerole)::integer = 1))
  • Rows Removed by Filter: 206197
64. 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
65. 0.063 0.063 ↑ 2.4 8 7

Seq Scan on par_creditstatuscaption loanpartstatus (cost=0.00..1.95 rows=19 width=19) (actual time=0.005..0.009 rows=8 loops=7)

  • Filter: (languageid = 4)
  • Rows Removed by Filter: 24
66. 20,998.131 22,291.507 ↓ 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,301.230..3,184.501 rows=92,384 loops=7)

67.          

CTE contractors

68. 350.673 5,442.390 ↓ 199.9 156,938 1

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

69. 410.911 4,307.027 ↓ 199.9 156,938 1

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

  • Sort Key: cd_3.pkey, r.roletypecd, r.pkey
  • Sort Method: external merge Disk: 28296kB
70. 206.394 3,896.116 ↓ 199.9 156,938 1

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

71. 186.615 3,218.908 ↓ 199.9 156,938 1

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

72. 88.116 2,404.541 ↓ 199.9 156,938 1

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

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

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

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

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

75. 495.794 1,534.803 ↓ 199.9 156,938 1

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

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

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

77. 169.141 997.967 ↓ 199.9 156,938 1

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

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

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

79. 72.332 72.332 ↓ 199.9 156,938 1

Seq Scan on role r (cost=0.00..4,711.59 rows=785 width=32) (actual time=0.146..72.332 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.005 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.009 0.009 ↓ 3.0 3 1

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

  • Filter: (languageid = 4)
  • Rows Removed by Filter: 9
84. 0.003 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
85. 0.008 0.008 ↓ 2.0 2 1

Seq Scan on par_partytypecaption ppt (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
86. 627.752 627.752 ↑ 1.0 1 156,938

Index Scan using credit_pkey on credit c_3 (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_3 (cost=0.29..0.38 rows=1 width=20) (actual time=0.003..0.003 rows=1 loops=156,938)

  • Index Cond: (pkey = c_3.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*_5" (cost=0.00..0.10 rows=8 width=8) (actual time=0.000..0.003 rows=8 loops=156,938)

91.          

CTE grouped

92. 951.666 10,158.646 ↓ 23,096.0 92,384 1

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

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

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

  • Sort Key: con.dossierentityid
  • Sort Method: external merge Disk: 24888kB
94. 351.761 8,865.871 ↓ 37,204.5 148,818 1

Hash Right Join (cost=7,254.70..7,584.60 rows=4 width=4,040) (actual time=6,386.619..8,865.871 rows=148,818 loops=1)

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

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

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

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

97. 417.221 553.314 ↓ 185.9 188,729 1

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

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

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

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

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

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

CTE Scan on contractors con (cost=0.00..17.66 rows=4 width=3,964) (actual time=4,209.497..5,748.566 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*_6" (cost=0.00..0.04 rows=3 width=8) (actual time=0.000..0.001 rows=3 loops=646,688)

104. 431.886 4,199.720 ↓ 344.2 31,324 7

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

105.          

CTE distinctrealestates

106. 619.410 1,583.709 ↓ 2.1 38,392 1

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

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

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

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

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

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

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

  • Hash Cond: (cd_2.pkey = c_2.creditdossierfk)
110. 17.028 17.028 ↓ 1.0 92,433 1

Seq Scan on creditdossier cd_2 (cost=0.00..2,429.32 rows=92,432 width=20) (actual time=0.159..17.028 rows=92,433 loops=1)

111. 28.284 420.505 ↓ 2.1 38,392 1

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

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

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

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

Seq Scan on credit c_2 (cost=0.00..5,988.63 rows=99,363 width=24) (actual time=0.153..25.400 rows=99,344 loops=1)

114. 25.258 243.190 ↓ 2.1 38,392 1

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

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

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

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

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

117. 18.452 161.626 ↓ 1.9 31,463 1

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

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

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

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

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

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

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

  • Filter: (warranty2assetstatuscd = 1)
121. 13.926 67.429 ↓ 1.9 31,463 1

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

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

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

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

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

124. 9.411 29.904 ↑ 1.0 31,324 1

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

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

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

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

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

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

Seq Scan on asset a (cost=0.00..863.30 rows=31,464 width=65) (actual time=0.148..16.143 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*_3" (cost=0.00..0.20 rows=16 width=8) (actual time=0.000..0.006 rows=16 loops=38,392)

131.          

CTE realestateaddressesfields

132. 928.182 2,876.518 ↓ 2.1 38,392 1

WindowAgg (cost=15,414.94..23,426.90 rows=18,209 width=542) (actual time=1,811.638..2,876.518 rows=38,392 loops=1)

133. 97.376 1,833.160 ↓ 2.1 38,392 1

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

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

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

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

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

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

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

137. 9.590 23.641 ↑ 1.0 31,464 1

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

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

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

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

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

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

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

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

SubPlan (forWindowAgg)

142. 76.784 115.176 ↑ 1.0 1 38,392

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

143. 38.392 38.392 ↑ 1.0 3 38,392

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

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

CTE Scan on realestateaddressesfields reaf1 (cost=0.00..409.70 rows=91 width=229) (actual time=258.816..444.290 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.751 121.751 ↓ 1.0 99,345 7

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

147. 604.142 865.389 ↓ 1.0 99,351 7

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

  • Group Key: pc_2.pkey
148. 261.247 261.247 ↓ 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..37.321 rows=99,351 loops=7)

149. 0.035 0.035 ↑ 1.0 1 7

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

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

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

  • Filter: (rn = 1)
151.          

SubPlan (forNested Loop Left Join)

152. 0.042 0.091 ↑ 1.0 1 7

Aggregate (cost=0.17..0.18 rows=1 width=8) (actual time=0.013..0.013 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.002..0.007 rows=11 loops=7)

Planning time : 20.943 ms
Execution time : 29,656.721 ms