explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wNjv

Settings
# exclusive inclusive rows x rows loops node
1. 0.280 359.366 ↓ 24.0 24 1
→

GroupAggregate (cost=10,330.46..10,330.48 rows=1 width=12) (actual time=358.984..359.366 rows=24 loops=1)

  • Group Key: tb.start_date
2.          

CTE other_loc_id
3. 0.025 9.794 ↓ 6.0 6 1
→

Unique (cost=107.22..107.22 rows=1 width=8) (actual time=9.750..9.794 rows=6 loops=1)

4. 0.170 9.769 ↓ 344.0 344 1
→

Sort (cost=107.22..107.22 rows=1 width=8) (actual time=9.749..9.769 rows=344 loops=1)

  • Sort Key: lfg.location_id
  • Sort Method: quicksort Memory: 35kB
5. 0.188 9.599 ↓ 344.0 344 1
→

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

6. 0.218 6.659 ↓ 344.0 344 1
→

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

7. 0.159 2.229 ↓ 351.0 351 1
→

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

8. 0.662 0.662 ↓ 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.662 rows=352 loops=1)

  • Index Cond: (user_id = 3,397,025)
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 = 70,683)
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.066 0.807 ↓ 45.8 366 1
→

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

14. 0.014 0.141 ↓ 40.0 40 1
→

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

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

  • Index Cond: (user_id = 3,397,025)
16. 0.063 0.063 ↓ 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.035..0.063 rows=40 loops=1)

  • Index Cond: (employee_id = emp_1.id)
17. 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
18. 0.878 359.086 ↓ 1,824.0 1,824 1
→

Sort (cost=10,127.43..10,127.43 rows=1 width=12) (actual time=358.952..359.086 rows=1,824 loops=1)

  • Sort Key: tb.start_date
  • Sort Method: quicksort Memory: 134kB
19. 0.497 358.208 ↓ 1,824.0 1,824 1
→

Nested Loop (cost=9,816.64..10,127.42 rows=1 width=12) (actual time=167.085..358.208 rows=1,824 loops=1)

20. 1.058 352.239 ↓ 1,824.0 1,824 1
→

Nested Loop (cost=9,816.22..10,124.98 rows=1 width=28) (actual time=167.011..352.239 rows=1,824 loops=1)

21. 1.678 343.821 ↓ 1,840.0 1,840 1
→

Nested Loop (cost=9,815.65..10,122.39 rows=1 width=36) (actual time=166.962..343.821 rows=1,840 loops=1)

22. 0.993 336.623 ↓ 1,840.0 1,840 1
→

Hash Join (cost=9,815.02..10,119.74 rows=1 width=44) (actual time=166.782..336.623 rows=1,840 loops=1)

  • Hash Cond: (pos.position_code_id = emp_data.posid)
23. 1.150 334.451 ↓ 38.3 1,840 1
→

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

24. 0.927 327.781 ↓ 38.3 1,840 1
→

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

25. 0.012 9.808 ↓ 6.0 6 1
→

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

  • Group Key: other_loc_id.locid
26. 9.796 9.796 ↓ 6.0 6 1
→

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

27. 9.618 317.046 ↓ 6.4 307 6
→

Bitmap Heap Scan on timeblock tb (cost=9,814.21..10,001.33 rows=48 width=44) (actual time=51.440..52.841 rows=307 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 (last_published = 'Y'::bpchar))
  • Rows Removed by Filter: 57
  • Heap Blocks: exact=1,384
28. 9.554 307.428 ↓ 0.0 0 6
→

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

29. 30.846 30.846 ↓ 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.141..5.141 rows=49,794 loops=6)

  • Index Cond: (location_id = other_loc_id.locid)
30. 267.028 267.028 ↑ 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=133.514..133.514 rows=717,507 loops=2)

  • Index Cond: ((start_date >= '2019-11-01'::date) AND (start_date <= '2019-11-30'::date))
31. 5.520 5.520 ↑ 1.0 1 1,840
→

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,840)

  • Index Cond: (id = tb.position_id)
32. 0.038 1.179 ↓ 25.8 206 1
→

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 17kB
33. 0.164 1.141 ↓ 25.8 206 1
→

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

  • Group Key: emp_data.posid
34. 0.977 0.977 ↓ 45.8 366 1
→

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

35. 5.468 5.520 ↑ 1.0 1 1,840
→

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,840)

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

SubPlan (for Index Scan)
37. 0.001 0.004 ↑ 1.0 1 1
→

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

38. 0.003 0.003 ↑ 8.0 1 1
→

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

39. 0.048 0.048 ↓ 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.048 rows=366 loops=1)

40. 7.360 7.360 ↑ 1.0 1 1,840
→

Index Scan using employee_pkey on employee emp (cost=0.57..2.59 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=1,840)

  • Index Cond: (id = tb.employee_id)
  • Filter: (user_id <> 3397025)
41. 5.472 5.472 ↑ 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.003..0.003 rows=1 loops=1,824)

  • Index Cond: (id = pos.position_code_id)
  • Heap Fetches: 1,824