explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UsQ2

Settings
# exclusive inclusive rows x rows loops node
1. 1,161.511 14,525.798 ↓ 5,017.0 3,070,389 1

Hash Join (cost=19,312.29..73,476.52 rows=612 width=16) (actual time=5,566.999..14,525.798 rows=3,070,389 loops=1)

  • Hash Cond: ((y."FinancialYear" = fb."FinancialYear") AND (ft."TransactionId" = fb."TransactionId"))
2. 1,481.284 13,334.256 ↓ 346.1 9,750,031 1

Hash Join (cost=6,270.59..60,217.42 rows=28,170 width=36) (actual time=5,536.764..13,334.256 rows=9,750,031 loops=1)

  • Hash Cond: (b."YearlyBalanceLookupId" = bt."BalanceTypeId")
3. 2,075.548 11,852.952 ↓ 346.1 9,750,031 1

Hash Join (cost=6,269.46..60,114.46 rows=28,170 width=52) (actual time=5,536.737..11,852.952 rows=9,750,031 loops=1)

  • Hash Cond: (c."Id" = b."CellId")
4. 1,511.828 9,768.506 ↓ 477.4 9,750,031 1

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

  • Hash Cond: (m."Id" = c."MappingId")
5. 2,714.565 8,250.114 ↓ 403.7 4,264,915 1

Hash Join (cost=4,495.22..57,516.67 rows=10,564 width=52) (actual time=5,521.127..8,250.114 rows=4,264,915 loops=1)

  • Hash Cond: (ft."ItemId" = mi."ItemMscoaId")
6. 40.625 43.709 ↑ 1.0 41,522 1

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

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

  • Index Cond: ("EntityId" = 'b787799f-4826-4f8a-9593-686999e54c9f'::uuid)
8. 1,964.887 5,491.840 ↓ 656.1 5,964,789 1

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

  • Buckets: 65536 (originally 16384) Batches: 256 (originally 1) Memory Usage: 3585kB
9. 482.362 3,526.953 ↓ 656.1 5,964,789 1

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

10. 2.842 10.739 ↓ 455.5 6,833 1

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

  • Hash Cond: (m."Year" = y."Year")
11. 4.159 7.888 ↓ 455.5 6,833 1

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

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

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

13. 0.001 0.016 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
14. 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.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
15. 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
16. 0.000 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)

17. 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)
18. 0.000 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)

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

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

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

22. 3,033.852 3,033.852 ↑ 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.014..0.444 rows=873 loops=6,833)

  • Index Cond: ("MappingId" = m."Id")
  • Heap Fetches: 5964789
23. 2.644 6.564 ↑ 1.0 18,921 1

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

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

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

25. 3.197 8.898 ↑ 1.0 26,245 1

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

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

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

27. 0.004 0.020 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=16) (actual time=0.020..0.020 rows=6 loops=1)

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

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

29. 7.636 30.031 ↓ 1.3 41,522 1

Hash (cost=12,572.47..12,572.47 rows=31,282 width=24) (actual time=30.031..30.031 rows=41,522 loops=1)

  • Buckets: 65536 (originally 32768) Batches: 1 (originally 1) Memory Usage: 2783kB
30. 9.697 22.395 ↓ 1.3 41,522 1

Hash Join (cost=1,163.86..12,572.47 rows=31,282 width=24) (actual time=2.909..22.395 rows=41,522 loops=1)

  • Hash Cond: (fb."FinancialYear" = y_1."FinancialYear")
31. 9.930 12.687 ↑ 1.0 41,522 1

Bitmap Heap Scan on "FlattenedBalances" fb (cost=1,163.67..12,103.05 rows=41,710 width=20) (actual time=2.886..12.687 rows=41,522 loops=1)

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

Bitmap Index Scan on "IX_FlattenedBalances_EntityId" (cost=0.00..1,153.25 rows=41,710 width=0) (actual time=2.757..2.757 rows=41,522 loops=1)

  • Index Cond: ("EntityId" = 'b787799f-4826-4f8a-9593-686999e54c9f'::uuid)
33. 0.004 0.011 ↑ 1.0 3 1

Hash (cost=0.15..0.15 rows=3 width=4) (actual time=0.011..0.011 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
34. 0.000 0.007 ↑ 1.0 3 1

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

35. 0.005 0.007 ↑ 1.0 3 1

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

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

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

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

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

Planning time : 4.742 ms