explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GDGQ

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.187 ↓ 0.0 0 1

GroupAggregate (cost=14,222.43..14,223.85 rows=81 width=12) (actual time=0.187..0.187 rows=0 loops=1)

  • Group Key: tb.start_date
2.          

CTE emp_data

3. 0.000 0.124 ↑ 8.0 1 1

Nested Loop (cost=1.14..95.81 rows=8 width=32) (actual time=0.124..0.124 rows=1 loops=1)

4. 0.003 0.100 ↑ 1.0 1 1

Nested Loop (cost=0.85..92.16 rows=1 width=24) (actual time=0.100..0.100 rows=1 loops=1)

5. 0.056 0.056 ↑ 13.0 1 1

Index Scan using i_emp_user_id on employee emp (cost=0.42..14.05 rows=13 width=16) (actual time=0.056..0.056 rows=1 loops=1)

  • Index Cond: (user_id = 3397025)
6. 0.041 0.041 ↑ 13.0 1 1

Index Scan using idx_emp_dep_eid_del on department_employee dep (cost=0.42..5.88 rows=13 width=16) (actual time=0.041..0.041 rows=1 loops=1)

  • Index Cond: (employee_id = emp.id)
7. 0.024 0.024 ↑ 6.0 1 1

Index Scan using department_position_dep_idx on department_position dp (cost=0.29..3.59 rows=6 width=16) (actual time=0.024..0.024 rows=1 loops=1)

  • Index Cond: (department_id = dep.department_id)
  • Filter: (deleted = 'N'::bpchar)
8.          

Initplan (for GroupAggregate)

9. 0.001 0.128 ↑ 1.0 1 1

Limit (cost=0.00..0.02 rows=1 width=8) (actual time=0.128..0.128 rows=1 loops=1)

10. 0.127 0.127 ↑ 8.0 1 1

CTE Scan on emp_data (cost=0.00..0.16 rows=8 width=8) (actual time=0.127..0.127 rows=1 loops=1)

11. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.00..0.02 rows=1 width=8) (never executed)

12. 0.000 0.000 ↓ 0.0 0

CTE Scan on emp_data emp_data_1 (cost=0.00..0.16 rows=8 width=8) (never executed)

13. 0.019 0.184 ↓ 0.0 0 1

Sort (cost=14,126.58..14,126.79 rows=81 width=12) (actual time=0.184..0.184 rows=0 loops=1)

  • Sort Key: tb.start_date
  • Sort Method: quicksort Memory: 25kB
14. 0.000 0.165 ↓ 0.0 0 1

Nested Loop (cost=986.86..14,124.02 rows=81 width=12) (actual time=0.165..0.165 rows=0 loops=1)

  • Join Filter: (sr.timeblock_id <> tb.id)
15. 0.000 0.165 ↓ 0.0 0 1

Nested Loop (cost=986.43..14,092.54 rows=1 width=12) (actual time=0.165..0.165 rows=0 loops=1)

16. 0.000 0.165 ↓ 0.0 0 1

Nested Loop (cost=985.80..14,089.88 rows=1 width=20) (actual time=0.165..0.165 rows=0 loops=1)

17. 0.001 0.165 ↓ 0.0 0 1

Nested Loop (cost=0.85..286.03 rows=14 width=16) (actual time=0.165..0.165 rows=0 loops=1)

18. 0.164 0.164 ↓ 0.0 0 1

Index Scan using emp_pos_employee_id on employee_position ep (cost=0.43..112.79 rows=71 width=8) (actual time=0.164..0.164 rows=0 loops=1)

  • Index Cond: (employee_id = $3)
  • Filter: (deleted = 'N'::bpchar)
19. 0.000 0.000 ↓ 0.0 0

Index Only Scan using i_position_id on "position" pos (cost=0.42..2.44 rows=1 width=8) (never executed)

  • Index Cond: (id = ep.position_id)
  • Heap Fetches: 0
20. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on timeblock tb (cost=984.95..985.98 rows=1 width=28) (never executed)

  • Recheck Cond: ((position_id = pos.id) AND (deleted = 'N'::bpchar) AND (start_date >= now()) AND (start_date >= '2019-12-01'::date) AND (start_date <= '2019-12-31'::date))
  • Filter: ((date_published IS NOT NULL) AND (last_published = 'Y'::bpchar) AND (shift_type = 2))
21. 0.000 0.000 ↓ 0.0 0

BitmapAnd (cost=984.95..984.95 rows=1 width=0) (never executed)

22. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on i_timeblock_position (cost=0.00..17.25 rows=1,558 width=0) (never executed)

  • Index Cond: (position_id = pos.id)
23. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on i_timeblock_start_nodel (cost=0.00..967.42 rows=63,428 width=0) (never executed)

  • Index Cond: ((start_date >= now()) AND (start_date >= '2019-12-01'::date) AND (start_date <= '2019-12-31'::date))
24. 0.000 0.000 ↓ 0.0 0

Index Scan using i_schedule_idtempn on schedule sch (cost=0.63..2.65 rows=1 width=8) (never executed)

  • Index Cond: (id = tb.schedule_id)
  • Filter: ((hashed SubPlan 4) OR (hashed SubPlan 5))
25.          

SubPlan (for Index Scan)

26. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.00..0.02 rows=1 width=8) (never executed)

27. 0.000 0.000 ↓ 0.0 0

CTE Scan on emp_data emp_data_2 (cost=0.00..0.16 rows=8 width=8) (never executed)

28. 0.000 0.000 ↓ 0.0 0

CTE Scan on emp_data emp_data_3 (cost=0.00..0.16 rows=8 width=8) (never executed)

29. 0.000 0.000 ↓ 0.0 0

Index Scan using shift_request_employee on shift_request sr (cost=0.42..30.47 rows=81 width=8) (never executed)

  • Index Cond: (requesting_employee_id = $4)