explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2BIW : d1.sql

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 508.087 ↑ 1.0 1 1

Nested Loop Left Join (cost=15,630,984.15..15,631,005.63 rows=1 width=421) (actual time=508.075..508.087 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 508.077 ↑ 1.0 1 1

Nested Loop Left Join (cost=15,630,983.74..15,631,002.02 rows=1 width=492) (actual time=508.066..508.077 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.004 508.070 ↑ 1.0 1 1

Nested Loop Left Join (cost=15,630,983.32..15,630,998.42 rows=1 width=469) (actual time=508.059..508.070 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.000 508.055 ↑ 1.0 1 1

Nested Loop Left Join (cost=15,630,982.91..15,630,994.82 rows=1 width=446) (actual time=508.045..508.055 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 508.043 ↑ 1.0 1 1

Nested Loop Left Join (cost=15,630,982.49..15,630,991.22 rows=1 width=423) (actual time=508.032..508.043 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.001 508.036 ↑ 1.0 1 1

Nested Loop Left Join (cost=15,630,982.20..15,630,987.75 rows=1 width=411) (actual time=508.025..508.036 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.004 508.021 ↑ 1.0 1 1

Nested Loop (cost=15,630,981.92..15,630,984.28 rows=1 width=399) (actual time=508.010..508.021 rows=1 loops=1)

8. 0.003 437.588 ↑ 1.0 1 1

Nested Loop Left Join (cost=15,630,969.16..15,630,971.49 rows=1 width=367) (actual time=437.578..437.588 rows=1 loops=1)

9. 0.003 437.560 ↑ 1.0 1 1

Nested Loop Left Join (cost=15,630,968.75..15,630,969.02 rows=1 width=349) (actual time=437.552..437.560 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.009 437.555 ↑ 1.0 1 1

Hash Join (cost=15,630,968.33..15,630,968.49 rows=1 width=336) (actual time=437.548..437.555 rows=1 loops=1)

  • Hash Cond: ("*SELECT* 1".emplstatus = (pe.emplstatus)::text)
11. 0.002 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)

12. 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.001..0.002 rows=1 loops=1)

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

14. 0.001 0.002 ↑ 1.0 1 1

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

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

16. 0.001 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)

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

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

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

20. 0.001 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)

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

22. 0.000 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)

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

24. 0.004 437.536 ↑ 1.0 1 1

Hash (cost=15,630,968.32..15,630,968.32 rows=1 width=214) (actual time=437.536..437.536 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
25. 0.010 437.532 ↑ 1.0 1 1

Hash Left Join (cost=15,630,953.55..15,630,968.32 rows=1 width=214) (actual time=437.526..437.532 rows=1 loops=1)

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

Nested Loop Left Join (cost=3.40..18.14 rows=1 width=193) (actual time=0.056..0.062 rows=1 loops=1)

  • Join Filter: ((((current_setting('synchr.asof'::text))::date) >= mperspos.effectivedate) AND (((current_setting('synchr.asof'::text))::date) <= mperspos.enddate))
27. 0.000 0.060 ↑ 1.0 1 1

Nested Loop Left Join (cost=3.11..17.75 rows=1 width=184) (actual time=0.055..0.060 rows=1 loops=1)

  • Join Filter: ((((current_setting('synchr.asof'::text))::date) >= mpd.effectivedate) AND (((current_setting('synchr.asof'::text))::date) <= mpd.enddate))
28. 0.001 0.059 ↑ 1.0 1 1

Nested Loop Left Join (cost=2.81..17.22 rows=1 width=170) (actual time=0.054..0.059 rows=1 loops=1)

  • Join Filter: ((((current_setting('synchr.asof'::text))::date) >= mpp.effectivedate) AND (((current_setting('synchr.asof'::text))::date) <= mpp.enddate))
29. 0.000 0.057 ↑ 1.0 1 1

Nested Loop Left Join (cost=2.53..14.89 rows=1 width=170) (actual time=0.053..0.057 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.055 ↑ 1.0 1 1

Nested Loop Left Join (cost=2.25..14.56 rows=1 width=159) (actual time=0.051..0.055 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.053 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.83..12.10 rows=1 width=155) (actual time=0.049..0.053 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.000 0.051 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.54..9.76 rows=1 width=141) (actual time=0.048..0.051 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.042 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.12..7.30 rows=1 width=137) (actual time=0.039..0.042 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.040 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.84..4.97 rows=1 width=90) (actual time=0.037..0.040 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.030 ↑ 1.0 1 1

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

36. 0.009 0.009 ↑ 1.0 1 1

Result (cost=0.01..0.04 rows=1 width=92) (actual time=0.008..0.009 rows=1 loops=1)

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

Index Scan using personemploymentenddate on person_employment pe (cost=0.41..2.45 rows=1 width=34) (actual time=0.018..0.019 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.009 0.009 ↓ 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.009..0.009 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.009 0.009 ↓ 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.009..0.009 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.001 0.001 ↓ 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.001..0.001 rows=0 loops=1)

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

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

  • Index Cond: (forg.organizationid = organizationid)
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
44. 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))
45. 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))
46. 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))
47. 0.006 437.460 ↓ 3.0 9 1

Hash (cost=15,630,950.11..15,630,950.11 rows=3 width=68) (actual time=437.460..437.460 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
48. 0.002 437.454 ↓ 3.0 9 1

Subquery Scan on wt (cost=15,630,950.05..15,630,950.11 rows=3 width=68) (actual time=437.451..437.454 rows=9 loops=1)

49. 0.014 437.452 ↓ 3.0 9 1

HashAggregate (cost=15,630,950.05..15,630,950.08 rows=3 width=100) (actual time=437.450..437.452 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))
50. 0.003 437.438 ↓ 3.0 9 1

Append (cost=0.58..15,630,950.02 rows=3 width=100) (actual time=0.055..437.438 rows=9 loops=1)

51. 0.002 0.087 ↓ 4.0 4 1

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

  • One-Time Filter: ((current_setting('synchr.companyid'::text))::integer = 52133)
52. 0.003 0.085 ↓ 4.0 4 1

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

53. 0.003 0.070 ↓ 4.0 4 1

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

54. 0.011 0.011 ↓ 4.0 4 1

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

  • Filter: (active AND (companyid = 52133))
  • Rows Removed by Filter: 65
55. 0.056 0.056 ↑ 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.014..0.014 rows=1 loops=4)

  • Index Cond: ((cwt.descriptionlocalstringid = localstringid) AND (locale = 'EN_US'::bpchar))
56. 0.012 0.012 ↓ 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.003..0.003 rows=0 loops=4)

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

Result (cost=15,630,933.74..15,630,940.02 rows=1 width=100) (actual time=437.275..437.283 rows=3 loops=1)

  • One-Time Filter: ((current_setting('synchr.companyid'::text))::integer = 52133)
58. 0.021 437.281 ↓ 3.0 3 1

Hash Join (cost=15,630,933.74..15,630,940.02 rows=1 width=100) (actual time=437.273..437.281 rows=3 loops=1)

  • Hash Cond: (pe_1.companyworkertype = ((btrim((cwt_1.emplpermanency)::text) || ':'::text) || (cwt_1.companyworkertypepid)::text))
59. 0.422 437.220 ↑ 25.0 8 1

HashAggregate (cost=15,630,927.23..15,630,929.23 rows=200 width=32) (actual time=437.217..437.220 rows=8 loops=1)

  • Group Key: pe_1.companyworkertype
60. 31.454 436.798 ↑ 2,514.4 764 1

Merge Left Join (cost=616,634.56..15,606,914.73 rows=1,921,000 width=3,425) (actual time=167.070..436.798 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
61.          

CTE cte_pe

62. 3.383 3.383 ↑ 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.056..3.383 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
63.          

CTE cte_names

64. 0.413 11.311 ↓ 3.0 771 1

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

  • Group Key: pe_3.personid, pn_3.fname, pn_3.lname, pn_3.nametype
65. 0.047 10.898 ↓ 3.0 771 1

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

66. 0.625 9.010 ↓ 3.0 764 1

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

67. 3.777 3.777 ↑ 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..3.777 rows=768 loops=1)

68. 4.608 4.608 ↑ 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.006..0.006 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
69. 0.028 1.841 ↓ 7.0 7 1

Hash Join (cost=75.91..896.21 rows=1 width=71) (actual time=1.254..1.841 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
70. 1.645 1.645 ↓ 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.010..1.645 rows=157 loops=1)

  • Index Cond: (nametype = 'Pref'::bpchar)
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 30
71. 0.083 0.168 ↑ 3.0 768 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 71kB
72. 0.085 0.085 ↑ 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.085 rows=768 loops=1)

73.          

CTE cte_hire

74. 0.483 7.209 ↑ 11.6 768 1

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

  • Hash Cond: (cte_pe.personid = pe_5.personid)
75. 0.189 0.189 ↑ 3.0 768 1

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

76. 0.081 5.245 ↑ 1.1 692 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 44kB
77. 0.307 5.164 ↑ 1.1 692 1

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

  • Group Key: pe_5.personid
78. 0.176 4.857 ↑ 1.0 735 1

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

79. 0.073 0.073 ↑ 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.073 rows=768 loops=1)

80. 4.608 4.608 ↑ 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.006 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
81.          

SubPlan (forHash Left Join)

82. 0.076 1.292 ↑ 1.0 1 76

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

83. 0.076 1.216 ↑ 1.0 1 76

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

  • Sort Key: h_1.effectivedate
  • Sort Method: quicksort Memory: 25kB
84. 1.140 1.140 ↑ 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.014..0.015 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
85.          

CTE cte_orighire

86. 0.204 6.908 ↑ 11.1 768 1

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

  • Hash Cond: (cte_pe_2.personid = pe_6.personid)
87. 0.061 0.061 ↑ 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.001..0.061 rows=768 loops=1)

88. 0.081 6.643 ↑ 1.1 683 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 41kB
89. 0.049 6.562 ↑ 1.1 683 1

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

90. 0.319 6.513 ↑ 1.1 683 1

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

  • Group Key: pe_7.personid
91. 0.000 6.194 ↑ 1.1 693 1

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

92. 0.126 0.126 ↑ 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.126 rows=768 loops=1)

93. 6.144 6.144 ↑ 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.007..0.008 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
94. 0.429 392.292 ↑ 2,514.4 764 1

Nested Loop Left Join (cost=575,793.14..14,559,829.91 rows=1,921,000 width=408) (actual time=166.964..392.292 rows=764 loops=1)

95. 0.024 387.279 ↑ 2,514.4 764 1

Nested Loop Left Join (cost=575,790.81..10,026,269.91 rows=1,921,000 width=408) (actual time=166.955..387.279 rows=764 loops=1)

96. 0.230 381.907 ↑ 2,514.4 764 1

Nested Loop Left Join (cost=575,788.48..5,492,709.91 rows=1,921,000 width=408) (actual time=166.945..381.907 rows=764 loops=1)

97. 5.216 374.801 ↑ 2,514.4 764 1

Merge Left Join (cost=575,786.15..959,149.91 rows=1,921,000 width=408) (actual time=166.927..374.801 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
98. 3.479 358.938 ↑ 2,514.4 764 1

Merge Left Join (cost=575,785.73..927,339.42 rows=1,921,000 width=408) (actual time=166.915..358.938 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
99. 9.537 343.349 ↑ 2,514.4 764 1

Merge Left Join (cost=575,785.32..903,658.39 rows=1,921,000 width=408) (actual time=166.893..343.349 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
100. 5.523 289.155 ↑ 2,514.4 764 1

Merge Left Join (cost=575,784.90..857,667.63 rows=1,921,000 width=88) (actual time=166.811..289.155 rows=764 loops=1)

  • Merge Cond: (pe_1.personid = pi_empno.personid)
101. 6.834 269.740 ↑ 2,514.4 764 1

Merge Left Join (cost=575,784.48..831,266.70 rows=1,921,000 width=88) (actual time=166.795..269.740 rows=764 loops=1)

  • Merge Cond: (pe_1.personid = pi_ssn.personid)
102. 3.708 244.359 ↑ 2,514.4 764 1

Merge Left Join (cost=575,784.07..801,794.68 rows=1,921,000 width=88) (actual time=166.771..244.359 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
103. 0.633 232.811 ↑ 2,514.4 764 1

Merge Left Join (cost=575,783.78..750,963.44 rows=1,921,000 width=88) (actual time=166.755..232.811 rows=764 loops=1)

  • Merge Cond: (pe_1.personid = pds.personid)
  • Join Filter: ((pe_1.asof >= pds.effectivedate) AND (pe_1.asof <= pds.enddate))
104. 7.427 231.478 ↑ 2,514.4 764 1

Merge Left Join (cost=575,783.50..703,457.66 rows=1,921,000 width=88) (actual time=166.715..231.478 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
105. 8.238 205.731 ↑ 2,514.4 764 1

Merge Left Join (cost=575,783.09..657,941.48 rows=1,921,000 width=88) (actual time=166.687..205.731 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
106. 0.649 167.458 ↑ 2,514.4 764 1

Merge Left Join (cost=575,782.67..611,778.99 rows=1,921,000 width=88) (actual time=166.630..167.458 rows=764 loops=1)

  • Merge Cond: (pe_1.personid = pvs.personid)
107. 1.395 164.685 ↑ 2,514.4 764 1

Sort (cost=575,694.14..580,496.64 rows=1,921,000 width=88) (actual time=164.559..164.685 rows=764 loops=1)

  • Sort Key: pe_1.personid
  • Sort Method: quicksort Memory: 84kB
108. 8.053 163.290 ↑ 2,514.4 764 1

Hash Right Join (cost=207,318.21..322,676.89 rows=1,921,000 width=88) (actual time=155.793..163.290 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
109. 0.141 0.141 ↑ 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.054..0.141 rows=81 loops=1)

  • Index Cond: (companyid = (current_setting('synchr.companyid'::text))::integer)
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
110. 1.196 155.096 ↑ 2,514.4 764 1

Hash (cost=131,377.92..131,377.92 rows=1,921,000 width=129) (actual time=155.096..155.096 rows=764 loops=1)

  • Buckets: 262144 Batches: 16 Memory Usage: 2075kB
111. 0.579 153.900 ↑ 2,514.4 764 1

Hash Left Join (cost=766.92..131,377.92 rows=1,921,000 width=129) (actual time=104.723..153.900 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
112. 0.104 149.834 ↑ 2,514.4 764 1

Nested Loop Left Join (cost=435.63..49,188.40 rows=1,921,000 width=120) (actual time=101.224..149.834 rows=764 loops=1)

113. 0.706 118.406 ↑ 2.5 764 1

Nested Loop Left Join (cost=435.38..10,768.15 rows=1,921 width=124) (actual time=101.041..118.406 rows=764 loops=1)

114. 0.467 111.588 ↑ 2.5 764 1

Nested Loop Left Join (cost=434.97..4,140.94 rows=1,921 width=113) (actual time=100.993..111.588 rows=764 loops=1)

  • Join Filter: ((pe_1.asof >= pcp.effectivedate) AND (pe_1.asof <= pcp.enddate))
  • Rows Removed by Join Filter: 10
115. 0.731 109.593 ↑ 2.5 764 1

Nested Loop Left Join (cost=434.68..3,490.72 rows=1,921 width=113) (actual time=100.951..109.593 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
116. 0.600 108.098 ↑ 2.5 764 1

Nested Loop Left Join (cost=434.39..2,474.07 rows=1,921 width=117) (actual time=100.942..108.098 rows=764 loops=1)

  • Join Filter: ((pe_1.asof >= ppos.effectivedate) AND (pe_1.asof <= ppos.enddate))
  • Rows Removed by Join Filter: 78
117. 0.257 106.734 ↑ 2.5 764 1

Hash Left Join (cost=434.10..1,826.40 rows=1,921 width=113) (actual time=100.933..106.734 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
118. 0.563 106.008 ↑ 2.5 764 1

Nested Loop Left Join (cost=349.80..1,620.59 rows=1,921 width=117) (actual time=100.457..106.008 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
119. 0.456 103.153 ↑ 2.5 764 1

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

  • Hash Cond: (h.personid = pe_1.personid)
120. 7.510 7.510 ↑ 11.6 768 1

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

121. 0.167 95.187 ↓ 17.8 764 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 68kB
122. 0.251 95.020 ↓ 17.8 764 1

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

  • Hash Cond: (oh.personid = pe_1.personid)
123. 7.056 7.056 ↑ 11.1 768 1

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

124. 0.319 87.713 ↓ 764.0 764 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 68kB
125. 0.527 87.394 ↓ 764.0 764 1

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

  • Join Filter: ((pe_1.asof >= fo.effectivedate) AND (pe_1.asof <= fo.enddate))
  • Rows Removed by Join Filter: 56
126. 0.587 85.339 ↓ 764.0 764 1

Nested Loop Left Join (cost=6.94..145.17 rows=1 width=117) (actual time=11.662..85.339 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
127. 0.271 82.460 ↓ 764.0 764 1

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

128. 0.663 76.841 ↓ 764.0 764 1

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

129. 0.360 70.830 ↓ 764.0 764 1

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

130. 0.582 0.582 ↓ 64.0 768 1

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

  • Filter: (companyid = (current_setting('synchr.companyid'::text))::integer)
131. 0.000 69.888 ↑ 1.0 1 768

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

  • Filter: (pe_1.personid = pn_1.personid)
132. 0.768 69.888 ↑ 1.0 1 768

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

133. 0.768 69.120 ↑ 1.0 1 768

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

  • Sort Key: pn_2.nametype DESC
  • Sort Method: quicksort Memory: 25kB
134. 68.352 68.352 ↑ 1.0 1 768

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

  • Filter: (personid = pe_1.personid)
  • Rows Removed by Filter: 770
135. 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
136. 5.348 5.348 ↑ 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.007..0.007 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
137. 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.003..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
138. 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
139. 2.292 2.292 ↑ 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.003 rows=1 loops=764)

  • Index Cond: (pd_1.positionid = positionid)
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 0
140. 0.116 0.469 ↓ 1.0 987 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 51kB
141. 0.353 0.353 ↓ 1.0 987 1

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

  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 229
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. 1.528 1.528 ↑ 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.002..0.002 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 6.112 ↑ 1.0 1 764

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

  • Filter: (pe_1.personid = pc.personid)
146. 0.000 6.112 ↑ 1.0 1 764

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

147. 6.112 6.112 ↑ 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.008..0.008 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. 31.324 31.324 ↓ 0.0 0 764

Function Scan on get_manager mgr (cost=0.25..10.25 rows=1,000 width=0) (actual time=0.041..0.041 rows=0 loops=764)

149. 0.594 3.487 ↑ 1.0 4,786 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 276kB
150. 2.893 2.893 ↑ 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.023..2.893 rows=4,786 loops=1)

  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 1995
151. 1.779 2.124 ↑ 1.1 892 1

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

  • Sort Key: pvs.personid
  • Sort Method: quicksort Memory: 66kB
152. 0.345 0.345 ↑ 1.1 892 1

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

  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 331
153. 3.521 30.035 ↓ 1.0 31,791 1

Materialize (cost=0.41..2,333.56 rows=31,093 width=21) (actual time=0.020..30.035 rows=31,791 loops=1)

154. 26.514 26.514 ↓ 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.018..26.514 rows=31,791 loops=1)

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

Materialize (cost=0.41..1,564.21 rows=29,226 width=21) (actual time=0.010..18.320 rows=29,039 loops=1)

156. 15.886 15.886 ↑ 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..15.886 rows=29,039 loops=1)

  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 4347
157. 0.098 0.700 ↑ 1.1 1,098 1

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

158. 0.602 0.602 ↑ 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.037..0.602 rows=1,098 loops=1)

  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 243
159. 1.189 7.840 ↑ 1.0 13,375 1

Materialize (cost=0.29..626.17 rows=13,444 width=21) (actual time=0.010..7.840 rows=13,375 loops=1)

160. 6.651 6.651 ↑ 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..6.651 rows=13,375 loops=1)

  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 163
161. 2.163 18.547 ↑ 1.0 24,926 1

Materialize (cost=0.42..2,696.53 rows=25,676 width=13) (actual time=0.013..18.547 rows=24,926 loops=1)

162. 16.384 16.384 ↑ 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..16.384 rows=24,926 loops=1)

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

Materialize (cost=0.42..2,603.57 rows=22,196 width=13) (actual time=0.011..13.892 rows=20,450 loops=1)

164. 12.103 12.103 ↑ 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.010..12.103 rows=20,450 loops=1)

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

Materialize (cost=0.41..3,001.35 rows=36,305 width=341) (actual time=0.031..44.657 rows=35,891 loops=1)

166. 38.803 38.803 ↑ 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.030..38.803 rows=35,891 loops=1)

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

Materialize (cost=0.41..1,663.46 rows=12,963 width=21) (actual time=0.012..12.110 rows=12,705 loops=1)

168. 11.018 11.018 ↑ 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.011..11.018 rows=12,705 loops=1)

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

Materialize (cost=0.41..1,678.73 rows=19,073 width=21) (actual time=0.008..10.647 rows=18,802 loops=1)

170. 9.002 9.002 ↑ 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.007..9.002 rows=18,802 loops=1)

  • Filter: ((netcontacttype = 'HomeEmail'::bpchar) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 14849
171. 0.764 6.876 ↓ 0.0 0 764

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

  • Filter: (pe_1.personid = pp_w_1.personid)
172. 0.000 6.112 ↓ 0.0 0 764

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

173. 1.528 6.112 ↓ 0.0 0 764

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

  • Sort Key: pp_w_2.effectivedate DESC
  • Sort Method: quicksort Memory: 25kB
174. 4.584 4.584 ↓ 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.006..0.006 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
175. 0.764 5.348 ↓ 0.0 0 764

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

  • Filter: (pe_1.personid = pp_h.personid)
176. 0.000 4.584 ↓ 0.0 0 764

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

177. 0.764 4.584 ↓ 0.0 0 764

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

  • Sort Key: pp_h_1.effectivedate DESC
  • Sort Method: quicksort Memory: 25kB
178. 3.820 3.820 ↓ 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.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 = 'Home'::bpchar) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 2
179. 0.000 4.584 ↓ 0.0 0 764

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

  • Filter: (pe_1.personid = pp_m_1.personid)
180. 0.000 4.584 ↓ 0.0 0 764

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

181. 0.764 4.584 ↓ 0.0 0 764

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

  • Sort Key: pp_m_2.effectivedate DESC
  • Sort Method: quicksort Memory: 25kB
182. 3.820 3.820 ↓ 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.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 = 'Mobile'::bpchar) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 3
183. 1.899 13.052 ↓ 1.0 22,413 1

Materialize (cost=0.29..1,218.51 rows=22,313 width=25) (actual time=0.009..13.052 rows=22,413 loops=1)

184. 11.153 11.153 ↓ 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..11.153 rows=22,413 loops=1)

  • Filter: ((payunitrelationship = 'M'::bpchar) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 2358
185. 0.004 0.040 ↓ 4.0 4 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
186. 0.004 0.036 ↓ 4.0 4 1

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

187. 0.001 0.028 ↓ 4.0 4 1

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

188. 0.007 0.007 ↓ 4.0 4 1

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

  • Filter: ((NOT active) AND (companyid = 52133))
  • Rows Removed by Filter: 65
189. 0.020 0.020 ↓ 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.005..0.005 rows=0 loops=4)

  • Index Cond: (cwt_1.descriptionlocalstringid = localstringid)
  • Filter: ((locale)::text = current_setting('synchr.userlocale'::text))
  • Rows Removed by Filter: 1
190. 0.004 0.004 ↑ 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.001..0.001 rows=1 loops=4)

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

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

192. 0.004 0.064 ↓ 2.0 2 1

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

  • One-Time Filter: ((current_setting('synchr.companyid'::text))::integer = 52133)
193. 0.007 0.060 ↓ 2.0 2 1

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

  • Join Filter: (empl_permanency.emplpermanency = wt_1.emplpermanency)
  • Filter: (wt_1.companyworkertypepid IS NULL)
194. 0.031 0.031 ↑ 1.0 2 1

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

195. 0.001 0.022 ↓ 0.0 0 2

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

196. 0.021 0.021 ↓ 0.0 0 1

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

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

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

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

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

  • Filter: ((pn.personid = '136011'::bpchar) AND (pe.personid = pn.personid))
199. 0.002 0.023 ↑ 1.0 1 1

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

200. 0.021 0.021 ↑ 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.021..0.021 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))
201. 0.006 70.429 ↑ 1.0 1 1

Aggregate (cost=12.75..12.76 rows=1 width=32) (actual time=70.429..70.429 rows=1 loops=1)

202. 70.423 70.423 ↑ 333.3 3 1

Function Scan on get_privacy_codes (cost=0.25..10.25 rows=1,000 width=32) (actual time=70.422..70.423 rows=3 loops=1)

203. 0.014 0.014 ↓ 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.014..0.014 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
204.          

SubPlan (forNested Loop Left Join)

205. 0.000 0.000 ↓ 0.0 0

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

206. 0.000 0.000 ↓ 0.0 0

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

207. 0.000 0.000 ↓ 0.0 0

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

208. 0.006 0.006 ↓ 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.006 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
209.          

SubPlan (forNested Loop Left Join)

210. 0.000 0.000 ↓ 0.0 0

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

211. 0.000 0.000 ↓ 0.0 0

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

212. 0.000 0.000 ↓ 0.0 0

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

213. 0.012 0.012 ↓ 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.012..0.012 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
214.          

SubPlan (forNested Loop Left Join)

215. 0.000 0.000 ↓ 0.0 0

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

216. 0.000 0.000 ↓ 0.0 0

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

217. 0.000 0.000 ↓ 0.0 0

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

218. 0.006 0.006 ↑ 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.006..0.006 rows=1 loops=1)

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

SubPlan (forNested Loop Left Join)

220. 0.002 0.005 ↑ 100.0 1 1

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

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

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

223. 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
224.          

SubPlan (forNested Loop Left Join)

225. 0.000 0.000 ↓ 0.0 0

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

226. 0.000 0.000 ↓ 0.0 0

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

227. 0.000 0.000 ↓ 0.0 0

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

228. 0.005 0.005 ↓ 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.005..0.005 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
229.          

SubPlan (forNested Loop Left Join)

230. 0.000 0.000 ↓ 0.0 0

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

231. 0.000 0.000 ↓ 0.0 0

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

232. 0.000 0.000 ↓ 0.0 0

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