explain.depesz.com

PostgreSQL's explain analyze made readable

Result: axhX

Settings
# exclusive inclusive rows x rows loops node
1. 2.151 3,405.828 ↑ 51.5 159 1

Hash Left Join (cost=35,489.38..207,969.94 rows=8,194 width=3,846) (actual time=1,077.604..3,405.828 rows=159 loops=1)

  • Hash Cond: ((t1_0.employee_id = t22_0.employee_id) AND (t1_0.peo_id = t22_0.peo_id))
2. 0.877 3,305.603 ↑ 51.5 159 1

Nested Loop Left Join (cost=29,417.02..145,944.46 rows=8,194 width=4,019) (actual time=986.464..3,305.603 rows=159 loops=1)

3. 0.410 3,303.454 ↑ 13.3 159 1

Hash Left Join (cost=29,416.74..145,286.83 rows=2,111 width=3,688) (actual time=986.426..3,303.454 rows=159 loops=1)

  • Hash Cond: ((t17_0.entity_id = t19_0.entity_id) AND (t17_0.peo_id = t19_0.peo_id))
4. 0.518 3,303.034 ↑ 13.3 159 1

Hash Left Join (cost=29,415.64..145,274.65 rows=2,111 width=3,628) (actual time=986.393..3,303.034 rows=159 loops=1)

  • Hash Cond: ((t17_0.employer_id = t18_0.employer_id) AND (t17_0.peo_id = t18_0.peo_id))
5. 5.331 3,302.386 ↑ 13.3 159 1

Nested Loop Left Join (cost=29,403.46..145,251.34 rows=2,111 width=3,520) (actual time=986.241..3,302.386 rows=159 loops=1)

6. 0.534 3,295.942 ↑ 13.3 159 1

Hash Left Join (cost=29,403.18..144,612.82 rows=2,111 width=3,122) (actual time=986.199..3,295.942 rows=159 loops=1)

  • Hash Cond: ((t8_0.peo_id = t16_0.peo_id) AND (t8_0.job_class = t16_0.pos_class))
7. 0.503 3,293.495 ↑ 13.3 159 1

Hash Left Join (cost=29,249.83..144,448.37 rows=2,111 width=2,754) (actual time=984.260..3,293.495 rows=159 loops=1)

  • Hash Cond: ((t2_0.peo_id = t15_0.peo_id) AND (t2_0.client_id = t15_0.client_id) AND (t2_0.pay_group = t15_0.pay_group))
8. 0.756 3,287.460 ↑ 13.3 159 1

Nested Loop Left Join (cost=28,995.91..144,177.83 rows=2,111 width=2,740) (actual time=978.702..3,287.460 rows=159 loops=1)

9. 0.595 3,284.955 ↑ 13.3 159 1

Nested Loop Left Join (cost=28,995.49..143,170.28 rows=2,111 width=2,690) (actual time=978.666..3,284.955 rows=159 loops=1)

10. 0.677 3,284.201 ↑ 1.6 159 1

Nested Loop Left Join (cost=28,995.22..143,093.10 rows=257 width=2,683) (actual time=978.645..3,284.201 rows=159 loops=1)

11. 0.719 3,283.365 ↑ 1.6 159 1

Nested Loop Left Join (cost=28,995.07..143,050.05 rows=257 width=2,665) (actual time=978.625..3,283.365 rows=159 loops=1)

12. 0.616 3,282.646 ↑ 1.6 159 1

Nested Loop Left Join (cost=28,994.65..142,919.89 rows=257 width=2,653) (actual time=978.606..3,282.646 rows=159 loops=1)

13. 0.705 3,281.394 ↑ 1.6 159 1

Nested Loop Left Join (cost=28,994.52..142,879.54 rows=257 width=2,634) (actual time=978.580..3,281.394 rows=159 loops=1)

14. 0.762 3,279.576 ↑ 1.6 159 1

Nested Loop Left Join (cost=28,994.24..142,802.22 rows=257 width=2,626) (actual time=978.548..3,279.576 rows=159 loops=1)

15. 0.835 3,278.814 ↑ 1.6 159 1

Nested Loop Left Join (cost=28,993.97..142,727.01 rows=257 width=2,608) (actual time=978.524..3,278.814 rows=159 loops=1)

16. 0.698 3,276.548 ↑ 1.6 159 1

Nested Loop Left Join (cost=28,993.68..142,645.85 rows=257 width=2,341) (actual time=978.490..3,276.548 rows=159 loops=1)

17. 0.369 3,275.532 ↑ 1.6 159 1

Hash Left Join (cost=28,993.40..142,565.92 rows=257 width=2,329) (actual time=978.467..3,275.532 rows=159 loops=1)

  • Hash Cond: ((t0_0.client_id = t7_0.client_id) AND (t0_0.peo_id = t7_0.peo_id) AND (t0_0.work_group_code = t7_0.work_group))
18. 0.414 3,275.118 ↑ 1.6 159 1

Hash Left Join (cost=28,990.66..142,561.16 rows=257 width=2,316) (actual time=978.402..3,275.118 rows=159 loops=1)

  • Hash Cond: ((t0_0.type_code = t6_0.type_code) AND (t0_0.peo_id = t6_0.peo_id))
19. 0.460 3,274.675 ↑ 1.6 159 1

Hash Left Join (cost=28,977.41..142,546.56 rows=257 width=2,252) (actual time=978.354..3,274.675 rows=159 loops=1)

  • Hash Cond: ((t0_0.status_code = t5_0.status_code) AND (t0_0.peo_id = t5_0.peo_id))
20. 0.000 3,274.198 ↑ 1.6 159 1

Hash Left Join (cost=28,963.66..142,531.46 rows=257 width=2,156) (actual time=978.314..3,274.198 rows=159 loops=1)

  • Hash Cond: ((t0_0.division_code = t4_0.division_code) AND (t0_0.peo_id = t4_0.peo_id) AND (t0_0.client_id = t4_0.client_id))
21. 0.000 3,297.418 ↑ 1.6 159 1

Gather (cost=28,939.70..142,505.47 rows=257 width=2,141) (actual time=977.948..3,297.418 rows=159 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
22. 186.251 3,338.650 ↑ 2.0 53 3 / 3

Nested Loop Left Join (cost=27,939.70..141,479.77 rows=107 width=2,141) (actual time=826.364..3,338.650 rows=53 loops=3)

  • Filter: ((t0_0.modified > '2020-05-01 17:48:04.885'::timestamp without time zone) OR (t2_0.modified > '2020-05-01 17:48:04.885'::timestamp without time zone) OR (t1_0.modified > '2020-05-01 17:48:04.885'::timestamp without time zone))
  • Rows Removed by Filter: 94,052
23. 625.981 1,176.194 ↑ 1.2 94,105 3 / 3

Parallel Hash Left Join (cost=27,939.27..63,877.15 rows=117,631 width=1,232) (actual time=737.402..1,176.194 rows=94,105 loops=3)

  • Hash Cond: ((t0_0.peo_id = t1_0.peo_id) AND (t0_0.employee_id = t1_0.employee_id))
24. 166.682 166.682 ↑ 1.2 94,105 3 / 3

Parallel Seq Scan on employee_com t0_0 (cost=0.00..7,659.31 rows=117,631 width=628) (actual time=0.011..166.682 rows=94,105 loops=3)

25. 201.820 383.531 ↑ 1.2 91,465 3 / 3

Parallel Hash (cost=17,403.31..17,403.31 rows=114,331 width=604) (actual time=383.531..383.531 rows=91,465 loops=3)

  • Buckets: 8,192 Batches: 64 Memory Usage: 992kB
26. 181.711 181.711 ↑ 1.2 91,465 3 / 3

Parallel Seq Scan on employee_per t1_0 (cost=0.00..17,403.31 rows=114,331 width=604) (actual time=0.009..181.711 rows=91,465 loops=3)

27. 1,976.205 1,976.205 ↑ 1.0 1 282,315 / 3

Index Scan using pk_employee_pay on employee_pay t2_0 (cost=0.42..0.64 rows=1 width=940) (actual time=0.021..0.021 rows=1 loops=282,315)

  • Index Cond: ((t0_0.peo_id = peo_id) AND (t0_0.client_id = client_id) AND (t0_0.employee_id = employee_id))
28. 0.206 0.333 ↑ 1.2 533 1

Hash (cost=13.17..13.17 rows=617 width=34) (actual time=0.333..0.333 rows=533 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 44kB
29. 0.127 0.127 ↑ 1.2 533 1

Seq Scan on division_codes t4_0 (cost=0.00..13.17 rows=617 width=34) (actual time=0.009..0.127 rows=533 loops=1)

30. 0.008 0.017 ↑ 8.8 17 1

Hash (cost=11.50..11.50 rows=150 width=160) (actual time=0.017..0.017 rows=17 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
31. 0.009 0.009 ↑ 8.8 17 1

Seq Scan on employee_status_codes t5_0 (cost=0.00..11.50 rows=150 width=160) (actual time=0.005..0.009 rows=17 loops=1)

32. 0.015 0.029 ↑ 3.4 38 1

Hash (cost=11.30..11.30 rows=130 width=128) (actual time=0.029..0.029 rows=38 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
33. 0.014 0.014 ↑ 3.4 38 1

Seq Scan on employee_type_codes t6_0 (cost=0.00..11.30 rows=130 width=128) (actual time=0.006..0.014 rows=38 loops=1)

34. 0.026 0.045 ↑ 1.0 63 1

Hash (cost=1.63..1.63 rows=63 width=32) (actual time=0.045..0.045 rows=63 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
35. 0.019 0.019 ↑ 1.0 63 1

Seq Scan on work_groups t7_0 (cost=0.00..1.63 rows=63 width=32) (actual time=0.006..0.019 rows=63 loops=1)

36. 0.318 0.318 ↓ 0.0 0 159

Index Scan using pk_dept_codes on dept_codes t3_0 (cost=0.29..0.31 rows=1 width=29) (actual time=0.002..0.002 rows=0 loops=159)

  • Index Cond: ((t0_0.peo_id = peo_id) AND (t0_0.client_id = client_id) AND (t0_0.dept_code = dept_code))
37. 1.431 1.431 ↑ 1.0 1 159

Index Scan using pk_job_codes on job_codes t8_0 (cost=0.29..0.32 rows=1 width=286) (actual time=0.009..0.009 rows=1 loops=159)

  • Index Cond: ((t0_0.peo_id = peo_id) AND (t0_0.client_id = client_id) AND (t0_0.job_code = job_code))
38. 0.000 0.000 ↓ 0.0 0 159

Index Scan using pk_union_codes on union_codes t9_0 (cost=0.27..0.29 rows=1 width=42) (actual time=0.000..0.000 rows=0 loops=159)

  • Index Cond: ((t0_0.peo_id = peo_id) AND (t0_0.client_id = client_id) AND (t0_0.union_code = union_code))
39. 1.113 1.113 ↑ 1.0 1 159

Index Scan using pk_benefit_groups on benefit_groups t10_0 (cost=0.28..0.30 rows=1 width=22) (actual time=0.007..0.007 rows=1 loops=159)

  • Index Cond: ((t0_0.peo_id = peo_id) AND (t0_0.client_id = client_id) AND (t0_0.benefit_group = group_id))
40. 0.636 0.636 ↑ 1.0 1 159

Index Scan using pk_eeo_ethnic_codes on eeo_ethnic_codes t11_0 (cost=0.14..0.16 rows=1 width=28) (actual time=0.004..0.004 rows=1 loops=159)

  • Index Cond: ((t1_0.peo_id = peo_id) AND (t1_0.ethnic_code = eeo_ethnic_code))
41. 0.000 0.000 ↓ 0.0 0 159

Index Scan using pk_hrp_users on hrp_users t12_0 (cost=0.41..0.51 rows=1 width=30) (actual time=0.000..0.000 rows=0 loops=159)

  • Index Cond: ((t0_0.peo_id = peo_id) AND (t0_0.reports_to = user_id))
42. 0.159 0.159 ↓ 0.0 0 159

Index Scan using pk_reason_codes on reason_codes t13_0 (cost=0.15..0.17 rows=1 width=31) (actual time=0.001..0.001 rows=0 loops=159)

  • Index Cond: ((t0_0.peo_id = peo_id) AND (t0_0.term_reason_code = reason_code))
43. 0.159 0.159 ↓ 0.0 0 159

Index Scan using pk_shift_codes on shift_codes t14_0 (cost=0.28..0.30 rows=1 width=22) (actual time=0.001..0.001 rows=0 loops=159)

  • Index Cond: ((t2_0.peo_id = peo_id) AND (t2_0.client_id = client_id) AND (t2_0.work_shift = shift_code))
44. 1.749 1.749 ↑ 1.0 1 159

Index Scan using pk_employee_hlh on employee_hlh t21_0 (cost=0.42..0.48 rows=1 width=70) (actual time=0.011..0.011 rows=1 loops=159)

  • Index Cond: ((t0_0.peo_id = peo_id) AND (t0_0.client_id = client_id) AND (t0_0.employee_id = employee_id))
45. 0.780 5.532 ↑ 1.0 1,868 1

Hash (cost=220.88..220.88 rows=1,888 width=47) (actual time=5.532..5.532 rows=1,868 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 164kB
46. 4.752 4.752 ↑ 1.0 1,868 1

Seq Scan on pay_groups t15_0 (cost=0.00..220.88 rows=1,888 width=47) (actual time=0.004..4.752 rows=1,868 loops=1)

47. 1.080 1.913 ↑ 1.0 2,000 1

Hash (cost=123.14..123.14 rows=2,014 width=390) (actual time=1.913..1.913 rows=2,000 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 800kB
48. 0.833 0.833 ↑ 1.0 2,000 1

Seq Scan on job_code_master t16_0 (cost=0.00..123.14 rows=2,014 width=390) (actual time=0.006..0.833 rows=2,000 loops=1)

49. 1.113 1.113 ↑ 1.0 1 159

Index Scan using pk_client_master on client_master t17_0 (cost=0.28..0.30 rows=1 width=403) (actual time=0.007..0.007 rows=1 loops=159)

  • Index Cond: ((t0_0.peo_id = peo_id) AND (t0_0.client_id = client_id))
50. 0.056 0.130 ↓ 1.1 96 1

Hash (cost=10.87..10.87 rows=87 width=121) (actual time=0.130..0.130 rows=96 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 23kB
51. 0.074 0.074 ↓ 1.1 96 1

Seq Scan on employer_master t18_0 (cost=0.00..10.87 rows=87 width=121) (actual time=0.005..0.074 rows=96 loops=1)

52. 0.004 0.010 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=81) (actual time=0.010..0.010 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
53. 0.006 0.006 ↑ 1.0 4 1

Seq Scan on client_federal_entities t19_0 (cost=0.00..1.04 rows=4 width=81) (actual time=0.004..0.006 rows=4 loops=1)

54. 1.272 1.272 ↑ 1.0 1 159

Index Scan using pk_location_codes on location_codes t20_0 (cost=0.29..0.31 rows=1 width=344) (actual time=0.008..0.008 rows=1 loops=159)

  • Index Cond: ((t0_0.peo_id = peo_id) AND (t0_0.client_id = client_id) AND (t0_0.location_code = location_code))
55. 43.181 90.760 ↑ 1.0 47,888 1

Hash (cost=5,353.51..5,353.51 rows=47,923 width=25) (actual time=90.760..90.760 rows=47,888 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 3,235kB
56. 47.579 47.579 ↓ 1.0 47,924 1

Seq Scan on hrp_users t22_0 (cost=0.00..5,353.51 rows=47,923 width=25) (actual time=0.005..47.579 rows=47,924 loops=1)

  • Filter: (user_type = 'E'::text)
  • Rows Removed by Filter: 2,688
57.          

SubPlan (for Hash Left Join)

58. 1.113 6.042 ↑ 20.0 5 159

Result (cost=0.00..2.27 rows=100 width=32) (actual time=0.032..0.038 rows=5 loops=159)

59. 4.929 4.929 ↑ 20.0 5 159

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.030..0.031 rows=5 loops=159)

60. 0.000 0.000 ↑ 1.0 1 159

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

61. 0.636 1.113 ↑ 20.0 5 159

Result (cost=0.00..2.27 rows=100 width=32) (actual time=0.003..0.007 rows=5 loops=159)

62. 0.477 0.477 ↑ 20.0 5 159

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.002..0.003 rows=5 loops=159)

63. 0.000 0.000 ↑ 1.0 1 159

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

64. 0.000 0.159 ↓ 0.0 0 159

Result (cost=0.00..2.27 rows=100 width=32) (actual time=0.001..0.001 rows=0 loops=159)

65. 0.159 0.159 ↓ 0.0 0 159

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.001..0.001 rows=0 loops=159)

66. 0.000 0.000 ↑ 1.0 1 159

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