explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yI5v

Settings
# exclusive inclusive rows x rows loops node
1. 22,925.455 22,925.455 ↓ 7.0 72,634 1

CTE Scan on whcf (cost=892,263.54..892,471.08 rows=10,377 width=1,168) (actual time=19,421.179..22,925.455 rows=72,634 loops=1)

2.          

CTE constant

3. 0.001 0.001 ↑ 1.0 1 1

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

4.          

CTE aot_was_in_spv

5. 80.208 419.663 ↓ 1.3 87,631 1

HashAggregate (cost=100,499.81..101,158.27 rows=65,846 width=4) (actual time=381.776..419.663 rows=87,631 loops=1)

  • Group Key: lo.id
6. 83.949 339.455 ↓ 1.3 87,631 1

Nested Loop (cost=1,630.08..100,335.20 rows=65,846 width=4) (actual time=5.836..339.455 rows=87,631 loops=1)

7. 43.277 80.244 ↓ 1.3 87,631 1

Nested Loop (cost=1,629.65..66,708.94 rows=66,962 width=4) (actual time=5.818..80.244 rows=87,631 loops=1)

8. 0.007 0.008 ↑ 1.0 1 1

HashAggregate (cost=0.02..0.03 rows=1 width=4) (actual time=0.006..0.008 rows=1 loops=1)

  • Group Key: constant.owner_entity_id
9. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on constant (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

10. 31.593 36.959 ↓ 1.3 87,631 1

Bitmap Heap Scan on asset_ownership_transfer aot (cost=1,629.63..66,039.29 rows=66,962 width=8) (actual time=5.805..36.959 rows=87,631 loops=1)

  • Recheck Cond: (to_entity_id = constant.owner_entity_id)
  • Filter: ((ownership_end_date IS NULL) OR (to_entity_id <> 504))
  • Heap Blocks: exact=3341
11. 5.366 5.366 ↓ 1.1 87,631 1

Bitmap Index Scan on nc_asset_asset_ownership_transfer_to_entity_id (cost=0.00..1,612.89 rows=79,528 width=0) (actual time=5.366..5.366 rows=87,631 loops=1)

  • Index Cond: (to_entity_id = constant.owner_entity_id)
12. 175.262 175.262 ↑ 1.0 1 87,631

Index Scan using unc_asset_loan_asset_id on loan lo (cost=0.42..0.49 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=87,631)

  • Index Cond: (asset_id = aot.asset_id)
  • Filter: (originator_id = 1003)
13.          

CTE spv_cashflow

14. 245.189 2,043.373 ↓ 2.0 318,907 1

Hash Semi Join (cost=1,482.00..26,469.25 rows=162,240 width=233) (actual time=507.322..2,043.373 rows=318,907 loops=1)

  • Hash Cond: (cf.recipient_entity_id = constant_1.spv_entity_id)
15. 381.751 1,798.179 ↑ 9.9 555,214 1

Nested Loop (cost=1,481.97..10,184.34 rows=5,516,175 width=233) (actual time=507.298..1,798.179 rows=555,214 loops=1)

16. 69.508 540.118 ↓ 438.2 87,631 1

HashAggregate (cost=1,481.54..1,483.54 rows=200 width=4) (actual time=507.271..540.118 rows=87,631 loops=1)

  • Group Key: aot_was_in_spv.asset_loan_id
17. 470.610 470.610 ↓ 1.3 87,631 1

CTE Scan on aot_was_in_spv (cost=0.00..1,316.92 rows=65,846 width=4) (actual time=381.778..470.610 rows=87,631 loops=1)

18. 876.310 876.310 ↑ 6.0 6 87,631

Index Scan using nc_asset_loan_cashflow_loan_id on loan_cashflow cf (cost=0.43..43.14 rows=36 width=233) (actual time=0.004..0.010 rows=6 loops=87,631)

  • Index Cond: (loan_id = aot_was_in_spv.asset_loan_id)
19. 0.002 0.005 ↑ 1.0 1 1

Hash (cost=0.02..0.02 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.003 0.003 ↑ 1.0 1 1

CTE Scan on constant constant_1 (cost=0.00..0.02 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=1)

21.          

CTE whcf

22. 3,199.973 22,791.705 ↓ 7.0 72,634 1

GroupAggregate (cost=759,966.35..764,636.00 rows=10,377 width=158) (actual time=19,421.175..22,791.705 rows=72,634 loops=1)

  • Group Key: cf_1.loan_id, cf_1.recipient_entity_id
23.          

Initplan (for GroupAggregate)

24. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_2 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

25. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on constant constant_3 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

26. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on constant constant_4 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

27. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_5 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

28. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_6 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

29. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on constant constant_7 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1)

30. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on constant constant_8 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1)

31. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_9 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

32. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on constant constant_10 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1)

33. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_11 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

34. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_12 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

35. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_13 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

36. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_14 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

37. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_15 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

38. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_16 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

39. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on constant constant_17 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

40. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on constant constant_18 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

41. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_19 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

42. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_20 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.001 rows=1 loops=1)

43. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on constant constant_21 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1)

44. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_22 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.001 rows=1 loops=1)

45. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_23 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

46. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_24 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

47. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on constant constant_25 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1)

48. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on constant constant_26 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

49. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on constant constant_27 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

50. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on constant constant_28 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1)

51. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on constant constant_29 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

52. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on constant constant_30 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

53. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on constant constant_31 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1)

54. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_32 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

55. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_33 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

56. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on constant constant_34 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

57. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_35 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

58. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on constant constant_36 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1)

59. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_37 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

60. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_38 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

61. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_39 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

62. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_40 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

63. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on constant constant_41 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1)

64. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on constant constant_42 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

65. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on constant constant_43 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

66. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on constant constant_44 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

67. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_45 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

68. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on constant constant_46 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1)

69. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on constant constant_47 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1)

70. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_48 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

71. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_49 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

72. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_50 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

73. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on constant constant_51 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1)

74. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on constant constant_52 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1)

75. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_53 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

76. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on constant constant_54 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=1)

77. 0.004 0.004 ↑ 1.0 1 1

CTE Scan on constant constant_55 (cost=0.00..0.02 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=1)

78. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_56 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

79. 0.005 0.005 ↑ 1.0 1 1

CTE Scan on constant constant_57 (cost=0.00..0.02 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=1)

80. 490.910 19,591.669 ↓ 27.7 287,153 1

Sort (cost=759,965.23..759,991.18 rows=10,377 width=158) (actual time=19,420.914..19,591.669 rows=287,153 loops=1)

  • Sort Key: cf_1.loan_id, cf_1.recipient_entity_id
  • Sort Method: external merge Disk: 16648kB
81. 643.034 19,100.759 ↓ 27.7 287,153 1

Merge Join (cost=754,967.00..759,273.03 rows=10,377 width=158) (actual time=18,360.019..19,100.759 rows=287,153 loops=1)

  • Merge Cond: (lcri.loan_cashflow_id = cf_1.id)
82. 2,170.705 15,231.291 ↓ 3.0 1,803,039 1

Sort (cost=724,924.63..726,417.37 rows=597,093 width=13) (actual time=14,567.128..15,231.291 rows=1,803,039 loops=1)

  • Sort Key: lcri.loan_cashflow_id
  • Sort Method: external merge Disk: 45792kB
83. 987.929 13,060.586 ↓ 3.0 1,803,039 1

Hash Join (cost=3,555.19..657,434.74 rows=597,093 width=13) (actual time=78.649..13,060.586 rows=1,803,039 loops=1)

  • Hash Cond: (lcri.loan_cashflow_report_id = lcr.id)
84. 12,013.740 12,013.740 ↓ 1.0 1,818,102 1

Seq Scan on loan_cashflow_report_item lcri (cost=0.00..641,165.85 rows=1,798,071 width=13) (actual time=19.684..12,013.740 rows=1,818,102 loops=1)

  • Filter: (type = 'WAREHOUSE_DISBURSEMENT'::text)
  • Rows Removed by Filter: 19727845
85. 22.188 58.917 ↓ 3.0 69,929 1

Hash (cost=3,265.14..3,265.14 rows=23,204 width=8) (actual time=58.917..58.917 rows=69,929 loops=1)

  • Buckets: 131072 (originally 32768) Batches: 1 (originally 1) Memory Usage: 3756kB
86. 36.729 36.729 ↓ 3.0 69,929 1

Seq Scan on loan_cashflow_report lcr (cost=0.00..3,265.14 rows=23,204 width=8) (actual time=0.013..36.729 rows=69,929 loops=1)

  • Filter: ((status = ANY ('{TRANSFER_CONFIRMED_PENDING_COMPLETION,COMPLETE}'::text[])) AND (cash_transfer_date <= $61))
  • Rows Removed by Filter: 360
87. 137.113 3,226.434 ↓ 1.9 304,790 1

Materialize (cost=30,042.36..30,853.56 rows=162,240 width=153) (actual time=2,897.744..3,226.434 rows=304,790 loops=1)

88. 558.393 3,089.321 ↓ 1.9 304,790 1

Sort (cost=30,042.36..30,447.96 rows=162,240 width=153) (actual time=2,897.741..3,089.321 rows=304,790 loops=1)

  • Sort Key: cf_1.id
  • Sort Method: external merge Disk: 17368kB
89. 2,530.928 2,530.928 ↓ 2.0 318,907 1

CTE Scan on spv_cashflow cf_1 (cost=0.00..3,244.80 rows=162,240 width=153) (actual time=507.330..2,530.928 rows=318,907 loops=1)

Planning time : 1.629 ms
Execution time : 22,961.713 ms