explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MiWR

Settings
# exclusive inclusive rows x rows loops node
1. 8,751.134 8,751.134 ↓ 8.3 72,634 1

CTE Scan on whcf (cost=242,402.00..242,577.62 rows=8,781 width=1,168) (actual time=4,991.499..8,751.134 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.001..0.002 rows=1 loops=1)

4.          

CTE aot_was_in_spv

5. 79.393 415.992 ↓ 1.3 87,631 1

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

  • Group Key: lo.id
6. 78.910 336.599 ↓ 1.3 87,631 1

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

7. 44.105 82.427 ↓ 1.3 87,631 1

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

8. 0.005 0.006 ↑ 1.0 1 1

HashAggregate (cost=0.02..0.03 rows=1 width=4) (actual time=0.004..0.006 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. 32.091 38.316 ↓ 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=6.663..38.316 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. 6.225 6.225 ↓ 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=6.225..6.225 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,442.545 8,613.834 ↓ 8.3 72,634 1

GroupAggregate (cost=137,292.27..141,243.72 rows=8,781 width=44) (actual time=4,991.494..8,613.834 rows=72,634 loops=1)

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

Initplan (for GroupAggregate)

16. 0.002 0.002 ↑ 1.0 1 1

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

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

21. 0.000 0.000 ↑ 1.0 1 1

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

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

23. 0.000 0.000 ↑ 1.0 1 1

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

24. 0.002 0.002 ↑ 1.0 1 1

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

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

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

CTE Scan on constant constant_18 (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_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.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.002 0.002 ↑ 1.0 1 1

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

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

44. 0.001 0.001 ↑ 1.0 1 1

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

48. 0.000 0.000 ↑ 1.0 1 1

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

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

52. 0.002 0.002 ↑ 1.0 1 1

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

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

54. 0.000 0.000 ↑ 1.0 1 1

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

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

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

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

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

68. 0.002 0.002 ↑ 1.0 1 1

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

72. 533.580 5,171.234 ↓ 32.7 287,153 1

Sort (cost=137,291.15..137,313.10 rows=8,781 width=44) (actual time=4,991.229..5,171.234 rows=287,153 loops=1)

  • Sort Key: cf.loan_id, cf.recipient_entity_id
  • Sort Method: external merge Disk: 16656kB
73. 200.211 4,637.654 ↓ 32.7 287,153 1

Hash Join (cost=5,037.63..136,715.98 rows=8,781 width=44) (actual time=566.536..4,637.654 rows=287,153 loops=1)

  • Hash Cond: (lcri.loan_cashflow_report_id = lcr.id)
74. 369.926 4,377.588 ↓ 11.4 302,196 1

Nested Loop (cost=1,482.44..132,973.83 rows=26,442 width=44) (actual time=506.627..4,377.588 rows=302,196 loops=1)

75. 238.721 2,094.220 ↓ 2.0 318,907 1

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

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

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

77. 73.823 541.715 ↓ 438.2 87,631 1

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

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

79. 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=39) (actual time=0.004..0.010 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. 1,913.442 1,913.442 ↑ 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.004..0.006 rows=1 loops=318,907)

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

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

  • Buckets: 131072 (originally 32768) Batches: 1 (originally 1) Memory Usage: 3756kB
84. 37.467 37.467 ↓ 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.014..37.467 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.958 ms
Execution time : 8,771.992 ms