explain.depesz.com

PostgreSQL's explain analyze made readable

Result: g0O

Settings
# exclusive inclusive rows x rows loops node
1. 1,095.796 1,095.796 ↓ 10.7 1,045 1

CTE Scan on whcf_adj (cost=368,974.48..368,976.44 rows=98 width=1,036) (actual time=1,092.656..1,095.796 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. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=100,499.81..101,158.27 rows=65,846 width=4) (never executed)

  • Group Key: lo.id
6. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1,630.08..100,335.20 rows=65,846 width=4) (never executed)

7. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1,629.65..66,708.94 rows=66,962 width=4) (never executed)

8. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=0.02..0.03 rows=1 width=4) (never executed)

  • Group Key: constant.owner_entity_id
9. 0.000 0.000 ↓ 0.0 0

CTE Scan on constant (cost=0.00..0.02 rows=1 width=4) (never executed)

10. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on asset_ownership_transfer aot (cost=1,629.63..66,039.29 rows=66,962 width=8) (never executed)

  • Recheck Cond: (to_entity_id = constant.owner_entity_id)
  • Filter: ((ownership_end_date IS NULL) OR (to_entity_id <> 504))
11. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on nc_asset_asset_ownership_transfer_to_entity_id (cost=0.00..1,612.89 rows=79,528 width=0) (never executed)

  • Index Cond: (to_entity_id = constant.owner_entity_id)
12. 0.000 0.000 ↓ 0.0 0

Index Scan using unc_asset_loan_asset_id on loan lo (cost=0.42..0.49 rows=1 width=8) (never executed)

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

CTE whcf_adj

14. 32.198 1,094.607 ↓ 10.7 1,045 1

HashAggregate (cost=267,807.38..267,816.20 rows=98 width=39) (actual time=1,092.651..1,094.607 rows=1,045 loops=1)

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

Initplan (for HashAggregate)

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.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.002 0.002 ↑ 1.0 1 1

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

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

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

35. 0.000 0.000 ↑ 1.0 1 1

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

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

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

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

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.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.003 0.003 ↑ 1.0 1 1

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

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

65. 2.077 1,062.355 ↓ 18.9 1,855 1

Nested Loop Anti Join (cost=6,734.66..267,776.26 rows=98 width=39) (actual time=483.532..1,062.355 rows=1,855 loops=1)

66. 1.228 613.223 ↓ 2.9 1,855 1

Nested Loop (cost=6,734.22..265,899.38 rows=637 width=43) (actual time=481.388..613.223 rows=1,855 loops=1)

67. 0.005 0.008 ↑ 1.0 1 1

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

  • Group Key: constant_50.spv_entity_id
68. 0.003 0.003 ↑ 1.0 1 1

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

69. 135.077 611.987 ↓ 2.9 1,855 1

Bitmap Heap Scan on loan_cashflow cf (cost=6,734.20..265,892.98 rows=637 width=43) (actual time=481.378..611.987 rows=1,855 loops=1)

  • Recheck Cond: (recipient_entity_id = constant_50.spv_entity_id)
  • Filter: ((cashflow_date <= $52) AND ((cashflow_type)::text = ANY ('{CREDIT,CREDIT_REVERSAL}'::text[])))
  • Rows Removed by Filter: 317052
  • Heap Blocks: exact=24876
70. 476.910 476.910 ↑ 1.0 318,918 1

Bitmap Index Scan on nc_asset_loan_cashflow_recipient_entity_id (cost=0.00..6,734.04 rows=324,481 width=0) (actual time=476.910..476.910 rows=318,918 loops=1)

  • Index Cond: (recipient_entity_id = constant_50.spv_entity_id)
71. 447.055 447.055 ↓ 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.241..0.241 rows=0 loops=1,855)

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