explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GBnu

Settings
# exclusive inclusive rows x rows loops node
1. 3,345.026 41,091.737 ↓ 440.9 3,070,389 1

Hash Join (cost=60,639.97..77,458.35 rows=6,964 width=64) (actual time=38,009.703..41,091.737 rows=3,070,389 loops=1)

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

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

3. 2,785.238 37,361.137 ↓ 346.1 9,750,031 1

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

  • Buckets: 65536 (originally 32768) Batches: 256 (originally 1) Memory Usage: 3585kB
4. 1,594.930 34,575.899 ↓ 346.1 9,750,031 1

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

  • Hash Cond: (b."YearlyBalanceLookupId" = bt."BalanceTypeId")
5. 2,108.910 32,980.894 ↓ 346.1 9,750,031 1

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

  • Hash Cond: (c."Id" = b."CellId")
6. 1,568.545 30,848.252 ↓ 477.4 9,750,031 1

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

  • Hash Cond: (m."Id" = c."MappingId")
7. 2,963.512 29,264.045 ↓ 403.7 4,264,915 1

Hash Join (cost=4,495.22..57,516.67 rows=10,564 width=52) (actual time=26,240.486..29,264.045 rows=4,264,915 loops=1)

  • Hash Cond: (fti."ItemId" = mi."ItemMscoaId")
8. 86.452 91.085 ↑ 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.227..91.085 rows=41,522 loops=1)

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

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

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

  • Buckets: 65536 (originally 16384) Batches: 256 (originally 1) Memory Usage: 3585kB
11. 531.727 23,933.088 ↓ 656.1 5,964,789 1

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

12. 3.596 18.835 ↓ 455.5 6,833 1

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

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

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

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

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

15. 0.002 0.017 ↑ 1.0 1 1

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

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

Seq Scan on "Workbook" wb (cost=0.00..19.75 rows=1 width=16) (actual time=0.015..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
17. 0.002 0.012 ↑ 1.0 3 1

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

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

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

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

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. 23,382.526 23,382.526 ↑ 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.086..3.422 rows=873 loops=6,833)

  • Index Cond: ("MappingId" = m."Id")
  • Heap Fetches: 5964789
25. 2.565 15.662 ↑ 1.0 18,921 1

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

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

Seq Scan on "Cell" c (cost=0.00..552.21 rows=18,921 width=32) (actual time=5.940..13.097 rows=18,921 loops=1)

27. 4.725 23.732 ↑ 1.0 26,245 1

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

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

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

29. 0.012 0.075 ↑ 1.0 6 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
30. 0.063 0.063 ↑ 1.0 6 1

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

Planning time : 22.148 ms