explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ADy3

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 42.160 ↑ 4.0 1 1

Nested Loop (cost=2,860.42..2,894.42 rows=4 width=8) (actual time=42.159..42.160 rows=1 loops=1)

2. 0.007 42.108 ↑ 4.0 1 1

Nested Loop (cost=2,860.14..2,893.18 rows=4 width=12) (actual time=42.107..42.108 rows=1 loops=1)

3. 0.007 42.081 ↑ 4.0 1 1

GroupAggregate (cost=2,859.86..2,859.94 rows=4 width=28) (actual time=42.081..42.081 rows=1 loops=1)

  • Group Key: t1.id, t1.emp_id, t1.counter_type_id
4. 0.011 42.074 ↑ 4.0 1 1

Sort (cost=2,859.86..2,859.87 rows=4 width=12) (actual time=42.074..42.074 rows=1 loops=1)

  • Sort Key: t1.emp_id, t1.counter_type_id
  • Sort Method: quicksort Memory: 25kB
5. 0.001 42.063 ↑ 4.0 1 1

Subquery Scan on t1 (cost=0.28..2,859.82 rows=4 width=12) (actual time=0.024..42.063 rows=1 loops=1)

6. 0.006 42.062 ↑ 4.0 1 1

Append (cost=0.28..2,859.78 rows=4 width=28) (actual time=0.023..42.062 rows=1 loops=1)

7. 0.005 0.024 ↑ 1.0 1 1

Nested Loop (cost=0.28..9.36 rows=1 width=28) (actual time=0.022..0.024 rows=1 loops=1)

  • Join Filter: (c.type_id = ct.id)
8. 0.014 0.014 ↑ 1.0 1 1

Index Scan using hr_holidays_counter_pkey on hr_holidays_counter c (cost=0.28..8.30 rows=1 width=28) (actual time=0.013..0.014 rows=1 loops=1)

  • Index Cond: (id = 13)
9. 0.005 0.005 ↑ 3.0 1 1

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

10. 0.000 7.076 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=130.94..1,463.37 rows=1 width=28) (actual time=7.076..7.076 rows=0 loops=1)

11. 0.000 7.076 ↓ 0.0 0 1

Nested Loop (cost=130.94..1,463.35 rows=1 width=76) (actual time=7.076..7.076 rows=0 loops=1)

12.          

Initplan (forNested Loop)

13. 0.000 0.000 ↓ 0.0 0

Seq Scan on hr_period (cost=0.00..1.20 rows=1 width=4) (never executed)

  • Filter: ((start_date <= (now())::date) AND (end_date >= (now())::date))
14. 0.000 0.000 ↓ 0.0 0

Seq Scan on hr_period hr_period_1 (cost=0.00..1.20 rows=1 width=4) (never executed)

  • Filter: ((start_date <= (now())::date) AND (end_date >= (now())::date))
15. 0.017 7.076 ↓ 0.0 0 1

Hash Join (cost=128.41..1,460.76 rows=1 width=29) (actual time=7.076..7.076 rows=0 loops=1)

  • Hash Cond: (ts.employee_id = hhc2.emp_id)
  • Join Filter: (aal.date >= (SubPlan 3))
  • Rows Removed by Join Filter: 2
16. 0.981 4.680 ↓ 2.0 2 1

Nested Loop (cost=7.31..1,339.65 rows=1 width=37) (actual time=1.115..4.680 rows=2 loops=1)

  • Join Filter: ((COALESCE(wrat.second_timesheet_product_id, wrat.default_product_id)) = aal.product_id)
  • Rows Removed by Join Filter: 855
17. 0.343 1.128 ↑ 2.2 857 1

Nested Loop (cost=6.87..204.12 rows=1,925 width=33) (actual time=0.173..1.128 rows=857 loops=1)

  • Join Filter: (c_1.emp_id = ts.employee_id)
18. 0.004 0.157 ↑ 1.0 1 1

Nested Loop (cost=6.44..22.57 rows=1 width=25) (actual time=0.150..0.157 rows=1 loops=1)

19. 0.004 0.135 ↑ 1.0 1 1

Nested Loop (cost=6.16..14.27 rows=1 width=17) (actual time=0.128..0.135 rows=1 loops=1)

  • Join Filter: (atc_rel.hct_id = c_1.type_id)
  • Rows Removed by Join Filter: 4
20. 0.005 0.005 ↑ 1.0 1 1

Index Scan using hr_holidays_counter_pkey on hr_holidays_counter c_1 (cost=0.28..8.30 rows=1 width=12) (actual time=0.004..0.005 rows=1 loops=1)

  • Index Cond: (id = 13)
21. 0.011 0.126 ↓ 2.5 5 1

GroupAggregate (cost=5.88..5.92 rows=2 width=9) (actual time=0.121..0.126 rows=5 loops=1)

  • Group Key: atc_rel.hct_id, (COALESCE(wrat.second_timesheet_product_id, wrat.default_product_id))
22. 0.010 0.115 ↓ 3.0 6 1

Sort (cost=5.88..5.89 rows=2 width=12) (actual time=0.114..0.115 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
23. 0.019 0.105 ↓ 3.0 6 1

Hash Join (cost=1.14..5.87 rows=2 width=12) (actual time=0.049..0.105 rows=6 loops=1)

  • Hash Cond: (wrat.id = atc_rel.wrat_id)
24. 0.078 0.078 ↑ 1.0 35 1

Seq Scan on hr_weekly_report_activity_type wrat (cost=0.00..4.59 rows=35 width=12) (actual time=0.028..0.078 rows=35 loops=1)

  • Filter: ((line_type)::text = 'absence'::text)
  • Rows Removed by Filter: 92
25. 0.004 0.008 ↑ 1.0 6 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
26. 0.004 0.004 ↑ 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.002..0.004 rows=6 loops=1)

27. 0.018 0.018 ↑ 1.0 1 1

Index Scan using hr_employee_pkey on hr_employee emp (cost=0.28..8.30 rows=1 width=8) (actual time=0.017..0.018 rows=1 loops=1)

  • Index Cond: (id = c_1.emp_id)
28. 0.628 0.628 ↑ 3.4 857 1

Index Scan using idx_timesheet_line_employee_id on hr_analytic_timesheet ts (cost=0.43..145.41 rows=2,892 width=8) (actual time=0.020..0.628 rows=857 loops=1)

  • Index Cond: (employee_id = emp.id)
29. 2.571 2.571 ↑ 1.0 1 857

Index Scan using account_analytic_line_pkey on account_analytic_line aal (cost=0.43..0.58 rows=1 width=20) (actual time=0.003..0.003 rows=1 loops=857)

  • Index Cond: (id = ts.line_id)
30. 0.226 2.211 ↑ 1.0 1,111 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 60kB
31. 1.412 1.985 ↑ 1.0 1,111 1

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

  • Group Key: hhc2.emp_id
32. 0.573 0.573 ↑ 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.010..0.573 rows=3,333 loops=1)

33.          

SubPlan (forHash Join)

34. 0.004 0.168 ↑ 1.0 1 2

Limit (cost=6.71..6.71 rows=1 width=4) (actual time=0.083..0.084 rows=1 loops=2)

35. 0.024 0.164 ↑ 87.0 1 2

Sort (cost=6.71..6.93 rows=87 width=4) (actual time=0.081..0.082 rows=1 loops=2)

  • Sort Key: hr_week_number.date_start
  • Sort Method: top-N heapsort Memory: 25kB
36. 0.140 0.140 ↑ 2.9 30 2

Seq Scan on hr_week_number (cost=0.00..6.28 rows=87 width=4) (actual time=0.010..0.070 rows=30 loops=2)

  • Filter: (validation_date > (max(hhc2.effective_date)))
  • Rows Removed by Filter: 232
37. 0.000 0.000 ↓ 0.0 0

Index Scan using hr_working_schedule_type_pkey on hr_working_schedule_type wst (cost=0.14..0.16 rows=1 width=12) (never executed)

  • Index Cond: (id = emp.working_schedule_type_id)
38. 0.000 16.439 ↓ 0.0 0 1

Subquery Scan on *SELECT* 3 (cost=224.25..693.25 rows=1 width=28) (actual time=16.439..16.439 rows=0 loops=1)

39. 2.167 16.439 ↓ 0.0 0 1

Hash Join (cost=224.25..693.23 rows=1 width=76) (actual time=16.439..16.439 rows=0 loops=1)

  • Hash Cond: ((ard.absence_request_id = ar.id) AND (c_2.emp_id = ar.emp_id))
  • Join Filter: (ard.date > (SubPlan 6))
40.          

Initplan (forHash Join)

41. 0.000 0.000 ↓ 0.0 0

Seq Scan on hr_period hr_period_2 (cost=0.00..1.20 rows=1 width=4) (never executed)

  • Filter: ((start_date <= (now())::date) AND (end_date >= (now())::date))
42. 0.000 0.000 ↓ 0.0 0

Seq Scan on hr_period hr_period_3 (cost=0.00..1.20 rows=1 width=4) (never executed)

  • Filter: ((start_date <= (now())::date) AND (end_date >= (now())::date))
43. 5.447 10.633 ↓ 7.8 13,446 1

Hash Join (cost=9.46..469.33 rows=1,734 width=20) (actual time=0.036..10.633 rows=13,446 loops=1)

  • Hash Cond: (ard.morning_type_id = atc_rel_1.wrat_id)
44. 5.171 5.171 ↑ 1.0 18,213 1

Seq Scan on cesbron_absence_request_date ard (cost=0.00..374.22 rows=18,217 width=12) (actual time=0.011..5.171 rows=18,213 loops=1)

  • Filter: morning
  • Rows Removed by Filter: 209
45. 0.002 0.015 ↑ 1.0 2 1

Hash (cost=9.43..9.43 rows=2 width=16) (actual time=0.015..0.015 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
46. 0.004 0.013 ↑ 1.0 2 1

Nested Loop (cost=0.28..9.43 rows=2 width=16) (actual time=0.010..0.013 rows=2 loops=1)

  • Join Filter: (atc_rel_1.hct_id = c_2.type_id)
  • Rows Removed by Join Filter: 4
47. 0.006 0.006 ↑ 1.0 1 1

Index Scan using hr_holidays_counter_pkey on hr_holidays_counter c_2 (cost=0.28..8.30 rows=1 width=12) (actual time=0.006..0.006 rows=1 loops=1)

  • Index Cond: (id = 13)
48. 0.003 0.003 ↑ 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.002..0.003 rows=6 loops=1)

49. 1.073 3.639 ↑ 1.0 4,383 1

Hash (cost=146.65..146.65 rows=4,383 width=8) (actual time=3.639..3.639 rows=4,383 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 236kB
50. 2.566 2.566 ↑ 1.0 4,383 1

Seq Scan on cesbron_absence_request ar (cost=0.00..146.65 rows=4,383 width=8) (actual time=0.014..2.566 rows=4,383 loops=1)

  • Filter: ((state)::text = ANY ('{validated,confirmed}'::text[]))
  • Rows Removed by Filter: 549
51.          

SubPlan (forHash Join)

52. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=32,955.92..32,955.93 rows=1 width=4) (never executed)

53. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=55.28..32,948.69 rows=2,892 width=4) (never executed)

54. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on hr_analytic_timesheet ts_1 (cost=54.84..9,647.29 rows=2,892 width=4) (never executed)

  • Recheck Cond: (employee_id = ar.emp_id)
55. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_timesheet_line_employee_id (cost=0.00..54.12 rows=2,892 width=0) (never executed)

  • Index Cond: (employee_id = ar.emp_id)
56. 0.000 0.000 ↓ 0.0 0

Index Scan using account_analytic_line_pkey on account_analytic_line aal_1 (cost=0.43..8.06 rows=1 width=8) (never executed)

  • Index Cond: (id = ts_1.line_id)
57. 0.000 18.517 ↓ 0.0 0 1

Subquery Scan on *SELECT* 4 (cost=224.25..693.77 rows=1 width=28) (actual time=18.517..18.517 rows=0 loops=1)

58. 2.549 18.517 ↓ 0.0 0 1

Hash Join (cost=224.25..693.75 rows=1 width=76) (actual time=18.517..18.517 rows=0 loops=1)

  • Hash Cond: ((ard_1.absence_request_id = ar_1.id) AND (c_3.emp_id = ar_1.emp_id))
  • Join Filter: (ard_1.date > (SubPlan 9))
59.          

Initplan (forHash Join)

60. 0.000 0.000 ↓ 0.0 0

Seq Scan on hr_period hr_period_4 (cost=0.00..1.20 rows=1 width=4) (never executed)

  • Filter: ((start_date <= (now())::date) AND (end_date >= (now())::date))
61. 0.000 0.000 ↓ 0.0 0

Seq Scan on hr_period hr_period_5 (cost=0.00..1.20 rows=1 width=4) (never executed)

  • Filter: ((start_date <= (now())::date) AND (end_date >= (now())::date))
62. 7.100 12.830 ↓ 7.7 13,483 1

Hash Join (cost=9.46..469.80 rows=1,744 width=20) (actual time=0.053..12.830 rows=13,483 loops=1)

  • Hash Cond: (ard_1.afternoon_type_id = atc_rel_2.wrat_id)
63. 5.699 5.699 ↓ 1.0 18,321 1

Seq Scan on cesbron_absence_request_date ard_1 (cost=0.00..374.22 rows=18,314 width=12) (actual time=0.007..5.699 rows=18,321 loops=1)

  • Filter: afternoon
  • Rows Removed by Filter: 101
64. 0.004 0.031 ↑ 1.0 2 1

Hash (cost=9.43..9.43 rows=2 width=16) (actual time=0.031..0.031 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
65. 0.005 0.027 ↑ 1.0 2 1

Nested Loop (cost=0.28..9.43 rows=2 width=16) (actual time=0.024..0.027 rows=2 loops=1)

  • Join Filter: (atc_rel_2.hct_id = c_3.type_id)
  • Rows Removed by Join Filter: 4
66. 0.018 0.018 ↑ 1.0 1 1

Index Scan using hr_holidays_counter_pkey on hr_holidays_counter c_3 (cost=0.28..8.30 rows=1 width=12) (actual time=0.017..0.018 rows=1 loops=1)

  • Index Cond: (id = 13)
67. 0.004 0.004 ↑ 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.003..0.004 rows=6 loops=1)

68. 1.006 3.138 ↑ 1.0 4,383 1

Hash (cost=146.65..146.65 rows=4,383 width=8) (actual time=3.138..3.138 rows=4,383 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 236kB
69. 2.132 2.132 ↑ 1.0 4,383 1

Seq Scan on cesbron_absence_request ar_1 (cost=0.00..146.65 rows=4,383 width=8) (actual time=0.009..2.132 rows=4,383 loops=1)

  • Filter: ((state)::text = ANY ('{validated,confirmed}'::text[]))
  • Rows Removed by Filter: 549
70.          

SubPlan (forHash Join)

71. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=32,955.92..32,955.93 rows=1 width=4) (never executed)

72. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=55.28..32,948.69 rows=2,892 width=4) (never executed)

73. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on hr_analytic_timesheet ts_2 (cost=54.84..9,647.29 rows=2,892 width=4) (never executed)

  • Recheck Cond: (employee_id = ar_1.emp_id)
74. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_timesheet_line_employee_id (cost=0.00..54.12 rows=2,892 width=0) (never executed)

  • Index Cond: (employee_id = ar_1.emp_id)
75. 0.000 0.000 ↓ 0.0 0

Index Scan using account_analytic_line_pkey on account_analytic_line aal_2 (cost=0.43..8.06 rows=1 width=8) (never executed)

  • Index Cond: (id = ts_2.line_id)
76. 0.020 0.020 ↑ 1.0 1 1

Index Scan using hr_employee_pkey on hr_employee hre (cost=0.28..8.30 rows=1 width=8) (actual time=0.020..0.020 rows=1 loops=1)

  • Index Cond: (id = t1.emp_id)
77. 0.047 0.047 ↑ 1.0 1 1

Index Only Scan using resource_resource_pkey on resource_resource rr (cost=0.28..0.31 rows=1 width=4) (actual time=0.047..0.047 rows=1 loops=1)

  • Index Cond: (id = hre.resource_id)
  • Heap Fetches: 0
Planning time : 10.408 ms
Execution time : 42.814 ms