explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mtz

Settings
# exclusive inclusive rows x rows loops node
1. 2.774 858.732 ↓ 41.1 4,024 1

Hash Join (cost=125,146.67..125,160.21 rows=98 width=764) (actual time=855.560..858.732 rows=4,024 loops=1)

  • Hash Cond: (pe.personid = sc.subjectid)
2. 10.769 796.800 ↓ 4.5 4,024 1

Sort (cost=125,077.43..125,079.66 rows=891 width=959) (actual time=796.380..796.800 rows=4,024 loops=1)

  • Sort Key: pn.lname
  • Sort Method: quicksort Memory: 2515kB
3. 14.055 786.031 ↓ 4.5 4,024 1

Hash Left Join (cost=82,120.20..125,033.78 rows=891 width=959) (actual time=562.262..786.031 rows=4,024 loops=1)

  • Hash Cond: (COALESCE(ppm.positionid, ppm2.positionid) = matrixorg.positionid)
4. 1.616 612.338 ↓ 4.5 4,024 1

Hash Left Join (cost=66,009.94..88,510.40 rows=891 width=484) (actual time=470.773..612.338 rows=4,024 loops=1)

  • Hash Cond: (COALESCE(ppm.positionid, ppm2.positionid) = budgetorg.positionid)
5. 1.220 380.660 ↓ 4.5 4,024 1

Hash Left Join (cost=39,068.04..61,566.16 rows=891 width=441) (actual time=240.528..380.660 rows=4,024 loops=1)

  • Hash Cond: (COALESCE(ppm.positionid, ppm2.positionid) = po_1.topositionid)
  • Join Filter: (NOT (SubPlan 9))
6. 3.221 373.195 ↓ 4.5 4,024 1

Nested Loop Left Join (cost=34,713.60..57,092.64 rows=891 width=428) (actual time=234.269..373.195 rows=4,024 loops=1)

7. 3.202 369.974 ↓ 4.5 4,024 1

Nested Loop Left Join (cost=34,713.18..55,087.45 rows=891 width=432) (actual time=234.262..369.974 rows=4,024 loops=1)

8. 2.852 366.772 ↓ 4.5 4,024 1

Nested Loop Left Join (cost=34,712.75..54,533.35 rows=891 width=425) (actual time=234.256..366.772 rows=4,024 loops=1)

9. 3.151 351.848 ↓ 4.5 4,024 1

Nested Loop Left Join (cost=34,710.63..48,252.57 rows=891 width=361) (actual time=234.245..351.848 rows=4,024 loops=1)

10. 3.114 328.577 ↓ 4.5 4,024 1

Nested Loop Left Join (cost=34,710.21..46,506.56 rows=891 width=338) (actual time=234.227..328.577 rows=4,024 loops=1)

  • Join Filter: ((CASE WHEN (ppm.effectivedate IS NULL) THEN ppm2.effectivedate ELSE CURRENT_DATE END >= oc.effectivedate) AND (CASE WHEN (ppm.effectivedate IS NULL) THEN ppm2.effectivedate ELSE CURRENT_DATE END <= oc.enddate))
  • Rows Removed by Join Filter: 89
11. 1.243 325.463 ↓ 4.5 4,024 1

Nested Loop Left Join (cost=34,709.92..46,203.66 rows=891 width=304) (actual time=234.221..325.463 rows=4,024 loops=1)

  • Join Filter: (((CURRENT_DATE >= pd.effectivedate) AND (CURRENT_DATE <= pd.enddate)) OR ((pe.effectivedate >= pd.effectivedate) AND (pe.effectivedate <= pd.enddate) AND (pd.enddate >= CURRENT_DATE)))
12. 3.207 320.196 ↓ 4.5 4,024 1

Nested Loop Left Join (cost=34,709.50..42,342.15 rows=891 width=272) (actual time=234.215..320.196 rows=4,024 loops=1)

13. 2.395 316.989 ↓ 4.5 4,024 1

Nested Loop Left Join (cost=34,709.22..42,049.43 rows=891 width=221) (actual time=234.210..316.989 rows=4,024 loops=1)

  • Join Filter: ((CASE WHEN (ppm.effectivedate IS NULL) THEN ppm2.effectivedate ELSE CURRENT_DATE END >= pl.effectivedate) AND (CASE WHEN (ppm.effectivedate IS NULL) THEN ppm2.effectivedate ELSE CURRENT_DATE END <= pl.enddate))
  • Rows Removed by Join Filter: 3350
14. 3.150 294.474 ↓ 4.5 4,024 1

Hash Left Join (cost=34,708.80..39,716.28 rows=891 width=214) (actual time=234.182..294.474 rows=4,024 loops=1)

  • Hash Cond: (COALESCE(ppm.positionid, ppm2.positionid) = por.positionid)
  • Join Filter: ((CASE WHEN (ppm.effectivedate IS NULL) THEN ppm2.effectivedate ELSE CURRENT_DATE END >= por.effectivedate) AND (CASE WHEN (ppm.effectivedate IS NULL) THEN ppm2.effectivedate ELSE CURRENT_DATE END <= por.enddate))
  • Rows Removed by Join Filter: 10
15. 1.792 134.740 ↓ 4.5 4,024 1

Nested Loop (cost=15,281.38..19,073.84 rows=891 width=210) (actual time=76.924..134.740 rows=4,024 loops=1)

16. 5.851 108.804 ↓ 3.7 4,024 1

Nested Loop Left Join (cost=15,280.96..16,863.55 rows=1,099 width=121) (actual time=76.899..108.804 rows=4,024 loops=1)

  • Join Filter: ((COALESCE(pec.emplstatus, pe.emplstatus))::text = "*SELECT* 1".emplstatus)
  • Rows Removed by Join Filter: 20120
17. 0.934 102.953 ↓ 3.7 4,024 1

Merge Left Join (cost=15,280.96..16,748.02 rows=1,099 width=91) (actual time=76.887..102.953 rows=4,024 loops=1)

  • Merge Cond: (pe.personid = ppm2.personid)
  • Join Filter: ((ppm.positionid IS NULL) AND (ppm2.perspospid = (SubPlan 8)))
18. 1.290 95.641 ↓ 3.7 4,024 1

Nested Loop (cost=11,738.81..13,191.48 rows=1,099 width=79) (actual time=70.744..95.641 rows=4,024 loops=1)

19. 0.000 94.351 ↓ 3.7 4,024 1

Gather Merge (cost=11,738.53..13,175.43 rows=1,099 width=79) (actual time=70.730..94.351 rows=4,024 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
20. 2.278 164.958 ↓ 3.1 2,012 2

Nested Loop Left Join (cost=10,738.52..12,051.78 rows=646 width=79) (actual time=64.485..82.479 rows=2,012 loops=2)

21. 7.056 138.536 ↓ 3.1 2,012 2

Merge Left Join (cost=10,738.09..10,804.25 rows=646 width=67) (actual time=64.444..69.268 rows=2,012 loops=2)

  • Merge Cond: (pe.personid = pi.personid)
22. 9.700 66.086 ↓ 3.1 2,012 2

Sort (cost=9,669.41..9,671.03 rows=646 width=63) (actual time=32.698..33.043 rows=2,012 loops=2)

  • Sort Key: pe.personid
  • Sort Method: quicksort Memory: 454kB
23. 3.812 56.386 ↓ 3.1 2,012 2

Nested Loop Left Join (cost=1.27..9,639.26 rows=646 width=63) (actual time=0.097..28.193 rows=2,012 loops=2)

24. 0.799 32.454 ↓ 3.1 2,012 2

Nested Loop Anti Join (cost=0.85..7,278.35 rows=646 width=54) (actual time=0.081..16.227 rows=2,012 loops=2)

25. 11.530 11.530 ↓ 2.1 2,012 2

Parallel Index Scan using employmentcompanyideffdt on person_employment pe (cost=0.42..4,480.39 rows=969 width=54) (actual time=0.042..5.765 rows=2,012 loops=2)

  • Index Cond: ((companyid = 876) AND (enddate >= CURRENT_DATE))
  • Filter: ((effectivedate < enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 2122
26. 20.125 20.125 ↓ 0.0 0 4,025

Index Scan using personemploymentenddate on person_employment fper (cost=0.42..2.96 rows=1 width=17) (actual time=0.005..0.005 rows=0 loops=4,025)

  • Index Cond: ((pe.personid = personid) AND (effectivedate > pe.effectivedate))
  • Filter: ((effectivedate < enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 0
27. 20.120 20.120 ↑ 1.0 1 4,024

Index Scan using personemploymentenddate on person_employment pec (cost=0.43..3.64 rows=1 width=26) (actual time=0.005..0.005 rows=1 loops=4,024)

  • Index Cond: ((pe.personid = personid) AND (CURRENT_DATE <= enddate) AND (CURRENT_DATE >= effectivedate))
  • Filter: ((companyid = 876) AND (pe.companyid = companyid) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 1
28. 62.252 65.394 ↓ 1.0 12,628 2

Sort (cost=1,068.68..1,099.69 rows=12,407 width=17) (actual time=31.687..32.697 rows=12,628 loops=2)

  • Sort Key: pi.personid
  • Sort Method: quicksort Memory: 1372kB
29. 3.142 3.142 ↓ 1.0 12,637 2

Seq Scan on person_image pi (cost=0.00..225.07 rows=12,407 width=17) (actual time=0.019..1.571 rows=12,637 loops=2)

30. 24.144 24.144 ↓ 0.0 0 4,024

Index Scan using pers_pospersendeff on pers_pos ppm (cost=0.42..1.92 rows=1 width=25) (actual time=0.006..0.006 rows=0 loops=4,024)

  • Index Cond: ((personid = pe.personid) AND (CURRENT_DATE <= enddate) AND (CURRENT_DATE >= effectivedate))
  • Filter: ((persposrel = 'Occupies'::bpchar) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 0
31. 0.000 0.000 ↑ 1.0 1 4,024

Materialize (cost=0.28..2.32 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=4,024)

32. 0.016 0.016 ↑ 1.0 1 1

Index Scan using pk_companyname on companyname cnam (cost=0.28..2.32 rows=1 width=4) (actual time=0.010..0.016 rows=1 loops=1)

  • Index Cond: (companyid = 876)
  • Filter: ((CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
33. 0.366 6.162 ↑ 1.7 197 1

Sort (cost=3,542.13..3,542.95 rows=328 width=29) (actual time=6.139..6.162 rows=197 loops=1)

  • Sort Key: ppm2.personid
  • Sort Method: quicksort Memory: 40kB
34. 5.796 5.796 ↑ 1.7 197 1

Index Scan using pers_pospositionid on pers_pos ppm2 (cost=0.42..3,528.43 rows=328 width=29) (actual time=1.236..5.796 rows=197 loops=1)

  • Index Cond: (CURRENT_DATE < effectivedate)
  • Filter: ((effectivedate < enddate) AND (persposrel = 'Occupies'::bpchar) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 83
35.          

SubPlan (forMerge Left Join)

36. 0.027 0.216 ↑ 1.0 1 27

Aggregate (cost=2.48..2.49 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=27)

37. 0.189 0.189 ↑ 1.0 1 27

Index Scan using pers_pospersendeff on pers_pos ppm3 (cost=0.42..2.48 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=27)

  • Index Cond: ((personid = pe.personid) AND (CURRENT_DATE < effectivedate))
  • Filter: ((effectivedate < enddate) AND (persposrel = 'Occupies'::bpchar) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
38. 0.000 0.000 ↑ 1.0 6 4,024

Materialize (cost=0.00..0.15 rows=6 width=32) (actual time=0.000..0.000 rows=6 loops=4,024)

39. 0.008 0.025 ↑ 1.0 6 1

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

40. 0.001 0.002 ↑ 1.0 1 1

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

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

42. 0.001 0.002 ↑ 1.0 1 1

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

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

44. 0.002 0.004 ↑ 1.0 1 1

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

45. 0.002 0.002 ↑ 1.0 1 1

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

46. 0.002 0.003 ↑ 1.0 1 1

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

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

48. 0.002 0.003 ↑ 1.0 1 1

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

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

50. 0.001 0.003 ↑ 1.0 1 1

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

51. 0.002 0.002 ↑ 1.0 1 1

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

52. 24.144 24.144 ↑ 1.0 1 4,024

Index Scan using personnamepersonidnametype on person_names pn (cost=0.42..2.00 rows=1 width=102) (actual time=0.006..0.006 rows=1 loops=4,024)

  • Index Cond: ((personid = pe.personid) AND (nametype = 'Legal'::bpchar))
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts) AND (enddate = to_date('12/31/2199'::text, 'mm/dd/yyyy'::text)))
  • Rows Removed by Filter: 0
53. 28.795 156.584 ↓ 1.1 162,345 1

Hash (cost=17,651.85..17,651.85 rows=142,046 width=16) (actual time=156.584..156.584 rows=162,345 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 9658kB
54. 26.351 127.789 ↓ 1.1 162,345 1

Hash Join (cost=971.87..17,651.85 rows=142,046 width=16) (actual time=7.481..127.789 rows=162,345 loops=1)

  • Hash Cond: (por.organizationid = corf.organizationid)
55. 93.997 93.997 ↓ 1.1 162,516 1

Index Scan using posorgrelbyposition on pos_org_rel por (cost=0.42..12,960.21 rows=153,315 width=16) (actual time=0.018..93.997 rows=162,516 loops=1)

  • Index Cond: (posorgreltype = 'Member'::bpchar)
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 10984
56. 1.549 7.441 ↑ 1.0 14,978 1

Hash (cost=777.35..777.35 rows=15,528 width=4) (actual time=7.441..7.441 rows=14,978 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 655kB
57. 5.892 5.892 ↑ 1.0 14,978 1

Seq Scan on company_organization_rel corf (cost=0.00..777.35 rows=15,528 width=4) (actual time=0.009..5.892 rows=14,978 loops=1)

  • Filter: ((CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 3726
58. 20.120 20.120 ↑ 2.0 1 4,024

Index Scan using pk_person_locations on person_locations pl (cost=0.42..2.58 rows=2 width=28) (actual time=0.005..0.005 rows=1 loops=4,024)

  • Index Cond: (personid = pe.personid)
  • Filter: ((personlocationtype = 'P'::bpchar) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 0
59. 0.000 0.000 ↓ 0.0 0 4,024

Index Scan using locationcodeslocationid on location_codes lc (cost=0.28..0.32 rows=1 width=55) (actual time=0.000..0.000 rows=0 loops=4,024)

  • Index Cond: (locationid = pl.locationid)
  • Filter: ((CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
60. 4.024 4.024 ↓ 0.0 0 4,024

Index Scan using pk_position_desc on position_desc pd (cost=0.42..4.30 rows=1 width=40) (actual time=0.001..0.001 rows=0 loops=4,024)

  • Index Cond: (positionid = COALESCE(ppm.positionid, ppm2.positionid))
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts) AND (((CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate)) OR (enddate >= CURRENT_DATE)))
  • Rows Removed by Filter: 0
61. 0.000 0.000 ↓ 0.0 0 4,024

Index Scan using organizationcodeorganizationid on organization_code oc (cost=0.29..0.32 rows=1 width=46) (actual time=0.000..0.000 rows=0 loops=4,024)

  • Index Cond: (organizationid = por.organizationid)
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
62. 20.120 20.120 ↓ 0.0 0 4,024

Index Scan using pk_person_net_contacts on person_net_contacts pnc (cost=0.42..1.95 rows=1 width=36) (actual time=0.005..0.005 rows=0 loops=4,024)

  • Index Cond: (personid = pe.personid)
  • Filter: ((netcontacttype = 'Email'::bpchar) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts) AND (CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate))
  • Rows Removed by Filter: 1
63. 0.000 12.072 ↓ 0.0 0 4,024

Nested Loop Left Join (cost=2.12..7.03 rows=2 width=76) (actual time=0.003..0.003 rows=0 loops=4,024)

64. 3.756 12.072 ↓ 0.0 0 4,024

Nested Loop Left Join (cost=1.70..6.29 rows=1 width=58) (actual time=0.002..0.003 rows=0 loops=4,024)

  • Join Filter: (pp.createts = "*SELECT* 1_1".createts)
  • Rows Removed by Join Filter: 0
65. 0.000 8.048 ↓ 0.0 0 4,024

Nested Loop (cost=1.70..6.22 rows=1 width=66) (actual time=0.002..0.002 rows=0 loops=4,024)

66. 0.272 8.048 ↓ 0.0 0 4,024

Nested Loop Left Join (cost=1.27..4.69 rows=1 width=51) (actual time=0.002..0.002 rows=0 loops=4,024)

67. 4.024 4.024 ↓ 0.0 0 4,024

Index Scan using pos_pos_topositionid on pos_pos po (cost=0.43..2.25 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=4,024)

  • Index Cond: ((COALESCE(ppm.positionid, ppm2.positionid) = topositionid) AND (CASE WHEN (ppm.effectivedate IS NULL) THEN ppm2.effectivedate ELSE CURRENT_DATE END >= effectivedate) AND (CASE WHEN (ppm.effectivedate IS NULL) THEN ppm2.effectivedate ELSE CURRENT_DATE END <= enddate) AND (CURRENT_DATE <= enddate))
  • Filter: ((effectivedate <= enddate) AND (posposrel = 'Manages'::bpchar) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 0
68. 0.000 3.752 ↑ 1.0 1 268

Nested Loop Left Join (cost=0.85..2.43 rows=1 width=39) (actual time=0.014..0.014 rows=1 loops=268)

69. 1.072 1.072 ↑ 1.0 1 268

Index Scan using pers_pospositionid on pers_pos pp (cost=0.42..0.71 rows=1 width=25) (actual time=0.003..0.004 rows=1 loops=268)

  • Index Cond: ((positionid = po.positionid) AND (CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate))
  • Filter: ((persposrel = 'Occupies'::bpchar) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
70. 0.536 2.680 ↑ 1.0 1 268

Nested Loop (cost=0.42..1.71 rows=1 width=27) (actual time=0.010..0.010 rows=1 loops=268)

71. 1.876 1.876 ↑ 1.0 1 268

Index Scan using personnamepersonidnametype on person_names pn_1 (cost=0.42..0.61 rows=1 width=33) (actual time=0.007..0.007 rows=1 loops=268)

  • Index Cond: ((pp.personid = personid) AND (nametype = 'Legal'::bpchar))
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts) AND (enddate = to_date('12/31/2199'::text, 'mm/dd/yyyy'::text)))
  • Rows Removed by Filter: 0
72. 0.268 0.268 ↑ 1.0 1 268

Seq Scan on name_type (cost=0.00..1.09 rows=1 width=6) (actual time=0.001..0.001 rows=1 loops=268)

  • Filter: (nametype = 'Legal'::bpchar)
  • Rows Removed by Filter: 6
73. 1.072 1.072 ↑ 1.0 1 268

Index Scan using pk_position_desc on position_desc pd_1 (cost=0.42..1.53 rows=1 width=23) (actual time=0.003..0.004 rows=1 loops=268)

  • Index Cond: (positionid = po.positionid)
  • Filter: ((CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 0
74. 0.000 0.268 ↑ 1.0 2 268

Append (cost=0.00..0.04 rows=2 width=4) (actual time=0.001..0.001 rows=2 loops=268)

75. 0.268 0.268 ↑ 1.0 1 268

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

76. 0.000 0.000 ↑ 1.0 1 268

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

77. 0.000 0.000 ↑ 1.0 1 268

Subquery Scan on *SELECT* 2_1 (cost=0.00..0.02 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=268)

78. 0.000 0.000 ↑ 1.0 1 268

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

79. 2.144 2.144 ↑ 2.0 1 268

Index Scan using personemploymentenddate on person_employment pe_1 (cost=0.43..0.72 rows=2 width=13) (actual time=0.007..0.008 rows=1 loops=268)

  • Index Cond: ((personid = pp.personid) AND (CURRENT_DATE <= enddate) AND (CURRENT_DATE >= effectivedate))
  • Filter: ((emplstatus <> 'T'::bpchar) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 0
80. 0.000 0.000 ↓ 0.0 0 4,024

Index Scan using personnamepersonidnametype on person_names mn (cost=0.42..0.61 rows=1 width=20) (actual time=0.000..0.000 rows=0 loops=4,024)

  • Index Cond: ((pp.personid = personid) AND (nametype = 'Legal'::bpchar))
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts) AND (enddate = to_date('12/31/2199'::text, 'mm/dd/yyyy'::text)))
  • Rows Removed by Filter: 0
81. 0.000 0.000 ↓ 0.0 0 4,024

Index Scan using pos_pos_topositionid on pos_pos mpp (cost=0.42..2.24 rows=1 width=16) (actual time=0.000..0.000 rows=0 loops=4,024)

  • Index Cond: ((topositionid = COALESCE(ppm.positionid, ppm2.positionid)) AND (CASE WHEN (ppm.effectivedate IS NULL) THEN ppm2.effectivedate ELSE CURRENT_DATE END >= effectivedate) AND (CASE WHEN (ppm.effectivedate IS NULL) THEN ppm2.effectivedate ELSE CURRENT_DATE END <= enddate))
  • Filter: ((posposrel = 'Manages'::bpchar) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 0
82. 0.030 6.245 ↑ 1.7 113 1

Hash (cost=4,351.99..4,351.99 rows=196 width=21) (actual time=6.245..6.245 rows=113 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
83. 0.082 6.215 ↑ 1.7 113 1

Nested Loop Left Join (cost=2.12..4,351.99 rows=196 width=21) (actual time=1.438..6.215 rows=113 loops=1)

84. 0.060 5.794 ↑ 1.1 113 1

Nested Loop Left Join (cost=1.69..4,256.90 rows=129 width=21) (actual time=1.411..5.794 rows=113 loops=1)

  • Join Filter: (pp_1.createts = "*SELECT* 1_2".createts)
  • Rows Removed by Join Filter: 226
85. 0.004 5.734 ↑ 1.1 113 1

Nested Loop Left Join (cost=1.69..4,252.98 rows=129 width=29) (actual time=1.398..5.734 rows=113 loops=1)

86. 0.070 4.939 ↑ 1.1 113 1

Nested Loop (cost=0.85..3,721.68 rows=129 width=12) (actual time=1.337..4.939 rows=113 loops=1)

87. 4.417 4.417 ↑ 1.4 113 1

Index Scan using pos_pos_topositionid on pos_pos po_1 (cost=0.42..3,039.88 rows=156 width=12) (actual time=1.315..4.417 rows=113 loops=1)

  • Index Cond: ((CURRENT_DATE < effectivedate) AND (CURRENT_DATE <= enddate))
  • Filter: ((effectivedate <= enddate) AND (posposrel = 'Manages'::bpchar) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 5
88. 0.452 0.452 ↑ 1.0 1 113

Index Scan using pk_position_desc on position_desc pd_2 (cost=0.42..4.36 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=113)

  • Index Cond: (positionid = po_1.positionid)
  • Filter: ((CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 2
89. 0.125 0.791 ↓ 0.0 0 113

Nested Loop Left Join (cost=0.85..4.11 rows=1 width=25) (actual time=0.007..0.007 rows=0 loops=113)

90. 0.226 0.226 ↓ 0.0 0 113

Index Scan using pers_pospositionid on pers_pos pp_1 (cost=0.42..2.39 rows=1 width=25) (actual time=0.002..0.002 rows=0 loops=113)

  • Index Cond: ((positionid = po_1.positionid) AND (CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate))
  • Filter: ((persposrel = 'Occupies'::bpchar) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 0
91. 0.040 0.440 ↑ 1.0 1 40

Nested Loop (cost=0.42..1.71 rows=1 width=13) (actual time=0.011..0.011 rows=1 loops=40)

92. 0.360 0.360 ↑ 1.0 1 40

Index Scan using personnamepersonidnametype on person_names pn_2 (cost=0.42..0.61 rows=1 width=19) (actual time=0.009..0.009 rows=1 loops=40)

  • Index Cond: ((pp_1.personid = personid) AND (nametype = 'Legal'::bpchar))
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts) AND (enddate = to_date('12/31/2199'::text, 'mm/dd/yyyy'::text)))
  • Rows Removed by Filter: 0
93. 0.040 0.040 ↑ 1.0 1 40

Seq Scan on name_type name_type_1 (cost=0.00..1.09 rows=1 width=6) (actual time=0.001..0.001 rows=1 loops=40)

  • Filter: (nametype = 'Legal'::bpchar)
  • Rows Removed by Filter: 6
94. 0.000 0.000 ↑ 1.0 2 113

Materialize (cost=0.00..0.06 rows=2 width=4) (actual time=0.000..0.000 rows=2 loops=113)

95. 0.001 0.008 ↑ 1.0 2 1

Append (cost=0.00..0.04 rows=2 width=4) (actual time=0.004..0.008 rows=2 loops=1)

96. 0.002 0.004 ↑ 1.0 1 1

Subquery Scan on *SELECT* 1_2 (cost=0.00..0.02 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=1)

97. 0.002 0.002 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=112) (actual time=0.002..0.002 rows=1 loops=1)

98. 0.000 0.003 ↑ 1.0 1 1

Subquery Scan on *SELECT* 2_2 (cost=0.00..0.02 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=1)

99. 0.003 0.003 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=112) (actual time=0.003..0.003 rows=1 loops=1)

100. 0.339 0.339 ↓ 0.0 0 113

Index Scan using personemploymentenddate on person_employment pe_2 (cost=0.43..0.72 rows=2 width=13) (actual time=0.003..0.003 rows=0 loops=113)

  • Index Cond: ((personid = pp_1.personid) AND (CURRENT_DATE <= enddate) AND (CURRENT_DATE >= effectivedate))
  • Filter: ((emplstatus <> 'T'::bpchar) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 0
101.          

SubPlan (forHash Left Join)

102. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=2.12..11.91 rows=2 width=0) (never executed)

103. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=1.69..11.17 rows=1 width=13) (never executed)

  • Join Filter: (pp_2.createts = "*SELECT* 1_3".createts)
104. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.69..11.10 rows=1 width=21) (never executed)

105. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=1.27..6.67 rows=1 width=25) (never executed)

106. 0.000 0.000 ↓ 0.0 0

Index Scan using pos_pos_topositionid on pos_pos po_2 (cost=0.42..2.46 rows=1 width=4) (never executed)

  • Index Cond: ((COALESCE(ppm.positionid, ppm2.positionid) = topositionid) AND (CURRENT_DATE < effectivedate) AND (effectivedate < po_1.effectivedate) AND (CURRENT_DATE <= enddate))
  • Filter: ((effectivedate <= enddate) AND (posposrel = 'Manages'::bpchar) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
107. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=0.85..4.19 rows=1 width=25) (never executed)

108. 0.000 0.000 ↓ 0.0 0

Index Scan using pers_pospositionid on pers_pos pp_2 (cost=0.42..2.47 rows=1 width=25) (never executed)

  • Index Cond: ((positionid = po_2.positionid) AND (CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate))
  • Filter: ((persposrel = 'Occupies'::bpchar) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
109. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.42..1.71 rows=1 width=13) (never executed)

110. 0.000 0.000 ↓ 0.0 0

Index Scan using personnamepersonidnametype on person_names pn_3 (cost=0.42..0.61 rows=1 width=19) (never executed)

  • Index Cond: ((pp_2.personid = personid) AND (nametype = 'Legal'::bpchar))
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts) AND (enddate = to_date('12/31/2199'::text, 'mm/dd/yyyy'::text)))
111. 0.000 0.000 ↓ 0.0 0

Seq Scan on name_type name_type_2 (cost=0.00..1.09 rows=1 width=6) (never executed)

  • Filter: (nametype = 'Legal'::bpchar)
112. 0.000 0.000 ↓ 0.0 0

Index Scan using pk_position_desc on position_desc pd_3 (cost=0.42..4.42 rows=1 width=4) (never executed)

  • Index Cond: (positionid = po_2.positionid)
  • Filter: ((CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
113. 0.000 0.000 ↓ 0.0 0

Append (cost=0.00..0.04 rows=2 width=4) (never executed)

114. 0.000 0.000 ↓ 0.0 0

Subquery Scan on *SELECT* 1_3 (cost=0.00..0.02 rows=1 width=4) (never executed)

115. 0.000 0.000 ↓ 0.0 0

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

116. 0.000 0.000 ↓ 0.0 0

Subquery Scan on *SELECT* 2_3 (cost=0.00..0.02 rows=1 width=4) (never executed)

117. 0.000 0.000 ↓ 0.0 0

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

118. 0.000 0.000 ↓ 0.0 0

Index Scan using personemploymentenddate on person_employment pe_3 (cost=0.43..0.72 rows=2 width=13) (never executed)

  • Index Cond: ((personid = pp_2.personid) AND (CURRENT_DATE <= enddate) AND (CURRENT_DATE >= effectivedate))
  • Filter: ((emplstatus <> 'T'::bpchar) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
119. 16.539 230.062 ↓ 1.3 72,145 1

Hash (cost=26,244.81..26,244.81 rows=55,767 width=47) (actual time=230.062..230.062 rows=72,145 loops=1)

  • Buckets: 131072 (originally 65536) Batches: 1 (originally 1) Memory Usage: 6507kB
120. 7.032 213.523 ↓ 1.3 72,145 1

Subquery Scan on budgetorg (cost=25,344.11..26,244.81 rows=55,767 width=47) (actual time=187.105..213.523 rows=72,145 loops=1)

121. 11.832 206.491 ↓ 1.3 72,145 1

Unique (cost=25,344.11..25,687.14 rows=55,767 width=47) (actual time=187.103..206.491 rows=72,145 loops=1)

122. 36.317 194.659 ↓ 1.1 76,402 1

Sort (cost=25,344.11..25,515.63 rows=68,605 width=47) (actual time=187.100..194.659 rows=76,402 loops=1)

  • Sort Key: ccorg.positionid, ccorg.posorgpercent DESC
  • Sort Method: quicksort Memory: 10509kB
123. 8.238 158.342 ↓ 1.1 76,402 1

Merge Join (cost=1,859.14..19,833.07 rows=68,605 width=47) (actual time=8.753..158.342 rows=76,402 loops=1)

  • Merge Cond: (cc.organizationid = ccorg.organizationid)
124. 3.339 20.298 ↓ 1.1 13,690 1

Merge Join (cost=1,858.60..3,322.89 rows=12,984 width=42) (actual time=8.533..20.298 rows=13,690 loops=1)

  • Merge Cond: (cc.organizationid = corb.organizationid)
125. 7.132 7.132 ↓ 1.0 13,703 1

Index Scan using organizatincodeeffective on organization_code cc (cost=0.29..1,243.30 rows=13,646 width=38) (actual time=0.017..7.132 rows=13,703 loops=1)

  • Index Cond: ((CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate))
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 1132
126. 3.900 9.827 ↑ 1.0 14,876 1

Sort (cost=1,858.30..1,897.12 rows=15,528 width=4) (actual time=8.510..9.827 rows=14,876 loops=1)

  • Sort Key: corb.organizationid
  • Sort Method: quicksort Memory: 1087kB
127. 5.927 5.927 ↑ 1.0 14,978 1

Seq Scan on company_organization_rel corb (cost=0.00..777.35 rows=15,528 width=4) (actual time=0.006..5.927 rows=14,978 loops=1)

  • Filter: ((CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 3726
128. 129.806 129.806 ↑ 1.1 77,072 1

Index Scan using posorgrelorganization on pos_org_rel ccorg (cost=0.43..15,596.62 rows=81,985 width=13) (actual time=0.214..129.806 rows=77,072 loops=1)

  • Index Cond: ((CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate))
  • Filter: ((posorgreltype = 'Budget'::bpchar) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 125089
129. 3.377 91.230 ↑ 1.1 19,769 1

Hash (cost=15,844.33..15,844.33 rows=21,274 width=47) (actual time=91.230..91.230 rows=19,769 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1683kB
130. 1.833 87.853 ↑ 1.1 19,769 1

Subquery Scan on matrixorg (cost=15,525.22..15,844.33 rows=21,274 width=47) (actual time=83.227..87.853 rows=19,769 loops=1)

131. 2.096 86.020 ↑ 1.1 19,769 1

Unique (cost=15,525.22..15,631.59 rows=21,274 width=47) (actual time=83.225..86.020 rows=19,769 loops=1)

132. 4.130 83.924 ↑ 1.1 19,770 1

Sort (cost=15,525.22..15,578.41 rows=21,274 width=47) (actual time=83.224..83.924 rows=19,770 loops=1)

  • Sort Key: ccorg_1.positionid, ccorg_1.posorgpercent DESC
  • Sort Method: quicksort Memory: 2482kB
133. 3.507 79.794 ↑ 1.1 19,770 1

Hash Join (cost=2,712.01..13,995.96 rows=21,274 width=47) (actual time=19.466..79.794 rows=19,770 loops=1)

  • Hash Cond: (ccorg_1.organizationid = cc_1.organizationid)
134. 57.399 57.399 ↑ 1.3 19,781 1

Index Scan using posorgrelbyposition on pos_org_rel ccorg_1 (cost=0.42..10,690.29 rows=25,423 width=13) (actual time=0.559..57.399 rows=19,781 loops=1)

  • Index Cond: (posorgreltype = 'Matrix'::bpchar)
  • Filter: ((CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 36984
135. 2.336 18.888 ↓ 1.1 13,792 1

Hash (cost=2,549.28..2,549.28 rows=12,984 width=42) (actual time=18.888..18.888 rows=13,792 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 1164kB
136. 3.155 16.552 ↓ 1.1 13,792 1

Hash Join (cost=971.74..2,549.28 rows=12,984 width=42) (actual time=7.722..16.552 rows=13,792 loops=1)

  • Hash Cond: (cc_1.organizationid = corm.organizationid)
137. 5.762 5.762 ↓ 1.0 13,805 1

Index Scan using organizatincodeeffective on organization_code cc_1 (cost=0.29..1,243.30 rows=13,646 width=38) (actual time=0.024..5.762 rows=13,805 loops=1)

  • Index Cond: ((CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate))
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 1132
138. 1.561 7.635 ↑ 1.0 14,978 1

Hash (cost=777.35..777.35 rows=15,528 width=4) (actual time=7.635..7.635 rows=14,978 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 655kB
139. 6.074 6.074 ↑ 1.0 14,978 1

Seq Scan on company_organization_rel corm (cost=0.00..777.35 rows=15,528 width=4) (actual time=0.016..6.074 rows=14,978 loops=1)

  • Filter: ((CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 3726
140.          

SubPlan (forHash Left Join)

141. 0.000 20.120 ↑ 1.0 1 4,024

Aggregate (cost=4.52..4.53 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=4,024)

142. 20.120 20.120 ↓ 0.0 0 4,024

Index Scan using pk_person_locations on person_locations futurepl (cost=0.42..4.52 rows=1 width=0) (actual time=0.005..0.005 rows=0 loops=4,024)

  • Index Cond: (personid = pe.personid)
  • Filter: ((effectivedate <= enddate) AND (personlocationtype = 'P'::bpchar) AND (CURRENT_DATE < effectivedate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 1
143. 0.000 4.024 ↑ 1.0 1 4,024

Aggregate (cost=3.12..3.13 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=4,024)

144. 4.024 4.024 ↓ 0.0 0 4,024

Index Scan using posorgrelbyposition on pos_org_rel futurepor (cost=0.42..3.11 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=4,024)

  • Index Cond: ((positionid = COALESCE(ppm.positionid, ppm2.positionid)) AND (posorgreltype = 'Member'::bpchar))
  • Filter: ((effectivedate <= enddate) AND (CURRENT_DATE < effectivedate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 0
145. 0.000 4.024 ↑ 1.0 1 4,024

Aggregate (cost=5.43..5.44 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=4,024)

146. 0.000 4.024 ↓ 0.0 0 4,024

Nested Loop (cost=0.71..5.42 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=4,024)

147. 4.024 4.024 ↓ 0.0 0 4,024

Index Scan using posorgrelbyposition on pos_org_rel ccorg_2 (cost=0.42..3.09 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=4,024)

  • Index Cond: ((positionid = COALESCE(ppm.positionid, ppm2.positionid)) AND (posorgreltype = 'Budget'::bpchar))
  • Filter: (CURRENT_DATE < effectivedate)
  • Rows Removed by Filter: 0
148. 0.081 0.081 ↑ 1.0 1 27

Index Scan using organizationcodeorganizationid on organization_code cc_2 (cost=0.29..2.33 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=27)

  • Index Cond: (organizationid = ccorg_2.organizationid)
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts) AND (CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate))
  • Rows Removed by Filter: 1
149. 20.120 20.120 ↓ 0.0 0 4,024

Index Scan using personemploymentenddate on person_employment pef (cost=0.42..2.50 rows=1 width=0) (actual time=0.005..0.005 rows=0 loops=4,024)

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

Index Scan using personemploymentenddate on person_employment pef_1 (cost=0.42..7,237.07 rows=335 width=13) (never executed)

  • Index Cond: (effectivedate > CURRENT_DATE)
  • Filter: ((effectivedate <= enddate) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
151. 16.096 16.096 ↓ 0.0 0 4,024

Index Scan using personemploymentenddate on person_employment pefs (cost=0.42..2.50 rows=1 width=0) (actual time=0.004..0.004 rows=0 loops=4,024)

  • Index Cond: ((personid = pe.personid) AND (effectivedate > CURRENT_DATE))
  • Filter: ((effectivedate <= enddate) AND ((emplstatus)::text <> "*SELECT* 1".emplstatus) AND (CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts))
  • Rows Removed by Filter: 0
152. 0.000 4.024 ↑ 1.0 1 4,024

Aggregate (cost=4.79..4.79 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=4,024)

153. 0.000 4.024 ↓ 0.0 0 4,024

Nested Loop (cost=0.71..4.78 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=4,024)

154. 4.024 4.024 ↓ 0.0 0 4,024

Index Scan using posorgrelbyposition on pos_org_rel ccorg_3 (cost=0.42..2.45 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=4,024)

  • Index Cond: ((positionid = COALESCE(ppm.positionid, ppm2.positionid)) AND (posorgreltype = 'Matrix'::bpchar))
  • Filter: (CURRENT_DATE < effectivedate)
  • Rows Removed by Filter: 0
155. 0.087 0.087 ↑ 1.0 1 29

Index Scan using organizationcodeorganizationid on organization_code cc_3 (cost=0.29..2.33 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=29)

  • Index Cond: (organizationid = ccorg_3.organizationid)
  • Filter: ((CURRENT_TIMESTAMP >= createts) AND (CURRENT_TIMESTAMP <= endts) AND (CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate))
  • Rows Removed by Filter: 1
156. 0.391 59.158 ↓ 201.2 4,024 1

Hash (cost=68.99..68.99 rows=20 width=13) (actual time=59.158..59.158 rows=4,024 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 209kB
157. 0.760 58.767 ↓ 201.2 4,024 1

Unique (cost=68.69..68.79 rows=20 width=13) (actual time=57.697..58.767 rows=4,024 loops=1)

158. 18.709 58.007 ↓ 415.6 8,311 1

Sort (cost=68.69..68.74 rows=20 width=13) (actual time=57.697..58.007 rows=8,311 loops=1)

  • Sort Key: sc.subjectid
  • Sort Method: quicksort Memory: 774kB
159. 0.515 39.298 ↓ 415.6 8,311 1

Nested Loop (cost=3.46..68.26 rows=20 width=13) (actual time=0.099..39.298 rows=8,311 loops=1)

160. 0.061 0.061 ↓ 3.8 19 1

Index Only Scan using security_control_override_uid on security_control_override sco (cost=0.56..6.70 rows=5 width=9) (actual time=0.029..0.061 rows=19 loops=1)

  • Index Cond: ((companyid = 876) AND (domainobject = 'CompanyStaffItem'::text) AND (action = 'Read'::text))
  • Heap Fetches: 19
161. 37.468 38.722 ↓ 109.2 437 19

Bitmap Heap Scan on security_cache_127288 sc (cost=2.90..12.27 rows=4 width=69) (actual time=0.073..2.038 rows=437 loops=19)

  • Recheck Cond: (roledata @> (json_build_object('items', ((('["'::text || (sco.rolename)::text) || '"]'::text))::jsonb))::jsonb)
  • Filter: (active AND (userid = '127288'::bpchar) AND ((subject)::text = 'person'::text))
  • Rows Removed by Filter: 653
  • Heap Blocks: exact=405
162. 1.254 1.254 ↓ 109.0 1,090 19

Bitmap Index Scan on security_cache_role_gin_127288 (cost=0.00..2.90 rows=10 width=0) (actual time=0.066..0.066 rows=1,090 loops=19)

  • Index Cond: (roledata @> (json_build_object('items', ((('["'::text || (sco.rolename)::text) || '"]'::text))::jsonb))::jsonb)