explain.depesz.com

PostgreSQL's explain analyze made readable

Result: owug

Settings
# exclusive inclusive rows x rows loops node
1. 26,749.641 26,749.641 ↓ 302.0 302 1

CTE Scan on incentive_day (cost=20,759.31..20,759.33 rows=1 width=176) (actual time=26,742.956..26,749.641 rows=302 loops=1)

2.          

CTE calculated_group_incentives_but_individual_with

3. 0.020 0.020 ↑ 1.0 1 1

Seq Scan on base_configuration (cost=0.00..1.48 rows=1 width=7) (actual time=0.013..0.020 rows=1 loops=1)

  • Filter: ((fk_domain = 1) AND ((configuration_key)::text = 'calculated_group_incentives_but_individual_with'::text))
  • Rows Removed by Filter: 31
4.          

CTE max_minutes_employees

5. 10.383 93.892 ↑ 22.0 348 1

HashAggregate (cost=4,986.17..5,081.87 rows=7,656 width=16) (actual time=93.770..93.892 rows=348 loops=1)

  • Group Key: p.date_taken, p.fk_employee, ped.minutes_theoric
6. 7.148 83.509 ↓ 1.7 13,391 1

Nested Loop (cost=0.84..4,890.47 rows=7,656 width=16) (actual time=0.106..83.509 rows=13,391 loops=1)

7. 25.901 25.901 ↑ 1.0 348 1

Index Scan using unique_prod_efficiency_day on prod_efficiency_day ped (cost=0.41..675.46 rows=352 width=12) (actual time=0.093..25.901 rows=348 loops=1)

  • Index Cond: ((date_taken >= '2019-04-01'::date) AND (date_taken <= '2019-04-03'::date))
8. 50.460 50.460 ↓ 2.9 38 348

Index Scan using production_date_taken_fk_employee_idx on production p (cost=0.43..11.84 rows=13 width=12) (actual time=0.010..0.145 rows=38 loops=348)

  • Index Cond: ((date_taken = ped.date_taken) AND (fk_employee = ped.fk_employee))
9.          

CTE total_minutes_by_manufacturing_cell

10. 5.253 177.687 ↓ 1.1 37 1

HashAggregate (cost=8,049.83..8,050.27 rows=35 width=20) (actual time=177.679..177.687 rows=37 loops=1)

  • Group Key: mo.fk_manufacturing_cell, p_1.date_taken
11. 5.664 172.434 ↓ 25.3 10,456 1

Nested Loop (cost=1,985.86..8,045.69 rows=414 width=20) (actual time=110.302..172.434 rows=10,456 loops=1)

12. 4.926 145.858 ↓ 25.3 10,456 1

Hash Join (cost=1,985.58..7,917.52 rows=414 width=20) (actual time=110.271..145.858 rows=10,456 loops=1)

  • Hash Cond: (p_1.fk_operation = m.fk_operation)
13. 6.395 125.124 ↓ 24.8 11,553 1

Merge Join (cost=647.45..6,572.34 rows=465 width=20) (actual time=94.354..125.124 rows=11,553 loops=1)

  • Merge Cond: ((p_1.date_taken = mme.date_taken) AND (p_1.fk_employee = mme.fk_employee))
14. 22.265 22.265 ↓ 1.6 11,553 1

Index Scan using production_date_taken_fk_employee_idx on production p_1 (cost=0.43..5,826.20 rows=7,410 width=16) (actual time=0.126..22.265 rows=11,553 loops=1)

  • Index Cond: ((date_taken >= '2019-04-01'::date) AND (date_taken <= '2019-04-03'::date) AND (fk_employee IS NOT NULL))
  • Filter: (((type_special)::text <> 'bad_quality'::text) AND ((type_special)::text <> 'reprocess_quality'::text) AND ((type_special)::text <> 'reprocess_quality_audit'::text))
  • Rows Removed by Filter: 1838
15. 2.382 96.464 ↓ 1.5 11,523 1

Sort (cost=647.02..666.16 rows=7,656 width=16) (actual time=94.223..96.464 rows=11,523 loops=1)

  • Sort Key: mme.date_taken, mme.fk_employee
  • Sort Method: quicksort Memory: 41kB
16. 94.082 94.082 ↑ 22.0 348 1

CTE Scan on max_minutes_employees mme (cost=0.00..153.12 rows=7,656 width=16) (actual time=93.776..94.082 rows=348 loops=1)

17. 6.671 15.808 ↓ 1.0 30,689 1

Hash (cost=955.28..955.28 rows=30,628 width=8) (actual time=15.808..15.808 rows=30,689 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1455kB
18. 9.137 9.137 ↓ 1.0 30,689 1

Seq Scan on mcell_operation_choosen m (cost=0.00..955.28 rows=30,628 width=8) (actual time=0.005..9.137 rows=30,689 loops=1)

19. 20.912 20.912 ↑ 1.0 1 10,456

Index Scan using pk_manufacturing_cell_operation on manufacturing_cell_operation mo (cost=0.28..0.30 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=10,456)

  • Index Cond: (id = m.fk_manufacturing_cell_operation)
20.          

CTE incentives_daily

21. 0.052 1.856 ↓ 1.5 30 1

Unique (cost=321.75..323.19 rows=20 width=12) (actual time=1.779..1.856 rows=30 loops=1)

22. 0.125 1.804 ↓ 1.5 283 1

Sort (cost=321.75..322.23 rows=192 width=12) (actual time=1.777..1.804 rows=283 loops=1)

  • Sort Key: production_incentives_by_mcell.fk_manufacturing_cell, production_incentives_by_mcell.date_taken
  • Sort Method: quicksort Memory: 38kB
23. 1.415 1.679 ↓ 1.5 283 1

Bitmap Heap Scan on production_incentives_by_mcell (cost=101.38..314.47 rows=192 width=12) (actual time=0.652..1.679 rows=283 loops=1)

  • Recheck Cond: ((date_taken >= '2019-04-01'::date) AND (date_taken <= '2019-04-03'::date))
  • Filter: (fk_domain = 1)
  • Heap Blocks: exact=618
24. 0.264 0.264 ↓ 11.1 2,135 1

Bitmap Index Scan on unique_production_incentives_by_mcell (cost=0.00..101.33 rows=192 width=0) (actual time=0.264..0.264 rows=2,135 loops=1)

  • Index Cond: ((date_taken >= '2019-04-01'::date) AND (date_taken <= '2019-04-03'::date))
25.          

CTE incentives_daily_total

26. 0.029 1.893 ↓ 1.5 30 1

HashAggregate (cost=0.60..0.80 rows=20 width=12) (actual time=1.886..1.893 rows=30 loops=1)

  • Group Key: incentives_daily.fk_manufacturing_cell, incentives_daily.date_taken
27. 1.864 1.864 ↓ 1.5 30 1

CTE Scan on incentives_daily (cost=0.00..0.40 rows=20 width=12) (actual time=1.780..1.864 rows=30 loops=1)

28.          

CTE incentives_hour

29. 0.141 5.496 ↓ 1.5 30 1

HashAggregate (cost=316.39..316.59 rows=20 width=12) (actual time=5.486..5.496 rows=30 loops=1)

  • Group Key: production_incentives_by_mcell_1.fk_manufacturing_cell, production_incentives_by_mcell_1.date_taken
30. 4.779 5.355 ↓ 1.5 283 1

Bitmap Heap Scan on production_incentives_by_mcell production_incentives_by_mcell_1 (cost=101.38..314.47 rows=192 width=12) (actual time=1.672..5.355 rows=283 loops=1)

  • Recheck Cond: ((date_taken >= '2019-04-01'::date) AND (date_taken <= '2019-04-03'::date))
  • Filter: (fk_domain = 1)
  • Heap Blocks: exact=618
31. 0.576 0.576 ↓ 11.1 2,135 1

Bitmap Index Scan on unique_production_incentives_by_mcell (cost=0.00..101.33 rows=192 width=0) (actual time=0.576..0.576 rows=2,135 loops=1)

  • Index Cond: ((date_taken >= '2019-04-01'::date) AND (date_taken <= '2019-04-03'::date))
32.          

CTE incentives_total

33. 0.043 7.478 ↓ 1.5 30 1

Hash Full Join (cost=0.70..1.31 rows=20 width=16) (actual time=7.438..7.478 rows=30 loops=1)

  • Hash Cond: ((incentives_daily_total.fk_manufacturing_cell = incentives_hour.fk_manufacturing_cell) AND (incentives_daily_total.date_taken = incentives_hour.date_taken))
34. 1.903 1.903 ↓ 1.5 30 1

CTE Scan on incentives_daily_total (cost=0.00..0.40 rows=20 width=12) (actual time=1.888..1.903 rows=30 loops=1)

35. 0.023 5.532 ↓ 1.5 30 1

Hash (cost=0.40..0.40 rows=20 width=12) (actual time=5.532..5.532 rows=30 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
36. 5.509 5.509 ↓ 1.5 30 1

CTE Scan on incentives_hour (cost=0.00..0.40 rows=20 width=12) (actual time=5.488..5.509 rows=30 loops=1)

37.          

CTE incentive_day

38. 4.903 26,749.387 ↓ 302.0 302 1

GroupAggregate (cost=6,983.72..6,983.80 rows=1 width=69) (actual time=26,742.951..26,749.387 rows=302 loops=1)

  • Group Key: mo_1.fk_manufacturing_cell, p_2.fk_employee, incentives_total.incentives, e.name, e.last_name, mcell.name, t.minutes, p_2.date_taken
39.          

Initplan (forGroupAggregate)

40. 0.024 0.024 ↑ 1.0 1 1

CTE Scan on calculated_group_incentives_but_individual_with (cost=0.00..0.02 rows=1 width=32) (actual time=0.017..0.024 rows=1 loops=1)

41. 15.170 26,744.460 ↓ 7,327.0 7,327 1

Sort (cost=6,983.70..6,983.71 rows=1 width=69) (actual time=26,742.837..26,744.460 rows=7,327 loops=1)

  • Sort Key: mo_1.fk_manufacturing_cell, p_2.fk_employee, incentives_total.incentives, e.name, e.last_name, mcell.name, t.minutes, p_2.date_taken
  • Sort Method: quicksort Memory: 1223kB
42. 8.839 26,729.290 ↓ 7,327.0 7,327 1

Nested Loop (cost=5,530.70..6,983.69 rows=1 width=69) (actual time=22,562.961..26,729.290 rows=7,327 loops=1)

43. 353.685 26,698.470 ↓ 7,327.0 7,327 1

Nested Loop (cost=5,530.43..6,983.39 rows=1 width=51) (actual time=22,562.944..26,698.470 rows=7,327 loops=1)

  • Join Filter: ((t.date_taken = mme_1.date_taken) AND (p_2.fk_employee = mme_1.fk_employee))
  • Rows Removed by Join Filter: 2542469
44. 3,340.854 25,890.511 ↓ 7,327.0 7,327 1

Hash Join (cost=5,530.43..6,715.43 rows=1 width=47) (actual time=22,562.924..25,890.511 rows=7,327 loops=1)

  • Hash Cond: ((m_1.fk_operation = p_2.fk_operation) AND (m_1.fk_manufacturing_cell_operation = mo_1.id))
45. 4.173 4.173 ↓ 1.0 30,689 1

Seq Scan on mcell_operation_choosen m_1 (cost=0.00..955.28 rows=30,628 width=8) (actual time=0.017..4.173 rows=30,689 loops=1)

46. 6,024.432 22,545.484 ↓ 17,373.2 12,804,054 1

Hash (cost=5,519.37..5,519.37 rows=737 width=55) (actual time=22,545.484..22,545.484 rows=12,804,054 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1024 (originally 1) Memory Usage: 2445kB
47. 3,408.221 16,521.052 ↓ 19,284.9 14,212,942 1

Merge Join (cost=248.74..5,519.37 rows=737 width=55) (actual time=187.214..16,521.052 rows=14,212,942 loops=1)

  • Merge Cond: (mcell.id = mo_1.fk_manufacturing_cell)
48. 0.038 0.038 ↑ 2.5 11 1

Index Scan using pk_manufacturing_cell on manufacturing_cell mcell (cost=0.14..6.04 rows=27 width=15) (actual time=0.006..0.038 rows=11 loops=1)

49. 6,032.958 13,112.793 ↓ 19,258.7 14,212,942 1

Materialize (cost=248.61..6,272.21 rows=738 width=52) (actual time=187.199..13,112.793 rows=14,212,942 loops=1)

50. 3,933.085 7,079.835 ↓ 19,258.7 14,212,942 1

Merge Join (cost=248.61..6,270.37 rows=738 width=52) (actual time=187.195..7,079.835 rows=14,212,942 loops=1)

  • Merge Cond: (t.fk_manufacturing_cell = mo_1.fk_manufacturing_cell)
51. 58.519 1,220.333 ↓ 16,611.7 99,670 1

Nested Loop (cost=4.64..6,006.93 rows=6 width=44) (actual time=185.330..1,220.333 rows=99,670 loops=1)

  • Join Filter: (t.date_taken = p_2.date_taken)
  • Rows Removed by Join Filter: 199340
52. 0.156 185.494 ↓ 30.0 30 1

Merge Join (cost=2.43..2.85 rows=1 width=28) (actual time=185.264..185.494 rows=30 loops=1)

  • Merge Cond: ((t.fk_manufacturing_cell = incentives_total.fk_manufacturing_cell) AND (t.date_taken = incentives_total.date_taken))
53. 0.063 177.773 ↑ 1.1 31 1

Sort (cost=1.60..1.69 rows=35 width=16) (actual time=177.744..177.773 rows=31 loops=1)

  • Sort Key: t.fk_manufacturing_cell, t.date_taken
  • Sort Method: quicksort Memory: 26kB
54. 177.710 177.710 ↓ 1.1 37 1

CTE Scan on total_minutes_by_manufacturing_cell t (cost=0.00..0.70 rows=35 width=16) (actual time=177.682..177.710 rows=37 loops=1)

55. 0.073 7.565 ↓ 1.5 30 1

Sort (cost=0.83..0.88 rows=20 width=12) (actual time=7.516..7.565 rows=30 loops=1)

  • Sort Key: incentives_total.fk_manufacturing_cell, incentives_total.date_taken
  • Sort Method: quicksort Memory: 26kB
56. 7.492 7.492 ↓ 1.5 30 1

CTE Scan on incentives_total (cost=0.00..0.40 rows=20 width=12) (actual time=7.440..7.492 rows=30 loops=1)

57. 163.387 976.320 ↓ 1.9 9,967 30

Hash Left Join (cost=2.21..5,937.53 rows=5,324 width=16) (actual time=0.023..32.544 rows=9,967 loops=30)

  • Hash Cond: (p_2.fk_downtimes = d.id)
  • Join Filter: ((p_2.date_taken >= '2019-04-01'::date) AND (p_2.date_taken <= '2019-04-03'::date) AND (p_2.fk_domain = 1) AND ((p_2.type_special)::text <> 'bad_quality'::text) AND ((p_2.type_special)::text <> 'reprocess_quality'::text) AND ((p_2.type_special)::text <> 'reprocess_quality_audit'::text) AND ((p_2.type_special)::text <> 'normal_without_efficiency'::text))
  • Filter: (d.add_to_efficiency OR (d.id IS NULL))
  • Rows Removed by Filter: 1440
58. 812.910 812.910 ↓ 1.5 11,407 30

Index Scan using production_date_taken_fk_employee_idx on production p_2 (cost=0.43..5,881.61 rows=7,454 width=33) (actual time=0.022..27.097 rows=11,407 loops=30)

  • Index Cond: ((date_taken >= '2019-04-01'::date) AND (date_taken <= '2019-04-03'::date))
  • Filter: (((type_special)::text <> 'bad_quality'::text) AND ((type_special)::text <> 'reprocess_quality'::text) AND ((type_special)::text <> 'reprocess_quality_audit'::text) AND ((type_special)::text <> 'normal_without_efficiency'::text) AND (fk_domain = 1))
  • Rows Removed by Filter: 1984
59. 0.012 0.023 ↑ 1.0 35 1

Hash (cost=1.35..1.35 rows=35 width=5) (actual time=0.023..0.023 rows=35 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
60. 0.011 0.011 ↑ 1.0 35 1

Seq Scan on downtimes d (cost=0.00..1.35 rows=35 width=5) (actual time=0.005..0.011 rows=35 loops=1)

61. 1,925.380 1,926.417 ↓ 4,744.0 14,212,943 1

Sort (cost=243.96..251.45 rows=2,996 width=8) (actual time=1.861..1,926.417 rows=14,212,943 loops=1)

  • Sort Key: mo_1.fk_manufacturing_cell
  • Sort Method: quicksort Memory: 234kB
62. 1.037 1.037 ↑ 1.0 2,932 1

Seq Scan on manufacturing_cell_operation mo_1 (cost=0.00..70.96 rows=2,996 width=8) (actual time=0.011..1.037 rows=2,932 loops=1)

63. 454.274 454.274 ↑ 22.0 348 7,327

CTE Scan on max_minutes_employees mme_1 (cost=0.00..153.12 rows=7,656 width=16) (actual time=0.000..0.062 rows=348 loops=7,327)

64. 21.981 21.981 ↑ 1.0 1 7,327

Index Scan using employee_pkey on employee e (cost=0.27..0.29 rows=1 width=26) (actual time=0.003..0.003 rows=1 loops=7,327)

  • Index Cond: (id = mme_1.fk_employee)
Planning time : 36.037 ms