explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DJnN

Settings
# exclusive inclusive rows x rows loops node
1. 420.096 31,357.468 ↑ 10.8 37 1

Gather (cost=1,655,274.87..2,172,641.74 rows=401 width=1,138) (actual time=30,881.484..31,357.468 rows=37 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
2. 0.030 30,937.372 ↑ 13.9 12 3 / 3

Nested Loop Left Join (cost=1,654,274.87..2,171,601.64 rows=167 width=1,138) (actual time=30,869.434..30,937.372 rows=12 loops=3)

3. 4.638 30,936.886 ↑ 13.9 12 3 / 3

Hash Join (cost=1,654,274.03..2,169,341.56 rows=167 width=1,024) (actual time=30,869.347..30,936.886 rows=12 loops=3)

  • Hash Cond: (cs.row_id = ph_1.cost_settlement_id)
4. 91.881 30,921.176 ↑ 17.0 6,743 3 / 3

Parallel Hash Left Join (cost=1,228,652.88..1,743,287.60 rows=114,968 width=905) (actual time=30,857.140..30,921.176 rows=6,743 loops=3)

  • Hash Cond: (cs.row_id = lh.cost_settlement_id)
5. 816.368 30,040.569 ↑ 17.0 6,743 3 / 3

Parallel Hash Left Join (cost=1,186,287.48..1,674,765.98 rows=114,968 width=791) (actual time=29,339.301..30,040.569 rows=6,743 loops=3)

  • Hash Cond: (cs.row_id = ph.cost_settlement_id)
6. 26.761 28.565 ↑ 17.0 6,743 3 / 3

Parallel Bitmap Heap Scan on cost_settlements cs (cost=6,966.44..437,792.98 rows=114,968 width=672) (actual time=7.021..28.565 rows=6,743 loops=3)

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

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

  • Index Cond: (((bu)::text = 'Canada'::text) AND (fiscal_year_int = 2,020) AND (fiscal_period_id = 5))
8. 8,290.838 29,195.636 ↓ 1.4 2,890,192 3 / 3

Parallel Hash (cost=1,116,934.62..1,116,934.62 rows=2,008,914 width=123) (actual time=29,195.636..29,195.636 rows=2,890,192 loops=3)

  • Buckets: 32,768 (originally 32768) Batches: 512 (originally 256) Memory Usage: 2,656kB
9. 2,302.925 20,904.798 ↓ 1.4 2,890,192 3 / 3

Hash Join (cost=70.89..1,116,934.62 rows=2,008,914 width=123) (actual time=1,512.983..20,904.798 rows=2,890,192 loops=3)

  • Hash Cond: (c.scheme_id = s.row_id)
10. 3,749.240 18,601.860 ↓ 1.1 5,780,383 3 / 3

Hash Join (cost=69.75..1,095,421.36 rows=5,357,105 width=119) (actual time=2.401..18,601.860 rows=5,780,383 loops=3)

  • Hash Cond: (ph.category_id = c.row_id)
11. 5,491.563 14,850.258 ↓ 1.1 5,780,383 3 / 3

Hash Join (cost=4.38..1,081,254.57 rows=5,357,105 width=13) (actual time=0.030..14,850.258 rows=5,780,383 loops=3)

  • Hash Cond: (ph.model_id = m.row_id)
12. 9,358.677 9,358.677 ↑ 1.3 21,327,997 3 / 3

Parallel Seq Scan on pred_history ph (cost=0.00..1,001,005.23 rows=26,785,523 width=17) (actual time=0.005..9,358.677 rows=21,327,997 loops=3)

13. 0.003 0.018 ↑ 1.0 6 3 / 3

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

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

Seq Scan on model m (cost=0.00..4.30 rows=6 width=4) (actual time=0.003..0.015 rows=6 loops=3)

  • Filter: is_current
  • Rows Removed by Filter: 24
15. 1.898 2.362 ↑ 1.0 1,528 3 / 3

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

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

Seq Scan on category c (cost=0.00..46.28 rows=1,528 width=110) (actual time=0.004..0.464 rows=1,528 loops=3)

17. 0.004 0.013 ↑ 1.0 3 3 / 3

Hash (cost=1.10..1.10 rows=3 width=12) (actual time=0.012..0.013 rows=3 loops=3)

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

Seq Scan on scheme s (cost=0.00..1.10 rows=3 width=12) (actual time=0.007..0.009 rows=3 loops=3)

  • Filter: (is_current AND ((scheme_name)::text = 'Activity'::text))
  • Rows Removed by Filter: 5
19. 124.003 788.726 ↓ 1.1 171,119 3 / 3

Parallel Hash (cost=37,574.29..37,574.29 rows=159,288 width=118) (actual time=788.726..788.726 rows=171,119 loops=3)

  • Buckets: 32,768 Batches: 16 Memory Usage: 4,096kB
20. 128.833 664.723 ↓ 1.1 171,119 3 / 3

Hash Join (cost=66.52..37,574.29 rows=159,288 width=118) (actual time=99.053..664.723 rows=171,119 loops=3)

  • Hash Cond: (c_1.scheme_id = s_1.row_id)
21. 211.936 535.878 ↑ 1.2 342,238 3 / 3

Hash Join (cost=65.38..35,867.45 rows=424,768 width=114) (actual time=99.022..535.878 rows=342,238 loops=3)

  • Hash Cond: (lh.category_id = c_1.row_id)
22. 322.141 322.141 ↑ 1.2 342,238 3 / 3

Parallel Seq Scan on label_history lh (cost=0.00..34,683.96 rows=424,768 width=8) (actual time=97.193..322.141 rows=342,238 loops=3)

  • Filter: is_current
  • Rows Removed by Filter: 513,359
23. 1.351 1.801 ↑ 1.0 1,528 3 / 3

Hash (cost=46.28..46.28 rows=1,528 width=110) (actual time=1.800..1.801 rows=1,528 loops=3)

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

Seq Scan on category c_1 (cost=0.00..46.28 rows=1,528 width=110) (actual time=0.004..0.450 rows=1,528 loops=3)

25. 0.006 0.012 ↑ 1.0 3 3 / 3

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

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

Seq Scan on scheme s_1 (cost=0.00..1.10 rows=3 width=12) (actual time=0.004..0.006 rows=3 loops=3)

  • Filter: (is_current AND ((scheme_name)::text = 'Activity'::text))
  • Rows Removed by Filter: 5
27. 3.253 11.072 ↑ 3.0 4,267 3 / 3

Hash (cost=425,463.39..425,463.39 rows=12,621 width=123) (actual time=11.072..11.072 rows=4,267 loops=3)

  • Buckets: 16,384 Batches: 1 Memory Usage: 529kB
28. 0.734 7.819 ↑ 3.0 4,267 3 / 3

Nested Loop (cost=0.56..425,463.39 rows=12,621 width=123) (actual time=0.151..7.819 rows=4,267 loops=3)

29. 0.006 0.320 ↓ 1.5 3 3 / 3

Nested Loop (cost=0.00..51.39 rows=2 width=114) (actual time=0.067..0.320 rows=3 loops=3)

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

Seq Scan on category c_2 (cost=0.00..50.10 rows=4 width=110) (actual time=0.058..0.302 rows=4 loops=3)

  • Filter: ((lvl3_name)::text = 'Gasoline'::text)
  • Rows Removed by Filter: 1,524
31. 0.006 0.012 ↑ 1.5 2 12 / 3

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

32. 0.006 0.006 ↑ 1.0 3 3 / 3

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

  • Filter: (is_current AND ((scheme_name)::text = 'Element'::text))
  • Rows Removed by Filter: 5
33. 0.642 6.765 ↑ 45.4 1,422 9 / 3

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

34. 0.039 0.039 ↑ 1.0 6 9 / 3

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

  • Filter: is_current
  • Rows Removed by Filter: 24
35. 6.084 6.084 ↑ 45.4 237 54 / 3

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.338 rows=237 loops=54)

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

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

37. 0.074 0.345 ↑ 1.0 2 37 / 3

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

38. 0.197 0.197 ↑ 1.0 2 37 / 3

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.011..0.016 rows=2 loops=37)

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

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

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

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 : 3.263 ms
Execution time : 31,358.449 ms