explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BDQ1

Settings
# exclusive inclusive rows x rows loops node
1. 2,587.087 27,671.364 ↑ 31.7 280,282 1

Hash Left Join (cost=8,978.90..61,267,543.88 rows=8,871,693 width=3,869) (actual time=90.814..27,671.364 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. 626.247 13,580.919 ↑ 31.4 282,327 1

Hash Left Join (cost=8,063.87..691,817.65 rows=8,871,693 width=4,214) (actual time=78.819..13,580.919 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. 706.222 12,927.239 ↑ 31.4 282,315 1

Hash Left Join (cost=1,991.51..609,907.38 rows=8,871,693 width=4,218) (actual time=51.131..12,927.239 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. 579.206 12,210.946 ↑ 8.2 282,315 1

Hash Left Join (cost=1,508.48..591,146.68 rows=2,319,406 width=3,879) (actual time=40.962..12,210.946 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. 611.208 11,627.741 ↑ 8.2 282,315 1

Hash Left Join (cost=1,355.13..578,803.90 rows=2,319,406 width=3,503) (actual time=36.932..11,627.741 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. 724.260 11,014.452 ↑ 8.2 282,315 1

Merge Left Join (cost=1,101.21..560,281.07 rows=2,319,406 width=3,481) (actual time=34.814..11,014.452 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. 729.335 10,036.773 ↑ 8.2 282,315 1

Merge Left Join (cost=1,100.79..530,666.57 rows=2,319,406 width=3,423) (actual time=34.759..10,036.773 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. 705.544 9,233.167 ↑ 8.2 282,315 1

Nested Loop Left Join (cost=1,100.24..492,457.89 rows=2,319,406 width=2,833) (actual time=30.830..9,233.167 rows=282,315 loops=1)

9. 884.183 7,680.678 ↑ 1.0 282,315 1

Merge Left Join (cost=1,099.96..407,678.26 rows=282,315 width=2,818) (actual time=30.804..7,680.678 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 6,791.638 ↑ 1.0 282,315 1

Gather Merge (cost=1,099.69..402,394.70 rows=282,315 width=2,802) (actual time=30.742..6,791.638 rows=282,315 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
11. 264.611 7,651.218 ↑ 1.2 94,105 3 / 3

Nested Loop Left Join (cost=99.66..368,808.52 rows=117,631 width=2,802) (actual time=2.593..7,651.218 rows=94,105 loops=3)

12. 287.786 7,010.187 ↑ 1.2 94,105 3 / 3

Nested Loop Left Join (cost=99.52..349,102.19 rows=117,631 width=2,776) (actual time=2.558..7,010.187 rows=94,105 loops=3)

13. 295.418 6,722.401 ↑ 1.2 94,105 3 / 3

Nested Loop Left Join (cost=99.10..289,526.05 rows=117,631 width=2,756) (actual time=2.528..6,722.401 rows=94,105 loops=3)

14. 228.414 6,144.668 ↑ 1.2 94,105 3 / 3

Merge Left Join (cost=98.96..271,061.36 rows=117,631 width=2,729) (actual time=2.495..6,144.668 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. 371.327 5,898.448 ↑ 1.2 94,105 3 / 3

Nested Loop Left Join (cost=79.90..259,731.82 rows=117,631 width=2,703) (actual time=2.003..5,898.448 rows=94,105 loops=3)

16. 176.603 4,774.281 ↑ 1.2 94,105 3 / 3

Merge Left Join (cost=79.62..222,586.22 rows=117,631 width=2,428) (actual time=1.965..4,774.281 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. 297.819 4,588.940 ↑ 1.2 94,105 3 / 3

Merge Left Join (cost=76.10..218,547.17 rows=117,631 width=2,407) (actual time=1.858..4,588.940 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. 233.435 4,246.521 ↑ 1.2 94,105 3 / 3

Merge Left Join (cost=60.24..216,178.68 rows=117,631 width=2,335) (actual time=1.789..4,246.521 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. 182.130 3,984.856 ↑ 1.2 94,105 3 / 3

Merge Left Join (cost=43.32..213,809.14 rows=117,631 width=2,231) (actual time=1.735..3,984.856 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. 317.558 3,786.723 ↑ 1.2 94,105 3 / 3

Nested Loop Left Join (cost=1.55..208,473.70 rows=117,631 width=2,208) (actual time=0.612..3,786.723 rows=94,105 loops=3)

21. 513.377 2,904.535 ↑ 1.2 94,105 3 / 3

Merge Left Join (cost=1.27..171,887.20 rows=117,631 width=2,188) (actual time=0.583..2,904.535 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. 377.880 1,686.155 ↑ 1.2 94,105 3 / 3

Nested Loop Left Join (cost=0.84..109,923.19 rows=117,631 width=1,255) (actual time=0.095..1,686.155 rows=94,105 loops=3)

23. 273.120 273.120 ↑ 1.2 94,105 3 / 3

Parallel Index Scan using pk_employee_com on employee_com t0_0 (cost=0.42..27,114.87 rows=117,631 width=651) (actual time=0.052..273.120 rows=94,105 loops=3)

24. 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))
25. 705.003 705.003 ↑ 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..705.003 rows=282,126 loops=3)

26. 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=37) (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))
27. 15.805 16.003 ↓ 186.9 115,327 3 / 3

Sort (cost=41.77..43.31 rows=617 width=42) (actual time=1.106..16.003 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.198 0.198 ↑ 1.2 533 3 / 3

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

29. 28.210 28.230 ↓ 1,699.5 254,925 3 / 3

Sort (cost=16.92..17.30 rows=150 width=168) (actual time=0.038..28.230 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.020 0.020 ↑ 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.015..0.020 rows=17 loops=3)

31. 44.573 44.600 ↓ 4,314.7 560,912 3 / 3

Sort (cost=15.86..16.19 rows=130 width=136) (actual time=0.053..44.600 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.027 0.027 ↑ 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.014..0.027 rows=38 loops=3)

33. 8.717 8.738 ↓ 1,788.7 112,686 3 / 3

Sort (cost=3.51..3.67 rows=63 width=40) (actual time=0.095..8.738 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.021 0.021 ↑ 1.0 63 3 / 3

Seq Scan on work_groups t7_0 (cost=0.00..1.63 rows=63 width=40) (actual time=0.007..0.021 rows=63 loops=3)

35. 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=294) (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))
36. 17.704 17.806 ↓ 880.1 213,861 3 / 3

Sort (cost=19.06..19.67 rows=243 width=50) (actual time=0.478..17.806 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.102 0.102 ↑ 1.0 243 3 / 3

Seq Scan on union_codes t9_0 (cost=0.00..9.43 rows=243 width=50) (actual time=0.011..0.102 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.14..0.16 rows=1 width=36) (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. 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=38) (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))
40. 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=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.919 4.857 ↓ 1.3 2,398 1

Materialize (cost=0.28..227.89 rows=1,894 width=30) (actual time=0.044..4.857 rows=2,398 loops=1)

42. 3.938 3.938 ↑ 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=30) (actual time=0.039..3.938 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. 37.352 74.271 ↓ 154.5 280,351 1

Materialize (cost=0.55..1,223.06 rows=1,815 width=602) (actual time=3.908..74.271 rows=280,351 loops=1)

45. 2.504 36.919 ↑ 1.0 1,807 1

Merge Left Join (cost=0.55..1,218.52 rows=1,815 width=602) (actual time=3.896..36.919 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. 19.852 30.593 ↑ 1.0 1,807 1

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

47. 3.513 3.513 ↑ 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.038..3.513 rows=1,807 loops=1)

48. 7.228 7.228 ↑ 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.004..0.004 rows=1 loops=1,807)

  • Index Cond: ((t17_0.peo_id = peo_id) AND (t17_0.employer_id = employer_id))
49. 3.822 3.822 ↓ 1.8 7 1

Index Scan using pk_client_federal_entities on client_federal_entities t19_0 (cost=0.13..12.19 rows=4 width=89) (actual time=3.819..3.822 rows=7 loops=1)

50. 253.419 253.419 ↑ 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=0.032..253.419 rows=154,599 loops=1)

51. 0.772 2.081 ↑ 1.0 1,868 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 185kB
52. 1.309 1.309 ↑ 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.006..1.309 rows=1,868 loops=1)

53. 3.127 3.999 ↑ 1.0 2,000 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 823kB
54. 0.872 0.872 ↑ 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.006..0.872 rows=2,000 loops=1)

55. 5.377 10.071 ↑ 1.0 9,562 1

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

  • Buckets: 16,384 Batches: 1 Memory Usage: 1,669kB
56. 4.694 4.694 ↑ 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.009..4.694 rows=9,562 loops=1)

57. 11.694 27.433 ↑ 1.0 47,888 1

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

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

  • Filter: (user_type = 'E'::text)
  • Rows Removed by Filter: 2,688
59. 5.739 11.796 ↑ 1.0 22,183 1

Hash (cost=526.83..526.83 rows=22,183 width=32) (actual time=11.796..11.796 rows=22,183 loops=1)

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

Seq Scan on proj_work_codes t23_0 (cost=0.00..526.83 rows=22,183 width=32) (actual time=0.006..6.057 rows=22,183 loops=1)

61.          

SubPlan (for Hash Left Join)

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

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

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

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

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

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

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

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

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

70. 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 : 50.430 ms
Execution time : 27,711.056 ms