explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3nMF

Settings
# exclusive inclusive rows x rows loops node
1. 1.135 51,886.271 ↓ 873.0 873 1

Limit (cost=48,586.79..56,197.23 rows=1 width=4) (actual time=48,095.481..51,886.271 rows=873 loops=1)

2.          

CTE loanpartstatushistory

3. 0.136 48.979 ↑ 1.0 1,000 1

Limit (cost=1,420.39..1,485.39 rows=1,000 width=73) (actual time=43.113..48.979 rows=1,000 loops=1)

4. 3.650 48.843 ↑ 14.5 1,000 1

WindowAgg (cost=1,420.39..2,362.76 rows=14,498 width=73) (actual time=43.112..48.843 rows=1,000 loops=1)

5. 12.211 43.193 ↑ 14.5 1,000 1

Sort (cost=1,420.39..1,456.63 rows=14,498 width=73) (actual time=43.086..43.193 rows=1,000 loops=1)

  • Sort Key: loanpartstatushistory_1.creditfk, loanpartstatushistory_1.creditstatuscd, loanpartstatushistory_1.statuschangedate, loanpartstatushistory_1.pkey
  • Sort Method: quicksort Memory: 1946kB
6. 5.994 30.982 ↓ 1.0 14,506 1

Hash Join (cost=4.25..418.32 rows=14,498 width=73) (actual time=0.07..30.982 rows=14,506 loops=1)

7. 21.898 24.965 ↓ 1.0 14,506 1

Hash Join (cost=2.19..373.71 rows=14,498 width=51) (actual time=0.041..24.965 rows=14,506 loops=1)

8. 3.039 3.039 ↓ 1.0 14,506 1

Seq Scan on creditstatushistory loanpartstatushistory_1 (cost=0..324.98 rows=14,498 width=36) (actual time=0.006..3.039 rows=14,506 loops=1)

9. 0.010 0.028 ↑ 1.0 19 1

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

10. 0.018 0.018 ↑ 1.0 19 1

Seq Scan on par_creditstatuscaption loanpartstatus_1 (cost=0..1.95 rows=19 width=19) (actual time=0.006..0.018 rows=19 loops=1)

  • Filter: (languageid = 4)
11. 0.007 0.023 ↑ 1.0 17 1

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

12. 0.016 0.016 ↑ 1.0 17 1

Seq Scan on par_creditsubstatuscaption loanpartsubstatus (cost=0..1.85 rows=17 width=26) (actual time=0.006..0.016 rows=17 loops=1)

  • Filter: (languageid = 4)
13.          

SubPlan (forWindowAgg)

14. 1.000 2.000 ↑ 1.0 1 1,000

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

15. 1.000 1.000 ↑ 1.0 2 1,000

Values Scan (cost=0..0.03 rows=2 width=8) (actual time=0..0.001 rows=2 loops=1,000)

16. 286.358 51,885.136 ↓ 873.0 873 1

Nested Loop (cost=47,101.4..54,711.84 rows=1 width=4) (actual time=48,095.479..51,885.136 rows=873 loops=1)

17. 2.474 47,967.098 ↓ 873.0 873 1

Merge Join (cost=39,771.36..46,812.88 rows=1 width=8) (actual time=47,951.128..47,967.098 rows=873 loops=1)

18. 7.803 13.441 ↑ 7.1 14,065 1

Group (cost=0.29..5,799.92 rows=99,350 width=40) (actual time=0.017..13.441 rows=14,065 loops=1)

19. 5.638 5.638 ↑ 7.1 14,065 1

Index Scan using productcopy_pkey on productcopy pc (cost=0.29..5,551.54 rows=99,350 width=8) (actual time=0.015..5.638 rows=14,065 loops=1)

20. 0.525 47,951.183 ↓ 873.0 873 1

Sort (cost=39,771.07..39,771.07 rows=1 width=12) (actual time=47,950.99..47,951.183 rows=873 loops=1)

  • Sort Key: c.productcopyfk
  • Sort Method: quicksort Memory: 65kB
21. 34.215 47,950.658 ↓ 873.0 873 1

Hash Join (cost=36,575.32..39,771.06 rows=1 width=12) (actual time=47,895.172..47,950.658 rows=873 loops=1)

22. 21.510 21.510 ↑ 1.0 99,347 1

Seq Scan on creditbalances cb (cost=0..2,822.98 rows=99,398 width=4) (actual time=0.155..21.51 rows=99,347 loops=1)

23. 2.249 47,894.933 ↓ 873.0 873 1

Hash (cost=36,575.31..36,575.31 rows=1 width=16) (actual time=47,894.933..47,894.933 rows=873 loops=1)

24. 3,464.256 47,892.684 ↓ 873.0 873 1

Nested Loop (cost=35,660.65..36,575.31 rows=1 width=16) (actual time=796.808..47,892.684 rows=873 loops=1)

25. 13.498 307.881 ↓ 873.0 873 1

Nested Loop (cost=9,734.9..9,783.5 rows=1 width=20) (actual time=238.625..307.881 rows=873 loops=1)

26. 2.933 287.399 ↓ 873.0 873 1

Nested Loop (cost=9,734.61..9,783.12 rows=1 width=16) (actual time=238.613..287.399 rows=873 loops=1)

27. 6.655 269.625 ↓ 873.0 873 1

Nested Loop (cost=9,734.61..9,743.06 rows=1 width=20) (actual time=238.6..269.625 rows=873 loops=1)

28. 5.624 249.002 ↓ 873.0 873 1

Merge Join (cost=9,734.32..9,736.8 rows=1 width=16) (actual time=238.569..249.002 rows=873 loops=1)

29. 0.765 50.669 ↓ 174.6 873 1

Sort (cost=22.56..22.57 rows=5 width=8) (actual time=50.127..50.669 rows=873 loops=1)

  • Sort Key: loanpartstatushistory.loanpartentityid
  • Sort Method: quicksort Memory: 65kB
30. 49.904 49.904 ↓ 174.6 873 1

CTE Scan on loanpartstatushistory loanpartstatushistory (cost=0..22.5 rows=5 width=8) (actual time=43.117..49.904 rows=873 loops=1)

  • Filter: (rn = 1)
31. 91.660 192.709 ↓ 29.8 14,591 1

Sort (cost=9,711.76..9,712.98 rows=489 width=8) (actual time=188.4..192.709 rows=14,591 loops=1)

  • Sort Key: ar.creditfk
  • Sort Method: external sort Disk: 2144kB
32. 101.049 101.049 ↓ 203.2 99,347 1

Seq Scan on adminrole ar (cost=0..9,689.92 rows=489 width=8) (actual time=0.145..101.049 rows=99,347 loops=1)

  • Filter: ((adminroletypecd = 2) AND ((activerole)::integer = 1))
33. 13.968 13.968 ↑ 1.0 1 873

Index Scan using credit_pkey on credit c (cost=0.29..6.25 rows=1 width=16) (actual time=0.016..0.016 rows=1 loops=873)

  • Index Cond: (pkey = ar.creditfk)
34. 14.841 14.841 ↑ 449.0 1 873

Seq Scan on par_creditprovidernamecaption pcpn (cost=0..34.45 rows=449 width=4) (actual time=0.017..0.017 rows=1 loops=873)

  • Filter: (languageid = 4)
35. 6.984 6.984 ↑ 1.0 1 873

Index Only Scan using creditdossier_pkey on creditdossier cd (cost=0.29..0.38 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=873)

  • Index Cond: (pkey = c.creditdossierfk)
36. 5,437.917 44,120.547 ↓ 344.2 31,325 873

Subquery Scan on f3red (cost=25,925.75..26,790.67 rows=91 width=4) (actual time=0.583..50.539 rows=31,325 loops=873)

  • Filter: (f3red.rn = 1)
37. 33,066.621 38,682.630 ↓ 2.1 38,394 873

WindowAgg (cost=25,925.75..26,563.06 rows=18,209 width=749) (actual time=0.583..44.31 rows=38,394 loops=873)

38. 5,131.932 5,616.009 ↓ 2.1 38,394 873

Sort (cost=25,925.75..25,971.27 rows=18,209 width=16) (actual time=0.581..6.433 rows=38,394 loops=873)

  • Sort Key: c_1.creditdossierfk, (CASE WHEN (ad.isactive IS NULL) THEN 2 WHEN ((ad.isactive)::integer = 1) THEN 1 WHEN ((ad.isactive)::integer = 0) THEN 2 ELSE 3 END), w2a.rank, a.pkey
  • Sort Method: quicksort Memory: 3166kB
39. 46.835 484.077 ↓ 2.1 38,394 1

Hash Join (cost=17,773.27..24,637.24 rows=18,209 width=16) (actual time=436.902..484.077 rows=38,394 loops=1)

40. 79.411 79.411 ↓ 186.0 189,381 1

Seq Scan on address ad (cost=0..6,677.26 rows=1,018 width=9) (actual time=0.147..79.411 rows=189,381 loops=1)

  • Filter: ((isactive)::integer = 1)
41. 13.287 357.831 ↓ 2.1 38,394 1

Hash (cost=17,545.66..17,545.66 rows=18,209 width=16) (actual time=357.831..357.831 rows=38,394 loops=1)

42. 32.279 344.544 ↓ 2.1 38,394 1

Hash Join (cost=14,472.09..17,545.66 rows=18,209 width=16) (actual time=296.486..344.544 rows=38,394 loops=1)

43. 16.042 16.042 ↓ 1.0 92,434 1

Seq Scan on creditdossier cd_1 (cost=0..2,429.32 rows=92,432 width=4) (actual time=0.123..16.042 rows=92,434 loops=1)

44. 14.934 296.223 ↓ 2.1 38,394 1

Hash (cost=14,244.48..14,244.48 rows=18,209 width=16) (actual time=296.223..296.223 rows=38,394 loops=1)

45. 60.591 281.289 ↓ 2.1 38,394 1

Hash Join (cost=7,699.87..14,244.48 rows=18,209 width=16) (actual time=196.879..281.289 rows=38,394 loops=1)

46. 24.086 24.086 ↑ 1.0 99,346 1

Seq Scan on credit c_1 (cost=0..5,989.83 rows=99,383 width=8) (actual time=0.127..24.086 rows=99,346 loops=1)

47. 13.340 196.612 ↓ 2.1 38,394 1

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

48. 31.356 183.272 ↓ 2.1 38,394 1

Hash Join (cost=5,779.35..7,472.26 rows=18,209 width=16) (actual time=141.315..183.272 rows=38,394 loops=1)

49. 10.817 10.817 ↓ 1.0 68,208 1

Seq Scan on collateral2credit c2c (cost=0..1,255.05 rows=68,205 width=8) (actual time=0.142..10.817 rows=68,208 loops=1)

50. 11.083 141.099 ↓ 1.9 31,464 1

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

51. 16.308 130.016 ↓ 1.9 31,464 1

Hash Join (cost=4,499.41..5,576.04 rows=16,265 width=20) (actual time=83.79..130.016 rows=31,464 loops=1)

52. 15.418 89.891 ↓ 1.9 31,464 1

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

53. 14.840 14.840 ↓ 1.0 31,464 1

Seq Scan on warranty2assetstatushistory w2ash (cost=0..753.29 rows=31,463 width=4) (actual time=0.141..14.84 rows=31,464 loops=1)

  • Filter: (warranty2assetstatuscd = 1)
54. 10.920 59.633 ↓ 1.9 31,464 1

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

55. 16.517 48.713 ↓ 1.9 31,464 1

Hash Join (cost=2,316.26..3,039.49 rows=16,265 width=20) (actual time=27.583..48.713 rows=31,464 loops=1)

56. 4.888 4.888 ↓ 1.0 31,464 1

Seq Scan on warranty2asset w2a (cost=0..640.63 rows=31,463 width=16) (actual time=0.141..4.888 rows=31,464 loops=1)

57. 8.089 27.308 ↑ 1.0 31,325 1

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

58. 19.219 19.219 ↑ 1.0 31,325 1

Seq Scan on collateral col (cost=0..1,922.56 rows=31,496 width=4) (actual time=0.14..19.219 rows=31,325 loops=1)

  • Filter: ((classname)::text = 'Warranty'::text)
59. 9.287 23.817 ↓ 1.0 31,465 1

Hash (cost=863.3..863.3 rows=31,464 width=8) (actual time=23.817..23.817 rows=31,465 loops=1)

60. 14.530 14.530 ↓ 1.0 31,465 1

Seq Scan on asset a (cost=0..863.3 rows=31,464 width=8) (actual time=0.138..14.53 rows=31,465 loops=1)

  • Filter: ((classname)::text = 'RealEstate'::text)
61. 476.658 3,631.680 ↓ 32.0 2,788 873

Subquery Scan on dd (cost=7,330.04..7,897.88 rows=87 width=4) (actual time=0.162..4.16 rows=2,788 loops=873)

  • Filter: (dd.rn = 1)
62. 2,688.840 3,155.022 ↑ 5.2 3,390 873

WindowAgg (cost=7,330.04..7,679.48 rows=17,472 width=648) (actual time=0.162..3.614 rows=3,390 loops=873)

63. 327.489 466.182 ↑ 5.2 3,390 873

Sort (cost=7,330.04..7,373.72 rows=17,472 width=8) (actual time=0.161..0.534 rows=3,390 loops=873)

  • 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: 255kB
64. 2.220 138.693 ↑ 5.2 3,390 1

Merge Join (cost=5,829.99..6,098.9 rows=17,472 width=8) (actual time=135.864..138.693 rows=3,390 loops=1)

65. 1.602 107.630 ↓ 2.5 3,390 1

Sort (cost=4,751.52..4,754.93 rows=1,365 width=8) (actual time=107.312..107.63 rows=3,390 loops=1)

  • Sort Key: ds.dunningdossierfk
  • Sort Method: quicksort Memory: 255kB
66. 11.357 106.028 ↓ 2.5 3,390 1

Hash Join (cost=4,136.72..4,680.44 rows=1,365 width=8) (actual time=83.399..106.028 rows=3,390 loops=1)

67. 12.759 12.759 ↓ 2.5 3,390 1

Foreign Scan (cost=100..150.95 rows=1,365 width=36) (actual time=0.772..12.759 rows=3,390 loops=1)

68. 34.900 81.912 ↓ 1.0 92,434 1

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

69. 47.012 47.012 ↓ 1.0 92,434 1

Seq Scan on creditdossier cd_1_1 (cost=0..2,429.32 rows=92,432 width=11) (actual time=0.123..47.012 rows=92,434 loops=1)

70. 1.674 28.843 ↓ 1.3 3,390 1

Sort (cost=1,078.47..1,084.87 rows=2,560 width=8) (actual time=28.546..28.843 rows=3,390 loops=1)

  • Sort Key: dd_1.pkey
  • Sort Method: quicksort Memory: 255kB
71. 1.193 27.169 ↓ 1.3 3,378 1

Hash Join (cost=734.62..933.55 rows=2,560 width=8) (actual time=11.343..27.169 rows=3,378 loops=1)

72. 1.209 25.581 ↓ 1.3 3,378 1

Hash Join (cost=587.57..771.78 rows=2,560 width=12) (actual time=10.932..25.581 rows=3,378 loops=1)

73. 1.506 23.971 ↓ 1.3 3,378 1

Hash Join (cost=440.52..610.01 rows=2,560 width=12) (actual time=10.52..23.971 rows=3,378 loops=1)

74. 12.945 12.945 ↓ 1.3 3,378 1

Foreign Scan (cost=100..186.8 rows=2,560 width=8) (actual time=0.981..12.945 rows=3,378 loops=1)

75. 0.008 9.520 ↑ 171.0 1 1

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

76. 0.220 9.512 ↑ 171.0 1 1

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

77. 8.675 8.675 ↑ 2.3 996 1

Foreign Scan (cost=100..178.25 rows=2,275 width=12) (actual time=0.476..8.675 rows=996 loops=1)

78. 0.004 0.617 ↑ 15.0 1 1

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

79. 0.613 0.613 ↑ 15.0 1 1

Foreign Scan (cost=100..146.86 rows=15 width=4) (actual time=0.612..0.613 rows=1 loops=1)

80. 0.012 0.401 ↓ 1.5 23 1

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

81. 0.389 0.389 ↓ 1.5 23 1

Foreign Scan (cost=100..146.86 rows=15 width=4) (actual time=0.385..0.389 rows=23 loops=1)

82. 0.012 0.395 ↓ 1.5 23 1

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

83. 0.383 0.383 ↓ 1.5 23 1

Foreign Scan (cost=100..146.86 rows=15 width=4) (actual time=0.379..0.383 rows=23 loops=1)