explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tSJu

Settings
# exclusive inclusive rows x rows loops node
1. 3.228 50,862.697 ↓ 39.7 7,942 1

Hash Join (cost=376,277.98..376,879.87 rows=200 width=73) (actual time=50,859.954..50,862.697 rows=7,942 loops=1)

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

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

3. 2.101 50,850.887 ↓ 39.7 7,942 1

Hash (cost=376,275.48..376,275.48 rows=200 width=48) (actual time=50,850.887..50,850.887 rows=7,942 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 474kB
4. 1.140 50,848.786 ↓ 39.7 7,942 1

Subquery Scan on q (cost=376,189.00..376,275.48 rows=200 width=48) (actual time=49,907.990..50,848.786 rows=7,942 loops=1)

5. 539.675 50,847.646 ↓ 39.7 7,942 1

GroupAggregate (cost=376,189.00..376,273.48 rows=200 width=48) (actual time=49,907.988..50,847.646 rows=7,942 loops=1)

  • Group Key: i."CellId
6. 2,647.858 50,307.971 ↓ 347.1 3,794,048 1

Sort (cost=376,189.00..376,216.33 rows=10,930 width=48) (actual time=49,907.963..50,307.971 rows=3,794,048 loops=1)

  • Sort Key: i."CellId
  • Sort Method: external merge Disk: 112064kB
7. 210.348 47,660.113 ↓ 347.1 3,794,048 1

Subquery Scan on i (cost=375,237.22..375,455.82 rows=10,930 width=48) (actual time=46,388.519..47,660.113 rows=3,794,048 loops=1)

8. 2,902.861 47,449.765 ↓ 347.1 3,794,048 1

HashAggregate (cost=375,237.22..375,346.52 rows=10,930 width=64) (actual time=46,388.518..47,449.765 rows=3,794,048 loops=1)

  • Group Key: c_1."Id", (NULL::uuid), (0.0000::numeric(19,4))
9. 99.585 44,546.904 ↓ 353.1 3,859,855 1

Append (cost=20.05..375,155.24 rows=10,930 width=64) (actual time=1.439..44,546.904 rows=3,859,855 loops=1)

10. 0.000 27.290 ↓ 273.9 7,942 1

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

11. 1.688 14.759 ↓ 455.5 6,833 1

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

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

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

13. 0.012 0.034 ↑ 1.0 1 1

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

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

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

  • Filter: (("EntityId" = 'b787799f-4826-4f8a-9593-686999e54c9f'::uuid) AND ("Name" = 'AFS 2019.xlsx'::text))
  • Rows Removed by Filter: 2
15. 13.666 13.666 ↑ 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.002 rows=1 loops=6,833)

  • Index Cond: ("MappingId" = m."Id")
16. 3,359.680 40,651.682 ↓ 440.9 3,070,389 1

Hash Join (cost=60,639.97..77,458.35 rows=6,964 width=64) (actual time=37,565.617..40,651.682 rows=3,070,389 loops=1)

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

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

18. 2,853.882 37,029.465 ↓ 346.1 9,750,031 1

Hash (cost=60,217.42..60,217.42 rows=28,170 width=68) (actual time=37,029.465..37,029.465 rows=9,750,031 loops=1)

  • Buckets: 65536 (originally 32768) Batches: 256 (originally 1) Memory Usage: 3585kB
19. 1,646.674 34,175.583 ↓ 346.1 9,750,031 1

Hash Join (cost=6,270.59..60,217.42 rows=28,170 width=68) (actual time=25,848.226..34,175.583 rows=9,750,031 loops=1)

  • Hash Cond: (b."YearlyBalanceLookupId" = bt."BalanceTypeId")
20. 2,166.115 32,528.863 ↓ 346.1 9,750,031 1

Hash Join (cost=6,269.46..60,114.46 rows=28,170 width=52) (actual time=25,848.169..32,528.863 rows=9,750,031 loops=1)

  • Hash Cond: (c_2."Id" = b."CellId")
21. 1,598.753 30,343.434 ↓ 477.4 9,750,031 1

Hash Join (cost=5,283.94..58,666.94 rows=20,424 width=36) (actual time=25,828.778..30,343.434 rows=9,750,031 loops=1)

  • Hash Cond: (m_1."Id" = c_2."MappingId")
22. 2,831.598 28,738.567 ↓ 403.7 4,264,915 1

Hash Join (cost=4,495.22..57,516.67 rows=10,564 width=52) (actual time=25,822.583..28,738.567 rows=4,264,915 loops=1)

  • Hash Cond: (fti."ItemId" = mi."ItemMscoaId")
23. 111.303 116.197 ↑ 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=5.579..116.197 rows=41,522 loops=1)

  • Recheck Cond: ("EntityId" = 'b787799f-4826-4f8a-9593-686999e54c9f'::uuid)
  • Heap Blocks: exact=5037
24. 4.894 4.894 ↑ 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=4.894..4.894 rows=41,522 loops=1)

  • Index Cond: ("EntityId" = 'b787799f-4826-4f8a-9593-686999e54c9f'::uuid)
25. 2,238.398 25,790.772 ↓ 656.1 5,964,789 1

Hash (cost=3,217.90..3,217.90 rows=9,091 width=52) (actual time=25,790.772..25,790.772 rows=5,964,789 loops=1)

  • Buckets: 65536 (originally 16384) Batches: 256 (originally 1) Memory Usage: 3585kB
26. 520.562 23,552.374 ↓ 656.1 5,964,789 1

Nested Loop (cost=20.51..3,217.90 rows=9,091 width=52) (actual time=0.865..23,552.374 rows=5,964,789 loops=1)

27. 3.526 11.435 ↓ 455.5 6,833 1

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

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

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

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

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

30. 0.002 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
31. 0.010 0.010 ↑ 1.0 1 1

Seq Scan on "Workbook" wb_1 (cost=0.00..19.75 rows=1 width=16) (actual time=0.010..0.010 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.010 ↑ 1.0 3 1

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

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

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

34. 0.004 0.007 ↑ 1.0 3 1

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

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

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

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

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. 23,020.377 23,020.377 ↑ 6.0 873 6,833

Index Only Scan using "PK_MappingItem" on "MappingItem" mi (cost=0.56..129.45 rows=5,237 width=32) (actual time=0.089..3.369 rows=873 loops=6,833)

  • Index Cond: ("MappingId" = m_1."Id")
  • Heap Fetches: 5964789
40. 2.447 6.114 ↑ 1.0 18,921 1

Hash (cost=552.21..552.21 rows=18,921 width=32) (actual time=6.114..6.114 rows=18,921 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1439kB
41. 3.667 3.667 ↑ 1.0 18,921 1

Seq Scan on "Cell" c_2 (cost=0.00..552.21 rows=18,921 width=32) (actual time=0.059..3.667 rows=18,921 loops=1)

42. 3.196 19.314 ↑ 1.0 26,245 1

Hash (cost=657.45..657.45 rows=26,245 width=32) (actual time=19.314..19.314 rows=26,245 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1706kB
43. 16.118 16.118 ↑ 1.0 26,245 1

Seq Scan on "Balance" b (cost=0.00..657.45 rows=26,245 width=32) (actual time=0.265..16.118 rows=26,245 loops=1)

44. 0.008 0.046 ↑ 1.0 6 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
45. 0.038 0.038 ↑ 1.0 6 1

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

46. 0.733 446.470 ↓ 17.5 3,134 1

Nested Loop (cost=1,707.61..56,071.59 rows=179 width=64) (actual time=291.202..446.470 rows=3,134 loops=1)

  • Join Filter: (y_1."FinancialYear" = fb_1."FinancialYear")
  • Rows Removed by Join Filter: 7819
47. 3.069 128.100 ↓ 15.1 10,953 1

Hash Join (cost=1,707.19..54,840.90 rows=725 width=68) (actual time=80.023..128.100 rows=10,953 loops=1)

  • Hash Cond: (b_1."YearlyBalanceLookupId" = bt_1."BalanceTypeId")
48. 5.068 124.989 ↓ 15.1 10,953 1

Nested Loop (cost=1,706.05..54,837.15 rows=725 width=52) (actual time=79.962..124.989 rows=10,953 loops=1)

49. 8.745 98.015 ↓ 20.7 10,953 1

Hash Join (cost=1,705.76..54,574.73 rows=530 width=36) (actual time=79.920..98.015 rows=10,953 loops=1)

  • Hash Cond: (fti_1."ProjectId" = mi_1."ProjectMscoaId")
50. 9.967 13.157 ↑ 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.790..13.157 rows=41,522 loops=1)

  • Recheck Cond: ("EntityId" = 'b787799f-4826-4f8a-9593-686999e54c9f'::uuid)
  • Heap Blocks: exact=5037
51. 3.190 3.190 ↑ 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.190..3.190 rows=41,522 loops=1)

  • Index Cond: ("EntityId" = 'b787799f-4826-4f8a-9593-686999e54c9f'::uuid)
52. 2.884 76.113 ↓ 337.8 16,550 1

Hash (cost=541.47..541.47 rows=49 width=36) (actual time=76.113..76.113 rows=16,550 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1356kB
53. 8.626 73.229 ↓ 337.8 16,550 1

Nested Loop (cost=20.53..541.47 rows=49 width=36) (actual time=19.677..73.229 rows=16,550 loops=1)

  • Join Filter: (m_2."Id" = c_3."MappingId")
54. 0.815 48.053 ↓ 662.0 16,550 1

Nested Loop (cost=20.24..528.66 rows=25 width=52) (actual time=19.634..48.053 rows=16,550 loops=1)

55. 1.404 6.240 ↓ 455.5 6,833 1

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

  • Hash Cond: (m_2."Year" = y_1."Year")
56. 2.162 4.828 ↓ 455.5 6,833 1

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

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

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

58. 0.001 0.015 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
59. 0.014 0.014 ↑ 1.0 1 1

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
61. 0.002 0.007 ↑ 1.0 3 1

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

62. 0.003 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)
63. 0.000 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)

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

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

67. 40.998 40.998 ↑ 38.0 2 6,833

Index Only Scan using "PK_MappingProject" on "MappingProject" mi_1 (cost=0.29..1.78 rows=76 width=32) (actual time=0.003..0.006 rows=2 loops=6,833)

  • Index Cond: ("MappingId" = m_2."Id")
  • Heap Fetches: 16550
68. 16.550 16.550 ↑ 2.0 1 16,550

Index Scan using "IX_Cell_MappingId" on "Cell" c_3 (cost=0.29..0.49 rows=2 width=32) (actual time=0.001..0.001 rows=1 loops=16,550)

  • Index Cond: ("MappingId" = mi_1."MappingId")
69. 21.906 21.906 ↑ 1.0 1 10,953

Index Scan using "IX_Balance_CellId" on "Balance" b_1 (cost=0.29..0.49 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=10,953)

  • Index Cond: ("CellId" = c_3."Id")
70. 0.006 0.042 ↑ 1.0 6 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
71. 0.036 0.036 ↑ 1.0 6 1

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

72. 317.637 317.637 ↑ 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.028..0.029 rows=1 loops=10,953)

  • Index Cond: ("TransactionId" = fti_1."TransactionId")
73. 0.001 8.744 ↓ 0.0 0 1

Nested Loop (cost=1,663.93..56,663.27 rows=309 width=64) (actual time=8.744..8.744 rows=0 loops=1)

  • Join Filter: (y_2."FinancialYear" = fb_2."FinancialYear")
74. 0.024 8.743 ↓ 0.0 0 1

Hash Join (cost=1,663.51..54,539.69 rows=1,251 width=68) (actual time=8.743..8.743 rows=0 loops=1)

  • Hash Cond: (fti_2."CostId" = mi_2."CostingMscoaId")
75. 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)
76. 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)
77. 0.002 8.719 ↓ 0.0 0 1

Hash (cost=499.75..499.75 rows=6 width=68) (actual time=8.719..8.719 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
78. 0.000 8.717 ↓ 0.0 0 1

Nested Loop (cost=20.81..499.75 rows=6 width=68) (actual time=8.717..8.717 rows=0 loops=1)

79. 0.001 8.717 ↓ 0.0 0 1

Nested Loop (cost=20.68..498.86 rows=6 width=52) (actual time=8.717..8.717 rows=0 loops=1)

80. 0.000 8.716 ↓ 0.0 0 1

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

81. 0.005 8.716 ↓ 0.0 0 1

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

  • Hash Cond: (m_3."Year" = y_2."Year")
82. 4.262 8.693 ↓ 0.0 0 1

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

83. 3.322 4.431 ↓ 455.5 6,833 1

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

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

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

85. 0.004 0.019 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
86. 0.015 0.015 ↑ 1.0 1 1

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

  • Filter: (("EntityId" = 'b787799f-4826-4f8a-9593-686999e54c9f'::uuid) AND ("Name" = 'AFS 2019.xlsx'::text))
  • Rows Removed by Filter: 2
87. 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
88. 0.008 0.018 ↑ 1.0 3 1

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

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

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

90. 0.005 0.009 ↑ 1.0 3 1

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

  • Group Key: (2017), (1)
91. 0.002 0.004 ↑ 1.0 3 1

Append (cost=0.00..0.07 rows=3 width=8) (actual time=0.001..0.004 rows=3 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 ↑ 1.0 1 1

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

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

95. 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")
96. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: ("CellId" = c_4."Id")
97. 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")
98. 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")
99. 1,429.082 3,115.818 ↓ 258.0 775,150 1

Hash Join (cost=54,872.47..71,591.87 rows=3,005 width=64) (actual time=1,906.553..3,115.818 rows=775,150 loops=1)

  • Hash Cond: ((fb_3."TransactionId" = fti_3."TransactionId") AND (fb_3."FinancialYear" = y_3."FinancialYear"))
100. 85.229 85.229 ↑ 1.0 355,787 1

Seq Scan on "FlattenedBalances" fb_3 (cost=0.00..13,975.87 rows=355,787 width=39) (actual time=0.094..85.229 rows=355,787 loops=1)

101. 1,002.832 1,601.507 ↓ 300.6 3,653,936 1

Hash (cost=54,690.12..54,690.12 rows=12,157 width=68) (actual time=1,601.507..1,601.507 rows=3,653,936 loops=1)

  • Buckets: 65536 (originally 16384) Batches: 128 (originally 1) Memory Usage: 3585kB
102. 489.951 598.675 ↓ 300.6 3,653,936 1

Hash Join (cost=1,704.88..54,690.12 rows=12,157 width=68) (actual time=94.180..598.675 rows=3,653,936 loops=1)

  • Hash Cond: (fti_3."FunctionId" = mi_3."FunctionMscoaId")
103. 15.125 17.296 ↑ 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.744..17.296 rows=41,522 loops=1)

  • Recheck Cond: ("EntityId" = 'b787799f-4826-4f8a-9593-686999e54c9f'::uuid)
  • Heap Blocks: exact=5037
104. 2.171 2.171 ↑ 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.171..2.171 rows=41,522 loops=1)

  • Index Cond: ("EntityId" = 'b787799f-4826-4f8a-9593-686999e54c9f'::uuid)
105. 4.002 91.428 ↓ 301.9 17,512 1

Hash (cost=540.47..540.47 rows=58 width=68) (actual time=91.428..91.428 rows=17,512 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1556kB
106. 7.046 87.426 ↓ 301.9 17,512 1

Nested Loop (cost=20.81..540.47 rows=58 width=68) (actual time=8.670..87.426 rows=17,512 loops=1)

  • Join Filter: (b_3."YearlyBalanceLookupId" = bt_3."BalanceTypeId")
  • Rows Removed by Join Filter: 25870
107. 12.656 80.380 ↓ 301.9 17,512 1

Nested Loop (cost=20.81..534.50 rows=58 width=52) (actual time=8.645..80.380 rows=17,512 loops=1)

108. 11.085 50.212 ↓ 417.0 17,512 1

Nested Loop (cost=20.52..513.70 rows=42 width=36) (actual time=8.613..50.212 rows=17,512 loops=1)

  • Join Filter: (m_4."Id" = c_5."MappingId")
109. 5.403 24.799 ↓ 651.3 14,328 1

Nested Loop (cost=20.23..501.87 rows=22 width=52) (actual time=8.583..24.799 rows=14,328 loops=1)

110. 1.784 5.730 ↓ 455.5 6,833 1

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

  • Hash Cond: (m_4."Year" = y_3."Year")
111. 2.965 3.933 ↓ 455.5 6,833 1

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

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

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

113. 0.001 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
114. 0.012 0.012 ↑ 1.0 1 1

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
116. 0.001 0.009 ↑ 1.0 3 1

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

117. 0.005 0.008 ↑ 1.0 3 1

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

  • Group Key: (2017), (1)
118. 0.002 0.003 ↑ 1.0 3 1

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

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

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

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

122. 13.666 13.666 ↑ 6.0 2 6,833

Index Only Scan using "PK_MappingFunction" on "MappingFunction" mi_3 (cost=0.29..0.63 rows=12 width=32) (actual time=0.002..0.002 rows=2 loops=6,833)

  • Index Cond: ("MappingId" = m_4."Id")
  • Heap Fetches: 14328
123. 14.328 14.328 ↑ 2.0 1 14,328

Index Scan using "IX_Cell_MappingId" on "Cell" c_5 (cost=0.29..0.51 rows=2 width=32) (actual time=0.001..0.001 rows=1 loops=14,328)

  • Index Cond: ("MappingId" = mi_3."MappingId")
124. 17.512 17.512 ↑ 1.0 1 17,512

Index Scan using "IX_Balance_CellId" on "Balance" b_3 (cost=0.29..0.49 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=17,512)

  • Index Cond: ("CellId" = c_5."Id")
125. 0.000 0.000 ↑ 3.0 2 17,512

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

126. 0.013 0.013 ↑ 1.0 6 1

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

127. 0.888 192.493 ↓ 24.0 3,240 1

Nested Loop (cost=2,246.21..56,043.46 rows=135 width=64) (actual time=102.496..192.493 rows=3,240 loops=1)

  • Join Filter: (y_4."FinancialYear" = fb_4."FinancialYear")
  • Rows Removed by Join Filter: 1476
128. 80.926 177.457 ↓ 8.6 4,716 1

Hash Join (cost=2,245.78..55,114.92 rows=547 width=68) (actual time=90.586..177.457 rows=4,716 loops=1)

  • Hash Cond: (fti_4."FundId" = mi_4."FundMscoaId")
129. 8.857 11.162 ↑ 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=3.044..11.162 rows=41,522 loops=1)

  • Recheck Cond: ("EntityId" = 'b787799f-4826-4f8a-9593-686999e54c9f'::uuid)
  • Heap Blocks: exact=5037
130. 2.305 2.305 ↑ 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.305..2.305 rows=41,522 loops=1)

  • Index Cond: ("EntityId" = 'b787799f-4826-4f8a-9593-686999e54c9f'::uuid)
131. 18.723 85.369 ↓ 737.9 78,216 1

Hash (cost=1,080.78..1,080.78 rows=106 width=68) (actual time=85.369..85.369 rows=78,216 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 2 (originally 1) Memory Usage: 3585kB
132. 14.789 66.646 ↓ 737.9 78,216 1

Hash Join (cost=515.87..1,080.78 rows=106 width=68) (actual time=35.132..66.646 rows=78,216 loops=1)

  • Hash Cond: (b_4."YearlyBalanceLookupId" = bt_4."BalanceTypeId")
133. 13.470 51.829 ↓ 737.9 78,216 1

Hash Join (cost=514.73..1,079.26 rows=106 width=52) (actual time=35.101..51.829 rows=78,216 loops=1)

  • Hash Cond: (mi_4."MappingId" = c_6."MappingId")
134. 3.355 3.355 ↑ 1.0 26,072 1

Seq Scan on "MappingFund" mi_4 (cost=0.00..465.72 rows=26,072 width=32) (actual time=0.090..3.355 rows=26,072 loops=1)

135. 1.874 35.004 ↓ 198.6 7,942 1

Hash (cost=514.23..514.23 rows=40 width=68) (actual time=35.004..35.004 rows=7,942 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 840kB
136. 0.000 33.130 ↓ 198.6 7,942 1

Nested Loop (cost=20.53..514.23 rows=40 width=68) (actual time=0.157..33.130 rows=7,942 loops=1)

137. 0.055 18.022 ↓ 273.9 7,942 1

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

138. 1.319 4.301 ↓ 455.5 6,833 1

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

  • Hash Cond: (m_5."Year" = y_4."Year")
139. 2.211 2.974 ↓ 455.5 6,833 1

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

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

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

141. 0.001 0.010 ↑ 1.0 1 1

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

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

Seq Scan on "Workbook" wb_5 (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
143. 0.001 0.008 ↑ 1.0 3 1

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

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

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

145. 0.005 0.006 ↑ 1.0 3 1

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

  • Group Key: (2017), (1)
146. 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)

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

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

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

150. 13.666 13.666 ↑ 2.0 1 6,833

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

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

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

  • Index Cond: ("CellId" = c_6."Id")
152. 0.005 0.028 ↑ 1.0 6 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
153. 0.023 0.023 ↑ 1.0 6 1

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

154. 14.148 14.148 ↑ 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.003..0.003 rows=1 loops=4,716)

  • Index Cond: ("TransactionId" = fti_4."TransactionId")
155. 0.002 4.822 ↓ 0.0 0 1

Nested Loop (cost=1,663.93..56,663.27 rows=309 width=64) (actual time=4.822..4.822 rows=0 loops=1)

  • Join Filter: (y_5."FinancialYear" = fb_5."FinancialYear")
156. 0.023 4.820 ↓ 0.0 0 1

Hash Join (cost=1,663.51..54,539.69 rows=1,251 width=68) (actual time=4.820..4.820 rows=0 loops=1)

  • Hash Cond: (fti_5."RegionId" = mi_5."RegionMscoaId")
157. 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)
158. 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)
159. 0.000 4.797 ↓ 0.0 0 1

Hash (cost=499.75..499.75 rows=6 width=68) (actual time=4.797..4.797 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
160. 0.001 4.797 ↓ 0.0 0 1

Nested Loop (cost=20.81..499.75 rows=6 width=68) (actual time=4.796..4.797 rows=0 loops=1)

161. 0.001 4.796 ↓ 0.0 0 1

Nested Loop (cost=20.68..498.86 rows=6 width=52) (actual time=4.796..4.796 rows=0 loops=1)

162. 0.000 4.795 ↓ 0.0 0 1

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

163. 0.005 4.795 ↓ 0.0 0 1

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

  • Hash Cond: (m_6."Year" = y_5."Year")
164. 2.327 4.748 ↓ 0.0 0 1

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

165. 1.746 2.421 ↓ 455.5 6,833 1

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

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

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

167. 0.007 0.022 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
168. 0.015 0.015 ↑ 1.0 1 1

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

  • Filter: (("EntityId" = 'b787799f-4826-4f8a-9593-686999e54c9f'::uuid) AND ("Name" = 'AFS 2019.xlsx'::text))
  • Rows Removed by Filter: 2
169. 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
170. 0.022 0.042 ↑ 1.0 3 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
171. 0.002 0.020 ↑ 1.0 3 1

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

172. 0.011 0.018 ↑ 1.0 3 1

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

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

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

174. 0.004 0.004 ↑ 1.0 1 1

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

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

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

177. 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")
178. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: ("CellId" = c_7."Id")
179. 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")
180. 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 : 39.815 ms