explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1sEC

Settings
# exclusive inclusive rows x rows loops node
1. 3.017 89,414.288 ↑ 35.1 240 1

Hash Join (cost=128,452,473.73..128,452,921.55 rows=8,418 width=40) (actual time=89,407.908..89,414.288 rows=240 loops=1)

  • Hash Cond: (c.emp_id = hre.id)
2. 27.887 89,252.449 ↑ 5.4 3,114 1

HashAggregate (cost=128,444,051.80..128,444,220.17 rows=16,837 width=24) (actual time=89,249.002..89,252.449 rows=3,114 loops=1)

  • Group Key: c.emp_id, c.type_id
3. 18.973 89,224.562 ↑ 16.1 10,447 1

Append (cost=1.07..128,440,684.54 rows=168,363 width=19) (actual time=0.129..89,224.562 rows=10,447 loops=1)

4. 8.717 17.596 ↑ 1.0 3,114 1

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

  • Hash Cond: (c.type_id = ct.id)
5. 8.846 8.846 ↑ 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.013..8.846 rows=3,114 loops=1)

6. 0.017 0.033 ↑ 1.0 3 1

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

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

8. 1.548 2.845 ↓ 0.0 0 1

Hash Join (cost=190,678.40..302,867.61 rows=132,491 width=20) (actual time=2.845..2.845 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.008 0.382 ↑ 1.0 1 1

Limit (cost=8.18..8.19 rows=1 width=4) (actual time=0.380..0.382 rows=1 loops=1)

13. 0.221 0.374 ↑ 1.0 1 1

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

  • Sort Key: hr_week_number.date_start
  • Sort Method: top-N heapsort Memory: 25kB
14. 0.153 0.153 ↓ 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.087..0.153 rows=35 loops=1)

  • Index Cond: (validation_date > (now())::date)
15. 0.040 0.040 ↑ 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.040..0.040 rows=1 loops=1)

16. 0.003 0.875 ↓ 0.0 0 1

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

  • Buckets: 262144 Batches: 1 Memory Usage: 2048kB
17. 0.032 0.872 ↓ 0.0 0 1

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

  • Hash Cond: (aal.product_id = (COALESCE(wrat.second_timesheet_product_id, wrat.default_product_id)))
18. 0.498 0.498 ↑ 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.486..0.498 rows=2 loops=1)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.037 0.326 ↑ 79.2 5 1

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

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

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

  • Hash Cond: (atc_rel.wrat_id = wrat.id)
22. 0.021 0.021 ↑ 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.012..0.021 rows=6 loops=1)

23. 0.062 0.225 ↑ 1.0 35 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
24. 0.163 0.163 ↑ 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.163 rows=35 loops=1)

  • Filter: ((line_type)::text = 'absence'::text)
  • Rows Removed by Filter: 89
25. 18.330 45,728.401 ↑ 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=10.454..45,728.401 rows=3,664 loops=1)

26. 15.030 45,710.071 ↑ 4.5 3,664 1

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

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

Initplan (forHash Join)

28. 0.040 0.040 ↑ 42.0 1 1

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

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

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

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

Nested Loop (cost=28,736.96..64,066,889.00 rows=1,448 width=12) (actual time=10.203..45,694.951 rows=4,235 loops=1)

31. 9.104 9.104 ↑ 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.041..9.104 rows=1,140 loops=1)

  • Filter: ((state)::text = ANY ('{validated,confirmed}'::text[]))
  • Rows Removed by Filter: 77
32. 0.000 3,312.840 ↓ 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.321..2.906 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,166.920 42,348.720 ↑ 1.0 1 1,140

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

35. 13,631.840 39,181.800 ↓ 1.3 3,346 1,140

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

36. 5,890.380 6,476.340 ↓ 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.647..5.681 rows=3,346 loops=1,140)

  • Recheck Cond: (employee_id = ar.emp_id)
  • Heap Blocks: exact=783965
37. 585.960 585.960 ↓ 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.514..0.514 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,166.920 42,348.720 ↑ 1.0 1 1,140

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

40. 13,631.840 39,181.800 ↓ 1.3 3,346 1,140

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

41. 5,890.380 6,476.340 ↓ 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.647..5.681 rows=3,346 loops=1,140)

  • Recheck Cond: (employee_id = ar.emp_id)
  • Heap Blocks: exact=783965
42. 585.960 585.960 ↓ 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.514..0.514 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.024 0.037 ↑ 376.7 6 1

Hash (cost=32.60..32.60 rows=2,260 width=8) (actual time=0.037..0.037 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. 16.595 43,456.747 ↑ 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=4.946..43,456.747 rows=3,669 loops=1)

47. 13.622 43,440.152 ↑ 4.5 3,669 1

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

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

Initplan (forHash Join)

49. 0.021 0.021 ↑ 42.0 1 1

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

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

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

  • Filter: ((start_date <= (now())::date) AND (end_date >= (now())::date))
  • Rows Removed by Filter: 1
51. 40,186.595 43,426.478 ↓ 2.9 4,245 1

Nested Loop (cost=28,736.96..64,066,889.00 rows=1,451 width=12) (actual time=4.821..43,426.478 rows=4,245 loops=1)

52. 6.843 6.843 ↑ 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.043..6.843 rows=1,140 loops=1)

  • Filter: ((state)::text = ANY ('{validated,confirmed}'::text[]))
  • Rows Removed by Filter: 77
53. 0.000 3,233.040 ↓ 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.269..2.836 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,109.920 40,164.480 ↑ 1.0 1 1,140

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

56. 12,116.780 37,054.560 ↓ 1.3 3,346 1,140

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

57. 5,330.640 5,864.160 ↓ 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.597..5.144 rows=3,346 loops=1,140)

  • Recheck Cond: (employee_id = ar_1.emp_id)
  • Heap Blocks: exact=783965
58. 533.520 533.520 ↓ 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.468..0.468 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,109.920 40,164.480 ↑ 1.0 1 1,140

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

61. 12,116.780 37,054.560 ↓ 1.3 3,346 1,140

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

62. 5,330.640 5,864.160 ↓ 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.597..5.144 rows=3,346 loops=1,140)

  • Recheck Cond: (employee_id = ar_1.emp_id)
  • Heap Blocks: exact=783965
63. 533.520 533.520 ↓ 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.468..0.468 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.011 0.019 ↑ 376.7 6 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 33kB
66. 0.008 0.008 ↑ 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.008 rows=6 loops=1)

67. 0.460 158.822 ↓ 1.0 107 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
68. 0.709 158.362 ↓ 1.0 107 1

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

69. 1.210 155.620 ↓ 1.0 107 1

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

70. 152.484 152.484 ↓ 1.0 107 1

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

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

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

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

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

  • Index Cond: (id = emp.id)
73. 2.033 2.033 ↑ 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.017..0.019 rows=1 loops=107)

  • Index Cond: (id = hre.resource_id)
Planning time : 16.050 ms
Execution time : 89,416.701 ms