explain.depesz.com

PostgreSQL's explain analyze made readable

Result: T1eT

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 1.876 ↓ 0.0 0 1

GroupAggregate (cost=10,247.54..10,247.56 rows=1 width=12) (actual time=1.876..1.876 rows=0 loops=1)

  • Group Key: tb.start_date
2.          

CTE other_loc_id

3. 0.000 0.663 ↓ 0.0 0 1

Unique (cost=24.30..24.30 rows=1 width=8) (actual time=0.663..0.663 rows=0 loops=1)

4. 0.023 0.663 ↓ 0.0 0 1

Sort (cost=24.30..24.30 rows=1 width=8) (actual time=0.663..0.663 rows=0 loops=1)

  • Sort Key: lfg.location_id
  • Sort Method: quicksort Memory: 25kB
5. 0.000 0.640 ↓ 0.0 0 1

Nested Loop Left Join (cost=5.89..24.29 rows=1 width=8) (actual time=0.640..0.640 rows=0 loops=1)

6. 0.015 0.640 ↓ 0.0 0 1

Hash Join (cost=5.33..21.69 rows=1 width=8) (actual time=0.640..0.640 rows=0 loops=1)

  • Hash Cond: (ul.location_id = loc.id)
7. 0.028 0.028 ↑ 37.0 1 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.028 rows=1 loops=1)

  • Index Cond: (user_id = 3397025)
8. 0.000 0.597 ↓ 0.0 0 1

Hash (cost=4.90..4.90 rows=1 width=16) (actual time=0.597..0.597 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
9. 0.001 0.597 ↓ 0.0 0 1

Nested Loop (cost=0.85..4.90 rows=1 width=16) (actual time=0.597..0.597 rows=0 loops=1)

10. 0.596 0.596 ↓ 0.0 0 1

Index Scan using i_loc_corp_id on location loc (cost=0.29..2.31 rows=1 width=8) (actual time=0.596..0.596 rows=0 loops=1)

  • Index Cond: (corporation_id = 105)
11. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_loc_flag_del on location_flag lf (cost=0.56..2.58 rows=1 width=8) (never executed)

  • Index Cond: ((location_id = loc.id) AND (flag_id = 14))
  • Filter: (value = 'Yes'::text)
12. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_loc_flag_del on location_flag lfg (cost=0.56..2.58 rows=1 width=8) (never executed)

  • Index Cond: ((location_id = ul.location_id) AND (flag_id = 300))
  • Filter: (value = 'No'::text)
13.          

CTE emp_data

14. 0.059 0.800 ↓ 45.8 366 1

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

15. 0.018 0.141 ↓ 40.0 40 1

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

16. 0.064 0.064 ↑ 13.0 1 1

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

  • Index Cond: (user_id = 3397025)
17. 0.059 0.059 ↓ 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.059 rows=40 loops=1)

  • Index Cond: (employee_id = emp_1.id)
18. 0.600 0.600 ↓ 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.015 rows=9 loops=40)

  • Index Cond: (department_id = dep.department_id)
  • Filter: (deleted = 'N'::bpchar)
  • Rows Removed by Filter: 4
19. 0.024 1.873 ↓ 0.0 0 1

Sort (cost=10,127.43..10,127.43 rows=1 width=12) (actual time=1.873..1.873 rows=0 loops=1)

  • Sort Key: tb.start_date
  • Sort Method: quicksort Memory: 25kB
20. 0.001 1.849 ↓ 0.0 0 1

Nested Loop (cost=9,816.64..10,127.42 rows=1 width=12) (actual time=1.849..1.849 rows=0 loops=1)

21. 0.000 1.848 ↓ 0.0 0 1

Nested Loop (cost=9,816.22..10,124.98 rows=1 width=28) (actual time=1.848..1.848 rows=0 loops=1)

22. 0.000 1.848 ↓ 0.0 0 1

Nested Loop (cost=9,815.65..10,122.39 rows=1 width=36) (actual time=1.848..1.848 rows=0 loops=1)

23. 0.014 1.848 ↓ 0.0 0 1

Hash Join (cost=9,815.02..10,119.74 rows=1 width=44) (actual time=1.848..1.848 rows=0 loops=1)

  • Hash Cond: (pos.position_code_id = emp_data.posid)
24. 0.001 0.665 ↓ 0.0 0 1

Nested Loop (cost=9,814.66..10,119.19 rows=48 width=36) (actual time=0.665..0.665 rows=0 loops=1)

25. 0.000 0.664 ↓ 0.0 0 1

Nested Loop (cost=9,814.23..10,001.84 rows=48 width=36) (actual time=0.664..0.664 rows=0 loops=1)

26. 0.000 0.664 ↓ 0.0 0 1

HashAggregate (cost=0.02..0.03 rows=1 width=8) (actual time=0.664..0.664 rows=0 loops=1)

  • Group Key: other_loc_id.locid
27. 0.664 0.664 ↓ 0.0 0 1

CTE Scan on other_loc_id (cost=0.00..0.02 rows=1 width=8) (actual time=0.664..0.664 rows=0 loops=1)

28. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on timeblock tb (cost=9,814.21..10,001.33 rows=48 width=44) (never executed)

  • 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 (last_published = 'Y'::bpchar))
29. 0.000 0.000 ↓ 0.0 0

BitmapAnd (cost=9,814.21..9,814.21 rows=183 width=0) (never executed)

30. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on i_timeblock_loc (cost=0.00..246.38 rows=23,975 width=0) (never executed)

  • Index Cond: (location_id = other_loc_id.locid)
31. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on i_timeblock_start (cost=0.00..9,518.61 rows=747,105 width=0) (never executed)

  • Index Cond: ((start_date >= '2019-11-01'::date) AND (start_date <= '2019-11-30'::date))
32. 0.000 0.000 ↓ 0.0 0

Index Scan using position_pkey on "position" pos (cost=0.43..2.44 rows=1 width=16) (never executed)

  • Index Cond: (id = tb.position_id)
33. 0.043 1.169 ↓ 25.8 206 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
34. 0.159 1.126 ↓ 25.8 206 1

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

  • Group Key: emp_data.posid
35. 0.967 0.967 ↓ 45.8 366 1

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

36. 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 3) OR (hashed SubPlan 4))
37.          

SubPlan (for Index Scan)

38. 0.000 0.000 ↓ 0.0 0

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

39. 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)

40. 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)

41. 0.000 0.000 ↓ 0.0 0

Index Scan using employee_pkey on employee emp (cost=0.57..2.59 rows=1 width=8) (never executed)

  • Index Cond: (id = tb.employee_id)
  • Filter: (user_id <> 299)
42. 0.000 0.000 ↓ 0.0 0

Index Only Scan using position_code_pkey on position_code pc (cost=0.42..2.44 rows=1 width=8) (never executed)

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