explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iFrk

Settings
# exclusive inclusive rows x rows loops node
1. 14,517.280 75,453.709 ↓ 2,196.8 4,578,114 1

Hash Join (cost=95,737.80..117,835.42 rows=2,084 width=64) (actual time=60,791.402..75,453.709 rows=4,578,114 loops=1)

  • Hash Cond: ((fb."FinancialYear" = y."FinancialYear") AND (fb."TransactionId" = ft."TransactionId"))
2. 24.652 204.882 ↓ 1.4 49,996 1

Hash Join (cost=0.19..18,625.33 rows=36,234 width=115) (actual time=0.117..204.882 rows=49,996 loops=1)

  • Hash Cond: (fb."FinancialYear" = y_1."FinancialYear")
3. 180.219 180.219 ↑ 1.0 71,985 1

Seq Scan on "FlattenedBalances" fb (cost=0.00..17,991.05 rows=72,467 width=111) (actual time=0.089..180.219 rows=71,985 loops=1)

  • Filter: ("EntityId" = 'b787799f-4826-4f8a-9593-686999e54c9f'::uuid)
  • Rows Removed by Filter: 333539
4. 0.003 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
5. 0.001 0.008 ↑ 1.0 3 1

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

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

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

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

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

11. 9,587.647 60,731.547 ↓ 146.4 20,720,224 1

Hash (cost=91,956.22..91,956.22 rows=141,493 width=68) (actual time=60,731.547..60,731.547 rows=20,720,224 loops=1)

  • Buckets: 65536 (originally 65536) Batches: 1024 (originally 8) Memory Usage: 3585kB
12. 33,163.209 51,143.900 ↓ 146.4 20,720,224 1

Hash Join (cost=88,574.69..91,956.22 rows=141,493 width=68) (actual time=18,052.153..51,143.900 rows=20,720,224 loops=1)

  • Hash Cond: (c."MappingId" = m."Id")
13. 12.334 71.321 ↑ 3.4 23,090 1

Hash Join (cost=12,856.58..14,441.17 rows=78,735 width=64) (actual time=48.713..71.321 rows=23,090 loops=1)

  • Hash Cond: (b."CellId" = c."Id")
14. 8.310 47.810 ↑ 3.4 23,090 1

Unique (cost=12,071.86..12,662.37 rows=78,735 width=48) (actual time=37.421..47.810 rows=23,090 loops=1)

15. 19.513 39.500 ↑ 3.3 24,031 1

Sort (cost=12,071.86..12,268.70 rows=78,735 width=48) (actual time=37.407..39.500 rows=24,031 loops=1)

  • Sort Key: b."CellId", bt."BalanceType
  • Sort Method: quicksort Memory: 2646kB
16. 1.172 19.987 ↑ 3.3 24,031 1

Append (cost=1.14..3,243.35 rows=78,735 width=48) (actual time=0.295..19.987 rows=24,031 loops=1)

17. 6.414 9.171 ↑ 1.9 14,007 1

Hash Join (cost=1.14..752.46 rows=26,245 width=24) (actual time=0.293..9.171 rows=14,007 loops=1)

  • Hash Cond: (b."YearlyBalanceLookupId" = bt."BalanceTypeId")
18. 2.724 2.724 ↑ 1.0 26,245 1

Seq Scan on "Balance" b (cost=0.00..656.45 rows=26,245 width=32) (actual time=0.243..2.724 rows=26,245 loops=1)

19. 0.016 0.033 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=24) (actual time=0.033..0.033 rows=6 loops=1)

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

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

21. 4.313 6.877 ↑ 4.1 6,464 1

Hash Join (cost=1.27..798.22 rows=26,245 width=48) (actual time=0.913..6.877 rows=6,464 loops=1)

  • Hash Cond: (b_1."MonthlyActualBalance_MonthId" = lfm."FinMonthId")
22. 2.544 2.544 ↑ 1.0 26,245 1

Seq Scan on "Balance" b_1 (cost=0.00..656.45 rows=26,245 width=32) (actual time=0.112..2.544 rows=26,245 loops=1)

23. 0.007 0.020 ↑ 1.0 12 1

Hash (cost=1.12..1.12 rows=12 width=20) (actual time=0.019..0.020 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
24. 0.013 0.013 ↑ 1.0 12 1

Seq Scan on "LookupFinMonth" lfm (cost=0.00..1.12 rows=12 width=20) (actual time=0.011..0.013 rows=12 loops=1)

25. 1.070 2.767 ↑ 7.4 3,560 1

Merge Join (cost=1.63..511.64 rows=26,245 width=48) (actual time=0.090..2.767 rows=3,560 loops=1)

  • Merge Cond: (b_2."MonthlyBudgetBalance_MonthId" = lfm_1."FinMonthId")
26. 1.649 1.649 ↑ 7.4 3,561 1

Index Scan using "IX_Balance_MonthlyBudgetBalance_MonthId" on "Balance" b_2 (cost=0.29..2,948.06 rows=26,245 width=32) (actual time=0.036..1.649 rows=3,561 loops=1)

27. 0.019 0.048 ↑ 1.3 9 1

Sort (cost=1.34..1.37 rows=12 width=20) (actual time=0.047..0.048 rows=9 loops=1)

  • Sort Key: lfm_1."FinMonthId
  • Sort Method: quicksort Memory: 25kB
28. 0.029 0.029 ↑ 1.0 12 1

Seq Scan on "LookupFinMonth" lfm_1 (cost=0.00..1.12 rows=12 width=20) (actual time=0.026..0.029 rows=12 loops=1)

29. 3.743 11.177 ↑ 1.0 18,921 1

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

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

Seq Scan on "Cell" c (cost=0.00..548.21 rows=18,921 width=32) (actual time=1.340..7.434 rows=18,921 loops=1)

31. 3,559.060 17,909.370 ↓ 506.7 8,888,746 1

Hash (cost=75,498.85..75,498.85 rows=17,541 width=52) (actual time=17,909.370..17,909.370 rows=8,888,746 loops=1)

  • Buckets: 65536 (originally 32768) Batches: 32768 (originally 1) Memory Usage: 5870kB
32. 5,381.581 14,350.310 ↓ 506.7 8,888,746 1

Hash Join (cost=3,312.61..75,498.85 rows=17,541 width=52) (actual time=8,592.186..14,350.310 rows=8,888,746 loops=1)

  • Hash Cond: (ft."ItemId" = mi."ItemMscoaId")
33. 532.189 532.189 ↓ 1.0 71,985 1

Seq Scan on "FlattenedTransactions" ft (cost=0.00..71,753.09 rows=68,731 width=32) (actual time=0.057..532.189 rows=71,985 loops=1)

  • Filter: ("EntityId" = 'b787799f-4826-4f8a-9593-686999e54c9f'::uuid)
  • Rows Removed by Filter: 338979
34. 2,628.268 8,436.540 ↓ 656.1 5,964,789 1

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

  • Buckets: 65536 (originally 16384) Batches: 256 (originally 1) Memory Usage: 3585kB
35. 812.564 5,808.272 ↓ 656.1 5,964,789 1

Nested Loop (cost=20.51..3,198.97 rows=9,091 width=52) (actual time=0.239..5,808.272 rows=5,964,789 loops=1)

36. 4.351 14.451 ↓ 455.5 6,833 1

Hash Join (cost=19.95..484.58 rows=15 width=20) (actual time=0.133..14.451 rows=6,833 loops=1)

  • Hash Cond: (m."Year" = y."Year")
37. 6.559 10.084 ↓ 455.5 6,833 1

Hash Join (cost=19.76..484.18 rows=15 width=20) (actual time=0.037..10.084 rows=6,833 loops=1)

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

Seq Scan on "Mapping" m (cost=0.00..438.42 rows=9,842 width=36) (actual time=0.010..3.506 rows=9,842 loops=1)

39. 0.002 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
40. 0.017 0.017 ↑ 1.0 1 1

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
42. 0.001 0.013 ↑ 1.0 3 1

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

43. 0.008 0.012 ↑ 1.0 3 1

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

  • Group Key: (2017), (1)
44. 0.001 0.004 ↑ 1.0 3 1

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

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

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

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

48. 4,981.257 4,981.257 ↑ 6.0 873 6,833

Index Only Scan using "PK_MappingItem" on "MappingItem" mi (cost=0.56..128.87 rows=5,209 width=32) (actual time=0.021..0.729 rows=873 loops=6,833)

  • Index Cond: ("MappingId" = m."Id")
  • Heap Fetches: 5964789
Planning time : 27.707 ms