explain.depesz.com

PostgreSQL's explain analyze made readable

Result: K68X

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

Limit (cost=7,225,395.71..7,225,395.78 rows=1 width=0) (actual time=46,645.253..46,645.253 rows=1 loops=1)

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

Initplan (forLimit)

3. 5.387 23,184.879 ↑ 1.0 1 1

Aggregate (cost=3,504,109.05..3,504,109.06 rows=1 width=0) (actual time=23,184.879..23,184.879 rows=1 loops=1)

  • Output: count(*)
4.          

Initplan (forAggregate)

5. 0.004 0.060 ↓ 0.0 0 1

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

6. 0.006 0.006 ↓ 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.005..0.006 rows=2 loops=1)

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

Index Scan using ixpskvpolicysetid on e390456bb6e840b6ad8d2732a449fdc8.policysetkeyvalue policysetkeyvalue6 (cost=0.28..29.32 rows=1 width=16) (actual time=0.025..0.025 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. 12.202 23,179.432 ↓ 1.4 13,977 1

Nested Loop Semi Join (cost=14,116.45..3,504,052.44 rows=9,990 width=0) (actual time=1,556.816..23,179.432 rows=13,977 loops=1)

  • Join Filter: ("*SELECT* 1_1".timeoffid = timeoffs11.id)
9. 28.386 22,957.575 ↑ 1.4 13,977 1

Merge Semi Join (cost=14,115.02..3,503,132.50 rows=19,980 width=8) (actual time=1,556.720..22,957.575 rows=13,977 loops=1)

  • Output: "*SELECT* 1_1".timeoffid, timeoffs1.id
  • Merge Cond: ("*SELECT* 1_1".timeoffid = timeoffs1.id)
10. 30.231 120.621 ↑ 1.0 39,478 1

Sort (cost=12,520.50..12,620.40 rows=39,960 width=4) (actual time=105.934..120.621 rows=39,478 loops=1)

  • Output: "*SELECT* 1_1".timeoffid
  • Sort Key: "*SELECT* 1_1".timeoffid
  • Sort Method: quicksort Memory: 3387kB
11. 14.869 90.390 ↑ 1.0 39,479 1

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

12. 15.389 73.786 ↑ 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..73.786 rows=38,603 loops=1)

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

Hash Anti Join (cost=114.55..7,780.22 rows=39,086 width=4) (actual time=0.664..58.397 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. 30.622 30.622 ↑ 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.005..30.622 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.332 1.735 ↓ 1.0 876 1

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

  • Output: "*SELECT* 2_1".timeoffid
17. 1.403 1.403 ↓ 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.011..1.403 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. 3,282.139 22,808.568 ↑ 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,450.076..22,808.568 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.startduratio (...)
  • Filter: ((timeoffs1.userid = 4664) OR (hashed SubPlan 1) OR $6 OR ((SubPlan 3) AND (hashed SubPlan 4)))
  • Rows Removed by Filter: 44234
19.          

SubPlan (forIndex Scan)

20. 23.320 63.469 ↓ 51.6 48,064 1

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

  • Output: timeoffs2.id
21. 1.858 3.619 ↓ 4.8 3,653 1

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

  • Output: userlocation3.userid, userlocation3.startdate, userlocation3.enddate
22. 0.007 0.007 ↑ 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.007..0.007 rows=1 loops=1)

  • Output: locationflathierarchy4.parentid, locationflathierarchy4.childid
  • Index Cond: (locationflathierarchy4.parentid = '9caacfef-323b-49b2-bd43-565b0dbd36e1'::uuid)
  • Heap Fetches: 1
23. 1.754 1.754 ↓ 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.009..1.754 rows=3,653 loops=1)

  • Output: userlocation3.id, userlocation3.userid, userlocation3.locationid, userlocation3.startdate, userlocation3.enddate
  • Index Cond: (userlocation3.locationid = locationflathierarchy4.childid)
24. 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, timeoffs (...)
  • Index Cond: ((timeoffs2.userid = userlocation3.userid) AND (userlocation3.enddate >= timeoffs2.startdate) AND (userlocation3.startdate <= timeoffs2.enddate))
25. 19,462.960 19,462.960 ↓ 9.0 314 44,234

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

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

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

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

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

  • Output: userlocation9.userid
28. 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
29. 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))
30. 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
31. 55.908 209.655 ↑ 1.0 1 13,977

Hash Semi Join (cost=1.43..1.46 rows=1 width=8) (actual time=0.015..0.015 rows=1 loops=13,977)

  • Output: timeoffs11.id, timeoffs12.id
  • Hash Cond: (timeoffs11.id = timeoffs12.id)
32. 13.977 13.977 ↑ 1.0 1 13,977

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,977)

  • Output: timeoffs11.id
  • Index Cond: (timeoffs11.id = timeoffs1.id)
  • Heap Fetches: 5400
33. 13.977 139.770 ↑ 1.0 1 13,977

Hash (cost=1.00..1.00 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=13,977)

  • Output: timeoffs12.id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
34. 27.954 125.793 ↑ 1.0 1 13,977

Nested Loop Semi Join (cost=0.84..1.00 rows=1 width=4) (actual time=0.007..0.009 rows=1 loops=13,977)

  • Output: timeoffs12.id
35. 27.954 83.862 ↑ 1.0 1 13,977

Nested Loop (cost=0.70..0.82 rows=1 width=20) (actual time=0.005..0.006 rows=1 loops=13,977)

  • Output: timeoffs12.id, userlocation13.locationid
36. 27.954 27.954 ↑ 1.0 1 13,977

Index Scan using timeoffs_pkey on e390456bb6e840b6ad8d2732a449fdc8.timeoffs timeoffs12 (cost=0.42..0.50 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=13,977)

  • Output: timeoffs12.id, timeoffs12.guidid, timeoffs12.userid, timeoffs12.createdbyuserid, timeoffs12.timeoffcodeid, timeoffs12.startdate, timeoffs12.enddate, timeoffs12.startdurationtype, timeoffs12.enddurationt (...)
  • Index Cond: (timeoffs12.id = timeoffs1.id)
37. 27.954 27.954 ↑ 1.0 1 13,977

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

  • 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: 3072
38. 13.977 13.977 ↑ 1.0 1 13,977

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=1 loops=13,977)

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

Aggregate (cost=10,526.38..10,526.39 rows=1 width=0) (actual time=114.251..114.251 rows=1 loops=1)

  • Output: count(*)
40.          

Initplan (forAggregate)

41. 0.000 0.000 ↓ 0.0 0

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

42. 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
43. 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 (...)
44. 0.062 114.230 ↓ 56.0 56 1

Nested Loop Semi Join (cost=1,596.04..10,494.74 rows=1 width=0) (actual time=87.038..114.230 rows=56 loops=1)

  • Join Filter: ("*SELECT* 1_2".timeoffid = timeoffs16.id)
45. 0.117 27.424 ↓ 56.0 56 1

Nested Loop Semi Join (cost=1.53..8,862.42 rows=1 width=12) (actual time=0.430..27.424 rows=56 loops=1)

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

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

47. 0.049 24.930 ↓ 2.4 104 1

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

  • Output: "*SELECT* 1_2".timeoffid
48. 0.317 24.881 ↓ 2.4 104 1

Nested Loop Anti Join (cost=0.28..7,451.19 rows=44 width=4) (actual time=0.175..24.881 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.001..0.0 (...)
49. 24.564 24.564 ↓ 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.164..24.564 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
50. 0.000 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
51. 1.077 1.077 ↓ 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.077..1.077 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
52. 0.104 1.248 ↑ 1.0 1 104

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

  • Output: timeoffs26.id, timeoffs27.id
53. 0.312 0.312 ↑ 1.0 1 104

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

  • Output: timeoffs26.id
  • Index Cond: (timeoffs26.id = "*SELECT* 1_2".timeoffid)
  • Heap Fetches: 23
54. 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
55. 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
56. 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)
57. 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
58. 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
59. 21.261 86.744 ↑ 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.549..1.549 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)))
60.          

SubPlan (forIndex Scan)

61. 21.640 65.483 ↓ 51.6 48,064 1

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

  • Output: timeoffs17.id
62. 1.811 3.660 ↓ 4.8 3,653 1

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

  • Output: userlocation18.userid, userlocation18.startdate, userlocation18.enddate
63. 0.005 0.005 ↑ 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.003..0.005 rows=1 loops=1)

  • Output: locationflathierarchy19.parentid, locationflathierarchy19.childid
  • Index Cond: (locationflathierarchy19.parentid = '9caacfef-323b-49b2-bd43-565b0dbd36e1'::uuid)
  • Heap Fetches: 1
64. 1.844 1.844 ↓ 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.009..1.844 rows=3,653 loops=1)

  • Output: userlocation18.id, userlocation18.userid, userlocation18.locationid, userlocation18.startdate, userlocation18.enddate
  • Index Cond: (userlocation18.locationid = locationflathierarchy19.childid)
65. 40.183 40.183 ↓ 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.011 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))
66. 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))
67. 0.000 0.000 ↓ 0.0 0

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

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

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

  • Output: userlocation24.userid
69. 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
70. 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))
71. 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
72. 0.096 120.697 ↑ 1.0 1 1

Aggregate (cost=206,461.01..206,461.02 rows=1 width=0) (actual time=120.696..120.697 rows=1 loops=1)

  • Output: count(*)
73.          

Initplan (forAggregate)

74. 0.000 0.000 ↓ 0.0 0

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

75. 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
76. 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 (...)
77. 0.405 120.601 ↓ 4.7 279 1

Nested Loop Semi Join (cost=1,596.04..206,429.22 rows=60 width=0) (actual time=87.054..120.601 rows=279 loops=1)

  • Join Filter: ("*SELECT* 1_3".timeoffid = timeoffs31.id)
78. 0.483 33.427 ↓ 2.3 279 1

Nested Loop Semi Join (cost=1.53..9,449.60 rows=121 width=12) (actual time=0.732..33.427 rows=279 loops=1)

  • Output: "*SELECT* 1_3".timeoffid, timeoffs41.id, timeoffs42.id
79. 0.256 27.754 ↓ 2.1 519 1

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

80. 0.220 26.410 ↓ 2.3 498 1

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

  • Output: "*SELECT* 1_3".timeoffid
81. 1.283 26.190 ↓ 2.3 498 1

Nested Loop Anti Join (cost=0.28..7,555.44 rows=220 width=4) (actual time=0.145..26.190 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 (...)
82. 24.907 24.907 ↓ 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.136..24.907 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
83. 0.009 1.088 ↑ 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.088 rows=21 loops=1)

  • Output: "*SELECT* 2_3".timeoffid
84. 1.079 1.079 ↑ 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.021..1.079 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
85. 0.519 5.190 ↑ 1.0 1 519

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

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

Index Only Scan using timeoffs_pkey on e390456bb6e840b6ad8d2732a449fdc8.timeoffs timeoffs41 (cost=0.42..1.34 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: 236
87. 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
88. 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
89. 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)
90. 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.002..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
91. 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
92. 21.662 86.769 ↑ 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.311..0.311 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)))
93.          

SubPlan (forIndex Scan)

94. 24.914 65.107 ↓ 51.6 48,064 1

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

  • Output: timeoffs32.id
95. 1.766 3.663 ↓ 4.8 3,653 1

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

  • Output: userlocation33.userid, userlocation33.startdate, userlocation33.enddate
96. 0.003 0.003 ↑ 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.002..0.003 rows=1 loops=1)

  • Output: locationflathierarchy34.parentid, locationflathierarchy34.childid
  • Index Cond: (locationflathierarchy34.parentid = '9caacfef-323b-49b2-bd43-565b0dbd36e1'::uuid)
  • Heap Fetches: 1
97. 1.894 1.894 ↓ 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.894 rows=3,653 loops=1)

  • Output: userlocation33.id, userlocation33.userid, userlocation33.locationid, userlocation33.startdate, userlocation33.enddate
  • Index Cond: (userlocation33.locationid = locationflathierarchy34.childid)
98. 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))
99. 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))
100. 0.000 0.000 ↓ 0.0 0

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

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

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

  • Output: userlocation39.userid
102. 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
103. 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))
104. 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
105. 5.075 23,225.388 ↑ 1.0 1 1

Aggregate (cost=3,504,298.54..3,504,298.55 rows=1 width=0) (actual time=23,225.388..23,225.388 rows=1 loops=1)

  • Output: count(*)
106.          

Initplan (forAggregate)

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

108. 0.008 0.008 ↓ 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.008 rows=2 loops=1)

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

Index Scan using ixpskvpolicysetid on e390456bb6e840b6ad8d2732a449fdc8.policysetkeyvalue policysetkeyvalue51 (cost=0.28..29.32 rows=1 width=16) (actual time=0.026..0.026 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
110. 12.975 23,220.250 ↓ 1.4 13,629 1

Nested Loop Semi Join (cost=14,317.23..3,504,242.12 rows=9,913 width=0) (actual time=1,580.093..23,220.250 rows=13,629 loops=1)

  • Join Filter: ("*SELECT* 1_4".timeoffid = timeoffs56.id)
111. 28.575 23,002.840 ↑ 1.5 13,629 1

Merge Semi Join (cost=14,315.80..3,503,329.26 rows=19,826 width=8) (actual time=1,580.039..23,002.840 rows=13,629 loops=1)

  • Output: "*SELECT* 1_4".timeoffid, timeoffs46.id
  • Merge Cond: ("*SELECT* 1_4".timeoffid = timeoffs46.id)
112. 31.253 123.603 ↑ 1.0 38,828 1

Sort (cost=12,721.29..12,820.42 rows=39,653 width=4) (actual time=109.022..123.603 rows=38,828 loops=1)

  • Output: "*SELECT* 1_4".timeoffid
  • Sort Key: "*SELECT* 1_4".timeoffid
  • Sort Method: quicksort Memory: 3357kB
113. 14.651 92.350 ↑ 1.0 38,828 1

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

114. 15.300 75.860 ↑ 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.651..75.860 rows=37,975 loops=1)

  • Output: "*SELECT* 1_4".timeoffid
115. 26.765 60.560 ↑ 1.0 37,975 1

Hash Anti Join (cost=114.55..8,007.45 rows=38,808 width=4) (actual time=0.651..60.560 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)
116. 33.165 33.165 ↑ 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..33.165 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
117. 0.630 0.630 ↓ 1.0 921 1

Hash (cost=103.06..103.06 rows=919 width=4) (actual time=0.630..0.630 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
118. 0.340 1.839 ↓ 1.0 853 1

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

  • Output: "*SELECT* 2_4".timeoffid
119. 1.499 1.499 ↓ 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.013..1.499 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
120. 3,279.088 22,850.662 ↑ 1.6 48,063 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,470.298..22,850.662 rows=48,063 loops=1)

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

SubPlan (forIndex Scan)

122. 25.005 65.262 ↓ 51.6 48,064 1

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

  • Output: timeoffs47.id
123. 1.824 3.727 ↓ 4.8 3,653 1

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

  • Output: userlocation48.userid, userlocation48.startdate, userlocation48.enddate
124. 0.008 0.008 ↑ 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.006..0.008 rows=1 loops=1)

  • Output: locationflathierarchy49.parentid, locationflathierarchy49.childid
  • Index Cond: (locationflathierarchy49.parentid = '9caacfef-323b-49b2-bd43-565b0dbd36e1'::uuid)
  • Heap Fetches: 1
125. 1.895 1.895 ↓ 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.008..1.895 rows=3,653 loops=1)

  • Output: userlocation48.id, userlocation48.userid, userlocation48.locationid, userlocation48.startdate, userlocation48.enddate
  • Index Cond: (userlocation48.locationid = locationflathierarchy49.childid)
126. 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, (...)
  • Index Cond: ((timeoffs47.userid = userlocation48.userid) AND (userlocation48.enddate >= timeoffs47.startdate) AND (userlocation48.startdate <= timeoffs47.enddate))
127. 19,506.312 19,506.312 ↓ 9.0 314 44,232

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

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

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

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

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

  • Output: userlocation54.userid
130. 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
131. 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))
132. 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
133. 54.516 204.435 ↑ 1.0 1 13,629

Hash Semi Join (cost=1.43..1.46 rows=1 width=8) (actual time=0.015..0.015 rows=1 loops=13,629)

  • Output: timeoffs56.id, timeoffs57.id
  • Hash Cond: (timeoffs56.id = timeoffs57.id)
134. 13.629 13.629 ↑ 1.0 1 13,629

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,629)

  • Output: timeoffs56.id
  • Index Cond: (timeoffs56.id = timeoffs46.id)
  • Heap Fetches: 5281
135. 13.629 136.290 ↑ 1.0 1 13,629

Hash (cost=1.00..1.00 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=13,629)

  • Output: timeoffs57.id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
136. 27.258 122.661 ↑ 1.0 1 13,629

Nested Loop Semi Join (cost=0.84..1.00 rows=1 width=4) (actual time=0.007..0.009 rows=1 loops=13,629)

  • Output: timeoffs57.id
137. 27.258 81.774 ↑ 1.0 1 13,629

Nested Loop (cost=0.70..0.82 rows=1 width=20) (actual time=0.005..0.006 rows=1 loops=13,629)

  • Output: timeoffs57.id, userlocation58.locationid
138. 27.258 27.258 ↑ 1.0 1 13,629

Index Scan using timeoffs_pkey on e390456bb6e840b6ad8d2732a449fdc8.timeoffs timeoffs57 (cost=0.42..0.50 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=13,629)

  • Output: timeoffs57.id, timeoffs57.guidid, timeoffs57.userid, timeoffs57.createdbyuserid, timeoffs57.timeoffcodeid, timeoffs57.startdate, timeoffs57.enddate, timeoffs57.startdurationtype, timeoffs57.enddurationt (...)
  • Index Cond: (timeoffs57.id = timeoffs46.id)
139. 27.258 27.258 ↑ 1.0 1 13,629

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

  • 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: 3034
140. 13.629 13.629 ↑ 1.0 1 13,629

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=1 loops=13,629)

  • Output: locationflathierarchy59.parentid, locationflathierarchy59.childid
  • Index Cond: ((locationflathierarchy59.parentid = '9caacfef-323b-49b2-bd43-565b0dbd36e1'::uuid) AND (locationflathierarchy59.childid = userlocation58.locationid))
  • Heap Fetches: 13629
141. 46,645.226 46,645.251 ↑ 92,290.0 1 1

Result (cost=0.69..6,468.29 rows=92,290 width=0) (actual time=46,645.251..46,645.251 rows=1 loops=1)

  • Output: $18, $39, $60, $79
142. 0.000 0.025 ↑ 92,290.0 1 1

Append (cost=0.69..6,468.29 rows=92,290 width=0) (actual time=0.025..0.025 rows=1 loops=1)

143. 0.002 0.025 ↑ 91,380.0 1 1

Subquery Scan on *SELECT* 1 (cost=0.69..5,175.00 rows=91,380 width=0) (actual time=0.025..0.025 rows=1 loops=1)

144. 0.004 0.023 ↑ 91,380.0 1 1

Merge Anti Join (cost=0.69..4,261.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)
145. 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..3,915.90 rows=92,299 width=4) (actual time=0.017..0.017 rows=1 loops=1)

  • Output: dm_timeofflist_facts.timeoffid
  • Heap Fetches: 1
146. 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
147. 0.000 0.000 ↓ 0.0 0

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

148. 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.642 ms
Execution time : 46,646.093 ms