explain.depesz.com

PostgreSQL's explain analyze made readable

Result: vSzi

Settings
# exclusive inclusive rows x rows loops node
1. 19,201.872 19,201.872 ↓ 16.5 72,634 1

CTE Scan on whcf (cost=315,203.71..315,291.51 rows=4,390 width=1,168) (actual time=15,457.113..19,201.872 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. 78.417 413.173 ↓ 1.3 87,631 1

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

  • Group Key: lo.id
6. 78.801 334.756 ↓ 1.3 87,631 1

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

7. 43.249 80.693 ↓ 1.3 87,631 1

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

8. 0.005 0.005 ↑ 1.0 1 1

HashAggregate (cost=0.02..0.03 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=1)

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

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

10. 31.999 37.439 ↓ 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.875..37.439 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.440 5.440 ↓ 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.440..5.440 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 whcf

14. 3,418.702 19,062.681 ↓ 16.5 72,634 1

GroupAggregate (cost=212,069.92..214,045.42 rows=4,390 width=44) (actual time=15,457.109..19,062.681 rows=72,634 loops=1)

  • Group Key: cf.loan_id, cf.recipient_entity_id
15.          

Initplan (for GroupAggregate)

16. 0.000 0.000 ↑ 1.0 1 1

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

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

18. 0.001 0.001 ↑ 1.0 1 1

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

19. 0.001 0.001 ↑ 1.0 1 1

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

20. 0.002 0.002 ↑ 1.0 1 1

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

21. 0.002 0.002 ↑ 1.0 1 1

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

22. 0.001 0.001 ↑ 1.0 1 1

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

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

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

25. 0.000 0.000 ↑ 1.0 1 1

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

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

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

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

29. 0.000 0.000 ↑ 1.0 1 1

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

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

31. 0.002 0.002 ↑ 1.0 1 1

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

32. 0.001 0.001 ↑ 1.0 1 1

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

33. 0.002 0.002 ↑ 1.0 1 1

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

34. 0.002 0.002 ↑ 1.0 1 1

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

35. 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.001..0.001 rows=1 loops=1)

36. 0.000 0.000 ↑ 1.0 1 1

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

37. 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.001..0.001 rows=1 loops=1)

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

39. 0.002 0.002 ↑ 1.0 1 1

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

40. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on constant constant_25 (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_26 (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_27 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

43. 0.000 0.000 ↑ 1.0 1 1

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

44. 0.002 0.002 ↑ 1.0 1 1

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

45. 0.001 0.001 ↑ 1.0 1 1

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

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

47. 0.002 0.002 ↑ 1.0 1 1

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

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

49. 0.001 0.001 ↑ 1.0 1 1

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

50. 0.000 0.000 ↑ 1.0 1 1

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

51. 0.000 0.000 ↑ 1.0 1 1

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

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

53. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on constant constant_38 (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_39 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

55. 0.000 0.000 ↑ 1.0 1 1

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

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

57. 0.001 0.001 ↑ 1.0 1 1

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

58. 0.001 0.001 ↑ 1.0 1 1

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

59. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_44 (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_45 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

61. 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.002..0.002 rows=1 loops=1)

62. 0.001 0.001 ↑ 1.0 1 1

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

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

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

65. 0.002 0.002 ↑ 1.0 1 1

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

66. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on constant constant_51 (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_52 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

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

69. 0.003 0.003 ↑ 1.0 1 1

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

70. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_55 (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_57 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

72. 591.938 15,643.917 ↓ 65.4 287,153 1

Sort (cost=212,068.80..212,079.78 rows=4,390 width=44) (actual time=15,456.846..15,643.917 rows=287,153 loops=1)

  • Sort Key: cf.loan_id, cf.recipient_entity_id
  • Sort Method: external merge Disk: 16656kB
73. 275.454 15,051.979 ↓ 65.4 287,153 1

Nested Loop (cost=1,482.73..211,803.21 rows=4,390 width=44) (actual time=500.066..15,051.979 rows=287,153 loops=1)

74. 379.975 14,202.219 ↓ 21.7 287,153 1

Nested Loop (cost=1,482.44..207,435.03 rows=13,221 width=44) (actual time=500.057..14,202.219 rows=287,153 loops=1)

75. 197.391 12,673.632 ↓ 3.5 287,153 1

Hash Semi Join (cost=1,482.00..140,476.46 rows=81,120 width=39) (actual time=500.051..12,673.632 rows=287,153 loops=1)

  • Hash Cond: (cf.recipient_entity_id = constant_58.spv_entity_id)
76. 281.279 12,476.235 ↑ 8.4 326,737 1

Nested Loop (cost=1,481.97..132,333.99 rows=2,758,088 width=39) (actual time=500.036..12,476.235 rows=326,737 loops=1)

77. 75.814 540.033 ↓ 438.2 87,631 1

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

  • Group Key: aot_was_in_spv.asset_loan_id
78. 464.219 464.219 ↓ 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=376.745..464.219 rows=87,631 loops=1)

79. 1,105.857 11,654.923 ↑ 4.5 4 87,631

Index Scan using nc_asset_loan_cashflow_loan_id on loan_cashflow cf (cost=0.43..654.07 rows=18 width=39) (actual time=0.023..0.133 rows=4 loops=87,631)

  • Index Cond: (loan_id = aot_was_in_spv.asset_loan_id)
  • Filter: (SubPlan 59)
  • Rows Removed by Filter: 3
80.          

SubPlan (for Index Scan)

81. 426.866 10,549.066 ↑ 1.0 1 555,214

Nested Loop (cost=0.75..16.97 rows=1 width=0) (actual time=0.019..0.019 rows=1 loops=555,214)

82.          

Initplan (for Nested Loop)

83. 0.002 0.002 ↑ 1.0 1 1

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

84. 9,438.638 9,438.638 ↑ 1.0 1 555,214

Index Scan using fki_loan_cashflow_report_item_2_loan_cashflow_id on loan_cashflow_report_item lcri (cost=0.44..8.63 rows=1 width=4) (actual time=0.016..0.017 rows=1 loops=555,214)

  • Index Cond: (cf.id = loan_cashflow_id)
  • Filter: (type = 'WAREHOUSE_DISBURSEMENT'::text)
  • Rows Removed by Filter: 1
85. 683.560 683.560 ↑ 1.0 1 341,780

Index Scan using loan_cashflow_report_pkey on loan_cashflow_report lcr (cost=0.29..8.32 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=341,780)

  • Index Cond: (id = lcri.loan_cashflow_report_id)
  • Filter: ((status = ANY ('{TRANSFER_CONFIRMED_PENDING_COMPLETION,COMPLETE}'::text[])) AND (cash_transfer_date <= $59))
  • Rows Removed by Filter: 0
86. 0.002 0.006 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
87. 0.004 0.004 ↑ 1.0 1 1

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

88. 1,148.612 1,148.612 ↑ 1.0 1 287,153

Index Scan using fki_loan_cashflow_report_item_2_loan_cashflow_id on loan_cashflow_report_item lcri_1 (cost=0.44..0.82 rows=1 width=13) (actual time=0.002..0.004 rows=1 loops=287,153)

  • Index Cond: (loan_cashflow_id = cf.id)
  • Filter: (type = 'WAREHOUSE_DISBURSEMENT'::text)
  • Rows Removed by Filter: 1
89. 574.306 574.306 ↑ 1.0 1 287,153

Index Scan using loan_cashflow_report_pkey on loan_cashflow_report lcr_1 (cost=0.29..0.32 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=287,153)

  • Index Cond: (id = lcri_1.loan_cashflow_report_id)
  • Filter: ((status = ANY ('{TRANSFER_CONFIRMED_PENDING_COMPLETION,COMPLETE}'::text[])) AND (cash_transfer_date <= $62))
Planning time : 2.180 ms
Execution time : 19,223.519 ms