explain.depesz.com

PostgreSQL's explain analyze made readable

Result: llZg : 17 secs

Settings
# exclusive inclusive rows x rows loops node
1. 1.841 18,433.177 ↓ 5.4 3,333 1

Hash Join (cost=73,540.04..74,141.93 rows=612 width=73) (actual time=18,431.426..18,433.177 rows=3,333 loops=1)

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

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

3. 1.517 18,428.505 ↓ 5.4 3,333 1

Hash (cost=73,532.39..73,532.39 rows=612 width=48) (actual time=18,428.505..18,428.505 rows=3,333 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 211kB
4. 0.740 18,426.988 ↓ 5.4 3,333 1

Subquery Scan on q (cost=73,504.85..73,532.39 rows=612 width=48) (actual time=17,288.414..18,426.988 rows=3,333 loops=1)

5. 744.966 18,426.248 ↓ 5.4 3,333 1

GroupAggregate (cost=73,504.85..73,526.27 rows=612 width=48) (actual time=17,288.413..18,426.248 rows=3,333 loops=1)

  • Group Key: c_1."Id
6. 2,670.604 17,681.282 ↓ 5,017.0 3,070,389 1

Sort (cost=73,504.85..73,506.38 rows=612 width=67) (actual time=17,288.393..17,681.282 rows=3,070,389 loops=1)

  • Sort Key: c_1."Id
  • Sort Method: external merge Disk: 173064kB
7. 1,434.477 15,010.678 ↓ 5,017.0 3,070,389 1

Hash Join (cost=19,312.29..73,476.52 rows=612 width=67) (actual time=5,773.406..15,010.678 rows=3,070,389 loops=1)

  • Hash Cond: ((y."FinancialYear" = fb."FinancialYear") AND (ft."TransactionId" = fb."TransactionId"))
8. 1,548.502 13,543.092 ↓ 346.1 9,750,031 1

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

  • Hash Cond: (b."YearlyBalanceLookupId" = bt."BalanceTypeId")
9. 1,998.458 11,994.561 ↓ 346.1 9,750,031 1

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

  • Hash Cond: (c_1."Id" = b."CellId")
10. 1,506.908 9,987.338 ↓ 477.4 9,750,031 1

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

  • Hash Cond: (m."Id" = c_1."MappingId")
11. 2,725.790 8,476.090 ↓ 403.7 4,264,915 1

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

  • Hash Cond: (ft."ItemId" = mi."ItemMscoaId")
12. 46.287 49.577 ↑ 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.875..49.577 rows=41,522 loops=1)

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

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

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

  • Buckets: 65536 (originally 16384) Batches: 256 (originally 1) Memory Usage: 3585kB
15. 493.639 3,695.545 ↓ 656.1 5,964,789 1

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

16. 2.617 10.895 ↓ 455.5 6,833 1

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

  • Hash Cond: (m."Year" = y."Year")
17. 4.147 8.266 ↓ 455.5 6,833 1

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

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

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

19. 0.008 0.043 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.035 0.035 ↑ 1.0 1 1

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

  • Filter: (("EntityId" = 'b787799f-4826-4f8a-9593-686999e54c9f'::uuid) AND ("Name" = 'AFS 2019.xlsx'::text))
  • Rows Removed by Filter: 2
21. 0.003 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
22. 0.001 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)

23. 0.005 0.008 ↑ 1.0 3 1

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

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

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

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

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

28. 3,191.011 3,191.011 ↑ 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.013..0.467 rows=873 loops=6,833)

  • Index Cond: ("MappingId" = m."Id")
  • Heap Fetches: 5964789
29. 2.291 4.340 ↑ 1.0 18,921 1

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

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

Seq Scan on "Cell" c_1 (cost=0.00..552.21 rows=18,921 width=32) (actual time=0.077..2.049 rows=18,921 loops=1)

31. 3.156 8.765 ↑ 1.0 26,245 1

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

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

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

33. 0.004 0.029 ↑ 1.0 6 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
34. 0.025 0.025 ↑ 1.0 6 1

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

35. 10.117 33.109 ↓ 1.3 41,522 1

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

  • Buckets: 65536 (originally 32768) Batches: 1 (originally 1) Memory Usage: 3704kB
36. 10.993 22.992 ↓ 1.3 41,522 1

Hash Join (cost=1,163.86..12,572.47 rows=31,282 width=43) (actual time=2.893..22.992 rows=41,522 loops=1)

  • Hash Cond: (fb."FinancialYear" = y_1."FinancialYear")
37. 9.238 11.989 ↑ 1.0 41,522 1

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

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

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

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

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

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

41. 0.004 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)
42. 0.002 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)

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

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

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

Planning time : 5.514 ms