explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0zie

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 46,247.135 ↑ 1.0 1 1

Limit (cost=7,223,012.18..7,223,012.23 rows=1 width=0) (actual time=46,247.134..46,247.135 rows=1 loops=1)

  • Output: ($18), ($39), ($60), ($79)
2.          

Initplan (forLimit)

3. 4.146 22,842.053 ↑ 1.0 1 1

Aggregate (cost=3,502,928.15..3,502,928.16 rows=1 width=0) (actual time=22,842.053..22,842.053 rows=1 loops=1)

  • Output: count(*)
4.          

Initplan (forAggregate)

5. 0.003 0.063 ↓ 0.0 0 1

Nested Loop (cost=0.57..31.64 rows=1 width=0) (actual time=0.063..0.063 rows=0 loops=1)

6. 0.008 0.008 ↓ 2.0 2 1

Index Only Scan using userpolicyset_pkey on e390456bb6e840b6ad8d2732a449fdc8.userpolicyset userpolicyset5 (cost=0.29..2.30 rows=1 width=16) (actual time=0.006..0.008 rows=2 loops=1)

  • Output: userpolicyset5.userid, userpolicyset5.policysetid
  • Index Cond: (userpolicyset5.userid = 4664)
  • Heap Fetches: 1
7. 0.052 0.052 ↓ 0.0 0 2

Index Scan using ixpskvpolicysetid on e390456bb6e840b6ad8d2732a449fdc8.policysetkeyvalue policysetkeyvalue6 (cost=0.28..29.32 rows=1 width=16) (actual time=0.026..0.026 rows=0 loops=2)

  • Output: policysetkeyvalue6.id, policysetkeyvalue6.policysetid, policysetkeyvalue6.parentid, policysetkeyvalue6.index, policysetkeyvalue6.key, policysetkeyvalue6.uri, policysetkeyvalue6.slug, policysetkeyvalue6."boolean", policysetke (...)
  • Index Cond: (policysetkeyvalue6.policysetid = userpolicyset5.policysetid)
  • Filter: ((policysetkeyvalue6.parentid IS NULL) AND (upper(policysetkeyvalue6.key) = 'URN:REPLICON:POLICY:TIME-OFF:CAN-USER-VIEW-ALL-TIME-OFF'::text) AND (upper(policysetkeyvalue6.uri) = 'URN:REPLICON:POLICY:TIME-OFF:CAN-USER-VIEW-AL (...)
  • Rows Removed by Filter: 16
8. 21.793 22,837.844 ↓ 1.4 13,977 1

Merge Semi Join (cost=14,116.30..3,502,871.54 rows=9,990 width=0) (actual time=1,988.361..22,837.844 rows=13,977 loops=1)

  • Merge Cond: ("*SELECT* 1_1".timeoffid = timeoffs1.id)
9. 12.702 566.011 ↑ 1.4 13,977 1

Sort (cost=12,521.79..12,571.74 rows=19,980 width=12) (actual time=560.513..566.011 rows=13,977 loops=1)

  • Output: "*SELECT* 1_1".timeoffid, timeoffs11.id, timeoffs12.id
  • Sort Key: "*SELECT* 1_1".timeoffid
  • Sort Method: quicksort Memory: 1040kB
10. 12.770 553.309 ↑ 1.4 13,977 1

Nested Loop Semi Join (cost=116.04..11,094.59 rows=19,980 width=12) (actual time=0.725..553.309 rows=13,977 loops=1)

  • Output: "*SELECT* 1_1".timeoffid, timeoffs11.id, timeoffs12.id
11. 15.426 106.270 ↑ 1.0 39,479 1

Append (cost=114.55..9,466.31 rows=39,960 width=4) (actual time=0.664..106.270 rows=39,479 loops=1)

12. 17.121 88.853 ↑ 1.0 38,603 1

Subquery Scan on *SELECT* 1_1 (cost=114.55..8,171.08 rows=39,086 width=4) (actual time=0.664..88.853 rows=38,603 loops=1)

  • Output: "*SELECT* 1_1".timeoffid
13. 35.322 71.732 ↑ 1.0 38,603 1

Hash Anti Join (cost=114.55..7,780.22 rows=39,086 width=4) (actual time=0.663..71.732 rows=38,603 loops=1)

  • Output: dm_timeofflist_facts_1.timeoffid, NULL::text, NULL::integer, NULL::text, NULL::text, NULL::boolean, NULL::integer, NULL::text, NULL::text, NULL::integer, NULL::text, NULL::text, NULL::integer, NULL::tex (...)
  • Hash Cond: (dm_timeofflist_facts_1.timeoffid = dm_timeofflist_realtime_facts_2.timeoffid)
14. 35.757 35.757 ↑ 1.0 39,479 1

Seq Scan on e390456bb6e840b6ad8d2732a449fdc8.dm_timeofflist_facts dm_timeofflist_facts_1 (cost=0.00..7,170.74 rows=39,479 width=4) (actual time=0.004..35.757 rows=39,479 loops=1)

  • Output: dm_timeofflist_facts_1.timeoffid, dm_timeofflist_facts_1.timeoffslug, dm_timeofflist_facts_1.timeoffowneruserid, dm_timeofflist_facts_1.timeoffowneruserdisplayname, dm_timeofflist_facts_1.timeoffo (...)
  • Filter: (dm_timeofflist_facts_1.timeoffcodeid <> 5)
  • Rows Removed by Filter: 52820
15. 0.653 0.653 ↓ 1.0 921 1

Hash (cost=103.06..103.06 rows=919 width=4) (actual time=0.653..0.653 rows=921 loops=1)

  • Output: dm_timeofflist_realtime_facts_2.timeoffid
  • Buckets: 1024 Batches: 1 Memory Usage: 41kB
  • -> Index Only Scan using dm_timeofflist_realtime_facts_pkey on e390456bb6e840b6ad8d2732a449fdc8.dm_timeofflist_realtime_facts dm_timeofflist_realtime_facts_2 (cost=0.28..103.06 rows=919 width=4) (actual (...)
  • Output: dm_timeofflist_realtime_facts_2.timeoffid
  • Heap Fetches: 400
16. 0.420 1.991 ↓ 1.0 876 1

Subquery Scan on *SELECT* 2_1 (cost=0.00..1,295.23 rows=874 width=4) (actual time=0.009..1.991 rows=876 loops=1)

  • Output: "*SELECT* 2_1".timeoffid
17. 1.571 1.571 ↓ 1.0 876 1

Seq Scan on e390456bb6e840b6ad8d2732a449fdc8.dm_timeofflist_realtime_facts dm_timeofflist_realtime_facts_3 (cost=0.00..1,286.49 rows=874 width=4) (actual time=0.009..1.571 rows=876 loops=1)

  • Output: dm_timeofflist_realtime_facts_3.timeoffid, NULL::text, NULL::integer, NULL::text, NULL::text, NULL::boolean, NULL::integer, NULL::text, NULL::text, NULL::integer, NULL::text, NULL::text, NULL::integer, (...)
  • Filter: ((NOT dm_timeofflist_realtime_facts_3.deleted) AND (dm_timeofflist_realtime_facts_3.timeoffcodeid <> 5))
  • Rows Removed by Filter: 45
18. 104.459 434.269 ↓ 0.0 0 39,479

Hash Semi Join (cost=1.49..1.52 rows=1 width=8) (actual time=0.011..0.011 rows=0 loops=39,479)

  • Output: timeoffs11.id, timeoffs12.id
  • Hash Cond: (timeoffs11.id = timeoffs12.id)
19. 13.978 13.978 ↑ 1.0 1 13,978

Index Only Scan using timeoffs_pkey on e390456bb6e840b6ad8d2732a449fdc8.timeoffs timeoffs11 (cost=0.42..0.45 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=13,978)

  • Output: timeoffs11.id
  • Index Cond: (timeoffs11.id = "*SELECT* 1_1".timeoffid)
  • Heap Fetches: 0
20. 39.479 315.832 ↓ 0.0 0 39,479

Hash (cost=1.06..1.06 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=39,479)

  • Output: timeoffs12.id
  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
21. 78.958 276.353 ↓ 0.0 0 39,479

Nested Loop Semi Join (cost=0.84..1.06 rows=1 width=4) (actual time=0.007..0.007 rows=0 loops=39,479)

  • Output: timeoffs12.id
  • -> Index Only Scan using locationflathierarchy_pkey on e390456bb6e840b6ad8d2732a449fdc8.locationflathierarchy locationflathierarchy14 (cost=0.14..0.16 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=3 (...)
22. 39.481 197.395 ↑ 1.0 1 39,479

Nested Loop (cost=0.70..0.88 rows=1 width=20) (actual time=0.004..0.005 rows=1 loops=39,479)

  • Output: timeoffs12.id, userlocation13.locationid
  • Output: locationflathierarchy14.parentid, locationflathierarchy14.childid
  • Index Cond: ((locationflathierarchy14.parentid = '9caacfef-323b-49b2-bd43-565b0dbd36e1'::uuid) AND (locationflathierarchy14.childid = userlocation13.locationid))
  • Heap Fetches: 13977
23. 78.958 78.958 ↑ 1.0 1 39,479

Index Scan using timeoffs_pkey on e390456bb6e840b6ad8d2732a449fdc8.timeoffs timeoffs12 (cost=0.42..0.56 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=39,479)

  • Output: timeoffs12.id, timeoffs12.guidid, timeoffs12.userid, timeoffs12.createdbyuserid, timeoffs12.timeoffcodeid, timeoffs12.startdate, timeoffs12.enddate, timeoffs12.startdurationtype, timeoffs12. (...)
  • Index Cond: (timeoffs12.id = "*SELECT* 1_1".timeoffid)
24. 78.956 78.956 ↑ 1.0 1 39,478

Index Only Scan using ix4ul_userlocationstartend on e390456bb6e840b6ad8d2732a449fdc8.userlocation userlocation13 (cost=0.29..0.31 rows=1 width=28) (actual time=0.001..0.002 rows=1 loops=39,478)

  • Output: userlocation13.userid, userlocation13.locationid, userlocation13.startdate, userlocation13.enddate
  • Index Cond: ((userlocation13.userid = timeoffs12.userid) AND (userlocation13.startdate <= timeoffs12.enddate) AND (userlocation13.enddate >= timeoffs12.startdate))
  • Heap Fetches: 4037
25. 3,255.122 22,250.040 ↑ 1.6 48,064 1

Index Scan using timeoffs_pkey on e390456bb6e840b6ad8d2732a449fdc8.timeoffs timeoffs1 (cost=1,594.51..3,489,899.99 rows=75,001 width=4) (actual time=1,427.842..22,250.040 rows=48,064 loops=1)

  • Output: timeoffs1.id, timeoffs1.guidid, timeoffs1.userid, timeoffs1.createdbyuserid, timeoffs1.timeoffcodeid, timeoffs1.startdate, timeoffs1.enddate, timeoffs1.startdurationtype, timeoffs1.enddurationtype, timeoffs1.startduration, tim (...)
  • Filter: ((timeoffs1.userid = 4664) OR (hashed SubPlan 1) OR $6 OR ((SubPlan 3) AND (hashed SubPlan 4)))
  • Rows Removed by Filter: 44232
26.          

SubPlan (forIndex Scan)

27. 23.360 63.622 ↓ 51.6 48,064 1

Nested Loop (cost=0.84..1,167.08 rows=932 width=4) (actual time=0.428..63.622 rows=48,064 loops=1)

  • Output: timeoffs2.id
28. 1.941 3.732 ↓ 4.8 3,653 1

Nested Loop (cost=0.42..151.51 rows=763 width=12) (actual time=0.014..3.732 rows=3,653 loops=1)

  • Output: userlocation3.userid, userlocation3.startdate, userlocation3.enddate
29. 0.006 0.006 ↑ 1.0 1 1

Index Only Scan using locationflathierarchy_pkey on e390456bb6e840b6ad8d2732a449fdc8.locationflathierarchy locationflathierarchy4 (cost=0.14..1.16 rows=1 width=16) (actual time=0.004..0.006 rows=1 loops=1)

  • Output: locationflathierarchy4.parentid, locationflathierarchy4.childid
  • Index Cond: (locationflathierarchy4.parentid = '9caacfef-323b-49b2-bd43-565b0dbd36e1'::uuid)
  • Heap Fetches: 1
30. 1.785 1.785 ↓ 4.8 3,653 1

Index Scan using ixullocationid on e390456bb6e840b6ad8d2732a449fdc8.userlocation userlocation3 (cost=0.29..142.72 rows=763 width=28) (actual time=0.008..1.785 rows=3,653 loops=1)

  • Output: userlocation3.id, userlocation3.userid, userlocation3.locationid, userlocation3.startdate, userlocation3.enddate
  • Index Cond: (userlocation3.locationid = locationflathierarchy4.childid)
31. 36.530 36.530 ↓ 6.5 13 3,653

Index Scan using ixto4userid on e390456bb6e840b6ad8d2732a449fdc8.timeoffs timeoffs2 (cost=0.42..1.31 rows=2 width=16) (actual time=0.002..0.010 rows=13 loops=3,653)

  • Output: timeoffs2.id, timeoffs2.guidid, timeoffs2.userid, timeoffs2.createdbyuserid, timeoffs2.timeoffcodeid, timeoffs2.startdate, timeoffs2.enddate, timeoffs2.startdurationtype, timeoffs2.enddurationtype, timeoffs2.star (...)
  • Index Cond: ((timeoffs2.userid = userlocation3.userid) AND (userlocation3.enddate >= timeoffs2.startdate) AND (userlocation3.startdate <= timeoffs2.enddate))
32. 18,931.296 18,931.296 ↓ 9.0 314 44,232

Index Scan using ix3usa_ededst on e390456bb6e840b6ad8d2732a449fdc8.userscheduleassignment userscheduleassignment7 (cost=0.29..75.05 rows=35 width=4) (actual time=0.302..0.428 rows=314 loops=44,232)

  • Output: userscheduleassignment7.userid
  • Index Cond: ((userscheduleassignment7.effectivedate <= timeoffs1.startdate) AND (userscheduleassignment7.enddate >= timeoffs1.enddate) AND (userscheduleassignment7.scheduletype = 1))
33. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.71..422.78 rows=763 width=4) (never executed)

  • Output: userinfo8.id
34. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.42..155.32 rows=763 width=4) (never executed)

  • Output: userlocation9.userid
35. 0.000 0.000 ↓ 0.0 0

Index Only Scan using locationflathierarchy_pkey on e390456bb6e840b6ad8d2732a449fdc8.locationflathierarchy locationflathierarchy10 (cost=0.14..1.16 rows=1 width=16) (never executed)

  • Output: locationflathierarchy10.parentid, locationflathierarchy10.childid
  • Index Cond: (locationflathierarchy10.parentid = '9caacfef-323b-49b2-bd43-565b0dbd36e1'::uuid)
  • Heap Fetches: 0
36. 0.000 0.000 ↓ 0.0 0

Index Scan using ixullocationid on e390456bb6e840b6ad8d2732a449fdc8.userlocation userlocation9 (cost=0.29..146.54 rows=763 width=20) (never executed)

  • Output: userlocation9.id, userlocation9.userid, userlocation9.locationid, userlocation9.startdate, userlocation9.enddate
  • Index Cond: (userlocation9.locationid = locationflathierarchy10.childid)
  • Filter: ((userlocation9.startdate <= '2019-10-08'::date) AND (userlocation9.enddate >= '2019-10-08'::date))
37. 0.000 0.000 ↓ 0.0 0

Index Only Scan using userinfo_pkey on e390456bb6e840b6ad8d2732a449fdc8.userinfo userinfo8 (cost=0.29..0.34 rows=1 width=4) (never executed)

  • Output: userinfo8.id
  • Index Cond: (userinfo8.id = userlocation9.userid)
  • Heap Fetches: 0
38. 0.019 112.805 ↑ 1.0 1 1

Aggregate (cost=10,520.25..10,520.26 rows=1 width=0) (actual time=112.805..112.805 rows=1 loops=1)

  • Output: count(*)
39.          

Initplan (forAggregate)

40. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.57..31.64 rows=1 width=0) (never executed)

41. 0.000 0.000 ↓ 0.0 0

Index Only Scan using userpolicyset_pkey on e390456bb6e840b6ad8d2732a449fdc8.userpolicyset userpolicyset20 (cost=0.29..2.30 rows=1 width=16) (never executed)

  • Output: userpolicyset20.userid, userpolicyset20.policysetid
  • Index Cond: (userpolicyset20.userid = 4664)
  • Heap Fetches: 0
42. 0.000 0.000 ↓ 0.0 0

Index Scan using ixpskvpolicysetid on e390456bb6e840b6ad8d2732a449fdc8.policysetkeyvalue policysetkeyvalue21 (cost=0.28..29.32 rows=1 width=16) (never executed)

  • Output: policysetkeyvalue21.id, policysetkeyvalue21.policysetid, policysetkeyvalue21.parentid, policysetkeyvalue21.index, policysetkeyvalue21.key, policysetkeyvalue21.uri, policysetkeyvalue21.slug, policysetkeyvalue21."boolean", pol (...)
  • Index Cond: (policysetkeyvalue21.policysetid = userpolicyset20.policysetid)
  • Filter: ((policysetkeyvalue21.parentid IS NULL) AND (upper(policysetkeyvalue21.key) = 'URN:REPLICON:POLICY:TIME-OFF:CAN-USER-VIEW-ALL-TIME-OFF'::text) AND (upper(policysetkeyvalue21.uri) = 'URN:REPLICON:POLICY:TIME-OFF:CAN-USER-VIEW (...)
43. 0.073 112.786 ↓ 56.0 56 1

Nested Loop Semi Join (cost=1,596.04..10,488.61 rows=1 width=0) (actual time=85.992..112.786 rows=56 loops=1)

  • Join Filter: ("*SELECT* 1_2".timeoffid = timeoffs16.id)
44. 0.071 27.033 ↓ 56.0 56 1

Nested Loop Semi Join (cost=1.53..8,856.29 rows=1 width=12) (actual time=0.426..27.033 rows=56 loops=1)

  • Output: "*SELECT* 1_2".timeoffid, timeoffs26.id, timeoffs27.id
45. 0.052 25.714 ↓ 2.3 104 1

Append (cost=0.28..8,740.43 rows=45 width=4) (actual time=0.171..25.714 rows=104 loops=1)

46. 0.060 24.585 ↓ 2.4 104 1

Subquery Scan on *SELECT* 1_2 (cost=0.28..7,451.63 rows=44 width=4) (actual time=0.171..24.585 rows=104 loops=1)

  • Output: "*SELECT* 1_2".timeoffid
47. 0.304 24.525 ↓ 2.4 104 1

Nested Loop Anti Join (cost=0.28..7,451.19 rows=44 width=4) (actual time=0.170..24.525 rows=104 loops=1)

  • Output: dm_timeofflist_facts_2.timeoffid, NULL::text, NULL::integer, NULL::text, NULL::text, NULL::boolean, NULL::integer, NULL::text, NULL::text, NULL::integer, NULL::text, NULL::text, NULL::integer, NULL::text, NUL (...)
  • -> Index Only Scan using dm_timeofflist_realtime_facts_pkey on e390456bb6e840b6ad8d2732a449fdc8.dm_timeofflist_realtime_facts dm_timeofflist_realtime_facts_4 (cost=0.28..1.11 rows=1 width=4) (actual time=0.002..0.0 (...)
48. 24.221 24.221 ↓ 2.4 104 1

Seq Scan on e390456bb6e840b6ad8d2732a449fdc8.dm_timeofflist_facts dm_timeofflist_facts_2 (cost=0.00..7,401.48 rows=44 width=4) (actual time=0.158..24.221 rows=104 loops=1)

  • Output: dm_timeofflist_facts_2.timeoffid, dm_timeofflist_facts_2.timeoffslug, dm_timeofflist_facts_2.timeoffowneruserid, dm_timeofflist_facts_2.timeoffowneruserdisplayname, dm_timeofflist_facts_2.timeoffownerus (...)
  • Filter: ((dm_timeofflist_facts_2.timeoffcodeid <> 5) AND (dm_timeofflist_facts_2.timeoffstatus = 3))
  • Rows Removed by Filter: 92195
  • Output: dm_timeofflist_realtime_facts_4.timeoffid
  • Index Cond: (dm_timeofflist_realtime_facts_4.timeoffid = dm_timeofflist_facts_2.timeoffid)
  • Heap Fetches: 0
49. 0.001 1.077 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2_2 (cost=0.00..1,288.80 rows=1 width=4) (actual time=1.077..1.077 rows=0 loops=1)

  • Output: "*SELECT* 2_2".timeoffid
50. 1.076 1.076 ↓ 0.0 0 1

Seq Scan on e390456bb6e840b6ad8d2732a449fdc8.dm_timeofflist_realtime_facts dm_timeofflist_realtime_facts_5 (cost=0.00..1,288.79 rows=1 width=4) (actual time=1.076..1.076 rows=0 loops=1)

  • Output: dm_timeofflist_realtime_facts_5.timeoffid, NULL::text, NULL::integer, NULL::text, NULL::text, NULL::boolean, NULL::integer, NULL::text, NULL::text, NULL::integer, NULL::text, NULL::text, NULL::integer, NULL:: (...)
  • Filter: ((NOT dm_timeofflist_realtime_facts_5.deleted) AND (dm_timeofflist_realtime_facts_5.timeoffcodeid <> 5) AND (dm_timeofflist_realtime_facts_5.timeoffstatus = 3))
  • Rows Removed by Filter: 921
51. 0.208 1.248 ↑ 1.0 1 104

Nested Loop Semi Join (cost=1.26..2.54 rows=1 width=8) (actual time=0.012..0.012 rows=1 loops=104)

  • Output: timeoffs26.id, timeoffs27.id
52. 0.208 0.208 ↑ 1.0 1 104

Index Only Scan using timeoffs_pkey on e390456bb6e840b6ad8d2732a449fdc8.timeoffs timeoffs26 (cost=0.42..1.39 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=104)

  • Output: timeoffs26.id
  • Index Cond: (timeoffs26.id = "*SELECT* 1_2".timeoffid)
  • Heap Fetches: 0
53. 0.104 0.832 ↑ 1.0 1 104

Nested Loop Semi Join (cost=0.84..0.99 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=104)

  • Output: timeoffs27.id
54. 0.208 0.624 ↑ 1.0 1 104

Nested Loop (cost=0.70..0.81 rows=1 width=20) (actual time=0.005..0.006 rows=1 loops=104)

  • Output: timeoffs27.id, userlocation28.locationid
55. 0.208 0.208 ↑ 1.0 1 104

Index Scan using timeoffs_pkey on e390456bb6e840b6ad8d2732a449fdc8.timeoffs timeoffs27 (cost=0.42..0.49 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=104)

  • Output: timeoffs27.id, timeoffs27.guidid, timeoffs27.userid, timeoffs27.createdbyuserid, timeoffs27.timeoffcodeid, timeoffs27.startdate, timeoffs27.enddate, timeoffs27.startdurationtype, timeoffs27.enddurationt (...)
  • Index Cond: (timeoffs27.id = timeoffs26.id)
56. 0.208 0.208 ↑ 1.0 1 104

Index Only Scan using ix4ul_userlocationstartend on e390456bb6e840b6ad8d2732a449fdc8.userlocation userlocation28 (cost=0.29..0.31 rows=1 width=28) (actual time=0.002..0.002 rows=1 loops=104)

  • Output: userlocation28.userid, userlocation28.locationid, userlocation28.startdate, userlocation28.enddate
  • Index Cond: ((userlocation28.userid = timeoffs27.userid) AND (userlocation28.startdate <= timeoffs27.enddate) AND (userlocation28.enddate >= timeoffs27.startdate))
  • Heap Fetches: 14
57. 0.104 0.104 ↑ 1.0 1 104

Index Only Scan using locationflathierarchy_pkey on e390456bb6e840b6ad8d2732a449fdc8.locationflathierarchy locationflathierarchy29 (cost=0.14..0.16 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=104)

  • Output: locationflathierarchy29.parentid, locationflathierarchy29.childid
  • Index Cond: ((locationflathierarchy29.parentid = '9caacfef-323b-49b2-bd43-565b0dbd36e1'::uuid) AND (locationflathierarchy29.childid = userlocation28.locationid))
  • Heap Fetches: 56
58. 21.653 85.680 ↑ 1.0 1 56

Index Scan using timeoffs_pkey on e390456bb6e840b6ad8d2732a449fdc8.timeoffs timeoffs16 (cost=1,594.51..1,632.31 rows=1 width=4) (actual time=1.530..1.530 rows=1 loops=56)

  • Output: timeoffs16.id, timeoffs16.guidid, timeoffs16.userid, timeoffs16.createdbyuserid, timeoffs16.timeoffcodeid, timeoffs16.startdate, timeoffs16.enddate, timeoffs16.startdurationtype, timeoffs16.enddurationtype, timeoffs16.startdur (...)
  • Index Cond: (timeoffs16.id = timeoffs26.id)
  • Filter: ((timeoffs16.userid = 4664) OR (hashed SubPlan 6) OR $25 OR ((SubPlan 8) AND (hashed SubPlan 9)))
59.          

SubPlan (forIndex Scan)

60. 23.897 64.027 ↓ 51.6 48,064 1

Nested Loop (cost=0.84..1,167.08 rows=932 width=4) (actual time=0.445..64.027 rows=48,064 loops=1)

  • Output: timeoffs17.id
61. 1.780 3.600 ↓ 4.8 3,653 1

Nested Loop (cost=0.42..151.51 rows=763 width=12) (actual time=0.013..3.600 rows=3,653 loops=1)

  • Output: userlocation18.userid, userlocation18.startdate, userlocation18.enddate
62. 0.003 0.003 ↑ 1.0 1 1

Index Only Scan using locationflathierarchy_pkey on e390456bb6e840b6ad8d2732a449fdc8.locationflathierarchy locationflathierarchy19 (cost=0.14..1.16 rows=1 width=16) (actual time=0.002..0.003 rows=1 loops=1)

  • Output: locationflathierarchy19.parentid, locationflathierarchy19.childid
  • Index Cond: (locationflathierarchy19.parentid = '9caacfef-323b-49b2-bd43-565b0dbd36e1'::uuid)
  • Heap Fetches: 1
63. 1.817 1.817 ↓ 4.8 3,653 1

Index Scan using ixullocationid on e390456bb6e840b6ad8d2732a449fdc8.userlocation userlocation18 (cost=0.29..142.72 rows=763 width=28) (actual time=0.008..1.817 rows=3,653 loops=1)

  • Output: userlocation18.id, userlocation18.userid, userlocation18.locationid, userlocation18.startdate, userlocation18.enddate
  • Index Cond: (userlocation18.locationid = locationflathierarchy19.childid)
64. 36.530 36.530 ↓ 6.5 13 3,653

Index Scan using ixto4userid on e390456bb6e840b6ad8d2732a449fdc8.timeoffs timeoffs17 (cost=0.42..1.31 rows=2 width=16) (actual time=0.002..0.010 rows=13 loops=3,653)

  • Output: timeoffs17.id, timeoffs17.guidid, timeoffs17.userid, timeoffs17.createdbyuserid, timeoffs17.timeoffcodeid, timeoffs17.startdate, timeoffs17.enddate, timeoffs17.startdurationtype, timeoffs17.enddurationtype, timeo (...)
  • Index Cond: ((timeoffs17.userid = userlocation18.userid) AND (userlocation18.enddate >= timeoffs17.startdate) AND (userlocation18.startdate <= timeoffs17.enddate))
65. 0.000 0.000 ↓ 0.0 0

Index Scan using ix3usa_ededst on e390456bb6e840b6ad8d2732a449fdc8.userscheduleassignment userscheduleassignment22 (cost=0.29..75.05 rows=35 width=4) (never executed)

  • Output: userscheduleassignment22.userid
  • Index Cond: ((userscheduleassignment22.effectivedate <= timeoffs16.startdate) AND (userscheduleassignment22.enddate >= timeoffs16.enddate) AND (userscheduleassignment22.scheduletype = 1))
66. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.71..422.78 rows=763 width=4) (never executed)

  • Output: userinfo23.id
67. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.42..155.32 rows=763 width=4) (never executed)

  • Output: userlocation24.userid
68. 0.000 0.000 ↓ 0.0 0

Index Only Scan using locationflathierarchy_pkey on e390456bb6e840b6ad8d2732a449fdc8.locationflathierarchy locationflathierarchy25 (cost=0.14..1.16 rows=1 width=16) (never executed)

  • Output: locationflathierarchy25.parentid, locationflathierarchy25.childid
  • Index Cond: (locationflathierarchy25.parentid = '9caacfef-323b-49b2-bd43-565b0dbd36e1'::uuid)
  • Heap Fetches: 0
69. 0.000 0.000 ↓ 0.0 0

Index Scan using ixullocationid on e390456bb6e840b6ad8d2732a449fdc8.userlocation userlocation24 (cost=0.29..146.54 rows=763 width=20) (never executed)

  • Output: userlocation24.id, userlocation24.userid, userlocation24.locationid, userlocation24.startdate, userlocation24.enddate
  • Index Cond: (userlocation24.locationid = locationflathierarchy25.childid)
  • Filter: ((userlocation24.startdate <= '2019-10-08'::date) AND (userlocation24.enddate >= '2019-10-08'::date))
70. 0.000 0.000 ↓ 0.0 0

Index Only Scan using userinfo_pkey on e390456bb6e840b6ad8d2732a449fdc8.userinfo userinfo23 (cost=0.29..0.34 rows=1 width=4) (never executed)

  • Output: userinfo23.id
  • Index Cond: (userinfo23.id = userlocation24.userid)
  • Heap Fetches: 0
71. 0.079 118.613 ↑ 1.0 1 1

Aggregate (cost=206,432.01..206,432.02 rows=1 width=0) (actual time=118.613..118.613 rows=1 loops=1)

  • Output: count(*)
72.          

Initplan (forAggregate)

73. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.57..31.64 rows=1 width=0) (never executed)

74. 0.000 0.000 ↓ 0.0 0

Index Only Scan using userpolicyset_pkey on e390456bb6e840b6ad8d2732a449fdc8.userpolicyset userpolicyset35 (cost=0.29..2.30 rows=1 width=16) (never executed)

  • Output: userpolicyset35.userid, userpolicyset35.policysetid
  • Index Cond: (userpolicyset35.userid = 4664)
  • Heap Fetches: 0
75. 0.000 0.000 ↓ 0.0 0

Index Scan using ixpskvpolicysetid on e390456bb6e840b6ad8d2732a449fdc8.policysetkeyvalue policysetkeyvalue36 (cost=0.28..29.32 rows=1 width=16) (never executed)

  • Output: policysetkeyvalue36.id, policysetkeyvalue36.policysetid, policysetkeyvalue36.parentid, policysetkeyvalue36.index, policysetkeyvalue36.key, policysetkeyvalue36.uri, policysetkeyvalue36.slug, policysetkeyvalue36."boolean", pol (...)
  • Index Cond: (policysetkeyvalue36.policysetid = userpolicyset35.policysetid)
  • Filter: ((policysetkeyvalue36.parentid IS NULL) AND (upper(policysetkeyvalue36.key) = 'URN:REPLICON:POLICY:TIME-OFF:CAN-USER-VIEW-ALL-TIME-OFF'::text) AND (upper(policysetkeyvalue36.uri) = 'URN:REPLICON:POLICY:TIME-OFF:CAN-USER-VIEW (...)
76. 0.192 118.534 ↓ 4.7 279 1

Nested Loop Semi Join (cost=1,596.04..206,400.22 rows=60 width=0) (actual time=85.447..118.534 rows=279 loops=1)

  • Join Filter: ("*SELECT* 1_3".timeoffid = timeoffs31.id)
77. 0.278 32.968 ↓ 2.3 279 1

Nested Loop Semi Join (cost=1.53..9,420.60 rows=121 width=12) (actual time=0.722..32.968 rows=279 loops=1)

  • Output: "*SELECT* 1_3".timeoffid, timeoffs41.id, timeoffs42.id
78. 0.222 27.500 ↓ 2.1 519 1

Append (cost=0.28..8,846.64 rows=242 width=4) (actual time=0.151..27.500 rows=519 loops=1)

79. 0.235 26.184 ↓ 2.3 498 1

Subquery Scan on *SELECT* 1_3 (cost=0.28..7,557.64 rows=220 width=4) (actual time=0.151..26.184 rows=498 loops=1)

  • Output: "*SELECT* 1_3".timeoffid
80. 1.235 25.949 ↓ 2.3 498 1

Nested Loop Anti Join (cost=0.28..7,555.44 rows=220 width=4) (actual time=0.149..25.949 rows=498 loops=1)

  • Output: dm_timeofflist_facts_3.timeoffid, NULL::text, NULL::integer, NULL::text, NULL::text, NULL::boolean, NULL::integer, NULL::text, NULL::text, NULL::integer, NULL::text, NULL::text, NULL::integer, NULL::text, NUL (...)
  • -> Index Only Scan using dm_timeofflist_realtime_facts_pkey on e390456bb6e840b6ad8d2732a449fdc8.dm_timeofflist_realtime_facts dm_timeofflist_realtime_facts_6 (cost=0.28..0.69 rows=1 width=4) (actual time=0.001..0.0 (...)
81. 24.714 24.714 ↓ 2.3 519 1

Seq Scan on e390456bb6e840b6ad8d2732a449fdc8.dm_timeofflist_facts dm_timeofflist_facts_3 (cost=0.00..7,401.48 rows=222 width=4) (actual time=0.140..24.714 rows=519 loops=1)

  • Output: dm_timeofflist_facts_3.timeoffid, dm_timeofflist_facts_3.timeoffslug, dm_timeofflist_facts_3.timeoffowneruserid, dm_timeofflist_facts_3.timeoffowneruserdisplayname, dm_timeofflist_facts_3.timeoffownerus (...)
  • Filter: ((dm_timeofflist_facts_3.timeoffcodeid <> 5) AND (dm_timeofflist_facts_3.timeoffstatus = 1))
  • Rows Removed by Filter: 91780
  • Output: dm_timeofflist_realtime_facts_6.timeoffid
  • Index Cond: (dm_timeofflist_realtime_facts_6.timeoffid = dm_timeofflist_facts_3.timeoffid)
  • Heap Fetches: 11
82. 0.006 1.094 ↑ 1.0 21 1

Subquery Scan on *SELECT* 2_3 (cost=0.00..1,289.01 rows=22 width=4) (actual time=0.021..1.094 rows=21 loops=1)

  • Output: "*SELECT* 2_3".timeoffid
83. 1.088 1.088 ↑ 1.0 21 1

Seq Scan on e390456bb6e840b6ad8d2732a449fdc8.dm_timeofflist_realtime_facts dm_timeofflist_realtime_facts_7 (cost=0.00..1,288.79 rows=22 width=4) (actual time=0.020..1.088 rows=21 loops=1)

  • Output: dm_timeofflist_realtime_facts_7.timeoffid, NULL::text, NULL::integer, NULL::text, NULL::text, NULL::boolean, NULL::integer, NULL::text, NULL::text, NULL::integer, NULL::text, NULL::text, NULL::integer, NULL:: (...)
  • Filter: ((NOT dm_timeofflist_realtime_facts_7.deleted) AND (dm_timeofflist_realtime_facts_7.timeoffcodeid <> 5) AND (dm_timeofflist_realtime_facts_7.timeoffstatus = 1))
  • Rows Removed by Filter: 900
84. 0.519 5.190 ↑ 1.0 1 519

Nested Loop Semi Join (cost=1.26..2.36 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=519)

  • Output: timeoffs41.id, timeoffs42.id
85. 1.038 1.038 ↑ 1.0 1 519

Index Only Scan using timeoffs_pkey on e390456bb6e840b6ad8d2732a449fdc8.timeoffs timeoffs41 (cost=0.42..1.22 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=519)

  • Output: timeoffs41.id
  • Index Cond: (timeoffs41.id = "*SELECT* 1_3".timeoffid)
  • Heap Fetches: 0
86. 0.519 3.633 ↑ 1.0 1 519

Nested Loop Semi Join (cost=0.84..0.99 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=519)

  • Output: timeoffs42.id
87. 0.519 2.595 ↑ 1.0 1 519

Nested Loop (cost=0.70..0.81 rows=1 width=20) (actual time=0.004..0.005 rows=1 loops=519)

  • Output: timeoffs42.id, userlocation43.locationid
88. 1.038 1.038 ↑ 1.0 1 519

Index Scan using timeoffs_pkey on e390456bb6e840b6ad8d2732a449fdc8.timeoffs timeoffs42 (cost=0.42..0.49 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=519)

  • Output: timeoffs42.id, timeoffs42.guidid, timeoffs42.userid, timeoffs42.createdbyuserid, timeoffs42.timeoffcodeid, timeoffs42.startdate, timeoffs42.enddate, timeoffs42.startdurationtype, timeoffs42.enddurationt (...)
  • Index Cond: (timeoffs42.id = timeoffs41.id)
89. 1.038 1.038 ↑ 1.0 1 519

Index Only Scan using ix4ul_userlocationstartend on e390456bb6e840b6ad8d2732a449fdc8.userlocation userlocation43 (cost=0.29..0.31 rows=1 width=28) (actual time=0.001..0.002 rows=1 loops=519)

  • Output: userlocation43.userid, userlocation43.locationid, userlocation43.startdate, userlocation43.enddate
  • Index Cond: ((userlocation43.userid = timeoffs42.userid) AND (userlocation43.startdate <= timeoffs42.enddate) AND (userlocation43.enddate >= timeoffs42.startdate))
  • Heap Fetches: 45
90. 0.519 0.519 ↑ 1.0 1 519

Index Only Scan using locationflathierarchy_pkey on e390456bb6e840b6ad8d2732a449fdc8.locationflathierarchy locationflathierarchy44 (cost=0.14..0.16 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=519)

  • Output: locationflathierarchy44.parentid, locationflathierarchy44.childid
  • Index Cond: ((locationflathierarchy44.parentid = '9caacfef-323b-49b2-bd43-565b0dbd36e1'::uuid) AND (locationflathierarchy44.childid = userlocation43.locationid))
  • Heap Fetches: 279
91. 21.467 85.374 ↑ 1.0 1 279

Index Scan using timeoffs_pkey on e390456bb6e840b6ad8d2732a449fdc8.timeoffs timeoffs31 (cost=1,594.51..1,632.31 rows=1 width=4) (actual time=0.306..0.306 rows=1 loops=279)

  • Output: timeoffs31.id, timeoffs31.guidid, timeoffs31.userid, timeoffs31.createdbyuserid, timeoffs31.timeoffcodeid, timeoffs31.startdate, timeoffs31.enddate, timeoffs31.startdurationtype, timeoffs31.enddurationtype, timeoffs31.startdur (...)
  • Index Cond: (timeoffs31.id = timeoffs41.id)
  • Filter: ((timeoffs31.userid = 4664) OR (hashed SubPlan 11) OR $46 OR ((SubPlan 13) AND (hashed SubPlan 14)))
92.          

SubPlan (forIndex Scan)

93. 23.746 63.907 ↓ 51.6 48,064 1

Nested Loop (cost=0.84..1,167.08 rows=932 width=4) (actual time=0.415..63.907 rows=48,064 loops=1)

  • Output: timeoffs32.id
94. 1.839 3.631 ↓ 4.8 3,653 1

Nested Loop (cost=0.42..151.51 rows=763 width=12) (actual time=0.012..3.631 rows=3,653 loops=1)

  • Output: userlocation33.userid, userlocation33.startdate, userlocation33.enddate
95. 0.004 0.004 ↑ 1.0 1 1

Index Only Scan using locationflathierarchy_pkey on e390456bb6e840b6ad8d2732a449fdc8.locationflathierarchy locationflathierarchy34 (cost=0.14..1.16 rows=1 width=16) (actual time=0.003..0.004 rows=1 loops=1)

  • Output: locationflathierarchy34.parentid, locationflathierarchy34.childid
  • Index Cond: (locationflathierarchy34.parentid = '9caacfef-323b-49b2-bd43-565b0dbd36e1'::uuid)
  • Heap Fetches: 1
96. 1.788 1.788 ↓ 4.8 3,653 1

Index Scan using ixullocationid on e390456bb6e840b6ad8d2732a449fdc8.userlocation userlocation33 (cost=0.29..142.72 rows=763 width=28) (actual time=0.007..1.788 rows=3,653 loops=1)

  • Output: userlocation33.id, userlocation33.userid, userlocation33.locationid, userlocation33.startdate, userlocation33.enddate
  • Index Cond: (userlocation33.locationid = locationflathierarchy34.childid)
97. 36.530 36.530 ↓ 6.5 13 3,653

Index Scan using ixto4userid on e390456bb6e840b6ad8d2732a449fdc8.timeoffs timeoffs32 (cost=0.42..1.31 rows=2 width=16) (actual time=0.002..0.010 rows=13 loops=3,653)

  • Output: timeoffs32.id, timeoffs32.guidid, timeoffs32.userid, timeoffs32.createdbyuserid, timeoffs32.timeoffcodeid, timeoffs32.startdate, timeoffs32.enddate, timeoffs32.startdurationtype, timeoffs32.enddurationtype, timeo (...)
  • Index Cond: ((timeoffs32.userid = userlocation33.userid) AND (userlocation33.enddate >= timeoffs32.startdate) AND (userlocation33.startdate <= timeoffs32.enddate))
98. 0.000 0.000 ↓ 0.0 0

Index Scan using ix3usa_ededst on e390456bb6e840b6ad8d2732a449fdc8.userscheduleassignment userscheduleassignment37 (cost=0.29..75.05 rows=35 width=4) (never executed)

  • Output: userscheduleassignment37.userid
  • Index Cond: ((userscheduleassignment37.effectivedate <= timeoffs31.startdate) AND (userscheduleassignment37.enddate >= timeoffs31.enddate) AND (userscheduleassignment37.scheduletype = 1))
99. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.71..422.78 rows=763 width=4) (never executed)

  • Output: userinfo38.id
100. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.42..155.32 rows=763 width=4) (never executed)

  • Output: userlocation39.userid
101. 0.000 0.000 ↓ 0.0 0

Index Only Scan using locationflathierarchy_pkey on e390456bb6e840b6ad8d2732a449fdc8.locationflathierarchy locationflathierarchy40 (cost=0.14..1.16 rows=1 width=16) (never executed)

  • Output: locationflathierarchy40.parentid, locationflathierarchy40.childid
  • Index Cond: (locationflathierarchy40.parentid = '9caacfef-323b-49b2-bd43-565b0dbd36e1'::uuid)
  • Heap Fetches: 0
102. 0.000 0.000 ↓ 0.0 0

Index Scan using ixullocationid on e390456bb6e840b6ad8d2732a449fdc8.userlocation userlocation39 (cost=0.29..146.54 rows=763 width=20) (never executed)

  • Output: userlocation39.id, userlocation39.userid, userlocation39.locationid, userlocation39.startdate, userlocation39.enddate
  • Index Cond: (userlocation39.locationid = locationflathierarchy40.childid)
  • Filter: ((userlocation39.startdate <= '2019-10-08'::date) AND (userlocation39.enddate >= '2019-10-08'::date))
103. 0.000 0.000 ↓ 0.0 0

Index Only Scan using userinfo_pkey on e390456bb6e840b6ad8d2732a449fdc8.userinfo userinfo38 (cost=0.29..0.34 rows=1 width=4) (never executed)

  • Output: userinfo38.id
  • Index Cond: (userinfo38.id = userlocation39.userid)
  • Heap Fetches: 0
104. 3.964 23,173.625 ↑ 1.0 1 1

Aggregate (cost=3,503,131.04..3,503,131.05 rows=1 width=0) (actual time=23,173.624..23,173.625 rows=1 loops=1)

  • Output: count(*)
105.          

Initplan (forAggregate)

106. 0.005 0.070 ↓ 0.0 0 1

Nested Loop (cost=0.57..31.64 rows=1 width=0) (actual time=0.070..0.070 rows=0 loops=1)

107. 0.007 0.007 ↓ 2.0 2 1

Index Only Scan using userpolicyset_pkey on e390456bb6e840b6ad8d2732a449fdc8.userpolicyset userpolicyset50 (cost=0.29..2.30 rows=1 width=16) (actual time=0.006..0.007 rows=2 loops=1)

  • Output: userpolicyset50.userid, userpolicyset50.policysetid
  • Index Cond: (userpolicyset50.userid = 4664)
  • Heap Fetches: 1
108. 0.058 0.058 ↓ 0.0 0 2

Index Scan using ixpskvpolicysetid on e390456bb6e840b6ad8d2732a449fdc8.policysetkeyvalue policysetkeyvalue51 (cost=0.28..29.32 rows=1 width=16) (actual time=0.029..0.029 rows=0 loops=2)

  • Output: policysetkeyvalue51.id, policysetkeyvalue51.policysetid, policysetkeyvalue51.parentid, policysetkeyvalue51.index, policysetkeyvalue51.key, policysetkeyvalue51.uri, policysetkeyvalue51.slug, policysetkeyvalue51."boolean", pol (...)
  • Index Cond: (policysetkeyvalue51.policysetid = userpolicyset50.policysetid)
  • Filter: ((policysetkeyvalue51.parentid IS NULL) AND (upper(policysetkeyvalue51.key) = 'URN:REPLICON:POLICY:TIME-OFF:CAN-USER-VIEW-ALL-TIME-OFF'::text) AND (upper(policysetkeyvalue51.uri) = 'URN:REPLICON:POLICY:TIME-OFF:CAN-USER-VIEW (...)
  • Rows Removed by Filter: 16
109. 21.696 23,169.591 ↓ 1.4 13,629 1

Merge Semi Join (cost=14,321.40..3,503,074.62 rows=9,913 width=0) (actual time=1,998.287..23,169.591 rows=13,629 loops=1)

  • Merge Cond: ("*SELECT* 1_4".timeoffid = timeoffs46.id)
110. 12.406 572.703 ↑ 1.5 13,629 1

Sort (cost=12,726.89..12,776.46 rows=19,826 width=12) (actual time=567.421..572.703 rows=13,629 loops=1)

  • Output: "*SELECT* 1_4".timeoffid, timeoffs56.id, timeoffs57.id
  • Sort Key: "*SELECT* 1_4".timeoffid
  • Sort Method: quicksort Memory: 1023kB
111. 24.375 560.297 ↑ 1.5 13,629 1

Nested Loop Semi Join (cost=116.04..11,311.80 rows=19,826 width=12) (actual time=0.705..560.297 rows=13,629 loops=1)

  • Output: "*SELECT* 1_4".timeoffid, timeoffs56.id, timeoffs57.id
112. 15.088 108.814 ↑ 1.0 38,828 1

Append (cost=114.55..9,692.76 rows=39,653 width=4) (actual time=0.636..108.814 rows=38,828 loops=1)

113. 16.937 91.660 ↑ 1.0 37,975 1

Subquery Scan on *SELECT* 1_4 (cost=114.55..8,395.53 rows=38,808 width=4) (actual time=0.636..91.660 rows=37,975 loops=1)

  • Output: "*SELECT* 1_4".timeoffid
114. 35.306 74.723 ↑ 1.0 37,975 1

Hash Anti Join (cost=114.55..8,007.45 rows=38,808 width=4) (actual time=0.634..74.723 rows=37,975 loops=1)

  • Output: dm_timeofflist_facts_4.timeoffid, NULL::text, NULL::integer, NULL::text, NULL::text, NULL::boolean, NULL::integer, NULL::text, NULL::text, NULL::integer, NULL::text, NULL::text, NULL::integer, NULL::tex (...)
  • Hash Cond: (dm_timeofflist_facts_4.timeoffid = dm_timeofflist_realtime_facts_8.timeoffid)
115. 38.805 38.805 ↑ 1.0 38,828 1

Seq Scan on e390456bb6e840b6ad8d2732a449fdc8.dm_timeofflist_facts dm_timeofflist_facts_4 (cost=0.00..7,401.48 rows=39,198 width=4) (actual time=0.006..38.805 rows=38,828 loops=1)

  • Output: dm_timeofflist_facts_4.timeoffid, dm_timeofflist_facts_4.timeoffslug, dm_timeofflist_facts_4.timeoffowneruserid, dm_timeofflist_facts_4.timeoffowneruserdisplayname, dm_timeofflist_facts_4.timeoffo (...)
  • Filter: ((dm_timeofflist_facts_4.timeoffcodeid <> 5) AND (dm_timeofflist_facts_4.timeoffstatus = 2))
  • Rows Removed by Filter: 53471
116. 0.612 0.612 ↓ 1.0 921 1

Hash (cost=103.06..103.06 rows=919 width=4) (actual time=0.612..0.612 rows=921 loops=1)

  • Output: dm_timeofflist_realtime_facts_8.timeoffid
  • Buckets: 1024 Batches: 1 Memory Usage: 41kB
  • -> Index Only Scan using dm_timeofflist_realtime_facts_pkey on e390456bb6e840b6ad8d2732a449fdc8.dm_timeofflist_realtime_facts dm_timeofflist_realtime_facts_8 (cost=0.28..103.06 rows=919 width=4) (actual (...)
  • Output: dm_timeofflist_realtime_facts_8.timeoffid
  • Heap Fetches: 400
117. 0.402 2.066 ↓ 1.0 853 1

Subquery Scan on *SELECT* 2_4 (cost=0.00..1,297.24 rows=845 width=4) (actual time=0.012..2.066 rows=853 loops=1)

  • Output: "*SELECT* 2_4".timeoffid
118. 1.664 1.664 ↓ 1.0 853 1

Seq Scan on e390456bb6e840b6ad8d2732a449fdc8.dm_timeofflist_realtime_facts dm_timeofflist_realtime_facts_9 (cost=0.00..1,288.79 rows=845 width=4) (actual time=0.011..1.664 rows=853 loops=1)

  • Output: dm_timeofflist_realtime_facts_9.timeoffid, NULL::text, NULL::integer, NULL::text, NULL::text, NULL::boolean, NULL::integer, NULL::text, NULL::text, NULL::integer, NULL::text, NULL::text, NULL::integer, (...)
  • Filter: ((NOT dm_timeofflist_realtime_facts_9.deleted) AND (dm_timeofflist_realtime_facts_9.timeoffcodeid <> 5) AND (dm_timeofflist_realtime_facts_9.timeoffstatus = 2))
  • Rows Removed by Filter: 68
119. 102.854 427.108 ↓ 0.0 0 38,828

Hash Semi Join (cost=1.49..1.52 rows=1 width=8) (actual time=0.011..0.011 rows=0 loops=38,828)

  • Output: timeoffs56.id, timeoffs57.id
  • Hash Cond: (timeoffs56.id = timeoffs57.id)
120. 13.630 13.630 ↑ 1.0 1 13,630

Index Only Scan using timeoffs_pkey on e390456bb6e840b6ad8d2732a449fdc8.timeoffs timeoffs56 (cost=0.42..0.45 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=13,630)

  • Output: timeoffs56.id
  • Index Cond: (timeoffs56.id = "*SELECT* 1_4".timeoffid)
  • Heap Fetches: 0
121. 0.000 310.624 ↓ 0.0 0 38,828

Hash (cost=1.06..1.06 rows=1 width=4) (actual time=0.008..0.008 rows=0 loops=38,828)

  • Output: timeoffs57.id
  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
122. 77.656 310.624 ↓ 0.0 0 38,828

Nested Loop Semi Join (cost=0.84..1.06 rows=1 width=4) (actual time=0.007..0.008 rows=0 loops=38,828)

  • Output: timeoffs57.id
  • -> Index Only Scan using locationflathierarchy_pkey on e390456bb6e840b6ad8d2732a449fdc8.locationflathierarchy locationflathierarchy59 (cost=0.14..0.16 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=3 (...)
123. 77.656 232.968 ↑ 1.0 1 38,828

Nested Loop (cost=0.70..0.88 rows=1 width=20) (actual time=0.004..0.006 rows=1 loops=38,828)

  • Output: timeoffs57.id, userlocation58.locationid
  • Output: locationflathierarchy59.parentid, locationflathierarchy59.childid
  • Index Cond: ((locationflathierarchy59.parentid = '9caacfef-323b-49b2-bd43-565b0dbd36e1'::uuid) AND (locationflathierarchy59.childid = userlocation58.locationid))
  • Heap Fetches: 13629
124. 77.656 77.656 ↑ 1.0 1 38,828

Index Scan using timeoffs_pkey on e390456bb6e840b6ad8d2732a449fdc8.timeoffs timeoffs57 (cost=0.42..0.56 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=38,828)

  • Output: timeoffs57.id, timeoffs57.guidid, timeoffs57.userid, timeoffs57.createdbyuserid, timeoffs57.timeoffcodeid, timeoffs57.startdate, timeoffs57.enddate, timeoffs57.startdurationtype, timeoffs57. (...)
  • Index Cond: (timeoffs57.id = "*SELECT* 1_4".timeoffid)
125. 77.656 77.656 ↑ 1.0 1 38,828

Index Only Scan using ix4ul_userlocationstartend on e390456bb6e840b6ad8d2732a449fdc8.userlocation userlocation58 (cost=0.29..0.31 rows=1 width=28) (actual time=0.001..0.002 rows=1 loops=38,828)

  • Output: userlocation58.userid, userlocation58.locationid, userlocation58.startdate, userlocation58.enddate
  • Index Cond: ((userlocation58.userid = timeoffs57.userid) AND (userlocation58.startdate <= timeoffs57.enddate) AND (userlocation58.enddate >= timeoffs57.startdate))
  • Heap Fetches: 3975
126. 3,314.836 22,575.192 ↑ 1.6 48,061 1

Index Scan using timeoffs_pkey on e390456bb6e840b6ad8d2732a449fdc8.timeoffs timeoffs46 (cost=1,594.51..3,489,899.99 rows=75,001 width=4) (actual time=1,430.860..22,575.192 rows=48,061 loops=1)

  • Output: timeoffs46.id, timeoffs46.guidid, timeoffs46.userid, timeoffs46.createdbyuserid, timeoffs46.timeoffcodeid, timeoffs46.startdate, timeoffs46.enddate, timeoffs46.startdurationtype, timeoffs46.enddurationtype, timeoffs46.startdur (...)
  • Filter: ((timeoffs46.userid = 4664) OR (hashed SubPlan 16) OR $67 OR ((SubPlan 18) AND (hashed SubPlan 19)))
  • Rows Removed by Filter: 44230
127.          

SubPlan (forIndex Scan)

128. 24.365 64.536 ↓ 51.6 48,064 1

Nested Loop (cost=0.84..1,167.08 rows=932 width=4) (actual time=0.425..64.536 rows=48,064 loops=1)

  • Output: timeoffs47.id
129. 1.813 3.641 ↓ 4.8 3,653 1

Nested Loop (cost=0.42..151.51 rows=763 width=12) (actual time=0.013..3.641 rows=3,653 loops=1)

  • Output: userlocation48.userid, userlocation48.startdate, userlocation48.enddate
130. 0.005 0.005 ↑ 1.0 1 1

Index Only Scan using locationflathierarchy_pkey on e390456bb6e840b6ad8d2732a449fdc8.locationflathierarchy locationflathierarchy49 (cost=0.14..1.16 rows=1 width=16) (actual time=0.004..0.005 rows=1 loops=1)

  • Output: locationflathierarchy49.parentid, locationflathierarchy49.childid
  • Index Cond: (locationflathierarchy49.parentid = '9caacfef-323b-49b2-bd43-565b0dbd36e1'::uuid)
  • Heap Fetches: 1
131. 1.823 1.823 ↓ 4.8 3,653 1

Index Scan using ixullocationid on e390456bb6e840b6ad8d2732a449fdc8.userlocation userlocation48 (cost=0.29..142.72 rows=763 width=28) (actual time=0.007..1.823 rows=3,653 loops=1)

  • Output: userlocation48.id, userlocation48.userid, userlocation48.locationid, userlocation48.startdate, userlocation48.enddate
  • Index Cond: (userlocation48.locationid = locationflathierarchy49.childid)
132. 36.530 36.530 ↓ 6.5 13 3,653

Index Scan using ixto4userid on e390456bb6e840b6ad8d2732a449fdc8.timeoffs timeoffs47 (cost=0.42..1.31 rows=2 width=16) (actual time=0.002..0.010 rows=13 loops=3,653)

  • Output: timeoffs47.id, timeoffs47.guidid, timeoffs47.userid, timeoffs47.createdbyuserid, timeoffs47.timeoffcodeid, timeoffs47.startdate, timeoffs47.enddate, timeoffs47.startdurationtype, timeoffs47.enddurationtype, timeo (...)
  • Index Cond: ((timeoffs47.userid = userlocation48.userid) AND (userlocation48.enddate >= timeoffs47.startdate) AND (userlocation48.startdate <= timeoffs47.enddate))
133. 19,195.820 19,195.820 ↓ 9.0 314 44,230

Index Scan using ix3usa_ededst on e390456bb6e840b6ad8d2732a449fdc8.userscheduleassignment userscheduleassignment52 (cost=0.29..75.05 rows=35 width=4) (actual time=0.306..0.434 rows=314 loops=44,230)

  • Output: userscheduleassignment52.userid
  • Index Cond: ((userscheduleassignment52.effectivedate <= timeoffs46.startdate) AND (userscheduleassignment52.enddate >= timeoffs46.enddate) AND (userscheduleassignment52.scheduletype = 1))
134. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.71..422.78 rows=763 width=4) (never executed)

  • Output: userinfo53.id
135. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.42..155.32 rows=763 width=4) (never executed)

  • Output: userlocation54.userid
136. 0.000 0.000 ↓ 0.0 0

Index Only Scan using locationflathierarchy_pkey on e390456bb6e840b6ad8d2732a449fdc8.locationflathierarchy locationflathierarchy55 (cost=0.14..1.16 rows=1 width=16) (never executed)

  • Output: locationflathierarchy55.parentid, locationflathierarchy55.childid
  • Index Cond: (locationflathierarchy55.parentid = '9caacfef-323b-49b2-bd43-565b0dbd36e1'::uuid)
  • Heap Fetches: 0
137. 0.000 0.000 ↓ 0.0 0

Index Scan using ixullocationid on e390456bb6e840b6ad8d2732a449fdc8.userlocation userlocation54 (cost=0.29..146.54 rows=763 width=20) (never executed)

  • Output: userlocation54.id, userlocation54.userid, userlocation54.locationid, userlocation54.startdate, userlocation54.enddate
  • Index Cond: (userlocation54.locationid = locationflathierarchy55.childid)
  • Filter: ((userlocation54.startdate <= '2019-10-08'::date) AND (userlocation54.enddate >= '2019-10-08'::date))
138. 0.000 0.000 ↓ 0.0 0

Index Only Scan using userinfo_pkey on e390456bb6e840b6ad8d2732a449fdc8.userinfo userinfo53 (cost=0.29..0.34 rows=1 width=4) (never executed)

  • Output: userinfo53.id
  • Index Cond: (userinfo53.id = userlocation54.userid)
  • Heap Fetches: 0
139. 46,247.109 46,247.132 ↑ 92,290.0 1 1

Result (cost=0.69..4,611.29 rows=92,290 width=0) (actual time=46,247.132..46,247.132 rows=1 loops=1)

  • Output: $18, $39, $60, $79
140. 0.000 0.023 ↑ 92,290.0 1 1

Append (cost=0.69..4,611.29 rows=92,290 width=0) (actual time=0.023..0.023 rows=1 loops=1)

141. 0.000 0.023 ↑ 91,380.0 1 1

Subquery Scan on *SELECT* 1 (cost=0.69..3,318.00 rows=91,380 width=0) (actual time=0.023..0.023 rows=1 loops=1)

142. 0.004 0.023 ↑ 91,380.0 1 1

Merge Anti Join (cost=0.69..2,404.20 rows=91,380 width=0) (actual time=0.023..0.023 rows=1 loops=1)

  • Output: NULL::integer, NULL::text, NULL::integer, NULL::text, NULL::text, NULL::boolean, NULL::integer, NULL::text, NULL::text, NULL::integer, NULL::text, NULL::text, NULL::integer, NULL::text, NULL::text, NULL::integer, NULL::int (...)
  • Merge Cond: (dm_timeofflist_facts.timeoffid = dm_timeofflist_realtime_facts.timeoffid)
143. 0.017 0.017 ↑ 92,299.0 1 1

Index Only Scan using dm_timeofflist_facts_pkey on e390456bb6e840b6ad8d2732a449fdc8.dm_timeofflist_facts (cost=0.42..2,058.90 rows=92,299 width=4) (actual time=0.017..0.017 rows=1 loops=1)

  • Output: dm_timeofflist_facts.timeoffid
  • Heap Fetches: 0
144. 0.002 0.002 ↑ 919.0 1 1

Index Only Scan using dm_timeofflist_realtime_facts_pkey on e390456bb6e840b6ad8d2732a449fdc8.dm_timeofflist_realtime_facts (cost=0.28..103.06 rows=919 width=4) (actual time=0.002..0.002 rows=1 loops=1)

  • Output: dm_timeofflist_realtime_facts.timeoffid
  • Heap Fetches: 1
145. 0.000 0.000 ↓ 0.0 0

Subquery Scan on *SELECT* 2 (cost=0.00..1,293.29 rows=910 width=0) (never executed)

146. 0.000 0.000 ↓ 0.0 0

Seq Scan on e390456bb6e840b6ad8d2732a449fdc8.dm_timeofflist_realtime_facts dm_timeofflist_realtime_facts_1 (cost=0.00..1,284.19 rows=910 width=0) (never executed)

  • Output: NULL::integer, NULL::text, NULL::integer, NULL::text, NULL::text, NULL::boolean, NULL::integer, NULL::text, NULL::text, NULL::integer, NULL::text, NULL::text, NULL::integer, NULL::text, NULL::text, NULL::integer, NULL::int (...)
  • Filter: (NOT dm_timeofflist_realtime_facts_1.deleted)
Planning time : 9.728 ms
Execution time : 46,247.971 ms