explain.depesz.com

PostgreSQL's explain analyze made readable

Result: h77

Settings
# exclusive inclusive rows x rows loops node
1. 748.883 748.883 ↓ 302.0 302 1

CTE Scan on incentive_day (cost=31,839.24..31,839.26 rows=1 width=176) (actual time=742.787..748.883 rows=302 loops=1)

2.          

CTE calculated_group_incentives_but_individual_with

3. 0.017 0.017 ↑ 1.0 1 1

Seq Scan on base_configuration (cost=0.00..1.48 rows=1 width=7) (actual time=0.010..0.017 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. 8.097 86.016 ↑ 22.0 348 1

HashAggregate (cost=4,898.45..4,994.15 rows=7,656 width=16) (actual time=85.868..86.016 rows=348 loops=1)

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

Nested Loop (cost=0.84..4,802.75 rows=7,656 width=16) (actual time=0.104..77.919 rows=13,391 loops=1)

7. 27.032 27.032 ↑ 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.091..27.032 rows=348 loops=1)

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

Index Scan using production_date_taken_fk_employee_idx on production p (cost=0.43..11.60 rows=13 width=12) (actual time=0.008..0.130 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. 4.473 57.303 ↑ 2.0 37 1

HashAggregate (cost=13,544.38..13,545.31 rows=75 width=20) (actual time=57.290..57.303 rows=37 loops=1)

  • Group Key: mo.fk_manufacturing_cell, p_1.date_taken
11. 3.428 52.830 ↓ 11.7 10,456 1

Hash Join (cost=2,093.99..13,535.47 rows=891 width=20) (actual time=17.445..52.830 rows=10,456 loops=1)

  • Hash Cond: (m.fk_manufacturing_cell_operation = mo.id)
12. 4.087 47.973 ↓ 11.7 10,456 1

Hash Join (cost=1,985.58..13,414.80 rows=891 width=20) (actual time=15.998..47.973 rows=10,456 loops=1)

  • Hash Cond: (p_1.fk_operation = m.fk_operation)
13. 5.456 28.452 ↓ 11.6 11,553 1

Merge Join (cost=647.45..12,061.53 rows=997 width=20) (actual time=0.467..28.452 rows=11,553 loops=1)

  • Merge Cond: ((p_1.date_taken = mme.date_taken) AND (p_1.fk_employee = mme.fk_employee))
14. 20.675 20.675 ↑ 1.4 11,553 1

Index Scan using production_date_taken_fk_employee_idx on production p_1 (cost=0.43..11,266.14 rows=16,197 width=16) (actual time=0.023..20.675 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.010 2.321 ↓ 1.5 11,523 1

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

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

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

17. 6.249 15.434 ↓ 1.0 30,689 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 1455kB
18. 9.185 9.185 ↓ 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.003..9.185 rows=30,689 loops=1)

19. 0.546 1.429 ↑ 1.0 2,932 1

Hash (cost=70.96..70.96 rows=2,996 width=8) (actual time=1.429..1.429 rows=2,932 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 146kB
20. 0.883 0.883 ↑ 1.0 2,932 1

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

21.          

CTE incentives_daily

22. 0.047 2.788 ↓ 2.1 30 1

Unique (cost=233.75..234.79 rows=14 width=12) (actual time=2.713..2.788 rows=30 loops=1)

23. 0.134 2.741 ↓ 2.1 283 1

Sort (cost=233.75..234.10 rows=138 width=12) (actual time=2.711..2.741 rows=283 loops=1)

  • Sort Key: production_incentives_by_mcell.fk_manufacturing_cell, production_incentives_by_mcell.date_taken
  • Sort Method: quicksort Memory: 38kB
24. 2.169 2.607 ↓ 2.1 283 1

Bitmap Heap Scan on production_incentives_by_mcell (cost=75.53..228.85 rows=138 width=12) (actual time=1.191..2.607 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=786
25. 0.438 0.438 ↓ 20.1 2,773 1

Bitmap Index Scan on unique_production_incentives_by_mcell (cost=0.00..75.49 rows=138 width=0) (actual time=0.438..0.438 rows=2,773 loops=1)

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

CTE incentives_daily_total

27. 0.028 2.825 ↓ 2.1 30 1

HashAggregate (cost=0.42..0.56 rows=14 width=12) (actual time=2.815..2.825 rows=30 loops=1)

  • Group Key: incentives_daily.fk_manufacturing_cell, incentives_daily.date_taken
28. 2.797 2.797 ↓ 2.1 30 1

CTE Scan on incentives_daily (cost=0.00..0.28 rows=14 width=12) (actual time=2.714..2.797 rows=30 loops=1)

29.          

CTE incentives_hour

30. 0.125 7.568 ↓ 2.1 30 1

HashAggregate (cost=230.23..230.37 rows=14 width=12) (actual time=7.560..7.568 rows=30 loops=1)

  • Group Key: production_incentives_by_mcell_1.fk_manufacturing_cell, production_incentives_by_mcell_1.date_taken
31. 6.779 7.443 ↓ 2.1 283 1

Bitmap Heap Scan on production_incentives_by_mcell production_incentives_by_mcell_1 (cost=75.53..228.85 rows=138 width=12) (actual time=2.378..7.443 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=786
32. 0.664 0.664 ↓ 20.1 2,773 1

Bitmap Index Scan on unique_production_incentives_by_mcell (cost=0.00..75.49 rows=138 width=0) (actual time=0.664..0.664 rows=2,773 loops=1)

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

CTE incentives_total

34. 0.027 10.466 ↓ 2.1 30 1

Hash Full Join (cost=0.49..0.92 rows=14 width=16) (actual time=10.428..10.466 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))
35. 2.839 2.839 ↓ 2.1 30 1

CTE Scan on incentives_daily_total (cost=0.00..0.28 rows=14 width=12) (actual time=2.817..2.839 rows=30 loops=1)

36. 0.015 7.600 ↓ 2.1 30 1

Hash (cost=0.28..0.28 rows=14 width=12) (actual time=7.600..7.600 rows=30 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
37. 7.585 7.585 ↓ 2.1 30 1

CTE Scan on incentives_hour (cost=0.00..0.28 rows=14 width=12) (actual time=7.562..7.585 rows=30 loops=1)

38.          

CTE incentive_day

39. 4.888 748.676 ↓ 302.0 302 1

GroupAggregate (cost=12,831.58..12,831.66 rows=1 width=69) (actual time=742.784..748.676 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
40.          

Initplan (forGroupAggregate)

41. 0.019 0.019 ↑ 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.012..0.019 rows=1 loops=1)

42. 13.616 743.769 ↓ 7,327.0 7,327 1

Sort (cost=12,831.56..12,831.57 rows=1 width=69) (actual time=742.677..743.769 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
43. 8.895 730.153 ↓ 7,327.0 7,327 1

Nested Loop (cost=11,761.31..12,831.55 rows=1 width=69) (actual time=250.702..730.153 rows=7,327 loops=1)

  • Join Filter: (t.fk_manufacturing_cell = mcell.id)
44. 15.491 713.931 ↓ 7,327.0 7,327 1

Nested Loop (cost=11,761.18..12,831.39 rows=1 width=66) (actual time=250.694..713.931 rows=7,327 loops=1)

  • Join Filter: (t.fk_manufacturing_cell = mo_1.fk_manufacturing_cell)
  • Rows Removed by Join Filter: 81433
45. 40.623 520.920 ↓ 88,760.0 88,760 1

Nested Loop (cost=11,760.90..12,831.07 rows=1 width=66) (actual time=250.663..520.920 rows=88,760 loops=1)

46. 51.149 302.777 ↓ 88,760.0 88,760 1

Hash Join (cost=11,760.62..12,830.77 rows=1 width=48) (actual time=250.635..302.777 rows=88,760 loops=1)

  • Hash Cond: (m_1.fk_operation = p_2.fk_operation)
47. 3.863 3.863 ↓ 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.028..3.863 rows=30,689 loops=1)

48. 31.340 247.765 ↓ 89,790.0 89,790 1

Hash (cost=11,760.61..11,760.61 rows=1 width=48) (actual time=247.765..247.765 rows=89,790 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 4 (originally 1) Memory Usage: 2366kB
49. 31.134 216.425 ↓ 99,670.0 99,670 1

Hash Join (cost=187.57..11,760.61 rows=1 width=48) (actual time=155.869..216.425 rows=99,670 loops=1)

  • Hash Cond: ((p_2.date_taken = t.date_taken) AND (p_2.fk_employee = mme_1.fk_employee))
50. 4.731 29.623 ↑ 1.2 9,967 1

Hash Left Join (cost=2.21..11,487.97 rows=11,633 width=16) (actual time=0.177..29.623 rows=9,967 loops=1)

  • 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): (...)
  • Filter: (d.add_to_efficiency OR (d.id IS NULL))
  • Rows Removed by Filter: 1440
51. 24.849 24.849 ↑ 1.4 11,407 1

Index Scan using production_date_taken_fk_employee_idx on production p_2 (cost=0.43..11,367.77 rows=16,286 width=33) (actual time=0.120..24.849 rows=11,407 loops=1)

  • 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) (...)
  • Rows Removed by Filter: 1984
52. 0.017 0.043 ↑ 1.0 35 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
53. 0.026 0.026 ↑ 1.0 35 1

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

54. 0.979 155.668 ↓ 91.6 3,480 1

Hash (cost=184.78..184.78 rows=38 width=44) (actual time=155.668..155.668 rows=3,480 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 304kB
55. 0.886 154.689 ↓ 91.6 3,480 1

Hash Join (cost=2.58..184.78 rows=38 width=44) (actual time=153.760..154.689 rows=3,480 loops=1)

  • Hash Cond: (mme_1.date_taken = t.date_taken)
56. 85.936 85.936 ↑ 22.0 348 1

CTE Scan on max_minutes_employees mme_1 (cost=0.00..153.12 rows=7,656 width=16) (actual time=85.871..85.936 rows=348 loops=1)

57. 0.009 67.867 ↓ 30.0 30 1

Hash (cost=2.56..2.56 rows=1 width=28) (actual time=67.867..67.867 rows=30 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
58. 0.038 67.858 ↓ 30.0 30 1

Hash Join (cost=0.49..2.56 rows=1 width=28) (actual time=67.814..67.858 rows=30 loops=1)

  • Hash Cond: ((t.date_taken = incentives_total.date_taken) AND (t.fk_manufacturing_cell = incentives_total.fk_manufacturing_cell))
59. 57.323 57.323 ↑ 2.0 37 1

CTE Scan on total_minutes_by_manufacturing_cell t (cost=0.00..1.50 rows=75 width=16) (actual time=57.293..57.323 rows=37 loops=1)

60. 0.013 10.497 ↓ 2.1 30 1

Hash (cost=0.28..0.28 rows=14 width=12) (actual time=10.497..10.497 rows=30 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
61. 10.484 10.484 ↓ 2.1 30 1

CTE Scan on incentives_total (cost=0.00..0.28 rows=14 width=12) (actual time=10.431..10.484 rows=30 loops=1)

62. 177.520 177.520 ↑ 1.0 1 88,760

Index Scan using employee_pkey on employee e (cost=0.27..0.29 rows=1 width=26) (actual time=0.001..0.002 rows=1 loops=88,760)

  • Index Cond: (id = mme_1.fk_employee)
63. 177.520 177.520 ↑ 1.0 1 88,760

Index Scan using pk_manufacturing_cell_operation on manufacturing_cell_operation mo_1 (cost=0.28..0.30 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=88,760)

  • Index Cond: (id = m_1.fk_manufacturing_cell_operation)
64. 7.327 7.327 ↑ 1.0 1 7,327

Index Scan using pk_manufacturing_cell on manufacturing_cell mcell (cost=0.14..0.16 rows=1 width=15) (actual time=0.001..0.001 rows=1 loops=7,327)

  • Index Cond: (id = mo_1.fk_manufacturing_cell)