explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Pigf

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 1,180.631 ↑ 188.8 17 1

Append (cost=2,010.47..39,377.51 rows=3,210 width=674) (actual time=67.482..1,180.631 rows=17 loops=1)

2. 0.015 1,032.436 ↑ 186.1 17 1

Result (cost=2,010.47..36,776.98 rows=3,163 width=674) (actual time=67.482..1,032.436 rows=17 loops=1)

3. 0.010 1,032.421 ↑ 186.1 17 1

Append (cost=2,010.47..36,737.44 rows=3,163 width=670) (actual time=67.479..1,032.421 rows=17 loops=1)

4. 0.001 67.479 ↑ 88.0 1 1

Unique (cost=2,010.47..2,013.11 rows=88 width=583) (actual time=67.478..67.479 rows=1 loops=1)

5. 0.023 67.478 ↑ 88.0 1 1

Sort (cost=2,010.47..2,010.69 rows=88 width=583) (actual time=67.478..67.478 rows=1 loops=1)

  • Sort Key: ppp.personid, pn.lname, pe.effectivedate, pe.createts, pe.endts, (CASE WHEN (pe.createts > (SubPlan 17)) THEN 'Not Processed'::text ELSE 'Processed'::text END), pe.companyid, pl.payunitid, pp.payperiod, pp.payyear, pp.payprocesstype
  • Sort Method: quicksort Memory: 25kB
6. 0.012 67.455 ↑ 88.0 1 1

Nested Loop (cost=2.25..2,007.63 rows=88 width=583) (actual time=28.965..67.455 rows=1 loops=1)

  • Join Filter: (ppp.personid = pl.personid)
7. 0.277 67.277 ↑ 76.0 1 1

Nested Loop (cost=1.83..1,755.24 rows=76 width=89) (actual time=28.787..67.277 rows=1 loops=1)

  • Join Filter: ((ppp.personid = pe.personid) AND (((pp.periodstartdate >= pe.effectivedate) AND (pp.periodstartdate <= pe.enddate)) OR ((pe.effectivedate >= pp.periodstartdate) AND (pe.effectivedate <= pp.periodenddate))) AND ((pe.createts >= pp.createts) OR ((pp.createts >= pe.createts) AND (pp.createts <= pe.endts) AND (pe.endts < CURRENT_TIMESTAMP))))
  • Rows Removed by Join Filter: 11039
8. 1.294 30.307 ↓ 10.8 4,077 1

Nested Loop (cost=1.40..1,471.79 rows=376 width=68) (actual time=0.073..30.307 rows=4,077 loops=1)

9. 3.026 29.013 ↓ 10.8 4,077 1

Nested Loop (cost=0.99..1,464.65 rows=376 width=37) (actual time=0.050..29.013 rows=4,077 loops=1)

10. 1.525 1.525 ↓ 6.4 4,077 1

Index Only Scan using pk_person_pay_process on person_pay_process ppp (cost=0.56..16.78 rows=641 width=17) (actual time=0.024..1.525 rows=4,077 loops=1)

  • Index Cond: (payprocessid = 324739)
  • Heap Fetches: 4077
11. 24.462 24.462 ↑ 1.0 1 4,077

Index Scan using person_names_personid_idx on person_names pn (cost=0.42..2.25 rows=1 width=20) (actual time=0.006..0.006 rows=1 loops=4,077)

  • Index Cond: (personid = ppp.personid)
  • Filter: ((nametype = 'Legal'::bpchar) AND (CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 0
12. 0.000 0.000 ↑ 1.0 1 4,077

Materialize (cost=0.42..2.44 rows=1 width=35) (actual time=0.000..0.000 rows=1 loops=4,077)

13. 0.019 0.019 ↑ 1.0 1 1

Index Scan using pk_pay_process on pay_process pp (cost=0.42..2.44 rows=1 width=35) (actual time=0.019..0.019 rows=1 loops=1)

  • Index Cond: (payprocessid = 324739)
14. 36.693 36.693 ↑ 1.0 3 4,077

Index Scan using person_employment_personid_idx on person_employment pe (cost=0.42..0.65 rows=3 width=41) (actual time=0.006..0.009 rows=3 loops=4,077)

  • Index Cond: (personid = pn.personid)
  • Filter: ((createts <> endts) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 2
15. 0.019 0.019 ↑ 1.0 1 1

Index Scan using person_payroll_personid_idx on person_payroll pl (cost=0.42..0.47 rows=1 width=17) (actual time=0.019..0.019 rows=1 loops=1)

  • Index Cond: (personid = pe.personid)
  • Filter: ((CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
16.          

SubPlan (for Nested Loop)

17. 0.014 0.147 ↑ 1.0 1 1

Aggregate (cost=2.44..2.45 rows=1 width=8) (actual time=0.147..0.147 rows=1 loops=1)

18. 0.133 0.133 ↓ 79.0 79 1

Index Scan using payprocesspayunitperiod on pay_process pp2_15 (cost=0.42..2.44 rows=1 width=8) (actual time=0.024..0.133 rows=79 loops=1)

  • Index Cond: ((pp.payunitid = payunitid) AND (pp.payyear = payyear) AND (pp.payperiod = payperiod))
19. 0.014 64.738 ↑ 33.5 2 1

Unique (cost=1,952.37..1,954.38 rows=67 width=583) (actual time=64.723..64.738 rows=2 loops=1)

20. 0.115 64.724 ↑ 1.5 44 1

Sort (cost=1,952.37..1,952.54 rows=67 width=583) (actual time=64.723..64.724 rows=44 loops=1)

  • Sort Key: ppp_1.personid, pn_1.lname, pe_1.effectivedate, pe_1.createts, pe_1.endts, (CASE WHEN (pe_1.createts > (SubPlan 16)) THEN 'Not Processed'::text ELSE 'Processed'::text END), pe_1.companyid, pl_1.payunitid, pp_1.payperiod, pp_1.payyear, pp_1.payprocesstype
  • Sort Method: quicksort Memory: 36kB
21. 0.034 64.609 ↑ 1.5 44 1

Nested Loop (cost=2.67..1,950.34 rows=67 width=583) (actual time=12.092..64.609 rows=44 loops=1)

22. 0.045 62.595 ↑ 1.5 44 1

Nested Loop (cost=2.25..1,754.11 rows=66 width=102) (actual time=11.921..62.595 rows=44 loops=1)

  • Join Filter: (ppp_1.personid = pe_1.personid)
23. 4.280 62.198 ↑ 1.7 44 1

Nested Loop (cost=1.82..1,707.32 rows=74 width=65) (actual time=11.888..62.198 rows=44 loops=1)

  • Join Filter: ((ppp_1.personid = pdd.personid) AND (((pp_1.periodstartdate >= pdd.effectivedate) AND (pp_1.periodstartdate <= pdd.enddate)) OR ((pdd.effectivedate >= pp_1.periodstartdate) AND (pdd.effectivedate <= CASE WHEN (pp_1.checkdate > pp_1.periodenddate) THEN pp_1.checkdate ELSE pp_1.periodenddate END))) AND ((pdd.createts >= pp_1.createts) OR ((pp_1.createts >= pdd.createts) AND (pp_1.createts <= pdd.endts) AND (pdd.endts < CURRENT_TIMESTAMP))))
  • Rows Removed by Join Filter: 17257
24. 1.194 29.379 ↓ 10.8 4,077 1

Nested Loop (cost=1.40..1,471.79 rows=376 width=72) (actual time=0.060..29.379 rows=4,077 loops=1)

25. 2.295 28.185 ↓ 10.8 4,077 1

Nested Loop (cost=0.99..1,464.65 rows=376 width=37) (actual time=0.039..28.185 rows=4,077 loops=1)

26. 1.428 1.428 ↓ 6.4 4,077 1

Index Only Scan using pk_person_pay_process on person_pay_process ppp_1 (cost=0.56..16.78 rows=641 width=17) (actual time=0.018..1.428 rows=4,077 loops=1)

  • Index Cond: (payprocessid = 324739)
  • Heap Fetches: 4077
27. 24.462 24.462 ↑ 1.0 1 4,077

Index Scan using person_names_personid_idx on person_names pn_1 (cost=0.42..2.25 rows=1 width=20) (actual time=0.006..0.006 rows=1 loops=4,077)

  • Index Cond: (personid = ppp_1.personid)
  • Filter: ((nametype = 'Legal'::bpchar) AND (CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 0
28. 0.000 0.000 ↑ 1.0 1 4,077

Materialize (cost=0.42..2.44 rows=1 width=39) (actual time=0.000..0.000 rows=1 loops=4,077)

29. 0.017 0.017 ↑ 1.0 1 1

Index Scan using pk_pay_process on pay_process pp_1 (cost=0.42..2.44 rows=1 width=39) (actual time=0.017..0.017 rows=1 loops=1)

  • Index Cond: (payprocessid = 324739)
30. 28.539 28.539 ↓ 1.3 4 4,077

Index Scan using pk_person_direct_deposits on person_direct_deposits pdd (cost=0.42..0.51 rows=3 width=37) (actual time=0.005..0.007 rows=4 loops=4,077)

  • Index Cond: (personid = pn_1.personid)
  • Filter: (createts <> endts)
31. 0.352 0.352 ↑ 2.0 1 44

Index Scan using personemploymentenddate on person_employment pe_1 (cost=0.43..0.61 rows=2 width=37) (actual time=0.008..0.008 rows=1 loops=44)

  • Index Cond: ((personid = pdd.personid) AND (CURRENT_DATE <= enddate) AND (CURRENT_DATE >= effectivedate))
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 4
32. 0.264 0.264 ↑ 1.0 1 44

Index Scan using person_payroll_personid_idx on person_payroll pl_1 (cost=0.42..0.47 rows=1 width=17) (actual time=0.005..0.006 rows=1 loops=44)

  • Index Cond: (personid = pe_1.personid)
  • Filter: ((CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
33.          

SubPlan (for Nested Loop)

34. 0.352 1.716 ↑ 1.0 1 44

Aggregate (cost=2.44..2.45 rows=1 width=8) (actual time=0.039..0.039 rows=1 loops=44)

35. 1.364 1.364 ↓ 79.0 79 44

Index Scan using payprocesspayunitperiod on pay_process pp2_14 (cost=0.42..2.44 rows=1 width=8) (actual time=0.004..0.031 rows=79 loops=44)

  • Index Cond: ((pp_1.payunitid = payunitid) AND (pp_1.payyear = payyear) AND (pp_1.payperiod = payperiod))
36. 0.001 77.689 ↑ 160.0 1 1

Unique (cost=2,288.30..2,293.10 rows=160 width=583) (actual time=77.689..77.689 rows=1 loops=1)

37. 0.025 77.688 ↑ 160.0 1 1

Sort (cost=2,288.30..2,288.70 rows=160 width=583) (actual time=77.688..77.688 rows=1 loops=1)

  • Sort Key: ppp_2.personid, pn_2.lname, pc.effectivedate, pc.createts, pc.endts, (CASE WHEN (pc.createts > (SubPlan 15)) THEN 'Not Processed'::text ELSE 'Processed'::text END), pe_2.companyid, pl_2.payunitid, pp_2.payperiod, pp_2.payyear, pp_2.payprocesstype
  • Sort Method: quicksort Memory: 25kB
38. 0.009 77.663 ↑ 160.0 1 1

Nested Loop (cost=2.68..2,282.44 rows=160 width=583) (actual time=37.352..77.663 rows=1 loops=1)

  • Join Filter: (ppp_2.personid = pe_2.personid)
39. 0.005 77.537 ↑ 90.0 1 1

Nested Loop (cost=2.25..1,836.87 rows=90 width=102) (actual time=37.233..77.537 rows=1 loops=1)

  • Join Filter: (ppp_2.personid = pl_2.personid)
40. 3.256 77.515 ↑ 91.0 1 1

Nested Loop (cost=1.83..1,793.51 rows=91 width=85) (actual time=37.211..77.515 rows=1 loops=1)

  • Join Filter: ((ppp_2.personid = pc.personid) AND (((pp_2.periodstartdate >= pc.effectivedate) AND (pp_2.periodstartdate <= pc.enddate)) OR ((pc.effectivedate >= pp_2.periodstartdate) AND (pc.effectivedate <= pp_2.periodenddate))) AND ((pc.createts >= pp_2.createts) OR ((pp_2.createts >= pc.createts) AND (pp_2.createts <= pc.endts) AND (pc.endts < CURRENT_TIMESTAMP))))
  • Rows Removed by Join Filter: 20287
41. 1.219 29.412 ↓ 10.8 4,077 1

Nested Loop (cost=1.40..1,471.79 rows=376 width=68) (actual time=0.057..29.412 rows=4,077 loops=1)

42. 2.334 28.193 ↓ 10.8 4,077 1

Nested Loop (cost=0.99..1,464.65 rows=376 width=37) (actual time=0.038..28.193 rows=4,077 loops=1)

43. 1.397 1.397 ↓ 6.4 4,077 1

Index Only Scan using pk_person_pay_process on person_pay_process ppp_2 (cost=0.56..16.78 rows=641 width=17) (actual time=0.019..1.397 rows=4,077 loops=1)

  • Index Cond: (payprocessid = 324739)
  • Heap Fetches: 4077
44. 24.462 24.462 ↑ 1.0 1 4,077

Index Scan using person_names_personid_idx on person_names pn_2 (cost=0.42..2.25 rows=1 width=20) (actual time=0.006..0.006 rows=1 loops=4,077)

  • Index Cond: (personid = ppp_2.personid)
  • Filter: ((nametype = 'Legal'::bpchar) AND (CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 0
45. 0.000 0.000 ↑ 1.0 1 4,077

Materialize (cost=0.42..2.44 rows=1 width=35) (actual time=0.000..0.000 rows=1 loops=4,077)

46. 0.015 0.015 ↑ 1.0 1 1

Index Scan using pk_pay_process on pay_process pp_2 (cost=0.42..2.44 rows=1 width=35) (actual time=0.015..0.015 rows=1 loops=1)

  • Index Cond: (payprocessid = 324739)
47. 44.847 44.847 ↓ 1.7 5 4,077

Index Scan using pk_person_compensation on person_compensation pc (cost=0.42..0.75 rows=3 width=37) (actual time=0.008..0.011 rows=5 loops=4,077)

  • Index Cond: (personid = pn_2.personid)
  • Filter: ((createts <> endts) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 3
48. 0.017 0.017 ↑ 1.0 1 1

Index Scan using person_payroll_personid_idx on person_payroll pl_2 (cost=0.42..0.46 rows=1 width=17) (actual time=0.017..0.017 rows=1 loops=1)

  • Index Cond: (personid = pc.personid)
  • Filter: ((CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
49. 0.027 0.027 ↑ 2.0 1 1

Index Scan using personemploymentenddate on person_employment pe_2 (cost=0.43..0.56 rows=2 width=17) (actual time=0.020..0.027 rows=1 loops=1)

  • Index Cond: ((personid = pc.personid) AND (CURRENT_DATE <= enddate) AND (CURRENT_DATE >= effectivedate))
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 4
50.          

SubPlan (for Nested Loop)

51. 0.011 0.090 ↑ 1.0 1 1

Aggregate (cost=2.44..2.45 rows=1 width=8) (actual time=0.090..0.090 rows=1 loops=1)

52. 0.079 0.079 ↓ 79.0 79 1

Index Scan using payprocesspayunitperiod on pay_process pp2_13 (cost=0.42..2.44 rows=1 width=8) (actual time=0.015..0.079 rows=79 loops=1)

  • Index Cond: ((pp_2.payunitid = payunitid) AND (pp_2.payyear = payyear) AND (pp_2.payperiod = payperiod))
53. 0.006 70.760 ↑ 6.8 12 1

Unique (cost=1,954.67..1,957.13 rows=82 width=583) (actual time=70.754..70.760 rows=12 loops=1)

54. 0.055 70.754 ↑ 6.3 13 1

Sort (cost=1,954.67..1,954.88 rows=82 width=583) (actual time=70.754..70.754 rows=13 loops=1)

  • Sort Key: ppp_3.personid, pn_3.lname, persp.effectivedate, persp.createts, persp.endts, (CASE WHEN (persp.createts > (SubPlan 14)) THEN 'Not Processed'::text ELSE 'Processed'::text END), pe_3.companyid, pl_3.payunitid, pp_3.payperiod, pp_3.payyear, pp_3.payprocesstype
  • Sort Method: quicksort Memory: 28kB
55. 0.035 70.699 ↑ 6.3 13 1

Nested Loop (cost=2.68..1,952.07 rows=82 width=583) (actual time=17.083..70.699 rows=13 loops=1)

  • Join Filter: (ppp_3.personid = pe_3.personid)
56. 0.010 69.884 ↑ 3.7 13 1

Nested Loop (cost=2.25..1,720.93 rows=48 width=102) (actual time=16.967..69.884 rows=13 loops=1)

  • Join Filter: (ppp_3.personid = pl_3.personid)
57. 3.895 69.757 ↑ 3.8 13 1

Nested Loop (cost=1.83..1,697.25 rows=49 width=85) (actual time=16.943..69.757 rows=13 loops=1)

  • Join Filter: ((ppp_3.personid = persp.personid) AND (((pp_3.periodstartdate >= persp.effectivedate) AND (pp_3.periodstartdate <= persp.enddate)) OR ((persp.effectivedate >= pp_3.periodstartdate) AND (persp.effectivedate <= pp_3.periodenddate))) AND ((persp.createts >= pp_3.createts) OR ((pp_3.createts >= persp.createts) AND (pp_3.createts <= persp.endts) AND (persp.endts < CURRENT_TIMESTAMP))))
  • Rows Removed by Join Filter: 20237
58. 1.273 29.169 ↓ 10.8 4,077 1

Nested Loop (cost=1.40..1,471.79 rows=376 width=68) (actual time=0.056..29.169 rows=4,077 loops=1)

59. 2.063 27.896 ↓ 10.8 4,077 1

Nested Loop (cost=0.99..1,464.65 rows=376 width=37) (actual time=0.040..27.896 rows=4,077 loops=1)

60. 1.371 1.371 ↓ 6.4 4,077 1

Index Only Scan using pk_person_pay_process on person_pay_process ppp_3 (cost=0.56..16.78 rows=641 width=17) (actual time=0.019..1.371 rows=4,077 loops=1)

  • Index Cond: (payprocessid = 324739)
  • Heap Fetches: 4077
61. 24.462 24.462 ↑ 1.0 1 4,077

Index Scan using person_names_personid_idx on person_names pn_3 (cost=0.42..2.25 rows=1 width=20) (actual time=0.006..0.006 rows=1 loops=4,077)

  • Index Cond: (personid = ppp_3.personid)
  • Filter: ((nametype = 'Legal'::bpchar) AND (CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 0
62. 0.000 0.000 ↑ 1.0 1 4,077

Materialize (cost=0.42..2.44 rows=1 width=35) (actual time=0.000..0.000 rows=1 loops=4,077)

63. 0.013 0.013 ↑ 1.0 1 1

Index Scan using pk_pay_process on pay_process pp_3 (cost=0.42..2.44 rows=1 width=35) (actual time=0.013..0.013 rows=1 loops=1)

  • Index Cond: (payprocessid = 324739)
64. 36.693 36.693 ↓ 2.5 5 4,077

Index Scan using pers_pos_personid_idx on pers_pos persp (cost=0.42..0.53 rows=2 width=37) (actual time=0.006..0.009 rows=5 loops=4,077)

  • Index Cond: (personid = pn_3.personid)
  • Filter: ((createts <> endts) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 1
65. 0.117 0.117 ↑ 1.0 1 13

Index Scan using person_payroll_personid_idx on person_payroll pl_3 (cost=0.42..0.47 rows=1 width=17) (actual time=0.008..0.009 rows=1 loops=13)

  • Index Cond: (personid = persp.personid)
  • Filter: ((CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
66. 0.143 0.143 ↑ 2.0 1 13

Index Scan using personemploymentenddate on person_employment pe_3 (cost=0.43..0.60 rows=2 width=17) (actual time=0.010..0.011 rows=1 loops=13)

  • Index Cond: ((personid = persp.personid) AND (CURRENT_DATE <= enddate) AND (CURRENT_DATE >= effectivedate))
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 1
67.          

SubPlan (for Nested Loop)

68. 0.117 0.637 ↑ 1.0 1 13

Aggregate (cost=2.44..2.45 rows=1 width=8) (actual time=0.049..0.049 rows=1 loops=13)

69. 0.520 0.520 ↓ 79.0 79 13

Index Scan using payprocesspayunitperiod on pay_process pp2_12 (cost=0.42..2.44 rows=1 width=8) (actual time=0.006..0.040 rows=79 loops=13)

  • Index Cond: ((pp_3.payunitid = payunitid) AND (pp_3.payyear = payyear) AND (pp_3.payperiod = payperiod))
70. 0.000 23.674 ↓ 0.0 0 1

Unique (cost=1,776.62..1,777.22 rows=20 width=583) (actual time=23.674..23.674 rows=0 loops=1)

71. 0.025 23.674 ↓ 0.0 0 1

Sort (cost=1,776.62..1,776.67 rows=20 width=583) (actual time=23.674..23.674 rows=0 loops=1)

  • Sort Key: ppp_4.personid, pn_4.lname, pd.effectivedate, pd.createts, pd.endts, (CASE WHEN (pd.createts > (SubPlan 13)) THEN 'Not Processed'::text ELSE 'Processed'::text END), pe_4.companyid, pl_4.payunitid, pp_4.payperiod, pp_4.payyear, pp_4.payprocesstype
  • Sort Method: quicksort Memory: 25kB
72. 0.001 23.649 ↓ 0.0 0 1

Nested Loop (cost=3.10..1,776.19 rows=20 width=583) (actual time=23.649..23.649 rows=0 loops=1)

  • Join Filter: (ppp_4.personid = pe_4.personid)
73. 0.001 23.648 ↓ 0.0 0 1

Nested Loop (cost=2.67..1,719.88 rows=12 width=102) (actual time=23.648..23.648 rows=0 loops=1)

  • Join Filter: (ppp_4.personid = pl_4.personid)
74. 0.001 23.647 ↓ 0.0 0 1

Nested Loop (cost=2.25..1,714.13 rows=12 width=85) (actual time=23.647..23.647 rows=0 loops=1)

  • Join Filter: (ppp_4.personid = pn_4.personid)
75. 0.005 23.646 ↓ 0.0 0 1

Nested Loop (cost=1.83..1,703.87 rows=20 width=65) (actual time=23.646..23.646 rows=0 loops=1)

  • Join Filter: ((((pp_4.periodstartdate >= pd.effectivedate) AND (pp_4.periodstartdate <= pd.enddate)) OR ((pd.effectivedate >= pp_4.periodstartdate) AND (pd.effectivedate <= pp_4.periodenddate))) AND ((pd.createts >= pp_4.createts) OR ((pp_4.createts >= pd.createts) AND (pp_4.createts <= pd.endts) AND (pd.endts < CURRENT_TIMESTAMP))))
  • Rows Removed by Join Filter: 36
76. 0.976 23.497 ↑ 7.3 24 1

Nested Loop (cost=1.40..1,600.89 rows=176 width=65) (actual time=0.702..23.497 rows=24 loops=1)

77. 1.058 2.136 ↓ 6.4 4,077 1

Nested Loop (cost=0.98..27.23 rows=641 width=48) (actual time=0.040..2.136 rows=4,077 loops=1)

78. 1.078 1.078 ↓ 6.4 4,077 1

Index Only Scan using pk_person_pay_process on person_pay_process ppp_4 (cost=0.56..16.78 rows=641 width=17) (actual time=0.018..1.078 rows=4,077 loops=1)

  • Index Cond: (payprocessid = 324739)
  • Heap Fetches: 4077
79. 0.000 0.000 ↑ 1.0 1 4,077

Materialize (cost=0.42..2.44 rows=1 width=35) (actual time=0.000..0.000 rows=1 loops=4,077)

80. 0.017 0.017 ↑ 1.0 1 1

Index Scan using pk_pay_process on pay_process pp_4 (cost=0.42..2.44 rows=1 width=35) (actual time=0.017..0.017 rows=1 loops=1)

  • Index Cond: (payprocessid = 324739)
81. 20.385 20.385 ↓ 0.0 0 4,077

Index Scan using pers_pospersendeff on pers_pos persp_1 (cost=0.42..2.44 rows=1 width=25) (actual time=0.005..0.005 rows=0 loops=4,077)

  • Index Cond: ((personid = ppp_4.personid) AND (enddate <= pp_4.periodenddate) AND (pp_4.periodstartdate <= effectivedate))
82. 0.144 0.144 ↓ 2.0 2 24

Index Scan using position_desc_positionid_idx on position_desc pd (cost=0.42..0.55 rows=1 width=28) (actual time=0.005..0.006 rows=2 loops=24)

  • Index Cond: (positionid = persp_1.positionid)
  • Filter: ((createts <> endts) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 0
83. 0.000 0.000 ↓ 0.0 0

Index Scan using person_names_personid_idx on person_names pn_4 (cost=0.42..0.50 rows=1 width=20) (never executed)

  • Index Cond: (personid = persp_1.personid)
  • Filter: ((nametype = 'Legal'::bpchar) AND (CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
84. 0.000 0.000 ↓ 0.0 0

Index Scan using person_payroll_personid_idx on person_payroll pl_4 (cost=0.42..0.47 rows=1 width=17) (never executed)

  • Index Cond: (personid = persp_1.personid)
  • Filter: ((CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
85. 0.000 0.000 ↓ 0.0 0

Index Scan using personemploymentenddate on person_employment pe_4 (cost=0.43..0.58 rows=2 width=17) (never executed)

  • Index Cond: ((personid = persp_1.personid) AND (CURRENT_DATE <= enddate) AND (CURRENT_DATE >= effectivedate))
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
86.          

SubPlan (for Nested Loop)

87. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=2.44..2.45 rows=1 width=8) (never executed)

88. 0.000 0.000 ↓ 0.0 0

Index Scan using payprocesspayunitperiod on pay_process pp2_11 (cost=0.42..2.44 rows=1 width=8) (never executed)

  • Index Cond: ((pp_4.payunitid = payunitid) AND (pp_4.payyear = payyear) AND (pp_4.payperiod = payperiod))
89. 0.000 46.980 ↓ 0.0 0 1

Unique (cost=1,771.54..1,772.02 rows=16 width=583) (actual time=46.980..46.980 rows=0 loops=1)

90. 0.018 46.980 ↓ 0.0 0 1

Sort (cost=1,771.54..1,771.58 rows=16 width=583) (actual time=46.980..46.980 rows=0 loops=1)

  • Sort Key: ppp_5.personid, pn_5.lname, pmd.effectivedate, pmd.createts, pmd.endts, (CASE WHEN (pmd.createts > (SubPlan 12)) THEN 'Not Processed'::text ELSE 'Processed'::text END), pe_5.companyid, pl_5.payunitid, pp_5.payperiod, pp_5.payyear, pp_5.payprocesstype
  • Sort Method: quicksort Memory: 25kB
91. 0.001 46.962 ↓ 0.0 0 1

Nested Loop (cost=2.67..1,771.22 rows=16 width=583) (actual time=46.961..46.962 rows=0 loops=1)

92. 0.001 46.961 ↓ 0.0 0 1

Nested Loop (cost=2.25..1,724.24 rows=16 width=102) (actual time=46.961..46.961 rows=0 loops=1)

  • Join Filter: (ppp_5.personid = pe_5.personid)
93. 0.403 46.960 ↓ 0.0 0 1

Nested Loop (cost=1.82..1,710.35 rows=21 width=85) (actual time=46.960..46.960 rows=0 loops=1)

  • Join Filter: ((((pp_5.periodstartdate >= pmd.effectivedate) AND (pp_5.periodstartdate <= pmd.enddate)) OR ((pmd.effectivedate >= pp_5.periodstartdate) AND (pmd.effectivedate <= pp_5.periodenddate))) AND ((pmd.createts >= pp_5.createts) OR ((pp_5.createts >= pmd.createts) AND (pp_5.createts <= pmd.endts) AND (pmd.endts < CURRENT_TIMESTAMP))))
  • Rows Removed by Join Filter: 1342
94. 2.472 46.557 ↓ 4.8 1,342 1

Nested Loop (cost=1.40..1,698.15 rows=279 width=74) (actual time=0.069..46.557 rows=1,342 loops=1)

  • Join Filter: (ppp_5.personid = pmd.personid)
95. 2.027 27.777 ↓ 10.8 4,077 1

Nested Loop (cost=0.99..1,464.65 rows=376 width=37) (actual time=0.041..27.777 rows=4,077 loops=1)

96. 1.288 1.288 ↓ 6.4 4,077 1

Index Only Scan using pk_person_pay_process on person_pay_process ppp_5 (cost=0.56..16.78 rows=641 width=17) (actual time=0.017..1.288 rows=4,077 loops=1)

  • Index Cond: (payprocessid = 324739)
  • Heap Fetches: 4077
97. 24.462 24.462 ↑ 1.0 1 4,077

Index Scan using person_names_personid_idx on person_names pn_5 (cost=0.42..2.25 rows=1 width=20) (actual time=0.006..0.006 rows=1 loops=4,077)

  • Index Cond: (personid = ppp_5.personid)
  • Filter: ((nametype = 'Legal'::bpchar) AND (CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 0
98. 16.308 16.308 ↓ 0.0 0 4,077

Index Scan using pk_person_misc_deduction on person_misc_deduction pmd (cost=0.42..0.58 rows=3 width=37) (actual time=0.004..0.004 rows=0 loops=4,077)

  • Index Cond: (personid = pn_5.personid)
  • Filter: ((createts <> endts) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 0
99. 0.000 0.000 ↑ 1.0 1 1,342

Materialize (cost=0.42..2.44 rows=1 width=35) (actual time=0.000..0.000 rows=1 loops=1,342)

100. 0.016 0.016 ↑ 1.0 1 1

Index Scan using pk_pay_process on pay_process pp_5 (cost=0.42..2.44 rows=1 width=35) (actual time=0.016..0.016 rows=1 loops=1)

  • Index Cond: (payprocessid = 324739)
101. 0.000 0.000 ↓ 0.0 0

Index Scan using personemploymentenddate on person_employment pe_5 (cost=0.43..0.64 rows=2 width=17) (never executed)

  • Index Cond: ((personid = pn_5.personid) AND (CURRENT_DATE <= enddate) AND (CURRENT_DATE >= effectivedate))
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
102. 0.000 0.000 ↓ 0.0 0

Index Scan using person_payroll_personid_idx on person_payroll pl_5 (cost=0.42..0.47 rows=1 width=17) (never executed)

  • Index Cond: (personid = pe_5.personid)
  • Filter: ((CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
103.          

SubPlan (for Nested Loop)

104. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=2.44..2.45 rows=1 width=8) (never executed)

105. 0.000 0.000 ↓ 0.0 0

Index Scan using payprocesspayunitperiod on pay_process pp2_10 (cost=0.42..2.44 rows=1 width=8) (never executed)

  • Index Cond: ((pp_5.payunitid = payunitid) AND (pp_5.payyear = payyear) AND (pp_5.payperiod = payperiod))
106. 0.001 53.750 ↓ 0.0 0 1

Unique (cost=1,834.92..1,836.15 rows=41 width=583) (actual time=53.750..53.750 rows=0 loops=1)

107. 0.023 53.749 ↓ 0.0 0 1

Sort (cost=1,834.92..1,835.02 rows=41 width=583) (actual time=53.749..53.749 rows=0 loops=1)

  • Sort Key: ppp_6.personid, pn_6.lname, pptop.effectivedate, pptop.createts, pptop.endts, (CASE WHEN (pptop.createts > (SubPlan 11)) THEN 'Not Processed'::text ELSE 'Processed'::text END), pe_6.companyid, pl_6.payunitid, pp_6.payperiod, pp_6.payyear, pp_6.payprocesstype
  • Sort Method: quicksort Memory: 25kB
108. 0.001 53.726 ↓ 0.0 0 1

Nested Loop (cost=2.67..1,833.82 rows=41 width=583) (actual time=53.726..53.726 rows=0 loops=1)

109. 0.001 53.725 ↓ 0.0 0 1

Nested Loop (cost=2.25..1,713.93 rows=40 width=102) (actual time=53.725..53.725 rows=0 loops=1)

  • Join Filter: (ppp_6.personid = pe_6.personid)
110. 3.942 53.724 ↓ 0.0 0 1

Nested Loop (cost=1.82..1,676.24 rows=57 width=85) (actual time=53.724..53.724 rows=0 loops=1)

  • Join Filter: ((ppp_6.personid = pptop.personid) AND (((pp_6.periodstartdate >= pptop.effectivedate) AND (pp_6.periodstartdate <= pptop.enddate)) OR ((pptop.effectivedate >= pp_6.periodstartdate) AND (pptop.effectivedate <= pp_6.periodenddate))) AND ((pptop.createts >= pp_6.createts) OR ((pp_6.createts >= pptop.createts) AND (pp_6.createts <= pptop.endts) AND (pptop.endts < CURRENT_TIMESTAMP))))
  • Rows Removed by Join Filter: 8626
111. 1.170 29.397 ↓ 10.8 4,077 1

Nested Loop (cost=1.40..1,471.79 rows=376 width=68) (actual time=0.061..29.397 rows=4,077 loops=1)

112. 2.393 28.227 ↓ 10.8 4,077 1

Nested Loop (cost=0.99..1,464.65 rows=376 width=37) (actual time=0.042..28.227 rows=4,077 loops=1)

113. 1.372 1.372 ↓ 6.4 4,077 1

Index Only Scan using pk_person_pay_process on person_pay_process ppp_6 (cost=0.56..16.78 rows=641 width=17) (actual time=0.020..1.372 rows=4,077 loops=1)

  • Index Cond: (payprocessid = 324739)
  • Heap Fetches: 4077
114. 24.462 24.462 ↑ 1.0 1 4,077

Index Scan using person_names_personid_idx on person_names pn_6 (cost=0.42..2.25 rows=1 width=20) (actual time=0.006..0.006 rows=1 loops=4,077)

  • Index Cond: (personid = ppp_6.personid)
  • Filter: ((nametype = 'Legal'::bpchar) AND (CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 0
115. 0.000 0.000 ↑ 1.0 1 4,077

Materialize (cost=0.42..2.44 rows=1 width=35) (actual time=0.000..0.000 rows=1 loops=4,077)

116. 0.015 0.015 ↑ 1.0 1 1

Index Scan using pk_pay_process on pay_process pp_6 (cost=0.42..2.44 rows=1 width=35) (actual time=0.015..0.015 rows=1 loops=1)

  • Index Cond: (payprocessid = 324739)
117. 20.385 20.385 ↑ 1.0 2 4,077

Index Scan using personptoplansx on person_pto_plans pptop (cost=0.42..0.47 rows=2 width=37) (actual time=0.005..0.005 rows=2 loops=4,077)

  • Index Cond: (personid = pn_6.personid)
  • Filter: (createts <> endts)
118. 0.000 0.000 ↓ 0.0 0

Index Scan using personemploymentenddate on person_employment pe_6 (cost=0.43..0.64 rows=2 width=17) (never executed)

  • Index Cond: ((personid = pn_6.personid) AND (CURRENT_DATE <= enddate) AND (CURRENT_DATE >= effectivedate))
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
119. 0.000 0.000 ↓ 0.0 0

Index Scan using person_payroll_personid_idx on person_payroll pl_6 (cost=0.42..0.47 rows=1 width=17) (never executed)

  • Index Cond: (personid = pe_6.personid)
  • Filter: ((CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
120.          

SubPlan (for Nested Loop)

121. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=2.44..2.45 rows=1 width=8) (never executed)

122. 0.000 0.000 ↓ 0.0 0

Index Scan using payprocesspayunitperiod on pay_process pp2_9 (cost=0.42..2.44 rows=1 width=8) (never executed)

  • Index Cond: ((pp_6.payunitid = payunitid) AND (pp_6.payyear = payyear) AND (pp_6.payperiod = payperiod))
123. 0.000 1.813 ↓ 0.0 0 1

Unique (cost=50.96..50.99 rows=1 width=583) (actual time=1.813..1.813 rows=0 loops=1)

124. 0.025 1.813 ↓ 0.0 0 1

Sort (cost=50.96..50.96 rows=1 width=583) (actual time=1.813..1.813 rows=0 loops=1)

  • Sort Key: ppp_7.personid, pn_7.lname, persp_2.effectivedate, persp_2.createts, persp_2.endts, (CASE WHEN (persp_2.createts > (SubPlan 10)) THEN 'Not Processed'::text ELSE 'Processed'::text END), pe_7.companyid, pl_7.payunitid, pp_7.payperiod, pp_7.payyear, pp_7.payprocesstype
  • Sort Method: quicksort Memory: 25kB
125. 0.001 1.788 ↓ 0.0 0 1

Nested Loop (cost=2.96..50.95 rows=1 width=583) (actual time=1.788..1.788 rows=0 loops=1)

126. 0.000 1.787 ↓ 0.0 0 1

Nested Loop (cost=2.68..47.81 rows=1 width=78) (actual time=1.787..1.787 rows=0 loops=1)

127. 0.001 1.787 ↓ 0.0 0 1

Nested Loop (cost=2.26..47.28 rows=1 width=110) (actual time=1.787..1.787 rows=0 loops=1)

  • Join Filter: (ppp_7.personid = pe_7.personid)
128. 0.000 1.786 ↓ 0.0 0 1

Nested Loop (cost=1.83..46.61 rows=1 width=93) (actual time=1.786..1.786 rows=0 loops=1)

129. 0.008 1.786 ↓ 0.0 0 1

Nested Loop (cost=1.41..44.59 rows=1 width=76) (actual time=1.785..1.786 rows=0 loops=1)

  • Join Filter: (((persp_2.createts >= pp_7.createts) AND (CURRENT_TIMESTAMP >= persp_2.createts) AND (CURRENT_TIMESTAMP <= persp_2.endts)) OR ((pp_7.createts >= persp_2.createts) AND (pp_7.createts <= persp_2.endts) AND (persp_2.endts < CURRENT_TIMESTAMP)))
  • Rows Removed by Join Filter: 9
130. 0.668 1.751 ↓ 9.0 9 1

Merge Join (cost=0.99..42.13 rows=1 width=65) (actual time=0.081..1.751 rows=9 loops=1)

  • Merge Cond: (ppp_7.personid = persp_2.personid)
131. 1.051 1.051 ↓ 5.9 3,810 1

Index Only Scan using pk_person_pay_process on person_pay_process ppp_7 (cost=0.56..16.78 rows=641 width=17) (actual time=0.021..1.051 rows=3,810 loops=1)

  • Index Cond: (payprocessid = 324739)
  • Heap Fetches: 3810
132. 0.032 0.032 ↑ 5.2 9 1

Index Scan using pk_person_payments on person_payments persp_2 (cost=0.43..23.61 rows=47 width=48) (actual time=0.020..0.032 rows=9 loops=1)

  • Index Cond: (payprocessid = 324739)
  • Filter: (createts <> endts)
133. 0.027 0.027 ↑ 1.0 1 9

Index Scan using pk_pay_process on pay_process pp_7 (cost=0.42..2.44 rows=1 width=27) (actual time=0.003..0.003 rows=1 loops=9)

  • Index Cond: (payprocessid = 324739)
134. 0.000 0.000 ↓ 0.0 0

Index Scan using person_payroll_personid_idx on person_payroll pl_7 (cost=0.42..2.01 rows=1 width=17) (never executed)

  • Index Cond: (personid = ppp_7.personid)
  • Filter: ((CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
135. 0.000 0.000 ↓ 0.0 0

Index Scan using personemploymentenddate on person_employment pe_7 (cost=0.43..0.65 rows=2 width=17) (never executed)

  • Index Cond: ((personid = pl_7.personid) AND (CURRENT_DATE <= enddate) AND (CURRENT_DATE >= effectivedate))
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
136. 0.000 0.000 ↓ 0.0 0

Index Scan using person_names_personid_idx on person_names pn_7 (cost=0.42..0.52 rows=1 width=20) (never executed)

  • Index Cond: (personid = pe_7.personid)
  • Filter: ((nametype = 'Legal'::bpchar) AND (CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
137. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_edt_codes on edt_codes e (cost=0.27..0.68 rows=1 width=11) (never executed)

  • Index Cond: (edtcode = persp_2.edtcode)
  • Filter: (edttype = 'E'::bpchar)
138.          

SubPlan (for Nested Loop)

139. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=2.44..2.45 rows=1 width=8) (never executed)

140. 0.000 0.000 ↓ 0.0 0

Index Scan using payprocesspayunitperiod on pay_process pp2_8 (cost=0.42..2.44 rows=1 width=8) (never executed)

  • Index Cond: ((pp_7.payunitid = payunitid) AND (pp_7.payyear = payyear) AND (pp_7.payperiod = payperiod))
141. 0.000 1.371 ↓ 0.0 0 1

Unique (cost=50.96..50.99 rows=1 width=583) (actual time=1.371..1.371 rows=0 loops=1)

142. 0.021 1.371 ↓ 0.0 0 1

Sort (cost=50.96..50.96 rows=1 width=583) (actual time=1.371..1.371 rows=0 loops=1)

  • Sort Key: ppp_8.personid, pn_8.lname, persp_3.effectivedate, persp_3.createts, persp_3.endts, (CASE WHEN (persp_3.createts > (SubPlan 9)) THEN 'Not Processed'::text ELSE 'Processed'::text END), pe_8.companyid, pl_8.payunitid, pp_8.payperiod, pp_8.payyear, pp_8.payprocesstype
  • Sort Method: quicksort Memory: 25kB
143. 0.000 1.350 ↓ 0.0 0 1

Nested Loop (cost=2.96..50.95 rows=1 width=583) (actual time=1.350..1.350 rows=0 loops=1)

144. 0.000 1.350 ↓ 0.0 0 1

Nested Loop (cost=2.68..47.81 rows=1 width=78) (actual time=1.350..1.350 rows=0 loops=1)

145. 0.001 1.350 ↓ 0.0 0 1

Nested Loop (cost=2.26..47.28 rows=1 width=110) (actual time=1.350..1.350 rows=0 loops=1)

  • Join Filter: (ppp_8.personid = pe_8.personid)
146. 0.000 1.349 ↓ 0.0 0 1

Nested Loop (cost=1.83..46.61 rows=1 width=93) (actual time=1.349..1.349 rows=0 loops=1)

147. 0.007 1.349 ↓ 0.0 0 1

Nested Loop (cost=1.41..44.59 rows=1 width=76) (actual time=1.349..1.349 rows=0 loops=1)

  • Join Filter: (((persp_3.createts >= pp_8.createts) AND (CURRENT_TIMESTAMP >= persp_3.createts) AND (CURRENT_TIMESTAMP <= persp_3.endts)) OR ((pp_8.createts >= persp_3.createts) AND (pp_8.createts <= persp_3.endts) AND (persp_3.endts < CURRENT_TIMESTAMP)))
  • Rows Removed by Join Filter: 9
148. 0.664 1.324 ↓ 9.0 9 1

Merge Join (cost=0.99..42.13 rows=1 width=65) (actual time=0.052..1.324 rows=9 loops=1)

  • Merge Cond: (ppp_8.personid = persp_3.personid)
149. 0.646 0.646 ↓ 5.9 3,810 1

Index Only Scan using pk_person_pay_process on person_pay_process ppp_8 (cost=0.56..16.78 rows=641 width=17) (actual time=0.012..0.646 rows=3,810 loops=1)

  • Index Cond: (payprocessid = 324739)
  • Heap Fetches: 3810
150. 0.014 0.014 ↑ 5.2 9 1

Index Scan using pk_person_payments on person_payments persp_3 (cost=0.43..23.61 rows=47 width=48) (actual time=0.008..0.014 rows=9 loops=1)

  • Index Cond: (payprocessid = 324739)
  • Filter: (createts <> endts)
151. 0.018 0.018 ↑ 1.0 1 9

Index Scan using pk_pay_process on pay_process pp_8 (cost=0.42..2.44 rows=1 width=27) (actual time=0.002..0.002 rows=1 loops=9)

  • Index Cond: (payprocessid = 324739)
152. 0.000 0.000 ↓ 0.0 0

Index Scan using person_payroll_personid_idx on person_payroll pl_8 (cost=0.42..2.01 rows=1 width=17) (never executed)

  • Index Cond: (personid = ppp_8.personid)
  • Filter: ((CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
153. 0.000 0.000 ↓ 0.0 0

Index Scan using personemploymentenddate on person_employment pe_8 (cost=0.43..0.65 rows=2 width=17) (never executed)

  • Index Cond: ((personid = pl_8.personid) AND (CURRENT_DATE <= enddate) AND (CURRENT_DATE >= effectivedate))
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
154. 0.000 0.000 ↓ 0.0 0

Index Scan using person_names_personid_idx on person_names pn_8 (cost=0.42..0.52 rows=1 width=20) (never executed)

  • Index Cond: (personid = pe_8.personid)
  • Filter: ((nametype = 'Legal'::bpchar) AND (CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
155. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_edt_codes on edt_codes e_1 (cost=0.27..0.68 rows=1 width=11) (never executed)

  • Index Cond: (edtcode = persp_3.edtcode)
  • Filter: (edttype = 'D'::bpchar)
156.          

SubPlan (for Nested Loop)

157. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=2.44..2.45 rows=1 width=8) (never executed)

158. 0.000 0.000 ↓ 0.0 0

Index Scan using payprocesspayunitperiod on pay_process pp2_7 (cost=0.42..2.44 rows=1 width=8) (never executed)

  • Index Cond: ((pp_8.payunitid = payunitid) AND (pp_8.payyear = payyear) AND (pp_8.payperiod = payperiod))
159. 0.007 343.552 ↑ 1,872.0 1 1

Unique (cost=14,230.70..14,286.86 rows=1,872 width=583) (actual time=343.546..343.552 rows=1 loops=1)

160. 0.051 343.545 ↑ 117.0 16 1

Sort (cost=14,230.70..14,235.38 rows=1,872 width=583) (actual time=343.545..343.545 rows=16 loops=1)

  • Sort Key: ppp_9.personid, pn_9.lname, pbe.effectivedate, pbe.createts, pbe.endts, (CASE WHEN (pbe.createts > (SubPlan 8)) THEN 'Not Processed'::text ELSE 'Processed'::text END), pe_9.companyid, pl_9.payunitid, pp_9.payperiod, pp_9.payyear, pp_9.payprocesstype
  • Sort Method: quicksort Memory: 29kB
161. 0.043 343.494 ↑ 117.0 16 1

Nested Loop (cost=2.81..14,128.96 rows=1,872 width=583) (actual time=153.618..343.494 rows=16 loops=1)

  • Join Filter: (ppp_9.personid = pe_9.personid)
162. 20.097 342.651 ↑ 155.6 16 1

Nested Loop (cost=2.38..8,149.90 rows=2,490 width=102) (actual time=153.440..342.651 rows=16 loops=1)

  • Join Filter: ((ppp_9.personid = pbe.personid) AND (((pp_9.periodstartdate >= pbe.effectivedate) AND (pp_9.periodstartdate <= pbe.enddate)) OR ((pbe.effectivedate >= pp_9.periodstartdate) AND (pbe.effectivedate <= pp_9.periodenddate))) AND ((pbe.createts >= pp_9.createts) OR ((pp_9.createts >= pbe.createts) AND (pp_9.createts <= pbe.endts) AND (pbe.endts < CURRENT_TIMESTAMP))))
  • Rows Removed by Join Filter: 156778
163. 1.333 57.549 ↓ 11.0 4,077 1

Nested Loop (cost=1.82..1,656.26 rows=371 width=85) (actual time=0.077..57.549 rows=4,077 loops=1)

164. 0.696 56.216 ↓ 11.0 4,077 1

Nested Loop (cost=1.41..1,649.19 rows=371 width=54) (actual time=0.061..56.216 rows=4,077 loops=1)

  • Join Filter: (ppp_9.personid = pl_9.personid)
165. 0.925 31.058 ↓ 10.8 4,077 1

Nested Loop (cost=0.99..1,464.65 rows=376 width=37) (actual time=0.034..31.058 rows=4,077 loops=1)

166. 1.594 1.594 ↓ 6.4 4,077 1

Index Only Scan using pk_person_pay_process on person_pay_process ppp_9 (cost=0.56..16.78 rows=641 width=17) (actual time=0.010..1.594 rows=4,077 loops=1)

  • Index Cond: (payprocessid = 324739)
  • Heap Fetches: 4077
167. 28.539 28.539 ↑ 1.0 1 4,077

Index Scan using person_names_personid_idx on person_names pn_9 (cost=0.42..2.25 rows=1 width=20) (actual time=0.007..0.007 rows=1 loops=4,077)

  • Index Cond: (personid = ppp_9.personid)
  • Filter: ((nametype = 'Legal'::bpchar) AND (CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 0
168. 24.462 24.462 ↑ 1.0 1 4,077

Index Scan using person_payroll_personid_idx on person_payroll pl_9 (cost=0.42..0.48 rows=1 width=17) (actual time=0.006..0.006 rows=1 loops=4,077)

  • Index Cond: (personid = pn_9.personid)
  • Filter: ((CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 0
169. 0.000 0.000 ↑ 1.0 1 4,077

Materialize (cost=0.42..2.44 rows=1 width=35) (actual time=0.000..0.000 rows=1 loops=4,077)

170. 0.012 0.012 ↑ 1.0 1 1

Index Scan using pk_pay_process on pay_process pp_9 (cost=0.42..2.44 rows=1 width=35) (actual time=0.011..0.012 rows=1 loops=1)

  • Index Cond: (payprocessid = 324739)
171. 265.005 265.005 ↑ 2.3 38 4,077

Index Scan using person_bene_election_personid_idx on person_bene_election pbe (cost=0.56..14.39 rows=89 width=37) (actual time=0.012..0.065 rows=38 loops=4,077)

  • Index Cond: (personid = pn_9.personid)
  • Filter: ((createts <> endts) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 40
172. 0.128 0.128 ↑ 2.0 1 16

Index Scan using personemploymentenddate on person_employment pe_9 (cost=0.43..0.53 rows=2 width=17) (actual time=0.006..0.008 rows=1 loops=16)

  • Index Cond: ((personid = pbe.personid) AND (CURRENT_DATE <= enddate) AND (CURRENT_DATE >= effectivedate))
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 4
173.          

SubPlan (for Nested Loop)

174. 0.144 0.672 ↑ 1.0 1 16

Aggregate (cost=2.44..2.45 rows=1 width=8) (actual time=0.042..0.042 rows=1 loops=16)

175. 0.528 0.528 ↓ 79.0 79 16

Index Scan using payprocesspayunitperiod on pay_process pp2_6 (cost=0.42..2.44 rows=1 width=8) (actual time=0.005..0.033 rows=79 loops=16)

  • Index Cond: ((pp_9.payunitid = payunitid) AND (pp_9.payyear = payyear) AND (pp_9.payperiod = payperiod))
176. 0.001 85.467 ↓ 0.0 0 1

Unique (cost=3,617.23..3,630.91 rows=456 width=583) (actual time=85.467..85.467 rows=0 loops=1)

177. 0.027 85.466 ↓ 0.0 0 1

Sort (cost=3,617.23..3,618.37 rows=456 width=583) (actual time=85.466..85.466 rows=0 loops=1)

  • Sort Key: ppp_10.personid, pn_10.lname, pte.effectivedate, pte.createts, pte.endts, (CASE WHEN (pte.createts > (SubPlan 7)) THEN 'Not Processed'::text ELSE 'Processed'::text END), pe_10.companyid, pl_10.payunitid, pp_10.payperiod, pp_10.payyear, pp_10.payprocesstype
  • Sort Method: quicksort Memory: 25kB
178. 0.000 85.439 ↓ 0.0 0 1

Nested Loop (cost=2.68..3,597.10 rows=456 width=583) (actual time=85.439..85.439 rows=0 loops=1)

  • Join Filter: (ppp_10.personid = pe_10.personid)
179. 2.758 85.439 ↓ 0.0 0 1

Nested Loop (cost=2.25..2,242.52 rows=417 width=102) (actual time=85.439..85.439 rows=0 loops=1)

  • Join Filter: ((ppp_10.personid = pte.personid) AND (((pp_10.periodstartdate >= pte.effectivedate) AND (pp_10.periodstartdate <= pte.enddate)) OR ((pte.effectivedate >= pp_10.periodstartdate) AND (pte.effectivedate <= pp_10.periodenddate))) AND ((pte.createts >= pp_10.createts) OR ((pp_10.createts >= pte.createts) AND (pp_10.createts <= pte.endts) AND (pte.endts < CURRENT_TIMESTAMP))))
  • Rows Removed by Join Filter: 7962
180. 1.284 54.142 ↓ 11.0 4,077 1

Nested Loop (cost=1.82..1,656.26 rows=371 width=85) (actual time=0.092..54.142 rows=4,077 loops=1)

181. 3.249 52.858 ↓ 11.0 4,077 1

Nested Loop (cost=1.41..1,649.19 rows=371 width=54) (actual time=0.073..52.858 rows=4,077 loops=1)

  • Join Filter: (ppp_10.personid = pl_10.personid)
182. 3.242 29.224 ↓ 10.8 4,077 1

Nested Loop (cost=0.99..1,464.65 rows=376 width=37) (actual time=0.052..29.224 rows=4,077 loops=1)

183. 1.520 1.520 ↓ 6.4 4,077 1

Index Only Scan using pk_person_pay_process on person_pay_process ppp_10 (cost=0.56..16.78 rows=641 width=17) (actual time=0.027..1.520 rows=4,077 loops=1)

  • Index Cond: (payprocessid = 324739)
  • Heap Fetches: 4077
184. 24.462 24.462 ↑ 1.0 1 4,077

Index Scan using person_names_personid_idx on person_names pn_10 (cost=0.42..2.25 rows=1 width=20) (actual time=0.006..0.006 rows=1 loops=4,077)

  • Index Cond: (personid = ppp_10.personid)
  • Filter: ((nametype = 'Legal'::bpchar) AND (CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 0
185. 20.385 20.385 ↑ 1.0 1 4,077

Index Scan using person_payroll_personid_idx on person_payroll pl_10 (cost=0.42..0.48 rows=1 width=17) (actual time=0.005..0.005 rows=1 loops=4,077)

  • Index Cond: (personid = pn_10.personid)
  • Filter: ((CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 0
186. 0.000 0.000 ↑ 1.0 1 4,077

Materialize (cost=0.42..2.44 rows=1 width=35) (actual time=0.000..0.000 rows=1 loops=4,077)

187. 0.015 0.015 ↑ 1.0 1 1

Index Scan using pk_pay_process on pay_process pp_10 (cost=0.42..2.44 rows=1 width=35) (actual time=0.015..0.015 rows=1 loops=1)

  • Index Cond: (payprocessid = 324739)
188. 28.539 28.539 ↑ 8.5 2 4,077

Index Scan using pk_person_tax_elections on person_tax_elections pte (cost=0.43..0.99 rows=17 width=37) (actual time=0.006..0.007 rows=2 loops=4,077)

  • Index Cond: (personid = pn_10.personid)
  • Filter: (createts <> endts)
189. 0.000 0.000 ↓ 0.0 0

Index Scan using personemploymentenddate on person_employment pe_10 (cost=0.43..0.54 rows=2 width=17) (never executed)

  • Index Cond: ((personid = pte.personid) AND (CURRENT_DATE <= enddate) AND (CURRENT_DATE >= effectivedate))
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
190.          

SubPlan (for Nested Loop)

191. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=2.44..2.45 rows=1 width=8) (never executed)

192. 0.000 0.000 ↓ 0.0 0

Index Scan using payprocesspayunitperiod on pay_process pp2_5 (cost=0.42..2.44 rows=1 width=8) (never executed)

  • Index Cond: ((pp_10.payunitid = payunitid) AND (pp_10.payyear = payyear) AND (pp_10.payperiod = payperiod))
193. 0.000 172.598 ↓ 0.0 0 1

Unique (cost=3,354.51..3,366.31 rows=337 width=583) (actual time=172.598..172.598 rows=0 loops=1)

194. 0.030 172.598 ↓ 0.0 0 1

Sort (cost=3,354.51..3,355.36 rows=337 width=583) (actual time=172.598..172.598 rows=0 loops=1)

  • Sort Key: ppp_11.personid, pn_11.lname, por.effectivedate, por.createts, por.endts, por.positionid, por.organizationid, (CASE WHEN (por.createts > (SubPlan 6)) THEN 'Not Processed'::text ELSE 'Processed'::text END), pe_11.companyid, pl_11.payunitid, pp_11.payperiod, pp_11.payyear, pp_11.payprocesstype
  • Sort Method: quicksort Memory: 25kB
195. 0.001 172.568 ↓ 0.0 0 1

Nested Loop (cost=3.10..3,340.37 rows=337 width=583) (actual time=172.568..172.568 rows=0 loops=1)

  • Join Filter: (ppp_11.personid = pe_11.personid)
196. 0.000 172.567 ↓ 0.0 0 1

Nested Loop (cost=2.67..2,394.00 rows=198 width=110) (actual time=172.567..172.567 rows=0 loops=1)

  • Join Filter: (ppp_11.personid = pl_11.personid)
197. 5.296 172.567 ↓ 0.0 0 1

Nested Loop (cost=2.25..2,298.14 rows=200 width=93) (actual time=172.567..172.567 rows=0 loops=1)

  • Join Filter: ((((pp_11.periodstartdate >= por.effectivedate) AND (pp_11.periodstartdate <= por.enddate)) OR ((por.effectivedate >= pp_11.periodstartdate) AND (por.effectivedate <= pp_11.periodenddate))) AND ((por.createts >= pp_11.createts) OR ((pp_11.createts >= por.createts) AND (pp_11.createts <= por.endts) AND (por.endts < CURRENT_TIMESTAMP))))
  • Rows Removed by Join Filter: 97190
198. 4.289 71.727 ↓ 25.7 23,886 1

Nested Loop (cost=1.83..1,670.93 rows=928 width=85) (actual time=0.078..71.727 rows=23,886 loops=1)

  • Join Filter: (ppp_11.personid = persp_4.personid)
199. 1.241 30.745 ↓ 10.8 4,077 1

Nested Loop (cost=1.40..1,471.79 rows=376 width=68) (actual time=0.057..30.745 rows=4,077 loops=1)

200. 0.000 29.504 ↓ 10.8 4,077 1

Nested Loop (cost=0.99..1,464.65 rows=376 width=37) (actual time=0.039..29.504 rows=4,077 loops=1)

201. 1.549 1.549 ↓ 6.4 4,077 1

Index Only Scan using pk_person_pay_process on person_pay_process ppp_11 (cost=0.56..16.78 rows=641 width=17) (actual time=0.018..1.549 rows=4,077 loops=1)

  • Index Cond: (payprocessid = 324739)
  • Heap Fetches: 4077
202. 28.539 28.539 ↑ 1.0 1 4,077

Index Scan using person_names_personid_idx on person_names pn_11 (cost=0.42..2.25 rows=1 width=20) (actual time=0.006..0.007 rows=1 loops=4,077)

  • Index Cond: (personid = ppp_11.personid)
  • Filter: ((nametype = 'Legal'::bpchar) AND (CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 0
203. 0.000 0.000 ↑ 1.0 1 4,077

Materialize (cost=0.42..2.44 rows=1 width=35) (actual time=0.000..0.000 rows=1 loops=4,077)

204. 0.015 0.015 ↑ 1.0 1 1

Index Scan using pk_pay_process on pay_process pp_11 (cost=0.42..2.44 rows=1 width=35) (actual time=0.015..0.015 rows=1 loops=1)

  • Index Cond: (payprocessid = 324739)
205. 36.693 36.693 ↓ 3.0 6 4,077

Index Scan using pers_pos_personid_idx on pers_pos persp_4 (cost=0.42..0.50 rows=2 width=17) (actual time=0.006..0.009 rows=6 loops=4,077)

  • Index Cond: (personid = pn_11.personid)
206. 95.544 95.544 ↓ 1.3 4 23,886

Index Scan using pos_org_rel_positionid_idx on pos_org_rel por (cost=0.42..0.58 rows=3 width=32) (actual time=0.002..0.004 rows=4 loops=23,886)

  • Index Cond: (positionid = persp_4.positionid)
  • Filter: ((createts <> endts) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 1
207. 0.000 0.000 ↓ 0.0 0

Index Scan using person_payroll_personid_idx on person_payroll pl_11 (cost=0.42..0.47 rows=1 width=17) (never executed)

  • Index Cond: (personid = persp_4.personid)
  • Filter: ((CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
208. 0.000 0.000 ↓ 0.0 0

Index Scan using personemploymentenddate on person_employment pe_11 (cost=0.43..0.58 rows=2 width=17) (never executed)

  • Index Cond: ((personid = persp_4.personid) AND (CURRENT_DATE <= enddate) AND (CURRENT_DATE >= effectivedate))
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
209.          

SubPlan (for Nested Loop)

210. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=2.44..2.45 rows=1 width=8) (never executed)

211. 0.000 0.000 ↓ 0.0 0

Index Scan using payprocesspayunitperiod on pay_process pp2_4 (cost=0.42..2.44 rows=1 width=8) (never executed)

  • Index Cond: ((pp_11.payunitid = payunitid) AND (pp_11.payyear = payyear) AND (pp_11.payperiod = payperiod))
212. 0.000 22.540 ↓ 0.0 0 1

Unique (cost=1,715.97..1,716.63 rows=22 width=603) (actual time=22.540..22.540 rows=0 loops=1)

213. 0.029 22.540 ↓ 0.0 0 1

Sort (cost=1,715.97..1,716.02 rows=22 width=603) (actual time=22.540..22.540 rows=0 loops=1)

  • Sort Key: ppp_12.personid, pn_12.lname, pfpe.effectivedate, pfpe.createts, pfpe.endts, (CASE WHEN (pfpe.createts > (SubPlan 5)) THEN 'Not Processed'::text ELSE 'Processed'::text END), pe_12.companyid, pl_12.payunitid, pp_12.payperiod, pp_12.payyear, pp_12.payprocesstype
  • Sort Method: quicksort Memory: 25kB
214. 0.001 22.511 ↓ 0.0 0 1

Nested Loop (cost=2.67..1,715.48 rows=22 width=603) (actual time=22.511..22.511 rows=0 loops=1)

215. 0.001 22.510 ↓ 0.0 0 1

Nested Loop (cost=2.25..1,650.89 rows=22 width=102) (actual time=22.510..22.510 rows=0 loops=1)

  • Join Filter: (ppp_12.personid = pe_12.personid)
216. 0.000 22.509 ↓ 0.0 0 1

Nested Loop (cost=1.82..1,630.39 rows=31 width=85) (actual time=22.509..22.509 rows=0 loops=1)

  • Join Filter: (ppp_12.personid = pn_12.personid)
217. 1.021 22.509 ↓ 0.0 0 1

Nested Loop (cost=1.40..1,595.51 rows=53 width=65) (actual time=22.509..22.509 rows=0 loops=1)

  • Join Filter: ((((pp_12.periodstartdate >= pfpe.effectivedate) AND (pp_12.periodstartdate <= pfpe.enddate)) OR ((pfpe.effectivedate >= pp_12.periodstartdate) AND (pfpe.effectivedate <= pp_12.periodenddate))) AND ((pfpe.createts >= pp_12.createts) OR ((pp_12.createts >= pfpe.createts) AND (pp_12.createts <= pfpe.endts) AND (pfpe.endts < CURRENT_TIMESTAMP))))
  • Rows Removed by Join Filter: 3853
218. 0.000 21.488 ↓ 5.2 3,853 1

Nested Loop (cost=0.98..1,567.14 rows=741 width=54) (actual time=0.042..21.488 rows=3,853 loops=1)

219. 1.201 1.201 ↓ 6.4 4,077 1

Index Only Scan using pk_person_pay_process on person_pay_process ppp_12 (cost=0.56..16.78 rows=641 width=17) (actual time=0.021..1.201 rows=4,077 loops=1)

  • Index Cond: (payprocessid = 324739)
  • Heap Fetches: 4077
220. 20.385 20.385 ↑ 2.0 1 4,077

Index Scan using pk_person_financial_plan_election on person_financial_plan_election pfpe (cost=0.42..2.40 rows=2 width=37) (actual time=0.004..0.005 rows=1 loops=4,077)

  • Index Cond: (personid = ppp_12.personid)
  • Filter: ((createts <> endts) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 0
221. 0.000 0.000 ↑ 1.0 1 3,853

Materialize (cost=0.42..2.44 rows=1 width=35) (actual time=0.000..0.000 rows=1 loops=3,853)

222. 0.016 0.016 ↑ 1.0 1 1

Index Scan using pk_pay_process on pay_process pp_12 (cost=0.42..2.44 rows=1 width=35) (actual time=0.016..0.016 rows=1 loops=1)

  • Index Cond: (payprocessid = 324739)
223. 0.000 0.000 ↓ 0.0 0

Index Scan using person_names_personid_idx on person_names pn_12 (cost=0.42..0.65 rows=1 width=20) (never executed)

  • Index Cond: (personid = pfpe.personid)
  • Filter: ((nametype = 'Legal'::bpchar) AND (CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
224. 0.000 0.000 ↓ 0.0 0

Index Scan using personemploymentenddate on person_employment pe_12 (cost=0.43..0.64 rows=2 width=17) (never executed)

  • Index Cond: ((personid = pn_12.personid) AND (CURRENT_DATE <= enddate) AND (CURRENT_DATE >= effectivedate))
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
225. 0.000 0.000 ↓ 0.0 0

Index Scan using person_payroll_personid_idx on person_payroll pl_12 (cost=0.42..0.47 rows=1 width=17) (never executed)

  • Index Cond: (personid = pe_12.personid)
  • Filter: ((CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
226.          

SubPlan (for Nested Loop)

227. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=2.44..2.45 rows=1 width=8) (never executed)

228. 0.000 0.000 ↓ 0.0 0

Index Scan using payprocesspayunitperiod on pay_process pp2_3 (cost=0.42..2.44 rows=1 width=8) (never executed)

  • Index Cond: ((pp_12.payunitid = payunitid) AND (pp_12.payyear = payyear) AND (pp_12.payperiod = payperiod))
229. 0.000 1.269 ↓ 0.0 0 1

Unique (cost=405.93..405.96 rows=1 width=607) (actual time=1.269..1.269 rows=0 loops=1)

230. 0.023 1.269 ↓ 0.0 0 1

Sort (cost=405.93..405.94 rows=1 width=607) (actual time=1.269..1.269 rows=0 loops=1)

  • Sort Key: ppp_13.personid, pn_13.lname, (((pte_1.createts)::date)::timestamp with time zone), pte_1.createts, (CASE WHEN (pte_1.updatets > (SubPlan 4)) THEN 'Not Processed'::text ELSE 'Processed'::text END), pe_13.companyid, pl_13.payunitid, pp_13.payperiod, pp_13.payyear, pp_13.payprocesstype
  • Sort Method: quicksort Memory: 25kB
231. 0.000 1.246 ↓ 0.0 0 1

Nested Loop (cost=375.55..405.92 rows=1 width=607) (actual time=1.246..1.246 rows=0 loops=1)

  • Join Filter: (pe_13.personid = pl_13.personid)
232. 0.001 1.246 ↓ 0.0 0 1

Nested Loop (cost=375.13..402.98 rows=1 width=98) (actual time=1.246..1.246 rows=0 loops=1)

  • Join Filter: (ppp_13.personid = pe_13.personid)
233. 0.000 1.245 ↓ 0.0 0 1

Nested Loop (cost=374.70..402.39 rows=1 width=81) (actual time=1.245..1.245 rows=0 loops=1)

  • Join Filter: (ppp_13.personid = pn_13.personid)
234. 0.010 1.245 ↓ 0.0 0 1

Nested Loop (cost=374.28..401.41 rows=2 width=61) (actual time=1.245..1.245 rows=0 loops=1)

  • Join Filter: ((pte_1.createts >= pp_13.createts) OR (pte_1.updatets >= pp_13.createts))
  • Rows Removed by Join Filter: 103
235. 0.014 0.014 ↑ 1.0 1 1

Index Scan using pk_pay_process on pay_process pp_13 (cost=0.42..2.44 rows=1 width=27) (actual time=0.013..0.014 rows=1 loops=1)

  • Index Cond: (payprocessid = 324739)
236. 0.285 1.221 ↓ 34.3 103 1

Hash Join (cost=373.86..398.93 rows=3 width=50) (actual time=0.161..1.221 rows=103 loops=1)

  • Hash Cond: (ppp_13.personid = pte_1.personid)
237. 0.818 0.818 ↓ 6.4 4,077 1

Index Only Scan using pk_person_pay_process on person_pay_process ppp_13 (cost=0.56..16.78 rows=641 width=17) (actual time=0.019..0.818 rows=4,077 loops=1)

  • Index Cond: (payprocessid = 324739)
  • Heap Fetches: 4077
238. 0.022 0.118 ↑ 2.9 103 1

Hash (cost=369.62..369.62 rows=294 width=33) (actual time=0.118..0.118 rows=103 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
239. 0.019 0.096 ↑ 2.9 103 1

Nested Loop (cost=0.85..369.62 rows=294 width=33) (actual time=0.042..0.096 rows=103 loops=1)

240. 0.019 0.019 ↓ 2.0 2 1

Index Only Scan using pk_pay_time_control_batch on pay_time_control_batch ptcb (cost=0.29..2.31 rows=1 width=8) (actual time=0.017..0.019 rows=2 loops=1)

  • Index Cond: (payprocessid = 324739)
  • Heap Fetches: 2
241. 0.058 0.058 ↑ 14.1 52 2

Index Scan using idx_persontimeexxid on person_time_ex pte_1 (cost=0.56..359.98 rows=734 width=33) (actual time=0.014..0.029 rows=52 loops=2)

  • Index Cond: (paytimecontrol = ptcb.paytimecontrol)
242. 0.000 0.000 ↓ 0.0 0

Index Scan using person_names_personid_idx on person_names pn_13 (cost=0.42..0.48 rows=1 width=20) (never executed)

  • Index Cond: (personid = pte_1.personid)
  • Filter: ((nametype = 'Legal'::bpchar) AND (CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
243. 0.000 0.000 ↓ 0.0 0

Index Scan using personemploymentenddate on person_employment pe_13 (cost=0.43..0.57 rows=2 width=17) (never executed)

  • Index Cond: ((personid = pte_1.personid) AND (CURRENT_DATE <= enddate) AND (CURRENT_DATE >= effectivedate))
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
244. 0.000 0.000 ↓ 0.0 0

Index Scan using person_payroll_personid_idx on person_payroll pl_13 (cost=0.42..0.47 rows=1 width=17) (never executed)

  • Index Cond: (personid = pte_1.personid)
  • Filter: ((CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
245.          

SubPlan (for Nested Loop)

246. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=2.44..2.45 rows=1 width=8) (never executed)

247. 0.000 0.000 ↓ 0.0 0

Index Scan using payprocesspayunitperiod on pay_process pp2_2 (cost=0.42..2.44 rows=1 width=8) (never executed)

  • Index Cond: ((pp_13.payunitid = payunitid) AND (pp_13.payyear = payyear) AND (pp_13.payperiod = payperiod))
248. 0.001 89.818 ↓ 0.0 0 1

Subquery Scan on *SELECT* 15 (cost=389.85..389.90 rows=1 width=607) (actual time=89.818..89.818 rows=0 loops=1)

249. 0.000 89.817 ↓ 0.0 0 1

Unique (cost=389.85..389.88 rows=1 width=603) (actual time=89.817..89.817 rows=0 loops=1)

250. 0.021 89.817 ↓ 0.0 0 1

Sort (cost=389.85..389.86 rows=1 width=603) (actual time=89.817..89.817 rows=0 loops=1)

  • Sort Key: pe_14.personid, pn_14.lname, pe_14.effectivedate, pe_14.createts, pe_14.endts, (CASE WHEN (pe_14.createts > (SubPlan 2)) THEN 'Not Processed'::text ELSE 'Processed'::text END), pe_14.companyid, pp_14.payunitid, pp_14.payperiod, pp_14.payyear, pp_14.payprocesstype
  • Sort Method: quicksort Memory: 25kB
251. 2.879 89.796 ↓ 0.0 0 1

Nested Loop (cost=1.68..389.84 rows=1 width=603) (actual time=89.796..89.796 rows=0 loops=1)

252. 3.274 39.307 ↓ 529.0 5,290 1

Nested Loop (cost=1.26..307.05 rows=10 width=64) (actual time=0.055..39.307 rows=5,290 loops=1)

  • Join Filter: ((pp_14.periodenddate >= pn_14.effectivedate) AND (pp_14.periodenddate <= pn_14.enddate))
  • Rows Removed by Join Filter: 655
253. 0.536 4.125 ↓ 45.1 5,318 1

Nested Loop (cost=0.84..242.23 rows=118 width=44) (actual time=0.032..4.125 rows=5,318 loops=1)

254. 0.009 0.009 ↑ 1.0 1 1

Index Scan using pk_pay_process on pay_process pp_14 (cost=0.42..2.44 rows=1 width=31) (actual time=0.007..0.009 rows=1 loops=1)

  • Index Cond: (payprocessid = 324739)
255. 3.580 3.580 ↓ 123.7 5,318 1

Index Scan using person_payroll_payunitid_idx on person_payroll pl_14 (cost=0.42..239.36 rows=43 width=25) (actual time=0.022..3.580 rows=5,318 loops=1)

  • Index Cond: (payunitid = pp_14.payunitid)
  • Filter: ((payunitrelationship = 'M'::bpchar) AND (pp_14.periodenddate >= effectivedate) AND (pp_14.periodenddate <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 400
256. 31.908 31.908 ↑ 1.0 1 5,318

Index Scan using person_names_personid_idx on person_names pn_14 (cost=0.42..0.53 rows=1 width=28) (actual time=0.006..0.006 rows=1 loops=5,318)

  • Index Cond: (personid = pl_14.personid)
  • Filter: ((nametype = 'Legal'::bpchar) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 0
257. 47.576 47.610 ↓ 0.0 0 5,290

Index Scan using personemploymentenddate on person_employment pe_14 (cost=0.42..8.02 rows=1 width=37) (actual time=0.009..0.009 rows=0 loops=5,290)

  • Index Cond: ((personid = pn_14.personid) AND (effectivedate <= pp_14.periodenddate))
  • Filter: ((effectivedate <= enddate) AND (createts > pp_14.createts) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts) AND (createts = (SubPlan 3)))
  • Rows Removed by Filter: 5
258.          

SubPlan (for Index Scan)

259. 0.009 0.034 ↑ 1.0 1 1

Aggregate (cost=5.52..5.53 rows=1 width=8) (actual time=0.034..0.034 rows=1 loops=1)

260. 0.025 0.025 ↓ 4.0 4 1

Index Scan using person_employment_personid_idx on person_employment pe2 (cost=0.42..5.52 rows=1 width=8) (actual time=0.019..0.025 rows=4 loops=1)

  • Index Cond: (personid = pe_14.personid)
  • Filter: ((effectivedate <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 5
261.          

SubPlan (for Nested Loop)

262. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=2.44..2.45 rows=1 width=8) (never executed)

263. 0.000 0.000 ↓ 0.0 0

Index Scan using payprocesspayunitperiod on pay_process pp2_1 (cost=0.42..2.44 rows=1 width=8) (never executed)

  • Index Cond: ((pp_14.payunitid = payunitid) AND (pp_14.payyear = payyear) AND (pp_14.payperiod = payperiod))
264. 0.001 57.104 ↓ 0.0 0 1

Subquery Scan on *SELECT* 16 (cost=1,802.75..1,804.66 rows=45 width=587) (actual time=57.104..57.104 rows=0 loops=1)

265. 0.001 57.103 ↓ 0.0 0 1

Unique (cost=1,802.75..1,804.10 rows=45 width=583) (actual time=57.103..57.103 rows=0 loops=1)

266. 0.025 57.102 ↓ 0.0 0 1

Sort (cost=1,802.75..1,802.86 rows=45 width=583) (actual time=57.102..57.102 rows=0 loops=1)

  • Sort Key: ppp_14.personid, pn_15.lname, pl_15.effectivedate, pl_15.createts, pl_15.endts, (CASE WHEN (pl_15.createts > (SubPlan 1)) THEN 'Not Processed'::text ELSE 'Processed'::text END), ppay.companyid, ppay.payunitid, pp_15.payperiod, pp_15.payyear, pp_15.payprocesstype
  • Sort Method: quicksort Memory: 25kB
267. 0.000 57.077 ↓ 0.0 0 1

Nested Loop (cost=2.24..1,801.51 rows=45 width=583) (actual time=57.077..57.077 rows=0 loops=1)

  • Join Filter: (ppp_14.personid = ppay.personid)
268. 2.822 57.077 ↓ 0.0 0 1

Nested Loop (cost=1.82..1,674.51 rows=34 width=85) (actual time=57.077..57.077 rows=0 loops=1)

  • Join Filter: ((pl_15.createts >= pp_15.createts) AND (ppp_14.personid = pl_15.personid) AND (((pp_15.periodstartdate >= pl_15.effectivedate) AND (pp_15.periodstartdate <= pl_15.enddate)) OR ((pl_15.effectivedate >= pp_15.periodstartdate) AND (pl_15.effectivedate <= pp_15.periodenddate))))
  • Rows Removed by Join Filter: 7043
269. 1.168 29.793 ↓ 10.8 4,077 1

Nested Loop (cost=1.40..1,471.79 rows=376 width=68) (actual time=0.066..29.793 rows=4,077 loops=1)

270. 2.681 28.625 ↓ 10.8 4,077 1

Nested Loop (cost=0.99..1,464.65 rows=376 width=37) (actual time=0.046..28.625 rows=4,077 loops=1)

271. 1.482 1.482 ↓ 6.4 4,077 1

Index Only Scan using pk_person_pay_process on person_pay_process ppp_14 (cost=0.56..16.78 rows=641 width=17) (actual time=0.027..1.482 rows=4,077 loops=1)

  • Index Cond: (payprocessid = 324739)
  • Heap Fetches: 4077
272. 24.462 24.462 ↑ 1.0 1 4,077

Index Scan using person_names_personid_idx on person_names pn_15 (cost=0.42..2.25 rows=1 width=20) (actual time=0.006..0.006 rows=1 loops=4,077)

  • Index Cond: (personid = ppp_14.personid)
  • Filter: ((nametype = 'Legal'::bpchar) AND (CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 0
273. 0.000 0.000 ↑ 1.0 1 4,077

Materialize (cost=0.42..2.44 rows=1 width=35) (actual time=0.000..0.000 rows=1 loops=4,077)

274. 0.014 0.014 ↑ 1.0 1 1

Index Scan using pk_pay_process on pay_process pp_15 (cost=0.42..2.44 rows=1 width=35) (actual time=0.014..0.014 rows=1 loops=1)

  • Index Cond: (payprocessid = 324739)
275. 24.462 24.462 ↓ 2.0 2 4,077

Index Scan using person_locations_personid_idx on person_locations pl_15 (cost=0.42..0.51 rows=1 width=37) (actual time=0.006..0.006 rows=2 loops=4,077)

  • Index Cond: (personid = pn_15.personid)
  • Filter: ((personlocationtype = ANY ('{P,TL}'::bpchar[])) AND (createts <> endts) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 0
276. 0.000 0.000 ↓ 0.0 0

Index Scan using person_payroll_personid_idx on person_payroll ppay (cost=0.42..0.47 rows=1 width=21) (never executed)

  • Index Cond: (personid = pl_15.personid)
  • Filter: ((CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
277.          

SubPlan (for Nested Loop)

278. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=2.44..2.45 rows=1 width=8) (never executed)

279. 0.000 0.000 ↓ 0.0 0

Index Scan using payprocesspayunitperiod on pay_process pp2 (cost=0.42..2.44 rows=1 width=8) (never executed)

  • Index Cond: ((pp_15.payunitid = payunitid) AND (pp_15.payyear = payyear) AND (pp_15.payperiod = payperiod))
Planning time : 4,853.086 ms
Execution time : 1,183.614 ms