explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Hgjv

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 7,864.723 ↑ 9,115.5 2 1

Append (cost=146,871.96..899,665.01 rows=18,231 width=674) (actual time=4,221.704..7,864.723 rows=2 loops=1)

2.          

CTE payrollpeeps

3. 964.449 2,249.280 ↓ 1.1 2,038,016 1

Hash Join (cost=607.96..101,100.87 rows=1,804,791 width=543) (actual time=3.369..2,249.280 rows=2,038,016 loops=1)

  • Hash Cond: (ppp.payprocessid = pp_16.payprocessid)
4. 308.012 1,281.652 ↓ 1.1 2,038,113 1

Merge Join (cost=2.75..95,756.59 rows=1,804,791 width=24) (actual time=0.171..1,281.652 rows=2,038,113 loops=1)

  • Merge Cond: (pl_1.personid = ppp.personid)
5. 22.322 60.158 ↑ 1.0 21,913 1

Merge Join (cost=0.71..4,445.25 rows=22,318 width=33) (actual time=0.036..60.158 rows=21,913 loops=1)

  • Merge Cond: (pl_1.personid = pn.personid)
6. 17.470 17.470 ↓ 1.0 23,494 1

Index Scan using person_payroll_personid_idx on person_payroll pl_1 (cost=0.29..1,460.16 rows=23,285 width=13) (actual time=0.010..17.470 rows=23,494 loops=1)

  • Filter: ((CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 3931
7. 20.366 20.366 ↓ 1.0 27,792 1

Index Scan using personnamenametypedates on person_names pn (cost=0.42..2,635.57 rows=27,249 width=20) (actual time=0.024..20.366 rows=27,792 loops=1)

  • Index Cond: ((nametype = 'Legal'::bpchar) AND (CURRENT_DATE <= enddate) AND (CURRENT_DATE >= effectivedate))
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 5135
8. 913.482 913.482 ↓ 1.0 2,054,263 1

Index Scan using personpayprocesspersonid on person_pay_process ppp (cost=0.43..68,073.00 rows=2,054,238 width=17) (actual time=0.008..913.482 rows=2,054,263 loops=1)

9. 1.744 3.179 ↑ 1.0 13,387 1

Hash (cost=437.87..437.87 rows=13,387 width=523) (actual time=3.179..3.179 rows=13,387 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 2524kB
10. 1.435 1.435 ↑ 1.0 13,387 1

Seq Scan on pay_process pp_16 (cost=0.00..437.87 rows=13,387 width=523) (actual time=0.007..1.435 rows=13,387 loops=1)

11. 0.004 7,248.868 ↑ 17,592.0 1 1

Result (cost=45,771.09..638,669.06 rows=17,592 width=674) (actual time=4,221.704..7,248.868 rows=1 loops=1)

12. 0.010 7,248.864 ↑ 17,592.0 1 1

Append (cost=45,771.09..638,449.16 rows=17,592 width=670) (actual time=4,221.700..7,248.864 rows=1 loops=1)

13. 0.001 4,221.700 ↑ 702.0 1 1

Unique (cost=45,771.09..45,792.15 rows=702 width=762) (actual time=4,221.700..4,221.700 rows=1 loops=1)

14. 0.009 4,221.699 ↑ 702.0 1 1

Sort (cost=45,771.09..45,772.85 rows=702 width=762) (actual time=4,221.699..4,221.699 rows=1 loops=1)

  • Sort Key: pp.personid, pp.lname, pe.effectivedate, pe.createts, pe.endts, (CASE WHEN (pe.createts > (SubPlan 18)) THEN 'Not Processed'::text ELSE 'Processed'::text END), pe.companyid, pp.payunitid, pp.payperiod, pp.payyear, pp.payprocesstype
  • Sort Method: quicksort Memory: 25kB
15. 6.515 4,221.690 ↑ 702.0 1 1

Merge Join (cost=41,200.94..45,737.90 rows=702 width=762) (actual time=4,220.528..4,221.690 rows=1 loops=1)

  • Merge Cond: (pe.personid = pp.personid)
  • 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
16. 17.302 17.302 ↑ 1.0 31,518 1

Index Scan using person_employment_personid_idx on person_employment pe (cost=0.29..2,468.68 rows=32,547 width=41) (actual time=0.011..17.302 rows=31,518 loops=1)

  • Filter: ((createts <> endts) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 13733
17. 0.675 4,197.841 ↑ 10.9 826 1

Sort (cost=41,200.65..41,223.21 rows=9,024 width=234) (actual time=4,197.779..4,197.841 rows=826 loops=1)

  • Sort Key: pp.personid
  • Sort Method: quicksort Memory: 134kB
18. 4,197.166 4,197.166 ↑ 11.6 779 1

CTE Scan on payrollpeeps pp (cost=0.00..40,607.80 rows=9,024 width=234) (actual time=318.128..4,197.166 rows=779 loops=1)

  • Filter: (payprocessid = 41095)
  • Rows Removed by Filter: 2037237
19.          

SubPlan (for Merge Join)

20. 0.009 0.032 ↑ 1.0 1 1

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

21. 0.023 0.023 ↓ 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.012..0.023 rows=22 loops=1)

  • Index Cond: ((pp.payunitid = payunitid) AND (pp.payyear = payyear) AND (pp.payperiod = payperiod))
22. 0.000 286.591 ↓ 0.0 0 1

Unique (cost=44,159.08..44,163.79 rows=157 width=762) (actual time=286.591..286.591 rows=0 loops=1)

23. 0.014 286.591 ↓ 0.0 0 1

Sort (cost=44,159.08..44,159.47 rows=157 width=762) (actual time=286.591..286.591 rows=0 loops=1)

  • Sort Key: pp_1.personid, pp_1.lname, pe_1.effectivedate, pe_1.createts, pe_1.endts, (CASE WHEN (pe_1.createts > (SubPlan 17)) THEN 'Not Processed'::text ELSE 'Processed'::text END), pe_1.companyid, pp_1.payunitid, pp_1.payperiod, pp_1.payyear, pp_1.payprocesstype
  • Sort Method: quicksort Memory: 25kB
24. 0.001 286.577 ↓ 0.0 0 1

Nested Loop (cost=41,201.23..44,153.35 rows=157 width=762) (actual time=286.577..286.577 rows=0 loops=1)

25. 1.445 286.576 ↓ 0.0 0 1

Merge Join (cost=41,200.94..42,703.31 rows=2,156 width=231) (actual time=286.576..286.576 rows=0 loops=1)

  • Merge Cond: (pdd.personid = pp_1.personid)
  • 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
26. 2.226 2.226 ↑ 1.2 5,603 1

Index Scan using person_direct_deposits_pkey on person_direct_deposits pdd (cost=0.28..278.16 rows=6,474 width=37) (actual time=0.007..2.226 rows=5,603 loops=1)

  • Filter: (createts <> endts)
27. 0.493 282.905 ↑ 5.4 1,671 1

Sort (cost=41,200.65..41,223.21 rows=9,024 width=238) (actual time=282.825..282.905 rows=1,671 loops=1)

  • Sort Key: pp_1.personid
  • Sort Method: quicksort Memory: 134kB
28. 282.412 282.412 ↑ 11.6 779 1

CTE Scan on payrollpeeps pp_1 (cost=0.00..40,607.80 rows=9,024 width=238) (actual time=22.669..282.412 rows=779 loops=1)

  • Filter: (payprocessid = 41095)
  • Rows Removed by Filter: 2037237
29. 0.000 0.000 ↓ 0.0 0

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

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

SubPlan (for Nested Loop)

31. 0.000 0.000 ↓ 0.0 0

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

32. 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))
33. 0.000 309.347 ↓ 0.0 0 1

Unique (cost=45,864.04..45,890.71 rows=889 width=762) (actual time=309.347..309.347 rows=0 loops=1)

34. 0.015 309.347 ↓ 0.0 0 1

Sort (cost=45,864.04..45,866.27 rows=889 width=762) (actual time=309.347..309.347 rows=0 loops=1)

  • Sort Key: pp_2.personid, pp_2.lname, pc.effectivedate, pc.createts, pc.endts, (CASE WHEN (pc.createts > (SubPlan 16)) THEN 'Not Processed'::text ELSE 'Processed'::text END), pe_2.companyid, pp_2.payunitid, pp_2.payperiod, pp_2.payyear, pp_2.payprocesstype
  • Sort Method: quicksort Memory: 25kB
35. 0.001 309.332 ↓ 0.0 0 1

Nested Loop (cost=41,201.36..45,820.50 rows=889 width=762) (actual time=309.332..309.332 rows=0 loops=1)

  • Join Filter: (pp_2.personid = pe_2.personid)
36. 5.747 309.331 ↓ 0.0 0 1

Merge Join (cost=41,201.07..43,429.71 rows=775 width=251) (actual time=309.331..309.331 rows=0 loops=1)

  • Merge Cond: (pc.personid = pp_2.personid)
  • 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
37. 11.160 11.160 ↑ 1.0 25,828 1

Index Scan using person_compensation_pkey on person_compensation pc (cost=0.41..1,763.97 rows=26,792 width=37) (actual time=0.012..11.160 rows=25,828 loops=1)

  • Filter: ((createts <> endts) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 6307
38. 0.479 292.424 ↑ 5.8 1,560 1

Sort (cost=41,200.65..41,223.21 rows=9,024 width=234) (actual time=292.342..292.424 rows=1,560 loops=1)

  • Sort Key: pp_2.personid
  • Sort Method: quicksort Memory: 134kB
39. 291.945 291.945 ↑ 11.6 779 1

CTE Scan on payrollpeeps pp_2 (cost=0.00..40,607.80 rows=9,024 width=234) (actual time=23.232..291.945 rows=779 loops=1)

  • Filter: (payprocessid = 41095)
  • Rows Removed by Filter: 2037237
40. 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 = pc.personid)
  • Filter: ((CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
41.          

SubPlan (for Nested Loop)

42. 0.000 0.000 ↓ 0.0 0

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

43. 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))
44. 0.001 298.646 ↓ 0.0 0 1

Unique (cost=45,749.06..45,777.41 rows=945 width=762) (actual time=298.646..298.646 rows=0 loops=1)

45. 0.014 298.645 ↓ 0.0 0 1

Sort (cost=45,749.06..45,751.42 rows=945 width=762) (actual time=298.645..298.645 rows=0 loops=1)

  • Sort Key: pp_3.personid, pp_3.lname, persp.effectivedate, persp.createts, persp.endts, (CASE WHEN (persp.createts > (SubPlan 15)) THEN 'Not Processed'::text ELSE 'Processed'::text END), pe_3.companyid, pp_3.payunitid, pp_3.payperiod, pp_3.payyear, pp_3.payprocesstype
  • Sort Method: quicksort Memory: 25kB
46. 0.001 298.631 ↓ 0.0 0 1

Nested Loop (cost=41,201.23..45,702.36 rows=945 width=762) (actual time=298.631..298.631 rows=0 loops=1)

  • Join Filter: (pp_3.personid = pe_3.personid)
47. 5.185 298.630 ↓ 0.0 0 1

Merge Join (cost=41,200.94..43,248.03 rows=611 width=251) (actual time=298.630..298.630 rows=0 loops=1)

  • Merge Cond: (persp.personid = pp_3.personid)
  • 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
48. 11.212 11.212 ↑ 1.1 23,072 1

Index Scan using pers_pos_personid_idx on pers_pos persp (cost=0.29..1,670.91 rows=24,497 width=37) (actual time=0.013..11.212 rows=23,072 loops=1)

  • Filter: ((createts <> endts) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 9977
49. 0.449 282.233 ↑ 11.4 791 1

Sort (cost=41,200.65..41,223.21 rows=9,024 width=234) (actual time=282.170..282.233 rows=791 loops=1)

  • Sort Key: pp_3.personid
  • Sort Method: quicksort Memory: 134kB
50. 281.784 281.784 ↑ 11.6 779 1

CTE Scan on payrollpeeps pp_3 (cost=0.00..40,607.80 rows=9,024 width=234) (actual time=22.526..281.784 rows=779 loops=1)

  • Filter: (payprocessid = 41095)
  • Rows Removed by Filter: 2037237
51. 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 = persp.personid)
  • Filter: ((CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
52.          

SubPlan (for Nested Loop)

53. 0.000 0.000 ↓ 0.0 0

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

54. 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))
55. 0.000 296.957 ↓ 0.0 0 1

Unique (cost=43,741.59..43,747.02 rows=181 width=762) (actual time=296.957..296.957 rows=0 loops=1)

56. 0.013 296.957 ↓ 0.0 0 1

Sort (cost=43,741.59..43,742.05 rows=181 width=762) (actual time=296.957..296.957 rows=0 loops=1)

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

Nested Loop (cost=41,201.52..43,734.81 rows=181 width=762) (actual time=296.944..296.944 rows=0 loops=1)

  • Join Filter: (pp_4.personid = pe_4.personid)
58. 0.000 296.943 ↓ 0.0 0 1

Nested Loop (cost=41,201.23..43,266.39 rows=117 width=251) (actual time=296.943..296.943 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))))
59. 7.057 296.943 ↓ 0.0 0 1

Merge Join (cost=41,200.94..42,759.16 rows=1,318 width=251) (actual time=296.942..296.943 rows=0 loops=1)

  • Merge Cond: (persp_1.personid = pp_4.personid)
  • Join Filter: ((persp_1.enddate <= pp_4.periodenddate) AND (pp_4.periodstartdate <= persp_1.effectivedate))
  • Rows Removed by Join Filter: 809
60. 8.002 8.002 ↑ 1.1 33,049 1

Index Scan using pers_pos_personid_idx on pers_pos persp_1 (cost=0.29..1,233.81 rows=34,968 width=25) (actual time=0.007..8.002 rows=33,049 loops=1)

61. 0.439 281.884 ↑ 11.2 809 1

Sort (cost=41,200.65..41,223.21 rows=9,024 width=234) (actual time=281.844..281.884 rows=809 loops=1)

  • Sort Key: pp_4.personid
  • Sort Method: quicksort Memory: 134kB
62. 281.445 281.445 ↑ 11.6 779 1

CTE Scan on payrollpeeps pp_4 (cost=0.00..40,607.80 rows=9,024 width=234) (actual time=22.599..281.445 rows=779 loops=1)

  • Filter: (payprocessid = 41095)
  • Rows Removed by Filter: 2037237
63. 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))
64. 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))
65.          

SubPlan (for Nested Loop)

66. 0.000 0.000 ↓ 0.0 0

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

67. 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))
68. 0.001 279.504 ↓ 0.0 0 1

Unique (cost=43,029.02..43,032.14 rows=104 width=762) (actual time=279.504..279.504 rows=0 loops=1)

69. 0.014 279.503 ↓ 0.0 0 1

Sort (cost=43,029.02..43,029.28 rows=104 width=762) (actual time=279.503..279.503 rows=0 loops=1)

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

Nested Loop (cost=288.73..43,025.54 rows=104 width=762) (actual time=279.489..279.489 rows=0 loops=1)

71. 0.669 279.488 ↓ 0.0 0 1

Hash Join (cost=288.44..42,034.88 rows=1,488 width=251) (actual time=279.488..279.488 rows=0 loops=1)

  • Hash Cond: (pp_5.personid = pmd.personid)
  • 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
72. 276.655 276.655 ↑ 11.6 779 1

CTE Scan on payrollpeeps pp_5 (cost=0.00..40,607.80 rows=9,024 width=234) (actual time=22.525..276.655 rows=779 loops=1)

  • Filter: (payprocessid = 41095)
  • Rows Removed by Filter: 2037237
73. 0.674 2.164 ↓ 1.0 4,300 1

Hash (cost=234.97..234.97 rows=4,278 width=37) (actual time=2.164..2.164 rows=4,300 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 384kB
74. 1.490 1.490 ↓ 1.0 4,300 1

Seq Scan on person_misc_deduction pmd (cost=0.00..234.97 rows=4,278 width=37) (actual time=0.011..1.490 rows=4,300 loops=1)

  • Filter: ((createts <> endts) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 943
75. 0.000 0.000 ↓ 0.0 0

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

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

SubPlan (for Nested Loop)

77. 0.000 0.000 ↓ 0.0 0

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

78. 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))
79. 0.001 293.772 ↓ 0.0 0 1

Unique (cost=43,636.28..43,642.04 rows=192 width=762) (actual time=293.772..293.772 rows=0 loops=1)

80. 0.015 293.771 ↓ 0.0 0 1

Sort (cost=43,636.28..43,636.76 rows=192 width=762) (actual time=293.771..293.771 rows=0 loops=1)

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

Nested Loop (cost=435.62..43,629.00 rows=192 width=762) (actual time=293.756..293.756 rows=0 loops=1)

  • Join Filter: (pp_6.personid = pe_6.personid)
82. 1.666 293.755 ↓ 0.0 0 1

Hash Join (cost=435.33..42,229.46 rows=1,979 width=251) (actual time=293.755..293.755 rows=0 loops=1)

  • Hash Cond: (pp_6.personid = pptop.personid)
  • Join Filter: ((((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
83. 288.279 288.279 ↑ 11.6 779 1

CTE Scan on payrollpeeps pp_6 (cost=0.00..40,607.80 rows=9,024 width=234) (actual time=21.961..288.279 rows=779 loops=1)

  • Filter: (payprocessid = 41095)
  • Rows Removed by Filter: 2037237
84. 1.675 3.810 ↑ 1.0 11,057 1

Hash (cost=296.51..296.51 rows=11,105 width=37) (actual time=3.810..3.810 rows=11,057 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 949kB
85. 2.135 2.135 ↑ 1.0 11,057 1

Seq Scan on person_pto_plans pptop (cost=0.00..296.51 rows=11,105 width=37) (actual time=0.014..2.135 rows=11,057 loops=1)

  • Filter: (createts <> endts)
  • Rows Removed by Filter: 104
86. 0.000 0.000 ↓ 0.0 0

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

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

SubPlan (for Nested Loop)

88. 0.000 0.000 ↓ 0.0 0

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

89. 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))
90. 0.000 0.039 ↓ 0.0 0 1

Unique (cost=40,935.18..40,935.22 rows=1 width=762) (actual time=0.039..0.039 rows=0 loops=1)

91. 0.027 0.039 ↓ 0.0 0 1

Sort (cost=40,935.18..40,935.19 rows=1 width=762) (actual time=0.039..0.039 rows=0 loops=1)

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

Nested Loop (cost=0.99..40,935.17 rows=1 width=762) (actual time=0.012..0.012 rows=0 loops=1)

93. 0.000 0.011 ↓ 0.0 0 1

Nested Loop (cost=0.70..40,932.35 rows=1 width=251) (actual time=0.011..0.011 rows=0 loops=1)

  • Join Filter: ((persp_2.personid = pp_7.personid) AND (((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))))
94. 0.000 0.011 ↓ 0.0 0 1

Nested Loop (cost=0.70..8.71 rows=1 width=37) (actual time=0.011..0.011 rows=0 loops=1)

  • Join Filter: (persp_2.edtcode = e.edtcode)
95. 0.011 0.011 ↓ 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.011..0.011 rows=0 loops=1)

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

Materialize (cost=0.27..3.32 rows=2 width=44) (never executed)

97. 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)
98. 0.000 0.000 ↓ 0.0 0

CTE Scan on payrollpeeps pp_7 (cost=0.00..40,607.80 rows=9,024 width=226) (never executed)

  • Filter: (payprocessid = 41095)
99. 0.000 0.000 ↓ 0.0 0

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

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

SubPlan (for Nested Loop)

101. 0.000 0.000 ↓ 0.0 0

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

102. 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))
103. 0.000 0.008 ↓ 0.0 0 1

Unique (cost=40,935.18..40,935.22 rows=1 width=762) (actual time=0.008..0.008 rows=0 loops=1)

104. 0.005 0.008 ↓ 0.0 0 1

Sort (cost=40,935.18..40,935.19 rows=1 width=762) (actual time=0.008..0.008 rows=0 loops=1)

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

Nested Loop (cost=0.99..40,935.17 rows=1 width=762) (actual time=0.003..0.003 rows=0 loops=1)

106. 0.001 0.003 ↓ 0.0 0 1

Nested Loop (cost=0.70..40,932.35 rows=1 width=251) (actual time=0.002..0.003 rows=0 loops=1)

  • Join Filter: ((persp_3.personid = pp_8.personid) AND (((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))))
107. 0.000 0.002 ↓ 0.0 0 1

Nested Loop (cost=0.70..8.71 rows=1 width=37) (actual time=0.002..0.002 rows=0 loops=1)

  • Join Filter: (persp_3.edtcode = e_1.edtcode)
108. 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)
109. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.27..3.32 rows=2 width=44) (never executed)

110. 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)
111. 0.000 0.000 ↓ 0.0 0

CTE Scan on payrollpeeps pp_8 (cost=0.00..40,607.80 rows=9,024 width=226) (never executed)

  • Filter: (payprocessid = 41095)
112. 0.000 0.000 ↓ 0.0 0

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

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

SubPlan (for Nested Loop)

114. 0.000 0.000 ↓ 0.0 0

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

115. 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))
116. 0.000 316.929 ↓ 0.0 0 1

Unique (cost=88,525.98..88,792.02 rows=8,868 width=762) (actual time=316.929..316.929 rows=0 loops=1)

117. 0.006 316.929 ↓ 0.0 0 1

Sort (cost=88,525.98..88,548.15 rows=8,868 width=762) (actual time=316.929..316.929 rows=0 loops=1)

  • Sort Key: pp_9.personid, pp_9.lname, pbe.effectivedate, pbe.createts, pbe.endts, (CASE WHEN (pbe.createts > (SubPlan 9)) THEN 'Not Processed'::text ELSE 'Processed'::text END), pe_9.companyid, pp_9.payunitid, pp_9.payperiod, pp_9.payyear, pp_9.payprocesstype
  • Sort Method: quicksort Memory: 25kB
118. 0.987 316.923 ↓ 0.0 0 1

Nested Loop (cost=41,201.50..87,944.48 rows=8,868 width=762) (actual time=316.923..316.923 rows=0 loops=1)

  • Join Filter: ((((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
119. 6.058 307.367 ↑ 10.1 779 1

Merge Join (cost=41,201.07..43,846.60 rows=7,902 width=251) (actual time=288.879..307.367 rows=779 loops=1)

  • Merge Cond: (pe_9.personid = pp_9.personid)
120. 16.846 16.846 ↑ 1.1 25,285 1

Index Scan using personemploymentenddate on person_employment pe_9 (cost=0.42..2,454.23 rows=27,029 width=17) (actual time=0.023..16.846 rows=25,285 loops=1)

  • Index Cond: ((CURRENT_DATE <= enddate) AND (CURRENT_DATE >= effectivedate))
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 12183
121. 0.466 284.463 ↑ 11.6 779 1

Sort (cost=41,200.65..41,223.21 rows=9,024 width=234) (actual time=284.406..284.463 rows=779 loops=1)

  • Sort Key: pp_9.personid
  • Sort Method: quicksort Memory: 134kB
122. 283.997 283.997 ↑ 11.6 779 1

CTE Scan on payrollpeeps pp_9 (cost=0.00..40,607.80 rows=9,024 width=234) (actual time=22.480..283.997 rows=779 loops=1)

  • Filter: (payprocessid = 41095)
  • Rows Removed by Filter: 2037237
123. 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 = pe_9.personid) AND (CURRENT_TIMESTAMP >= createts))
  • Filter: ((createts <> endts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 0
124.          

SubPlan (for Nested Loop)

125. 0.000 0.000 ↓ 0.0 0

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

126. 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))
127. 0.000 351.810 ↓ 0.0 0 1

Unique (cost=56,325.31..56,400.49 rows=2,506 width=762) (actual time=351.810..351.810 rows=0 loops=1)

128. 0.012 351.810 ↓ 0.0 0 1

Sort (cost=56,325.31..56,331.57 rows=2,506 width=762) (actual time=351.810..351.810 rows=0 loops=1)

  • Sort Key: pp_10.personid, pp_10.lname, pte.effectivedate, pte.createts, pte.endts, (CASE WHEN (pte.createts > (SubPlan 8)) THEN 'Not Processed'::text ELSE 'Processed'::text END), pe_10.companyid, pp_10.payunitid, pp_10.payperiod, pp_10.payyear, pp_10.payprocesstype
  • Sort Method: quicksort Memory: 25kB
129. 13.175 351.798 ↓ 0.0 0 1

Merge Join (cost=41,204.12..56,183.83 rows=2,506 width=762) (actual time=351.798..351.798 rows=0 loops=1)

  • Merge Cond: (pe_10.personid = pte.personid)
  • 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
130. 5.910 305.120 ↑ 10.1 779 1

Merge Join (cost=41,201.07..43,846.60 rows=7,902 width=251) (actual time=286.807..305.120 rows=779 loops=1)

  • Merge Cond: (pe_10.personid = pp_10.personid)
131. 16.801 16.801 ↑ 1.1 25,285 1

Index Scan using personemploymentenddate on person_employment pe_10 (cost=0.42..2,454.23 rows=27,029 width=17) (actual time=0.018..16.801 rows=25,285 loops=1)

  • Index Cond: ((CURRENT_DATE <= enddate) AND (CURRENT_DATE >= effectivedate))
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 12183
132. 0.449 282.409 ↑ 11.6 779 1

Sort (cost=41,200.65..41,223.21 rows=9,024 width=234) (actual time=282.345..282.409 rows=779 loops=1)

  • Sort Key: pp_10.personid
  • Sort Method: quicksort Memory: 134kB
133. 281.960 281.960 ↑ 11.6 779 1

CTE Scan on payrollpeeps pp_10 (cost=0.00..40,607.80 rows=9,024 width=234) (actual time=22.494..281.960 rows=779 loops=1)

  • Filter: (payprocessid = 41095)
  • Rows Removed by Filter: 2037237
134. 33.503 33.503 ↑ 1.1 117,110 1

Index Scan using person_tax_elections_pkey on person_tax_elections pte (cost=0.42..5,021.72 rows=125,671 width=37) (actual time=0.011..33.503 rows=117,110 loops=1)

  • Filter: (createts <> endts)
135.          

SubPlan (for Merge Join)

136. 0.000 0.000 ↓ 0.0 0

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

137. 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))
138. 0.000 300.282 ↓ 0.0 0 1

Unique (cost=55,909.85..56,014.50 rows=2,990 width=762) (actual time=300.282..300.282 rows=0 loops=1)

139. 0.014 300.282 ↓ 0.0 0 1

Sort (cost=55,909.85..55,917.32 rows=2,990 width=762) (actual time=300.282..300.282 rows=0 loops=1)

  • Sort Key: pp_11.personid, pp_11.lname, por.effectivedate, por.createts, por.endts, por.positionid, por.organizationid, (CASE WHEN (por.createts > (SubPlan 7)) THEN 'Not Processed'::text ELSE 'Processed'::text END), pe_11.companyid, pp_11.payunitid, pp_11.payperiod, pp_11.payyear, pp_11.payprocesstype
  • Sort Method: quicksort Memory: 25kB
140. 0.000 300.268 ↓ 0.0 0 1

Nested Loop (cost=41,201.52..55,737.24 rows=2,990 width=762) (actual time=300.268..300.268 rows=0 loops=1)

  • Join Filter: (pp_11.personid = pe_11.personid)
141. 0.557 300.268 ↓ 0.0 0 1

Nested Loop (cost=41,201.23..48,000.01 rows=1,931 width=259) (actual time=300.268..300.268 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
142. 7.007 298.093 ↑ 14.7 809 1

Merge Join (cost=41,200.94..42,699.84 rows=11,864 width=251) (actual time=285.527..298.093 rows=809 loops=1)

  • Merge Cond: (persp_4.personid = pp_11.personid)
143. 8.427 8.427 ↑ 1.1 33,049 1

Index Scan using pers_pos_personid_idx on pers_pos persp_4 (cost=0.29..1,233.81 rows=34,968 width=17) (actual time=0.009..8.427 rows=33,049 loops=1)

144. 0.462 282.659 ↑ 11.2 809 1

Sort (cost=41,200.65..41,223.21 rows=9,024 width=234) (actual time=282.591..282.659 rows=809 loops=1)

  • Sort Key: pp_11.personid
  • Sort Method: quicksort Memory: 134kB
145. 282.197 282.197 ↑ 11.6 779 1

CTE Scan on payrollpeeps pp_11 (cost=0.00..40,607.80 rows=9,024 width=234) (actual time=22.323..282.197 rows=779 loops=1)

  • Filter: (payprocessid = 41095)
  • Rows Removed by Filter: 2037237
146. 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.002..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
147. 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))
148.          

SubPlan (for Nested Loop)

149. 0.000 0.000 ↓ 0.0 0

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

150. 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))
151. 0.001 293.269 ↓ 0.0 0 1

Unique (cost=43,148.85..43,150.53 rows=56 width=782) (actual time=293.268..293.269 rows=0 loops=1)

152. 0.014 293.268 ↓ 0.0 0 1

Sort (cost=43,148.85..43,148.99 rows=56 width=782) (actual time=293.268..293.268 rows=0 loops=1)

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

Nested Loop (cost=41,201.22..43,147.23 rows=56 width=782) (actual time=293.254..293.254 rows=0 loops=1)

154. 0.686 293.253 ↓ 0.0 0 1

Merge Join (cost=41,200.93..42,184.99 rows=1,653 width=251) (actual time=293.253..293.253 rows=0 loops=1)

  • Merge Cond: (pfpe.personid = pp_12.personid)
  • 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
155. 1.021 1.021 ↑ 1.1 1,697 1

Index Scan using pk_person_financial_plan_election on person_financial_plan_election pfpe (cost=0.28..109.11 rows=1,924 width=37) (actual time=0.009..1.021 rows=1,697 loops=1)

  • Filter: ((createts <> endts) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 153
156. 0.587 291.546 ↑ 11.6 779 1

Sort (cost=41,200.65..41,223.21 rows=9,024 width=234) (actual time=291.489..291.546 rows=779 loops=1)

  • Sort Key: pp_12.personid
  • Sort Method: quicksort Memory: 134kB
157. 290.959 290.959 ↑ 11.6 779 1

CTE Scan on payrollpeeps pp_12 (cost=0.00..40,607.80 rows=9,024 width=234) (actual time=22.303..290.959 rows=779 loops=1)

  • Filter: (payprocessid = 41095)
  • Rows Removed by Filter: 2037237
158. 0.000 0.000 ↓ 0.0 0

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

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

SubPlan (for Nested Loop)

160. 0.000 0.000 ↓ 0.0 0

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

161. 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))
162. 0.000 29.444 ↓ 0.0 0 1

Unique (cost=40,967.41..40,967.76 rows=13 width=786) (actual time=29.444..29.444 rows=0 loops=1)

163. 0.014 29.444 ↓ 0.0 0 1

Sort (cost=40,967.41..40,967.44 rows=13 width=786) (actual time=29.444..29.444 rows=0 loops=1)

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

Nested Loop (cost=48.00..40,967.17 rows=13 width=786) (actual time=29.430..29.430 rows=0 loops=1)

  • Join Filter: (pp_13.personid = pe_13.personid)
165. 0.013 29.430 ↓ 0.0 0 1

Hash Join (cost=47.71..40,926.94 rows=26 width=247) (actual time=29.430..29.430 rows=0 loops=1)

  • Hash Cond: (pp_13.personid = pte_1.personid)
  • Join Filter: ((pte_1.createts >= pp_13.createts) OR (pte_1.updatets >= pp_13.createts))
166. 29.406 29.406 ↑ 9,024.0 1 1

CTE Scan on payrollpeeps pp_13 (cost=0.00..40,607.80 rows=9,024 width=226) (actual time=29.406..29.406 rows=1 loops=1)

  • Filter: (payprocessid = 41095)
  • Rows Removed by Filter: 162029
167. 0.000 0.011 ↓ 0.0 0 1

Hash (cost=46.99..46.99 rows=58 width=33) (actual time=0.011..0.011 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
168. 0.001 0.011 ↓ 0.0 0 1

Nested Loop (cost=0.70..46.99 rows=58 width=33) (actual time=0.011..0.011 rows=0 loops=1)

169. 0.010 0.010 ↓ 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.010..0.010 rows=0 loops=1)

  • Index Cond: (payprocessid = 41095)
  • Heap Fetches: 0
170. 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)
171. 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))
172.          

SubPlan (for Nested Loop)

173. 0.000 0.000 ↓ 0.0 0

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

174. 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))
175. 0.004 287.762 ↑ 2.0 1 1

Subquery Scan on *SELECT* 15 (cost=74,324.04..74,324.13 rows=2 width=747) (actual time=287.761..287.762 rows=1 loops=1)

176. 0.001 287.758 ↑ 2.0 1 1

Unique (cost=74,324.04..74,324.10 rows=2 width=743) (actual time=287.758..287.758 rows=1 loops=1)

177. 0.012 287.757 ↑ 2.0 1 1

Sort (cost=74,324.04..74,324.05 rows=2 width=743) (actual time=287.757..287.757 rows=1 loops=1)

  • Sort Key: pe_14.personid, pp_14.lname, pe_14.effectivedate, pe_14.createts, pe_14.endts, (CASE WHEN (pe_14.createts > (SubPlan 3)) 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
178. 0.147 287.745 ↑ 2.0 1 1

Nested Loop (cost=0.41..74,324.03 rows=2 width=743) (actual time=157.434..287.745 rows=1 loops=1)

179. 283.678 283.678 ↑ 11.6 779 1

CTE Scan on payrollpeeps pp_14 (cost=0.00..40,607.80 rows=9,024 width=230) (actual time=22.185..283.678 rows=779 loops=1)

  • Filter: (payprocessid = 41095)
  • Rows Removed by Filter: 2037237
180. 3.879 3.895 ↓ 0.0 0 779

Index Scan using personemploymentenddate on person_employment pe_14 (cost=0.41..3.73 rows=1 width=37) (actual time=0.005..0.005 rows=0 loops=779)

  • Index Cond: ((personid = pp_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 4)))
  • Rows Removed by Filter: 1
181.          

SubPlan (for Index Scan)

182. 0.005 0.016 ↑ 1.0 1 1

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

183. 0.011 0.011 ↓ 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.010..0.011 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
184.          

SubPlan (for Nested Loop)

185. 0.006 0.025 ↑ 1.0 1 1

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

186. 0.019 0.019 ↓ 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.007..0.019 rows=22 loops=1)

  • Index Cond: ((pp_14.payunitid = payunitid) AND (pp_14.payyear = payyear) AND (pp_14.payperiod = payperiod))
187. 0.002 298.645 ↓ 0.0 0 1

Subquery Scan on *SELECT* 16 (cost=44,576.54..44,603.06 rows=624 width=766) (actual time=298.644..298.645 rows=0 loops=1)

188. 0.000 298.643 ↓ 0.0 0 1

Unique (cost=44,576.54..44,595.26 rows=624 width=762) (actual time=298.643..298.643 rows=0 loops=1)

189. 0.014 298.643 ↓ 0.0 0 1

Sort (cost=44,576.54..44,578.10 rows=624 width=762) (actual time=298.643..298.643 rows=0 loops=1)

  • Sort Key: pp_15.personid, pp_15.lname, pl.effectivedate, pl.createts, pl.endts, (CASE WHEN (pl.createts > (SubPlan 2)) THEN 'Not Processed'::text ELSE 'Processed'::text END), pp_15.companyid, pp_15.payunitid, pp_15.payperiod, pp_15.payyear, pp_15.payprocesstype
  • Sort Method: quicksort Memory: 25kB
190. 5.611 298.629 ↓ 0.0 0 1

Merge Join (cost=41,200.94..44,547.57 rows=624 width=762) (actual time=298.629..298.629 rows=0 loops=1)

  • Merge Cond: (pl.personid = pp_15.personid)
  • Join Filter: ((pl.createts >= pp_15.createts) AND (((pp_15.periodstartdate >= pl.effectivedate) AND (pp_15.periodstartdate <= pl.enddate)) OR ((pl.effectivedate >= pp_15.periodstartdate) AND (pl.effectivedate <= pp_15.periodenddate))))
  • Rows Removed by Join Filter: 1208
191. 10.353 10.353 ↑ 1.0 25,225 1

Index Scan using person_locations_personid_idx on person_locations pl (cost=0.29..1,587.20 rows=25,828 width=37) (actual time=0.013..10.353 rows=25,225 loops=1)

  • Filter: ((personlocationtype = ANY ('{P,TL}'::bpchar[])) AND (createts <> endts) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 6803
192. 0.460 282.665 ↑ 7.5 1,207 1

Sort (cost=41,200.65..41,223.21 rows=9,024 width=238) (actual time=282.598..282.665 rows=1,207 loops=1)

  • Sort Key: pp_15.personid
  • Sort Method: quicksort Memory: 134kB
193. 282.205 282.205 ↑ 11.6 779 1

CTE Scan on payrollpeeps pp_15 (cost=0.00..40,607.80 rows=9,024 width=238) (actual time=22.459..282.205 rows=779 loops=1)

  • Filter: (payprocessid = 41095)
  • Rows Removed by Filter: 2037237
194.          

SubPlan (for Merge Join)

195. 0.000 0.000 ↓ 0.0 0

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

196. 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 : 355.055 ms
Execution time : 7,916.985 ms