explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TuhB

Settings
# exclusive inclusive rows x rows loops node
1. 0.040 9,129,575.856 ↓ 0.0 0 1

Sort (cost=833,866.87..833,866.88 rows=1 width=837) (actual time=9,129,575.856..9,129,575.856 rows=0 loops=1)

  • Sort Key: ((ui.externalid)::character varying(255)) COLLATE "en_US", ((ui.lastname)::character varying(50)) COLLATE "en_US", ((ui.firstname)::character varying(50)) COLLATE "en_US", dmv_timesheetday_facts9.userduplicatename6, ((login.loginname)::character varying(255)) COLLATE "en_US", ((location11.name)::character varying(50)) COLLATE "en_US", ((pj.name)::character varying(255)) COLLATE "en_US", ((tk.name)::character varying(255)) COLLATE "en_US", ((dep.name)::character varying(255)) COLLATE "en_US", dmv_timesheetday_facts9.billingratename4 COLLATE "en_US", ((ui.info48)::character varying(255)) COLLATE "en_US", ((cl.name)::character varying(255)) COLLATE "en_US", ((userinfo16.displayname)::text) COLLATE "en_US", ((currencyinfo19.symbol)::character varying(50)) COLLATE "en_US", uprrh.hourlyrate, ((tk.info20)::character varying(255)) COLLATE "en_US", ((pj.info1)::character varying(255)) COLLATE "en_US
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=86431452 read=136850
2.          

CTE dmv_timesheetday_facts0cte

3. 0.002 9,129,575.793 ↓ 0.0 0 1

HashAggregate (cost=833,317.55..833,317.57 rows=1 width=120) (actual time=9,129,575.793..9,129,575.793 rows=0 loops=1)

  • Group Key: ts.userid, at_1.entrydate, upper((br.name)::text), pbrh.currencyid, ui_1.duplicatename, tslist.timesheetstatus, at_1.userid, at_1.projectid, at_1.taskid, (CASE WHEN (pj_4.clientbillingallocationmethod = 0) THEN pc_2.clientid ELSE at_1.userspecifiedclientid END), at_1.id, (CASE WHEN (pj_4.clientbillingallocationmethod = 0) THEN pc_2.effectivedate ELSE pc_3.effectivedate END), ts.id
  • Buffers: shared hit=86431441 read=136850
4.          

Initplan (forHashAggregate)

5. 0.000 0.000 ↓ 0.0 0

Seq Scan on projectsysteminformation (cost=0.00..1.01 rows=1 width=16) (never executed)

6. 0.000 0.000 ↓ 0.0 0

Seq Scan on projectsysteminformation projectsysteminformation_1 (cost=0.00..1.01 rows=1 width=16) (never executed)

7. 0.000 9,129,575.791 ↓ 0.0 0 1

Nested Loop Semi Join (cost=114,172.57..833,315.48 rows=1 width=120) (actual time=9,129,575.791..9,129,575.791 rows=0 loops=1)

  • Buffers: shared hit=86431441 read=136850
8. 0.001 9,129,575.791 ↓ 0.0 0 1

Nested Loop Semi Join (cost=114,172.28..833,315.15 rows=1 width=120) (actual time=9,129,575.791..9,129,575.791 rows=0 loops=1)

  • Join Filter: (ts.userid = userinfo6.id)
  • Buffers: shared hit=86431441 read=136850
9. 0.001 9,129,575.790 ↓ 0.0 0 1

Nested Loop (cost=114,172.00..833,314.82 rows=1 width=124) (actual time=9,129,575.790..9,129,575.790 rows=0 loops=1)

  • Join Filter: (ts.id = tslist.timesheetid)
  • Buffers: shared hit=86431441 read=136850
10. 0.001 9,129,575.789 ↓ 0.0 0 1

Nested Loop (cost=114,171.58..833,314.34 rows=1 width=152) (actual time=9,129,575.789..9,129,575.789 rows=0 loops=1)

  • Join Filter: (ts.id = ts_1.id)
  • Buffers: shared hit=86431441 read=136850
11. 0.000 9,129,575.788 ↓ 0.0 0 1

Nested Loop (cost=114,171.16..833,313.89 rows=1 width=136) (actual time=9,129,575.788..9,129,575.788 rows=0 loops=1)

  • Buffers: shared hit=86431441 read=136850
12. 0.001 9,129,575.788 ↓ 0.0 0 1

Nested Loop Left Join (cost=114,170.87..833,311.58 rows=1 width=131) (actual time=9,129,575.788..9,129,575.788 rows=0 loops=1)

  • Buffers: shared hit=86431441 read=136850
13. 121,750.689 9,129,575.787 ↓ 0.0 0 1

Nested Loop (cost=114,169.05..833,304.85 rows=1 width=92) (actual time=9,129,575.787..9,129,575.787 rows=0 loops=1)

  • Join Filter: (at_1.timesheetid = ts.id)
  • Rows Removed by Join Filter: 237643068
  • Buffers: shared hit=86431441 read=136850
14. 17.562 17.562 ↓ 732.0 732 1

Index Scan using uix2tsuseridstartdate on timesheet ts (cost=0.42..2,792.26 rows=1 width=20) (actual time=0.018..17.562 rows=732 loops=1)

  • Index Cond: ((startdate >= '2019-03-09'::date) AND (startdate <= '2019-03-15'::date))
  • Filter: ((enddate >= '2019-03-09'::date) AND (enddate <= '2019-03-15'::date))
  • Rows Removed by Filter: 982
  • Buffers: shared hit=978 read=645
15. 444,837.330 9,007,807.536 ↓ 13.7 324,649 732

Hash Join (cost=114,168.63..830,216.00 rows=23,727 width=72) (actual time=15.712..12,305.748 rows=324,649 loops=732)

  • Hash Cond: (at_1.userid = userlocation5.userid)
  • Join Filter: ((at_1.entrydate >= userlocation5.startdate) AND (at_1.entrydate <= userlocation5.enddate))
  • Buffers: shared hit=86430463 read=136205
16. 3,877,289.051 8,562,967.476 ↓ 1.3 1,229,922 732

Hash Left Join (cost=114,076.72..811,649.07 rows=939,808 width=137) (actual time=2.582..11,698.043 rows=1,229,922 loops=732)

  • Hash Cond: (at_1.projectid = pj_4.id)
  • Join Filter: ((at_1.entrydate >= pc_2.effectivedate) AND (at_1.entrydate <= pc_2.enddate))
  • Buffers: shared hit=86430317 read=136153
17. 487,518.517 4,685,649.852 ↓ 1.3 1,229,922 732

Hash Left Join (cost=112,970.01..308,036.99 rows=939,808 width=93) (actual time=2.513..6,401.161 rows=1,229,922 loops=732)

  • Hash Cond: ((at_1.projectid = pj_5.id) AND (at_1.userspecifiedclientid = pc_3.clientid))
  • Join Filter: ((at_1.entrydate >= pc_3.effectivedate) AND (at_1.entrydate <= pc_3.enddate))
  • Buffers: shared hit=86429939 read=136067
18. 765,459.949 4,198,118.820 ↓ 1.3 1,229,922 732

Hash Right Join (cost=112,416.22..300,434.62 rows=939,808 width=89) (actual time=2.494..5,735.135 rows=1,229,922 loops=732)

  • Hash Cond: (isbillable.factid = at_1.id)
  • Buffers: shared hit=86429793 read=135729
19. 3,430,955.004 3,430,955.004 ↓ 43.0 996,629 732

Seq Scan on dm_attendancetimeallocation_metadata isbillable (cost=0.00..187,754.75 rows=23,173 width=17) (actual time=0.162..4,687.097 rows=996,629 loops=732)

  • Filter: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:IS-BILLABLE'::text)
  • Rows Removed by Filter: 3628540
  • Buffers: shared hit=86429787 read=118233
20. 621.819 1,703.867 ↓ 1.3 1,229,922 1

Hash (cost=100,668.62..100,668.62 rows=939,808 width=88) (actual time=1,703.867..1,703.867 rows=1,229,922 loops=1)

  • Buckets: 2097152 (originally 1048576) Batches: 1 (originally 1) Memory Usage: 141339kB
  • Buffers: shared hit=6 read=17496
21. 1,082.048 1,082.048 ↓ 1.3 1,229,922 1

Seq Scan on dm_attendancetimeallocation_facts at_1 (cost=0.00..100,668.62 rows=939,808 width=88) (actual time=0.543..1,082.048 rows=1,229,922 loops=1)

  • Filter: ((billingrateid = ANY ('{6c1e65d8-88d2-4e30-b872-295cd7f64630,8c3724d6-7f83-49e4-8dea-9dcdd67cec73,9113c536-d33e-474a-b6be-f1b89d99b000,350dbaba-3cd2-4264-ad6a-8ed8a8ac6892,dd90603a-fd4e-4ac0-a50b-626d799ecb68,09f01e95-5fb2-475d-b2c2-d7ed02c4a437,449cb77e-5e5d-4aa2-927d-cbe5b6545971,aed4bbfb-1b1a-440a-bb7b-fe41de2e8fc2,d800c575-3712-4054-9914-89511994ee8b,81c7a91c-880d-4565-a7b9-137fff8e7efb,66ba227a-9e3e-4bb4-9b59-9664fed866fb,a0420abe-d4e7-460e-a072-a36891d860ba,0e795f59-009d-4cd9-9159-edaa4c247f8e,f18e94a2-914c-4d37-ac54-861be69caa44,1429f6c4-7365-4113-8daa-6f846b14edb2,8ae4befc-a7ae-4311-a2ec-1cecb3af2cb3,d3022bcb-5363-44b1-b99e-20fdd5d3efc1,1cc585c7-6ee8-466e-be52-1a3cdb4d6619,36d7a237-e312-4c18-b2f7-535c33b9cd35,33530502-b156-4749-8df3-6465255024b8,ad01f1ad-4931-4bde-ac17-30d4b451b3a4,71b0ceee-b9b8-4352-9020-b0d969379fb9,50eb867d-b6a7-4aec-978c-a8a0ae0fc89e,fdbd6697-70b8-4e13-b668-e812d5bbb060,d65a9bda-611e-4d42-9335-fdb3e717b306,e73ff517-30f6-4e3d-b3e8-02fa67cf94c7,482598b3-cd49-4be4-8f08-275320e3ebe0,5e3db23f-c0ae-45d4-82c8-c69a61738224,e494e96d-2dcb-42b1-9dd7-41ecc0b9bdc5,0ca19249-1dd5-4579-9cb1-c5b695278766,0eebb223-4d9a-481b-a087-6969b74dd498,7b8a1a53-7549-4ed7-b346-c5713a34b85e,56e85250-0967-40d7-a819-40963b4d259a,1ccec5da-8123-4547-b939-9095af3fe0c4,351ee345-09a4-4d62-9ab7-f3e22ec7f79d,f8a40b95-164d-440f-bf4b-6be020d6c3ec,214c90ae-dcad-4d19-bb98-dbe919338936,feead545-2331-4047-8c83-87d63f8bdf7e,1015f254-767a-4cf5-b858-6ee2fc46eec0,27cfb4b7-e4c0-4ed3-9786-31fc1a531fc3,e09ed7ac-66ef-4285-94f8-cfb8831414cb,c0ad986d-d2cc-4704-9f61-a3abfe1a675b,1ef12768-cade-40c2-9350-6d7af60eb02b,48d82ba3-ef89-4383-a377-e7dc013291df,714566f3-baaf-4f8b-97e0-dd7c73e8c5da,151cb870-d209-4d4c-9a75-db84ecfd0745}'::uuid[])) OR (billingrateid IS NULL))
  • Buffers: shared hit=6 read=17496
22. 0.055 12.515 ↓ 1.1 67 1

Hash (cost=552.87..552.87 rows=61 width=16) (actual time=12.515..12.515 rows=67 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
  • Buffers: shared hit=146 read=338
23. 0.109 12.460 ↓ 1.1 67 1

Nested Loop (cost=0.29..552.87 rows=61 width=16) (actual time=1.504..12.460 rows=67 loops=1)

  • Buffers: shared hit=146 read=338
24. 7.527 7.527 ↑ 1.0 67 1

Seq Scan on project pj_5 (cost=0.00..478.94 rows=67 width=4) (actual time=0.547..7.527 rows=67 loops=1)

  • Filter: (clientbillingallocationmethod = 1)
  • Rows Removed by Filter: 12008
  • Buffers: shared hit=2 read=326
25. 4.824 4.824 ↑ 1.0 1 67

Index Only Scan using uix4pc_projectclienteffectiveend on projectclient pc_3 (cost=0.29..1.09 rows=1 width=16) (actual time=0.072..0.072 rows=1 loops=67)

  • Index Cond: (projectid = pj_5.id)
  • Heap Fetches: 21
  • Buffers: shared hit=144 read=12
26. 4.533 28.573 ↑ 1.0 10,869 1

Hash (cost=970.78..970.78 rows=10,875 width=52) (actual time=28.573..28.573 rows=10,869 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 734kB
  • Buffers: shared hit=334 read=86
27. 6.972 24.040 ↑ 1.0 10,869 1

Hash Join (cost=338.06..970.78 rows=10,875 width=52) (actual time=11.427..24.040 rows=10,869 loops=1)

  • Hash Cond: (pj_4.id = pc_2.projectid)
  • Buffers: shared hit=334 read=86
28. 5.675 5.675 ↑ 1.0 12,008 1

Seq Scan on project pj_4 (cost=0.00..478.94 rows=12,008 width=8) (actual time=0.005..5.675 rows=12,008 loops=1)

  • Filter: (clientbillingallocationmethod = 0)
  • Rows Removed by Filter: 67
  • Buffers: shared hit=328
29. 3.863 11.393 ↑ 1.0 10,936 1

Hash (cost=201.36..201.36 rows=10,936 width=48) (actual time=11.393..11.393 rows=10,936 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 695kB
  • Buffers: shared hit=6 read=86
30. 7.530 7.530 ↑ 1.0 10,936 1

Seq Scan on projectclient pc_2 (cost=0.00..201.36 rows=10,936 width=48) (actual time=0.484..7.530 rows=10,936 loops=1)

  • Buffers: shared hit=6 read=86
31. 0.424 2.730 ↓ 1.0 1,224 1

Hash (cost=77.11..77.11 rows=1,184 width=12) (actual time=2.730..2.730 rows=1,224 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 69kB
  • Buffers: shared hit=146 read=52
32. 2.306 2.306 ↓ 1.0 1,224 1

Index Scan using ixullocationid on userlocation userlocation5 (cost=0.28..77.11 rows=1,184 width=12) (actual time=0.007..2.306 rows=1,224 loops=1)

  • Index Cond: (locationid = ANY ('{94284e87-f912-40f3-9743-911d788ea567,7cca40bd-823e-48a2-9294-fb5fa9aa6ed2,0e9f0e1d-e638-4e4b-be93-a4f2d9098c8b,fb5c32fe-43d1-4791-89ee-3d4fd434eb88,53e82298-10fa-4024-9840-dbff9e7ffa30,5af54efa-00b8-432c-a937-f5f2acc07854}'::uuid[]))
  • Buffers: shared hit=146 read=52
33. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.82..6.67 rows=6 width=55) (never executed)

34. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.41..5.89 rows=1 width=63) (never executed)

  • Join Filter: ((dm_attendancetimeallocation_facts.entrydate >= pbrh.effectivedate) AND (dm_attendancetimeallocation_facts.entrydate <= pbrh.enddate))
35. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.98..1.10 rows=10 width=72) (never executed)

36. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.57..0.64 rows=1 width=92) (never executed)

37. 0.000 0.000 ↓ 0.0 0

Index Scan using dm_attendancetimeallocation_facts_pkey on dm_attendancetimeallocation_facts (cost=0.43..0.47 rows=1 width=60) (never executed)

  • Index Cond: (at_1.id = id)
38. 0.000 0.000 ↓ 0.0 0

Index Scan using billingrate_pkey on billingrate br (cost=0.14..0.16 rows=1 width=32) (never executed)

  • Index Cond: (id = dm_attendancetimeallocation_facts.billingrateid)
39. 0.000 0.000 ↓ 0.0 0

Index Scan using ixpbr2projectid on projectbillingrate pbr (cost=0.42..0.45 rows=1 width=40) (never executed)

  • Index Cond: ((projectid = dm_attendancetimeallocation_facts.projectid) AND (billingrateid = dm_attendancetimeallocation_facts.billingrateid))
  • Filter: (((billingrateid = $0) OR ((billingrateid <> $1) AND (userid IS NULL))) AND (((billingrateid = $0) AND (dm_attendancetimeallocation_facts.userid = userid)) OR ((billingrateid <> $1) AND (userid IS NULL))))
40. 0.000 0.000 ↓ 0.0 0

Index Scan using ixpbrhprojectbillingrateid on projectbillingratehistory pbrh (cost=0.42..0.46 rows=1 width=31) (never executed)

  • Index Cond: (projectbillingrateid = pbr.id)
41. 0.000 0.000 ↓ 0.0 0

Index Only Scan using ixtsuseridstartdateenddate on timesheet dmvts (cost=0.42..0.72 rows=6 width=12) (never executed)

  • Index Cond: ((userid = dm_attendancetimeallocation_facts.userid) AND (startdate <= dm_attendancetimeallocation_facts.entrydate) AND (enddate >= dm_attendancetimeallocation_facts.entrydate))
  • Heap Fetches: 0
42. 0.000 0.000 ↓ 0.0 0

Index Scan using userinfo_pkey on userinfo ui_1 (cost=0.28..2.30 rows=1 width=5) (never executed)

  • Index Cond: (id = ts.userid)
43. 0.000 0.000 ↓ 0.0 0

Index Only Scan using timesheet_pkey on timesheet ts_1 (cost=0.42..0.44 rows=1 width=16) (never executed)

  • Index Cond: (id = at_1.timesheetid)
  • Heap Fetches: 0
44. 0.000 0.000 ↓ 0.0 0

Index Scan using ixdtslsftimesheetid on dm_timesheetlist_facts tslist (cost=0.42..0.47 rows=1 width=20) (never executed)

  • Index Cond: (timesheetid = at_1.timesheetid)
45. 0.000 0.000 ↓ 0.0 0

Index Scan using userinfo_pkey on userinfo userinfo6 (cost=0.28..0.33 rows=1 width=4) (never executed)

  • Index Cond: (id = ui_1.id)
  • Filter: (employeetypeid = 3)
46. 0.000 0.000 ↓ 0.0 0

Index Scan using task_pkey on task task7 (cost=0.29..0.31 rows=1 width=4) (never executed)

  • Index Cond: (id = at_1.taskid)
  • Filter: (upper((name)::text) = 'OVERTIME'::text)
47.          

Initplan (forSort)

48. 0.000 0.000 ↓ 0.0 0

Seq Scan on systeminformation (cost=0.00..1.01 rows=1 width=4) (never executed)

49. 0.000 0.000 ↓ 0.0 0

Seq Scan on systeminformation systeminformation_1 (cost=0.00..1.01 rows=1 width=4) (never executed)

50. 0.000 9,129,575.816 ↓ 0.0 0 1

Nested Loop Left Join (cost=310.49..547.28 rows=1 width=837) (actual time=9,129,575.816..9,129,575.816 rows=0 loops=1)

  • Buffers: shared hit=86431441 read=136850
51. 0.001 9,129,575.816 ↓ 0.0 0 1

Nested Loop (cost=310.35..547.11 rows=1 width=837) (actual time=9,129,575.816..9,129,575.816 rows=0 loops=1)

  • Buffers: shared hit=86431441 read=136850
52. 0.001 9,129,575.815 ↓ 0.0 0 1

Nested Loop Left Join (cost=310.22..546.94 rows=1 width=841) (actual time=9,129,575.815..9,129,575.815 rows=0 loops=1)

  • Join Filter: ((((dmv_timesheetday_facts9.clientid11 IS NULL) AND (CASE WHEN (pj_1.clientbillingallocationmethod = 0) THEN pc.clientid ELSE at.userspecifiedclientid END IS NULL)) OR (CASE WHEN (pj_1.clientbillingallocationmethod = 0) THEN pc.clientid ELSE at.userspecifiedclientid END = dmv_timesheetday_facts9.clientid11)) AND (((dmv_timesheetday_facts9.effectivedate13 IS NULL) AND (CASE WHEN (pj_1.clientbillingallocationmethod = 0) THEN pc.effectivedate ELSE pc_1.effectivedate END IS NULL)) OR (CASE WHEN (pj_1.clientbillingallocationmethod = 0) THEN pc.effectivedate ELSE pc_1.effectivedate END = dmv_timesheetday_facts9.effectivedate13)))
  • Buffers: shared hit=86431441 read=136850
53. 0.005 9,129,575.814 ↓ 0.0 0 1

Hash Join (cost=308.08..542.17 rows=1 width=856) (actual time=9,129,575.814..9,129,575.814 rows=0 loops=1)

  • Hash Cond: (uprh.userid = dmv_timesheetday_facts9.userid2)
  • Buffers: shared hit=86431441 read=136850
54. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=293.69..507.54 rows=5,394 width=18) (never executed)

  • Hash Cond: (exchangerate_1.fixedcurrencyid = uprh.currencyid)
55. 0.000 0.000 ↓ 0.0 0

Index Scan using uix3er_currencyideffectivedate on exchangerate exchangerate_1 (cost=0.29..86.34 rows=22 width=4) (never executed)

  • Index Cond: ((variablecurrencyid = $15) AND (('now'::cstring)::date >= effectivedate))
  • Filter: (('now'::cstring)::date <= enddate)
56. 0.000 0.000 ↓ 0.0 0

Hash (cost=227.95..227.95 rows=5,236 width=14) (never executed)

57. 0.000 0.000 ↓ 0.0 0

Index Scan using ix3uprh_userideffdateenddate on userpayrollratehistory uprh (cost=0.29..227.95 rows=5,236 width=14) (never executed)

  • Index Cond: ((('now'::cstring)::date >= effectivedate) AND (('now'::cstring)::date <= enddate))
58. 0.000 9,129,575.809 ↓ 0.0 0 1

Hash (cost=14.38..14.38 rows=1 width=850) (actual time=9,129,575.809..9,129,575.809 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=86431441 read=136850
59. 0.005 9,129,575.809 ↓ 0.0 0 1

Hash Right Join (cost=13.07..14.38 rows=1 width=850) (actual time=9,129,575.809..9,129,575.809 rows=0 loops=1)

  • Hash Cond: (currencyinfo21.id = dmv_timesheetday_facts9.currencyid5)
  • Buffers: shared hit=86431441 read=136850
60. 0.000 0.000 ↓ 0.0 0

Seq Scan on currencyinfo currencyinfo21 (cost=0.00..1.22 rows=22 width=8) (never executed)

61. 0.001 9,129,575.804 ↓ 0.0 0 1

Hash (cost=13.05..13.05 rows=1 width=846) (actual time=9,129,575.804..9,129,575.804 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=86431441 read=136850
62. 0.000 9,129,575.803 ↓ 0.0 0 1

Nested Loop Left Join (cost=3.11..13.05 rows=1 width=846) (actual time=9,129,575.803..9,129,575.803 rows=0 loops=1)

  • Buffers: shared hit=86431441 read=136850
63. 0.001 9,129,575.803 ↓ 0.0 0 1

Nested Loop Left Join (cost=2.83..12.72 rows=1 width=834) (actual time=9,129,575.803..9,129,575.803 rows=0 loops=1)

  • Buffers: shared hit=86431441 read=136850
64. 0.002 9,129,575.802 ↓ 0.0 0 1

Nested Loop Left Join (cost=2.55..12.39 rows=1 width=830) (actual time=9,129,575.802..9,129,575.802 rows=0 loops=1)

  • Buffers: shared hit=86431441 read=136850
65. 0.000 9,129,575.800 ↓ 0.0 0 1

Nested Loop (cost=2.27..10.09 rows=1 width=790) (actual time=9,129,575.800..9,129,575.800 rows=0 loops=1)

  • Buffers: shared hit=86431441 read=136850
66. 0.001 9,129,575.800 ↓ 0.0 0 1

Nested Loop (cost=2.13..9.92 rows=1 width=757) (actual time=9,129,575.800..9,129,575.800 rows=0 loops=1)

  • Join Filter: (dmv_timesheetday_facts9.userid2 = du.userid)
  • Buffers: shared hit=86431441 read=136850
67. 0.000 9,129,575.799 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.85..9.59 rows=1 width=749) (actual time=9,129,575.799..9,129,575.799 rows=0 loops=1)

  • Buffers: shared hit=86431441 read=136850
68. 0.002 9,129,575.799 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.27..6.46 rows=1 width=214) (actual time=9,129,575.799..9,129,575.799 rows=0 loops=1)

  • Buffers: shared hit=86431441 read=136850
69. 0.000 9,129,575.797 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.98..4.15 rows=1 width=187) (actual time=9,129,575.797..9,129,575.797 rows=0 loops=1)

  • Buffers: shared hit=86431441 read=136850
70. 0.001 9,129,575.797 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.85..3.98 rows=1 width=192) (actual time=9,129,575.797..9,129,575.797 rows=0 loops=1)

  • Buffers: shared hit=86431441 read=136850
71. 0.000 9,129,575.796 ↓ 0.0 0 1

Nested Loop (cost=0.56..2.67 rows=1 width=180) (actual time=9,129,575.796..9,129,575.796 rows=0 loops=1)

  • Join Filter: (dmv_timesheetday_facts9.userid2 = ui.id)
  • Buffers: shared hit=86431441 read=136850
72. 0.002 9,129,575.796 ↓ 0.0 0 1

Nested Loop (cost=0.28..2.33 rows=1 width=151) (actual time=9,129,575.796..9,129,575.796 rows=0 loops=1)

  • Buffers: shared hit=86431441 read=136850
73. 9,129,575.794 9,129,575.794 ↓ 0.0 0 1

CTE Scan on dmv_timesheetday_facts0cte dmv_timesheetday_facts9 (cost=0.00..0.02 rows=1 width=133) (actual time=9,129,575.794..9,129,575.794 rows=0 loops=1)

  • Filter: (timesheetstatus7 = 2)
  • Buffers: shared hit=86431441 read=136850
74. 0.000 0.000 ↓ 0.0 0

Index Scan using login_pkey on login (cost=0.28..2.30 rows=1 width=18) (never executed)

  • Index Cond: (userid = dmv_timesheetday_facts9.userid2)
75. 0.000 0.000 ↓ 0.0 0

Index Scan using userinfo_pkey on userinfo ui (cost=0.28..0.32 rows=1 width=29) (never executed)

  • Index Cond: (id = login.userid)
76. 0.000 0.000 ↓ 0.0 0

Index Only Scan using ix4ul_userlocationstartend on userlocation userlocation22 (cost=0.28..1.30 rows=1 width=28) (never executed)

  • Index Cond: ((userid = dmv_timesheetday_facts9.userid8) AND (startdate <= dmv_timesheetday_facts9.entrydate3) AND (enddate >= dmv_timesheetday_facts9.entrydate3))
  • Heap Fetches: 0
77. 0.000 0.000 ↓ 0.0 0

Index Scan using location_pkey on location location11 (cost=0.14..0.16 rows=1 width=27) (never executed)

  • Index Cond: (userlocation22.locationid = id)
78. 0.000 0.000 ↓ 0.0 0

Index Scan using project_pkey on project pj (cost=0.29..2.30 rows=1 width=31) (never executed)

  • Index Cond: (dmv_timesheetday_facts9.projectid9 = id)
79. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=0.58..3.12 rows=1 width=539) (never executed)

80. 0.000 0.000 ↓ 0.0 0

Index Scan using task_pkey on task tk (cost=0.29..2.31 rows=1 width=543) (never executed)

  • Index Cond: (dmv_timesheetday_facts9.taskid10 = id)
81. 0.000 0.000 ↓ 0.0 0

Index Scan using uix3er_currencyideffectivedate on exchangerate (cost=0.29..0.80 rows=1 width=4) (never executed)

  • Index Cond: ((variablecurrencyid = $14) AND (tk.estimatedcostcurrencyid = fixedcurrencyid) AND (('now'::cstring)::date >= effectivedate))
  • Filter: (('now'::cstring)::date <= enddate)
82. 0.000 0.000 ↓ 0.0 0

Index Scan using ixduuserid on departmentusers du (cost=0.28..0.31 rows=1 width=8) (never executed)

  • Index Cond: (userid = ui.id)
  • Filter: (departmentid = ANY ('{41,69,37,26}'::integer[]))
83. 0.000 0.000 ↓ 0.0 0

Index Scan using departments_pkey on departments dep (cost=0.14..0.16 rows=1 width=41) (never executed)

  • Index Cond: (id = du.departmentid)
84. 0.000 0.000 ↓ 0.0 0

Index Scan using clients_pkey on clients cl (cost=0.28..2.29 rows=1 width=40) (never executed)

  • Index Cond: (dmv_timesheetday_facts9.clientid11 = id)
85. 0.000 0.000 ↓ 0.0 0

Index Scan using ix3uh_usersuperstart on userhierarchy userhierarchy23 (cost=0.28..0.32 rows=1 width=8) (never executed)

  • Index Cond: ((ui.id = userid) AND ('2019-03-12'::date >= startdate))
  • Filter: ('2019-03-12'::date <= enddate)
86. 0.000 0.000 ↓ 0.0 0

Index Scan using userinfo_pkey on userinfo userinfo16 (cost=0.28..0.32 rows=1 width=20) (never executed)

  • Index Cond: (userhierarchy23.supervisorid = id)
87. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=2.14..4.74 rows=1 width=45) (never executed)

88. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=1.57..3.74 rows=1 width=49) (never executed)

89. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=1.28..3.40 rows=1 width=44) (never executed)

90. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=1.00..3.08 rows=1 width=40) (never executed)

91. 0.000 0.000 ↓ 0.0 0

Index Scan using dm_attendancetimeallocation_facts_pkey on dm_attendancetimeallocation_facts at (cost=0.43..2.45 rows=1 width=36) (never executed)

  • Index Cond: (id = dmv_timesheetday_facts9.timeallocationid12)
  • Filter: ((timeoffcodeid IS NULL) AND (breaktypeid IS NULL))
92. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.57..0.63 rows=1 width=16) (never executed)

  • Join Filter: ((at.entrydate >= pc.effectivedate) AND (at.entrydate <= pc.enddate))
93. 0.000 0.000 ↓ 0.0 0

Index Scan using project_pkey on project pj_2 (cost=0.29..0.31 rows=1 width=4) (never executed)

  • Index Cond: (id = at.projectid)
  • Filter: (clientbillingallocationmethod = 0)
94. 0.000 0.000 ↓ 0.0 0

Index Only Scan using uix4pc_projectclienteffectiveend on projectclient pc (cost=0.29..0.31 rows=1 width=16) (never executed)

  • Index Cond: (projectid = pj_2.id)
  • Heap Fetches: 0
95. 0.000 0.000 ↓ 0.0 0

Index Scan using project_pkey on project pj_1 (cost=0.29..0.30 rows=1 width=8) (never executed)

  • Index Cond: (at.projectid = id)
96. 0.000 0.000 ↓ 0.0 0

Index Scan using ixuprrhuserid on userprojectroleratehistory uprrh (cost=0.29..0.33 rows=1 width=21) (never executed)

  • Index Cond: (at.userid = userid)
  • Filter: ((at.entrydate >= effectivedate) AND (at.entrydate <= enddate))
97. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.57..1.00 rows=1 width=16) (never executed)

  • Join Filter: (pj_3.id = pc_1.projectid)
98. 0.000 0.000 ↓ 0.0 0

Index Scan using project_pkey on project pj_3 (cost=0.29..0.31 rows=1 width=4) (never executed)

  • Index Cond: (id = at.projectid)
  • Filter: (clientbillingallocationmethod = 1)
99. 0.000 0.000 ↓ 0.0 0

Index Scan using ixpcclientid on projectclient pc_1 (cost=0.29..0.67 rows=2 width=16) (never executed)

  • Index Cond: (clientid = at.userspecifiedclientid)
  • Filter: ((at.entrydate >= effectivedate) AND (at.entrydate <= enddate))
100. 0.000 0.000 ↓ 0.0 0

Index Scan using currencyinfo_pkey on currencyinfo currencyinfo17 (cost=0.14..0.16 rows=1 width=8) (never executed)

  • Index Cond: (id = uprh.currencyid)
101. 0.000 0.000 ↓ 0.0 0

Index Scan using currencyinfo_pkey on currencyinfo currencyinfo19 (cost=0.14..0.16 rows=1 width=8) (never executed)

  • Index Cond: (uprrh.currencyid = id)