explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tP8Y : PayPRocessChanges

Settings
# exclusive inclusive rows x rows loops node
1. 0.001 81.225 ↑ 5,546.0 2 1

Append (cost=335.89..30,875.30 rows=11,092 width=674) (actual time=7.383..81.225 rows=2 loops=1)

2. 0.003 73.233 ↑ 11,075.0 1 1

Result (cost=335.89..30,436.24 rows=11,075 width=674) (actual time=7.383..73.233 rows=1 loops=1)

3. 0.000 73.230 ↑ 11,075.0 1 1

Append (cost=335.89..30,297.80 rows=11,075 width=670) (actual time=7.381..73.230 rows=1 loops=1)

4. 0.001 7.381 ↑ 11.0 1 1

Unique (cost=335.89..336.22 rows=11 width=583) (actual time=7.380..7.381 rows=1 loops=1)

5. 0.013 7.380 ↑ 11.0 1 1

Sort (cost=335.89..335.92 rows=11 width=583) (actual time=7.380..7.380 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.005 7.367 ↑ 11.0 1 1

Nested Loop (cost=1.58..335.70 rows=11 width=583) (actual time=1.347..7.367 rows=1 loops=1)

  • Join Filter: (pl.personid = pn.personid)
7. 0.313 7.332 ↑ 11.0 1 1

Nested Loop (cost=1.29..305.92 rows=11 width=86) (actual time=1.312..7.332 rows=1 loops=1)

  • Join Filter: ((((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: 825
8. 0.307 3.903 ↓ 7.8 779 1

Nested Loop (cost=1.00..262.69 rows=100 width=65) (actual time=0.040..3.903 rows=779 loops=1)

9. 0.300 3.596 ↓ 7.8 779 1

Nested Loop (cost=0.71..259.14 rows=100 width=34) (actual time=0.032..3.596 rows=779 loops=1)

10. 0.180 0.180 ↓ 7.1 779 1

Index Only Scan using person_pay_process_pkey on person_pay_process ppp (cost=0.43..53.62 rows=109 width=17) (actual time=0.017..0.180 rows=779 loops=1)

  • Index Cond: (payprocessid = 41095)
  • Heap Fetches: 779
11. 3.116 3.116 ↑ 1.0 1 779

Index Scan using person_payroll_personid_idx on person_payroll pl (cost=0.29..1.88 rows=1 width=17) (actual time=0.004..0.004 rows=1 loops=779)

  • Index Cond: (personid = ppp.personid)
  • Filter: ((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 779

Materialize (cost=0.29..2.31 rows=1 width=35) (actual time=0.000..0.000 rows=1 loops=779)

13. 0.005 0.005 ↑ 1.0 1 1

Index Scan using pay_process_pkey on pay_process pp (cost=0.29..2.30 rows=1 width=35) (actual time=0.005..0.005 rows=1 loops=1)

  • Index Cond: (payprocessid = 41095)
14. 3.116 3.116 ↑ 1.0 1 779

Index Scan using person_employment_personid_idx on person_employment pe (cost=0.29..0.40 rows=1 width=41) (actual time=0.004..0.004 rows=1 loops=779)

  • Index Cond: (personid = pl.personid)
  • Filter: ((createts <> endts) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 0
15. 0.008 0.008 ↑ 1.0 1 1

Index Scan using person_names_personid_idx on person_names pn (cost=0.29..0.37 rows=1 width=20) (actual time=0.008..0.008 rows=1 loops=1)

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

SubPlan (for Nested Loop)

17. 0.005 0.022 ↑ 1.0 1 1

Aggregate (cost=2.31..2.32 rows=1 width=8) (actual time=0.022..0.022 rows=1 loops=1)

18. 0.017 0.017 ↓ 22.0 22 1

Index Scan using payprocesspayunitperiod on pay_process pp2_15 (cost=0.29..2.31 rows=1 width=8) (actual time=0.006..0.017 rows=22 loops=1)

  • Index Cond: ((pp.payunitid = payunitid) AND (pp.payyear = payyear) AND (pp.payperiod = payperiod))
19. 0.001 4.226 ↓ 0.0 0 1

Unique (cost=507.74..510.83 rows=103 width=583) (actual time=4.226..4.226 rows=0 loops=1)

20. 0.009 4.225 ↓ 0.0 0 1

Sort (cost=507.74..508.00 rows=103 width=583) (actual time=4.225..4.225 rows=0 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: 25kB
21. 0.000 4.216 ↓ 0.0 0 1

Nested Loop (cost=1.86..504.30 rows=103 width=583) (actual time=4.216..4.216 rows=0 loops=1)

22. 0.000 4.216 ↓ 0.0 0 1

Nested Loop (cost=1.57..264.30 rows=2 width=99) (actual time=4.216..4.216 rows=0 loops=1)

23. 0.000 4.216 ↓ 0.0 0 1

Nested Loop (cost=1.28..263.59 rows=2 width=82) (actual time=4.216..4.216 rows=0 loops=1)

  • Join Filter: (ppp_1.personid = pe_1.personid)
24. 0.572 4.216 ↓ 0.0 0 1

Nested Loop (cost=0.99..261.92 rows=3 width=45) (actual time=4.216..4.216 rows=0 loops=1)

  • Join Filter: ((((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: 1659
25. 0.380 3.644 ↓ 42.5 1,659 1

Nested Loop (cost=0.71..258.15 rows=39 width=54) (actual time=0.075..3.644 rows=1,659 loops=1)

26. 0.148 0.148 ↓ 7.1 779 1

Index Only Scan using person_pay_process_pkey on person_pay_process ppp_1 (cost=0.43..53.62 rows=109 width=17) (actual time=0.010..0.148 rows=779 loops=1)

  • Index Cond: (payprocessid = 41095)
  • Heap Fetches: 779
27. 3.116 3.116 ↑ 1.5 2 779

Index Scan using person_direct_deposits_pkey on person_direct_deposits pdd (cost=0.28..1.85 rows=3 width=37) (actual time=0.003..0.004 rows=2 loops=779)

  • Index Cond: (personid = ppp_1.personid)
  • Filter: (createts <> endts)
28. 0.000 0.000 ↑ 1.0 1 1,659

Materialize (cost=0.29..2.31 rows=1 width=39) (actual time=0.000..0.000 rows=1 loops=1,659)

29. 0.005 0.005 ↑ 1.0 1 1

Index Scan using pay_process_pkey on pay_process pp_1 (cost=0.29..2.30 rows=1 width=39) (actual time=0.005..0.005 rows=1 loops=1)

  • Index Cond: (payprocessid = 41095)
30. 0.000 0.000 ↓ 0.0 0

Index Scan using person_employment_personid_idx on person_employment pe_1 (cost=0.29..0.54 rows=1 width=37) (never executed)

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

Index Scan using person_payroll_personid_idx on person_payroll pl_1 (cost=0.29..0.34 rows=1 width=17) (never executed)

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

Index Scan using person_names_personid_idx on person_names pn_1 (cost=0.29..0.38 rows=1 width=20) (never executed)

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

SubPlan (for Nested Loop)

34. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=2.31..2.32 rows=1 width=8) (never executed)

35. 0.000 0.000 ↓ 0.0 0

Index Scan using payprocesspayunitperiod on pay_process pp2_14 (cost=0.29..2.31 rows=1 width=8) (never executed)

  • Index Cond: ((pp_1.payunitid = payunitid) AND (pp_1.payyear = payyear) AND (pp_1.payperiod = payperiod))
36. 0.000 4.180 ↓ 0.0 0 1

Unique (cost=1,684.74..1,702.20 rows=582 width=583) (actual time=4.180..4.180 rows=0 loops=1)

37. 0.007 4.180 ↓ 0.0 0 1

Sort (cost=1,684.74..1,686.19 rows=582 width=583) (actual time=4.180..4.180 rows=0 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.000 4.173 ↓ 0.0 0 1

Nested Loop (cost=2.00..1,658.01 rows=582 width=583) (actual time=4.173..4.173 rows=0 loops=1)

  • Join Filter: (ppp_2.personid = pe_2.personid)
39. 0.000 4.173 ↓ 0.0 0 1

Nested Loop (cost=1.71..303.37 rows=7 width=102) (actual time=4.173..4.173 rows=0 loops=1)

  • Join Filter: (ppp_2.personid = pl_2.personid)
40. 0.000 4.173 ↓ 0.0 0 1

Nested Loop (cost=1.42..300.88 rows=7 width=85) (actual time=4.173..4.173 rows=0 loops=1)

  • Join Filter: (ppp_2.personid = pn_2.personid)
41. 0.656 4.173 ↓ 0.0 0 1

Nested Loop (cost=1.13..297.34 rows=9 width=65) (actual time=4.172..4.173 rows=0 loops=1)

  • Join Filter: ((((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: 1560
42. 0.257 0.401 ↓ 7.1 779 1

Nested Loop (cost=0.71..57.29 rows=109 width=48) (actual time=0.014..0.401 rows=779 loops=1)

43. 0.144 0.144 ↓ 7.1 779 1

Index Only Scan using person_pay_process_pkey on person_pay_process ppp_2 (cost=0.43..53.62 rows=109 width=17) (actual time=0.009..0.144 rows=779 loops=1)

  • Index Cond: (payprocessid = 41095)
  • Heap Fetches: 779
44. 0.000 0.000 ↑ 1.0 1 779

Materialize (cost=0.29..2.31 rows=1 width=35) (actual time=0.000..0.000 rows=1 loops=779)

45. 0.004 0.004 ↑ 1.0 1 1

Index Scan using pay_process_pkey on pay_process pp_2 (cost=0.29..2.30 rows=1 width=35) (actual time=0.004..0.004 rows=1 loops=1)

  • Index Cond: (payprocessid = 41095)
46. 3.116 3.116 ↓ 2.0 2 779

Index Scan using person_compensation_pkey on person_compensation pc (cost=0.41..2.17 rows=1 width=37) (actual time=0.004..0.004 rows=2 loops=779)

  • Index Cond: (personid = ppp_2.personid)
  • Filter: ((createts <> endts) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 0
47. 0.000 0.000 ↓ 0.0 0

Index Scan using person_names_personid_idx on person_names pn_2 (cost=0.29..0.38 rows=1 width=20) (never executed)

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

Index Scan using person_payroll_personid_idx on person_payroll pl_2 (cost=0.29..0.34 rows=1 width=17) (never executed)

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

Index Scan using person_employment_personid_idx on person_employment pe_2 (cost=0.29..0.41 rows=1 width=17) (never executed)

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

SubPlan (for Nested Loop)

51. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=2.31..2.32 rows=1 width=8) (never executed)

52. 0.000 0.000 ↓ 0.0 0

Index Scan using payprocesspayunitperiod on pay_process pp2_13 (cost=0.29..2.31 rows=1 width=8) (never executed)

  • Index Cond: ((pp_2.payunitid = payunitid) AND (pp_2.payyear = payyear) AND (pp_2.payperiod = payperiod))
53. 0.001 3.626 ↓ 0.0 0 1

Unique (cost=1,746.15..1,764.72 rows=619 width=583) (actual time=3.626..3.626 rows=0 loops=1)

54. 0.006 3.625 ↓ 0.0 0 1

Sort (cost=1,746.15..1,747.70 rows=619 width=583) (actual time=3.625..3.625 rows=0 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: 25kB
55. 0.000 3.619 ↓ 0.0 0 1

Nested Loop (cost=1.87..1,717.45 rows=619 width=583) (actual time=3.619..3.619 rows=0 loops=1)

  • Join Filter: (ppp_3.personid = pe_3.personid)
56. 0.000 3.619 ↓ 0.0 0 1

Nested Loop (cost=1.58..277.72 rows=5 width=102) (actual time=3.619..3.619 rows=0 loops=1)

  • Join Filter: (ppp_3.personid = pl_3.personid)
57. 0.001 3.619 ↓ 0.0 0 1

Nested Loop (cost=1.29..275.94 rows=5 width=85) (actual time=3.619..3.619 rows=0 loops=1)

  • Join Filter: (ppp_3.personid = pn_3.personid)
58. 0.275 3.618 ↓ 0.0 0 1

Nested Loop (cost=1.00..273.15 rows=7 width=65) (actual time=3.618..3.618 rows=0 loops=1)

  • Join Filter: ((((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: 791
59. 0.083 3.343 ↓ 8.5 791 1

Nested Loop (cost=0.72..267.59 rows=93 width=54) (actual time=0.021..3.343 rows=791 loops=1)

60. 0.144 0.144 ↓ 7.1 779 1

Index Only Scan using person_pay_process_pkey on person_pay_process ppp_3 (cost=0.43..53.62 rows=109 width=17) (actual time=0.010..0.144 rows=779 loops=1)

  • Index Cond: (payprocessid = 41095)
  • Heap Fetches: 779
61. 3.116 3.116 ↑ 1.0 1 779

Index Scan using pers_pos_personid_idx on pers_pos persp (cost=0.29..1.95 rows=1 width=37) (actual time=0.004..0.004 rows=1 loops=779)

  • Index Cond: (personid = ppp_3.personid)
  • Filter: ((createts <> endts) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 0
62. 0.000 0.000 ↑ 1.0 1 791

Materialize (cost=0.29..2.31 rows=1 width=35) (actual time=0.000..0.000 rows=1 loops=791)

63. 0.003 0.003 ↑ 1.0 1 1

Index Scan using pay_process_pkey on pay_process pp_3 (cost=0.29..2.30 rows=1 width=35) (actual time=0.003..0.003 rows=1 loops=1)

  • Index Cond: (payprocessid = 41095)
64. 0.000 0.000 ↓ 0.0 0

Index Scan using person_names_personid_idx on person_names pn_3 (cost=0.29..0.39 rows=1 width=20) (never executed)

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

Index Scan using person_payroll_personid_idx on person_payroll pl_3 (cost=0.29..0.34 rows=1 width=17) (never executed)

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

Index Scan using person_employment_personid_idx on person_employment pe_3 (cost=0.29..0.41 rows=1 width=17) (never executed)

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

SubPlan (for Nested Loop)

68. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=2.31..2.32 rows=1 width=8) (never executed)

69. 0.000 0.000 ↓ 0.0 0

Index Scan using payprocesspayunitperiod on pay_process pp2_12 (cost=0.29..2.31 rows=1 width=8) (never executed)

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

Unique (cost=555.47..559.01 rows=118 width=583) (actual time=3.395..3.395 rows=0 loops=1)

71. 0.006 3.394 ↓ 0.0 0 1

Sort (cost=555.47..555.77 rows=118 width=583) (actual time=3.394..3.394 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 3.388 ↓ 0.0 0 1

Nested Loop (cost=2.16..551.41 rows=118 width=583) (actual time=3.388..3.388 rows=0 loops=1)

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

Nested Loop (cost=1.87..276.95 rows=1 width=102) (actual time=3.387..3.387 rows=0 loops=1)

  • Join Filter: (ppp_4.personid = pn_4.personid)
74. 0.000 3.387 ↓ 0.0 0 1

Nested Loop (cost=1.58..276.57 rows=1 width=82) (actual time=3.387..3.387 rows=0 loops=1)

  • Join Filter: (ppp_4.personid = pl_4.personid)
75. 0.000 3.387 ↓ 0.0 0 1

Nested Loop (cost=1.29..276.21 rows=1 width=65) (actual time=3.387..3.387 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))))
76. 0.000 3.387 ↓ 0.0 0 1

Nested Loop (cost=1.00..270.44 rows=15 width=65) (actual time=3.387..3.387 rows=0 loops=1)

  • Join Filter: ((persp_1.enddate <= pp_4.periodenddate) AND (pp_4.periodstartdate <= persp_1.effectivedate))
  • Rows Removed by Join Filter: 809
77. 0.249 0.385 ↓ 7.1 779 1

Nested Loop (cost=0.71..57.29 rows=109 width=48) (actual time=0.013..0.385 rows=779 loops=1)

78. 0.136 0.136 ↓ 7.1 779 1

Index Only Scan using person_pay_process_pkey on person_pay_process ppp_4 (cost=0.43..53.62 rows=109 width=17) (actual time=0.008..0.136 rows=779 loops=1)

  • Index Cond: (payprocessid = 41095)
  • Heap Fetches: 779
79. 0.000 0.000 ↑ 1.0 1 779

Materialize (cost=0.29..2.31 rows=1 width=35) (actual time=0.000..0.000 rows=1 loops=779)

80. 0.003 0.003 ↑ 1.0 1 1

Index Scan using pay_process_pkey on pay_process pp_4 (cost=0.29..2.30 rows=1 width=35) (actual time=0.003..0.003 rows=1 loops=1)

  • Index Cond: (payprocessid = 41095)
81. 3.116 3.116 ↑ 1.0 1 779

Index Scan using pers_pos_personid_idx on pers_pos persp_1 (cost=0.29..1.94 rows=1 width=25) (actual time=0.003..0.004 rows=1 loops=779)

  • Index Cond: (personid = ppp_4.personid)
82. 0.000 0.000 ↓ 0.0 0

Index Scan using position_desc_positionid_idx on position_desc pd (cost=0.29..0.35 rows=1 width=28) (never executed)

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

Index Scan using person_payroll_personid_idx on person_payroll pl_4 (cost=0.29..0.34 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))
84. 0.000 0.000 ↓ 0.0 0

Index Scan using person_names_personid_idx on person_names pn_4 (cost=0.29..0.37 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))
85. 0.000 0.000 ↓ 0.0 0

Index Scan using person_employment_personid_idx on person_employment pe_4 (cost=0.29..0.40 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))
86.          

SubPlan (for Nested Loop)

87. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=2.31..2.32 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.29..2.31 rows=1 width=8) (never executed)

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

Unique (cost=406.17..408.21 rows=68 width=583) (actual time=4.081..4.081 rows=0 loops=1)

90. 0.004 4.080 ↓ 0.0 0 1

Sort (cost=406.17..406.34 rows=68 width=583) (actual time=4.080..4.080 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 4.076 ↓ 0.0 0 1

Nested Loop (cost=1.86..404.10 rows=68 width=583) (actual time=4.075..4.076 rows=0 loops=1)

92. 0.000 4.075 ↓ 0.0 0 1

Nested Loop (cost=1.57..245.78 rows=1 width=99) (actual time=4.075..4.075 rows=0 loops=1)

93. 0.001 4.075 ↓ 0.0 0 1

Nested Loop (cost=1.28..245.43 rows=1 width=82) (actual time=4.075..4.075 rows=0 loops=1)

  • Join Filter: (ppp_5.personid = pe_5.personid)
94. 0.532 4.074 ↓ 0.0 0 1

Nested Loop (cost=0.99..244.13 rows=2 width=65) (actual time=4.074..4.074 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: 1608
95. 0.288 3.542 ↓ 51.9 1,608 1

Nested Loop (cost=0.71..240.74 rows=31 width=54) (actual time=0.031..3.542 rows=1,608 loops=1)

96. 0.138 0.138 ↓ 7.1 779 1

Index Only Scan using person_pay_process_pkey on person_pay_process ppp_5 (cost=0.43..53.62 rows=109 width=17) (actual time=0.007..0.138 rows=779 loops=1)

  • Index Cond: (payprocessid = 41095)
  • Heap Fetches: 779
97. 3.116 3.116 ↑ 1.0 2 779

Index Scan using person_misc_deduction_pkey on person_misc_deduction pmd (cost=0.28..1.70 rows=2 width=37) (actual time=0.003..0.004 rows=2 loops=779)

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

Materialize (cost=0.29..2.31 rows=1 width=35) (actual time=0.000..0.000 rows=1 loops=1,608)

99. 0.003 0.003 ↑ 1.0 1 1

Index Scan using pay_process_pkey on pay_process pp_5 (cost=0.29..2.30 rows=1 width=35) (actual time=0.003..0.003 rows=1 loops=1)

  • Index Cond: (payprocessid = 41095)
100. 0.000 0.000 ↓ 0.0 0

Index Scan using person_employment_personid_idx on person_employment pe_5 (cost=0.29..0.64 rows=1 width=17) (never executed)

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

Index Scan using person_payroll_personid_idx on person_payroll pl_5 (cost=0.29..0.34 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))
102. 0.000 0.000 ↓ 0.0 0

Index Scan using person_names_personid_idx on person_names pn_5 (cost=0.29..0.38 rows=1 width=20) (never executed)

  • Index Cond: (personid = pe_5.personid)
  • Filter: ((nametype = 'Legal'::bpchar) AND (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.31..2.32 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.29..2.31 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 10.357 ↓ 0.0 0 1

Unique (cost=620.91..624.69 rows=126 width=583) (actual time=10.356..10.357 rows=0 loops=1)

107. 0.006 10.356 ↓ 0.0 0 1

Sort (cost=620.91..621.23 rows=126 width=583) (actual time=10.356..10.356 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.000 10.350 ↓ 0.0 0 1

Nested Loop (cost=1.87..616.52 rows=126 width=583) (actual time=10.350..10.350 rows=0 loops=1)

109. 0.000 10.350 ↓ 0.0 0 1

Nested Loop (cost=1.58..322.71 rows=3 width=99) (actual time=10.350..10.350 rows=0 loops=1)

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

Nested Loop (cost=1.29..308.61 rows=33 width=82) (actual time=10.350..10.350 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: 7543
111. 0.300 3.943 ↓ 7.8 779 1

Nested Loop (cost=1.00..262.69 rows=100 width=65) (actual time=0.022..3.943 rows=779 loops=1)

112. 0.363 3.643 ↓ 7.8 779 1

Nested Loop (cost=0.71..259.14 rows=100 width=34) (actual time=0.018..3.643 rows=779 loops=1)

113. 0.164 0.164 ↓ 7.1 779 1

Index Only Scan using person_pay_process_pkey on person_pay_process ppp_6 (cost=0.43..53.62 rows=109 width=17) (actual time=0.008..0.164 rows=779 loops=1)

  • Index Cond: (payprocessid = 41095)
  • Heap Fetches: 779
114. 3.116 3.116 ↑ 1.0 1 779

Index Scan using person_payroll_personid_idx on person_payroll pl_6 (cost=0.29..1.88 rows=1 width=17) (actual time=0.004..0.004 rows=1 loops=779)

  • Index Cond: (personid = ppp_6.personid)
  • Filter: ((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 779

Materialize (cost=0.29..2.31 rows=1 width=35) (actual time=0.000..0.000 rows=1 loops=779)

116. 0.003 0.003 ↑ 1.0 1 1

Index Scan using pay_process_pkey on pay_process pp_6 (cost=0.29..2.30 rows=1 width=35) (actual time=0.003..0.003 rows=1 loops=1)

  • Index Cond: (payprocessid = 41095)
117. 5.453 5.453 ↓ 3.3 10 779

Index Scan using personptoplansx on person_pto_plans pptop (cost=0.29..0.35 rows=3 width=37) (actual time=0.004..0.007 rows=10 loops=779)

  • Index Cond: (personid = pl_6.personid)
  • Filter: (createts <> endts)
  • Rows Removed by Filter: 0
118. 0.000 0.000 ↓ 0.0 0

Index Scan using person_employment_personid_idx on person_employment pe_6 (cost=0.29..0.41 rows=1 width=17) (never executed)

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

Index Scan using person_names_personid_idx on person_names pn_6 (cost=0.29..0.38 rows=1 width=20) (never executed)

  • Index Cond: (personid = pe_6.personid)
  • Filter: ((nametype = 'Legal'::bpchar) AND (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.31..2.32 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.29..2.31 rows=1 width=8) (never executed)

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

Unique (cost=17.84..17.87 rows=1 width=583) (actual time=0.014..0.014 rows=0 loops=1)

124. 0.006 0.013 ↓ 0.0 0 1

Sort (cost=17.84..17.84 rows=1 width=583) (actual time=0.013..0.013 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.000 0.007 ↓ 0.0 0 1

Nested Loop (cost=5.33..17.83 rows=1 width=583) (actual time=0.007..0.007 rows=0 loops=1)

126. 0.001 0.007 ↓ 0.0 0 1

Nested Loop (cost=5.05..15.15 rows=1 width=102) (actual time=0.006..0.007 rows=0 loops=1)

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

Nested Loop (cost=4.76..14.73 rows=1 width=85) (actual time=0.006..0.006 rows=0 loops=1)

128. 0.000 0.006 ↓ 0.0 0 1

Nested Loop (cost=4.33..13.21 rows=1 width=72) (actual time=0.006..0.006 rows=0 loops=1)

129. 0.000 0.006 ↓ 0.0 0 1

Nested Loop (cost=4.04..10.87 rows=1 width=52) (actual time=0.006..0.006 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)))
130. 0.001 0.006 ↓ 0.0 0 1

Hash Join (cost=3.75..8.54 rows=1 width=37) (actual time=0.005..0.006 rows=0 loops=1)

  • Hash Cond: (persp_2.edtcode = e.edtcode)
131. 0.005 0.005 ↓ 0.0 0 1

Index Scan using person_payments_pkey on person_payments persp_2 (cost=0.42..5.19 rows=7 width=48) (actual time=0.005..0.005 rows=0 loops=1)

  • Index Cond: (payprocessid = 41095)
  • Filter: (createts <> endts)
132. 0.000 0.000 ↓ 0.0 0

Hash (cost=3.31..3.31 rows=2 width=44) (never executed)

133. 0.000 0.000 ↓ 0.0 0

Index Scan using edtdesc on edt_codes e (cost=0.27..3.31 rows=2 width=44) (never executed)

  • Index Cond: (edttype = 'E'::bpchar)
134. 0.000 0.000 ↓ 0.0 0

Index Scan using pay_process_pkey on pay_process pp_7 (cost=0.29..2.30 rows=1 width=27) (never executed)

  • Index Cond: (payprocessid = 41095)
135. 0.000 0.000 ↓ 0.0 0

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

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

Index Only Scan using person_pay_process_pkey on person_pay_process ppp_7 (cost=0.43..1.50 rows=1 width=17) (never executed)

  • Index Cond: ((payprocessid = 41095) AND (personid = pn_7.personid))
  • Heap Fetches: 0
137. 0.000 0.000 ↓ 0.0 0

Index Scan using person_employment_personid_idx on person_employment pe_7 (cost=0.29..0.41 rows=1 width=17) (never executed)

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

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

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

SubPlan (for Nested Loop)

140. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=2.31..2.32 rows=1 width=8) (never executed)

141. 0.000 0.000 ↓ 0.0 0

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

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

Unique (cost=17.84..17.87 rows=1 width=583) (actual time=0.008..0.008 rows=0 loops=1)

143. 0.005 0.008 ↓ 0.0 0 1

Sort (cost=17.84..17.84 rows=1 width=583) (actual time=0.008..0.008 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
144. 0.000 0.003 ↓ 0.0 0 1

Nested Loop (cost=5.33..17.83 rows=1 width=583) (actual time=0.003..0.003 rows=0 loops=1)

145. 0.000 0.003 ↓ 0.0 0 1

Nested Loop (cost=5.05..15.15 rows=1 width=102) (actual time=0.003..0.003 rows=0 loops=1)

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

Nested Loop (cost=4.76..14.73 rows=1 width=85) (actual time=0.003..0.003 rows=0 loops=1)

147. 0.000 0.002 ↓ 0.0 0 1

Nested Loop (cost=4.33..13.21 rows=1 width=72) (actual time=0.002..0.002 rows=0 loops=1)

148. 0.000 0.002 ↓ 0.0 0 1

Nested Loop (cost=4.04..10.87 rows=1 width=52) (actual time=0.002..0.002 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)))
149. 0.000 0.002 ↓ 0.0 0 1

Hash Join (cost=3.75..8.54 rows=1 width=37) (actual time=0.002..0.002 rows=0 loops=1)

  • Hash Cond: (persp_3.edtcode = e_1.edtcode)
150. 0.002 0.002 ↓ 0.0 0 1

Index Scan using person_payments_pkey on person_payments persp_3 (cost=0.42..5.19 rows=7 width=48) (actual time=0.002..0.002 rows=0 loops=1)

  • Index Cond: (payprocessid = 41095)
  • Filter: (createts <> endts)
151. 0.000 0.000 ↓ 0.0 0

Hash (cost=3.31..3.31 rows=2 width=44) (never executed)

152. 0.000 0.000 ↓ 0.0 0

Index Scan using edtdesc on edt_codes e_1 (cost=0.27..3.31 rows=2 width=44) (never executed)

  • Index Cond: (edttype = 'D'::bpchar)
153. 0.000 0.000 ↓ 0.0 0

Index Scan using pay_process_pkey on pay_process pp_8 (cost=0.29..2.30 rows=1 width=27) (never executed)

  • Index Cond: (payprocessid = 41095)
154. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (personid = persp_3.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 Only Scan using person_pay_process_pkey on person_pay_process ppp_8 (cost=0.43..1.50 rows=1 width=17) (never executed)

  • Index Cond: ((payprocessid = 41095) AND (personid = pn_8.personid))
  • Heap Fetches: 0
156. 0.000 0.000 ↓ 0.0 0

Index Scan using person_employment_personid_idx on person_employment pe_8 (cost=0.29..0.41 rows=1 width=17) (never executed)

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

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

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

SubPlan (for Nested Loop)

159. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=2.31..2.32 rows=1 width=8) (never executed)

160. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: ((pp_8.payunitid = payunitid) AND (pp_8.payyear = payyear) AND (pp_8.payperiod = payperiod))
161. 0.000 17.560 ↓ 0.0 0 1

Unique (cost=14,419.08..14,593.35 rows=5,809 width=583) (actual time=17.560..17.560 rows=0 loops=1)

162. 0.005 17.560 ↓ 0.0 0 1

Sort (cost=14,419.08..14,433.60 rows=5,809 width=583) (actual time=17.560..17.560 rows=0 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: 25kB
163. 0.000 17.555 ↓ 0.0 0 1

Nested Loop (cost=2.01..14,055.90 rows=5,809 width=583) (actual time=17.555..17.555 rows=0 loops=1)

  • Join Filter: (ppp_9.personid = pe_9.personid)
164. 1.120 17.555 ↓ 0.0 0 1

Nested Loop (cost=1.72..529.85 rows=91 width=102) (actual time=17.555..17.555 rows=0 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: 9740
165. 0.305 7.866 ↓ 10.4 779 1

Nested Loop (cost=1.29..302.54 rows=75 width=85) (actual time=0.033..7.866 rows=779 loops=1)

166. 0.025 7.561 ↓ 10.4 779 1

Nested Loop (cost=1.00..299.30 rows=75 width=54) (actual time=0.027..7.561 rows=779 loops=1)

  • Join Filter: (ppp_9.personid = pn_9.personid)
167. 0.354 3.641 ↓ 7.8 779 1

Nested Loop (cost=0.71..259.14 rows=100 width=34) (actual time=0.017..3.641 rows=779 loops=1)

168. 0.171 0.171 ↓ 7.1 779 1

Index Only Scan using person_pay_process_pkey on person_pay_process ppp_9 (cost=0.43..53.62 rows=109 width=17) (actual time=0.008..0.171 rows=779 loops=1)

  • Index Cond: (payprocessid = 41095)
  • Heap Fetches: 779
169. 3.116 3.116 ↑ 1.0 1 779

Index Scan using person_payroll_personid_idx on person_payroll pl_9 (cost=0.29..1.88 rows=1 width=17) (actual time=0.004..0.004 rows=1 loops=779)

  • Index Cond: (personid = ppp_9.personid)
  • Filter: ((CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 0
170. 3.895 3.895 ↑ 1.0 1 779

Index Scan using person_names_personid_idx on person_names pn_9 (cost=0.29..0.39 rows=1 width=20) (actual time=0.004..0.005 rows=1 loops=779)

  • Index Cond: (personid = pl_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
171. 0.000 0.000 ↑ 1.0 1 779

Materialize (cost=0.29..2.31 rows=1 width=35) (actual time=0.000..0.000 rows=1 loops=779)

172. 0.004 0.004 ↑ 1.0 1 1

Index Scan using pay_process_pkey on pay_process pp_9 (cost=0.29..2.30 rows=1 width=35) (actual time=0.004..0.004 rows=1 loops=1)

  • Index Cond: (payprocessid = 41095)
173. 8.569 8.569 ↑ 1.9 13 779

Index Scan using personbenelectioncreatets on person_bene_election pbe (cost=0.43..2.16 rows=25 width=37) (actual time=0.006..0.011 rows=13 loops=779)

  • Index Cond: ((personid = pn_9.personid) AND (CURRENT_TIMESTAMP >= createts))
  • Filter: ((createts <> endts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 0
174. 0.000 0.000 ↓ 0.0 0

Index Scan using person_employment_personid_idx on person_employment pe_9 (cost=0.29..0.37 rows=1 width=17) (never executed)

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

SubPlan (for Nested Loop)

176. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=2.31..2.32 rows=1 width=8) (never executed)

177. 0.000 0.000 ↓ 0.0 0

Index Scan using payprocesspayunitperiod on pay_process pp2_6 (cost=0.29..2.31 rows=1 width=8) (never executed)

  • Index Cond: ((pp_9.payunitid = payunitid) AND (pp_9.payyear = payyear) AND (pp_9.payperiod = payperiod))
178. 0.000 10.789 ↓ 0.0 0 1

Unique (cost=4,290.01..4,339.27 rows=1,642 width=583) (actual time=10.789..10.789 rows=0 loops=1)

179. 0.008 10.789 ↓ 0.0 0 1

Sort (cost=4,290.01..4,294.11 rows=1,642 width=583) (actual time=10.789..10.789 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
180. 0.000 10.781 ↓ 0.0 0 1

Nested Loop (cost=2.00..4,202.32 rows=1,642 width=583) (actual time=10.781..10.781 rows=0 loops=1)

  • Join Filter: (ppp_10.personid = pe_10.personid)
181. 0.000 10.781 ↓ 0.0 0 1

Nested Loop (cost=1.71..377.17 rows=30 width=102) (actual time=10.781..10.781 rows=0 loops=1)

  • Join Filter: (ppp_10.personid = pl_10.personid)
182. 1.462 10.781 ↓ 0.0 0 1

Nested Loop (cost=1.42..365.90 rows=33 width=85) (actual time=10.781..10.781 rows=0 loops=1)

  • Join Filter: ((((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: 4534
183. 0.762 9.319 ↓ 9.7 4,534 1

Nested Loop (cost=1.14..347.18 rows=469 width=74) (actual time=0.057..9.319 rows=4,534 loops=1)

  • Join Filter: (ppp_10.personid = pte.personid)
184. 0.612 3.883 ↓ 9.6 779 1

Nested Loop (cost=0.72..278.68 rows=81 width=37) (actual time=0.019..3.883 rows=779 loops=1)

185. 0.155 0.155 ↓ 7.1 779 1

Index Only Scan using person_pay_process_pkey on person_pay_process ppp_10 (cost=0.43..53.62 rows=109 width=17) (actual time=0.009..0.155 rows=779 loops=1)

  • Index Cond: (payprocessid = 41095)
  • Heap Fetches: 779
186. 3.116 3.116 ↑ 1.0 1 779

Index Scan using person_names_personid_idx on person_names pn_10 (cost=0.29..2.05 rows=1 width=20) (actual time=0.004..0.004 rows=1 loops=779)

  • 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
187. 4.674 4.674 ↑ 1.7 6 779

Index Scan using person_tax_elections_pkey on person_tax_elections pte (cost=0.42..0.72 rows=10 width=37) (actual time=0.005..0.006 rows=6 loops=779)

  • Index Cond: (personid = pn_10.personid)
  • Filter: (createts <> endts)
188. 0.000 0.000 ↑ 1.0 1 4,534

Materialize (cost=0.29..2.31 rows=1 width=35) (actual time=0.000..0.000 rows=1 loops=4,534)

189. 0.003 0.003 ↑ 1.0 1 1

Index Scan using pay_process_pkey on pay_process pp_10 (cost=0.29..2.30 rows=1 width=35) (actual time=0.003..0.003 rows=1 loops=1)

  • Index Cond: (payprocessid = 41095)
190. 0.000 0.000 ↓ 0.0 0

Index Scan using person_payroll_personid_idx on person_payroll pl_10 (cost=0.29..0.33 rows=1 width=17) (never executed)

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

Index Scan using person_employment_personid_idx on person_employment pe_10 (cost=0.29..0.37 rows=1 width=17) (never executed)

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

SubPlan (for Nested Loop)

193. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=2.31..2.32 rows=1 width=8) (never executed)

194. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: ((pp_10.payunitid = payunitid) AND (pp_10.payyear = payyear) AND (pp_10.payperiod = payperiod))
195. 0.001 5.686 ↓ 0.0 0 1

Unique (cost=5,004.01..5,072.54 rows=1,958 width=583) (actual time=5.685..5.686 rows=0 loops=1)

196. 0.007 5.685 ↓ 0.0 0 1

Sort (cost=5,004.01..5,008.91 rows=1,958 width=583) (actual time=5.685..5.685 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
197. 0.001 5.678 ↓ 0.0 0 1

Nested Loop (cost=2.16..4,896.96 rows=1,958 width=583) (actual time=5.677..5.678 rows=0 loops=1)

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

Nested Loop (cost=1.87..343.34 rows=15 width=110) (actual time=5.677..5.677 rows=0 loops=1)

  • Join Filter: (ppp_11.personid = pl_11.personid)
199. 0.000 5.677 ↓ 0.0 0 1

Nested Loop (cost=1.58..337.71 rows=16 width=93) (actual time=5.677..5.677 rows=0 loops=1)

  • Join Filter: (ppp_11.personid = pn_11.personid)
200. 0.476 5.677 ↓ 0.0 0 1

Nested Loop (cost=1.29..329.31 rows=22 width=73) (actual time=5.676..5.677 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: 2063
201. 0.031 3.583 ↓ 6.1 809 1

Nested Loop (cost=1.00..269.90 rows=133 width=65) (actual time=0.022..3.583 rows=809 loops=1)

202. 0.285 0.436 ↓ 7.1 779 1

Nested Loop (cost=0.71..57.29 rows=109 width=48) (actual time=0.013..0.436 rows=779 loops=1)

203. 0.151 0.151 ↓ 7.1 779 1

Index Only Scan using person_pay_process_pkey on person_pay_process ppp_11 (cost=0.43..53.62 rows=109 width=17) (actual time=0.008..0.151 rows=779 loops=1)

  • Index Cond: (payprocessid = 41095)
  • Heap Fetches: 779
204. 0.000 0.000 ↑ 1.0 1 779

Materialize (cost=0.29..2.31 rows=1 width=35) (actual time=0.000..0.000 rows=1 loops=779)

205. 0.003 0.003 ↑ 1.0 1 1

Index Scan using pay_process_pkey on pay_process pp_11 (cost=0.29..2.30 rows=1 width=35) (actual time=0.003..0.003 rows=1 loops=1)

  • Index Cond: (payprocessid = 41095)
206. 3.116 3.116 ↑ 1.0 1 779

Index Scan using pers_pos_personid_idx on pers_pos persp_4 (cost=0.29..1.94 rows=1 width=17) (actual time=0.004..0.004 rows=1 loops=779)

  • Index Cond: (personid = ppp_11.personid)
207. 1.618 1.618 ↓ 1.5 3 809

Index Scan using pos_org_rel_positionid_idx on pos_org_rel por (cost=0.29..0.38 rows=2 width=32) (actual time=0.001..0.002 rows=3 loops=809)

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

Index Scan using person_names_personid_idx on person_names pn_11 (cost=0.29..0.37 rows=1 width=20) (never executed)

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

Index Scan using person_payroll_personid_idx on person_payroll pl_11 (cost=0.29..0.34 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))
210. 0.000 0.000 ↓ 0.0 0

Index Scan using person_employment_personid_idx on person_employment pe_11 (cost=0.29..0.40 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))
211.          

SubPlan (for Nested Loop)

212. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=2.31..2.32 rows=1 width=8) (never executed)

213. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: ((pp_11.payunitid = payunitid) AND (pp_11.payyear = payyear) AND (pp_11.payperiod = payperiod))
214. 0.001 1.928 ↓ 0.0 0 1

Unique (cost=239.15..240.26 rows=37 width=603) (actual time=1.927..1.928 rows=0 loops=1)

215. 0.006 1.927 ↓ 0.0 0 1

Sort (cost=239.15..239.25 rows=37 width=603) (actual time=1.927..1.927 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
216. 0.001 1.921 ↓ 0.0 0 1

Nested Loop (cost=1.86..238.19 rows=37 width=603) (actual time=1.921..1.921 rows=0 loops=1)

217. 0.000 1.920 ↓ 0.0 0 1

Nested Loop (cost=1.57..151.87 rows=1 width=99) (actual time=1.920..1.920 rows=0 loops=1)

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

Nested Loop (cost=1.28..151.44 rows=1 width=82) (actual time=1.920..1.920 rows=0 loops=1)

  • Join Filter: (ppp_12.personid = pl_12.personid)
219. 0.001 1.920 ↓ 0.0 0 1

Nested Loop (cost=0.99..150.84 rows=1 width=65) (actual time=1.919..1.920 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: 1
220. 0.219 1.914 ↑ 9.0 1 1

Nested Loop (cost=0.71..148.22 rows=9 width=54) (actual time=0.048..1.914 rows=1 loops=1)

221. 0.137 0.137 ↓ 7.1 779 1

Index Only Scan using person_pay_process_pkey on person_pay_process ppp_12 (cost=0.43..53.62 rows=109 width=17) (actual time=0.008..0.137 rows=779 loops=1)

  • Index Cond: (payprocessid = 41095)
  • Heap Fetches: 779
222. 1.558 1.558 ↓ 0.0 0 779

Index Scan using pk_person_financial_plan_election on person_financial_plan_election pfpe (cost=0.28..0.84 rows=3 width=37) (actual time=0.002..0.002 rows=0 loops=779)

  • Index Cond: (personid = ppp_12.personid)
  • Filter: ((createts <> endts) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
223. 0.002 0.005 ↑ 1.0 1 1

Materialize (cost=0.29..2.31 rows=1 width=35) (actual time=0.004..0.005 rows=1 loops=1)

224. 0.003 0.003 ↑ 1.0 1 1

Index Scan using pay_process_pkey on pay_process pp_12 (cost=0.29..2.30 rows=1 width=35) (actual time=0.003..0.003 rows=1 loops=1)

  • Index Cond: (payprocessid = 41095)
225. 0.000 0.000 ↓ 0.0 0

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

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

Index Scan using person_employment_personid_idx on person_employment pe_12 (cost=0.29..0.41 rows=1 width=17) (never executed)

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

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

  • Index Cond: (personid = pe_12.personid)
  • Filter: ((nametype = 'Legal'::bpchar) AND (CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
228.          

SubPlan (for Nested Loop)

229. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=2.31..2.32 rows=1 width=8) (never executed)

230. 0.000 0.000 ↓ 0.0 0

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

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

Unique (cost=90.69..90.91 rows=8 width=607) (actual time=0.011..0.011 rows=0 loops=1)

232. 0.005 0.011 ↓ 0.0 0 1

Sort (cost=90.69..90.71 rows=8 width=607) (actual time=0.011..0.011 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
233. 0.000 0.006 ↓ 0.0 0 1

Nested Loop (cost=2.28..90.57 rows=8 width=607) (actual time=0.006..0.006 rows=0 loops=1)

  • Join Filter: (pe_13.personid = pn_13.personid)
234. 0.000 0.006 ↓ 0.0 0 1

Nested Loop (cost=1.99..71.60 rows=1 width=95) (actual time=0.006..0.006 rows=0 loops=1)

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

Nested Loop (cost=1.70..71.26 rows=1 width=78) (actual time=0.006..0.006 rows=0 loops=1)

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

Nested Loop (cost=1.41..70.88 rows=1 width=61) (actual time=0.005..0.005 rows=0 loops=1)

237. 0.000 0.005 ↓ 0.0 0 1

Nested Loop (cost=0.99..49.69 rows=32 width=48) (actual time=0.005..0.005 rows=0 loops=1)

  • Join Filter: ((pte_1.createts >= pp_13.createts) OR (pte_1.updatets >= pp_13.createts))
238. 0.001 0.005 ↓ 0.0 0 1

Nested Loop (cost=0.57..4.61 rows=1 width=31) (actual time=0.005..0.005 rows=0 loops=1)

239. 0.004 0.004 ↓ 0.0 0 1

Index Only Scan using pay_time_control_batch_pkey on pay_time_control_batch ptcb (cost=0.28..2.30 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=1)

  • Index Cond: (payprocessid = 41095)
  • Heap Fetches: 0
240. 0.000 0.000 ↓ 0.0 0

Index Scan using pay_process_pkey on pay_process pp_13 (cost=0.29..2.30 rows=1 width=27) (never executed)

  • Index Cond: (payprocessid = 41095)
241. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_persontimeexxid on person_time_ex pte_1 (cost=0.42..43.92 rows=77 width=33) (never executed)

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

Index Only Scan using person_pay_process_pkey on person_pay_process ppp_13 (cost=0.43..0.65 rows=1 width=17) (never executed)

  • Index Cond: ((payprocessid = 41095) AND (personid = pte_1.personid))
  • Heap Fetches: 0
243. 0.000 0.000 ↓ 0.0 0

Index Scan using person_employment_personid_idx on person_employment pe_13 (cost=0.29..0.37 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))
244. 0.000 0.000 ↓ 0.0 0

Index Scan using person_payroll_personid_idx on person_payroll pl_13 (cost=0.29..0.33 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. 0.000 0.000 ↓ 0.0 0

Index Scan using person_names_personid_idx on person_names pn_13 (cost=0.29..0.34 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))
246.          

SubPlan (for Nested Loop)

247. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=2.31..2.32 rows=1 width=8) (never executed)

248. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: ((pp_13.payunitid = payunitid) AND (pp_13.payyear = payyear) AND (pp_13.payperiod = payperiod))
249. 0.004 3.940 ↑ 1.0 1 1

Subquery Scan on *SELECT* 15 (cost=54.77..54.81 rows=1 width=607) (actual time=3.939..3.940 rows=1 loops=1)

250. 0.001 3.936 ↑ 1.0 1 1

Unique (cost=54.77..54.80 rows=1 width=603) (actual time=3.935..3.936 rows=1 loops=1)

251. 0.008 3.935 ↑ 1.0 1 1

Sort (cost=54.77..54.77 rows=1 width=603) (actual time=3.935..3.935 rows=1 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
252. 0.004 3.927 ↑ 1.0 1 1

Nested Loop (cost=1.28..54.76 rows=1 width=603) (actual time=1.101..3.927 rows=1 loops=1)

  • Join Filter: ((pp_14.periodenddate >= pn_14.effectivedate) AND (pp_14.periodenddate <= pn_14.enddate))
253. 0.002 3.897 ↑ 1.0 1 1

Nested Loop (cost=0.99..52.04 rows=1 width=73) (actual time=1.071..3.897 rows=1 loops=1)

254. 0.003 0.003 ↑ 1.0 1 1

Index Scan using pay_process_pkey on pay_process pp_14 (cost=0.29..2.30 rows=1 width=31) (actual time=0.002..0.003 rows=1 loops=1)

  • Index Cond: (payprocessid = 41095)
255. 0.401 3.892 ↑ 3.0 1 1

Nested Loop (cost=0.70..49.71 rows=3 width=62) (actual time=1.067..3.892 rows=1 loops=1)

256. 0.263 0.263 ↓ 269.0 807 1

Index Scan using personpayrollpayunitid on person_payroll pl_14 (cost=0.29..32.91 rows=3 width=25) (actual time=0.008..0.263 rows=807 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: 28
257. 3.216 3.228 ↓ 0.0 0 807

Index Scan using personemploymentenddate on person_employment pe_14 (cost=0.41..5.59 rows=1 width=37) (actual time=0.004..0.004 rows=0 loops=807)

  • Index Cond: ((personid = pl_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: 1
258.          

SubPlan (for Index Scan)

259. 0.003 0.012 ↑ 1.0 1 1

Aggregate (cost=3.12..3.13 rows=1 width=8) (actual time=0.012..0.012 rows=1 loops=1)

260. 0.009 0.009 ↓ 2.0 2 1

Index Scan using person_employment_personid_idx on person_employment pe2 (cost=0.29..3.12 rows=1 width=8) (actual time=0.008..0.009 rows=2 loops=1)

  • Index Cond: (personid = pe_14.personid)
  • Filter: ((effectivedate <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 3
261. 0.006 0.006 ↑ 1.0 1 1

Index Scan using person_names_personid_idx on person_names pn_14 (cost=0.29..0.38 rows=1 width=28) (actual time=0.006..0.006 rows=1 loops=1)

  • Index Cond: (personid = pl_14.personid)
  • Filter: ((nametype = 'Legal'::bpchar) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
262.          

SubPlan (for Nested Loop)

263. 0.004 0.020 ↑ 1.0 1 1

Aggregate (cost=2.31..2.32 rows=1 width=8) (actual time=0.020..0.020 rows=1 loops=1)

264. 0.016 0.016 ↓ 22.0 22 1

Index Scan using payprocesspayunitperiod on pay_process pp2_1 (cost=0.29..2.31 rows=1 width=8) (actual time=0.005..0.016 rows=22 loops=1)

  • Index Cond: ((pp_14.payunitid = payunitid) AND (pp_14.payyear = payyear) AND (pp_14.payperiod = payperiod))
265. 0.000 4.040 ↓ 0.0 0 1

Subquery Scan on *SELECT* 16 (cost=292.92..293.26 rows=8 width=587) (actual time=4.040..4.040 rows=0 loops=1)

266. 0.001 4.040 ↓ 0.0 0 1

Unique (cost=292.92..293.16 rows=8 width=583) (actual time=4.040..4.040 rows=0 loops=1)

267. 0.005 4.039 ↓ 0.0 0 1

Sort (cost=292.92..292.94 rows=8 width=583) (actual time=4.039..4.039 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
268. 0.001 4.034 ↓ 0.0 0 1

Nested Loop (cost=1.58..292.80 rows=8 width=583) (actual time=4.034..4.034 rows=0 loops=1)

  • Join Filter: (ppp_14.personid = ppay.personid)
269. 0.000 4.033 ↓ 0.0 0 1

Nested Loop (cost=1.29..272.44 rows=5 width=85) (actual time=4.033..4.033 rows=0 loops=1)

  • Join Filter: (ppp_14.personid = pn_15.personid)
270. 0.350 4.033 ↓ 0.0 0 1

Nested Loop (cost=1.00..269.67 rows=7 width=65) (actual time=4.033..4.033 rows=0 loops=1)

  • Join Filter: ((pl_15.createts >= pp_15.createts) 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: 1208
271. 0.426 3.683 ↓ 12.1 1,208 1

Nested Loop (cost=0.72..264.86 rows=100 width=54) (actual time=0.022..3.683 rows=1,208 loops=1)

272. 0.141 0.141 ↓ 7.1 779 1

Index Only Scan using person_pay_process_pkey on person_pay_process ppp_14 (cost=0.43..53.62 rows=109 width=17) (actual time=0.009..0.141 rows=779 loops=1)

  • Index Cond: (payprocessid = 41095)
  • Heap Fetches: 779
273. 3.116 3.116 ↓ 2.0 2 779

Index Scan using person_locations_personid_idx on person_locations pl_15 (cost=0.29..1.93 rows=1 width=37) (actual time=0.004..0.004 rows=2 loops=779)

  • Index Cond: (personid = ppp_14.personid)
  • Filter: ((personlocationtype = ANY ('{P,TL}'::bpchar[])) AND (createts <> endts) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 1
274. 0.000 0.000 ↑ 1.0 1 1,208

Materialize (cost=0.29..2.31 rows=1 width=35) (actual time=0.000..0.000 rows=1 loops=1,208)

275. 0.003 0.003 ↑ 1.0 1 1

Index Scan using pay_process_pkey on pay_process pp_15 (cost=0.29..2.30 rows=1 width=35) (actual time=0.003..0.003 rows=1 loops=1)

  • Index Cond: (payprocessid = 41095)
276. 0.000 0.000 ↓ 0.0 0

Index Scan using person_names_personid_idx on person_names pn_15 (cost=0.29..0.38 rows=1 width=20) (never executed)

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

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

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

SubPlan (for Nested Loop)

279. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=2.31..2.32 rows=1 width=8) (never executed)

280. 0.000 0.000 ↓ 0.0 0

Index Scan using payprocesspayunitperiod on pay_process pp2 (cost=0.29..2.31 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,622.768 ms
Execution time : 82.760 ms