explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mY9Z

Settings
# exclusive inclusive rows x rows loops node
1. 1,474.928 1,474.928 ↓ 21.3 1,045 1

CTE Scan on whcf_adj (cost=112,301.05..112,302.03 rows=49 width=1,036) (actual time=1,471.586..1,474.928 rows=1,045 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.629 415.065 ↓ 1.3 87,631 1

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

  • Group Key: lo.id
6. 79.913 336.436 ↓ 1.3 87,631 1

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

7. 43.902 81.261 ↓ 1.3 87,631 1

Nested Loop (cost=1,629.65..66,708.94 rows=66,962 width=4) (actual time=5.845..81.261 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.005..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.000..0.001 rows=1 loops=1)

10. 31.951 37.353 ↓ 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.838..37.353 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.402 5.402 ↓ 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.402..5.402 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_adj

14. 34.008 1,473.668 ↓ 21.3 1,045 1

HashAggregate (cost=11,138.36..11,142.77 rows=49 width=39) (actual time=1,471.581..1,473.668 rows=1,045 loops=1)

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

Initplan (for HashAggregate)

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

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

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

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

27. 0.000 0.000 ↑ 1.0 1 1

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

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

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

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

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

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

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

65. 2.078 1,439.610 ↓ 37.9 1,855 1

Nested Loop Anti Join (cost=1,482.44..11,122.31 rows=49 width=39) (actual time=504.954..1,439.610 rows=1,855 loops=1)

66. 1.776 1,430.112 ↓ 5.8 1,855 1

Hash Semi Join (cost=1,482.00..10,182.37 rows=319 width=43) (actual time=504.942..1,430.112 rows=1,855 loops=1)

  • Hash Cond: (cf.recipient_entity_id = constant_50.spv_entity_id)
67. 14.970 1,428.330 ↑ 5.0 2,175 1

Nested Loop (cost=1,481.97..10,150.34 rows=10,836 width=43) (actual time=504.926..1,428.330 rows=2,175 loops=1)

68. 70.201 537.050 ↓ 438.2 87,631 1

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

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

70. 876.310 876.310 ↓ 0.0 0 87,631

Index Scan using nc_asset_loan_cashflow_loan_id on loan_cashflow cf (cost=0.43..43.32 rows=1 width=43) (actual time=0.009..0.010 rows=0 loops=87,631)

  • Index Cond: (loan_id = aot_was_in_spv.asset_loan_id)
  • Filter: ((cashflow_date <= $52) AND ((cashflow_type)::text = ANY ('{CREDIT,CREDIT_REVERSAL}'::text[])))
  • Rows Removed by Filter: 6
71. 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
72. 0.004 0.004 ↑ 1.0 1 1

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

73. 7.420 7.420 ↓ 0.0 0 1,855

Index Only Scan using fki_loan_cashflow_report_item_2_loan_cashflow_id on loan_cashflow_report_item lcri (cost=0.44..4.73 rows=2 width=4) (actual time=0.004..0.004 rows=0 loops=1,855)

  • Index Cond: (loan_cashflow_id = cf.id)
  • Heap Fetches: 0
Planning time : 1.550 ms
Execution time : 1,475.991 ms