explain.depesz.com

PostgreSQL's explain analyze made readable

Result: H3HI

Settings
# exclusive inclusive rows x rows loops node
1. 2,686.748 27,246.786 ↑ 32.1 280,282 1

Hash Left Join (cost=8,461.97..62,091,201.05 rows=9,002,887 width=3,846) (actual time=82.380..27,246.786 rows=280,282 loops=1)

  • Hash Cond: ((t1_0.employee_id = t22_0.employee_id) AND (t1_0.peo_id = t22_0.peo_id))
2. 638.462 13,041.432 ↑ 32.1 280,270 1

Hash Left Join (cost=2,389.61..608,479.96 rows=9,002,887 width=4,019) (actual time=55.048..13,041.432 rows=280,270 loops=1)

  • Hash Cond: ((t0_0.location_code = t20_0.location_code) AND (t0_0.peo_id = t20_0.peo_id) AND (t0_0.client_id = t20_0.client_id))
3. 439.800 12,393.228 ↑ 8.3 280,270 1

Hash Left Join (cost=1,906.58..589,719.06 rows=2,319,406 width=3,688) (actual time=45.228..12,393.228 rows=280,270 loops=1)

  • Hash Cond: ((t17_0.entity_id = t19_0.entity_id) AND (t17_0.peo_id = t19_0.peo_id))
4. 582.065 11,953.419 ↑ 8.3 280,270 1

Hash Left Join (cost=1,905.48..577,541.08 rows=2,319,406 width=3,628) (actual time=45.201..11,953.419 rows=280,270 loops=1)

  • Hash Cond: ((t17_0.employer_id = t18_0.employer_id) AND (t17_0.peo_id = t18_0.peo_id))
  • 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) OR (t17_0.modified > '2020-05-01 17:48:04.885'::timestamp without time zone) OR (t18_0.modified > '2020-05-01 17:48:04.885'::timestamp without time zone))
  • Rows Removed by Filter: 2,045
5. 652.684 11,371.213 ↑ 8.2 282,315 1

Hash Left Join (cost=1,893.31..565,247.41 rows=2,319,406 width=3,552) (actual time=45.027..11,371.213 rows=282,315 loops=1)

  • Hash Cond: ((t0_0.peo_id = t17_0.peo_id) AND (t0_0.client_id = t17_0.client_id))
6. 504.845 10,709.429 ↑ 8.2 282,315 1

Hash Left Join (cost=1,507.93..552,675.37 rows=2,319,406 width=3,146) (actual time=35.891..10,709.429 rows=282,315 loops=1)

  • Hash Cond: ((t8_0.peo_id = t16_0.peo_id) AND (t8_0.job_class = t16_0.pos_class))
7. 510.980 10,202.388 ↑ 8.2 282,315 1

Hash Left Join (cost=1,354.58..540,332.60 rows=2,319,406 width=2,778) (actual time=33.671..10,202.388 rows=282,315 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. 656.818 9,689.814 ↑ 8.2 282,315 1

Merge Left Join (cost=1,100.66..521,809.77 rows=2,319,406 width=2,764) (actual time=32.053..9,689.814 rows=282,315 loops=1)

  • Merge Cond: ((t0_0.peo_id = t21_0.peo_id) AND (t0_0.client_id = t21_0.client_id) AND (t0_0.employee_id = t21_0.employee_id))
9. 686.550 8,804.873 ↑ 8.2 282,315 1

Nested Loop Left Join (cost=1,100.24..492,205.29 rows=2,319,406 width=2,714) (actual time=32.006..8,804.873 rows=282,315 loops=1)

10. 825.381 7,271.378 ↑ 1.0 282,315 1

Merge Left Join (cost=1,099.96..407,425.66 rows=282,315 width=2,707) (actual time=31.983..7,271.378 rows=282,315 loops=1)

  • Merge Cond: ((t0_0.peo_id = t10_0.peo_id) AND (t0_0.client_id = t10_0.client_id))
  • Join Filter: (t0_0.benefit_group = t10_0.group_id)
  • Rows Removed by Join Filter: 517
11. 0.000 6,441.517 ↑ 1.0 282,315 1

Gather Merge (cost=1,099.69..402,152.09 rows=282,315 width=2,699) (actual time=31.923..6,441.517 rows=282,315 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
12. 251.649 7,621.592 ↑ 1.2 94,105 3 / 3

Nested Loop Left Join (cost=99.66..368,565.91 rows=117,631 width=2,699) (actual time=2.560..7,621.592 rows=94,105 loops=3)

13. 283.240 6,993.523 ↑ 1.2 94,105 3 / 3

Nested Loop Left Join (cost=99.52..348,859.59 rows=117,631 width=2,681) (actual time=2.528..6,993.523 rows=94,105 loops=3)

14. 291.949 6,710.283 ↑ 1.2 94,105 3 / 3

Nested Loop Left Join (cost=99.10..289,283.45 rows=117,631 width=2,669) (actual time=2.499..6,710.283 rows=94,105 loops=3)

15. 224.547 6,136.019 ↑ 1.2 94,105 3 / 3

Merge Left Join (cost=98.96..270,818.76 rows=117,631 width=2,650) (actual time=2.466..6,136.019 rows=94,105 loops=3)

  • Merge Cond: ((t0_0.peo_id = t9_0.peo_id) AND (t0_0.client_id = t9_0.client_id))
  • Join Filter: (t0_0.union_code = t9_0.union_code)
  • Rows Removed by Join Filter: 213,691
16. 367.861 5,888.541 ↑ 1.2 94,105 3 / 3

Nested Loop Left Join (cost=79.90..259,665.74 rows=117,631 width=2,632) (actual time=2.045..5,888.541 rows=94,105 loops=3)

17. 174.558 4,767.840 ↑ 1.2 94,105 3 / 3

Merge Left Join (cost=79.62..222,520.13 rows=117,631 width=2,365) (actual time=2.008..4,767.840 rows=94,105 loops=3)

  • Merge Cond: ((t0_0.peo_id = t7_0.peo_id) AND (t0_0.client_id = t7_0.client_id))
  • Join Filter: (t0_0.work_group_code = t7_0.work_group)
  • Rows Removed by Join Filter: 112,647
18. 288.825 4,579.538 ↑ 1.2 94,105 3 / 3

Merge Left Join (cost=76.10..218,532.91 rows=117,631 width=2,352) (actual time=1.911..4,579.538 rows=94,105 loops=3)

  • Merge Cond: (t0_0.peo_id = t6_0.peo_id)
  • Join Filter: (t0_0.type_code = t6_0.type_code)
  • Rows Removed by Join Filter: 560,904
19. 226.861 4,242.072 ↑ 1.2 94,105 3 / 3

Merge Left Join (cost=60.24..216,164.42 rows=117,631 width=2,288) (actual time=1.852..4,242.072 rows=94,105 loops=3)

  • Merge Cond: (t0_0.peo_id = t5_0.peo_id)
  • Join Filter: (t0_0.status_code = t5_0.status_code)
  • Rows Removed by Join Filter: 254,933
20. 192.678 3,988.534 ↑ 1.2 94,105 3 / 3

Merge Left Join (cost=43.32..213,794.88 rows=117,631 width=2,192) (actual time=1.800..3,988.534 rows=94,105 loops=3)

  • Merge Cond: ((t0_0.peo_id = t4_0.peo_id) AND (t0_0.client_id = t4_0.client_id))
  • Join Filter: (t0_0.division_code = t4_0.division_code)
  • Rows Removed by Join Filter: 114,987
21. 326.669 3,783.863 ↑ 1.2 94,105 3 / 3

Nested Loop Left Join (cost=1.55..208,522.06 rows=117,631 width=2,177) (actual time=0.803..3,783.863 rows=94,105 loops=3)

22. 524.361 2,892.564 ↑ 1.2 94,105 3 / 3

Merge Left Join (cost=1.27..171,935.55 rows=117,631 width=2,165) (actual time=0.772..2,892.564 rows=94,105 loops=3)

  • Merge Cond: ((t0_0.peo_id = t2_0.peo_id) AND (t0_0.client_id = t2_0.client_id) AND (t0_0.employee_id = t2_0.employee_id))
23. 385.857 1,692.134 ↑ 1.2 94,105 3 / 3

Nested Loop Left Join (cost=0.84..109,973.32 rows=117,631 width=1,232) (actual time=0.088..1,692.134 rows=94,105 loops=3)

24. 271.122 271.122 ↑ 1.2 94,105 3 / 3

Parallel Index Scan using pk_employee_com on employee_com t0_0 (cost=0.42..27,165.00 rows=117,631 width=628) (actual time=0.051..271.122 rows=94,105 loops=3)

25. 1,035.155 1,035.155 ↑ 1.0 1 282,315 / 3

Index Scan using pk_employee_per on employee_per t1_0 (cost=0.42..0.70 rows=1 width=604) (actual time=0.011..0.011 rows=1 loops=282,315)

  • Index Cond: ((t0_0.peo_id = peo_id) AND (t0_0.employee_id = employee_id))
26. 676.069 676.069 ↑ 1.0 282,126 3 / 3

Index Scan using pk_employee_pay on employee_pay t2_0 (cost=0.42..58,845.38 rows=282,284 width=940) (actual time=0.028..676.069 rows=282,126 loops=3)

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

Index Scan using pk_dept_codes on dept_codes t3_0 (cost=0.29..0.31 rows=1 width=29) (actual time=0.006..0.006 rows=1 loops=282,315)

  • Index Cond: ((t0_0.peo_id = peo_id) AND (t0_0.client_id = client_id) AND (t0_0.dept_code = dept_code))
28. 11.869 11.993 ↓ 186.9 115,328 3 / 3

Sort (cost=41.77..43.31 rows=617 width=34) (actual time=0.982..11.993 rows=115,328 loops=3)

  • Sort Key: t4_0.peo_id, t4_0.client_id
  • Sort Method: quicksort Memory: 68kB
  • Worker 0: Sort Method: quicksort Memory: 68kB
  • Worker 1: Sort Method: quicksort Memory: 68kB
29. 0.124 0.124 ↑ 1.2 533 3 / 3

Seq Scan on division_codes t4_0 (cost=0.00..13.17 rows=617 width=34) (actual time=0.014..0.124 rows=533 loops=3)

30. 26.660 26.677 ↓ 1,699.5 254,925 3 / 3

Sort (cost=16.92..17.30 rows=150 width=160) (actual time=0.037..26.677 rows=254,925 loops=3)

  • Sort Key: t5_0.peo_id
  • Sort Method: quicksort Memory: 26kB
  • Worker 0: Sort Method: quicksort Memory: 26kB
  • Worker 1: Sort Method: quicksort Memory: 26kB
31. 0.017 0.017 ↑ 8.8 17 3 / 3

Seq Scan on employee_status_codes t5_0 (cost=0.00..11.50 rows=150 width=160) (actual time=0.014..0.017 rows=17 loops=3)

32. 48.620 48.641 ↓ 4,314.7 560,912 3 / 3

Sort (cost=15.86..16.19 rows=130 width=128) (actual time=0.043..48.641 rows=560,912 loops=3)

  • Sort Key: t6_0.peo_id
  • Sort Method: quicksort Memory: 28kB
  • Worker 0: Sort Method: quicksort Memory: 28kB
  • Worker 1: Sort Method: quicksort Memory: 28kB
33. 0.021 0.021 ↑ 3.4 38 3 / 3

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

34. 13.727 13.744 ↓ 1,788.7 112,686 3 / 3

Sort (cost=3.51..3.67 rows=63 width=32) (actual time=0.086..13.744 rows=112,686 loops=3)

  • Sort Key: t7_0.peo_id, t7_0.client_id
  • Sort Method: quicksort Memory: 30kB
  • Worker 0: Sort Method: quicksort Memory: 30kB
  • Worker 1: Sort Method: quicksort Memory: 30kB
35. 0.017 0.017 ↑ 1.0 63 3 / 3

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

36. 752.840 752.840 ↑ 1.0 1 282,315 / 3

Index Scan using pk_job_codes on job_codes t8_0 (cost=0.29..0.32 rows=1 width=286) (actual time=0.008..0.008 rows=1 loops=282,315)

  • Index Cond: ((t0_0.peo_id = peo_id) AND (t0_0.client_id = client_id) AND (t0_0.job_code = job_code))
37. 22.865 22.931 ↓ 880.1 213,863 3 / 3

Sort (cost=19.06..19.67 rows=243 width=42) (actual time=0.408..22.931 rows=213,863 loops=3)

  • Sort Key: t9_0.peo_id, t9_0.client_id
  • Sort Method: quicksort Memory: 49kB
  • Worker 0: Sort Method: quicksort Memory: 49kB
  • Worker 1: Sort Method: quicksort Memory: 49kB
38. 0.066 0.066 ↑ 1.0 243 3 / 3

Seq Scan on union_codes t9_0 (cost=0.00..9.43 rows=243 width=42) (actual time=0.009..0.066 rows=243 loops=3)

39. 282.315 282.315 ↑ 1.0 1 282,315 / 3

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.003..0.003 rows=1 loops=282,315)

  • Index Cond: ((t1_0.peo_id = peo_id) AND (t1_0.ethnic_code = eeo_ethnic_code))
40. 0.000 0.000 ↓ 0.0 0 282,315 / 3

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=282,315)

  • Index Cond: ((t0_0.peo_id = peo_id) AND (t0_0.reports_to = user_id))
41. 376.420 376.420 ↑ 1.0 1 282,315 / 3

Index Scan using pk_reason_codes on reason_codes t13_0 (cost=0.15..0.17 rows=1 width=31) (actual time=0.004..0.004 rows=1 loops=282,315)

  • Index Cond: ((t0_0.peo_id = peo_id) AND (t0_0.term_reason_code = reason_code))
42. 0.850 4.480 ↓ 1.3 2,398 1

Materialize (cost=0.28..227.89 rows=1,894 width=22) (actual time=0.042..4.480 rows=2,398 loops=1)

43. 3.630 3.630 ↑ 1.0 1,889 1

Index Scan using pk_benefit_groups on benefit_groups t10_0 (cost=0.28..223.15 rows=1,894 width=22) (actual time=0.038..3.630 rows=1,889 loops=1)

44. 846.945 846.945 ↓ 0.0 0 282,315

Index Scan using pk_shift_codes on shift_codes t14_0 (cost=0.28..0.30 rows=1 width=22) (actual time=0.003..0.003 rows=0 loops=282,315)

  • Index Cond: ((t2_0.peo_id = peo_id) AND (t2_0.client_id = client_id) AND (t2_0.work_shift = shift_code))
45. 228.123 228.123 ↑ 1.0 154,599 1

Index Scan using pk_employee_hlh on employee_hlh t21_0 (cost=0.42..10,009.95 rows=154,727 width=70) (actual time=0.032..228.123 rows=154,599 loops=1)

46. 0.791 1.594 ↑ 1.0 1,868 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 164kB
47. 0.803 0.803 ↑ 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.005..0.803 rows=1,868 loops=1)

48. 1.350 2.196 ↑ 1.0 2,000 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 800kB
49. 0.846 0.846 ↑ 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.846 rows=2,000 loops=1)

50. 2.136 9.100 ↑ 1.0 1,807 1

Hash (cost=358.15..358.15 rows=1,815 width=411) (actual time=9.100..9.100 rows=1,807 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 553kB
51. 6.964 6.964 ↑ 1.0 1,807 1

Seq Scan on client_master t17_0 (cost=0.00..358.15 rows=1,815 width=411) (actual time=0.012..6.964 rows=1,807 loops=1)

52. 0.059 0.141 ↓ 1.1 96 1

Hash (cost=10.87..10.87 rows=87 width=129) (actual time=0.141..0.141 rows=96 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 24kB
53. 0.082 0.082 ↓ 1.1 96 1

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

54. 0.003 0.009 ↑ 1.0 4 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
55. 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.005..0.006 rows=4 loops=1)

56. 5.095 9.742 ↑ 1.0 9,562 1

Hash (cost=315.01..315.01 rows=9,601 width=344) (actual time=9.742..9.742 rows=9,562 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 1,564kB
57. 4.647 4.647 ↑ 1.0 9,562 1

Seq Scan on location_codes t20_0 (cost=0.00..315.01 rows=9,601 width=344) (actual time=0.006..4.647 rows=9,562 loops=1)

58. 11.569 27.044 ↑ 1.0 47,888 1

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

  • Buckets: 65,536 Batches: 1 Memory Usage: 3,235kB
59. 15.475 15.475 ↓ 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.004..15.475 rows=47,924 loops=1)

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

SubPlan (for Hash Left Join)

61. 2,802.820 5,605.640 ↑ 4.3 23 280,282

Result (cost=0.00..2.27 rows=100 width=32) (actual time=0.007..0.020 rows=23 loops=280,282)

62. 2,802.820 2,802.820 ↑ 4.3 23 280,282

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.006..0.010 rows=23 loops=280,282)

63. 0.000 0.000 ↑ 1.0 1 280,282

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

64. 3,083.102 5,605.640 ↑ 4.3 23 280,282

Result (cost=0.00..2.27 rows=100 width=32) (actual time=0.007..0.020 rows=23 loops=280,282)

65. 2,522.538 2,522.538 ↑ 4.3 23 280,282

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.006..0.009 rows=23 loops=280,282)

66. 0.000 0.000 ↑ 1.0 1 280,282

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

67. 280.282 280.282 ↓ 0.0 0 280,282

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

68. 0.000 0.000 ↓ 0.0 0 280,282

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.000..0.000 rows=0 loops=280,282)

69. 0.000 0.000 ↑ 1.0 1 280,282

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

Planning time : 48.179 ms
Execution time : 27,283.607 ms