explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aZWy

Settings
# exclusive inclusive rows x rows loops node
1. 0.287 446.222 ↓ 24.0 24 1

GroupAggregate (cost=10,244.87..10,244.89 rows=1 width=12) (actual time=445.836..446.222 rows=24 loops=1)

  • Group Key: tb.start_date
2.          

CTE other_loc_id

3. 0.027 9.749 ↓ 6.0 6 1

Unique (cost=107.22..107.22 rows=1 width=8) (actual time=9.705..9.749 rows=6 loops=1)

4. 0.163 9.722 ↓ 344.0 344 1

Sort (cost=107.22..107.22 rows=1 width=8) (actual time=9.701..9.722 rows=344 loops=1)

  • Sort Key: lfg.location_id
  • Sort Method: quicksort Memory: 35kB
5. 0.178 9.559 ↓ 344.0 344 1

Nested Loop Left Join (cost=1.83..107.21 rows=1 width=8) (actual time=0.145..9.559 rows=344 loops=1)

6. 0.180 6.629 ↓ 344.0 344 1

Nested Loop (cost=1.27..104.61 rows=1 width=8) (actual time=0.114..6.629 rows=344 loops=1)

7. 0.176 2.237 ↓ 351.0 351 1

Nested Loop (cost=0.71..102.02 rows=1 width=16) (actual time=0.060..2.237 rows=351 loops=1)

8. 0.653 0.653 ↓ 9.5 352 1

Index Scan using user_loc_user_id on user_location ul (cost=0.42..16.63 rows=37 width=8) (actual time=0.028..0.653 rows=352 loops=1)

  • Index Cond: (user_id = 3397025)
9. 1.408 1.408 ↑ 1.0 1 352

Index Scan using idx_location_id_del on location loc (cost=0.29..2.31 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=352)

  • Index Cond: (id = ul.location_id)
  • Filter: (corporation_id = 70683)
10. 4.212 4.212 ↑ 1.0 1 351

Index Scan using idx_loc_flag_del on location_flag lf (cost=0.56..2.58 rows=1 width=8) (actual time=0.012..0.012 rows=1 loops=351)

  • Index Cond: ((location_id = loc.id) AND (flag_id = 14))
  • Filter: (value = 'Yes'::text)
  • Rows Removed by Filter: 0
11. 2.752 2.752 ↓ 0.0 0 344

Index Scan using idx_loc_flag_del on location_flag lfg (cost=0.56..2.58 rows=1 width=8) (actual time=0.008..0.008 rows=0 loops=344)

  • Index Cond: ((location_id = ul.location_id) AND (flag_id = 300))
  • Filter: (value = 'No'::text)
  • Rows Removed by Filter: 1
12.          

CTE emp_data

13. 0.095 0.790 ↓ 45.8 366 1

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

14. 0.013 0.135 ↓ 40.0 40 1

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

15. 0.064 0.064 ↑ 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.063..0.064 rows=1 loops=1)

  • Index Cond: (user_id = 3397025)
16. 0.058 0.058 ↓ 3.1 40 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.032..0.058 rows=40 loops=1)

  • Index Cond: (employee_id = emp.id)
17. 0.560 0.560 ↓ 1.5 9 40

Index Scan using department_position_dep_idx on department_position dp (cost=0.29..3.59 rows=6 width=16) (actual time=0.005..0.014 rows=9 loops=40)

  • Index Cond: (department_id = dep.department_id)
  • Filter: (deleted = 'N'::bpchar)
  • Rows Removed by Filter: 4
18. 0.866 445.935 ↓ 1,824.0 1,824 1

Sort (cost=10,041.83..10,041.84 rows=1 width=12) (actual time=445.804..445.935 rows=1,824 loops=1)

  • Sort Key: tb.start_date
  • Sort Method: quicksort Memory: 134kB
19. 1.986 445.069 ↓ 1,824.0 1,824 1

Nested Loop (cost=9,791.61..10,041.82 rows=1 width=12) (actual time=170.741..445.069 rows=1,824 loops=1)

20. 1.646 439.435 ↓ 1,824.0 1,824 1

Nested Loop (cost=9,791.19..10,039.38 rows=1 width=28) (actual time=170.659..439.435 rows=1,824 loops=1)

21. 0.936 432.317 ↓ 1,824.0 1,824 1

Hash Join (cost=9,790.56..10,036.73 rows=1 width=36) (actual time=170.495..432.317 rows=1,824 loops=1)

  • Hash Cond: (pos.position_code_id = emp_data.posid)
22. 1.037 430.215 ↓ 76.0 1,824 1

Nested Loop (cost=9,790.20..10,036.27 rows=24 width=28) (actual time=169.310..430.215 rows=1,824 loops=1)

23. 0.895 423.706 ↓ 76.0 1,824 1

Nested Loop (cost=9,789.77..9,977.60 rows=24 width=28) (actual time=169.260..423.706 rows=1,824 loops=1)

24. 0.013 9.765 ↓ 6.0 6 1

HashAggregate (cost=0.02..0.03 rows=1 width=8) (actual time=9.758..9.765 rows=6 loops=1)

  • Group Key: other_loc_id.locid
25. 9.752 9.752 ↓ 6.0 6 1

CTE Scan on other_loc_id (cost=0.00..0.02 rows=1 width=8) (actual time=9.706..9.752 rows=6 loops=1)

26. 10.075 413.046 ↓ 12.7 304 6

Bitmap Heap Scan on timeblock tb (cost=9,789.75..9,977.33 rows=24 width=36) (actual time=67.374..68.841 rows=304 loops=6)

  • Recheck Cond: ((location_id = other_loc_id.locid) AND (start_date >= '2019-11-01'::date) AND (start_date <= '2019-11-30'::date) AND (deleted = 'N'::bpchar))
  • Filter: ((date_published IS NOT NULL) AND (NOT (hashed SubPlan 5)) AND (last_published = 'Y'::bpchar))
  • Rows Removed by Filter: 59
  • Heap Blocks: exact=1384
27. 10.390 402.966 ↓ 0.0 0 6

BitmapAnd (cost=9,789.73..9,789.73 rows=183 width=0) (actual time=67.161..67.161 rows=0 loops=6)

28. 30.732 30.732 ↓ 2.1 49,794 6

Bitmap Index Scan on i_timeblock_loc (cost=0.00..246.38 rows=23,975 width=0) (actual time=5.122..5.122 rows=49,794 loops=6)

  • Index Cond: (location_id = other_loc_id.locid)
29. 361.844 361.844 ↑ 1.0 717,507 2

Bitmap Index Scan on i_timeblock_start (cost=0.00..9,518.61 rows=747,105 width=0) (actual time=180.922..180.922 rows=717,507 loops=2)

  • Index Cond: ((start_date >= '2019-11-01'::date) AND (start_date <= '2019-11-30'::date))
30.          

SubPlan (for Bitmap Heap Scan)

31. 0.001 0.005 ↑ 1.0 1 1

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

32. 0.004 0.004 ↑ 8.0 1 1

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

33. 5.472 5.472 ↑ 1.0 1 1,824

Index Scan using position_pkey on "position" pos (cost=0.43..2.44 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=1,824)

  • Index Cond: (id = tb.position_id)
34. 0.040 1.166 ↓ 25.8 206 1

Hash (cost=0.26..0.26 rows=8 width=8) (actual time=1.166..1.166 rows=206 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
35. 0.169 1.126 ↓ 25.8 206 1

HashAggregate (cost=0.18..0.26 rows=8 width=8) (actual time=1.099..1.126 rows=206 loops=1)

  • Group Key: emp_data.posid
36. 0.957 0.957 ↓ 45.8 366 1

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

37. 5.418 5.472 ↑ 1.0 1 1,824

Index Scan using i_schedule_idtempn on schedule sch (cost=0.63..2.65 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1,824)

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

SubPlan (for Index Scan)

39. 0.001 0.002 ↑ 1.0 1 1

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

40. 0.001 0.001 ↑ 8.0 1 1

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

41. 0.052 0.052 ↓ 45.8 366 1

CTE Scan on emp_data emp_data_2 (cost=0.00..0.16 rows=8 width=8) (actual time=0.001..0.052 rows=366 loops=1)

42. 3.648 3.648 ↑ 1.0 1 1,824

Index Only Scan using position_code_pkey on position_code pc (cost=0.42..2.44 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1,824)

  • Index Cond: (id = pos.position_code_id)
  • Heap Fetches: 1824