explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ngKn

Settings
# exclusive inclusive rows x rows loops node
1. 4.332 34,982.620 ↓ 39.7 7,942 1

Hash Join (cost=379,065.80..379,667.69 rows=200 width=73) (actual time=34,979.544..34,982.620 rows=7,942 loops=1)

  • Hash Cond: (c."Id" = q."CellId")
2. 3.017 3.017 ↑ 1.0 18,921 1

Seq Scan on "Cell" c (cost=0.00..552.21 rows=18,921 width=57) (actual time=0.057..3.017 rows=18,921 loops=1)

3. 2.363 34,975.271 ↓ 39.7 7,942 1

Hash (cost=379,063.30..379,063.30 rows=200 width=48) (actual time=34,975.271..34,975.271 rows=7,942 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 474kB
4. 1.414 34,972.908 ↓ 39.7 7,942 1

Subquery Scan on q (cost=378,963.31..379,063.30 rows=200 width=48) (actual time=33,967.678..34,972.908 rows=7,942 loops=1)

5. 570.967 34,971.494 ↓ 39.7 7,942 1

GroupAggregate (cost=378,963.31..379,061.30 rows=200 width=48) (actual time=33,967.677..34,971.494 rows=7,942 loops=1)

  • Group Key: i."CellId
6. 2,428.192 34,400.527 ↓ 298.0 3,794,048 1

Sort (cost=378,963.31..378,995.14 rows=12,733 width=48) (actual time=33,967.658..34,400.527 rows=3,794,048 loops=1)

  • Sort Key: i."CellId
  • Sort Method: external merge Disk: 112064kB
7. 184.212 31,972.335 ↓ 298.0 3,794,048 1

Subquery Scan on i (cost=377,840.49..378,095.15 rows=12,733 width=48) (actual time=30,868.653..31,972.335 rows=3,794,048 loops=1)

8. 2,684.342 31,788.123 ↓ 298.0 3,794,048 1

HashAggregate (cost=377,840.49..377,967.82 rows=12,733 width=64) (actual time=30,868.653..31,788.123 rows=3,794,048 loops=1)

  • Group Key: c_1."Id", (NULL::uuid), (0.0000::numeric(19,4))
9. 100.823 29,103.781 ↓ 303.1 3,859,855 1

Append (cost=20.05..377,744.99 rows=12,733 width=64) (actual time=0.185..29,103.781 rows=3,859,855 loops=1)

10. 4.845 14.791 ↓ 273.9 7,942 1

Nested Loop (cost=20.05..499.48 rows=29 width=52) (actual time=0.184..14.791 rows=7,942 loops=1)

11. 2.345 3.113 ↓ 455.5 6,833 1

Hash Join (cost=19.76..490.18 rows=15 width=16) (actual time=0.168..3.113 rows=6,833 loops=1)

  • Hash Cond: (m."WorkbookId" = wb."Id")
12. 0.747 0.747 ↑ 1.0 9,842 1

Seq Scan on "Mapping" m (cost=0.00..444.42 rows=9,842 width=32) (actual time=0.011..0.747 rows=9,842 loops=1)

13. 0.005 0.021 ↑ 1.0 1 1

Hash (cost=19.75..19.75 rows=1 width=16) (actual time=0.021..0.021 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 0.016 0.016 ↑ 1.0 1 1

Seq Scan on "Workbook" wb (cost=0.00..19.75 rows=1 width=16) (actual time=0.014..0.016 rows=1 loops=1)

  • Filter: (("EntityId" = 'b787799f-4826-4f8a-9593-686999e54c9f'::uuid) AND ("Name" = 'AFS 2019.xlsx'::text))
  • Rows Removed by Filter: 2
15. 6.833 6.833 ↑ 2.0 1 6,833

Index Scan using "IX_Cell_MappingId" on "Cell" c_1 (cost=0.29..0.60 rows=2 width=32) (actual time=0.001..0.001 rows=1 loops=6,833)

  • Index Cond: ("MappingId" = m."Id")
16. 2,831.620 22,499.748 ↓ 299.6 3,070,389 1

Hash Join (cost=62,299.21..85,245.71 rows=10,249 width=64) (actual time=19,792.442..22,499.748 rows=3,070,389 loops=1)

  • Hash Cond: ((fb."TransactionId" = fti."TransactionId") AND (fb."FinancialYear" = y."FinancialYear"))
17. 100.500 100.500 ↑ 1.0 355,787 1

Seq Scan on "FlattenedBalances" fb (cost=0.00..13,975.87 rows=355,787 width=39) (actual time=0.117..100.500 rows=355,787 loops=1)

18. 2,579.466 19,567.628 ↓ 235.2 9,750,031 1

Hash (cost=61,191.36..61,191.36 rows=41,457 width=68) (actual time=19,567.628..19,567.628 rows=9,750,031 loops=1)

  • Buckets: 65536 (originally 65536) Batches: 256 (originally 2) Memory Usage: 3585kB
19. 5,733.665 16,988.162 ↓ 235.2 9,750,031 1

Hash Join (cost=7,496.01..61,191.36 rows=41,457 width=68) (actual time=11,311.727..16,988.162 rows=9,750,031 loops=1)

  • Hash Cond: (fti."ItemId" = mi."ItemMscoaId")
20. 36.871 38.895 ↑ 1.0 41,522 1

Bitmap Heap Scan on "FlattenedTransactions" fti (cost=1,163.68..53,870.94 rows=41,711 width=32) (actual time=2.627..38.895 rows=41,522 loops=1)

  • Recheck Cond: ("EntityId" = 'b787799f-4826-4f8a-9593-686999e54c9f'::uuid)
  • Heap Blocks: exact=5037
21. 2.024 2.024 ↑ 1.0 41,522 1

Bitmap Index Scan on "IX_FlattenedTransactions_EntityId" (cost=0.00..1,153.25 rows=41,711 width=0) (actual time=2.024..2.024 rows=41,522 loops=1)

  • Index Cond: ("EntityId" = 'b787799f-4826-4f8a-9593-686999e54c9f'::uuid)
22. 3,919.494 11,215.602 ↓ 422.5 13,833,566 1

Hash (cost=5,923.00..5,923.00 rows=32,746 width=68) (actual time=11,215.601..11,215.602 rows=13,833,566 loops=1)

  • Buckets: 65536 (originally 32768) Batches: 512 (originally 1) Memory Usage: 3585kB
23. 1,064.670 7,296.108 ↓ 422.5 13,833,566 1

Nested Loop (cost=21.08..5,923.00 rows=32,746 width=68) (actual time=0.213..7,296.108 rows=13,833,566 loops=1)

24. 5.865 76.388 ↓ 378.2 7,942 1

Nested Loop (cost=20.52..516.87 rows=21 width=84) (actual time=0.160..76.388 rows=7,942 loops=1)

  • Join Filter: (b."YearlyBalanceLookupId" = bt."BalanceTypeId")
  • Rows Removed by Join Filter: 10595
25. 7.089 70.523 ↓ 378.2 7,942 1

Nested Loop (cost=20.52..514.16 rows=21 width=68) (actual time=0.130..70.523 rows=7,942 loops=1)

26. 7.245 39.608 ↓ 273.9 7,942 1

Nested Loop (cost=20.24..499.87 rows=29 width=52) (actual time=0.125..39.608 rows=7,942 loops=1)

27. 2.933 11.864 ↓ 455.5 6,833 1

Hash Join (cost=19.95..490.58 rows=15 width=20) (actual time=0.117..11.864 rows=6,833 loops=1)

  • Hash Cond: (m_1."Year" = y."Year")
28. 6.673 8.922 ↓ 455.5 6,833 1

Hash Join (cost=19.76..490.18 rows=15 width=20) (actual time=0.103..8.922 rows=6,833 loops=1)

  • Hash Cond: (m_1."WorkbookId" = wb_1."Id")
29. 2.240 2.240 ↑ 1.0 9,842 1

Seq Scan on "Mapping" m_1 (cost=0.00..444.42 rows=9,842 width=36) (actual time=0.016..2.240 rows=9,842 loops=1)

30. 0.001 0.009 ↑ 1.0 1 1

Hash (cost=19.75..19.75 rows=1 width=16) (actual time=0.009..0.009 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
31. 0.008 0.008 ↑ 1.0 1 1

Seq Scan on "Workbook" wb_1 (cost=0.00..19.75 rows=1 width=16) (actual time=0.007..0.008 rows=1 loops=1)

  • Filter: (("EntityId" = 'b787799f-4826-4f8a-9593-686999e54c9f'::uuid) AND ("Name" = 'AFS 2019.xlsx'::text))
  • Rows Removed by Filter: 2
32. 0.002 0.009 ↑ 1.0 3 1

Hash (cost=0.15..0.15 rows=3 width=8) (actual time=0.009..0.009 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
33. 0.001 0.007 ↑ 1.0 3 1

Subquery Scan on y (cost=0.09..0.15 rows=3 width=8) (actual time=0.007..0.007 rows=3 loops=1)

34. 0.005 0.006 ↑ 1.0 3 1

HashAggregate (cost=0.09..0.12 rows=3 width=8) (actual time=0.006..0.006 rows=3 loops=1)

  • Group Key: (2017), (1)
35. 0.001 0.001 ↑ 1.0 3 1

Append (cost=0.00..0.07 rows=3 width=8) (actual time=0.001..0.001 rows=3 loops=1)

36. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=1)

37. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=1)

38. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=1)

39. 20.499 20.499 ↑ 2.0 1 6,833

Index Scan using "IX_Cell_MappingId" on "Cell" c_2 (cost=0.29..0.60 rows=2 width=32) (actual time=0.003..0.003 rows=1 loops=6,833)

  • Index Cond: ("MappingId" = m_1."Id")
40. 23.826 23.826 ↑ 1.0 1 7,942

Index Scan using "IX_Balance_CellId" on "Balance" b (cost=0.29..0.48 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=7,942)

  • Index Cond: ("CellId" = c_2."Id")
41. 0.000 0.000 ↑ 3.0 2 7,942

Materialize (cost=0.00..1.09 rows=6 width=48) (actual time=0.000..0.000 rows=2 loops=7,942)

42. 0.010 0.010 ↑ 1.0 6 1

Seq Scan on "LookupBalanceTypes" bt (cost=0.00..1.06 rows=6 width=48) (actual time=0.008..0.010 rows=6 loops=1)

43. 6,155.050 6,155.050 ↑ 4.3 1,742 7,942

Index Only Scan using "PK_MappingItem" on "MappingItem" mi (cost=0.56..182.12 rows=7,531 width=32) (actual time=0.015..0.775 rows=1,742 loops=7,942)

  • Index Cond: ("MappingId" = c_2."MappingId")
  • Heap Fetches: 13833566
44. 1.307 106.734 ↓ 13.8 3,134 1

Nested Loop (cost=1,711.48..56,142.22 rows=227 width=64) (actual time=80.340..106.734 rows=3,134 loops=1)

  • Join Filter: (y_1."FinancialYear" = fb_1."FinancialYear")
  • Rows Removed by Join Filter: 7819
45. 6.358 72.568 ↓ 11.9 10,953 1

Hash Join (cost=1,711.06..54,583.91 rows=918 width=68) (actual time=58.872..72.568 rows=10,953 loops=1)

  • Hash Cond: (fti_1."ProjectId" = mi_1."ProjectMscoaId")
46. 7.949 10.959 ↑ 1.0 41,522 1

Bitmap Heap Scan on "FlattenedTransactions" fti_1 (cost=1,163.68..53,870.94 rows=41,711 width=32) (actual time=3.569..10.959 rows=41,522 loops=1)

  • Recheck Cond: ("EntityId" = 'b787799f-4826-4f8a-9593-686999e54c9f'::uuid)
  • Heap Blocks: exact=5037
47. 3.010 3.010 ↑ 1.0 41,522 1

Bitmap Index Scan on "IX_FlattenedTransactions_EntityId" (cost=0.00..1,153.25 rows=41,711 width=0) (actual time=3.010..3.010 rows=41,522 loops=1)

  • Index Cond: ("EntityId" = 'b787799f-4826-4f8a-9593-686999e54c9f'::uuid)
48. 2.590 55.251 ↓ 194.7 16,550 1

Hash (cost=546.31..546.31 rows=85 width=68) (actual time=55.251..55.251 rows=16,550 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1485kB
49. 2.340 52.661 ↓ 194.7 16,550 1

Nested Loop (cost=20.94..546.31 rows=85 width=68) (actual time=32.039..52.661 rows=16,550 loops=1)

50. 2.491 34.437 ↓ 378.2 7,942 1

Nested Loop (cost=20.52..516.87 rows=21 width=84) (actual time=0.559..34.437 rows=7,942 loops=1)

  • Join Filter: (b_1."YearlyBalanceLookupId" = bt_1."BalanceTypeId")
  • Rows Removed by Join Filter: 10595
51. 5.698 31.946 ↓ 378.2 7,942 1

Nested Loop (cost=20.52..514.16 rows=21 width=68) (actual time=0.508..31.946 rows=7,942 loops=1)

52. 0.000 18.306 ↓ 273.9 7,942 1

Nested Loop (cost=20.24..499.87 rows=29 width=52) (actual time=0.495..18.306 rows=7,942 loops=1)

53. 1.244 5.824 ↓ 455.5 6,833 1

Hash Join (cost=19.95..490.58 rows=15 width=20) (actual time=0.391..5.824 rows=6,833 loops=1)

  • Hash Cond: (m_2."Year" = y_1."Year")
54. 1.827 4.566 ↓ 455.5 6,833 1

Hash Join (cost=19.76..490.18 rows=15 width=20) (actual time=0.372..4.566 rows=6,833 loops=1)

  • Hash Cond: (m_2."WorkbookId" = wb_2."Id")
55. 2.653 2.653 ↑ 1.0 9,842 1

Seq Scan on "Mapping" m_2 (cost=0.00..444.42 rows=9,842 width=36) (actual time=0.185..2.653 rows=9,842 loops=1)

56. 0.002 0.086 ↑ 1.0 1 1

Hash (cost=19.75..19.75 rows=1 width=16) (actual time=0.086..0.086 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
57. 0.084 0.084 ↑ 1.0 1 1

Seq Scan on "Workbook" wb_2 (cost=0.00..19.75 rows=1 width=16) (actual time=0.082..0.084 rows=1 loops=1)

  • Filter: (("EntityId" = 'b787799f-4826-4f8a-9593-686999e54c9f'::uuid) AND ("Name" = 'AFS 2019.xlsx'::text))
  • Rows Removed by Filter: 2
58. 0.004 0.014 ↑ 1.0 3 1

Hash (cost=0.15..0.15 rows=3 width=8) (actual time=0.014..0.014 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
59. 0.001 0.010 ↑ 1.0 3 1

Subquery Scan on y_1 (cost=0.09..0.15 rows=3 width=8) (actual time=0.010..0.010 rows=3 loops=1)

60. 0.006 0.009 ↑ 1.0 3 1

HashAggregate (cost=0.09..0.12 rows=3 width=8) (actual time=0.009..0.009 rows=3 loops=1)

  • Group Key: (2017), (1)
61. 0.001 0.003 ↑ 1.0 3 1

Append (cost=0.00..0.07 rows=3 width=8) (actual time=0.003..0.003 rows=3 loops=1)

62. 0.002 0.002 ↑ 1.0 1 1

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

63. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=1)

64. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=1)

65. 13.666 13.666 ↑ 2.0 1 6,833

Index Scan using "IX_Cell_MappingId" on "Cell" c_3 (cost=0.29..0.60 rows=2 width=32) (actual time=0.001..0.002 rows=1 loops=6,833)

  • Index Cond: ("MappingId" = m_2."Id")
66. 7.942 7.942 ↑ 1.0 1 7,942

Index Scan using "IX_Balance_CellId" on "Balance" b_1 (cost=0.29..0.48 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=7,942)

  • Index Cond: ("CellId" = c_3."Id")
67. 0.000 0.000 ↑ 3.0 2 7,942

Materialize (cost=0.00..1.09 rows=6 width=48) (actual time=0.000..0.000 rows=2 loops=7,942)

68. 0.043 0.043 ↑ 1.0 6 1

Seq Scan on "LookupBalanceTypes" bt_1 (cost=0.00..1.06 rows=6 width=48) (actual time=0.042..0.043 rows=6 loops=1)

69. 15.884 15.884 ↑ 15.0 2 7,942

Index Only Scan using "PK_MappingProject" on "MappingProject" mi_1 (cost=0.41..1.10 rows=30 width=32) (actual time=0.001..0.002 rows=2 loops=7,942)

  • Index Cond: ("MappingId" = c_3."MappingId")
  • Heap Fetches: 16550
70. 32.859 32.859 ↑ 1.0 1 10,953

Index Scan using "PK_FlattenedBalances" on "FlattenedBalances" fb_1 (cost=0.42..1.68 rows=1 width=39) (actual time=0.003..0.003 rows=1 loops=10,953)

  • Index Cond: ("TransactionId" = fti_1."TransactionId")
71. 0.000 4.607 ↓ 0.0 0 1

Nested Loop (cost=1,663.45..55,595.60 rows=155 width=64) (actual time=4.607..4.607 rows=0 loops=1)

  • Join Filter: (y_2."FinancialYear" = fb_2."FinancialYear")
72. 0.007 4.607 ↓ 0.0 0 1

Hash Join (cost=1,663.03..54,532.96 rows=626 width=68) (actual time=4.607..4.607 rows=0 loops=1)

  • Hash Cond: (fti_2."CostId" = mi_2."CostingMscoaId")
73. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on "FlattenedTransactions" fti_2 (cost=1,163.68..53,870.94 rows=41,711 width=32) (never executed)

  • Recheck Cond: ("EntityId" = 'b787799f-4826-4f8a-9593-686999e54c9f'::uuid)
74. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on "IX_FlattenedTransactions_EntityId" (cost=0.00..1,153.25 rows=41,711 width=0) (never executed)

  • Index Cond: ("EntityId" = 'b787799f-4826-4f8a-9593-686999e54c9f'::uuid)
75. 0.001 4.600 ↓ 0.0 0 1

Hash (cost=499.31..499.31 rows=3 width=68) (actual time=4.600..4.600 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
76. 0.000 4.599 ↓ 0.0 0 1

Nested Loop (cost=20.81..499.31 rows=3 width=68) (actual time=4.599..4.599 rows=0 loops=1)

77. 0.000 4.599 ↓ 0.0 0 1

Nested Loop (cost=20.68..498.85 rows=3 width=52) (actual time=4.599..4.599 rows=0 loops=1)

78. 0.000 4.599 ↓ 0.0 0 1

Nested Loop (cost=20.39..496.88 rows=4 width=36) (actual time=4.599..4.599 rows=0 loops=1)

79. 0.003 4.599 ↓ 0.0 0 1

Hash Join (cost=20.10..495.64 rows=2 width=52) (actual time=4.599..4.599 rows=0 loops=1)

  • Hash Cond: (m_3."Year" = y_2."Year")
80. 2.346 4.587 ↓ 0.0 0 1

Nested Loop (cost=19.91..495.43 rows=2 width=52) (actual time=4.586..4.587 rows=0 loops=1)

81. 1.675 2.241 ↓ 455.5 6,833 1

Hash Join (cost=19.76..490.18 rows=15 width=20) (actual time=0.092..2.241 rows=6,833 loops=1)

  • Hash Cond: (m_3."WorkbookId" = wb_3."Id")
82. 0.557 0.557 ↑ 1.0 9,842 1

Seq Scan on "Mapping" m_3 (cost=0.00..444.42 rows=9,842 width=36) (actual time=0.009..0.557 rows=9,842 loops=1)

83. 0.002 0.009 ↑ 1.0 1 1

Hash (cost=19.75..19.75 rows=1 width=16) (actual time=0.008..0.009 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
84. 0.007 0.007 ↑ 1.0 1 1

Seq Scan on "Workbook" wb_3 (cost=0.00..19.75 rows=1 width=16) (actual time=0.006..0.007 rows=1 loops=1)

  • Filter: (("EntityId" = 'b787799f-4826-4f8a-9593-686999e54c9f'::uuid) AND ("Name" = 'AFS 2019.xlsx'::text))
  • Rows Removed by Filter: 2
85. 0.000 0.000 ↓ 0.0 0 6,833

Index Only Scan using "PK_MappingCosting" on "MappingCosting" mi_2 (cost=0.15..0.28 rows=7 width=32) (actual time=0.000..0.000 rows=0 loops=6,833)

  • Index Cond: ("MappingId" = m_3."Id")
  • Heap Fetches: 0
86. 0.003 0.009 ↑ 1.0 3 1

Hash (cost=0.15..0.15 rows=3 width=8) (actual time=0.009..0.009 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
87. 0.002 0.006 ↑ 1.0 3 1

Subquery Scan on y_2 (cost=0.09..0.15 rows=3 width=8) (actual time=0.005..0.006 rows=3 loops=1)

88. 0.002 0.004 ↑ 1.0 3 1

HashAggregate (cost=0.09..0.12 rows=3 width=8) (actual time=0.004..0.004 rows=3 loops=1)

  • Group Key: (2017), (1)
89. 0.001 0.002 ↑ 1.0 3 1

Append (cost=0.00..0.07 rows=3 width=8) (actual time=0.001..0.002 rows=3 loops=1)

90. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=1)

91. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=1)

92. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=1)

93. 0.000 0.000 ↓ 0.0 0

Index Scan using "IX_Cell_MappingId" on "Cell" c_4 (cost=0.29..0.60 rows=2 width=32) (never executed)

  • Index Cond: ("MappingId" = m_3."Id")
94. 0.000 0.000 ↓ 0.0 0

Index Scan using "IX_Balance_CellId" on "Balance" b_2 (cost=0.29..0.48 rows=1 width=32) (never executed)

  • Index Cond: ("CellId" = c_4."Id")
95. 0.000 0.000 ↓ 0.0 0

Index Scan using "PK_LookupBalanceTypes" on "LookupBalanceTypes" bt_2 (cost=0.13..0.15 rows=1 width=48) (never executed)

  • Index Cond: ("BalanceTypeId" = b_2."YearlyBalanceLookupId")
96. 0.000 0.000 ↓ 0.0 0

Index Scan using "PK_FlattenedBalances" on "FlattenedBalances" fb_2 (cost=0.42..1.68 rows=1 width=39) (never executed)

  • Index Cond: ("TransactionId" = fti_2."TransactionId")
97. 2,070.605 6,294.072 ↓ 437.7 775,150 1

Nested Loop (cost=1,694.18..66,791.75 rows=1,771 width=64) (actual time=1,317.803..6,294.072 rows=775,150 loops=1)

  • Join Filter: (y_3."FinancialYear" = fb_3."FinancialYear")
  • Rows Removed by Join Filter: 2878786
98. 500.114 569.531 ↓ 510.0 3,653,936 1

Hash Join (cost=1,693.76..54,629.08 rows=7,165 width=68) (actual time=58.402..569.531 rows=3,653,936 loops=1)

  • Hash Cond: (fti_3."FunctionId" = mi_3."FunctionMscoaId")
99. 11.576 13.543 ↑ 1.0 41,522 1

Bitmap Heap Scan on "FlattenedTransactions" fti_3 (cost=1,163.68..53,870.94 rows=41,711 width=32) (actual time=2.520..13.543 rows=41,522 loops=1)

  • Recheck Cond: ("EntityId" = 'b787799f-4826-4f8a-9593-686999e54c9f'::uuid)
  • Heap Blocks: exact=5037
100. 1.967 1.967 ↑ 1.0 41,522 1

Bitmap Index Scan on "IX_FlattenedTransactions_EntityId" (cost=0.00..1,153.25 rows=41,711 width=0) (actual time=1.967..1.967 rows=41,522 loops=1)

  • Index Cond: ("EntityId" = 'b787799f-4826-4f8a-9593-686999e54c9f'::uuid)
101. 3.996 55.874 ↓ 515.1 17,512 1

Hash (cost=529.65..529.65 rows=34 width=68) (actual time=55.874..55.874 rows=17,512 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1556kB
102. 0.601 51.878 ↓ 515.1 17,512 1

Nested Loop (cost=20.81..529.65 rows=34 width=68) (actual time=18.399..51.878 rows=17,512 loops=1)

103. 2.557 35.393 ↓ 378.2 7,942 1

Nested Loop (cost=20.52..516.87 rows=21 width=84) (actual time=0.253..35.393 rows=7,942 loops=1)

  • Join Filter: (b_3."YearlyBalanceLookupId" = bt_3."BalanceTypeId")
  • Rows Removed by Join Filter: 10595
104. 0.000 32.836 ↓ 378.2 7,942 1

Nested Loop (cost=20.52..514.16 rows=21 width=68) (actual time=0.242..32.836 rows=7,942 loops=1)

105. 0.000 17.869 ↓ 273.9 7,942 1

Nested Loop (cost=20.24..499.87 rows=29 width=52) (actual time=0.237..17.869 rows=7,942 loops=1)

106. 1.220 4.369 ↓ 455.5 6,833 1

Hash Join (cost=19.95..490.58 rows=15 width=20) (actual time=0.203..4.369 rows=6,833 loops=1)

  • Hash Cond: (m_4."Year" = y_3."Year")
107. 2.424 3.143 ↓ 455.5 6,833 1

Hash Join (cost=19.76..490.18 rows=15 width=20) (actual time=0.194..3.143 rows=6,833 loops=1)

  • Hash Cond: (m_4."WorkbookId" = wb_4."Id")
108. 0.707 0.707 ↑ 1.0 9,842 1

Seq Scan on "Mapping" m_4 (cost=0.00..444.42 rows=9,842 width=36) (actual time=0.008..0.707 rows=9,842 loops=1)

109. 0.001 0.012 ↑ 1.0 1 1

Hash (cost=19.75..19.75 rows=1 width=16) (actual time=0.012..0.012 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
110. 0.011 0.011 ↑ 1.0 1 1

Seq Scan on "Workbook" wb_4 (cost=0.00..19.75 rows=1 width=16) (actual time=0.009..0.011 rows=1 loops=1)

  • Filter: (("EntityId" = 'b787799f-4826-4f8a-9593-686999e54c9f'::uuid) AND ("Name" = 'AFS 2019.xlsx'::text))
  • Rows Removed by Filter: 2
111. 0.002 0.006 ↑ 1.0 3 1

Hash (cost=0.15..0.15 rows=3 width=8) (actual time=0.006..0.006 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
112. 0.000 0.004 ↑ 1.0 3 1

Subquery Scan on y_3 (cost=0.09..0.15 rows=3 width=8) (actual time=0.004..0.004 rows=3 loops=1)

113. 0.003 0.004 ↑ 1.0 3 1

HashAggregate (cost=0.09..0.12 rows=3 width=8) (actual time=0.004..0.004 rows=3 loops=1)

  • Group Key: (2017), (1)
114. 0.000 0.001 ↑ 1.0 3 1

Append (cost=0.00..0.07 rows=3 width=8) (actual time=0.000..0.001 rows=3 loops=1)

115. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.000..0.001 rows=1 loops=1)

116. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.000..0.001 rows=1 loops=1)

117. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=1)

118. 13.666 13.666 ↑ 2.0 1 6,833

Index Scan using "IX_Cell_MappingId" on "Cell" c_5 (cost=0.29..0.60 rows=2 width=32) (actual time=0.002..0.002 rows=1 loops=6,833)

  • Index Cond: ("MappingId" = m_4."Id")
119. 15.884 15.884 ↑ 1.0 1 7,942

Index Scan using "IX_Balance_CellId" on "Balance" b_3 (cost=0.29..0.48 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=7,942)

  • Index Cond: ("CellId" = c_5."Id")
120. 0.000 0.000 ↑ 3.0 2 7,942

Materialize (cost=0.00..1.09 rows=6 width=48) (actual time=0.000..0.000 rows=2 loops=7,942)

121. 0.008 0.008 ↑ 1.0 6 1

Seq Scan on "LookupBalanceTypes" bt_3 (cost=0.00..1.06 rows=6 width=48) (actual time=0.007..0.008 rows=6 loops=1)

122. 15.884 15.884 ↑ 4.5 2 7,942

Index Only Scan using "PK_MappingFunction" on "MappingFunction" mi_3 (cost=0.29..0.52 rows=9 width=32) (actual time=0.001..0.002 rows=2 loops=7,942)

  • Index Cond: ("MappingId" = c_5."MappingId")
  • Heap Fetches: 17512
123. 3,653.936 3,653.936 ↑ 1.0 1 3,653,936

Index Scan using "PK_FlattenedBalances" on "FlattenedBalances" fb_3 (cost=0.42..1.68 rows=1 width=39) (actual time=0.001..0.001 rows=1 loops=3,653,936)

  • Index Cond: ("TransactionId" = fti_3."TransactionId")
124. 4.003 78.076 ↓ 22.0 3,240 1

Nested Loop (cost=3,236.54..57,683.63 rows=147 width=64) (actual time=28.789..78.076 rows=3,240 loops=1)

  • Join Filter: (y_4."FinancialYear" = fb_4."FinancialYear")
  • Rows Removed by Join Filter: 1476
125. 1.447 64.641 ↓ 7.9 4,716 1

Hash Join (cost=3,236.11..56,671.92 rows=596 width=68) (actual time=24.191..64.641 rows=4,716 loops=1)

  • Hash Cond: (b_4."YearlyBalanceLookupId" = bt_4."BalanceTypeId")
126. 1.707 63.171 ↓ 7.9 4,716 1

Nested Loop (cost=3,234.98..56,668.12 rows=596 width=52) (actual time=24.156..63.171 rows=4,716 loops=1)

127. 2.148 52.032 ↓ 5.7 4,716 1

Nested Loop (cost=3,234.69..56,261.71 rows=825 width=36) (actual time=24.150..52.032 rows=4,716 loops=1)

  • Join Filter: (m_5."Id" = c_6."MappingId")
128. 12.193 46.740 ↓ 3.7 1,572 1

Hash Join (cost=3,234.41..56,102.35 rows=427 width=52) (actual time=24.140..46.740 rows=1,572 loops=1)

  • Hash Cond: (fti_4."FundId" = mi_4."FundMscoaId")
129. 11.080 13.020 ↑ 1.0 41,522 1

Bitmap Heap Scan on "FlattenedTransactions" fti_4 (cost=1,163.68..53,870.94 rows=41,711 width=32) (actual time=2.541..13.020 rows=41,522 loops=1)

  • Recheck Cond: ("EntityId" = 'b787799f-4826-4f8a-9593-686999e54c9f'::uuid)
  • Heap Blocks: exact=5037
130. 1.940 1.940 ↑ 1.0 41,522 1

Bitmap Index Scan on "IX_FlattenedTransactions_EntityId" (cost=0.00..1,153.25 rows=41,711 width=0) (actual time=1.940..1.940 rows=41,522 loops=1)

  • Index Cond: ("EntityId" = 'b787799f-4826-4f8a-9593-686999e54c9f'::uuid)
131. 4.119 21.527 ↓ 160.9 26,072 1

Hash (cost=2,068.70..2,068.70 rows=162 width=52) (actual time=21.527..21.527 rows=26,072 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2395kB
132. 0.299 17.408 ↓ 160.9 26,072 1

Nested Loop (cost=20.37..2,068.70 rows=162 width=52) (actual time=12.195..17.408 rows=26,072 loops=1)

133. 1.102 3.443 ↓ 455.5 6,833 1

Hash Join (cost=19.95..490.58 rows=15 width=20) (actual time=0.108..3.443 rows=6,833 loops=1)

  • Hash Cond: (m_5."Year" = y_4."Year")
134. 1.718 2.334 ↓ 455.5 6,833 1

Hash Join (cost=19.76..490.18 rows=15 width=20) (actual time=0.096..2.334 rows=6,833 loops=1)

  • Hash Cond: (m_5."WorkbookId" = wb_5."Id")
135. 0.608 0.608 ↑ 1.0 9,842 1

Seq Scan on "Mapping" m_5 (cost=0.00..444.42 rows=9,842 width=36) (actual time=0.009..0.608 rows=9,842 loops=1)

136. 0.001 0.008 ↑ 1.0 1 1

Hash (cost=19.75..19.75 rows=1 width=16) (actual time=0.008..0.008 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
137. 0.007 0.007 ↑ 1.0 1 1

Seq Scan on "Workbook" wb_5 (cost=0.00..19.75 rows=1 width=16) (actual time=0.007..0.007 rows=1 loops=1)

  • Filter: (("EntityId" = 'b787799f-4826-4f8a-9593-686999e54c9f'::uuid) AND ("Name" = 'AFS 2019.xlsx'::text))
  • Rows Removed by Filter: 2
138. 0.001 0.007 ↑ 1.0 3 1

Hash (cost=0.15..0.15 rows=3 width=8) (actual time=0.007..0.007 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
139. 0.001 0.006 ↑ 1.0 3 1

Subquery Scan on y_4 (cost=0.09..0.15 rows=3 width=8) (actual time=0.005..0.006 rows=3 loops=1)

140. 0.003 0.005 ↑ 1.0 3 1

HashAggregate (cost=0.09..0.12 rows=3 width=8) (actual time=0.004..0.005 rows=3 loops=1)

  • Group Key: (2017), (1)
141. 0.001 0.002 ↑ 1.0 3 1

Append (cost=0.00..0.07 rows=3 width=8) (actual time=0.001..0.002 rows=3 loops=1)

142. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.000..0.001 rows=1 loops=1)

143. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=1)

144. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=1)

145. 13.666 13.666 ↑ 947.2 4 6,833

Index Only Scan using "PK_MappingFund" on "MappingFund" mi_4 (cost=0.42..67.32 rows=3,789 width=32) (actual time=0.001..0.002 rows=4 loops=6,833)

  • Index Cond: ("MappingId" = m_5."Id")
  • Heap Fetches: 232
146. 3.144 3.144 ↓ 1.5 3 1,572

Index Scan using "IX_Cell_MappingId" on "Cell" c_6 (cost=0.29..0.35 rows=2 width=32) (actual time=0.002..0.002 rows=3 loops=1,572)

  • Index Cond: ("MappingId" = mi_4."MappingId")
147. 9.432 9.432 ↑ 1.0 1 4,716

Index Scan using "IX_Balance_CellId" on "Balance" b_4 (cost=0.29..0.48 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=4,716)

  • Index Cond: ("CellId" = c_6."Id")
148. 0.006 0.023 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=48) (actual time=0.023..0.023 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
149. 0.017 0.017 ↑ 1.0 6 1

Seq Scan on "LookupBalanceTypes" bt_4 (cost=0.00..1.06 rows=6 width=48) (actual time=0.016..0.017 rows=6 loops=1)

150. 9.432 9.432 ↑ 1.0 1 4,716

Index Scan using "PK_FlattenedBalances" on "FlattenedBalances" fb_4 (cost=0.42..1.68 rows=1 width=39) (actual time=0.002..0.002 rows=1 loops=4,716)

  • Index Cond: ("TransactionId" = fti_4."TransactionId")
151. 0.000 4.930 ↓ 0.0 0 1

Nested Loop (cost=1,663.45..55,595.60 rows=155 width=64) (actual time=4.930..4.930 rows=0 loops=1)

  • Join Filter: (y_5."FinancialYear" = fb_5."FinancialYear")
152. 0.014 4.930 ↓ 0.0 0 1

Hash Join (cost=1,663.03..54,532.96 rows=626 width=68) (actual time=4.930..4.930 rows=0 loops=1)

  • Hash Cond: (fti_5."RegionId" = mi_5."RegionMscoaId")
153. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on "FlattenedTransactions" fti_5 (cost=1,163.68..53,870.94 rows=41,711 width=32) (never executed)

  • Recheck Cond: ("EntityId" = 'b787799f-4826-4f8a-9593-686999e54c9f'::uuid)
154. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on "IX_FlattenedTransactions_EntityId" (cost=0.00..1,153.25 rows=41,711 width=0) (never executed)

  • Index Cond: ("EntityId" = 'b787799f-4826-4f8a-9593-686999e54c9f'::uuid)
155. 0.001 4.916 ↓ 0.0 0 1

Hash (cost=499.31..499.31 rows=3 width=68) (actual time=4.916..4.916 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
156. 0.000 4.915 ↓ 0.0 0 1

Nested Loop (cost=20.81..499.31 rows=3 width=68) (actual time=4.915..4.915 rows=0 loops=1)

157. 0.001 4.915 ↓ 0.0 0 1

Nested Loop (cost=20.68..498.85 rows=3 width=52) (actual time=4.915..4.915 rows=0 loops=1)

158. 0.000 4.914 ↓ 0.0 0 1

Nested Loop (cost=20.39..496.88 rows=4 width=36) (actual time=4.914..4.914 rows=0 loops=1)

159. 0.005 4.914 ↓ 0.0 0 1

Hash Join (cost=20.10..495.64 rows=2 width=52) (actual time=4.914..4.914 rows=0 loops=1)

  • Hash Cond: (m_6."Year" = y_5."Year")
160. 2.475 4.889 ↓ 0.0 0 1

Nested Loop (cost=19.91..495.43 rows=2 width=52) (actual time=4.888..4.889 rows=0 loops=1)

161. 1.791 2.414 ↓ 455.5 6,833 1

Hash Join (cost=19.76..490.18 rows=15 width=20) (actual time=0.123..2.414 rows=6,833 loops=1)

  • Hash Cond: (m_6."WorkbookId" = wb_6."Id")
162. 0.610 0.610 ↑ 1.0 9,842 1

Seq Scan on "Mapping" m_6 (cost=0.00..444.42 rows=9,842 width=36) (actual time=0.026..0.610 rows=9,842 loops=1)

163. 0.004 0.013 ↑ 1.0 1 1

Hash (cost=19.75..19.75 rows=1 width=16) (actual time=0.013..0.013 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
164. 0.009 0.009 ↑ 1.0 1 1

Seq Scan on "Workbook" wb_6 (cost=0.00..19.75 rows=1 width=16) (actual time=0.008..0.009 rows=1 loops=1)

  • Filter: (("EntityId" = 'b787799f-4826-4f8a-9593-686999e54c9f'::uuid) AND ("Name" = 'AFS 2019.xlsx'::text))
  • Rows Removed by Filter: 2
165. 0.000 0.000 ↓ 0.0 0 6,833

Index Only Scan using "PK_MappingRegion" on "MappingRegion" mi_5 (cost=0.15..0.28 rows=7 width=32) (actual time=0.000..0.000 rows=0 loops=6,833)

  • Index Cond: ("MappingId" = m_6."Id")
  • Heap Fetches: 0
166. 0.014 0.020 ↑ 1.0 3 1

Hash (cost=0.15..0.15 rows=3 width=8) (actual time=0.020..0.020 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
167. 0.001 0.006 ↑ 1.0 3 1

Subquery Scan on y_5 (cost=0.09..0.15 rows=3 width=8) (actual time=0.006..0.006 rows=3 loops=1)

168. 0.004 0.005 ↑ 1.0 3 1

HashAggregate (cost=0.09..0.12 rows=3 width=8) (actual time=0.005..0.005 rows=3 loops=1)

  • Group Key: (2017), (1)
169. 0.000 0.001 ↑ 1.0 3 1

Append (cost=0.00..0.07 rows=3 width=8) (actual time=0.001..0.001 rows=3 loops=1)

170. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=1)

171. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.000..0.001 rows=1 loops=1)

172. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=8) (actual time=0.000..0.000 rows=1 loops=1)

173. 0.000 0.000 ↓ 0.0 0

Index Scan using "IX_Cell_MappingId" on "Cell" c_7 (cost=0.29..0.60 rows=2 width=32) (never executed)

  • Index Cond: ("MappingId" = m_6."Id")
174. 0.000 0.000 ↓ 0.0 0

Index Scan using "IX_Balance_CellId" on "Balance" b_5 (cost=0.29..0.48 rows=1 width=32) (never executed)

  • Index Cond: ("CellId" = c_7."Id")
175. 0.000 0.000 ↓ 0.0 0

Index Scan using "PK_LookupBalanceTypes" on "LookupBalanceTypes" bt_5 (cost=0.13..0.15 rows=1 width=48) (never executed)

  • Index Cond: ("BalanceTypeId" = b_5."YearlyBalanceLookupId")
176. 0.000 0.000 ↓ 0.0 0

Index Scan using "PK_FlattenedBalances" on "FlattenedBalances" fb_5 (cost=0.42..1.68 rows=1 width=39) (never executed)

  • Index Cond: ("TransactionId" = fti_5."TransactionId")
Planning time : 27.563 ms