explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nEXp

Settings
# exclusive inclusive rows x rows loops node
1. 2.672 92,891.088 ↑ 35.1 240 1

Hash Join (cost=128,452,473.73..128,452,921.55 rows=8,418 width=40) (actual time=92,885.003..92,891.088 rows=240 loops=1)

  • Hash Cond: (c.emp_id = hre.id)
2. 30.189 92,860.191 ↑ 5.4 3,114 1

HashAggregate (cost=128,444,051.80..128,444,220.17 rows=16,837 width=24) (actual time=92,856.699..92,860.191 rows=3,114 loops=1)

  • Group Key: c.emp_id, c.type_id
3. 19.476 92,830.002 ↑ 16.1 10,447 1

Append (cost=1.07..128,440,684.54 rows=168,363 width=19) (actual time=0.100..92,830.002 rows=10,447 loops=1)

4. 8.441 23.829 ↑ 1.0 3,114 1

Hash Join (cost=1.07..838.02 rows=3,114 width=24) (actual time=0.097..23.829 rows=3,114 loops=1)

  • Hash Cond: (c.type_id = ct.id)
5. 15.366 15.366 ↑ 1.0 3,114 1

Seq Scan on hr_holidays_counter c (cost=0.00..794.14 rows=3,114 width=24) (actual time=0.035..15.366 rows=3,114 loops=1)

6. 0.009 0.022 ↑ 1.0 3 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
7. 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.007..0.013 rows=3 loops=1)

8. 1.866 2.961 ↓ 0.0 0 1

Hash Join (cost=190,678.40..302,867.61 rows=132,491 width=20) (actual time=2.961..2.961 rows=0 loops=1)

  • Hash Cond: (ts.line_id = aal.id)
9.          

Initplan (forHash Join)

10. 0.000 0.000 ↓ 0.0 0

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

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

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

  • Filter: ((start_date <= (now())::date) AND (end_date >= (now())::date))
12. 0.007 0.298 ↑ 1.0 1 1

Limit (cost=8.18..8.19 rows=1 width=4) (actual time=0.296..0.298 rows=1 loops=1)

13. 0.089 0.291 ↑ 1.0 1 1

Sort (cost=8.18..8.19 rows=1 width=4) (actual time=0.291..0.291 rows=1 loops=1)

  • Sort Key: hr_week_number.date_start
  • Sort Method: top-N heapsort Memory: 25kB
14. 0.202 0.202 ↓ 35.0 35 1

Index Scan using idx_week_number_validation_date on hr_week_number (cost=0.15..8.17 rows=1 width=4) (actual time=0.097..0.202 rows=35 loops=1)

  • Index Cond: (validation_date > (now())::date)
15. 0.027 0.027 ↑ 3,567,810.0 1 1

Seq Scan on hr_analytic_timesheet ts (cost=0.00..96,160.10 rows=3,567,810 width=8) (actual time=0.027..0.027 rows=1 loops=1)

16. 0.003 0.770 ↓ 0.0 0 1

Hash (cost=188,211.45..188,211.45 rows=193,581 width=20) (actual time=0.770..0.770 rows=0 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 2048kB
17. 0.034 0.767 ↓ 0.0 0 1

Hash Join (cost=65.30..188,211.45 rows=193,581 width=20) (actual time=0.767..0.767 rows=0 loops=1)

  • Hash Cond: (aal.product_id = (COALESCE(wrat.second_timesheet_product_id, wrat.default_product_id)))
18. 0.370 0.370 ↑ 868,813.5 2 1

Index Scan using account_analytic_line_date_index on account_analytic_line aal (cost=0.43..94,985.35 rows=1,737,627 width=20) (actual time=0.364..0.370 rows=2 loops=1)

  • Index Cond: (date >= $14)
19. 0.026 0.363 ↑ 79.2 5 1

Hash (cost=59.92..59.92 rows=396 width=8) (actual time=0.363..0.363 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.032 0.337 ↑ 79.2 5 1

HashAggregate (cost=52.00..55.96 rows=396 width=12) (actual time=0.327..0.337 rows=5 loops=1)

  • Group Key: atc_rel.hct_id, COALESCE(wrat.second_timesheet_product_id, wrat.default_product_id)
21. 0.054 0.305 ↑ 66.0 6 1

Hash Join (cost=4.99..50.02 rows=396 width=12) (actual time=0.277..0.305 rows=6 loops=1)

  • Hash Cond: (atc_rel.wrat_id = wrat.id)
22. 0.030 0.030 ↑ 376.7 6 1

Seq Scan on hr_week_act_type_hol_count_type_rel atc_rel (cost=0.00..32.60 rows=2,260 width=8) (actual time=0.023..0.030 rows=6 loops=1)

23. 0.071 0.221 ↑ 1.0 35 1

Hash (cost=4.55..4.55 rows=35 width=12) (actual time=0.221..0.221 rows=35 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
24. 0.150 0.150 ↑ 1.0 35 1

Seq Scan on hr_weekly_report_activity_type wrat (cost=0.00..4.55 rows=35 width=12) (actual time=0.031..0.150 rows=35 loops=1)

  • Filter: ((line_type)::text = 'absence'::text)
  • Rows Removed by Filter: 89
25. 19.867 46,365.292 ↑ 4.5 3,664 1

Subquery Scan on *SELECT* 3 (cost=28,836.81..64,067,810.58 rows=16,362 width=12) (actual time=8.811..46,365.292 rows=3,664 loops=1)

26. 17.108 46,345.425 ↑ 4.5 3,664 1

Hash Join (cost=28,836.81..64,067,646.96 rows=16,362 width=12) (actual time=8.765..46,345.425 rows=3,664 loops=1)

  • Hash Cond: (ard.morning_type_id = atc_rel_1.wrat_id)
27.          

Initplan (forHash Join)

28. 0.029 0.029 ↑ 42.0 1 1

Seq Scan on hr_period hr_period_2 (cost=0.00..19.50 rows=42 width=4) (actual time=0.025..0.029 rows=1 loops=1)

  • Filter: ((start_date <= (now())::date) AND (end_date >= (now())::date))
  • Rows Removed by Filter: 1
29. 0.012 0.012 ↑ 42.0 1 1

Seq Scan on hr_period hr_period_3 (cost=0.00..19.50 rows=42 width=4) (actual time=0.010..0.012 rows=1 loops=1)

  • Filter: ((start_date <= (now())::date) AND (end_date >= (now())::date))
  • Rows Removed by Filter: 1
30. 42,916.467 46,328.247 ↓ 2.9 4,235 1

Nested Loop (cost=28,736.96..64,066,889.00 rows=1,448 width=12) (actual time=8.624..46,328.247 rows=4,235 loops=1)

31. 7.740 7.740 ↑ 2.0 1,140 1

Seq Scan on cesbron_absence_request ar (cost=0.00..179.70 rows=2,227 width=8) (actual time=0.043..7.740 rows=1,140 loops=1)

  • Filter: ((state)::text = ANY ('{validated,confirmed}'::text[]))
  • Rows Removed by Filter: 77
32. 0.000 3,404.040 ↓ 4.0 4 1,140

Index Scan using cesbron_absence_request_date_date_index on cesbron_absence_request_date ard (cost=28,736.96..28,768.16 rows=1 width=12) (actual time=1.352..2.986 rows=4 loops=1,140)

  • Index Cond: (date > (SubPlan 6))
  • Filter: (morning AND (ar.id = absence_request_id))
  • Rows Removed by Filter: 4467
33.          

SubPlan (forIndex Scan)

34. 3,206.820 42,889.080 ↑ 1.0 1 1,140

Aggregate (cost=28,736.67..28,736.68 rows=1 width=4) (actual time=37.621..37.622 rows=1 loops=1,140)

35. 14,371.700 39,682.260 ↓ 1.3 3,346 1,140

Nested Loop (cost=48.31..28,730.40 rows=2,510 width=4) (actual time=0.653..34.809 rows=3,346 loops=1,140)

36. 5,677.200 6,236.940 ↓ 1.3 3,346 1,140

Bitmap Heap Scan on hr_analytic_timesheet ts_2 (cost=47.88..8,427.80 rows=2,510 width=4) (actual time=0.625..5.471 rows=3,346 loops=1,140)

  • Recheck Cond: (employee_id = ar.emp_id)
  • Heap Blocks: exact=783965
37. 559.740 559.740 ↓ 1.3 3,346 1,140

Bitmap Index Scan on hr_analytic_timesheet_employee_id_index (cost=0.00..47.26 rows=2,510 width=0) (actual time=0.491..0.491 rows=3,346 loops=1,140)

  • Index Cond: (employee_id = ar.emp_id)
38. 19,073.620 19,073.620 ↑ 1.0 1 3,814,724

Index Scan using account_analytic_line_pkey on account_analytic_line aal_2 (cost=0.43..8.08 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=3,814,724)

  • Index Cond: (id = ts_2.line_id)
39. 3,206.820 42,889.080 ↑ 1.0 1 1,140

Aggregate (cost=28,736.67..28,736.68 rows=1 width=4) (actual time=37.621..37.622 rows=1 loops=1,140)

40. 14,371.700 39,682.260 ↓ 1.3 3,346 1,140

Nested Loop (cost=48.31..28,730.40 rows=2,510 width=4) (actual time=0.653..34.809 rows=3,346 loops=1,140)

41. 5,677.200 6,236.940 ↓ 1.3 3,346 1,140

Bitmap Heap Scan on hr_analytic_timesheet ts_2 (cost=47.88..8,427.80 rows=2,510 width=4) (actual time=0.625..5.471 rows=3,346 loops=1,140)

  • Recheck Cond: (employee_id = ar.emp_id)
  • Heap Blocks: exact=783965
42. 559.740 559.740 ↓ 1.3 3,346 1,140

Bitmap Index Scan on hr_analytic_timesheet_employee_id_index (cost=0.00..47.26 rows=2,510 width=0) (actual time=0.491..0.491 rows=3,346 loops=1,140)

  • Index Cond: (employee_id = ar.emp_id)
43. 19,073.620 19,073.620 ↑ 1.0 1 3,814,724

Index Scan using account_analytic_line_pkey on account_analytic_line aal_2 (cost=0.43..8.08 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=3,814,724)

  • Index Cond: (id = ts_2.line_id)
44. 0.016 0.029 ↑ 376.7 6 1

Hash (cost=32.60..32.60 rows=2,260 width=8) (actual time=0.029..0.029 rows=6 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 33kB
45. 0.013 0.013 ↑ 376.7 6 1

Seq Scan on hr_week_act_type_hol_count_type_rel atc_rel_1 (cost=0.00..32.60 rows=2,260 width=8) (actual time=0.005..0.013 rows=6 loops=1)

46. 19.501 46,418.444 ↑ 4.5 3,669 1

Subquery Scan on *SELECT* 4 (cost=28,836.81..64,067,812.28 rows=16,396 width=12) (actual time=5.385..46,418.444 rows=3,669 loops=1)

47. 16.684 46,398.943 ↑ 4.5 3,669 1

Hash Join (cost=28,836.81..64,067,648.32 rows=16,396 width=12) (actual time=5.351..46,398.943 rows=3,669 loops=1)

  • Hash Cond: (ard_1.afternoon_type_id = atc_rel_2.wrat_id)
48.          

Initplan (forHash Join)

49. 0.026 0.026 ↑ 42.0 1 1

Seq Scan on hr_period (cost=0.00..19.50 rows=42 width=4) (actual time=0.024..0.026 rows=1 loops=1)

  • Filter: ((start_date <= (now())::date) AND (end_date >= (now())::date))
  • Rows Removed by Filter: 1
50. 0.009 0.009 ↑ 42.0 1 1

Seq Scan on hr_period hr_period_1 (cost=0.00..19.50 rows=42 width=4) (actual time=0.008..0.009 rows=1 loops=1)

  • Filter: ((start_date <= (now())::date) AND (end_date >= (now())::date))
  • Rows Removed by Filter: 1
51. 42,942.879 46,382.176 ↓ 2.9 4,245 1

Nested Loop (cost=28,736.96..64,066,889.00 rows=1,451 width=12) (actual time=5.211..46,382.176 rows=4,245 loops=1)

52. 7.897 7.897 ↑ 2.0 1,140 1

Seq Scan on cesbron_absence_request ar_1 (cost=0.00..179.70 rows=2,227 width=8) (actual time=0.016..7.897 rows=1,140 loops=1)

  • Filter: ((state)::text = ANY ('{validated,confirmed}'::text[]))
  • Rows Removed by Filter: 77
53. 0.000 3,431.400 ↓ 4.0 4 1,140

Index Scan using cesbron_absence_request_date_date_index on cesbron_absence_request_date ard_1 (cost=28,736.96..28,768.16 rows=1 width=12) (actual time=1.344..3.010 rows=4 loops=1,140)

  • Index Cond: (date > (SubPlan 3))
  • Filter: (afternoon AND (ar_1.id = absence_request_id))
  • Rows Removed by Filter: 4467
54.          

SubPlan (forIndex Scan)

55. 3,193.140 42,916.440 ↑ 1.0 1 1,140

Aggregate (cost=28,736.67..28,736.68 rows=1 width=4) (actual time=37.645..37.646 rows=1 loops=1,140)

56. 14,452.640 39,723.300 ↓ 1.3 3,346 1,140

Nested Loop (cost=48.31..28,730.40 rows=2,510 width=4) (actual time=0.640..34.845 rows=3,346 loops=1,140)

57. 5,655.540 6,197.040 ↓ 1.3 3,346 1,140

Bitmap Heap Scan on hr_analytic_timesheet ts_1 (cost=47.88..8,427.80 rows=2,510 width=4) (actual time=0.610..5.436 rows=3,346 loops=1,140)

  • Recheck Cond: (employee_id = ar_1.emp_id)
  • Heap Blocks: exact=783965
58. 541.500 541.500 ↓ 1.3 3,346 1,140

Bitmap Index Scan on hr_analytic_timesheet_employee_id_index (cost=0.00..47.26 rows=2,510 width=0) (actual time=0.475..0.475 rows=3,346 loops=1,140)

  • Index Cond: (employee_id = ar_1.emp_id)
59. 19,073.620 19,073.620 ↑ 1.0 1 3,814,724

Index Scan using account_analytic_line_pkey on account_analytic_line aal_1 (cost=0.43..8.08 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=3,814,724)

  • Index Cond: (id = ts_1.line_id)
60. 3,193.140 42,916.440 ↑ 1.0 1 1,140

Aggregate (cost=28,736.67..28,736.68 rows=1 width=4) (actual time=37.645..37.646 rows=1 loops=1,140)

61. 14,452.640 39,723.300 ↓ 1.3 3,346 1,140

Nested Loop (cost=48.31..28,730.40 rows=2,510 width=4) (actual time=0.640..34.845 rows=3,346 loops=1,140)

62. 5,655.540 6,197.040 ↓ 1.3 3,346 1,140

Bitmap Heap Scan on hr_analytic_timesheet ts_1 (cost=47.88..8,427.80 rows=2,510 width=4) (actual time=0.610..5.436 rows=3,346 loops=1,140)

  • Recheck Cond: (employee_id = ar_1.emp_id)
  • Heap Blocks: exact=783965
63. 541.500 541.500 ↓ 1.3 3,346 1,140

Bitmap Index Scan on hr_analytic_timesheet_employee_id_index (cost=0.00..47.26 rows=2,510 width=0) (actual time=0.475..0.475 rows=3,346 loops=1,140)

  • Index Cond: (employee_id = ar_1.emp_id)
64. 19,073.620 19,073.620 ↑ 1.0 1 3,814,724

Index Scan using account_analytic_line_pkey on account_analytic_line aal_1 (cost=0.43..8.08 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=3,814,724)

  • Index Cond: (id = ts_1.line_id)
65. 0.037 0.048 ↑ 376.7 6 1

Hash (cost=32.60..32.60 rows=2,260 width=8) (actual time=0.048..0.048 rows=6 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 33kB
66. 0.011 0.011 ↑ 376.7 6 1

Seq Scan on hr_week_act_type_hol_count_type_rel atc_rel_2 (cost=0.00..32.60 rows=2,260 width=8) (actual time=0.005..0.011 rows=6 loops=1)

67. 0.274 28.225 ↓ 1.0 107 1

Hash (cost=8,420.60..8,420.60 rows=106 width=24) (actual time=28.225..28.225 rows=107 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
68. 0.630 27.951 ↓ 1.0 107 1

Nested Loop (cost=1.10..8,420.60 rows=106 width=24) (actual time=0.131..27.951 rows=107 loops=1)

69. 0.960 26.251 ↓ 1.0 107 1

Nested Loop (cost=0.82..8,380.64 rows=106 width=24) (actual time=0.103..26.251 rows=107 loops=1)

70. 23.900 23.900 ↓ 1.0 107 1

Seq Scan on hr_employee emp (cost=0.00..7,867.75 rows=106 width=4) (actual time=0.050..23.900 rows=107 loops=1)

  • Filter: ((parent_id = 743) OR (coach_id = 743))
  • Rows Removed by Filter: 2943
71. 0.642 1.391 ↑ 1.0 1 107

Bitmap Heap Scan on hr_employee hre (cost=0.82..4.83 rows=1 width=20) (actual time=0.012..0.013 rows=1 loops=107)

  • Recheck Cond: (id = emp.id)
  • Heap Blocks: exact=107
72. 0.749 0.749 ↑ 1.0 1 107

Bitmap Index Scan on hr_employee_pkey (cost=0.00..0.82 rows=1 width=0) (actual time=0.007..0.007 rows=1 loops=107)

  • Index Cond: (id = emp.id)
73. 1.070 1.070 ↑ 1.0 1 107

Index Scan using resource_resource_pkey on resource_resource rr (cost=0.28..0.37 rows=1 width=8) (actual time=0.008..0.010 rows=1 loops=107)

  • Index Cond: (id = hre.resource_id)