explain.depesz.com

PostgreSQL's explain analyze made readable

Result: c5u

Settings
# exclusive inclusive rows x rows loops node
1. 149.899 105,363.054 ↓ 130.0 130 1

Nested Loop Left Join (cost=261.53..395.34 rows=1 width=4,961) (actual time=1,028.084..105,363.054 rows=130 loops=1)

  • Join Filter: (pp_analysis.sid = dt.sid)
2. 0.645 105,213.155 ↓ 130.0 130 1

Nested Loop Left Join (cost=261.40..366.03 rows=1 width=421) (actual time=1,023.567..105,213.155 rows=130 loops=1)

  • Join Filter: (wt_companies.ctype = cttt.ctype)
3. 0.656 105,211.860 ↓ 130.0 130 1

Nested Loop Left Join (cost=261.26..365.66 rows=1 width=409) (actual time=1,023.556..105,211.860 rows=130 loops=1)

4. 0.520 105,210.034 ↓ 130.0 130 1

Nested Loop (cost=260.98..364.69 rows=1 width=387) (actual time=1,023.540..105,210.034 rows=130 loops=1)

5. 0.678 105,159.724 ↓ 130.0 130 1

Nested Loop (cost=195.41..271.87 rows=1 width=355) (actual time=1,022.826..105,159.724 rows=130 loops=1)

6. 0.526 105,158.396 ↓ 130.0 130 1

Nested Loop (cost=195.12..269.41 rows=1 width=349) (actual time=1,022.814..105,158.396 rows=130 loops=1)

7. 0.504 105,156.960 ↓ 130.0 130 1

Nested Loop (cost=194.84..268.41 rows=1 width=333) (actual time=1,022.802..105,156.960 rows=130 loops=1)

8. 0.478 105,155.676 ↓ 130.0 130 1

Nested Loop (cost=194.55..265.95 rows=1 width=313) (actual time=1,022.792..105,155.676 rows=130 loops=1)

9. 0.393 105,154.418 ↓ 130.0 130 1

Nested Loop (cost=194.41..265.41 rows=1 width=285) (actual time=1,022.782..105,154.418 rows=130 loops=1)

10. 0.458 105,153.635 ↓ 130.0 130 1

Nested Loop (cost=194.26..265.01 rows=1 width=269) (actual time=1,022.776..105,153.635 rows=130 loops=1)

11. 0.763 105,152.007 ↓ 130.0 130 1

Nested Loop (cost=193.98..263.00 rows=1 width=240) (actual time=1,022.763..105,152.007 rows=130 loops=1)

12. 0.455 105,150.334 ↓ 130.0 130 1

Nested Loop (cost=193.84..262.55 rows=1 width=232) (actual time=1,022.753..105,150.334 rows=130 loops=1)

  • Join Filter: (pp_analysis.cid = wt_companies.cid)
  • Rows Removed by Join Filter: 130
13. 3.609 105,149.749 ↓ 6.5 130 1

Subquery Scan on pp_analysis (cost=193.69..259.29 rows=20 width=208) (actual time=1,022.734..105,149.749 rows=130 loops=1)

  • Filter: (abs(pp_analysis.pcols[((string_to_array((string_to_array(array_dims(pp_analysis.pcols), ']'::text))[1], ':'::text))[2])::integer]) > '0'::double precision)
  • Rows Removed by Filter: 264
14. 105,130.058 105,146.140 ↓ 6.6 394 1

Nested Loop (cost=193.69..257.64 rows=60 width=208) (actual time=6.510..105,146.140 rows=394 loops=1)

15. 0.005 0.012 ↑ 12.0 1 1

HashAggregate (cost=0.33..0.45 rows=12 width=4) (actual time=0.012..0.012 rows=1 loops=1)

  • Group Key: (1)
16. 0.004 0.007 ↑ 12.0 1 1

Append (cost=0.00..0.30 rows=12 width=4) (actual time=0.005..0.007 rows=1 loops=1)

17. 0.002 0.002 ↓ 0.0 0 1

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

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

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

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

Result (cost=0.00..0.01 rows=1 width=4) (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=4) (actual time=0.000..0.000 rows=0 loops=1)

  • One-Time Filter: false
21. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

22. 0.000 0.000 ↓ 0.0 0 1

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

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

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

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

Result (cost=0.00..0.01 rows=1 width=4) (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=4) (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=4) (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=4) (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=4) (actual time=0.000..0.000 rows=0 loops=1)

  • One-Time Filter: false
29. 0.718 16.070 ↓ 78.8 394 1

Materialize (cost=193.36..238.04 rows=5 width=132) (actual time=1.133..16.070 rows=394 loops=1)

30. 0.630 15.352 ↓ 78.8 394 1

Nested Loop (cost=193.36..238.01 rows=5 width=132) (actual time=1.129..15.352 rows=394 loops=1)

31. 1.074 10.388 ↓ 394.0 394 1

Nested Loop (cost=193.36..220.43 rows=1 width=128) (actual time=1.106..10.388 rows=394 loops=1)

32. 1.112 6.950 ↓ 394.0 394 1

Nested Loop (cost=193.07..219.04 rows=1 width=84) (actual time=1.094..6.950 rows=394 loops=1)

33. 0.952 2.686 ↓ 131.3 394 1

Hash Join (cost=192.78..215.21 rows=3 width=28) (actual time=1.082..2.686 rows=394 loops=1)

  • Hash Cond: (wt_users_history.employee_id = u.employee_id)
34. 1.023 1.237 ↑ 2.3 395 1

HashAggregate (cost=126.18..135.14 rows=896 width=12) (actual time=0.576..1.237 rows=395 loops=1)

  • Group Key: wt_users_history.sid, wt_users_history.employee_id
35. 0.214 0.214 ↑ 1.0 865 1

Index Scan using wt_users_history_s on wt_users_history (cost=0.29..117.16 rows=902 width=16) (actual time=0.013..0.214 rows=865 loops=1)

  • Index Cond: (sid = 372000)
36. 0.084 0.497 ↓ 3.9 394 1

Hash (cost=65.35..65.35 rows=100 width=20) (actual time=0.497..0.497 rows=394 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
37. 0.095 0.413 ↓ 3.9 394 1

Nested Loop (cost=0.70..65.35 rows=100 width=20) (actual time=0.059..0.413 rows=394 loops=1)

38. 0.020 0.020 ↑ 1.0 1 1

Index Only Scan using dates_day on dates (cost=0.29..2.50 rows=1 width=4) (actual time=0.019..0.020 rows=1 loops=1)

  • Index Cond: (day = (now())::date)
  • Heap Fetches: 1
39. 0.298 0.298 ↓ 3.9 394 1

Index Scan using wt_users_history_s_f_t on wt_users_history u (cost=0.42..61.84 rows=100 width=28) (actual time=0.037..0.298 rows=394 loops=1)

  • Index Cond: ((sid = 372000) AND (dates.day >= f) AND (dates.day <= t))
40. 3.152 3.152 ↑ 1.0 1 394

Index Scan using wt_users_details_frequently_s_e on wt_users_details_frequently wt_users_details_frequently_1 (cost=0.29..1.27 rows=1 width=60) (actual time=0.007..0.008 rows=1 loops=394)

  • Index Cond: ((sid = 372000) AND (employee_id = u.employee_id))
41. 2.364 2.364 ↑ 1.0 1 394

Index Scan using wt_users_details_rarely_s_e on wt_users_details_rarely wt_users_details_rarely_1 (cost=0.29..1.38 rows=1 width=60) (actual time=0.005..0.006 rows=1 loops=394)

  • Index Cond: ((sid = 372000) AND (employee_id = u.employee_id))
42. 0.788 4.334 ↑ 5.0 1 394

Subquery Scan on years (cost=0.00..17.53 rows=5 width=4) (actual time=0.009..0.011 rows=1 loops=394)

  • Filter: (years.pp_year = 2019)
  • Rows Removed by Filter: 13
43. 3.546 3.546 ↑ 71.4 14 394

ProjectSet (cost=0.00..5.03 rows=1,000 width=4) (actual time=0.006..0.009 rows=14 loops=394)

44. 0.000 0.000 ↑ 1.0 1 394

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

45. 0.117 0.130 ↑ 1.0 2 130

Materialize (cost=0.15..2.66 rows=2 width=32) (actual time=0.001..0.001 rows=2 loops=130)

46. 0.013 0.013 ↑ 1.0 2 1

Index Scan using sid_comp_indx on wt_companies (cost=0.15..2.65 rows=2 width=32) (actual time=0.011..0.013 rows=2 loops=1)

  • Index Cond: (sid = 372000)
47. 0.910 0.910 ↑ 1.0 1 130

Index Scan using wt_status_ssc on wt_status (cost=0.14..0.39 rows=1 width=20) (actual time=0.007..0.007 rows=1 loops=130)

  • Index Cond: ((sid = 372000) AND (status_code = pp_analysis.status_code))
48. 1.170 1.170 ↑ 1.0 1 130

Index Scan using wt_depts_s_d on wt_depts (cost=0.28..2.01 rows=1 width=41) (actual time=0.008..0.009 rows=1 loops=130)

  • Index Cond: ((sid = 372000) AND (did = pp_analysis.did))
49. 0.390 0.390 ↑ 1.0 1 130

Index Scan using wt_pps_splc on wt_pps (cost=0.14..0.39 rows=1 width=24) (actual time=0.002..0.003 rows=1 loops=130)

  • Index Cond: ((sid = 372000) AND (ppid = pp_analysis.ppid))
50. 0.780 0.780 ↑ 1.0 1 130

Index Scan using wt_et on wt_employeetypes (cost=0.14..0.50 rows=1 width=36) (actual time=0.006..0.006 rows=1 loops=130)

  • Index Cond: ((sid = 372000) AND (etype = pp_analysis.etype))
51. 0.780 0.780 ↑ 1.0 1 130

Index Scan using wt_users_details_frequently_s_e on wt_users_details_frequently (cost=0.29..2.46 rows=1 width=28) (actual time=0.006..0.006 rows=1 loops=130)

  • Index Cond: ((sid = 372000) AND (employee_id = pp_analysis.supervisor_id))
52. 0.910 0.910 ↑ 1.0 1 130

Index Scan using wt_roles_s_r on wt_roles ro (cost=0.28..0.79 rows=1 width=28) (actual time=0.007..0.007 rows=1 loops=130)

  • Index Cond: ((sid = 372000) AND (role_code = pp_analysis.role_code))
53. 0.650 0.650 ↑ 1.0 1 130

Index Scan using wt_users_details_rarely_s_e on wt_users_details_rarely (cost=0.29..2.46 rows=1 width=14) (actual time=0.005..0.005 rows=1 loops=130)

  • Index Cond: ((sid = 372000) AND (employee_id = pp_analysis.employee_id))
54. 47.320 49.790 ↑ 25.0 1 130

GroupAggregate (cost=65.57..92.32 rows=25 width=36) (actual time=0.383..0.383 rows=1 loops=130)

  • Group Key: wt_et_cols_defs.sid
55. 2.191 2.470 ↑ 1.0 100 130

Sort (cost=65.57..65.82 rows=100 width=672) (actual time=0.003..0.019 rows=100 loops=130)

  • Sort Key: wt_et_cols_defs.pcol_number
  • Sort Method: quicksort Memory: 32kB
56. 0.279 0.279 ↑ 1.0 100 1

Seq Scan on wt_et_cols_defs (cost=0.00..62.25 rows=100 width=672) (actual time=0.038..0.279 rows=100 loops=1)

  • Filter: (sid = 372000)
  • Rows Removed by Filter: 2400
57. 1.170 1.170 ↑ 1.0 1 130

Index Scan using wt_locations_s_l on wt_locations (cost=0.28..0.96 rows=1 width=30) (actual time=0.008..0.009 rows=1 loops=130)

  • Index Cond: ((pp_analysis.sid = sid) AND (sid = 372000) AND (pp_analysis.lid = lid))
58. 0.650 0.650 ↑ 2.0 1 130

Index Scan using wt_companytypes_s_ct on wt_companytypes cttt (cost=0.14..0.34 rows=2 width=24) (actual time=0.005..0.005 rows=1 loops=130)

  • Index Cond: ((pp_analysis.sid = sid) AND (sid = 372000))
59. 0.000 0.000 ↓ 0.0 0 130

Index Scan using wt_dept_types_s_dt on wt_dept_types dt (cost=0.13..0.31 rows=1 width=226) (actual time=0.000..0.000 rows=0 loops=130)

  • Index Cond: ((sid = 372000) AND (wt_depts.dtype = dtype))
Planning time : 44.435 ms
Execution time : 105,363.986 ms