explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iCVx

Settings
# exclusive inclusive rows x rows loops node
1. 0.008 1,083.062 ↑ 3.8 6 1

Nested Loop (cost=98,399.67..98,451.43 rows=23 width=44) (actual time=1,083.019..1,083.062 rows=6 loops=1)

2. 0.014 1,083.030 ↑ 3.8 6 1

Nested Loop (cost=98,399.39..98,444.16 rows=23 width=44) (actual time=1,083.003..1,083.030 rows=6 loops=1)

3. 0.016 1,082.962 ↑ 3.8 6 1

GroupAggregate (cost=98,399.11..98,399.69 rows=23 width=28) (actual time=1,082.955..1,082.962 rows=6 loops=1)

  • Group Key: c.id, c.emp_id, c.type_id
4. 0.027 1,082.946 ↑ 1.8 13 1

Sort (cost=98,399.11..98,399.17 rows=23 width=28) (actual time=1,082.944..1,082.946 rows=13 loops=1)

  • Sort Key: c.id, c.emp_id, c.type_id
  • Sort Method: quicksort Memory: 26kB
5. 0.009 1,082.919 ↑ 1.8 13 1

Append (cost=1.35..98,398.36 rows=23 width=28) (actual time=0.063..1,082.919 rows=13 loops=1)

6. 0.049 0.117 ↑ 1.0 6 1

Hash Join (cost=1.35..13.59 rows=6 width=28) (actual time=0.062..0.117 rows=6 loops=1)

  • Hash Cond: (c.type_id = ct.id)
7. 0.048 0.048 ↑ 1.0 6 1

Index Scan using hr_holidays_counter_pkey on hr_holidays_counter c (cost=0.28..12.48 rows=6 width=28) (actual time=0.026..0.048 rows=6 loops=1)

  • Index Cond: (id = ANY ('{10,11,12,13,14,15}'::integer[]))
8. 0.007 0.020 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=4) (actual time=0.019..0.020 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
9. 0.013 0.013 ↑ 1.0 3 1

Seq Scan on hr_holidays_counter_type ct (cost=0.00..1.03 rows=3 width=4) (actual time=0.010..0.013 rows=3 loops=1)

10. 0.027 329.573 ↓ 6.0 6 1

Subquery Scan on *SELECT* 2 (cost=129.52..5,746.70 rows=1 width=28) (actual time=83.146..329.573 rows=6 loops=1)

11. 0.091 329.546 ↓ 6.0 6 1

Nested Loop (cost=129.52..5,746.68 rows=1 width=76) (actual time=83.134..329.546 rows=6 loops=1)

12.          

Initplan (forNested Loop)

13. 0.030 0.030 ↑ 1.0 1 1

Seq Scan on hr_period (cost=0.00..1.20 rows=1 width=4) (actual time=0.025..0.030 rows=1 loops=1)

  • Filter: ((start_date <= (now())::date) AND (end_date >= (now())::date))
  • Rows Removed by Filter: 7
14. 0.010 0.010 ↑ 1.0 1 1

Seq Scan on hr_period hr_period_1 (cost=0.00..1.20 rows=1 width=4) (actual time=0.007..0.010 rows=1 loops=1)

  • Filter: ((start_date <= (now())::date) AND (end_date >= (now())::date))
  • Rows Removed by Filter: 7
15. 0.319 329.385 ↓ 6.0 6 1

Hash Join (cost=126.99..5,744.09 rows=1 width=29) (actual time=83.041..329.385 rows=6 loops=1)

  • Hash Cond: (ts.employee_id = hhc2.emp_id)
  • Join Filter: (aal.date >= (SubPlan 3))
  • Rows Removed by Join Filter: 99
16. 32.276 321.454 ↓ 35.0 105 1

Nested Loop (cost=5.88..5,622.98 rows=3 width=37) (actual time=10.378..321.454 rows=105 loops=1)

  • Join Filter: (ct_1.product_id = aal.product_id)
  • Rows Removed by Join Filter: 55710
17. 29.017 65.918 ↓ 5.4 55,815 1

Nested Loop (cost=5.45..444.70 rows=10,407 width=33) (actual time=0.199..65.918 rows=55,815 loops=1)

  • Join Filter: (c_1.emp_id = ts.employee_id)
18. 0.042 0.341 ↓ 2.5 10 1

Nested Loop (cost=5.02..26.45 rows=4 width=25) (actual time=0.178..0.341 rows=10 loops=1)

19. 0.023 0.239 ↓ 2.5 10 1

Nested Loop (cost=4.74..17.19 rows=4 width=17) (actual time=0.161..0.239 rows=10 loops=1)

  • Join Filter: (ct_1.counter_type_id = c_1.type_id)
  • Rows Removed by Join Filter: 20
20. 0.036 0.036 ↑ 1.0 6 1

Index Scan using hr_holidays_counter_pkey on hr_holidays_counter c_1 (cost=0.28..12.48 rows=6 width=12) (actual time=0.010..0.036 rows=6 loops=1)

  • Index Cond: (id = ANY ('{10,11,12,13,14,15}'::integer[]))
21. 0.024 0.180 ↓ 2.5 5 6

Materialize (cost=4.46..4.53 rows=2 width=9) (actual time=0.025..0.030 rows=5 loops=6)

22. 0.004 0.156 ↓ 2.5 5 1

Subquery Scan on ct_1 (cost=4.46..4.52 rows=2 width=9) (actual time=0.143..0.156 rows=5 loops=1)

23. 0.019 0.152 ↓ 2.5 5 1

GroupAggregate (cost=4.46..4.50 rows=2 width=9) (actual time=0.141..0.152 rows=5 loops=1)

  • Group Key: atc_rel.hct_id, (COALESCE(wrat.second_timesheet_product_id, wrat.default_product_id))
24. 0.014 0.133 ↓ 3.0 6 1

Sort (cost=4.46..4.47 rows=2 width=12) (actual time=0.130..0.133 rows=6 loops=1)

  • Sort Key: atc_rel.hct_id, (COALESCE(wrat.second_timesheet_product_id, wrat.default_product_id))
  • Sort Method: quicksort Memory: 25kB
25. 0.019 0.119 ↓ 3.0 6 1

Merge Join (cost=2.17..4.45 rows=2 width=12) (actual time=0.056..0.119 rows=6 loops=1)

  • Merge Cond: (wrat.id = atc_rel.wrat_id)
26. 0.083 0.083 ↑ 1.5 23 1

Index Scan using hr_weekly_report_activity_type_pkey on hr_weekly_report_activity_type wrat (cost=0.14..7.86 rows=35 width=12) (actual time=0.015..0.083 rows=23 loops=1)

  • Filter: ((line_type)::text = 'absence'::text)
  • Rows Removed by Filter: 41
27. 0.011 0.017 ↑ 1.0 6 1

Sort (cost=1.14..1.15 rows=6 width=8) (actual time=0.015..0.017 rows=6 loops=1)

  • Sort Key: atc_rel.wrat_id
  • Sort Method: quicksort Memory: 25kB
28. 0.006 0.006 ↑ 1.0 6 1

Seq Scan on hr_week_act_type_hol_count_type_rel atc_rel (cost=0.00..1.06 rows=6 width=8) (actual time=0.004..0.006 rows=6 loops=1)

29. 0.060 0.060 ↑ 1.0 1 10

Index Scan using hr_employee_pkey on hr_employee emp (cost=0.28..2.31 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=10)

  • Index Cond: (id = c_1.emp_id)
30. 36.560 36.560 ↓ 2.1 5,582 10

Index Scan using idx_timesheet_line_employee_id on hr_analytic_timesheet ts (cost=0.43..71.97 rows=2,607 width=8) (actual time=0.014..3.656 rows=5,582 loops=10)

  • Index Cond: (employee_id = emp.id)
31. 223.260 223.260 ↑ 1.0 1 55,815

Index Scan using account_analytic_line_pkey on account_analytic_line aal (cost=0.43..0.49 rows=1 width=20) (actual time=0.003..0.004 rows=1 loops=55,815)

  • Index Cond: (id = ts.line_id)
32. 0.378 3.517 ↑ 1.0 1,111 1

Hash (cost=107.21..107.21 rows=1,111 width=8) (actual time=3.517..3.517 rows=1,111 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 60kB
33. 2.485 3.139 ↑ 1.0 1,111 1

HashAggregate (cost=84.99..96.10 rows=1,111 width=8) (actual time=2.724..3.139 rows=1,111 loops=1)

  • Group Key: hhc2.emp_id
34. 0.654 0.654 ↑ 1.0 3,333 1

Seq Scan on hr_holidays_counter hhc2 (cost=0.00..68.33 rows=3,333 width=8) (actual time=0.009..0.654 rows=3,333 loops=1)

35.          

SubPlan (forHash Join)

36. 0.315 4.095 ↑ 1.0 1 105

Limit (cost=6.01..6.01 rows=1 width=4) (actual time=0.038..0.039 rows=1 loops=105)

37. 1.365 3.780 ↑ 87.0 1 105

Sort (cost=6.01..6.23 rows=87 width=4) (actual time=0.036..0.036 rows=1 loops=105)

  • Sort Key: hr_week_number.date_start
  • Sort Method: top-N heapsort Memory: 25kB
38. 2.415 2.415 ↑ 2.9 30 105

Index Scan using idx_week_number_validation_date on hr_week_number (cost=0.15..5.57 rows=87 width=4) (actual time=0.006..0.023 rows=30 loops=105)

  • Index Cond: (validation_date > (max(hhc2.effective_date)))
39. 0.030 0.030 ↑ 1.0 1 6

Index Scan using hr_working_schedule_type_pkey on hr_working_schedule_type wst (cost=0.14..0.15 rows=1 width=12) (actual time=0.005..0.005 rows=1 loops=6)

  • Index Cond: (id = emp.working_schedule_type_id)
40. 0.013 753.220 ↑ 16.0 1 1

Subquery Scan on *SELECT* 3 (cost=92,636.82..92,637.90 rows=16 width=28) (actual time=753.219..753.220 rows=1 loops=1)

41. 0.019 753.207 ↑ 16.0 1 1

GroupAggregate (cost=92,636.82..92,637.66 rows=16 width=80) (actual time=753.207..753.207 rows=1 loops=1)

  • Group Key: c_2.id, ar.emp_id, atc_rel_1.hct_id
42. 0.016 753.188 ↑ 20.0 2 1

Sort (cost=92,636.82..92,636.92 rows=40 width=76) (actual time=753.188..753.188 rows=2 loops=1)

  • Sort Key: c_2.id, ar.emp_id, atc_rel_1.hct_id
  • Sort Method: quicksort Memory: 25kB
43. 4.938 753.172 ↑ 20.0 2 1

Nested Loop (cost=90,879.83..92,635.75 rows=40 width=76) (actual time=745.135..753.172 rows=2 loops=1)

  • Join Filter: ((ard.date > (max(ts_1.date_aal))) AND (ar.emp_id = ts_1.employee_id))
  • Rows Removed by Join Filter: 29638
44. 0.000 732.426 ↓ 1.3 1,976 1

Finalize GroupAggregate (cost=90,825.80..91,201.27 rows=1,482 width=8) (actual time=728.840..732.426 rows=1,976 loops=1)

  • Group Key: ts_1.employee_id
45. 0.000 737.515 ↓ 1.9 5,627 1

Gather Merge (cost=90,825.80..91,171.63 rows=2,964 width=8) (actual time=728.829..737.515 rows=5,627 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
46. 2.679 2,169.882 ↓ 1.3 1,876 3

Sort (cost=89,825.78..89,829.48 rows=1,482 width=8) (actual time=723.091..723.294 rows=1,876 loops=3)

  • Sort Key: ts_1.employee_id
  • Sort Method: quicksort Memory: 137kB
  • Worker 0: Sort Method: quicksort Memory: 136kB
  • Worker 1: Sort Method: quicksort Memory: 136kB
47. 1,516.173 2,167.203 ↓ 1.3 1,876 3

Partial HashAggregate (cost=89,732.91..89,747.73 rows=1,482 width=8) (actual time=721.937..722.401 rows=1,876 loops=3)

  • Group Key: ts_1.employee_id
48. 651.030 651.030 ↑ 1.2 1,288,415 3

Parallel Seq Scan on hr_analytic_timesheet ts_1 (cost=0.00..81,684.27 rows=1,609,727 width=8) (actual time=0.037..217.010 rows=1,288,415 loops=3)

49. 2.704 15.808 ↑ 1.1 15 1,976

Materialize (cost=54.03..1,004.75 rows=16 width=80) (actual time=0.000..0.008 rows=15 loops=1,976)

50. 0.005 13.104 ↑ 1.1 15 1

Append (cost=54.03..1,004.67 rows=16 width=80) (actual time=0.216..13.104 rows=15 loops=1)

51. 0.029 6.735 ↑ 1.1 7 1

Hash Join (cost=54.03..502.00 rows=8 width=80) (actual time=0.214..6.735 rows=7 loops=1)

  • Hash Cond: ((ard.morning_type_id = atc_rel_1.wrat_id) AND (c_2.type_id = atc_rel_1.hct_id))
52.          

Initplan (forHash Join)

53. 0.015 0.015 ↑ 1.0 1 1

Seq Scan on hr_period hr_period_2 (cost=0.00..1.20 rows=1 width=4) (actual time=0.012..0.015 rows=1 loops=1)

  • Filter: ((start_date <= (now())::date) AND (end_date >= (now())::date))
  • Rows Removed by Filter: 7
54. 0.006 0.006 ↑ 1.0 1 1

Seq Scan on hr_period hr_period_3 (cost=0.00..1.20 rows=1 width=4) (actual time=0.004..0.006 rows=1 loops=1)

  • Filter: ((start_date <= (now())::date) AND (end_date >= (now())::date))
  • Rows Removed by Filter: 7
55. 2.524 6.670 ↑ 4.2 21 1

Hash Join (cost=50.48..497.94 rows=88 width=20) (actual time=0.154..6.670 rows=21 loops=1)

  • Hash Cond: (ard.absence_request_id = ar.id)
56. 4.064 4.064 ↑ 1.0 18,360 1

Seq Scan on cesbron_absence_request_date ard (cost=0.00..377.72 rows=18,360 width=12) (actual time=0.005..4.064 rows=18,360 loops=1)

  • Filter: morning
  • Rows Removed by Filter: 212
57. 0.005 0.082 ↑ 2.7 9 1

Hash (cost=50.18..50.18 rows=24 width=16) (actual time=0.081..0.082 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
58. 0.014 0.077 ↑ 2.7 9 1

Nested Loop (cost=0.56..50.18 rows=24 width=16) (actual time=0.040..0.077 rows=9 loops=1)

59. 0.033 0.033 ↑ 1.0 6 1

Index Scan using hr_holidays_counter_pkey on hr_holidays_counter c_2 (cost=0.28..12.48 rows=6 width=12) (actual time=0.023..0.033 rows=6 loops=1)

  • Index Cond: (id = ANY ('{10,11,12,13,14,15}'::integer[]))
60. 0.030 0.030 ↑ 2.5 2 6

Index Scan using cesbron_absence_request_emp_id_index on cesbron_absence_request ar (cost=0.28..6.23 rows=5 width=8) (actual time=0.004..0.005 rows=2 loops=6)

  • Index Cond: (emp_id = c_2.emp_id)
  • Filter: ((state)::text = ANY ('{validated,confirmed}'::text[]))
61. 0.005 0.015 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=8) (actual time=0.015..0.015 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
62. 0.010 0.010 ↑ 1.0 6 1

Seq Scan on hr_week_act_type_hol_count_type_rel atc_rel_1 (cost=0.00..1.06 rows=6 width=8) (actual time=0.009..0.010 rows=6 loops=1)

63. 0.022 6.364 ↑ 1.0 8 1

Hash Join (cost=54.03..502.42 rows=8 width=80) (actual time=0.186..6.364 rows=8 loops=1)

  • Hash Cond: ((ard_1.afternoon_type_id = atc_rel_2.wrat_id) AND (c_3.type_id = atc_rel_2.hct_id))
64.          

Initplan (forHash Join)

65. 0.010 0.010 ↑ 1.0 1 1

Seq Scan on hr_period hr_period_4 (cost=0.00..1.20 rows=1 width=4) (actual time=0.007..0.010 rows=1 loops=1)

  • Filter: ((start_date <= (now())::date) AND (end_date >= (now())::date))
  • Rows Removed by Filter: 7
66. 0.006 0.006 ↑ 1.0 1 1

Seq Scan on hr_period hr_period_5 (cost=0.00..1.20 rows=1 width=4) (actual time=0.004..0.006 rows=1 loops=1)

  • Filter: ((start_date <= (now())::date) AND (end_date >= (now())::date))
  • Rows Removed by Filter: 7
67. 2.830 6.316 ↑ 3.7 24 1

Hash Join (cost=50.48..498.35 rows=89 width=20) (actual time=0.143..6.316 rows=24 loops=1)

  • Hash Cond: (ard_1.absence_request_id = ar_1.id)
68. 3.411 3.411 ↑ 1.0 18,470 1

Seq Scan on cesbron_absence_request_date ard_1 (cost=0.00..377.72 rows=18,470 width=12) (actual time=0.003..3.411 rows=18,470 loops=1)

  • Filter: afternoon
  • Rows Removed by Filter: 102
69. 0.007 0.075 ↑ 2.7 9 1

Hash (cost=50.18..50.18 rows=24 width=16) (actual time=0.075..0.075 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
70. 0.015 0.068 ↑ 2.7 9 1

Nested Loop (cost=0.56..50.18 rows=24 width=16) (actual time=0.032..0.068 rows=9 loops=1)

71. 0.023 0.023 ↑ 1.0 6 1

Index Scan using hr_holidays_counter_pkey on hr_holidays_counter c_3 (cost=0.28..12.48 rows=6 width=12) (actual time=0.013..0.023 rows=6 loops=1)

  • Index Cond: (id = ANY ('{10,11,12,13,14,15}'::integer[]))
72. 0.030 0.030 ↑ 2.5 2 6

Index Scan using cesbron_absence_request_emp_id_index on cesbron_absence_request ar_1 (cost=0.28..6.23 rows=5 width=8) (actual time=0.004..0.005 rows=2 loops=6)

  • Index Cond: (emp_id = c_3.emp_id)
  • Filter: ((state)::text = ANY ('{validated,confirmed}'::text[]))
73. 0.004 0.010 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=8) (actual time=0.010..0.010 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
74. 0.006 0.006 ↑ 1.0 6 1

Seq Scan on hr_week_act_type_hol_count_type_rel atc_rel_2 (cost=0.00..1.06 rows=6 width=8) (actual time=0.004..0.006 rows=6 loops=1)

75. 0.054 0.054 ↑ 1.0 1 6

Index Scan using hr_employee_pkey on hr_employee hre (cost=0.28..1.92 rows=1 width=20) (actual time=0.009..0.009 rows=1 loops=6)

  • Index Cond: (id = c.emp_id)
76. 0.024 0.024 ↑ 1.0 1 6

Index Scan using resource_resource_pkey on resource_resource rr (cost=0.28..0.32 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=6)

  • Index Cond: (id = hre.resource_id)
Planning time : 17.579 ms