explain.depesz.com

PostgreSQL's explain analyze made readable

Result: w6fK

Settings
# exclusive inclusive rows x rows loops node
1. 2.674 90,036.911 ↑ 35.1 240 1

Hash Join (cost=128,452,475.67..128,452,923.48 rows=8,418 width=40) (actual time=90,030.922..90,036.911 rows=240 loops=1)

  • Hash Cond: (c.emp_id = hre.id)
2. 28.538 90,014.758 ↑ 5.4 3,114 1

HashAggregate (cost=128,444,053.74..128,444,222.11 rows=16,837 width=24) (actual time=90,011.359..90,014.758 rows=3,114 loops=1)

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

Append (cost=1.07..128,440,686.48 rows=168,363 width=19) (actual time=0.144..89,986.220 rows=10,447 loops=1)

4. 8.762 19.684 ↑ 1.0 3,114 1

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

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

6. 0.024 0.036 ↑ 1.0 3 1

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

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

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

8. 1.699 3.021 ↓ 0.0 0 1

Hash Join (cost=190,680.34..302,869.55 rows=132,491 width=20) (actual time=3.021..3.021 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.370 ↑ 1.0 1 1

Limit (cost=10.12..10.12 rows=1 width=4) (actual time=0.368..0.370 rows=1 loops=1)

13. 0.083 0.363 ↑ 1.0 1 1

Sort (cost=10.12..10.12 rows=1 width=4) (actual time=0.363..0.363 rows=1 loops=1)

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

Seq Scan on hr_week_number (cost=0.00..10.11 rows=1 width=4) (actual time=0.102..0.280 rows=35 loops=1)

  • Filter: (validation_date > (now())::date)
  • Rows Removed by Filter: 227
15. 0.024 0.024 ↑ 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.024..0.024 rows=1 loops=1)

16. 0.004 0.928 ↓ 0.0 0 1

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

  • Buckets: 262144 Batches: 1 Memory Usage: 2048kB
17. 0.040 0.924 ↓ 0.0 0 1

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

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

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
20. 0.047 0.407 ↑ 79.2 5 1

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

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

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

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

23. 0.064 0.272 ↑ 1.0 35 1

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

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

  • Filter: ((line_type)::text = 'absence'::text)
  • Rows Removed by Filter: 89
25. 19.085 46,727.311 ↑ 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.792..46,727.311 rows=3,664 loops=1)

26. 16.076 46,708.226 ↑ 4.5 3,664 1

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

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

Initplan (forHash Join)

28. 0.054 0.054 ↑ 42.0 1 1

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

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

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

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

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

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

  • Filter: ((state)::text = ANY ('{validated,confirmed}'::text[]))
  • Rows Removed by Filter: 77
32. 0.000 3,353.880 ↓ 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.340..2.942 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,311.700 43,304.040 ↑ 1.0 1 1,140

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

35. 14,265.680 39,992.340 ↓ 1.3 3,346 1,140

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

36. 6,089.880 6,653.040 ↓ 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.629..5.836 rows=3,346 loops=1,140)

  • Recheck Cond: (employee_id = ar.emp_id)
  • Heap Blocks: exact=783965
37. 563.160 563.160 ↓ 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.494..0.494 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,311.700 43,304.040 ↑ 1.0 1 1,140

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

40. 14,265.680 39,992.340 ↓ 1.3 3,346 1,140

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

41. 6,089.880 6,653.040 ↓ 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.629..5.836 rows=3,346 loops=1,140)

  • Recheck Cond: (employee_id = ar.emp_id)
  • Heap Blocks: exact=783965
42. 563.160 563.160 ↓ 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.494..0.494 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.017 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.012 0.012 ↑ 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.012 rows=6 loops=1)

46. 16.909 43,217.349 ↑ 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.098..43,217.349 rows=3,669 loops=1)

47. 13.564 43,200.440 ↑ 4.5 3,669 1

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

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

Initplan (forHash Join)

49. 0.042 0.042 ↑ 42.0 1 1

Seq Scan on hr_period (cost=0.00..19.50 rows=42 width=4) (actual time=0.040..0.042 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.010..0.012 rows=1 loops=1)

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

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

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

  • Filter: ((state)::text = ANY ('{validated,confirmed}'::text[]))
  • Rows Removed by Filter: 77
53. 0.000 3,235.320 ↓ 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.265..2.838 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,103.080 39,922.800 ↑ 1.0 1 1,140

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

56. 11,918.420 36,819.720 ↓ 1.3 3,346 1,140

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

57. 5,310.120 5,827.680 ↓ 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.582..5.112 rows=3,346 loops=1,140)

  • Recheck Cond: (employee_id = ar_1.emp_id)
  • Heap Blocks: exact=783965
58. 517.560 517.560 ↓ 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.454..0.454 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,103.080 39,922.800 ↑ 1.0 1 1,140

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

61. 11,918.420 36,819.720 ↓ 1.3 3,346 1,140

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

62. 5,310.120 5,827.680 ↓ 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.582..5.112 rows=3,346 loops=1,140)

  • Recheck Cond: (employee_id = ar_1.emp_id)
  • Heap Blocks: exact=783965
63. 517.560 517.560 ↓ 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.454..0.454 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.014 0.046 ↑ 376.7 6 1

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

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

67. 0.339 19.479 ↓ 1.0 107 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
68. 0.727 19.140 ↓ 1.0 107 1

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

69. 0.861 17.343 ↓ 1.0 107 1

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

70. 15.091 15.091 ↓ 1.0 107 1

Seq Scan on hr_employee emp (cost=0.00..7,867.75 rows=106 width=4) (actual time=0.040..15.091 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.011..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)