explain.depesz.com

PostgreSQL's explain analyze made readable

Result: m8T2

Settings
# exclusive inclusive rows x rows loops node
1. 2,601.880 20,624.057 ↓ 299.6 3,070,389 1

Hash Join (cost=62,299.21..85,245.71 rows=10,249 width=64) (actual time=18,150.960..20,624.057 rows=3,070,389 loops=1)

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

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

3. 2,339.897 17,926.572 ↓ 235.2 9,750,031 1

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

  • Buckets: 65,536 (originally 65536) Batches: 256 (originally 2) Memory Usage: 3,585kB
4. 4,917.332 15,586.675 ↓ 235.2 9,750,031 1

Hash Join (cost=7,496.01..61,191.36 rows=41,457 width=68) (actual time=10,730.126..15,586.675 rows=9,750,031 loops=1)

  • Hash Cond: (fti."ItemId" = mi."ItemMscoaId")
5. 35.640 38.022 ↑ 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.987..38.022 rows=41,522 loops=1)

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

  • Index Cond: ("EntityId" = 'b787799f-4826-4f8a-9593-686999e54c9f'::uuid)
7. 3,863.481 10,631.321 ↓ 422.5 13,833,566 1

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

  • Buckets: 65,536 (originally 32768) Batches: 512 (originally 1) Memory Usage: 3,585kB
8. 1,034.991 6,767.840 ↓ 422.5 13,833,566 1

Nested Loop (cost=21.08..5,923.00 rows=32,746 width=68) (actual time=0.596..6,767.840 rows=13,833,566 loops=1)

9. 5.333 70.203 ↓ 378.2 7,942 1

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

  • Join Filter: (b."YearlyBalanceLookupId" = bt."BalanceTypeId")
  • Rows Removed by Join Filter: 10,595
10. 5.514 64.870 ↓ 378.2 7,942 1

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

11. 5.927 35.530 ↓ 273.9 7,942 1

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

12. 2.713 9.104 ↓ 455.5 6,833 1

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

  • Hash Cond: (m."Year" = y."Year")
13. 4.138 6.381 ↓ 455.5 6,833 1

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

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

Seq Scan on "Mapping" m (cost=0.00..444.42 rows=9,842 width=36) (actual time=0.022..2.228 rows=9,842 loops=1)

15. 0.006 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: 1,024 Batches: 1 Memory Usage: 9kB
16. 0.009 0.009 ↑ 1.0 1 1

Seq Scan on "Workbook" wb (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
17. 0.001 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: 1,024 Batches: 1 Memory Usage: 9kB
18. 0.002 0.009 ↑ 1.0 3 1

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

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

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

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

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

24. 20.499 20.499 ↑ 2.0 1 6,833

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

  • Index Cond: ("MappingId" = m."Id")
25. 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."Id")
26. 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)

27. 0.017 0.017 ↑ 1.0 6 1

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

28. 5,662.646 5,662.646 ↑ 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.713 rows=1,742 loops=7,942)

  • Index Cond: ("MappingId" = c."MappingId")
  • Heap Fetches: 13,833,566