explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xlcK

Settings
# exclusive inclusive rows x rows loops node
1. 0.291 333.983 ↓ 24.0 24 1

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

  • Group Key: tb.start_date
2.          

CTE other_loc_id

3. 0.024 9.784 ↓ 6.0 6 1

Unique (cost=107.22..107.22 rows=1 width=8) (actual time=9.740..9.784 rows=6 loops=1)

4. 0.163 9.760 ↓ 344.0 344 1

Sort (cost=107.22..107.22 rows=1 width=8) (actual time=9.739..9.760 rows=344 loops=1)

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

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

6. 0.215 6.650 ↓ 344.0 344 1

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

7. 0.155 2.223 ↓ 351.0 351 1

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

8. 0.660 0.660 ↓ 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.660 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.068 0.807 ↓ 45.8 366 1

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

14. 0.014 0.139 ↓ 40.0 40 1

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

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

  • Index Cond: (user_id = 3397025)
16. 0.062 0.062 ↓ 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.036..0.062 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.873 333.692 ↓ 1,824.0 1,824 1

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

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

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

20. 1.056 327.343 ↓ 1,824.0 1,824 1

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

21. 1.674 318.927 ↓ 1,840.0 1,840 1

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

22. 0.983 311.733 ↓ 1,840.0 1,840 1

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

  • Hash Cond: (pos.position_code_id = emp_data.posid)
23. 1.141 309.565 ↓ 38.3 1,840 1

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

24. 0.930 302.904 ↓ 38.3 1,840 1

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

25. 0.012 9.798 ↓ 6.0 6 1

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

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

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

27. 9.672 292.176 ↓ 6.4 307 6

Bitmap Heap Scan on timeblock tb (cost=9,814.21..10,001.33 rows=48 width=44) (actual time=47.291..48.696 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=1384
28. 10.464 282.504 ↓ 0.0 0 6

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

29. 30.768 30.768 ↓ 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.128..5.128 rows=49,794 loops=6)

  • Index Cond: (location_id = other_loc_id.locid)
30. 241.272 241.272 ↑ 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=120.636..120.636 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.040 1.185 ↓ 25.8 206 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
33. 0.171 1.145 ↓ 25.8 206 1

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

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

CTE Scan on emp_data (cost=0.00..0.16 rows=8 width=8) (actual time=0.140..0.974 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. 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