explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Wjus : after function prop change

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 371.733 ↑ 1.0 1 1
→

Nested Loop Left Join (cost=84,922.27..84,946.13 rows=1 width=421) (actual time=371.723..371.733 rows=1 loops=1)

  • Join Filter: ((((current_setting('synchr.asof'::text))::date) >= pnc_wp.effectivedate) AND (((current_setting('synchr.asof'::text))::date) <= pnc_wp.enddate) AND (pe.personid = pnc_wp.personid) AND (SubPlan 6))
2. 0.001 371.723 ↑ 1.0 1 1
→

Nested Loop Left Join (cost=84,921.85..84,942.52 rows=1 width=492) (actual time=371.714..371.723 rows=1 loops=1)

  • Join Filter: ((((current_setting('synchr.asof'::text))::date) >= pnc_im.effectivedate) AND (((current_setting('synchr.asof'::text))::date) <= pnc_im.enddate) AND (pe.personid = pnc_im.personid) AND (SubPlan 5))
3. 0.005 371.716 ↑ 1.0 1 1
→

Nested Loop Left Join (cost=84,921.44..84,938.92 rows=1 width=469) (actual time=371.707..371.716 rows=1 loops=1)

  • Join Filter: ((((current_setting('synchr.asof'::text))::date) >= pnc_home.effectivedate) AND (((current_setting('synchr.asof'::text))::date) <= pnc_home.enddate) AND (pe.personid = pnc_home.personid) AND (SubPlan 4))
4. 0.001 371.703 ↑ 1.0 1 1
→

Nested Loop Left Join (cost=84,921.02..84,935.32 rows=1 width=446) (actual time=371.695..371.703 rows=1 loops=1)

  • Join Filter: ((((current_setting('synchr.asof'::text))::date) >= pnc.effectivedate) AND (((current_setting('synchr.asof'::text))::date) <= pnc.enddate) AND (pe.personid = pnc.personid) AND (SubPlan 3))
5. 0.001 371.692 ↑ 1.0 1 1
→

Nested Loop Left Join (cost=84,920.61..84,931.72 rows=1 width=423) (actual time=371.683..371.692 rows=1 loops=1)

  • Join Filter: ((((current_setting('synchr.asof'::text))::date) >= pp_m.effectivedate) AND (((current_setting('synchr.asof'::text))::date) <= pp_m.enddate) AND (pe.personid = pp_m.personid) AND (SubPlan 2))
6. 0.002 371.686 ↑ 1.0 1 1
→

Nested Loop Left Join (cost=84,920.32..84,928.25 rows=1 width=411) (actual time=371.677..371.686 rows=1 loops=1)

  • Join Filter: ((((current_setting('synchr.asof'::text))::date) >= pp_w.effectivedate) AND (((current_setting('synchr.asof'::text))::date) <= pp_w.enddate) AND (pe.personid = pp_w.personid) AND (SubPlan 1))
7. 0.001 371.675 ↑ 1.0 1 1
→

Nested Loop (cost=84,920.03..84,924.78 rows=1 width=399) (actual time=371.667..371.675 rows=1 loops=1)

8. 0.002 367.874 ↑ 1.0 1 1
→

Nested Loop Left Join (cost=84,919.74..84,924.46 rows=1 width=367) (actual time=367.866..367.874 rows=1 loops=1)

9. 0.001 367.856 ↑ 1.0 1 1
→

Nested Loop Left Join (cost=84,919.33..84,921.98 rows=1 width=349) (actual time=367.849..367.856 rows=1 loops=1)

  • Join Filter: ((((current_setting('synchr.asof'::text))::date) >= mpn.effectivedate) AND (((current_setting('synchr.asof'::text))::date) <= mpn.enddate))
10. 0.002 367.854 ↑ 1.0 1 1
→

Nested Loop Left Join (cost=84,918.91..84,921.46 rows=1 width=336) (actual time=367.847..367.854 rows=1 loops=1)

  • Join Filter: ((((current_setting('synchr.asof'::text))::date) >= mperspos.effectivedate) AND (((current_setting('synchr.asof'::text))::date) <= mperspos.enddate))
11. 0.002 367.851 ↑ 1.0 1 1
→

Nested Loop Left Join (cost=84,918.62..84,921.06 rows=1 width=327) (actual time=367.844..367.851 rows=1 loops=1)

  • Join Filter: ((((current_setting('synchr.asof'::text))::date) >= mpd.effectivedate) AND (((current_setting('synchr.asof'::text))::date) <= mpd.enddate))
12. 0.002 367.848 ↑ 1.0 1 1
→

Nested Loop Left Join (cost=84,918.33..84,920.53 rows=1 width=313) (actual time=367.841..367.848 rows=1 loops=1)

  • Join Filter: ((((current_setting('synchr.asof'::text))::date) >= mpp.effectivedate) AND (((current_setting('synchr.asof'::text))::date) <= mpp.enddate))
13. 0.009 367.845 ↑ 1.0 1 1
→

Hash Join (cost=84,918.04..84,918.20 rows=1 width=313) (actual time=367.838..367.845 rows=1 loops=1)

  • Hash Cond: ("*SELECT* 1".emplstatus = (pe.emplstatus)::text)
14. 0.003 0.010 ↑ 1.0 6 1
→

Append (cost=0.00..0.12 rows=6 width=156) (actual time=0.002..0.010 rows=6 loops=1)

15. 0.001 0.002 ↑ 1.0 1 1
→

Subquery Scan on *SELECT* 1 (cost=0.00..0.02 rows=1 width=156) (actual time=0.002..0.002 rows=1 loops=1)

16. 0.001 0.001 ↑ 1.0 1 1
→

Result (cost=0.00..0.01 rows=1 width=164) (actual time=0.001..0.001 rows=1 loops=1)

17. 0.000 0.001 ↑ 1.0 1 1
→

Subquery Scan on *SELECT* 2 (cost=0.00..0.02 rows=1 width=156) (actual time=0.001..0.001 rows=1 loops=1)

18. 0.001 0.001 ↑ 1.0 1 1
→

Result (cost=0.00..0.01 rows=1 width=164) (actual time=0.001..0.001 rows=1 loops=1)

19. 0.000 0.001 ↑ 1.0 1 1
→

Subquery Scan on *SELECT* 3 (cost=0.00..0.02 rows=1 width=156) (actual time=0.001..0.001 rows=1 loops=1)

20. 0.001 0.001 ↑ 1.0 1 1
→

Result (cost=0.00..0.01 rows=1 width=164) (actual time=0.000..0.001 rows=1 loops=1)

21. 0.001 0.001 ↑ 1.0 1 1
→

Subquery Scan on *SELECT* 4 (cost=0.00..0.02 rows=1 width=156) (actual time=0.001..0.001 rows=1 loops=1)

22. 0.000 0.000 ↑ 1.0 1 1
→

Result (cost=0.00..0.01 rows=1 width=164) (actual time=0.000..0.000 rows=1 loops=1)

23. 0.000 0.001 ↑ 1.0 1 1
→

Subquery Scan on *SELECT* 5 (cost=0.00..0.02 rows=1 width=156) (actual time=0.001..0.001 rows=1 loops=1)

24. 0.001 0.001 ↑ 1.0 1 1
→

Result (cost=0.00..0.01 rows=1 width=164) (actual time=0.001..0.001 rows=1 loops=1)

25. 0.001 0.001 ↑ 1.0 1 1
→

Subquery Scan on *SELECT* 6 (cost=0.00..0.02 rows=1 width=156) (actual time=0.001..0.001 rows=1 loops=1)

26. 0.000 0.000 ↑ 1.0 1 1
→

Result (cost=0.00..0.01 rows=1 width=164) (actual time=0.000..0.000 rows=1 loops=1)

27. 0.003 367.826 ↑ 1.0 1 1
→

Hash (cost=84,918.03..84,918.03 rows=1 width=191) (actual time=367.826..367.826 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
28. 0.010 367.823 ↑ 1.0 1 1
→

Hash Left Join (cost=84,905.65..84,918.03 rows=1 width=191) (actual time=367.819..367.823 rows=1 loops=1)

  • Hash Cond: ((pe.companyid = wt.companyid) AND (((btrim((pe.emplpermanency)::text) || ':'::text) || COALESCE((pe.companyworkertypepid)::text, ''::text)) = wt.companyworkertype))
29. 0.001 0.083 ↑ 1.0 1 1
→

Nested Loop Left Join (cost=2.53..14.89 rows=1 width=170) (actual time=0.079..0.083 rows=1 loops=1)

  • Join Filter: ((((current_setting('synchr.asof'::text))::date) >= oc.effectivedate) AND (((current_setting('synchr.asof'::text))::date) <= oc.enddate))
30. 0.001 0.081 ↑ 1.0 1 1
→

Nested Loop Left Join (cost=2.25..14.56 rows=1 width=159) (actual time=0.078..0.081 rows=1 loops=1)

  • Join Filter: ((((current_setting('synchr.asof'::text))::date) >= forg.effectivedate) AND (((current_setting('synchr.asof'::text))::date) <= forg.enddate))
31. 0.001 0.078 ↑ 1.0 1 1
→

Nested Loop Left Join (cost=1.83..12.10 rows=1 width=155) (actual time=0.075..0.078 rows=1 loops=1)

  • Join Filter: ((((current_setting('synchr.asof'::text))::date) >= pd.effectivedate) AND (((current_setting('synchr.asof'::text))::date) <= pd.enddate))
32. 0.001 0.076 ↑ 1.0 1 1
→

Nested Loop Left Join (cost=1.54..9.76 rows=1 width=141) (actual time=0.074..0.076 rows=1 loops=1)

  • Join Filter: ((((current_setting('synchr.asof'::text))::date) >= pp.effectivedate) AND (((current_setting('synchr.asof'::text))::date) <= pp.enddate) AND (pe.personid = pp.personid))
33. 0.001 0.063 ↑ 1.0 1 1
→

Nested Loop Left Join (cost=1.12..7.30 rows=1 width=137) (actual time=0.061..0.063 rows=1 loops=1)

  • Join Filter: ((((current_setting('synchr.asof'::text))::date) >= lc.effectivedate) AND (((current_setting('synchr.asof'::text))::date) <= lc.enddate))
34. 0.001 0.061 ↑ 1.0 1 1
→

Nested Loop Left Join (cost=0.84..4.97 rows=1 width=90) (actual time=0.059..0.061 rows=1 loops=1)

  • Join Filter: ((((current_setting('synchr.asof'::text))::date) >= pl.effectivedate) AND (((current_setting('synchr.asof'::text))::date) <= pl.enddate) AND (pe.personid = pl.personid))
35. 0.002 0.047 ↑ 1.0 1 1
→

Nested Loop (cost=0.42..2.51 rows=1 width=86) (actual time=0.045..0.047 rows=1 loops=1)

36. 0.014 0.014 ↑ 1.0 1 1
→

Result (cost=0.01..0.04 rows=1 width=92) (actual time=0.013..0.014 rows=1 loops=1)

  • One-Time Filter: ((current_setting('synchr.companyid'::text))::integer = 52133)
37. 0.031 0.031 ↑ 1.0 1 1
→

Index Scan using personemploymentenddate on person_employment pe (cost=0.41..2.45 rows=1 width=34) (actual time=0.030..0.031 rows=1 loops=1)

  • Index Cond: ((personid = '136011'::bpchar) AND (((current_setting('synchr.asof'::text))::date) <= enddate) AND (((current_setting('synchr.asof'::text))::date) >= effectivedate))
  • Filter: ((companyid = 52133) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 1
38. 0.013 0.013 ↓ 0.0 0 1
→

Index Scan using person_locations_pkey on person_locations pl (cost=0.41..2.45 rows=1 width=25) (actual time=0.013..0.013 rows=0 loops=1)

  • Index Cond: (personid = '136011'::bpchar)
  • Filter: ((personlocationtype = 'P'::bpchar) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
39. 0.001 0.001 ↓ 0.0 0 1
→

Index Scan using locationcodeslocationid on location_codes lc (cost=0.28..2.31 rows=1 width=63) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (pl.locationid = locationid)
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
40. 0.012 0.012 ↓ 0.0 0 1
→

Index Scan using pers_pos_pkey on pers_pos pp (cost=0.41..2.45 rows=1 width=25) (actual time=0.012..0.012 rows=0 loops=1)

  • Index Cond: (personid = '136011'::bpchar)
  • Filter: ((persposrel = 'Occupies'::bpchar) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
41. 0.001 0.001 ↓ 0.0 0 1
→

Index Scan using position_desc_pkey on position_desc pd (cost=0.29..2.32 rows=1 width=26) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (pp.positionid = positionid)
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
42. 0.002 0.002 ↓ 0.0 0 1
→

Index Scan using posorgrelbyposition on pos_org_rel forg (cost=0.41..2.44 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=1)

  • Index Cond: ((positionid = pp.positionid) AND (posorgreltype = 'Member'::bpchar))
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
43. 0.001 0.001 ↓ 0.0 0 1
→

Index Scan using organizationcodeorganizationid on organization_code oc (cost=0.28..0.32 rows=1 width=27) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (forg.organizationid = organizationid)
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
44. 0.004 367.730 ↓ 3.0 9 1
→

Hash (cost=84,903.07..84,903.07 rows=3 width=68) (actual time=367.730..367.730 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
45. 0.002 367.726 ↓ 3.0 9 1
→

Subquery Scan on wt (cost=84,903.01..84,903.07 rows=3 width=68) (actual time=367.724..367.726 rows=9 loops=1)

46. 0.014 367.724 ↓ 3.0 9 1
→

HashAggregate (cost=84,903.01..84,903.04 rows=3 width=100) (actual time=367.723..367.724 rows=9 loops=1)

  • Group Key: cwt.companyid, (((btrim((cwt.emplpermanency)::text) || ':'::text) || (cwt.companyworkertypepid)::text)), (COALESCE(lsv.value, lsv_en.value)), (current_setting('synchr.userid'::text))
47. 0.002 367.710 ↓ 3.0 9 1
→

Append (cost=0.58..84,902.98 rows=3 width=100) (actual time=0.035..367.710 rows=9 loops=1)

48. 0.002 0.067 ↓ 4.0 4 1
→

Result (cost=0.58..6.52 rows=1 width=100) (actual time=0.035..0.067 rows=4 loops=1)

  • One-Time Filter: ((current_setting('synchr.companyid'::text))::integer = 52133)
49. 0.008 0.065 ↓ 4.0 4 1
→

Nested Loop Left Join (cost=0.58..6.52 rows=1 width=100) (actual time=0.033..0.065 rows=4 loops=1)

50. 0.002 0.041 ↓ 4.0 4 1
→

Nested Loop Left Join (cost=0.29..4.17 rows=1 width=60) (actual time=0.023..0.041 rows=4 loops=1)

51. 0.019 0.019 ↓ 4.0 4 1
→

Seq Scan on company_worker_type cwt (cost=0.00..1.86 rows=1 width=24) (actual time=0.014..0.019 rows=4 loops=1)

  • Filter: (active AND (companyid = 52133))
  • Rows Removed by Filter: 65
52. 0.020 0.020 ↑ 1.0 1 4
→

Index Scan using pk_local_string_values on local_string_values lsv_en (cost=0.29..2.30 rows=1 width=40) (actual time=0.005..0.005 rows=1 loops=4)

  • Index Cond: ((cwt.descriptionlocalstringid = localstringid) AND (locale = 'EN_US'::bpchar))
53. 0.016 0.016 ↓ 0.0 0 4
→

Index Scan using pk_local_string_values on local_string_values lsv (cost=0.29..2.31 rows=1 width=40) (actual time=0.004..0.004 rows=0 loops=4)

  • Index Cond: (cwt.descriptionlocalstringid = localstringid)
  • Filter: ((locale)::text = current_setting('synchr.userlocale'::text))
  • Rows Removed by Filter: 1
54. 0.002 367.615 ↓ 3.0 3 1
→

Result (cost=84,879.10..84,892.98 rows=1 width=100) (actual time=367.602..367.615 rows=3 loops=1)

  • One-Time Filter: ((current_setting('synchr.companyid'::text))::integer = 52133)
55. 0.018 367.613 ↓ 3.0 3 1
→

Hash Join (cost=84,879.10..84,892.98 rows=1 width=100) (actual time=367.600..367.613 rows=3 loops=1)

  • Hash Cond: (listpeople.companyworkertype = ((btrim((cwt_1.emplpermanency)::text) || ':'::text) || (cwt_1.companyworkertypepid)::text))
56. 0.046 367.543 ↑ 25.0 8 1
→

Unique (cost=84,872.59..84,882.19 rows=200 width=32) (actual time=367.474..367.543 rows=8 loops=1)

57. 0.235 367.497 ↑ 2.5 764 1
→

Sort (cost=84,872.59..84,877.39 rows=1,921 width=32) (actual time=367.473..367.497 rows=764 loops=1)

  • Sort Key: listpeople.companyworkertype
  • Sort Method: quicksort Memory: 60kB
58. 0.130 367.262 ↑ 2.5 764 1
→

Subquery Scan on listpeople (cost=52,415.81..84,767.82 rows=1,921 width=32) (actual time=170.282..367.262 rows=764 loops=1)

59. 24.237 367.132 ↑ 2.5 764 1
→

Merge Left Join (cost=52,415.81..84,748.61 rows=1,921 width=3,425) (actual time=170.282..367.132 rows=764 loops=1)

  • Merge Cond: (pe_1.personid = ppy.personid)
  • Join Filter: ((pe_1.asof >= ppy.effectivedate) AND (pe_1.asof <= ppy.enddate))
  • Rows Removed by Join Filter: 152
60.          

CTE cte_pe
61. 4.888 4.888 ↑ 3.0 768 1
→

Index Scan using employmentcompanyideffdt on person_employment pe_2 (cost=0.44..1,330.21 rows=2,323 width=211) (actual time=0.035..4.888 rows=768 loops=1)

  • Index Cond: ((companyid = (current_setting('synchr.companyid'::text))::integer) AND ((current_setting('synchr.asof'::text))::date >= effectivedate) AND ((current_setting('synchr.asof'::text))::date <= enddate))
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 1357
62.          

CTE cte_names
63. 0.722 18.569 ↓ 3.0 771 1
→

HashAggregate (cost=3,569.41..3,571.99 rows=258 width=292) (actual time=18.399..18.569 rows=771 loops=1)

  • Group Key: pe_3.personid, pn_3.fname, pn_3.lname, pn_3.nametype
64. 0.088 17.847 ↓ 3.0 771 1
→

Append (cost=0.41..3,566.83 rows=258 width=292) (actual time=0.018..17.847 rows=771 loops=1)

65. 0.513 14.612 ↓ 3.0 764 1
→

Nested Loop (cost=0.41..2,668.04 rows=257 width=71) (actual time=0.017..14.612 rows=764 loops=1)

66. 5.651 5.651 ↑ 3.0 768 1
→

CTE Scan on cte_pe pe_3 (cost=0.00..46.46 rows=2,323 width=56) (actual time=0.000..5.651 rows=768 loops=1)

67. 8.448 8.448 ↑ 1.0 1 768
→

Index Scan using personnamepersonidnametype on person_names pn_3 (cost=0.41..1.12 rows=1 width=40) (actual time=0.011..0.011 rows=1 loops=768)

  • Index Cond: ((personid = pe_3.personid) AND (nametype = 'Legal'::bpchar))
  • Filter: ((pe_3.asof >= effectivedate) AND (pe_3.asof <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 4
68. 0.043 3.147 ↓ 7.0 7 1
→

Hash Join (cost=75.91..896.21 rows=1 width=71) (actual time=2.154..3.147 rows=7 loops=1)

  • Hash Cond: (pn_4.personid = pe_4.personid)
  • Join Filter: ((pe_4.asof >= pn_4.effectivedate) AND (pe_4.asof <= pn_4.enddate))
  • Rows Removed by Join Filter: 20
69. 2.803 2.803 ↓ 1.1 157 1
→

Index Scan using personnamepersonidnametype on person_names pn_4 (cost=0.41..778.46 rows=144 width=40) (actual time=0.016..2.803 rows=157 loops=1)

  • Index Cond: (nametype = 'Pref'::bpchar)
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 30
70. 0.154 0.301 ↑ 3.0 768 1
→

Hash (cost=46.46..46.46 rows=2,323 width=56) (actual time=0.301..0.301 rows=768 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 71kB
71. 0.147 0.147 ↑ 3.0 768 1
→

CTE Scan on cte_pe pe_4 (cost=0.00..46.46 rows=2,323 width=56) (actual time=0.001..0.147 rows=768 loops=1)

72.          

CTE cte_hire
73. 0.352 6.190 ↑ 11.6 768 1
→

Hash Left Join (cost=2,573.11..33,325.95 rows=8,909 width=56) (actual time=4.716..6.190 rows=768 loops=1)

  • Hash Cond: (cte_pe.personid = pe_5.personid)
74. 0.140 0.140 ↑ 3.0 768 1
→

CTE Scan on cte_pe (cost=0.00..46.46 rows=2,323 width=52) (actual time=0.000..0.140 rows=768 loops=1)

75. 0.077 4.710 ↑ 1.1 692 1
→

Hash (cost=2,563.52..2,563.52 rows=767 width=17) (actual time=4.710..4.710 rows=692 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 44kB
76. 0.318 4.633 ↑ 1.1 692 1
→

HashAggregate (cost=2,548.18..2,555.85 rows=767 width=17) (actual time=4.552..4.633 rows=692 loops=1)

  • Group Key: pe_5.personid
77. 0.410 4.315 ↑ 1.0 735 1
→

Nested Loop (cost=0.41..2,544.34 rows=767 width=17) (actual time=0.010..4.315 rows=735 loops=1)

78. 0.065 0.065 ↑ 3.0 768 1
→

CTE Scan on cte_pe cte_pe_1 (cost=0.00..46.46 rows=2,323 width=52) (actual time=0.000..0.065 rows=768 loops=1)

79. 3.840 3.840 ↑ 1.0 1 768
→

Index Scan using personemploymentenddate on person_employment pe_5 (cost=0.41..1.07 rows=1 width=17) (actual time=0.005..0.005 rows=1 loops=768)

  • Index Cond: (personid = cte_pe_1.personid)
  • Filter: ((effectivedate <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts) AND (emplevent = ANY ('{Hire,Rehire,Contract,ContractR}'::bpchar[])))
  • Rows Removed by Filter: 4
80.          

SubPlan (forHash Left Join)
81. 0.076 0.988 ↑ 1.0 1 76
→

Limit (cost=3.44..3.45 rows=1 width=8) (actual time=0.012..0.013 rows=1 loops=76)

82. 0.152 0.912 ↑ 1.0 1 76
→

Sort (cost=3.44..3.45 rows=1 width=8) (actual time=0.012..0.012 rows=1 loops=76)

  • Sort Key: h_1.effectivedate
  • Sort Method: quicksort Memory: 25kB
83. 0.760 0.760 ↑ 1.0 1 76
→

Index Scan using personemploymentenddate on person_employment h_1 (cost=0.41..3.43 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=76)

  • Index Cond: (personid = cte_pe.personid)
  • Filter: ((effectivedate <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 20
84.          

CTE cte_orighire
85. 0.176 5.658 ↑ 11.1 768 1
→

Hash Left Join (cost=2,560.29..2,612.98 rows=8,537 width=56) (actual time=5.434..5.658 rows=768 loops=1)

  • Hash Cond: (cte_pe_2.personid = pe_6.personid)
86. 0.055 0.055 ↑ 3.0 768 1
→

CTE Scan on cte_pe cte_pe_2 (cost=0.00..46.46 rows=2,323 width=56) (actual time=0.000..0.055 rows=768 loops=1)

87. 0.080 5.427 ↑ 1.1 683 1
→

Hash (cost=2,551.11..2,551.11 rows=735 width=17) (actual time=5.427..5.427 rows=683 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 41kB
88. 0.051 5.347 ↑ 1.1 683 1
→

Subquery Scan on pe_6 (cost=2,536.41..2,551.11 rows=735 width=17) (actual time=5.231..5.347 rows=683 loops=1)

89. 0.272 5.296 ↑ 1.1 683 1
→

HashAggregate (cost=2,536.41..2,543.76 rows=735 width=17) (actual time=5.230..5.296 rows=683 loops=1)

  • Group Key: pe_7.personid
90. 0.347 5.024 ↑ 1.1 693 1
→

Nested Loop (cost=0.41..2,532.73 rows=735 width=17) (actual time=0.015..5.024 rows=693 loops=1)

91. 0.069 0.069 ↑ 3.0 768 1
→

CTE Scan on cte_pe cte_pe_3 (cost=0.00..46.46 rows=2,323 width=52) (actual time=0.000..0.069 rows=768 loops=1)

92. 4.608 4.608 ↑ 1.0 1 768
→

Index Scan using personemploymentenddate on person_employment pe_7 (cost=0.41..1.06 rows=1 width=17) (actual time=0.005..0.006 rows=1 loops=768)

  • Index Cond: (personid = cte_pe_3.personid)
  • Filter: ((effectivedate <= enddate) AND (emplevent = ANY ('{Hire,Contract}'::bpchar[])) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 4
93. 0.411 333.916 ↑ 2.5 764 1
→

Nested Loop Left Join (cost=11,574.40..41,693.83 rows=1,921 width=408) (actual time=170.202..333.916 rows=764 loops=1)

94. 0.058 329.685 ↑ 2.5 764 1
→

Nested Loop Left Join (cost=11,572.06..37,160.27 rows=1,921 width=408) (actual time=170.194..329.685 rows=764 loops=1)

95. 0.461 325.043 ↑ 2.5 764 1
→

Nested Loop Left Join (cost=11,569.73..32,626.71 rows=1,921 width=408) (actual time=170.185..325.043 rows=764 loops=1)

96. 4.154 319.234 ↑ 2.5 764 1
→

Merge Left Join (cost=11,567.40..28,093.15 rows=1,921 width=408) (actual time=170.171..319.234 rows=764 loops=1)

  • Merge Cond: (pe_1.personid = pnc_home_1.personid)
  • Join Filter: ((pe_1.asof >= pnc_home_1.effectivedate) AND (pe_1.asof <= pnc_home_1.enddate))
  • Rows Removed by Join Filter: 188
97. 2.972 307.073 ↑ 2.5 764 1
→

Merge Left Join (cost=11,566.98..26,390.58 rows=1,921 width=408) (actual time=170.163..307.073 rows=764 loops=1)

  • Merge Cond: (pe_1.personid = pnc_1.personid)
  • Join Filter: ((pe_1.asof >= pnc_1.effectivedate) AND (pe_1.asof <= pnc_1.enddate))
  • Rows Removed by Join Filter: 116
98. 7.503 295.138 ↑ 2.5 764 1
→

Merge Left Join (cost=11,566.57..24,709.39 rows=1,921 width=408) (actual time=170.143..295.138 rows=764 loops=1)

  • Merge Cond: (pe_1.personid = pnl.personid)
  • Join Filter: ((pe_1.asof >= pnl.effectivedate) AND (pe_1.asof <= pnl.enddate))
  • Rows Removed by Join Filter: 3102
99. 4.737 256.838 ↑ 2.5 764 1
→

Merge Left Join (cost=11,566.15..21,674.52 rows=1,921 width=88) (actual time=170.079..256.838 rows=764 loops=1)

  • Merge Cond: (pe_1.personid = pi_empno.personid)
100. 5.595 241.309 ↑ 2.5 764 1
→

Merge Left Join (cost=11,565.74..19,053.45 rows=1,921 width=88) (actual time=170.063..241.309 rows=764 loops=1)

  • Merge Cond: (pe_1.personid = pi_ssn.personid)
101. 2.973 221.752 ↑ 2.5 764 1
→

Merge Left Join (cost=11,565.32..16,337.43 rows=1,921 width=88) (actual time=170.041..221.752 rows=764 loops=1)

  • Merge Cond: (pe_1.personid = pm.personid)
  • Join Filter: ((pe_1.asof >= pm.effectivedate) AND (pe_1.asof <= pm.enddate))
  • Rows Removed by Join Filter: 44
102. 0.571 214.429 ↑ 2.5 764 1
→

Merge Left Join (cost=11,565.03..15,672.37 rows=1,921 width=88) (actual time=170.027..214.429 rows=764 loops=1)

  • Merge Cond: (pe_1.personid = pds.personid)
  • Join Filter: ((pe_1.asof >= pds.effectivedate) AND (pe_1.asof <= pds.enddate))
103. 6.288 213.324 ↑ 2.5 764 1
→

Merge Left Join (cost=11,564.76..15,566.03 rows=1,921 width=88) (actual time=170.014..213.324 rows=764 loops=1)

  • Merge Cond: (pe_1.personid = pv.personid)
  • Join Filter: ((pe_1.asof >= pv.effectivedate) AND (pe_1.asof <= pv.enddate))
  • Rows Removed by Join Filter: 83
104. 6.770 195.278 ↑ 2.5 764 1
→

Merge Left Join (cost=11,564.34..13,967.61 rows=1,921 width=88) (actual time=169.989..195.278 rows=764 loops=1)

  • Merge Cond: (pe_1.personid = pa.personid)
  • Join Filter: ((pe_1.asof >= pa.effectivedate) AND (pe_1.asof <= pa.enddate))
  • Rows Removed by Join Filter: 151
105. 0.550 170.630 ↑ 2.5 764 1
→

Merge Left Join (cost=11,563.93..11,599.92 rows=1,921 width=88) (actual time=169.947..170.630 rows=764 loops=1)

  • Merge Cond: (pe_1.personid = pvs.personid)
106. 1.371 168.048 ↑ 2.5 764 1
→

Sort (cost=11,475.40..11,480.20 rows=1,921 width=88) (actual time=167.956..168.048 rows=764 loops=1)

  • Sort Key: pe_1.personid
  • Sort Method: quicksort Memory: 84kB
107. 0.089 166.677 ↑ 2.5 764 1
→

Hash Right Join (cost=11,258.66..11,370.63 rows=1,921 width=88) (actual time=166.577..166.677 rows=764 loops=1)

  • Hash Cond: ((sgr.companyid = pe_1.companyid) AND (sgr.grade = pd_1.grade) AND (sgr.edtcode = pc.earningscode) AND (sgr.salarygraderegion = lc_1.salarygraderegion))
  • Join Filter: ((pe_1.asof >= sgr.effectivedate) AND (pe_1.asof <= sgr.enddate))
  • Rows Removed by Join Filter: 32
108. 0.032 0.032 ↑ 1.0 81 1
→

Index Scan using salarygraderangescompanyid on salary_grade_ranges sgr (cost=0.29..33.67 rows=81 width=52) (actual time=0.011..0.032 rows=81 loops=1)

  • Index Cond: (companyid = (current_setting('synchr.companyid'::text))::integer)
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
109. 0.436 166.556 ↑ 2.5 764 1
→

Hash (cost=11,219.95..11,219.95 rows=1,921 width=129) (actual time=166.556..166.556 rows=764 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 72kB
110. 0.496 166.120 ↑ 2.5 764 1
→

Hash Left Join (cost=766.92..11,219.95 rows=1,921 width=129) (actual time=125.654..166.120 rows=764 loops=1)

  • Hash Cond: (pl_1.locationid = lc_1.locationid)
  • Join Filter: ((pe_1.asof >= lc_1.effectivedate) AND (pe_1.asof <= lc_1.enddate))
  • Rows Removed by Join Filter: 576
111. 0.282 163.599 ↑ 2.5 764 1
→

Nested Loop Left Join (cost=435.63..10,806.82 rows=1,921 width=120) (actual time=123.617..163.599 rows=764 loops=1)

112. 0.658 157.969 ↑ 2.5 764 1
→

Nested Loop Left Join (cost=435.22..4,179.61 rows=1,921 width=109) (actual time=123.601..157.969 rows=764 loops=1)

  • Join Filter: ((pe_1.asof >= pcp.effectivedate) AND (pe_1.asof <= pcp.enddate))
  • Rows Removed by Join Filter: 10
113. 0.300 156.547 ↑ 2.5 764 1
→

Nested Loop Left Join (cost=434.93..3,529.39 rows=1,921 width=113) (actual time=123.594..156.547 rows=764 loops=1)

  • Join Filter: ((pe_1.asof >= mpd_1.effectivedate) AND (pe_1.asof <= mpd_1.enddate))
  • Rows Removed by Join Filter: 320
114. 0.467 155.483 ↑ 2.5 764 1
→

Nested Loop Left Join (cost=434.64..2,512.74 rows=1,921 width=117) (actual time=123.589..155.483 rows=764 loops=1)

  • Join Filter: ((pe_1.asof >= ppos.effectivedate) AND (pe_1.asof <= ppos.enddate))
  • Rows Removed by Join Filter: 78
115. 0.459 154.252 ↑ 2.5 764 1
→

Nested Loop Left Join (cost=434.35..1,865.07 rows=1,921 width=113) (actual time=123.583..154.252 rows=764 loops=1)

116. 0.250 127.817 ↑ 2.5 764 1
→

Hash Left Join (cost=434.10..1,826.40 rows=1,921 width=113) (actual time=123.417..127.817 rows=764 loops=1)

  • Hash Cond: (pd_1.jobid = jd.jobid)
  • Join Filter: ((pe_1.asof >= jd.effectivedate) AND (pe_1.asof <= jd.enddate))
  • Rows Removed by Join Filter: 7
117. 0.458 127.169 ↑ 2.5 764 1
→

Nested Loop Left Join (cost=349.80..1,620.59 rows=1,921 width=117) (actual time=123.012..127.169 rows=764 loops=1)

  • Join Filter: ((pe_1.asof >= mo.effectivedate) AND (pe_1.asof <= mo.enddate) AND (fo.posorgreltype = 'Matrix'::bpchar))
  • Rows Removed by Join Filter: 1132
118. 0.408 125.183 ↑ 2.5 764 1
→

Hash Right Join (cost=349.39..613.54 rows=1,921 width=128) (actual time=123.005..125.183 rows=764 loops=1)

  • Hash Cond: (h.personid = pe_1.personid)
119. 6.492 6.492 ↑ 11.6 768 1
→

CTE Scan on cte_hire h (cost=0.00..178.18 rows=8,909 width=52) (actual time=4.716..6.492 rows=768 loops=1)

120. 0.140 118.283 ↓ 17.8 764 1
→

Hash (cost=348.85..348.85 rows=43 width=128) (actual time=118.283..118.283 rows=764 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 68kB
121. 0.228 118.143 ↓ 17.8 764 1
→

Hash Right Join (cost=145.67..348.85 rows=43 width=128) (actual time=117.558..118.143 rows=764 loops=1)

  • Hash Cond: (oh.personid = pe_1.personid)
122. 5.797 5.797 ↑ 11.1 768 1
→

CTE Scan on cte_orighire oh (cost=0.00..170.74 rows=8,537 width=52) (actual time=5.436..5.797 rows=768 loops=1)

123. 0.371 112.118 ↓ 764.0 764 1
→

Hash (cost=145.65..145.65 rows=1 width=128) (actual time=112.118..112.118 rows=764 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 68kB
124. 0.694 111.747 ↓ 764.0 764 1
→

Nested Loop Left Join (cost=7.35..145.65 rows=1 width=128) (actual time=19.043..111.747 rows=764 loops=1)

  • Join Filter: ((pe_1.asof >= fo.effectivedate) AND (pe_1.asof <= fo.enddate))
  • Rows Removed by Join Filter: 56
125. 0.102 109.525 ↓ 764.0 764 1
→

Nested Loop Left Join (cost=6.94..145.17 rows=1 width=117) (actual time=19.033..109.525 rows=764 loops=1)

  • Join Filter: ((pe_1.asof >= pd_1.effectivedate) AND (pe_1.asof <= pd_1.enddate))
  • Rows Removed by Join Filter: 238
126. 0.761 107.131 ↓ 764.0 764 1
→

Nested Loop Left Join (cost=6.64..144.72 rows=1 width=100) (actual time=19.024..107.131 rows=764 loops=1)

127. 0.604 101.786 ↓ 764.0 764 1
→

Nested Loop Left Join (cost=6.23..142.26 rows=1 width=96) (actual time=19.006..101.786 rows=764 loops=1)

128. 0.743 95.834 ↓ 764.0 764 1
→

Nested Loop (cost=5.81..139.80 rows=1 width=92) (actual time=18.989..95.834 rows=764 loops=1)

129. 0.627 0.627 ↓ 64.0 768 1
→

CTE Scan on cte_pe pe_1 (cost=0.00..69.69 rows=12 width=92) (actual time=0.040..0.627 rows=768 loops=1)

  • Filter: (companyid = (current_setting('synchr.companyid'::text))::integer)
130. 0.000 94.464 ↑ 1.0 1 768
→

Subquery Scan on pn_1 (cost=5.81..5.83 rows=1 width=52) (actual time=0.123..0.123 rows=1 loops=768)

  • Filter: (pe_1.personid = pn_1.personid)
131. 0.768 94.464 ↑ 1.0 1 768
→

Limit (cost=5.81..5.82 rows=1 width=292) (actual time=0.123..0.123 rows=1 loops=768)

132. 0.768 93.696 ↑ 1.0 1 768
→

Sort (cost=5.81..5.82 rows=1 width=292) (actual time=0.122..0.122 rows=1 loops=768)

  • Sort Key: pn_2.nametype DESC
  • Sort Method: quicksort Memory: 25kB
133. 92.928 92.928 ↑ 1.0 1 768
→

CTE Scan on cte_names pn_2 (cost=0.00..5.80 rows=1 width=292) (actual time=0.073..0.121 rows=1 loops=768)

  • Filter: (personid = pe_1.personid)
  • Rows Removed by Filter: 770
134. 5.348 5.348 ↑ 1.0 1 764
→

Index Scan using person_locations_pkey on person_locations pl_1 (cost=0.41..2.45 rows=1 width=25) (actual time=0.007..0.007 rows=1 loops=764)

  • Index Cond: (pe_1.personid = personid)
  • Filter: ((personlocationtype = 'P'::bpchar) AND (pe_1.asof >= effectivedate) AND (pe_1.asof <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 3
135. 4.584 4.584 ↑ 1.0 1 764
→

Index Scan using pers_pospersendeff on pers_pos pp_1 (cost=0.41..2.45 rows=1 width=25) (actual time=0.006..0.006 rows=1 loops=764)

  • Index Cond: ((pe_1.personid = personid) AND (pe_1.asof <= enddate) AND (pe_1.asof >= effectivedate))
  • Filter: ((persposrel = 'Occupies'::bpchar) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 0
136. 2.292 2.292 ↑ 1.0 1 764
→

Index Scan using position_desc_pkey on position_desc pd_1 (cost=0.29..0.44 rows=1 width=29) (actual time=0.002..0.003 rows=1 loops=764)

  • Index Cond: (pp_1.positionid = positionid)
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 0
137. 1.528 1.528 ↑ 1.0 1 764
→

Index Scan using posorgrelbyposition on pos_org_rel fo (cost=0.41..0.46 rows=1 width=23) (actual time=0.002..0.002 rows=1 loops=764)

  • Index Cond: ((pd_1.positionid = positionid) AND (posorgreltype = 'Member'::bpchar))
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 0
138. 1.528 1.528 ↑ 2.0 1 764
→

Index Scan using posorgrelbyposition on pos_org_rel mo (cost=0.41..0.49 rows=2 width=12) (actual time=0.002..0.002 rows=1 loops=764)

  • Index Cond: (pd_1.positionid = positionid)
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 0
139. 0.100 0.398 ↓ 1.0 987 1
→

Hash (cost=72.06..72.06 rows=979 width=12) (actual time=0.398..0.398 rows=987 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 51kB
140. 0.298 0.298 ↓ 1.0 987 1
→

Seq Scan on job_desc jd (cost=0.00..72.06 rows=979 width=12) (actual time=0.006..0.298 rows=987 loops=1)

  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 229
141. 25.976 25.976 ↓ 0.0 0 764
→

Function Scan on get_manager mgr (cost=0.25..0.26 rows=1 width=0) (actual time=0.034..0.034 rows=0 loops=764)

142. 0.764 0.764 ↓ 0.0 0 764
→

Index Scan using pos_pos_topositionid on pos_pos ppos (cost=0.29..0.32 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=764)

  • Index Cond: (pd_1.positionid = topositionid)
  • Filter: ((posposrel = 'Manages'::bpchar) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 0
143. 0.764 0.764 ↑ 1.0 1 764
→

Index Scan using position_desc_pkey on position_desc mpd_1 (cost=0.29..0.51 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=764)

  • Index Cond: (ppos.positionid = positionid)
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 0
144. 0.764 0.764 ↑ 1.0 1 764
→

Index Scan using position_comp_plan_pkey on position_comp_plan pcp (cost=0.29..0.32 rows=1 width=12) (actual time=0.001..0.001 rows=1 loops=764)

  • Index Cond: (pd_1.positionid = positionid)
  • Filter: ((positioncompplanreltype = 'Main'::bpchar) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 0
145. 0.000 5.348 ↑ 1.0 1 764
→

Subquery Scan on pc (cost=0.41..3.44 rows=1 width=27) (actual time=0.007..0.007 rows=1 loops=764)

  • Filter: (pe_1.personid = pc.personid)
146. 0.764 5.348 ↑ 1.0 1 764
→

Limit (cost=0.41..3.43 rows=1 width=223) (actual time=0.007..0.007 rows=1 loops=764)

147. 4.584 4.584 ↑ 1.0 1 764
→

Index Scan using person_compensation_pkey on person_compensation pc_1 (cost=0.41..3.43 rows=1 width=223) (actual time=0.006..0.006 rows=1 loops=764)

  • Index Cond: (personid = pe_1.personid)
  • Filter: ((pe_1.asof >= effectivedate) AND (pe_1.asof <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts) AND (earningscode = ANY ('{Regular,RegHrly,1099H,1099Earns}'::bpchar[])))
  • Rows Removed by Filter: 1
148. 0.542 2.025 ↑ 1.0 4,786 1
→

Hash (cost=271.32..271.32 rows=4,797 width=25) (actual time=2.025..2.025 rows=4,786 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 276kB
149. 1.483 1.483 ↑ 1.0 4,786 1
→

Seq Scan on location_codes lc_1 (cost=0.00..271.32 rows=4,797 width=25) (actual time=0.005..1.483 rows=4,786 loops=1)

  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 1995
150. 1.835 2.032 ↑ 1.1 892 1
→

Sort (cost=88.53..90.98 rows=980 width=13) (actual time=1.988..2.032 rows=892 loops=1)

  • Sort Key: pvs.personid
  • Sort Method: quicksort Memory: 66kB
151. 0.197 0.197 ↑ 1.1 892 1
→

Seq Scan on person_veteran_status pvs (cost=0.00..39.84 rows=980 width=13) (actual time=0.007..0.197 rows=892 loops=1)

  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 331
152. 17.878 17.878 ↓ 1.0 31,791 1
→

Index Scan using persaddrtypeeffective on person_address pa (cost=0.41..2,255.83 rows=31,093 width=21) (actual time=0.014..17.878 rows=31,791 loops=1)

  • Index Cond: (addresstype = 'Res'::bpchar)
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 3666
153. 11.758 11.758 ↑ 1.0 29,039 1
→

Index Scan using person_vitals_pkey on person_vitals pv (cost=0.41..1,491.14 rows=29,226 width=21) (actual time=0.009..11.758 rows=29,039 loops=1)

  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 4347
154. 0.097 0.534 ↑ 1.1 1,098 1
→

Materialize (cost=0.28..61.83 rows=1,171 width=21) (actual time=0.010..0.534 rows=1,098 loops=1)

155. 0.437 0.437 ↑ 1.1 1,098 1
→

Index Scan using persondisabilitypersonid on person_disability pds (cost=0.28..58.90 rows=1,171 width=21) (actual time=0.009..0.437 rows=1,098 loops=1)

  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 243
156. 4.350 4.350 ↑ 1.0 13,375 1
→

Index Scan using person_maritalstatus_pkey on person_maritalstatus pm (cost=0.29..592.56 rows=13,444 width=21) (actual time=0.009..4.350 rows=13,375 loops=1)

  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 163
157. 13.962 13.962 ↑ 1.0 24,926 1
→

Index Scan using person_identity_pkey on person_identity pi_ssn (cost=0.42..2,632.34 rows=25,676 width=13) (actual time=0.013..13.962 rows=24,926 loops=1)

  • Index Cond: (identitytype = 'SSN'::bpchar)
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 5684
158. 10.792 10.792 ↑ 1.1 20,450 1
→

Index Scan using person_identity_pkey on person_identity pi_empno (cost=0.42..2,548.08 rows=22,196 width=13) (actual time=0.011..10.792 rows=20,450 loops=1)

  • Index Cond: (identitytype = 'EmpNo'::bpchar)
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 3748
159. 30.797 30.797 ↑ 1.0 35,891 1
→

Index Scan using personnamepersonidnametype on person_names pnl (cost=0.41..2,910.59 rows=36,305 width=341) (actual time=0.025..30.797 rows=35,891 loops=1)

  • Index Cond: (nametype = 'Legal'::bpchar)
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 5955
160. 8.963 8.963 ↑ 1.0 12,705 1
→

Index Scan using person_net_contacts_pkey on person_net_contacts pnc_1 (cost=0.41..1,631.05 rows=12,963 width=21) (actual time=0.010..8.963 rows=12,705 loops=1)

  • Filter: ((netcontacttype = 'Email'::bpchar) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 20944
161. 8.007 8.007 ↑ 1.0 18,802 1
→

Index Scan using person_net_contacts_pkey on person_net_contacts pnc_home_1 (cost=0.41..1,631.05 rows=19,073 width=21) (actual time=0.005..8.007 rows=18,802 loops=1)

  • Filter: ((netcontacttype = 'HomeEmail'::bpchar) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 14849
162. 0.000 5.348 ↓ 0.0 0 764
→

Subquery Scan on pp_w_1 (cost=2.33..2.35 rows=1 width=13) (actual time=0.007..0.007 rows=0 loops=764)

  • Filter: (pe_1.personid = pp_w_1.personid)
163. 0.000 5.348 ↓ 0.0 0 764
→

Limit (cost=2.33..2.34 rows=1 width=485) (actual time=0.007..0.007 rows=0 loops=764)

164. 1.528 5.348 ↓ 0.0 0 764
→

Sort (cost=2.33..2.34 rows=1 width=485) (actual time=0.007..0.007 rows=0 loops=764)

  • Sort Key: pp_w_2.effectivedate DESC
  • Sort Method: quicksort Memory: 25kB
165. 3.820 3.820 ↓ 0.0 0 764
→

Index Scan using person_phone_contacts_pkey on person_phone_contacts pp_w_2 (cost=0.29..2.32 rows=1 width=485) (actual time=0.005..0.005 rows=0 loops=764)

  • Index Cond: (pe_1.personid = personid)
  • Filter: ((pe_1.asof >= effectivedate) AND (pe_1.asof <= enddate) AND (phonecontacttype = 'BUSN'::bpchar) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 3
166. 0.764 4.584 ↓ 0.0 0 764
→

Subquery Scan on pp_h (cost=2.33..2.35 rows=1 width=13) (actual time=0.006..0.006 rows=0 loops=764)

  • Filter: (pe_1.personid = pp_h.personid)
167. 0.000 3.820 ↓ 0.0 0 764
→

Limit (cost=2.33..2.34 rows=1 width=485) (actual time=0.005..0.005 rows=0 loops=764)

168. 0.764 3.820 ↓ 0.0 0 764
→

Sort (cost=2.33..2.34 rows=1 width=485) (actual time=0.005..0.005 rows=0 loops=764)

  • Sort Key: pp_h_1.effectivedate DESC
  • Sort Method: quicksort Memory: 25kB
169. 3.056 3.056 ↓ 0.0 0 764
→

Index Scan using person_phone_contacts_pkey on person_phone_contacts pp_h_1 (cost=0.29..2.32 rows=1 width=485) (actual time=0.004..0.004 rows=0 loops=764)

  • Index Cond: (pe_1.personid = personid)
  • Filter: ((pe_1.asof >= effectivedate) AND (pe_1.asof <= enddate) AND (phonecontacttype = 'Home'::bpchar) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 2
170. 0.000 3.820 ↓ 0.0 0 764
→

Subquery Scan on pp_m_1 (cost=2.33..2.35 rows=1 width=13) (actual time=0.005..0.005 rows=0 loops=764)

  • Filter: (pe_1.personid = pp_m_1.personid)
171. 0.000 3.820 ↓ 0.0 0 764
→

Limit (cost=2.33..2.34 rows=1 width=485) (actual time=0.005..0.005 rows=0 loops=764)

172. 0.764 3.820 ↓ 0.0 0 764
→

Sort (cost=2.33..2.34 rows=1 width=485) (actual time=0.005..0.005 rows=0 loops=764)

  • Sort Key: pp_m_2.effectivedate DESC
  • Sort Method: quicksort Memory: 25kB
173. 3.056 3.056 ↓ 0.0 0 764
→

Index Scan using person_phone_contacts_pkey on person_phone_contacts pp_m_2 (cost=0.29..2.32 rows=1 width=485) (actual time=0.004..0.004 rows=0 loops=764)

  • Index Cond: (pe_1.personid = personid)
  • Filter: ((pe_1.asof >= effectivedate) AND (pe_1.asof <= enddate) AND (phonecontacttype = 'Mobile'::bpchar) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 3
174. 8.979 8.979 ↓ 1.0 22,413 1
→

Index Scan using person_payroll_pkey on person_payroll ppy (cost=0.29..1,162.73 rows=22,313 width=25) (actual time=0.008..8.979 rows=22,413 loops=1)

  • Filter: ((payunitrelationship = 'M'::bpchar) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 2358
175. 0.007 0.052 ↓ 4.0 4 1
→

Hash (cost=6.49..6.49 rows=1 width=92) (actual time=0.052..0.052 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
176. 0.004 0.045 ↓ 4.0 4 1
→

Nested Loop Left Join (cost=0.57..6.49 rows=1 width=92) (actual time=0.019..0.045 rows=4 loops=1)

177. 0.004 0.033 ↓ 4.0 4 1
→

Nested Loop Left Join (cost=0.29..4.18 rows=1 width=60) (actual time=0.014..0.033 rows=4 loops=1)

178. 0.013 0.013 ↓ 4.0 4 1
→

Seq Scan on company_worker_type cwt_1 (cost=0.00..1.86 rows=1 width=24) (actual time=0.007..0.013 rows=4 loops=1)

  • Filter: ((NOT active) AND (companyid = 52133))
  • Rows Removed by Filter: 65
179. 0.016 0.016 ↓ 0.0 0 4
→

Index Scan using pk_local_string_values on local_string_values lsv_1 (cost=0.29..2.31 rows=1 width=40) (actual time=0.004..0.004 rows=0 loops=4)

  • Index Cond: (cwt_1.descriptionlocalstringid = localstringid)
  • Filter: ((locale)::text = current_setting('synchr.userlocale'::text))
  • Rows Removed by Filter: 1
180. 0.008 0.008 ↑ 1.0 1 4
→

Index Scan using pk_local_string_values on local_string_values lsv_en_1 (cost=0.29..2.30 rows=1 width=40) (actual time=0.002..0.002 rows=1 loops=4)

  • Index Cond: ((cwt_1.descriptionlocalstringid = localstringid) AND (locale = 'EN_US'::bpchar))
181. 0.001 0.026 ↓ 2.0 2 1
→

Subquery Scan on *SELECT* 3_1 (cost=0.01..3.47 rows=1 width=100) (actual time=0.024..0.026 rows=2 loops=1)

182. 0.003 0.025 ↓ 2.0 2 1
→

Result (cost=0.01..3.46 rows=1 width=126) (actual time=0.024..0.025 rows=2 loops=1)

  • One-Time Filter: ((current_setting('synchr.companyid'::text))::integer = 52133)
183. 0.004 0.022 ↓ 2.0 2 1
→

Nested Loop Left Join (cost=0.01..3.46 rows=1 width=126) (actual time=0.021..0.022 rows=2 loops=1)

  • Join Filter: (empl_permanency.emplpermanency = wt_1.emplpermanency)
  • Filter: (wt_1.companyworkertypepid IS NULL)
184. 0.006 0.006 ↑ 1.0 2 1
→

Seq Scan on empl_permanency (cost=0.00..1.02 rows=2 width=70) (actual time=0.005..0.006 rows=2 loops=1)

185. 0.001 0.012 ↓ 0.0 0 2
→

Materialize (cost=0.00..2.38 rows=1 width=16) (actual time=0.006..0.006 rows=0 loops=2)

186. 0.011 0.011 ↓ 0.0 0 1
→

Seq Scan on company_worker_type wt_1 (cost=0.00..2.38 rows=1 width=16) (actual time=0.011..0.011 rows=0 loops=1)

  • Filter: ((NOT active) AND global AND (companyid = (current_setting('synchr.companyid'::text))::integer))
  • Rows Removed by Filter: 69
187. 0.001 0.001 ↓ 0.0 0 1
→

Index Scan using pos_pos_topositionid on pos_pos mpp (cost=0.29..2.32 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (topositionid = pp.positionid)
  • Filter: ((posposrel = 'Manages'::bpchar) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
188. 0.001 0.001 ↓ 0.0 0 1
→

Index Scan using position_desc_pkey on position_desc mpd (cost=0.29..0.51 rows=1 width=26) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (mpp.positionid = positionid)
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
189. 0.001 0.001 ↓ 0.0 0 1
→

Index Scan using pers_pospositionid on pers_pos mperspos (cost=0.29..0.38 rows=1 width=25) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (mpp.positionid = positionid)
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
190. 0.001 0.001 ↓ 0.0 0 1
→

Index Scan using personnamepersonidnametype on person_names mpn (cost=0.41..0.51 rows=1 width=34) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: ((mperspos.personid = personid) AND (nametype = 'Legal'::bpchar))
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
191. 0.002 0.016 ↑ 1.0 1 1
→

Subquery Scan on pn (cost=0.41..2.47 rows=1 width=31) (actual time=0.015..0.016 rows=1 loops=1)

  • Filter: ((pn.personid = '136011'::bpchar) AND (pe.personid = pn.personid))
192. 0.001 0.014 ↑ 1.0 1 1
→

Limit (cost=0.41..2.45 rows=1 width=993) (actual time=0.014..0.014 rows=1 loops=1)

193. 0.013 0.013 ↑ 1.0 1 1
→

Index Scan Backward using personnamepersonidnametype on person_names pn_1_1 (cost=0.41..2.45 rows=1 width=993) (actual time=0.013..0.013 rows=1 loops=1)

  • Index Cond: (personid = pe.personid)
  • Filter: ((((current_setting('synchr.asof'::text))::date) >= effectivedate) AND (((current_setting('synchr.asof'::text))::date) <= enddate) AND (nametype = ANY ('{Legal,Pref}'::bpchar[])) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
194. 0.006 3.800 ↑ 1.0 1 1
→

Aggregate (cost=0.29..0.30 rows=1 width=32) (actual time=3.800..3.800 rows=1 loops=1)

195. 3.794 3.794 ↑ 1.0 3 1
→

Function Scan on get_privacy_codes (cost=0.25..0.28 rows=3 width=32) (actual time=3.794..3.794 rows=3 loops=1)

196. 0.009 0.009 ↓ 0.0 0 1
→

Index Scan using person_phone_contacts_pkey on person_phone_contacts pp_w (cost=0.29..2.32 rows=1 width=35) (actual time=0.009..0.009 rows=0 loops=1)

  • Index Cond: (personid = '136011'::bpchar)
  • Filter: ((phonecontacttype = 'BUSN'::bpchar) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 1
197.          

SubPlan (forNested Loop Left Join)
198. 0.000 0.000 ↓ 0.0 0
→

Result (cost=0.00..2.02 rows=100 width=8) (never executed)

199. 0.000 0.000 ↓ 0.0 0
→

ProjectSet (cost=0.00..0.52 rows=100 width=32) (never executed)

200. 0.000 0.000 ↓ 0.0 0
→

Result (cost=0.00..0.01 rows=1 width=0) (never executed)

201. 0.005 0.005 ↓ 0.0 0 1
→

Index Scan using person_phone_contacts_pkey on person_phone_contacts pp_m (cost=0.29..2.32 rows=1 width=35) (actual time=0.005..0.005 rows=0 loops=1)

  • Index Cond: (personid = '136011'::bpchar)
  • Filter: ((phonecontacttype = 'Mobile'::bpchar) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 1
202.          

SubPlan (forNested Loop Left Join)
203. 0.000 0.000 ↓ 0.0 0
→

Result (cost=0.00..2.02 rows=100 width=8) (never executed)

204. 0.000 0.000 ↓ 0.0 0
→

ProjectSet (cost=0.00..0.52 rows=100 width=32) (never executed)

205. 0.000 0.000 ↓ 0.0 0
→

Result (cost=0.00..0.01 rows=1 width=0) (never executed)

206. 0.010 0.010 ↓ 0.0 0 1
→

Index Scan using person_net_contacts_pkey on person_net_contacts pnc (cost=0.41..2.45 rows=1 width=46) (actual time=0.010..0.010 rows=0 loops=1)

  • Index Cond: (personid = '136011'::bpchar)
  • Filter: ((netcontacttype = 'Email'::bpchar) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 1
207.          

SubPlan (forNested Loop Left Join)
208. 0.000 0.000 ↓ 0.0 0
→

Result (cost=0.00..2.02 rows=100 width=8) (never executed)

209. 0.000 0.000 ↓ 0.0 0
→

ProjectSet (cost=0.00..0.52 rows=100 width=32) (never executed)

210. 0.000 0.000 ↓ 0.0 0
→

Result (cost=0.00..0.01 rows=1 width=0) (never executed)

211. 0.005 0.005 ↑ 1.0 1 1
→

Index Scan using person_net_contacts_pkey on person_net_contacts pnc_home (cost=0.41..2.45 rows=1 width=46) (actual time=0.005..0.005 rows=1 loops=1)

  • Index Cond: (personid = '136011'::bpchar)
  • Filter: ((netcontacttype = 'HomeEmail'::bpchar) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
212.          

SubPlan (forNested Loop Left Join)
213. 0.000 0.003 ↑ 100.0 1 1
→

Result (cost=0.00..2.02 rows=100 width=8) (actual time=0.003..0.003 rows=1 loops=1)

214. 0.003 0.003 ↑ 100.0 1 1
→

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.003..0.003 rows=1 loops=1)

215. 0.000 0.000 ↑ 1.0 1 1
→

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=1)

216. 0.006 0.006 ↓ 0.0 0 1
→

Index Scan using person_net_contacts_pkey on person_net_contacts pnc_im (cost=0.41..2.45 rows=1 width=46) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: (personid = '136011'::bpchar)
  • Filter: ((netcontacttype = 'IM'::bpchar) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 1
217.          

SubPlan (forNested Loop Left Join)
218. 0.000 0.000 ↓ 0.0 0
→

Result (cost=0.00..2.02 rows=100 width=8) (never executed)

219. 0.000 0.000 ↓ 0.0 0
→

ProjectSet (cost=0.00..0.52 rows=100 width=32) (never executed)

220. 0.000 0.000 ↓ 0.0 0
→

Result (cost=0.00..0.01 rows=1 width=0) (never executed)

221. 0.006 0.006 ↓ 0.0 0 1
→

Index Scan using person_net_contacts_pkey on person_net_contacts pnc_wp (cost=0.41..2.45 rows=1 width=46) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: (personid = '136011'::bpchar)
  • Filter: ((netcontacttype = 'WebPage'::bpchar) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 1
222.          

SubPlan (forNested Loop Left Join)
223. 0.000 0.000 ↓ 0.0 0
→

Result (cost=0.00..2.02 rows=100 width=8) (never executed)

224. 0.000 0.000 ↓ 0.0 0
→

ProjectSet (cost=0.00..0.52 rows=100 width=32) (never executed)

225. 0.000 0.000 ↓ 0.0 0
→

Result (cost=0.00..0.01 rows=1 width=0) (never executed)