explain.depesz.com

PostgreSQL's explain analyze made readable

Result: H8In

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 90,650.423 ↑ 1.0 10 1

Limit (cost=17,973,235.35..17,973,259.98 rows=10 width=276) (actual time=90,650.360..90,650.423 rows=10 loops=1)

  • Buffers: shared hit=222,168
2. 7.648 90,650.421 ↑ 4,138.4 10 1

Result (cost=17,973,235.35..18,075,143.45 rows=41,384 width=276) (actual time=90,650.359..90,650.421 rows=10 loops=1)

  • Buffers: shared hit=222,168
3. 6.979 90,450.761 ↑ 4,138.4 10 1

Sort (cost=8,696,874.25..8,696,977.71 rows=41,384 width=260) (actual time=90,450.758..90,450.761 rows=10 loops=1)

  • Sort Key: "*SELECT* 1".timesheetstartdate DESC, (("*SELECT* 1".owneruserdisplayname)::text) COLLATE "en_US", "*SELECT* 1".timesheetid
  • Sort Method: top-N heapsort Memory: 30kB
  • Buffers: shared hit=182,843
4. 41.952 90,443.782 ↑ 11.6 3,568 1

Nested Loop Semi Join (cost=7,250,399.45..8,695,979.96 rows=41,384 width=260) (actual time=693.897..90,443.782 rows=3,568 loops=1)

  • Buffers: shared hit=182,843
5. 50,606.175 89,746.520 ↑ 23.2 3,568 1

Nested Loop Semi Join (cost=4,256.61..51,677.31 rows=82,768 width=175) (actual time=77.288..89,746.520 rows=3,568 loops=1)

  • Join Filter: ("*SELECT* 1".timesheetid = timesheetapprovalhistory36.timesheetid)
  • Rows Removed by Join Filter: 701,461,355
  • Buffers: shared hit=42,108
6. 18.725 239.150 ↑ 1.0 165,537 1

Append (cost=13.82..25,835.66 rows=165,537 width=159) (actual time=0.013..239.150 rows=165,537 loops=1)

  • Buffers: shared hit=20,411
7. 29.014 220.417 ↓ 1.0 165,537 1

Subquery Scan on *SELECT* 1 (cost=13.82..25,822.26 rows=165,367 width=159) (actual time=0.013..220.417 rows=165,537 loops=1)

  • Buffers: shared hit=20,411
8. 133.810 191.403 ↓ 1.0 165,537 1

Hash Anti Join (cost=13.82..24,168.59 rows=165,367 width=1,159) (actual time=0.013..191.403 rows=165,537 loops=1)

  • Hash Cond: (dm_timesheetlist_facts.timesheetid = dm_timesheetlist_realtime_facts.timesheetid)
  • Buffers: shared hit=20,411
9. 57.592 57.592 ↑ 1.0 165,537 1

Seq Scan on dm_timesheetlist_facts (cost=0.00..22,066.37 rows=165,537 width=159) (actual time=0.005..57.592 rows=165,537 loops=1)

  • Buffers: shared hit=20,411
10. 0.000 0.001 ↓ 0.0 0 1

Hash (cost=11.70..11.70 rows=170 width=16) (actual time=0.001..0.001 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
11. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on dm_timesheetlist_realtime_facts (cost=0.00..11.70 rows=170 width=16) (actual time=0.001..0.001 rows=0 loops=1)

12. 0.000 0.008 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=0.00..13.40 rows=170 width=159) (actual time=0.008..0.008 rows=0 loops=1)

13. 0.008 0.008 ↓ 0.0 0 1

Seq Scan on dm_timesheetlist_realtime_facts dm_timesheetlist_realtime_facts_1 (cost=0.00..11.70 rows=170 width=1,159) (actual time=0.008..0.008 rows=0 loops=1)

  • Filter: (NOT deleted)
14. 38,840.564 38,901.195 ↓ 2,119.0 4,238 165,537

Materialize (cost=4,242.79..20,875.54 rows=2 width=16) (actual time=0.000..0.235 rows=4,238 loops=165,537)

  • Buffers: shared hit=21,697
15. 1.825 60.631 ↓ 2,141.5 4,283 1

Nested Loop (cost=4,242.79..20,875.53 rows=2 width=16) (actual time=6.652..60.631 rows=4,283 loops=1)

  • Buffers: shared hit=21,697
16. 1.487 33.108 ↓ 61.2 4,283 1

Hash Join (cost=4,242.23..20,702.11 rows=70 width=32) (actual time=6.634..33.108 rows=4,283 loops=1)

  • Hash Cond: (sheetapprovalhistorykeyvalue37.timesheetapprovalhistoryid = timesheetapprovalhistory36.id)
  • Buffers: shared hit=246
17. 27.354 27.354 ↑ 2.8 4,677 1

Index Scan using ixtahkvuri on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue37 (cost=0.56..16,425.68 rows=13,242 width=32) (actual time=2.329..27.354 rows=4,677 loops=1)

  • Index Cond: (upper(uri) = 'URN:REPLICON-TENANT:E42FA0CDF18E4CEBA7EA5AB36F013E92:USER:1,578'::text)
  • Filter: (upper(key) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:REMOVED-APPROVAL-REQUEST-NODES'::text)
  • Rows Removed by Filter: 15,213
  • Buffers: shared hit=220
18. 0.821 4.267 ↓ 1.0 4,935 1

Hash (cost=4,181.44..4,181.44 rows=4,818 width=32) (actual time=4.267..4.267 rows=4,935 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 373kB
  • Buffers: shared hit=26
19. 3.446 3.446 ↓ 1.0 4,935 1

Index Scan using ixtahuserid on timesheetapprovalhistory timesheetapprovalhistory36 (cost=0.42..4,181.44 rows=4,818 width=32) (actual time=0.021..3.446 rows=4,935 loops=1)

  • Index Cond: (userid = 1,578)
  • Buffers: shared hit=26
20. 25.698 25.698 ↑ 1.0 1 4,283

Index Scan using timesheetapprovalhistorykeyvalue_pkey on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue38 (cost=0.56..2.48 rows=1 width=16) (actual time=0.006..0.006 rows=1 loops=4,283)

  • Index Cond: (id = sheetapprovalhistorykeyvalue37.parentid)
  • Filter: (upper(uri) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:REMOVED-APPROVAL-REQUEST-EXPECTED-APPROVAL-AGENT-USER'::text)
  • Buffers: shared hit=21,451
21. 18.478 60.656 ↑ 1.0 1 3,568

Nested Loop Left Join (cost=4.57..19.09 rows=1 width=16) (actual time=0.017..0.017 rows=1 loops=3,568)

  • Filter: ((hashed SubPlan 32) OR (timesheet29.userid = 1,578) OR (alternatives: SubPlan 33 or hashed SubPlan 34) OR (alternatives: SubPlan 35 or hashed SubPlan 36) OR (alternatives: SubPlan 37 or hashed SubPlan 38))
  • Buffers: shared hit=22,865
22. 24.976 24.976 ↑ 1.0 1 3,568

Index Scan using timesheet_pkey on timesheet timesheet29 (cost=0.42..0.45 rows=1 width=28) (actual time=0.007..0.007 rows=1 loops=3,568)

  • Index Cond: (id = "*SELECT* 1".timesheetid)
  • Buffers: shared hit=14,302
23. 14.272 14.272 ↑ 1.0 1 3,568

Index Only Scan using ix4ul_userlocationstartend on userlocation userlocation30 (cost=0.28..0.30 rows=1 width=28) (actual time=0.004..0.004 rows=1 loops=3,568)

  • Index Cond: ((userid = timesheet29.userid) AND (startdate <= timesheet29.enddate) AND (enddate >= timesheet29.startdate))
  • Heap Fetches: 0
  • Buffers: shared hit=7,137
24.          

SubPlan (for Nested Loop Left Join)

25. 0.020 0.020 ↑ 1.0 3 1

Index Only Scan using locationflathierarchy_pkey on locationflathierarchy locationflathierarchy31 (cost=0.27..3.87 rows=3 width=16) (actual time=0.016..0.020 rows=3 loops=1)

  • Index Cond: (parentid = ANY ('{54b703c7-220c-4dd5-add4-39132ac47bbe,dd6bba65-3e4c-46c6-af16-f44853d8da88,fc983f67-203a-4b2d-ac9d-876a11ea2f65}'::uuid[]))
  • Heap Fetches: 0
  • Buffers: shared hit=7
26. 0.772 0.772 ↓ 0.0 0 193

Index Scan using ix3uh_usersuperstart on userhierarchy userhierarchy32 (cost=0.29..2.31 rows=1 width=0) (actual time=0.004..0.004 rows=0 loops=193)

  • Index Cond: ((userid = timesheet29.userid) AND (supervisorid = 1,578) AND (startdate <= '2020-08-31'::date))
  • Filter: (enddate >= '2020-08-31'::date)
  • Buffers: shared hit=386
27. 0.000 0.000 ↓ 0.0 0

Index Scan using ixuhsupervisorid on userhierarchy userhierarchy32_1 (cost=0.29..16.64 rows=3 width=4) (never executed)

  • Index Cond: (supervisorid = 1,578)
  • Filter: ((startdate <= '2020-08-31'::date) AND (enddate >= '2020-08-31'::date))
28. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.54..4.59 rows=1 width=0) (never executed)

29. 0.000 0.000 ↓ 0.0 0

Index Only Scan using uix2tsar_nodeid_userid on tsapprovalrequest tsapprovalrequest33 (cost=0.27..1.29 rows=1 width=16) (never executed)

  • Index Cond: (userid = 1,578)
  • Heap Fetches: 0
30. 0.000 0.000 ↓ 0.0 0

Index Scan using timesheetapprovalnodes_pkey on timesheetapprovalnodes timesheetapprovalnodes34 (cost=0.27..2.29 rows=1 width=16) (never executed)

  • Index Cond: (id = tsapprovalrequest33.nodeid)
  • Filter: (timesheetid = timesheet29.id)
31. 0.001 0.015 ↓ 0.0 0 1

Nested Loop (cost=0.54..3.59 rows=1 width=16) (actual time=0.015..0.015 rows=0 loops=1)

  • Buffers: shared hit=2
32. 0.014 0.014 ↓ 0.0 0 1

Index Only Scan using uix2tsar_nodeid_userid on tsapprovalrequest tsapprovalrequest33_1 (cost=0.27..1.29 rows=1 width=16) (actual time=0.014..0.014 rows=0 loops=1)

  • Index Cond: (userid = 1,578)
  • Heap Fetches: 0
  • Buffers: shared hit=2
33. 0.000 0.000 ↓ 0.0 0

Index Scan using timesheetapprovalnodes_pkey on timesheetapprovalnodes timesheetapprovalnodes34_1 (cost=0.27..2.29 rows=1 width=32) (never executed)

  • Index Cond: (id = tsapprovalrequest33_1.nodeid)
34. 2.123 2.123 ↑ 1.0 1 193

Index Scan using ixtah2timesheetid on timesheetapprovalhistory timesheetapprovalhistory35 (cost=0.42..7.54 rows=1 width=0) (actual time=0.011..0.011 rows=1 loops=193)

  • Index Cond: (timesheetid = timesheet29.id)
  • Filter: (userid = 1,578)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=1,031
35. 0.000 0.000 ↓ 0.0 0

Index Scan using ixtahuserid on timesheetapprovalhistory timesheetapprovalhistory35_1 (cost=0.42..4,181.44 rows=4,818 width=16) (never executed)

  • Index Cond: (userid = 1,578)
36.          

SubPlan (for Nested Loop Semi Join)

37. 93.210 199.353 ↑ 9.7 16,037 1

Merge Left Join (cost=5.75..2,414,991.41 rows=155,203 width=16) (actual time=5.461..199.353 rows=16,037 loops=1)

  • Merge Cond: (timesheet1.userid = userlocation2.userid)
  • Join Filter: ((userlocation2.startdate <= timesheet1.enddate) AND (userlocation2.enddate >= timesheet1.startdate))
  • Rows Removed by Join Filter: 1,190
  • Filter: ((hashed SubPlan 1) OR (timesheet1.userid = 1,578) OR (alternatives: SubPlan 2 or hashed SubPlan 3) OR (alternatives: SubPlan 4 or hashed SubPlan 5) OR (alternatives: SubPlan 6 or hashed SubPlan 7))
  • Rows Removed by Filter: 149,500
  • Buffers: shared hit=39,290
38. 92.335 92.335 ↑ 1.0 165,537 1

Index Scan using uix2tsuseridstartdate on timesheet timesheet1 (cost=0.42..4,701.48 rows=165,537 width=28) (actual time=0.040..92.335 rows=165,537 loops=1)

  • Buffers: shared hit=39,232
39. 9.931 10.264 ↓ 85.3 166,790 1

Materialize (cost=0.28..48.49 rows=1,955 width=28) (actual time=0.019..10.264 rows=166,790 loops=1)

  • Buffers: shared hit=17
40. 0.333 0.333 ↑ 1.0 1,955 1

Index Only Scan using ix4ul_userlocationstartend on userlocation userlocation2 (cost=0.28..43.60 rows=1,955 width=28) (actual time=0.015..0.333 rows=1,955 loops=1)

  • Heap Fetches: 0
  • Buffers: shared hit=17
41.          

SubPlan (for Merge Left Join)

42. 0.011 0.011 ↑ 1.0 3 1

Index Only Scan using locationflathierarchy_pkey on locationflathierarchy locationflathierarchy3 (cost=0.27..3.87 rows=3 width=16) (actual time=0.008..0.011 rows=3 loops=1)

  • Index Cond: (parentid = ANY ('{54b703c7-220c-4dd5-add4-39132ac47bbe,dd6bba65-3e4c-46c6-af16-f44853d8da88,fc983f67-203a-4b2d-ac9d-876a11ea2f65}'::uuid[]))
  • Heap Fetches: 0
  • Buffers: shared hit=7
43. 0.000 0.000 ↓ 0.0 0

Index Scan using ix3uh_usersuperstart on userhierarchy userhierarchy4 (cost=0.29..2.31 rows=1 width=0) (never executed)

  • Index Cond: ((userid = timesheet1.userid) AND (supervisorid = 1,578) AND (startdate <= '2020-08-31'::date))
  • Filter: (enddate >= '2020-08-31'::date)
44. 0.031 0.031 ↓ 1.3 4 1

Index Scan using ixuhsupervisorid on userhierarchy userhierarchy4_1 (cost=0.29..16.64 rows=3 width=4) (actual time=0.025..0.031 rows=4 loops=1)

  • Index Cond: (supervisorid = 1,578)
  • Filter: ((startdate <= '2020-08-31'::date) AND (enddate >= '2020-08-31'::date))
  • Rows Removed by Filter: 12
  • Buffers: shared hit=6
45. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.54..4.59 rows=1 width=0) (never executed)

46. 0.000 0.000 ↓ 0.0 0

Index Only Scan using uix2tsar_nodeid_userid on tsapprovalrequest tsapprovalrequest5 (cost=0.27..1.29 rows=1 width=16) (never executed)

  • Index Cond: (userid = 1,578)
  • Heap Fetches: 0
47. 0.000 0.000 ↓ 0.0 0

Index Scan using timesheetapprovalnodes_pkey on timesheetapprovalnodes timesheetapprovalnodes6 (cost=0.27..2.29 rows=1 width=16) (never executed)

  • Index Cond: (id = tsapprovalrequest5.nodeid)
  • Filter: (timesheetid = timesheet1.id)
48. 0.001 0.008 ↓ 0.0 0 1

Nested Loop (cost=0.54..3.59 rows=1 width=16) (actual time=0.008..0.008 rows=0 loops=1)

  • Buffers: shared hit=2
49. 0.007 0.007 ↓ 0.0 0 1

Index Only Scan using uix2tsar_nodeid_userid on tsapprovalrequest tsapprovalrequest5_1 (cost=0.27..1.29 rows=1 width=16) (actual time=0.007..0.007 rows=0 loops=1)

  • Index Cond: (userid = 1,578)
  • Heap Fetches: 0
  • Buffers: shared hit=2
50. 0.000 0.000 ↓ 0.0 0

Index Scan using timesheetapprovalnodes_pkey on timesheetapprovalnodes timesheetapprovalnodes6_1 (cost=0.27..2.29 rows=1 width=32) (never executed)

  • Index Cond: (id = tsapprovalrequest5_1.nodeid)
51. 0.000 0.000 ↓ 0.0 0

Index Scan using ixtah2timesheetid on timesheetapprovalhistory timesheetapprovalhistory7 (cost=0.42..7.54 rows=1 width=0) (never executed)

  • Index Cond: (timesheetid = timesheet1.id)
  • Filter: (userid = 1,578)
52. 3.494 3.494 ↓ 1.0 4,935 1

Index Scan using ixtahuserid on timesheetapprovalhistory timesheetapprovalhistory7_1 (cost=0.42..4,181.44 rows=4,818 width=16) (actual time=0.027..3.494 rows=4,935 loops=1)

  • Index Cond: (userid = 1,578)
  • Buffers: shared hit=26
53. 89.560 195.669 ↑ 9.7 16,037 1

Merge Left Join (cost=5.75..2,414,991.41 rows=155,203 width=16) (actual time=5.193..195.669 rows=16,037 loops=1)

  • Merge Cond: (timesheet8.userid = userlocation9.userid)
  • Join Filter: ((userlocation9.startdate <= timesheet8.enddate) AND (userlocation9.enddate >= timesheet8.startdate))
  • Rows Removed by Join Filter: 1,190
  • Filter: ((hashed SubPlan 9) OR (timesheet8.userid = 1,578) OR (alternatives: SubPlan 10 or hashed SubPlan 11) OR (alternatives: SubPlan 12 or hashed SubPlan 13) OR (alternatives: SubPlan 14 or hashed SubPlan 15))
  • Rows Removed by Filter: 149,500
  • Buffers: shared hit=39,290
54. 91.981 91.981 ↑ 1.0 165,537 1

Index Scan using uix2tsuseridstartdate on timesheet timesheet8 (cost=0.42..4,701.48 rows=165,537 width=28) (actual time=0.039..91.981 rows=165,537 loops=1)

  • Buffers: shared hit=39,232
55. 10.316 10.671 ↓ 85.3 166,790 1

Materialize (cost=0.28..48.49 rows=1,955 width=28) (actual time=0.023..10.671 rows=166,790 loops=1)

  • Buffers: shared hit=17
56. 0.355 0.355 ↑ 1.0 1,955 1

Index Only Scan using ix4ul_userlocationstartend on userlocation userlocation9 (cost=0.28..43.60 rows=1,955 width=28) (actual time=0.020..0.355 rows=1,955 loops=1)

  • Heap Fetches: 0
  • Buffers: shared hit=17
57.          

SubPlan (for Merge Left Join)

58. 0.025 0.025 ↑ 1.0 3 1

Index Only Scan using locationflathierarchy_pkey on locationflathierarchy locationflathierarchy10 (cost=0.27..3.87 rows=3 width=16) (actual time=0.020..0.025 rows=3 loops=1)

  • Index Cond: (parentid = ANY ('{54b703c7-220c-4dd5-add4-39132ac47bbe,dd6bba65-3e4c-46c6-af16-f44853d8da88,fc983f67-203a-4b2d-ac9d-876a11ea2f65}'::uuid[]))
  • Heap Fetches: 0
  • Buffers: shared hit=7
59. 0.000 0.000 ↓ 0.0 0

Index Scan using ix3uh_usersuperstart on userhierarchy userhierarchy11 (cost=0.29..2.31 rows=1 width=0) (never executed)

  • Index Cond: ((userid = timesheet8.userid) AND (supervisorid = 1,578) AND (startdate <= '2020-08-31'::date))
  • Filter: (enddate >= '2020-08-31'::date)
60. 0.032 0.032 ↓ 1.3 4 1

Index Scan using ixuhsupervisorid on userhierarchy userhierarchy11_1 (cost=0.29..16.64 rows=3 width=4) (actual time=0.027..0.032 rows=4 loops=1)

  • Index Cond: (supervisorid = 1,578)
  • Filter: ((startdate <= '2020-08-31'::date) AND (enddate >= '2020-08-31'::date))
  • Rows Removed by Filter: 12
  • Buffers: shared hit=6
61. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.54..4.59 rows=1 width=0) (never executed)

62. 0.000 0.000 ↓ 0.0 0

Index Only Scan using uix2tsar_nodeid_userid on tsapprovalrequest tsapprovalrequest12 (cost=0.27..1.29 rows=1 width=16) (never executed)

  • Index Cond: (userid = 1,578)
  • Heap Fetches: 0
63. 0.000 0.000 ↓ 0.0 0

Index Scan using timesheetapprovalnodes_pkey on timesheetapprovalnodes timesheetapprovalnodes13 (cost=0.27..2.29 rows=1 width=16) (never executed)

  • Index Cond: (id = tsapprovalrequest12.nodeid)
  • Filter: (timesheetid = timesheet8.id)
64. 0.000 0.012 ↓ 0.0 0 1

Nested Loop (cost=0.54..3.59 rows=1 width=16) (actual time=0.012..0.012 rows=0 loops=1)

  • Buffers: shared hit=2
65. 0.012 0.012 ↓ 0.0 0 1

Index Only Scan using uix2tsar_nodeid_userid on tsapprovalrequest tsapprovalrequest12_1 (cost=0.27..1.29 rows=1 width=16) (actual time=0.012..0.012 rows=0 loops=1)

  • Index Cond: (userid = 1,578)
  • Heap Fetches: 0
  • Buffers: shared hit=2
66. 0.000 0.000 ↓ 0.0 0

Index Scan using timesheetapprovalnodes_pkey on timesheetapprovalnodes timesheetapprovalnodes13_1 (cost=0.27..2.29 rows=1 width=32) (never executed)

  • Index Cond: (id = tsapprovalrequest12_1.nodeid)
67. 0.000 0.000 ↓ 0.0 0

Index Scan using ixtah2timesheetid on timesheetapprovalhistory timesheetapprovalhistory14 (cost=0.42..7.54 rows=1 width=0) (never executed)

  • Index Cond: (timesheetid = timesheet8.id)
  • Filter: (userid = 1,578)
68. 3.388 3.388 ↓ 1.0 4,935 1

Index Scan using ixtahuserid on timesheetapprovalhistory timesheetapprovalhistory14_1 (cost=0.42..4,181.44 rows=4,818 width=16) (actual time=0.028..3.388 rows=4,935 loops=1)

  • Index Cond: (userid = 1,578)
  • Buffers: shared hit=26
69. 89.804 199.632 ↑ 9.7 16,037 1

Merge Left Join (cost=5.75..2,414,991.41 rows=155,203 width=16) (actual time=5.373..199.632 rows=16,037 loops=1)

  • Merge Cond: (timesheet15.userid = userlocation16.userid)
  • Join Filter: ((userlocation16.startdate <= timesheet15.enddate) AND (userlocation16.enddate >= timesheet15.startdate))
  • Rows Removed by Join Filter: 1,190
  • Filter: ((hashed SubPlan 17) OR (timesheet15.userid = 1,578) OR (alternatives: SubPlan 18 or hashed SubPlan 19) OR (alternatives: SubPlan 20 or hashed SubPlan 21) OR (alternatives: SubPlan 22 or hashed SubPlan 23))
  • Rows Removed by Filter: 149,500
  • Buffers: shared hit=39,290
70. 94.535 94.535 ↑ 1.0 165,537 1

Index Scan using uix2tsuseridstartdate on timesheet timesheet15 (cost=0.42..4,701.48 rows=165,537 width=28) (actual time=0.043..94.535 rows=165,537 loops=1)

  • Buffers: shared hit=39,232
71. 11.393 11.769 ↓ 85.3 166,790 1

Materialize (cost=0.28..48.49 rows=1,955 width=28) (actual time=0.028..11.769 rows=166,790 loops=1)

  • Buffers: shared hit=17
72. 0.376 0.376 ↑ 1.0 1,955 1

Index Only Scan using ix4ul_userlocationstartend on userlocation userlocation16 (cost=0.28..43.60 rows=1,955 width=28) (actual time=0.025..0.376 rows=1,955 loops=1)

  • Heap Fetches: 0
  • Buffers: shared hit=17
73.          

SubPlan (for Merge Left Join)

74. 0.024 0.024 ↑ 1.0 3 1

Index Only Scan using locationflathierarchy_pkey on locationflathierarchy locationflathierarchy17 (cost=0.27..3.87 rows=3 width=16) (actual time=0.020..0.024 rows=3 loops=1)

  • Index Cond: (parentid = ANY ('{54b703c7-220c-4dd5-add4-39132ac47bbe,dd6bba65-3e4c-46c6-af16-f44853d8da88,fc983f67-203a-4b2d-ac9d-876a11ea2f65}'::uuid[]))
  • Heap Fetches: 0
  • Buffers: shared hit=7
75. 0.000 0.000 ↓ 0.0 0

Index Scan using ix3uh_usersuperstart on userhierarchy userhierarchy18 (cost=0.29..2.31 rows=1 width=0) (never executed)

  • Index Cond: ((userid = timesheet15.userid) AND (supervisorid = 1,578) AND (startdate <= '2020-08-31'::date))
  • Filter: (enddate >= '2020-08-31'::date)
76. 0.028 0.028 ↓ 1.3 4 1

Index Scan using ixuhsupervisorid on userhierarchy userhierarchy18_1 (cost=0.29..16.64 rows=3 width=4) (actual time=0.023..0.028 rows=4 loops=1)

  • Index Cond: (supervisorid = 1,578)
  • Filter: ((startdate <= '2020-08-31'::date) AND (enddate >= '2020-08-31'::date))
  • Rows Removed by Filter: 12
  • Buffers: shared hit=6
77. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.54..4.59 rows=1 width=0) (never executed)

78. 0.000 0.000 ↓ 0.0 0

Index Only Scan using uix2tsar_nodeid_userid on tsapprovalrequest tsapprovalrequest19 (cost=0.27..1.29 rows=1 width=16) (never executed)

  • Index Cond: (userid = 1,578)
  • Heap Fetches: 0
79. 0.000 0.000 ↓ 0.0 0

Index Scan using timesheetapprovalnodes_pkey on timesheetapprovalnodes timesheetapprovalnodes20 (cost=0.27..2.29 rows=1 width=16) (never executed)

  • Index Cond: (id = tsapprovalrequest19.nodeid)
  • Filter: (timesheetid = timesheet15.id)
80. 0.000 0.013 ↓ 0.0 0 1

Nested Loop (cost=0.54..3.59 rows=1 width=16) (actual time=0.013..0.013 rows=0 loops=1)

  • Buffers: shared hit=2
81. 0.013 0.013 ↓ 0.0 0 1

Index Only Scan using uix2tsar_nodeid_userid on tsapprovalrequest tsapprovalrequest19_1 (cost=0.27..1.29 rows=1 width=16) (actual time=0.013..0.013 rows=0 loops=1)

  • Index Cond: (userid = 1,578)
  • Heap Fetches: 0
  • Buffers: shared hit=2
82. 0.000 0.000 ↓ 0.0 0

Index Scan using timesheetapprovalnodes_pkey on timesheetapprovalnodes timesheetapprovalnodes20_1 (cost=0.27..2.29 rows=1 width=32) (never executed)

  • Index Cond: (id = tsapprovalrequest19_1.nodeid)
83. 0.000 0.000 ↓ 0.0 0

Index Scan using ixtah2timesheetid on timesheetapprovalhistory timesheetapprovalhistory21 (cost=0.42..7.54 rows=1 width=0) (never executed)

  • Index Cond: (timesheetid = timesheet15.id)
  • Filter: (userid = 1,578)
84. 3.459 3.459 ↓ 1.0 4,935 1

Index Scan using ixtahuserid on timesheetapprovalhistory timesheetapprovalhistory21_1 (cost=0.42..4,181.44 rows=4,818 width=16) (actual time=0.026..3.459 rows=4,935 loops=1)

  • Index Cond: (userid = 1,578)
  • Buffers: shared hit=26
85.          

SubPlan (for Result)

86. 77.470 191.942 ↑ 9.0 16,037 1

Merge Left Join (cost=5.75..2,029,860.73 rows=144,845 width=16) (actual time=5.693..191.942 rows=16,037 loops=1)

  • Merge Cond: (timesheet22.userid = userlocation23.userid)
  • Join Filter: ((userlocation23.startdate <= timesheet22.enddate) AND (userlocation23.enddate >= timesheet22.startdate))
  • Rows Removed by Join Filter: 1,190
  • Filter: ((hashed SubPlan 25) OR (alternatives: SubPlan 26 or hashed SubPlan 27) OR (alternatives: SubPlan 28 or hashed SubPlan 29))
  • Rows Removed by Filter: 149,500
  • Buffers: shared hit=39,284
87. 97.985 97.985 ↑ 1.0 165,537 1

Index Scan using uix2tsuseridstartdate on timesheet timesheet22 (cost=0.42..4,701.48 rows=165,537 width=28) (actual time=0.047..97.985 rows=165,537 loops=1)

  • Buffers: shared hit=39,232
88. 12.392 12.766 ↓ 85.3 166,790 1

Materialize (cost=0.28..48.49 rows=1,955 width=28) (actual time=0.024..12.766 rows=166,790 loops=1)

  • Buffers: shared hit=17
89. 0.374 0.374 ↑ 1.0 1,955 1

Index Only Scan using ix4ul_userlocationstartend on userlocation userlocation23 (cost=0.28..43.60 rows=1,955 width=28) (actual time=0.022..0.374 rows=1,955 loops=1)

  • Heap Fetches: 0
  • Buffers: shared hit=17
90.          

SubPlan (for Merge Left Join)

91. 0.027 0.027 ↑ 1.0 3 1

Index Only Scan using locationflathierarchy_pkey on locationflathierarchy locationflathierarchy24 (cost=0.27..3.87 rows=3 width=16) (actual time=0.022..0.027 rows=3 loops=1)

  • Index Cond: (parentid = ANY ('{54b703c7-220c-4dd5-add4-39132ac47bbe,dd6bba65-3e4c-46c6-af16-f44853d8da88,fc983f67-203a-4b2d-ac9d-876a11ea2f65}'::uuid[]))
  • Heap Fetches: 0
  • Buffers: shared hit=7
92. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.54..4.59 rows=1 width=0) (never executed)

93. 0.000 0.000 ↓ 0.0 0

Index Only Scan using uix2tsar_nodeid_userid on tsapprovalrequest tsapprovalrequest25 (cost=0.27..1.29 rows=1 width=16) (never executed)

  • Index Cond: (userid = 1,578)
  • Heap Fetches: 0
94. 0.000 0.000 ↓ 0.0 0

Index Scan using timesheetapprovalnodes_pkey on timesheetapprovalnodes timesheetapprovalnodes26 (cost=0.27..2.29 rows=1 width=16) (never executed)

  • Index Cond: (id = tsapprovalrequest25.nodeid)
  • Filter: (timesheetid = timesheet22.id)
95. 0.001 0.014 ↓ 0.0 0 1

Nested Loop (cost=0.54..3.59 rows=1 width=16) (actual time=0.014..0.014 rows=0 loops=1)

  • Buffers: shared hit=2
96. 0.013 0.013 ↓ 0.0 0 1

Index Only Scan using uix2tsar_nodeid_userid on tsapprovalrequest tsapprovalrequest25_1 (cost=0.27..1.29 rows=1 width=16) (actual time=0.013..0.013 rows=0 loops=1)

  • Index Cond: (userid = 1,578)
  • Heap Fetches: 0
  • Buffers: shared hit=2
97. 0.000 0.000 ↓ 0.0 0

Index Scan using timesheetapprovalnodes_pkey on timesheetapprovalnodes timesheetapprovalnodes26_1 (cost=0.27..2.29 rows=1 width=32) (never executed)

  • Index Cond: (id = tsapprovalrequest25_1.nodeid)
98. 0.000 0.000 ↓ 0.0 0

Index Scan using ixtah2timesheetid on timesheetapprovalhistory timesheetapprovalhistory27 (cost=0.42..7.54 rows=1 width=0) (never executed)

  • Index Cond: (timesheetid = timesheet22.id)
  • Filter: (userid = 1,578)
99. 3.680 3.680 ↓ 1.0 4,935 1

Index Scan using ixtahuserid on timesheetapprovalhistory timesheetapprovalhistory27_1 (cost=0.42..4,181.44 rows=4,818 width=16) (actual time=0.029..3.680 rows=4,935 loops=1)

  • Index Cond: (userid = 1,578)
  • Buffers: shared hit=26
100. 0.070 0.070 ↑ 1.0 1 10

Index Scan using uix2dtslpl_tsid_plid on dm_timesheetlist_projectleadertime_facts list_projectleadertime_facts28 (cost=0.42..2.44 rows=1 width=16) (actual time=0.007..0.007 rows=1 loops=10)

  • Index Cond: ((timesheetid = "*SELECT* 1".timesheetid) AND (projectleaderid = 1,578))
  • Buffers: shared hit=41
Planning time : 5.878 ms
Execution time : 90,653.291 ms