explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Wdhz

Settings
# exclusive inclusive rows x rows loops node
1. 2,780.904 29,287.317 ↑ 33.8 280,282 1

Hash Left Join (cost=9,002.12..65,314,754.43 rows=9,463,181 width=4,049) (actual time=111.385..29,287.317 rows=280,282 loops=1)

  • Hash Cond: ((t0_0.proj_workord = t23_0.work_code) AND (t0_0.peo_id = t23_0.peo_id) AND (t0_0.client_id = t23_0.client_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 (t10_0.modified > '2020-05-01 17:48:04.885'::timestamp without time zone) OR (t12_0.modified > '2020-05-01 17:48:04.885'::timestamp without time zone) OR (t5_0.modified > '2020-05-01 17:48:04.885'::timestamp without time zone) OR (t6_0.modified > '2020-05-01 17:48:04.885'::timestamp without time zone) OR (t8_0.modified > '2020-05-01 17:48:04.885'::timestamp without time zone) OR (t20_0.modified > '2020-05-01 17:48:04.885'::timestamp without time zone) OR (t13_0.modified > '2020-05-01 17:48:04.885'::timestamp without time zone) OR (t23_0.modified > '2020-05-01 17:48:04.885'::timestamp without time zone) OR (t7_0.modified > '2020-05-01 17:48:04.885'::timestamp without time zone) OR (t16_0.modified > '2020-05-01 17:48:04.885'::timestamp without time zone) OR (t21_0.modified > '2020-05-01 17:48:04.885'::timestamp without time zone) OR (t11_0.modified > '2020-05-01 17:48:04.885'::timestamp without time zone) OR (t14_0.modified > '2020-05-01 17:48:04.885'::timestamp without time zone) OR (t15_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) OR (t4_0.modified > '2020-05-01 17:48:04.885'::timestamp without time zone) OR (t3_0.modified > '2020-05-01 17:48:04.885'::timestamp without time zone) OR (t19_0.modified > '2020-05-01 17:48:04.885'::timestamp without time zone) OR (t9_0.modified > '2020-05-01 17:48:04.885'::timestamp without time zone))
  • Rows Removed by Filter: 2,045
2. 593.093 14,999.844 ↑ 33.5 282,327 1

Hash Left Join (cost=8,085.30..700,420.29 rows=9,463,181 width=4,394) (actual time=96.125..14,999.844 rows=282,327 loops=1)

  • Hash Cond: ((t1_0.employee_id = t22_0.employee_id) AND (t1_0.peo_id = t22_0.peo_id))
3. 688.494 14,373.279 ↑ 33.5 282,315 1

Hash Left Join (cost=2,012.94..615,338.05 rows=9,463,181 width=4,397) (actual time=62.349..14,373.279 rows=282,315 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))
4. 613.035 13,670.943 ↑ 8.5 282,315 1

Hash Left Join (cost=1,529.91..596,035.84 rows=2,388,063 width=4,058) (actual time=48.414..13,670.943 rows=282,315 loops=1)

  • Hash Cond: ((t8_0.peo_id = t16_0.peo_id) AND (t8_0.job_class = t16_0.pos_class))
5. 587.442 13,056.360 ↑ 8.5 282,315 1

Hash Left Join (cost=1,376.56..583,332.25 rows=2,388,063 width=3,682) (actual time=46.830..13,056.360 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))
6. 745.917 12,467.510 ↑ 8.5 282,315 1

Merge Left Join (cost=1,122.64..564,268.53 rows=2,388,063 width=3,660) (actual time=45.387..12,467.510 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))
7. 747.260 11,449.608 ↑ 8.5 282,315 1

Merge Left Join (cost=1,122.22..534,155.69 rows=2,388,063 width=3,602) (actual time=43.675..11,449.608 rows=282,315 loops=1)

  • Merge Cond: ((t0_0.peo_id = t17_0.peo_id) AND (t0_0.client_id = t17_0.client_id))
8. 752.609 10,621.378 ↑ 8.5 282,315 1

Nested Loop Left Join (cost=1,101.60..495,040.01 rows=2,388,063 width=2,887) (actual time=41.908..10,621.378 rows=282,315 loops=1)

9. 910.899 9,021.824 ↑ 1.0 282,315 1

Merge Left Join (cost=1,101.32..409,980.27 rows=283,282 width=2,872) (actual time=41.889..9,021.824 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
10. 0.000 8,090.357 ↑ 1.0 282,315 1

Gather Merge (cost=1,101.05..407,191.37 rows=283,282 width=2,856) (actual time=39.906..8,090.357 rows=282,315 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
11. 277.939 9,129.170 ↑ 1.3 94,105 3 / 3

Nested Loop Left Join (cost=101.03..373,493.58 rows=118,034 width=2,856) (actual time=18.576..9,129.170 rows=94,105 loops=3)

12. 232.237 8,474.811 ↑ 1.3 94,105 3 / 3

Nested Loop Left Join (cost=100.88..354,014.99 rows=118,034 width=2,830) (actual time=17.613..8,474.811 rows=94,105 loops=3)

13. 291.735 8,148.469 ↑ 1.3 94,105 3 / 3

Nested Loop Left Join (cost=100.47..294,263.55 rows=118,034 width=2,810) (actual time=17.585..8,148.469 rows=94,105 loops=3)

14. 229.523 7,574.419 ↑ 1.3 94,105 3 / 3

Merge Left Join (cost=100.32..274,472.32 rows=118,034 width=2,770) (actual time=16.843..7,574.419 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
15. 280.059 7,323.424 ↑ 1.3 94,105 3 / 3

Nested Loop Left Join (cost=79.49..261,339.48 rows=118,034 width=2,744) (actual time=16.498..7,323.424 rows=94,105 loops=3)

16. 179.032 6,102.315 ↑ 1.3 94,105 3 / 3

Merge Left Join (cost=79.20..224,068.94 rows=118,034 width=2,469) (actual time=15.953..6,102.315 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
17. 298.017 5,912.473 ↑ 1.3 94,105 3 / 3

Merge Left Join (cost=76.10..219,582.14 rows=118,034 width=2,447) (actual time=15.886..5,912.473 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
18. 234.497 5,569.279 ↑ 1.3 94,105 3 / 3

Merge Left Join (cost=60.24..217,205.59 rows=118,034 width=2,375) (actual time=15.845..5,569.279 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
19. 180.407 5,306.521 ↑ 1.3 94,105 3 / 3

Merge Left Join (cost=43.32..214,827.99 rows=118,034 width=2,271) (actual time=15.806..5,306.521 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
20. 277.896 5,114.908 ↑ 1.3 94,105 3 / 3

Nested Loop Left Join (cost=1.55..209,637.37 rows=118,034 width=2,248) (actual time=15.080..5,114.908 rows=94,105 loops=3)

21. 505.118 4,178.277 ↑ 1.3 94,105 3 / 3

Merge Left Join (cost=1.27..172,926.95 rows=118,034 width=2,228) (actual time=14.820..4,178.277 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))
22. 350.162 2,924.375 ↑ 1.3 94,105 3 / 3

Nested Loop Left Join (cost=0.84..110,327.08 rows=118,034 width=1,354) (actual time=11.244..2,924.375 rows=94,105 loops=3)

23. 315.693 315.693 ↑ 1.3 94,105 3 / 3

Parallel Index Scan using pk_employee_com on employee_com t0_0 (cost=0.42..27,340.41 rows=118,034 width=741) (actual time=6.473..315.693 rows=94,105 loops=3)

24. 2,258.520 2,258.520 ↑ 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=613) (actual time=0.024..0.024 rows=1 loops=282,315)

  • Index Cond: ((t0_0.peo_id = peo_id) AND (t0_0.employee_id = employee_id))
25. 748.784 748.784 ↑ 1.1 282,126 3 / 3

Index Scan using pk_employee_pay on employee_pay t2_0 (cost=0.42..59,295.60 rows=306,983 width=881) (actual time=0.531..748.784 rows=282,126 loops=3)

26. 658.735 658.735 ↑ 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=37) (actual time=0.007..0.007 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))
27. 11.086 11.206 ↓ 186.9 115,327 3 / 3

Sort (cost=41.77..43.31 rows=617 width=42) (actual time=0.714..11.206 rows=115,327 loops=3)

  • Sort Key: t4_0.peo_id, t4_0.client_id
  • Sort Method: quicksort Memory: 76kB
  • Worker 0: Sort Method: quicksort Memory: 76kB
  • Worker 1: Sort Method: quicksort Memory: 76kB
28. 0.120 0.120 ↑ 1.2 533 3 / 3

Seq Scan on division_codes t4_0 (cost=0.00..13.17 rows=617 width=42) (actual time=0.011..0.120 rows=533 loops=3)

29. 28.247 28.261 ↓ 1,699.5 254,925 3 / 3

Sort (cost=16.92..17.30 rows=150 width=168) (actual time=0.029..28.261 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
30. 0.014 0.014 ↑ 8.8 17 3 / 3

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

31. 45.162 45.177 ↓ 4,314.7 560,912 3 / 3

Sort (cost=15.86..16.19 rows=130 width=136) (actual time=0.030..45.177 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
32. 0.015 0.015 ↑ 3.4 38 3 / 3

Seq Scan on employee_type_codes t6_0 (cost=0.00..11.30 rows=130 width=136) (actual time=0.008..0.015 rows=38 loops=3)

33. 10.798 10.810 ↓ 2,086.8 112,686 3 / 3

Sort (cost=3.09..3.23 rows=54 width=42) (actual time=0.058..10.810 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
34. 0.012 0.012 ↓ 1.2 63 3 / 3

Seq Scan on work_groups t7_0 (cost=0.00..1.54 rows=54 width=42) (actual time=0.004..0.012 rows=63 loops=3)

35. 941.050 941.050 ↑ 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=294) (actual time=0.010..0.010 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))
36. 21.395 21.472 ↓ 780.5 213,861 3 / 3

Sort (cost=20.83..21.52 rows=274 width=50) (actual time=0.333..21.472 rows=213,861 loops=3)

  • Sort Key: t9_0.peo_id, t9_0.client_id
  • Sort Method: quicksort Memory: 53kB
  • Worker 0: Sort Method: quicksort Memory: 53kB
  • Worker 1: Sort Method: quicksort Memory: 53kB
37. 0.077 0.077 ↑ 1.1 243 3 / 3

Seq Scan on union_codes t9_0 (cost=0.00..9.74 rows=274 width=50) (actual time=0.015..0.077 rows=243 loops=3)

38. 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.15..0.17 rows=1 width=104) (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))
39. 94.105 94.105 ↓ 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=38) (actual time=0.001..0.001 rows=0 loops=282,315)

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

Index Scan using pk_reason_codes on reason_codes t13_0 (cost=0.14..0.17 rows=1 width=39) (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))
41. 0.823 20.568 ↓ 4.7 2,398 1

Materialize (cost=0.27..203.66 rows=510 width=30) (actual time=1.968..20.568 rows=2,398 loops=1)

42. 19.745 19.745 ↓ 3.7 1,889 1

Index Scan using pk_benefit_groups on benefit_groups t10_0 (cost=0.27..202.39 rows=510 width=30) (actual time=1.961..19.745 rows=1,889 loops=1)

43. 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=30) (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))
44. 36.843 80.970 ↓ 154.5 280,351 1

Materialize (cost=20.62..1,270.44 rows=1,815 width=727) (actual time=1.748..80.970 rows=280,351 loops=1)

45. 3.105 44.127 ↑ 1.0 1,807 1

Merge Left Join (cost=20.62..1,265.90 rows=1,815 width=727) (actual time=1.739..44.127 rows=1,807 loops=1)

  • Merge Cond: (t17_0.peo_id = t19_0.peo_id)
  • Join Filter: (t17_0.entity_id = t19_0.entity_id)
  • Rows Removed by Join Filter: 6
46. 13.728 40.998 ↑ 1.0 1,807 1

Nested Loop Left Join (cost=0.42..1,207.81 rows=1,815 width=527) (actual time=1.709..40.998 rows=1,807 loops=1)

47. 18.235 18.235 ↑ 1.0 1,807 1

Index Scan using pk_client_master on client_master t17_0 (cost=0.28..865.00 rows=1,815 width=411) (actual time=0.784..18.235 rows=1,807 loops=1)

48. 9.035 9.035 ↑ 1.0 1 1,807

Index Scan using pk_employer_master on employer_master t18_0 (cost=0.14..0.19 rows=1 width=129) (actual time=0.005..0.005 rows=1 loops=1,807)

  • Index Cond: ((t17_0.peo_id = peo_id) AND (t17_0.employer_id = employer_id))
49. 0.013 0.024 ↑ 30.0 7 1

Sort (cost=20.20..20.72 rows=210 width=264) (actual time=0.023..0.024 rows=7 loops=1)

  • Sort Key: t19_0.peo_id
  • Sort Method: quicksort Memory: 25kB
50. 0.011 0.011 ↑ 52.5 4 1

Seq Scan on client_federal_entities t19_0 (cost=0.00..12.10 rows=210 width=264) (actual time=0.009..0.011 rows=4 loops=1)

51. 271.985 271.985 ↑ 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=78) (actual time=1.695..271.985 rows=154,599 loops=1)

52. 0.547 1.408 ↑ 1.0 1,868 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 185kB
53. 0.861 0.861 ↑ 1.0 1,868 1

Seq Scan on pay_groups t15_0 (cost=0.00..220.88 rows=1,888 width=55) (actual time=0.005..0.861 rows=1,868 loops=1)

54. 1.037 1.548 ↑ 1.0 2,000 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 823kB
55. 0.511 0.511 ↑ 1.0 2,000 1

Seq Scan on job_code_master t16_0 (cost=0.00..123.14 rows=2,014 width=398) (actual time=0.005..0.511 rows=2,000 loops=1)

56. 7.164 13.842 ↑ 1.0 9,562 1

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

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

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

58. 14.674 33.472 ↑ 1.0 47,888 1

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

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

  • Filter: (user_type = 'E'::text)
  • Rows Removed by Filter: 2,688
60. 7.140 15.007 ↑ 1.0 22,183 1

Hash (cost=527.48..527.48 rows=22,248 width=32) (actual time=15.007..15.007 rows=22,183 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,730kB
61. 7.867 7.867 ↑ 1.0 22,183 1

Seq Scan on proj_work_codes t23_0 (cost=0.00..527.48 rows=22,248 width=32) (actual time=0.008..7.867 rows=22,183 loops=1)

62.          

SubPlan (for Hash Left Join)

63. 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)

64. 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)

65. 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)

66. 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)

67. 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)

68. 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)

69. 0.000 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)

70. 280.282 280.282 ↓ 0.0 0 280,282

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

71. 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 : 53.521 ms
Execution time : 29,328.787 ms