explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CcHF

Settings
# exclusive inclusive rows x rows loops node
1. 598.530 32,608.999 ↑ 14.5 37 1

Gather (cost=1,383,533.15..1,826,184.10 rows=535 width=1,138) (actual time=31,975.108..32,608.999 rows=37 loops=1)

  • Workers Planned: 5
  • Workers Launched: 5
2. 0.023 32,010.469 ↑ 17.8 6 6 / 6

Nested Loop Left Join (cost=1,382,533.15..1,825,130.60 rows=107 width=1,138) (actual time=31,963.818..32,010.469 rows=6 loops=6)

3. 3.624 32,010.156 ↑ 17.8 6 6 / 6

Hash Join (cost=1,382,532.31..1,823,682.52 rows=107 width=1,024) (actual time=31,963.726..32,010.156 rows=6 loops=6)

  • Hash Cond: (cs.row_id = ph_1.cost_settlement_id)
4. 84.315 31,986.424 ↑ 16.4 3,372 6 / 6

Parallel Hash Left Join (cost=956,858.56..1,397,800.76 rows=55,184 width=905) (actual time=31,938.003..31,986.424 rows=3,372 loops=6)

  • Hash Cond: (cs.row_id = lh.cost_settlement_id)
5. 1,167.505 31,091.111 ↑ 16.4 3,372 6 / 6

Parallel Hash Left Join (cost=918,627.39..1,346,192.81 rows=55,184 width=791) (actual time=30,240.990..31,091.111 rows=3,372 loops=6)

  • Hash Cond: (cs.row_id = ph.cost_settlement_id)
6. 18.868 19.545 ↑ 16.4 3,372 6 / 6

Parallel Bitmap Heap Scan on cost_settlements cs (cost=6,966.44..409,985.83 rows=55,184 width=672) (actual time=5.475..19.545 rows=3,372 loops=6)

  • Recheck Cond: (((bu)::text = 'Canada'::text) AND (fiscal_year_int = 2,020) AND (fiscal_period_id = 5))
  • Heap Blocks: exact=1,065
7. 0.677 0.677 ↑ 13.6 20,229 1 / 6

Bitmap Index Scan on idx_bu_fiscal_year_period_bu_cs (cost=0.00..6,897.46 rows=275,922 width=0) (actual time=4.060..4.060 rows=20,229 loops=1)

  • Index Cond: (((bu)::text = 'Canada'::text) AND (fiscal_year_int = 2,020) AND (fiscal_period_id = 5))
8. 8,636.757 29,904.061 ↓ 1.8 1,445,096 6 / 6

Parallel Hash (cost=886,706.37..886,706.37 rows=803,566 width=123) (actual time=29,904.061..29,904.061 rows=1,445,096 loops=6)

  • Buckets: 32,768 (originally 32768) Batches: 512 (originally 256) Memory Usage: 2,720kB
9. 2,277.042 21,267.304 ↓ 1.8 1,445,096 6 / 6

Hash Join (cost=70.89..886,706.37 rows=803,566 width=123) (actual time=1,528.378..21,267.304 rows=1,445,096 loops=6)

  • Hash Cond: (c.scheme_id = s.row_id)
10. 3,816.805 18,990.245 ↓ 1.3 2,890,192 6 / 6

Hash Join (cost=69.75..878,100.39 rows=2,142,842 width=119) (actual time=1.057..18,990.245 rows=2,890,192 loops=6)

  • Hash Cond: (ph.category_id = c.row_id)
11. 5,731.479 15,172.424 ↓ 1.3 2,890,192 6 / 6

Hash Join (cost=4.38..872,394.45 rows=2,142,842 width=13) (actual time=0.031..15,172.424 rows=2,890,192 loops=6)

  • Hash Cond: (ph.model_id = m.row_id)
12. 9,440.926 9,440.926 ↑ 1.0 10,663,998 6 / 6

Parallel Seq Scan on pred_history ph (cost=0.00..840,292.09 rows=10,714,209 width=17) (actual time=0.005..9,440.926 rows=10,663,998 loops=6)

13. 0.003 0.019 ↑ 1.0 6 6 / 6

Hash (cost=4.30..4.30 rows=6 width=4) (actual time=0.019..0.019 rows=6 loops=6)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
14. 0.016 0.016 ↑ 1.0 6 6 / 6

Seq Scan on model m (cost=0.00..4.30 rows=6 width=4) (actual time=0.004..0.016 rows=6 loops=6)

  • Filter: is_current
  • Rows Removed by Filter: 24
15. 0.562 1.016 ↑ 1.0 1,528 6 / 6

Hash (cost=46.28..46.28 rows=1,528 width=110) (actual time=1.015..1.016 rows=1,528 loops=6)

  • Buckets: 2,048 Batches: 1 Memory Usage: 182kB
16. 0.454 0.454 ↑ 1.0 1,528 6 / 6

Seq Scan on category c (cost=0.00..46.28 rows=1,528 width=110) (actual time=0.005..0.454 rows=1,528 loops=6)

17. 0.004 0.017 ↑ 1.0 3 6 / 6

Hash (cost=1.10..1.10 rows=3 width=12) (actual time=0.017..0.017 rows=3 loops=6)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
18. 0.013 0.013 ↑ 1.0 3 6 / 6

Seq Scan on scheme s (cost=0.00..1.10 rows=3 width=12) (actual time=0.011..0.013 rows=3 loops=6)

  • Filter: (is_current AND ((scheme_name)::text = 'Activity'::text))
  • Rows Removed by Filter: 5
19. 203.222 810.998 ↑ 1.4 85,560 6 / 6

Parallel Hash (cost=34,521.67..34,521.67 rows=123,320 width=118) (actual time=810.998..810.998 rows=85,560 loops=6)

  • Buckets: 32,768 Batches: 16 Memory Usage: 4,128kB
20. 99.803 607.776 ↑ 1.4 85,560 6 / 6

Hash Join (cost=66.52..34,521.67 rows=123,320 width=118) (actual time=74.687..607.776 rows=85,560 loops=6)

  • Hash Cond: (c_1.scheme_id = s_1.row_id)
21. 217.938 507.962 ↑ 1.9 171,119 6 / 6

Hash Join (cost=65.38..33,199.98 rows=328,853 width=114) (actual time=74.657..507.962 rows=171,119 loops=6)

  • Hash Cond: (lh.category_id = c_1.row_id)
22. 285.479 285.479 ↑ 1.9 171,119 6 / 6

Parallel Seq Scan on label_history lh (cost=0.00..32,268.97 rows=328,853 width=8) (actual time=70.079..285.479 rows=171,119 loops=6)

  • Filter: is_current
  • Rows Removed by Filter: 256,679
23. 0.637 4.545 ↑ 1.0 1,528 6 / 6

Hash (cost=46.28..46.28 rows=1,528 width=110) (actual time=4.545..4.545 rows=1,528 loops=6)

  • Buckets: 2,048 Batches: 1 Memory Usage: 182kB
24. 3.908 3.908 ↑ 1.0 1,528 6 / 6

Seq Scan on category c_1 (cost=0.00..46.28 rows=1,528 width=110) (actual time=0.005..3.908 rows=1,528 loops=6)

25. 0.004 0.011 ↑ 1.0 3 6 / 6

Hash (cost=1.10..1.10 rows=3 width=12) (actual time=0.011..0.011 rows=3 loops=6)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
26. 0.007 0.007 ↑ 1.0 3 6 / 6

Seq Scan on scheme s_1 (cost=0.00..1.10 rows=3 width=12) (actual time=0.005..0.007 rows=3 loops=6)

  • Filter: (is_current AND ((scheme_name)::text = 'Activity'::text))
  • Rows Removed by Filter: 5
27. 6.875 20.108 ↑ 3.9 4,267 6 / 6

Hash (cost=425,463.39..425,463.39 rows=16,829 width=123) (actual time=20.108..20.108 rows=4,267 loops=6)

  • Buckets: 32,768 Batches: 1 Memory Usage: 657kB
28. 0.763 13.233 ↑ 3.9 4,267 6 / 6

Nested Loop (cost=0.56..425,463.39 rows=16,829 width=123) (actual time=0.160..13.233 rows=4,267 loops=6)

29. 0.009 0.317 ↓ 1.5 3 6 / 6

Nested Loop (cost=0.00..51.39 rows=2 width=114) (actual time=0.071..0.317 rows=3 loops=6)

  • Join Filter: (s_2.row_id = c_2.scheme_id)
  • Rows Removed by Join Filter: 6
30. 0.296 0.296 ↑ 1.0 4 6 / 6

Seq Scan on category c_2 (cost=0.00..50.10 rows=4 width=110) (actual time=0.060..0.296 rows=4 loops=6)

  • Filter: ((lvl3_name)::text = 'Gasoline'::text)
  • Rows Removed by Filter: 1,524
31. 0.004 0.012 ↑ 1.5 2 24 / 6

Materialize (cost=0.00..1.11 rows=3 width=12) (actual time=0.003..0.003 rows=2 loops=24)

32. 0.008 0.008 ↑ 1.0 3 6 / 6

Seq Scan on scheme s_2 (cost=0.00..1.10 rows=3 width=12) (actual time=0.006..0.008 rows=3 loops=6)

  • Filter: (is_current AND ((scheme_name)::text = 'Element'::text))
  • Rows Removed by Filter: 5
33. 0.648 12.153 ↑ 45.4 1,422 18 / 6

Nested Loop (cost=0.56..212,061.00 rows=64,500 width=13) (actual time=0.043..4.051 rows=1,422 loops=18)

34. 0.039 0.039 ↑ 1.0 6 18 / 6

Seq Scan on model m_1 (cost=0.00..4.30 rows=6 width=4) (actual time=0.003..0.013 rows=6 loops=18)

  • Filter: is_current
  • Rows Removed by Filter: 24
35. 11.466 11.466 ↑ 45.4 237 108 / 6

Index Scan using pred_history_multi_column_idx on pred_history ph_1 (cost=0.56..35,235.28 rows=10,750 width=17) (actual time=0.007..0.637 rows=237 loops=108)

  • Index Cond: ((model_id = m_1.row_id) AND (category_id = c_2.row_id))
36. 0.037 0.290 ↑ 1.0 1 37 / 6

Nested Loop (cost=0.84..13.52 rows=1 width=118) (actual time=0.046..0.047 rows=1 loops=37)

37. 0.049 0.216 ↑ 1.0 2 37 / 6

Nested Loop (cost=0.71..13.19 rows=2 width=114) (actual time=0.026..0.035 rows=2 loops=37)

38. 0.117 0.117 ↑ 1.0 2 37 / 6

Index Scan using label_history_multi_column_idx on label_history lh_1 (cost=0.43..8.27 rows=2 width=8) (actual time=0.014..0.019 rows=2 loops=37)

  • Index Cond: (cs.row_id = cost_settlement_id)
  • Filter: is_current
  • Rows Removed by Filter: 3
39. 0.049 0.049 ↑ 1.0 1 74 / 6

Index Scan using category_pkey on category c_3 (cost=0.28..2.46 rows=1 width=110) (actual time=0.004..0.004 rows=1 loops=74)

  • Index Cond: (row_id = lh_1.category_id)
40. 0.037 0.037 ↓ 0.0 0 74 / 6

Index Scan using category_scheme_pkey on scheme s_3 (cost=0.13..0.16 rows=1 width=12) (actual time=0.003..0.003 rows=0 loops=74)

  • Index Cond: (row_id = c_3.scheme_id)
  • Filter: (is_current AND ((scheme_name)::text = 'Element'::text))
  • Rows Removed by Filter: 0
Planning time : 2.929 ms
Execution time : 32,610.604 ms