explain.depesz.com

PostgreSQL's explain analyze made readable

Result: G1R9

Settings
# exclusive inclusive rows x rows loops node
1. 1.207 31,363.609 ↓ 1.3 126 1

Hash Join (cost=1,656.89..4,362.01 rows=100 width=6,246) (actual time=31,322.006..31,363.609 rows=126 loops=1)

  • Hash Cond: (pp_analysis.employee_id = public.wt_users_details_rarely.employee_id)
2. 41.970 31,361.762 ↓ 1.3 126 1

Group (cost=1,152.58..3,842.58 rows=100 width=428) (actual time=31,321.339..31,361.762 rows=126 loops=1)

3. 0.637 31,319.792 ↓ 1.3 126 1

Sort (cost=1,152.58..1,152.83 rows=100 width=428) (actual time=31,319.775..31,319.792 rows=126 loops=1)

  • Sort Key: pp_analysis.sid, pp_analysis.employee_id, pp_analysis.employee_number, pp_analysis.badge_number, pp_analysis.employee_name, pp_analysis.id_number, pp_analysis.payroll_interface_code, pp_analysis.pp, wt_pps.ppname
  • Sort Method: quicksort Memory: 530kB
4. 0.497 31,319.155 ↓ 1.3 126 1

Nested Loop (cost=458.92..1,149.26 rows=100 width=428) (actual time=223.647..31,319.155 rows=126 loops=1)

5. 0.361 31,293.080 ↓ 126.0 126 1

Nested Loop Left Join (cost=393.35..1,078.19 rows=1 width=396) (actual time=223.126..31,293.080 rows=126 loops=1)

  • Join Filter: ((pp_analysis.sid = dt.sid) AND (wt_depts.dtype = dt.dtype))
6. 0.579 31,292.341 ↓ 126.0 126 1

Nested Loop Left Join (cost=393.35..1,077.10 rows=1 width=383) (actual time=223.120..31,292.341 rows=126 loops=1)

  • Join Filter: ((pp_analysis.sid = cttt.sid) AND (wt_companies.ctype = cttt.ctype))
7. 0.237 31,290.754 ↓ 126.0 126 1

Nested Loop Left Join (cost=393.35..1,075.37 rows=1 width=371) (actual time=223.110..31,290.754 rows=126 loops=1)

  • Join Filter: (pp_analysis.sid = wt_locations.sid)
8. 0.310 31,289.257 ↓ 126.0 126 1

Nested Loop (cost=393.35..1,067.08 rows=1 width=349) (actual time=223.102..31,289.257 rows=126 loops=1)

9. 0.249 31,288.191 ↓ 126.0 126 1

Nested Loop (cost=393.35..1,065.98 rows=1 width=333) (actual time=223.093..31,288.191 rows=126 loops=1)

10. 0.214 31,287.312 ↓ 126.0 126 1

Nested Loop (cost=393.35..1,058.32 rows=1 width=313) (actual time=223.084..31,287.312 rows=126 loops=1)

11. 0.167 31,286.468 ↓ 126.0 126 1

Nested Loop (cost=393.35..1,057.49 rows=1 width=285) (actual time=223.077..31,286.468 rows=126 loops=1)

12. 0.210 31,284.159 ↓ 126.0 126 1

Nested Loop (cost=393.35..1,056.67 rows=1 width=261) (actual time=223.066..31,284.159 rows=126 loops=1)

13. 0.283 31,282.815 ↓ 126.0 126 1

Nested Loop (cost=393.35..1,051.93 rows=1 width=232) (actual time=223.054..31,282.815 rows=126 loops=1)

14. 0.331 31,281.650 ↓ 31.5 126 1

Hash Join (cost=393.35..1,049.33 rows=4 width=224) (actual time=223.038..31,281.650 rows=126 loops=1)

  • Hash Cond: (pp_analysis.ppid = wt_pps.ppid)
15. 3.010 31,281.296 ↓ 2.9 126 1

Subquery Scan on pp_analysis (cost=389.46..1,045.08 rows=44 width=208) (actual time=223.008..31,281.296 rows=126 loops=1)

  • Filter: (abs(pp_analysis.pcols[((string_to_array((string_to_array(array_dims(pp_analysis.pcols), ']'::text))[1], ':'::text))[2])::integer]) > 0::doubl
16. 31,271.450 31,278.286 ↓ 3.0 401 1

Nested Loop (cost=389.46..1,041.45 rows=132 width=136) (actual time=222.997..31,278.286 rows=401 loops=1)

17. 0.003 0.005 ↑ 12.0 1 1

HashAggregate (cost=0.27..0.39 rows=12 width=0) (actual time=0.005..0.005 rows=1 loops=1)

18. 0.002 0.002 ↑ 12.0 1 1

Append (cost=0.00..0.24 rows=12 width=0) (actual time=0.001..0.002 rows=1 loops=1)

19. 0.000 0.000 ↓ 0.0 0 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=1)

  • One-Time Filter: false
20. 0.000 0.000 ↓ 0.0 0 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=1)

  • One-Time Filter: false
21. 0.000 0.000 ↓ 0.0 0 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=1)

  • One-Time Filter: false
22. 0.000 0.000 ↓ 0.0 0 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=1)

  • One-Time Filter: false
23. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=1)

24. 0.000 0.000 ↓ 0.0 0 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=1)

  • One-Time Filter: false
25. 0.000 0.000 ↓ 0.0 0 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=1)

  • One-Time Filter: false
26. 0.000 0.000 ↓ 0.0 0 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=1)

  • One-Time Filter: false
27. 0.000 0.000 ↓ 0.0 0 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=1)

  • One-Time Filter: false
28. 0.000 0.000 ↓ 0.0 0 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=1)

  • One-Time Filter: false
29. 0.000 0.000 ↓ 0.0 0 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=1)

  • One-Time Filter: false
30. 0.000 0.000 ↓ 0.0 0 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=1)

  • One-Time Filter: false
31. 0.576 6.831 ↓ 36.5 401 1

Materialize (cost=389.19..998.39 rows=11 width=132) (actual time=0.930..6.831 rows=401 loops=1)

32. 0.192 6.255 ↓ 36.5 401 1

Nested Loop (cost=389.19..998.34 rows=11 width=132) (actual time=0.927..6.255 rows=401 loops=1)

33. 0.002 0.026 ↑ 1.0 1 1

Subquery Scan on years (cost=0.00..0.04 rows=1 width=4) (actual time=0.023..0.026 rows=1 loops=1)

  • Filter: (years.pp_year = 2019)
34. 0.024 0.024 ↓ 14.0 14 1

Result (cost=0.00..0.03 rows=1 width=0) (actual time=0.020..0.024 rows=14 loops=1)

35. 0.888 6.037 ↓ 36.5 401 1

Nested Loop (cost=389.19..998.19 rows=11 width=128) (actual time=0.902..6.037 rows=401 loops=1)

36. 0.771 3.946 ↓ 16.7 401 1

Nested Loop (cost=389.19..850.17 rows=24 width=84) (actual time=0.891..3.946 rows=401 loops=1)

37. 0.483 1.972 ↓ 7.7 401 1

Hash Join (cost=389.19..608.34 rows=52 width=28) (actual time=0.879..1.972 rows=401 loops=1)

  • Hash Cond: (u.employee_id = foo.employee_id)
38. 0.184 0.665 ↓ 4.2 401 1

Nested Loop (cost=0.00..212.87 rows=96 width=20) (actual time=0.051..0.665 rows=401 loops=1)

39. 0.012 0.012 ↑ 1.0 1 1

Index Scan using dates_day on dates (cost=0.00..8.27 rows=1 width=4) (actual time=0.011..0.012 rows=1 loops=1)

  • Index Cond: (day = (now())::date)
40. 0.469 0.469 ↓ 4.2 401 1

Index Scan using wt_users_history_s_f_t on wt_users_history u (cost=0.00..203.16 rows=96 width=28) (actual time=0.038..0.469 rows=401 loops=1)

  • Index Cond: ((u.sid = 372000) AND (dates.day >= u.f) AND (dates.day <= u.t))
41. 0.058 0.824 ↑ 1.2 402 1

Hash (cost=383.37..383.37 rows=465 width=12) (actual time=0.824..0.824 rows=402 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 18kB
42. 0.063 0.766 ↑ 1.2 402 1

Subquery Scan on foo (cost=371.75..383.37 rows=465 width=12) (actual time=0.652..0.766 rows=402 loops=1)

43. 0.407 0.703 ↑ 1.2 402 1

HashAggregate (cost=371.75..378.72 rows=465 width=16) (actual time=0.652..0.703 rows=402 loops=1)

44. 0.296 0.296 ↓ 1.0 884 1

Index Scan using wt_users_history_s on wt_users_history (cost=0.00..365.25 rows=866 width=16) (actual time=0.013..0.296 rows=884 loops=1)

  • Index Cond: (sid = 372000)
45. 1.203 1.203 ↑ 1.0 1 401

Index Scan using wt_users_details_frequently_s_e on wt_users_details_frequently (cost=0.00..4.64 rows=1 width=60) (actual time=0.003..0.003 rows=1 loops=401)

  • Index Cond: ((public.wt_users_details_frequently.sid = 372000) AND (public.wt_users_details_frequently.employee_id = u.employee_id))
46. 1.203 1.203 ↑ 1.0 1 401

Index Scan using wt_users_details_rarely_s_e on wt_users_details_rarely (cost=0.00..6.15 rows=1 width=60) (actual time=0.003..0.003 rows=1 loops=401)

  • Index Cond: ((public.wt_users_details_rarely.sid = 372000) AND (public.wt_users_details_rarely.employee_id = u.employee_id))
47. 0.002 0.023 ↑ 1.0 4 1

Hash (cost=3.84..3.84 rows=4 width=24) (actual time=0.023..0.023 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
48. 0.021 0.021 ↑ 1.0 4 1

Seq Scan on wt_pps (cost=0.00..3.84 rows=4 width=24) (actual time=0.008..0.021 rows=4 loops=1)

  • Filter: (sid = 372000)
49. 0.882 0.882 ↑ 1.0 1 126

Index Scan using wt_status_ssc on wt_status (cost=0.00..0.64 rows=1 width=20) (actual time=0.007..0.007 rows=1 loops=126)

  • Index Cond: ((wt_status.sid = 372000) AND (wt_status.status_code = pp_analysis.status_code))
50. 1.134 1.134 ↑ 1.0 1 126

Index Scan using did_indx on wt_depts (cost=0.00..4.73 rows=1 width=41) (actual time=0.008..0.009 rows=1 loops=126)

  • Index Cond: (wt_depts.did = pp_analysis.did)
  • Filter: (wt_depts.sid = 372000)
51. 2.142 2.142 ↑ 1.0 1 126

Index Scan using cid_indx on wt_companies (cost=0.00..0.82 rows=1 width=32) (actual time=0.007..0.017 rows=1 loops=126)

  • Index Cond: (wt_companies.cid = pp_analysis.cid)
  • Filter: (wt_companies.sid = 372000)
52. 0.630 0.630 ↑ 1.0 1 126

Index Scan using wt_et on wt_employeetypes (cost=0.00..0.82 rows=1 width=36) (actual time=0.005..0.005 rows=1 loops=126)

  • Index Cond: ((wt_employeetypes.sid = 372000) AND (wt_employeetypes.etype = pp_analysis.etype))
53. 0.630 0.630 ↑ 1.0 1 126

Index Scan using wt_users_details_frequently_s_e on wt_users_details_frequently (cost=0.00..7.64 rows=1 width=28) (actual time=0.005..0.005 rows=1 loops=126)

  • Index Cond: ((public.wt_users_details_frequently.sid = 372000) AND (public.wt_users_details_frequently.employee_id = pp_analysis.supervisor_id))
54. 0.756 0.756 ↑ 1.0 1 126

Index Scan using wt_roles_s_r on wt_roles ro (cost=0.00..1.09 rows=1 width=28) (actual time=0.006..0.006 rows=1 loops=126)

  • Index Cond: ((ro.sid = 372000) AND (ro.role_code = pp_analysis.role_code))
55. 1.260 1.260 ↑ 1.0 1 126

Index Scan using lid_indx on wt_locations (cost=0.00..8.27 rows=1 width=30) (actual time=0.004..0.010 rows=1 loops=126)

  • Index Cond: (pp_analysis.lid = wt_locations.lid)
  • Filter: (wt_locations.sid = 372000)
56. 1.008 1.008 ↑ 1.0 2 126

Seq Scan on wt_companytypes cttt (cost=0.00..1.70 rows=2 width=24) (actual time=0.004..0.008 rows=2 loops=126)

  • Filter: (cttt.sid = 372000)
57. 0.378 0.378 ↓ 0.0 0 126

Seq Scan on wt_dept_types dt (cost=0.00..1.07 rows=1 width=21) (actual time=0.003..0.003 rows=0 loops=126)

  • Filter: (dt.sid = 372000)
58. 24.066 25.578 ↑ 100.0 1 126

GroupAggregate (cost=65.57..68.57 rows=100 width=5) (actual time=0.203..0.203 rows=1 loops=126)

59. 1.289 1.512 ↑ 1.0 100 126

Sort (cost=65.57..65.82 rows=100 width=62) (actual time=0.002..0.012 rows=100 loops=126)

  • Sort Key: wt_et_cols_defs.pcol_number
  • Sort Method: quicksort Memory: 39kB
60. 0.223 0.223 ↑ 1.0 100 1

Seq Scan on wt_et_cols_defs (cost=0.00..62.25 rows=100 width=62) (actual time=0.026..0.223 rows=100 loops=1)

  • Filter: (sid = 372000)
61. 0.112 0.640 ↓ 1.0 401 1

Hash (cost=499.36..499.36 rows=396 width=14) (actual time=0.640..0.640 rows=401 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
62. 0.528 0.528 ↓ 1.0 401 1

Index Scan using wt_users_details_rarely_s on wt_users_details_rarely (cost=0.00..499.36 rows=396 width=14) (actual time=0.022..0.528 rows=401 loops=1)

  • Index Cond: (sid = 372000)