explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9f9P6

Settings
# exclusive inclusive rows x rows loops node
1. 24,021.943 24,021.943 ↓ 8.3 72,634 1

CTE Scan on whcf (cost=242,402.00..242,577.62 rows=8,781 width=1,168) (actual time=20,411.842..24,021.943 rows=72,634 loops=1)

2.          

CTE constant

3. 0.002 0.002 ↑ 1.0 1 1

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

4.          

CTE aot_was_in_spv

5. 75.724 411.567 ↓ 1.3 87,631 1

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

  • Group Key: lo.id
6. 79.167 335.843 ↓ 1.3 87,631 1

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

7. 44.209 81.414 ↓ 1.3 87,631 1

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

8. 0.004 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.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.772 37.200 ↓ 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.867..37.200 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.428 5.428 ↓ 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.428..5.428 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,304.922 23,885.121 ↓ 8.3 72,634 1

GroupAggregate (cost=137,292.27..141,243.72 rows=8,781 width=44) (actual time=20,411.838..23,885.121 rows=72,634 loops=1)

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

Initplan (for GroupAggregate)

16. 0.001 0.001 ↑ 1.0 1 1

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

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.000 0.000 ↑ 1.0 1 1

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

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

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.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_8 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 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.001 0.001 ↑ 1.0 1 1

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

26. 0.000 0.000 ↑ 1.0 1 1

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

27. 0.002 0.002 ↑ 1.0 1 1

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

28. 0.002 0.002 ↑ 1.0 1 1

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

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

30. 0.000 0.000 ↑ 1.0 1 1

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

33. 0.003 0.003 ↑ 1.0 1 1

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

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

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.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_21 (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_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.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)

40. 0.001 0.001 ↑ 1.0 1 1

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

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

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

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

45. 0.002 0.002 ↑ 1.0 1 1

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

46. 0.001 0.001 ↑ 1.0 1 1

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

47. 0.000 0.000 ↑ 1.0 1 1

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

48. 0.002 0.002 ↑ 1.0 1 1

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

49. 0.002 0.002 ↑ 1.0 1 1

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

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

51. 0.001 0.001 ↑ 1.0 1 1

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

56. 0.001 0.001 ↑ 1.0 1 1

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

57. 0.002 0.002 ↑ 1.0 1 1

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

58. 0.002 0.002 ↑ 1.0 1 1

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

59. 0.002 0.002 ↑ 1.0 1 1

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

60. 0.002 0.002 ↑ 1.0 1 1

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

61. 0.001 0.001 ↑ 1.0 1 1

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

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

63. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on constant constant_48 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 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.000 0.000 ↑ 1.0 1 1

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

66. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on constant constant_51 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.001 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.002..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.003 0.003 ↑ 1.0 1 1

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

72. 538.785 20,580.134 ↓ 32.7 287,153 1

Sort (cost=137,291.15..137,313.10 rows=8,781 width=44) (actual time=20,411.577..20,580.134 rows=287,153 loops=1)

  • Sort Key: cf.loan_id, cf.recipient_entity_id
  • Sort Method: external merge Disk: 16656kB
73. 212.767 20,041.349 ↓ 32.7 287,153 1

Hash Join (cost=5,037.63..136,715.98 rows=8,781 width=44) (actual time=580.198..20,041.349 rows=287,153 loops=1)

  • Hash Cond: (lcri.loan_cashflow_report_id = lcr.id)
74. 353.204 19,749.539 ↓ 11.4 302,196 1

Nested Loop (cost=1,482.44..132,973.83 rows=26,442 width=44) (actual time=501.090..19,749.539 rows=302,196 loops=1)

75. 248.964 2,175.357 ↓ 2.0 318,907 1

Hash Semi Join (cost=1,482.00..26,469.25 rows=162,240 width=39) (actual time=500.600..2,175.357 rows=318,907 loops=1)

  • Hash Cond: (cf.recipient_entity_id = constant_57.spv_entity_id)
76. 423.593 1,926.387 ↑ 9.9 555,214 1

Nested Loop (cost=1,481.97..10,184.34 rows=5,516,175 width=39) (actual time=500.580..1,926.387 rows=555,214 loops=1)

77. 75.362 538.853 ↓ 438.2 87,631 1

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

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

79. 963.941 963.941 ↑ 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=39) (actual time=0.004..0.011 rows=6 loops=87,631)

  • Index Cond: (loan_id = aot_was_in_spv.asset_loan_id)
80. 0.001 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
81. 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)

82. 17,220.978 17,220.978 ↑ 1.0 1 318,907

Index Scan using fki_loan_cashflow_report_item_2_loan_cashflow_id on loan_cashflow_report_item lcri (cost=0.44..0.65 rows=1 width=13) (actual time=0.024..0.054 rows=1 loops=318,907)

  • Index Cond: (loan_cashflow_id = cf.id)
  • Filter: (type = 'WAREHOUSE_DISBURSEMENT'::text)
  • Rows Removed by Filter: 1
83. 23.496 79.043 ↓ 3.0 69,929 1

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

  • Buckets: 131072 (originally 32768) Batches: 1 (originally 1) Memory Usage: 3756kB
84. 55.547 55.547 ↓ 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.791..55.547 rows=69,929 loops=1)

  • Filter: ((status = ANY ('{TRANSFER_CONFIRMED_PENDING_COMPLETION,COMPLETE}'::text[])) AND (cash_transfer_date <= $59))
  • Rows Removed by Filter: 360
Planning time : 1.914 ms
Execution time : 24,042.432 ms