explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xAwb

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 47,274.745 ↑ 1.0 1 1

Limit (cost=8,434,174.00..8,434,174.07 rows=1 width=0) (actual time=47,274.745..47,274.745 rows=1 loops=1)

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

Initplan (forLimit)

3. 4.156 23,690.630 ↑ 1.0 1 1

Aggregate (cost=4,108,339.23..4,108,339.24 rows=1 width=0) (actual time=23,690.629..23,690.630 rows=1 loops=1)

  • Output: count(*)
4.          

Initplan (forAggregate)

5. 0.007 0.081 ↓ 0.0 0 1

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

6. 0.022 0.022 ↓ 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.021..0.022 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.956 23,686.393 ↓ 1.4 13,977 1

Merge Semi Join (cost=14,157.28..4,108,282.64 rows=9,982 width=0) (actual time=2,185.039..23,686.393 rows=13,977 loops=1)

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

Sort (cost=12,518.48..12,568.39 rows=19,964 width=12) (actual time=722.483..728.224 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. 29.376 714.336 ↑ 1.4 13,977 1

Nested Loop Semi Join (cost=116.04..11,092.54 rows=19,964 width=12) (actual time=0.769..714.336 rows=13,977 loops=1)

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

Append (cost=114.55..9,465.19 rows=39,929 width=4) (actual time=0.682..132.254 rows=39,479 loops=1)

12. 21.540 110.708 ↑ 1.0 38,603 1

Subquery Scan on *SELECT* 1_1 (cost=114.55..8,169.97 rows=39,055 width=4) (actual time=0.681..110.708 rows=38,603 loops=1)

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

Hash Anti Join (cost=114.55..7,779.42 rows=39,055 width=4) (actual time=0.680..89.168 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. 44.593 44.593 ↓ 1.0 39,479 1

Seq Scan on e390456bb6e840b6ad8d2732a449fdc8.dm_timeofflist_facts dm_timeofflist_facts_1 (cost=0.00..7,170.32 rows=39,448 width=4) (actual time=0.003..44.593 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.666 0.666 ↓ 1.0 921 1

Hash (cost=103.06..103.06 rows=919 width=4) (actual time=0.666..0.666 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.393 2.048 ↓ 1.0 876 1

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

  • Output: "*SELECT* 2_1".timeoffid
17. 1.655 1.655 ↓ 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.655 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. 90.481 552.706 ↓ 0.0 0 39,479

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

  • Output: timeoffs11.id, timeoffs12.id
  • Hash Cond: (timeoffs11.id = timeoffs12.id)
19. 27.956 27.956 ↑ 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.002..0.002 rows=1 loops=13,978)

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

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

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

Nested Loop Semi Join (cost=0.84..1.06 rows=1 width=4) (actual time=0.009..0.010 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. 78.960 276.353 ↑ 1.0 1 39,479

Nested Loop (cost=0.70..0.88 rows=1 width=20) (actual time=0.006..0.007 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. 118.437 118.437 ↑ 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.003..0.003 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.002..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,275.991 22,936.213 ↑ 1.8 48,064 1

Index Scan using timeoffs_pkey on e390456bb6e840b6ad8d2732a449fdc8.timeoffs timeoffs1 (cost=1,638.79..4,095,280.90 rows=88,480 width=4) (actual time=1,462.549..22,936.213 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. 21.543 65.446 ↓ 43.7 48,064 1

Nested Loop (cost=0.84..1,210.94 rows=1,100 width=4) (actual time=0.433..65.446 rows=48,064 loops=1)

  • Output: timeoffs2.id
28. 1.822 3.720 ↓ 4.8 3,653 1

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

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

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

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

Index Scan using ixto4userid on e390456bb6e840b6ad8d2732a449fdc8.timeoffs timeoffs2 (cost=0.42..1.36 rows=3 width=16) (actual time=0.002..0.011 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. 19,594.776 19,594.776 ↓ 9.0 314 44,232

Index Scan using ix3usa_ededst on e390456bb6e840b6ad8d2732a449fdc8.userscheduleassignment userscheduleassignment7 (cost=0.29..74.68 rows=35 width=4) (actual time=0.315..0.443 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.026 113.432 ↑ 1.0 1 1

Aggregate (cost=10,563.03..10,563.04 rows=1 width=0) (actual time=113.432..113.432 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.097 113.406 ↓ 56.0 56 1

Nested Loop Semi Join (cost=1,640.32..10,531.39 rows=1 width=0) (actual time=85.651..113.406 rows=56 loops=1)

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

Nested Loop Semi Join (cost=1.53..8,854.99 rows=1 width=12) (actual time=0.456..28.021 rows=56 loops=1)

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

Append (cost=0.28..8,739.93 rows=45 width=4) (actual time=0.185..26.672 rows=104 loops=1)

46. 0.051 25.554 ↓ 2.4 104 1

Subquery Scan on *SELECT* 1_2 (cost=0.28..7,451.14 rows=44 width=4) (actual time=0.184..25.554 rows=104 loops=1)

  • Output: "*SELECT* 1_2".timeoffid
47. 0.323 25.503 ↓ 2.4 104 1

Nested Loop Anti Join (cost=0.28..7,450.70 rows=44 width=4) (actual time=0.183..25.503 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 (...)
48. 25.180 25.180 ↓ 2.4 104 1

Seq Scan on e390456bb6e840b6ad8d2732a449fdc8.dm_timeofflist_facts dm_timeofflist_facts_2 (cost=0.00..7,400.99 rows=44 width=4) (actual time=0.167..25.180 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.060 ↓ 0.0 0 1

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

  • Output: "*SELECT* 2_2".timeoffid
50. 1.059 1.059 ↓ 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.059..1.059 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.104 1.248 ↑ 1.0 1 104

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

  • Output: timeoffs26.id, timeoffs27.id
52. 0.312 0.312 ↑ 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.003..0.003 rows=1 loops=104)

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

Nested Loop Semi Join (cost=0.84..0.98 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.80 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.48 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.376 85.288 ↑ 1.0 1 56

Index Scan using timeoffs_pkey on e390456bb6e840b6ad8d2732a449fdc8.timeoffs timeoffs16 (cost=1,638.79..1,676.39 rows=1 width=4) (actual time=1.523..1.523 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.700 63.912 ↓ 43.7 48,064 1

Nested Loop (cost=0.84..1,210.94 rows=1,100 width=4) (actual time=0.441..63.912 rows=48,064 loops=1)

  • Output: timeoffs17.id
61. 1.886 3.682 ↓ 4.8 3,653 1

Nested Loop (cost=0.42..151.51 rows=763 width=12) (actual time=0.012..3.682 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.793 1.793 ↓ 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.793 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 ↓ 4.3 13 3,653

Index Scan using ixto4userid on e390456bb6e840b6ad8d2732a449fdc8.timeoffs timeoffs17 (cost=0.42..1.36 rows=3 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..74.68 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.093 120.226 ↑ 1.0 1 1

Aggregate (cost=206,728.34..206,728.35 rows=1 width=0) (actual time=120.226..120.226 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.293 120.133 ↓ 4.7 279 1

Nested Loop Semi Join (cost=1,640.32..206,696.56 rows=59 width=0) (actual time=86.126..120.133 rows=279 loops=1)

  • Join Filter: ("*SELECT* 1_3".timeoffid = timeoffs31.id)
77. 0.538 33.908 ↓ 2.4 279 1

Nested Loop Semi Join (cost=1.53..9,400.62 rows=118 width=12) (actual time=0.746..33.908 rows=279 loops=1)

  • Output: "*SELECT* 1_3".timeoffid, timeoffs41.id, timeoffs42.id
78. 0.239 28.170 ↓ 2.2 520 1

Append (cost=0.28..8,844.34 rows=236 width=4) (actual time=0.155..28.170 rows=520 loops=1)

79. 0.226 26.848 ↓ 2.3 499 1

Subquery Scan on *SELECT* 1_3 (cost=0.28..7,555.33 rows=214 width=4) (actual time=0.154..26.848 rows=499 loops=1)

  • Output: "*SELECT* 1_3".timeoffid
80. 1.275 26.622 ↓ 2.3 499 1

Nested Loop Anti Join (cost=0.28..7,553.19 rows=214 width=4) (actual time=0.154..26.622 rows=499 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.70 rows=1 width=4) (actual time=0.001..0.0 (...)
81. 25.347 25.347 ↓ 2.4 520 1

Seq Scan on e390456bb6e840b6ad8d2732a449fdc8.dm_timeofflist_facts dm_timeofflist_facts_3 (cost=0.00..7,400.99 rows=216 width=4) (actual time=0.142..25.347 rows=520 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: 91779
  • 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.012 1.083 ↑ 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.083 rows=21 loops=1)

  • Output: "*SELECT* 2_3".timeoffid
83. 1.071 1.071 ↑ 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.071 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.520 5.200 ↑ 1.0 1 520

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

  • Output: timeoffs41.id, timeoffs42.id
85. 1.040 1.040 ↑ 1.0 1 520

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=520)

  • Output: timeoffs41.id
  • Index Cond: (timeoffs41.id = "*SELECT* 1_3".timeoffid)
  • Heap Fetches: 237
86. 0.520 3.640 ↑ 1.0 1 520

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

  • Output: timeoffs42.id
87. 0.520 2.600 ↑ 1.0 1 520

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

  • Output: timeoffs42.id, userlocation43.locationid
88. 1.040 1.040 ↑ 1.0 1 520

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

  • 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.040 1.040 ↑ 1.0 1 520

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=520)

  • 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.520 0.520 ↑ 1.0 1 520

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=520)

  • 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.626 85.932 ↑ 1.0 1 279

Index Scan using timeoffs_pkey on e390456bb6e840b6ad8d2732a449fdc8.timeoffs timeoffs31 (cost=1,638.79..1,676.39 rows=1 width=4) (actual time=0.308..0.308 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. 24.085 64.306 ↓ 43.7 48,064 1

Nested Loop (cost=0.84..1,210.94 rows=1,100 width=4) (actual time=0.415..64.306 rows=48,064 loops=1)

  • Output: timeoffs32.id
94. 1.869 3.691 ↓ 4.8 3,653 1

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

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

  • Output: locationflathierarchy34.parentid, locationflathierarchy34.childid
  • Index Cond: (locationflathierarchy34.parentid = '9caacfef-323b-49b2-bd43-565b0dbd36e1'::uuid)
  • Heap Fetches: 1
96. 1.820 1.820 ↓ 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.820 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 ↓ 4.3 13 3,653

Index Scan using ixto4userid on e390456bb6e840b6ad8d2732a449fdc8.timeoffs timeoffs32 (cost=0.42..1.36 rows=3 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..74.68 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.931 23,350.417 ↑ 1.0 1 1

Aggregate (cost=4,108,542.67..4,108,542.68 rows=1 width=0) (actual time=23,350.417..23,350.417 rows=1 loops=1)

  • Output: count(*)
105.          

Initplan (forAggregate)

106. 0.003 0.058 ↓ 0.0 0 1

Nested Loop (cost=0.57..31.64 rows=1 width=0) (actual time=0.058..0.058 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.005..0.007 rows=2 loops=1)

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

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

Merge Semi Join (cost=14,362.87..4,108,486.26 rows=9,907 width=0) (actual time=2,026.025..23,346.428 rows=13,629 loops=1)

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

Sort (cost=12,724.08..12,773.62 rows=19,814 width=12) (actual time=568.426..573.671 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.149 561.634 ↑ 1.5 13,629 1

Nested Loop Semi Join (cost=116.04..11,309.93 rows=19,814 width=12) (actual time=0.732..561.634 rows=13,629 loops=1)

  • Output: "*SELECT* 1_4".timeoffid, timeoffs56.id, timeoffs57.id
112. 15.615 110.388 ↑ 1.0 38,827 1

Append (cost=114.55..9,691.68 rows=39,627 width=4) (actual time=0.659..110.388 rows=38,827 loops=1)

113. 17.359 92.726 ↑ 1.0 37,974 1

Subquery Scan on *SELECT* 1_4 (cost=114.55..8,394.44 rows=38,782 width=4) (actual time=0.658..92.726 rows=37,974 loops=1)

  • Output: "*SELECT* 1_4".timeoffid
114. 35.197 75.367 ↑ 1.0 37,974 1

Hash Anti Join (cost=114.55..8,006.62 rows=38,782 width=4) (actual time=0.657..75.367 rows=37,974 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. 39.535 39.535 ↑ 1.0 38,827 1

Seq Scan on e390456bb6e840b6ad8d2732a449fdc8.dm_timeofflist_facts dm_timeofflist_facts_4 (cost=0.00..7,400.99 rows=39,172 width=4) (actual time=0.006..39.535 rows=38,827 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: 53472
116. 0.635 0.635 ↓ 1.0 921 1

Hash (cost=103.06..103.06 rows=919 width=4) (actual time=0.635..0.635 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.364 2.047 ↓ 1.0 853 1

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

  • Output: "*SELECT* 2_4".timeoffid
118. 1.683 1.683 ↓ 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.009..1.683 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.851 427.097 ↓ 0.0 0 38,827

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

  • 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: 5281
121. 0.000 310.616 ↓ 0.0 0 38,827

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

  • Output: timeoffs57.id
  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
122. 77.654 310.616 ↓ 0.0 0 38,827

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

  • 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.654 232.962 ↑ 1.0 1 38,827

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

  • 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.654 77.654 ↑ 1.0 1 38,827

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

  • 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.654 77.654 ↑ 1.0 1 38,827

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

  • 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,315.754 22,751.477 ↑ 1.8 48,061 1

Index Scan using timeoffs_pkey on e390456bb6e840b6ad8d2732a449fdc8.timeoffs timeoffs46 (cost=1,638.79..4,095,280.90 rows=88,480 width=4) (actual time=1,457.594..22,751.477 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. 22.822 62.983 ↓ 43.7 48,064 1

Nested Loop (cost=0.84..1,210.94 rows=1,100 width=4) (actual time=0.415..62.983 rows=48,064 loops=1)

  • Output: timeoffs47.id
129. 1.830 3.631 ↓ 4.8 3,653 1

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

  • Output: userlocation48.userid, userlocation48.startdate, userlocation48.enddate
130. 0.004 0.004 ↑ 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.003..0.004 rows=1 loops=1)

  • Output: locationflathierarchy49.parentid, locationflathierarchy49.childid
  • Index Cond: (locationflathierarchy49.parentid = '9caacfef-323b-49b2-bd43-565b0dbd36e1'::uuid)
  • Heap Fetches: 1
131. 1.797 1.797 ↓ 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.797 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 ↓ 4.3 13 3,653

Index Scan using ixto4userid on e390456bb6e840b6ad8d2732a449fdc8.timeoffs timeoffs47 (cost=0.42..1.36 rows=3 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,372.740 19,372.740 ↓ 9.0 314 44,230

Index Scan using ix3usa_ededst on e390456bb6e840b6ad8d2732a449fdc8.userscheduleassignment userscheduleassignment52 (cost=0.29..74.68 rows=35 width=4) (actual time=0.311..0.438 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. 47,274.719 47,274.744 ↑ 92,257.0 1 1

Result (cost=0.69..6,286.27 rows=92,257 width=0) (actual time=47,274.744..47,274.744 rows=1 loops=1)

  • Output: $18, $39, $60, $79
140. 0.000 0.025 ↑ 92,257.0 1 1

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

141. 0.001 0.025 ↑ 91,347.0 1 1

Subquery Scan on *SELECT* 1 (cost=0.69..4,992.98 rows=91,347 width=0) (actual time=0.025..0.025 rows=1 loops=1)

142. 0.004 0.024 ↑ 91,347.0 1 1

Merge Anti Join (cost=0.69..4,079.51 rows=91,347 width=0) (actual time=0.024..0.024 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.015 0.015 ↑ 92,266.0 1 1

Index Only Scan using dm_timeofflist_facts_pkey on e390456bb6e840b6ad8d2732a449fdc8.dm_timeofflist_facts (cost=0.42..3,734.41 rows=92,266 width=4) (actual time=0.015..0.015 rows=1 loops=1)

  • Output: dm_timeofflist_facts.timeoffid
  • Heap Fetches: 1
144. 0.005 0.005 ↑ 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.005..0.005 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 : 11.783 ms