explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bnil

Settings
# exclusive inclusive rows x rows loops node
1. 42,887.437 82,819.693 ↑ 45.9 5,310,843 1

Merge Right Join (cost=74,279,195.36..129,889,299.29 rows=243,997,083 width=2,856) (actual time=21,441.783..82,819.693 rows=5,310,843 loops=1)

  • Merge Cond: ((fbc.period = k.period) AND ((fbc.fx_iso)::text = COALESCE(upper((dou_1.default_local_currency)::text), upper((dou_1_1.default_local_currency)::text))))
2. 4.143 5.128 ↑ 1.0 3,239 1

Sort (cost=379.82..387.92 rows=3,240 width=13) (actual time=4.493..5.128 rows=3,239 loops=1)

  • Sort Key: fbc.period, fbc.fx_iso
  • Sort Method: quicksort Memory: 253kB
3. 0.985 0.985 ↑ 1.0 3,240 1

Seq Scan on fx_by_period fbc (cost=0.00..190.90 rows=3,240 width=13) (actual time=0.377..0.985 rows=3,240 loops=1)

  • Filter: ((exchange_rate_version)::text = 'curr_year_budget'::text)
  • Rows Removed by Filter: 3,552
4. 2,355.434 39,927.128 ↑ 45.9 5,310,843 1

Materialize (cost=74,278,815.54..82,186,546.87 rows=243,997,083 width=287) (actual time=21,437.211..39,927.128 rows=5,310,843 loops=1)

5. 3,948.035 37,571.694 ↑ 45.9 5,310,843 1

Merge Left Join (cost=74,278,815.54..81,576,554.17 rows=243,997,083 width=287) (actual time=21,437.208..37,571.694 rows=5,310,843 loops=1)

  • Merge Cond: ((k.period = fb.period) AND (COALESCE(upper((dou_1.default_local_currency)::text), upper((dou_1_1.default_local_currency)::text)) = (fb.fx_iso)::text))
6. 3,205.267 33,149.907 ↑ 45.9 5,310,843 1

Merge Left Join (cost=74,278,528.79..78,232,394.45 rows=243,997,083 width=281) (actual time=21,437.197..33,149.907 rows=5,310,843 loops=1)

  • Merge Cond: ((k.period = fa.period) AND ((COALESCE(upper((dou_1.default_local_currency)::text), upper((dou_1_1.default_local_currency)::text))) = (fa.fx_iso)::text))
7. 16,848.312 29,478.189 ↑ 45.9 5,310,843 1

Sort (cost=74,278,242.03..74,888,234.74 rows=243,997,083 width=275) (actual time=21,437.194..29,478.189 rows=5,310,843 loops=1)

  • Sort Key: k.period, (COALESCE(upper((dou_1.default_local_currency)::text), upper((dou_1_1.default_local_currency)::text)))
  • Sort Method: external merge Disk: 757,640kB
8. 2,966.369 12,629.877 ↑ 45.9 5,310,843 1

Merge Right Join (cost=4,914,241.79..8,595,626.24 rows=243,997,083 width=275) (actual time=8,834.159..12,629.877 rows=5,310,843 loops=1)

  • Merge Cond: ((dk.kpi_name)::text = (k.kpi_name)::text)
9. 17.592 23.597 ↑ 1.0 49,449 1

Sort (cost=5,009.88..5,134.63 rows=49,900 width=24) (actual time=17.757..23.597 rows=49,449 loops=1)

  • Sort Key: dk.kpi_name
  • Sort Method: quicksort Memory: 5,416kB
10. 6.005 6.005 ↓ 1.0 50,021 1

Seq Scan on dim_kpi dk (cost=0.00..1,116.00 rows=49,900 width=24) (actual time=0.006..6.005 rows=50,021 loops=1)

11. 390.209 9,639.911 ↑ 1.6 5,310,843 1

Materialize (cost=4,909,231.91..4,951,589.31 rows=8,471,480 width=251) (actual time=8,816.372..9,639.911 rows=5,310,843 loops=1)

12. 3,733.891 9,249.702 ↑ 3.5 2,414,043 1

Sort (cost=4,909,231.91..4,930,410.61 rows=8,471,480 width=251) (actual time=8,816.369..9,249.702 rows=2,414,043 loops=1)

  • Sort Key: k.kpi_name
  • Sort Method: external merge Disk: 338,304kB
13. 578.660 5,515.811 ↑ 3.5 2,414,043 1

Hash Left Join (cost=493,227.43..2,920,975.47 rows=8,471,480 width=251) (actual time=1,836.022..5,515.811 rows=2,414,043 loops=1)

  • Hash Cond: (lower((k.segment)::text) = lower((i.name)::text))
14. 1,701.381 4,936.973 ↑ 3.2 2,412,699 1

Hash Left Join (cost=493,217.46..2,175,630.71 rows=7,772,000 width=196) (actual time=1,835.832..4,936.973 rows=2,412,699 loops=1)

  • Hash Cond: ((lower((k.area)::text) = lower((dou_1.area)::text)) AND (lower((k.subarea)::text) = lower((dou_1.legacy_subarea)::text)) AND (lower((k.country)::text) = lower((dou_1.legacy_country)::text)))
15. 683.970 2,345.965 ↑ 3.3 2,380,171 1

Hash Left Join (cost=226,550.45..539,147.24 rows=7,772,000 width=171) (actual time=946.188..2,345.965 rows=2,380,171 loops=1)

  • Hash Cond: ((k.org_unit_spk)::text = (dou_1_1.org_unit_spk)::text)
16. 715.831 715.831 ↑ 3.3 2,380,171 1

Seq Scan on fact_kpis k (cost=0.00..226,143.91 rows=7,772,000 width=124) (actual time=0.009..715.831 rows=2,380,171 loops=1)

  • Filter: (period > 0)
  • Rows Removed by Filter: 4,631
17. 0.265 946.164 ↓ 1.1 1,175 1

Hash (cost=226,536.89..226,536.89 rows=1,085 width=52) (actual time=946.164..946.164 rows=1,175 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 101kB
18. 1.036 945.899 ↓ 1.1 1,175 1

Merge Join (cost=226,517.49..226,536.89 rows=1,085 width=52) (actual time=944.671..945.899 rows=1,175 loops=1)

  • Merge Cond: ((dou_1_1.org_unit_spk)::text = (fk_1.org_unit_spk)::text)
19. 3.227 3.703 ↑ 1.0 1,199 1

Sort (cost=307.49..310.49 rows=1,200 width=52) (actual time=3.601..3.703 rows=1,199 loops=1)

  • Sort Key: dou_1_1.org_unit_spk
  • Sort Method: quicksort Memory: 155kB
20. 0.476 0.476 ↑ 1.0 1,200 1

Seq Scan on dim_organizational_unit dou_1_1 (cost=0.00..246.11 rows=1,200 width=52) (actual time=0.004..0.476 rows=1,200 loops=1)

  • Filter: ((active)::text = 'Yes'::text)
  • Rows Removed by Filter: 9
21. 2.787 941.160 ↓ 1.1 1,180 1

Sort (cost=226,210.01..226,212.74 rows=1,093 width=5) (actual time=941.067..941.160 rows=1,180 loops=1)

  • Sort Key: fk_1.org_unit_spk
  • Sort Method: quicksort Memory: 104kB
22. 374.539 938.373 ↓ 1.1 1,180 1

HashAggregate (cost=226,143.91..226,154.84 rows=1,093 width=5) (actual time=938.244..938.373 rows=1,180 loops=1)

  • Group Key: (fk_1.org_unit_spk)::text
23. 563.834 563.834 ↑ 3.3 2,384,802 1

Seq Scan on fact_kpis fk_1 (cost=0.00..206,678.13 rows=7,786,313 width=5) (actual time=0.003..563.834 rows=2,384,802 loops=1)

24. 0.249 889.627 ↓ 1.3 194 1

Hash (cost=266,664.38..266,664.38 rows=150 width=42) (actual time=889.627..889.627 rows=194 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 22kB
25. 0.796 889.378 ↓ 1.3 194 1

Hash Join (cost=266,400.26..266,664.38 rows=150 width=42) (actual time=888.436..889.378 rows=194 loops=1)

  • Hash Cond: ((lower((dou_1.legacy_country)::text) = (fk.country)::text) AND (lower((dou_1.area)::text) = (fk.area)::text) AND (lower((dou_1.legacy_subarea)::text) = (fk.subarea)::text))
26. 0.481 0.481 ↑ 1.0 1,200 1

Seq Scan on dim_organizational_unit dou_1 (cost=0.00..246.11 rows=1,200 width=42) (actual time=0.012..0.481 rows=1,200 loops=1)

  • Filter: ((active)::text = 'Yes'::text)
  • Rows Removed by Filter: 9
27. 0.129 888.101 ↑ 148.2 325 1

Hash (cost=265,557.22..265,557.22 rows=48,174 width=27) (actual time=888.100..888.101 rows=325 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 532kB
28. 394.573 887.972 ↑ 143.8 335 1

HashAggregate (cost=265,075.48..265,557.22 rows=48,174 width=27) (actual time=887.834..887.972 rows=335 loops=1)

  • Group Key: (fk.country)::text, (fk.area)::text, (fk.subarea)::text
29. 493.399 493.399 ↑ 3.3 2,384,802 1

Seq Scan on fact_kpis fk (cost=0.00..206,678.13 rows=7,786,313 width=27) (actual time=0.005..493.399 rows=2,384,802 loops=1)

30. 0.124 0.178 ↑ 1.0 218 1

Hash (cost=7.24..7.24 rows=218 width=64) (actual time=0.178..0.178 rows=218 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 28kB
31. 0.054 0.054 ↑ 1.0 218 1

Seq Scan on dim_image i (cost=0.00..7.24 rows=218 width=64) (actual time=0.008..0.054 rows=218 loops=1)

  • Filter: ((category)::text = 'partner'::text)
  • Rows Removed by Filter: 41
32. 465.570 466.451 ↓ 2,867.1 5,092,039 1

Sort (cost=286.75..291.19 rows=1,776 width=13) (actual time=3.051..466.451 rows=5,092,039 loops=1)

  • Sort Key: fa.period, fa.fx_iso
  • Sort Method: quicksort Memory: 135kB
33. 0.881 0.881 ↑ 1.0 1,776 1

Seq Scan on fx_by_period fa (cost=0.00..190.90 rows=1,776 width=13) (actual time=0.014..0.881 rows=1,776 loops=1)

  • Filter: ((exchange_rate_version)::text = 'Actual'::text)
  • Rows Removed by Filter: 5,016
34. 472.933 473.752 ↓ 2,867.1 5,092,039 1

Sort (cost=286.75..291.19 rows=1,776 width=13) (actual time=2.307..473.752 rows=5,092,039 loops=1)

  • Sort Key: fb.period, fb.fx_iso
  • Sort Method: quicksort Memory: 135kB
35. 0.819 0.819 ↑ 1.0 1,776 1

Seq Scan on fx_by_period fb (cost=0.00..190.90 rows=1,776 width=13) (actual time=0.062..0.819 rows=1,776 loops=1)

  • Filter: ((exchange_rate_version)::text = 'Budget'::text)
  • Rows Removed by Filter: 5,016
Planning time : 2.104 ms
Execution time : 83,307.174 ms