explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LQYu

Settings
# exclusive inclusive rows x rows loops node
1. 10.894 3,506.683 ↓ 2.1 2,272 1

Hash Join (cost=1,249.71..143,298.80 rows=1,060 width=76) (actual time=261.241..3,506.683 rows=2,272 loops=1)

  • Hash Cond: (emp.working_schedule_type_id = wst.id)
2.          

Initplan (forHash Join)

3. 0.014 0.014 ↑ 1.0 1 1

Seq Scan on hr_period (cost=0.00..1.20 rows=1 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: 7
4. 0.012 0.012 ↑ 1.0 1 1

Seq Scan on hr_period hr_period_1 (cost=0.00..1.20 rows=1 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: 7
5. 4.332 3,495.729 ↓ 2.1 2,272 1

Nested Loop (cost=1,246.11..143,254.21 rows=1,060 width=29) (actual time=261.111..3,495.729 rows=2,272 loops=1)

  • Join Filter: (hhc2.emp_id = emp.id)
6. 2.408 3,482.309 ↓ 2.1 2,272 1

Hash Join (cost=1,245.83..142,925.14 rows=1,060 width=33) (actual time=261.071..3,482.309 rows=2,272 loops=1)

  • Hash Cond: ((hhc2.emp_id = c.emp_id) AND (ct.counter_type_id = c.type_id))
7. 84.486 3,476.771 ↓ 2.1 2,272 1

Hash Join (cost=1,127.50..142,782.96 rows=1,060 width=25) (actual time=257.903..3,476.771 rows=2,272 loops=1)

  • Hash Cond: (ts.employee_id = hhc2.emp_id)
  • Join Filter: (aal.date >= (SubPlan 3))
  • Rows Removed by Join Filter: 45718
8. 0.000 125.437 ↓ 14.2 54,346 1

Gather (cost=1,006.40..142,645.08 rows=3,829 width=21) (actual time=2.315..125.437 rows=54,346 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
9. 114.968 1,124.397 ↓ 11.4 18,115 3

Nested Loop (cost=6.40..141,262.18 rows=1,595 width=21) (actual time=0.614..1,124.397 rows=18,115 loops=3)

10. 639.307 1,009.424 ↓ 8.3 19,450 3

Hash Join (cost=5.97..140,069.61 rows=2,332 width=21) (actual time=0.555..1,009.424 rows=19,450 loops=3)

  • Hash Cond: (aal.product_id = ct.product_id)
11. 369.813 369.813 ↑ 1.3 1,884,082 3

Parallel Seq Scan on account_analytic_line aal (cost=0.00..131,206.05 rows=2,355,805 width=20) (actual time=0.017..369.813 rows=1,884,082 loops=3)

12. 0.011 0.304 ↓ 2.5 5 3

Hash (cost=5.94..5.94 rows=2 width=9) (actual time=0.304..0.304 rows=5 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
13. 0.004 0.293 ↓ 2.5 5 3

Subquery Scan on ct (cost=5.88..5.94 rows=2 width=9) (actual time=0.284..0.293 rows=5 loops=3)

14. 0.022 0.289 ↓ 2.5 5 3

GroupAggregate (cost=5.88..5.92 rows=2 width=9) (actual time=0.282..0.289 rows=5 loops=3)

  • Group Key: atc_rel.hct_id, (COALESCE(wrat.second_timesheet_product_id, wrat.default_product_id))
15. 0.058 0.267 ↓ 3.0 6 3

Sort (cost=5.88..5.89 rows=2 width=12) (actual time=0.265..0.267 rows=6 loops=3)

  • Sort Key: atc_rel.hct_id, (COALESCE(wrat.second_timesheet_product_id, wrat.default_product_id))
  • Sort Method: quicksort Memory: 25kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
  • Worker 1: Sort Method: quicksort Memory: 25kB
16. 0.057 0.209 ↓ 3.0 6 3

Hash Join (cost=1.14..5.87 rows=2 width=12) (actual time=0.116..0.209 rows=6 loops=3)

  • Hash Cond: (wrat.id = atc_rel.wrat_id)
17. 0.109 0.109 ↑ 1.0 35 3

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

  • Filter: ((line_type)::text = 'absence'::text)
  • Rows Removed by Filter: 92
18. 0.015 0.043 ↑ 1.0 6 3

Hash (cost=1.06..1.06 rows=6 width=8) (actual time=0.042..0.043 rows=6 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.028 0.028 ↑ 1.0 6 3

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.026..0.028 rows=6 loops=3)

20. 0.005 0.005 ↑ 1.0 1 58,349

Index Scan using idx_timesheet_line_id on hr_analytic_timesheet ts (cost=0.43..0.50 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=58,349)

  • Index Cond: (line_id = aal.id)
21. 0.458 3.528 ↑ 1.0 1,111 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 60kB
22. 2.457 3.070 ↑ 1.0 1,111 1

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

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

24.          

SubPlan (forHash Join)

25. 47.990 3,263.320 ↑ 1.0 1 47,990

Limit (cost=6.71..6.71 rows=1 width=4) (actual time=0.068..0.068 rows=1 loops=47,990)

26. 335.930 3,215.330 ↑ 87.0 1 47,990

Sort (cost=6.71..6.93 rows=87 width=4) (actual time=0.067..0.067 rows=1 loops=47,990)

  • Sort Key: hr_week_number.date_start
  • Sort Method: top-N heapsort Memory: 25kB
27. 2,879.400 2,879.400 ↑ 2.9 30 47,990

Seq Scan on hr_week_number (cost=0.00..6.28 rows=87 width=4) (actual time=0.003..0.060 rows=30 loops=47,990)

  • Filter: (validation_date > (max(hhc2.effective_date)))
  • Rows Removed by Filter: 232
28. 1.798 3.130 ↑ 1.0 3,333 1

Hash (cost=68.33..68.33 rows=3,333 width=12) (actual time=3.130..3.130 rows=3,333 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 176kB
29. 1.332 1.332 ↑ 1.0 3,333 1

Seq Scan on hr_holidays_counter c (cost=0.00..68.33 rows=3,333 width=12) (actual time=0.007..1.332 rows=3,333 loops=1)

30. 9.088 9.088 ↑ 1.0 1 2,272

Index Scan using hr_employee_pkey on hr_employee emp (cost=0.28..0.30 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=2,272)

  • Index Cond: (id = ts.employee_id)
31. 0.012 0.034 ↑ 1.0 9 1

Hash (cost=1.09..1.09 rows=9 width=12) (actual time=0.034..0.034 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
32. 0.022 0.022 ↑ 1.0 9 1

Seq Scan on hr_working_schedule_type wst (cost=0.00..1.09 rows=9 width=12) (actual time=0.016..0.022 rows=9 loops=1)

Planning time : 11.255 ms
Execution time : 3,507.888 ms