explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CY75

Settings
# exclusive inclusive rows x rows loops node
1. 7.540 245,332.372 ↓ 29,736.0 29,736 1

Unique (cost=26,182.18..26,182.19 rows=1 width=42) (actual time=245,320.957..245,332.372 rows=29,736 loops=1)

2.          

CTE selectedemployees

3. 6.227 7.641 ↓ 2.1 29,736 1

Bitmap Heap Scan on employee_preference_type (cost=371.91..9,194.74 rows=14,267 width=8) (actual time=1.470..7.641 rows=29,736 loops=1)

  • Recheck Cond: (preference_type_id = 3,206)
  • Filter: (deleted = 'N'::bpchar)
  • Heap Blocks: exact=272
4. 1.414 1.414 ↑ 1.0 29,736 1

Bitmap Index Scan on employee_preference_type_pt_idx (cost=0.00..368.34 rows=29,989 width=0) (actual time=1.414..1.414 rows=29,736 loops=1)

  • Index Cond: (preference_type_id = 3,206)
5.          

CTE canappcte

6. 0.002 0.015 ↑ 1.0 1 1

Result (cost=3.77..3.78 rows=1 width=1) (actual time=0.015..0.015 rows=1 loops=1)

7.          

Initplan (for Result)

8. 0.013 0.013 ↓ 0.0 0 1

Index Scan using canapproveattendancetype_preferencetype_idx on canapprovepreferencetype (cost=0.28..3.77 rows=1 width=0) (actual time=0.013..0.013 rows=0 loops=1)

  • Index Cond: (preferencetype_id = 3,206)
  • Filter: ((corporation_id = 70,683) AND (deleted = 'N'::bpchar) AND (type = 'assi'::bpchar))
  • Rows Removed by Filter: 2
9.          

Initplan (for Unique)

10. 0.017 0.017 ↑ 1.0 1 1

CTE Scan on canappcte (cost=0.00..0.02 rows=1 width=1) (actual time=0.017..0.017 rows=1 loops=1)

11. 109.212 245,324.815 ↓ 32,003.0 32,003 1

Sort (cost=16,983.63..16,983.63 rows=1 width=42) (actual time=245,320.954..245,324.815 rows=32,003 loops=1)

  • Sort Key: emp.id, ((((((u.first_name)::text || ' '::text) || (COALESCE(u.middle_name, ''::character varying))::text) || ' '::text) || (u.last_name)::text)), ((sl.* IS NOT NULL)), (CASE WHEN ($3 AND (capt.* IS NULL)) THEN true WHEN ((emp.id = capt.employee_id) AND (capt.approve_all = 'N'::bpchar)) THEN true ELSE false END)
  • Sort Method: quicksort Memory: 3,269kB
12. 214.836 245,215.603 ↓ 32,003.0 32,003 1

Nested Loop Left Join (cost=2.13..16,983.62 rows=1 width=42) (actual time=4.732..245,215.603 rows=32,003 loops=1)

13. 58,073.710 244,616.731 ↓ 32,003.0 32,003 1

Nested Loop Left Join (cost=1.85..16,980.02 rows=1 width=55) (actual time=4.692..244,616.731 rows=32,003 loops=1)

  • Join Filter: (sl.employee_id = emp.id)
  • Rows Removed by Join Filter: 951,609,205
14. 102.340 2,493.768 ↓ 32,003.0 32,003 1

Nested Loop (cost=1.85..16,516.34 rows=1 width=23) (actual time=0.202..2,493.768 rows=32,003 loops=1)

15. 161.142 1,635.068 ↓ 75,636.0 75,636 1

Nested Loop (cost=1.43..16,496.22 rows=1 width=31) (actual time=0.086..1,635.068 rows=75,636 loops=1)

16. 37.712 717.566 ↓ 442.3 75,636 1

Nested Loop (cost=1.00..16,044.05 rows=171 width=8) (actual time=0.072..717.566 rows=75,636 loops=1)

17. 2.240 8.040 ↓ 4.6 351 1

Nested Loop (cost=0.57..1,432.74 rows=76 width=16) (actual time=0.037..8.040 rows=351 loops=1)

18. 1.939 1.939 ↑ 1.2 351 1

Index Scan using i_loc_corp_id on location (cost=0.29..359.68 rows=431 width=8) (actual time=0.026..1.939 rows=351 loops=1)

  • Index Cond: (corporation_id = 70,683)
19. 3.861 3.861 ↑ 1.0 1 351

Index Only Scan using idx_location_id_del on location l (cost=0.29..2.49 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=351)

  • Index Cond: (id = location.id)
  • Heap Fetches: 351
20. 671.814 671.814 ↓ 1.6 215 351

Index Scan using user_loc_loc_id on user_location ul (cost=0.42..190.93 rows=132 width=16) (actual time=0.023..1.914 rows=215 loops=351)

  • Index Cond: (location_id = location.id)
  • Filter: ((termination_date IS NULL) OR (termination_date >= now()))
  • Rows Removed by Filter: 285
21. 756.360 756.360 ↑ 1.0 1 75,636

Index Scan using i_au_id_del on app_user u (cost=0.42..2.64 rows=1 width=23) (actual time=0.010..0.010 rows=1 loops=75,636)

  • Index Cond: (id = ul.user_id)
  • Filter: (corporation_id = 70,683)
22. 756.360 756.360 ↓ 0.0 0 75,636

Index Scan using i_emp_user_id on employee emp (cost=0.42..19.95 rows=17 width=16) (actual time=0.009..0.010 rows=0 loops=75,636)

  • Index Cond: (user_id = u.id)
  • Filter: (admin_user = 'N'::bpchar)
  • Rows Removed by Filter: 1
23. 184,049.253 184,049.253 ↓ 2.1 29,736 32,003

CTE Scan on selectedemployees sl (cost=0.00..285.34 rows=14,267 width=40) (actual time=0.001..5.751 rows=29,736 loops=32,003)

24. 384.036 384.036 ↓ 0.0 0 32,003

Index Scan using canapproveattendancetype_employee_idx on canapprovepreferencetype capt (cost=0.28..3.58 rows=1 width=99) (actual time=0.012..0.012 rows=0 loops=32,003)

  • Index Cond: (employee_id = 12,349,808)
  • Filter: ((type = 'assi'::bpchar) AND (deleted = 'N'::bpchar) AND (preferencetype_id = 3,206))