explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BPtI

Settings
# exclusive inclusive rows x rows loops node
1. 0.032 10,064,906.070 ↓ 0.0 0 1

Sort (cost=1,097,954.48..1,097,954.48 rows=1 width=837) (actual time=10,064,906.070..10,064,906.070 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=87799298 read=53109 written=5229
2.          

CTE dmv_timesheetday_facts0cte

3. 0.002 10,064,906.010 ↓ 0.0 0 1

HashAggregate (cost=1,097,297.64..1,097,297.66 rows=1 width=120) (actual time=10,064,906.010..10,064,906.010 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=87799298 read=53109 written=5229
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 10,064,906.008 ↓ 0.0 0 1

Nested Loop Semi Join (cost=158,408.84..1,097,295.57 rows=1 width=120) (actual time=10,064,906.008..10,064,906.008 rows=0 loops=1)

  • Buffers: shared hit=87799298 read=53109 written=5229
8. 0.002 10,064,906.008 ↓ 0.0 0 1

Nested Loop Semi Join (cost=158,408.55..1,097,295.25 rows=1 width=120) (actual time=10,064,906.008..10,064,906.008 rows=0 loops=1)

  • Join Filter: (ts.userid = userinfo6.id)
  • Buffers: shared hit=87799298 read=53109 written=5229
9. 0.000 10,064,906.006 ↓ 0.0 0 1

Nested Loop (cost=158,408.27..1,097,294.88 rows=1 width=124) (actual time=10,064,906.006..10,064,906.006 rows=0 loops=1)

  • Join Filter: (ts.id = tslist.timesheetid)
  • Buffers: shared hit=87799298 read=53109 written=5229
10. 0.001 10,064,906.006 ↓ 0.0 0 1

Nested Loop (cost=158,407.84..1,097,294.32 rows=1 width=152) (actual time=10,064,906.006..10,064,906.006 rows=0 loops=1)

  • Join Filter: (ts.id = ts_1.id)
  • Buffers: shared hit=87799298 read=53109 written=5229
11. 0.000 10,064,906.005 ↓ 0.0 0 1

Nested Loop (cost=158,407.42..1,097,293.86 rows=1 width=136) (actual time=10,064,906.005..10,064,906.005 rows=0 loops=1)

  • Buffers: shared hit=87799298 read=53109 written=5229
12. 0.001 10,064,906.005 ↓ 0.0 0 1

Nested Loop Left Join (cost=158,407.14..1,097,291.55 rows=1 width=131) (actual time=10,064,906.005..10,064,906.005 rows=0 loops=1)

  • Buffers: shared hit=87799298 read=53109 written=5229
13. 119,605.215 10,064,906.004 ↓ 0.0 0 1

Nested Loop (cost=158,405.32..1,097,284.82 rows=1 width=92) (actual time=10,064,906.004..10,064,906.004 rows=0 loops=1)

  • Join Filter: (at_1.timesheetid = ts.id)
  • Rows Removed by Join Filter: 258257153
  • Buffers: shared hit=87799298 read=53109 written=5229
14. 604.027 604.027 ↓ 721.0 721 1

Index Scan using uix2tsuseridstartdate on timesheet ts (cost=0.42..2,908.75 rows=1 width=20) (actual time=0.018..604.027 rows=721 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: 987
  • Buffers: shared hit=1084 read=1045 written=22
15. 451,011.795 9,944,696.762 ↓ 11.1 358,193 721

Hash Join (cost=158,404.90..1,093,972.40 rows=32,294 width=72) (actual time=25.196..13,792.922 rows=358,193 loops=721)

  • Hash Cond: (at_1.userid = userlocation5.userid)
  • Join Filter: ((at_1.entrydate >= userlocation5.startdate) AND (at_1.entrydate <= userlocation5.enddate))
  • Buffers: shared hit=87798214 read=52064 written=5207
16. 3,916,596.540 9,493,665.118 ↓ 1.0 1,343,088 721

Hash Left Join (cost=158,292.54..1,068,632.54 rows=1,284,173 width=137) (actual time=25.164..13,167.358 rows=1,343,088 loops=721)

  • 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=87797730 read=51993 written=5207
17. 506,374.584 5,577,040.266 ↓ 1.0 1,343,088 721

Hash Left Join (cost=156,333.59..378,543.10 rows=1,284,173 width=93) (actual time=25.087..7,735.146 rows=1,343,088 loops=721)

  • 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=87796805 read=51993 written=5207
18. 901,224.681 5,070,659.615 ↓ 1.0 1,343,088 721

Hash Right Join (cost=155,445.94..368,024.15 rows=1,284,173 width=89) (actual time=25.076..7,032.815 rows=1,343,088 loops=721)

  • Hash Cond: (isbillable.factid = at_1.id)
  • Buffers: shared hit=87795978 read=51993 written=5207
19. 4,166,888.999 4,166,888.999 ↓ 36.2 1,090,542 721

Seq Scan on dm_attendancetimeallocation_metadata isbillable (cost=0.00..212,234.85 rows=30,150 width=17) (actual time=21.528..5,779.319 rows=1,090,542 loops=721)

  • Filter: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:IS-BILLABLE'::text)
  • Rows Removed by Filter: 4128761
  • Buffers: shared hit=87795967 read=26095 written=4612
20. 688.156 2,545.935 ↓ 1.0 1,343,088 1

Hash (cost=139,393.78..139,393.78 rows=1,284,173 width=88) (actual time=2,545.935..2,545.935 rows=1,343,088 loops=1)

  • Buckets: 2097152 Batches: 1 Memory Usage: 152739kB
  • Buffers: shared hit=11 read=25898 written=595
21. 1,857.779 1,857.779 ↓ 1.0 1,343,088 1

Seq Scan on dm_attendancetimeallocation_facts at_1 (cost=0.00..139,393.78 rows=1,284,173 width=88) (actual time=0.434..1,857.779 rows=1,343,088 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=11 read=25898 written=595
22. 0.030 6.067 ↑ 1.0 67 1

Hash (cost=886.64..886.64 rows=67 width=16) (actual time=6.067..6.067 rows=67 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
  • Buffers: shared hit=827
23. 0.086 6.037 ↑ 1.0 67 1

Nested Loop (cost=0.29..886.64 rows=67 width=16) (actual time=0.045..6.037 rows=67 loops=1)

  • Buffers: shared hit=827
24. 5.750 5.750 ↑ 1.0 67 1

Seq Scan on project pj_5 (cost=0.00..779.54 rows=67 width=4) (actual time=0.020..5.750 rows=67 loops=1)

  • Filter: (clientbillingallocationmethod = 1)
  • Rows Removed by Filter: 12488
  • Buffers: shared hit=624
25. 0.201 0.201 ↑ 1.0 1 67

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

  • Index Cond: (projectid = pj_5.id)
  • Heap Fetches: 67
  • Buffers: shared hit=203
26. 4.138 28.312 ↑ 1.1 11,350 1

Hash (cost=1,804.25..1,804.25 rows=12,376 width=52) (actual time=28.312..28.312 rows=11,350 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 760kB
  • Buffers: shared hit=925
27. 6.656 24.174 ↑ 1.1 11,350 1

Hash Join (cost=934.24..1,804.25 rows=12,376 width=52) (actual time=10.684..24.174 rows=11,350 loops=1)

  • Hash Cond: (pc_2.projectid = pj_4.id)
  • Buffers: shared hit=925
28. 6.927 6.927 ↑ 2.8 11,417 1

Seq Scan on projectclient pc_2 (cost=0.00..624.82 rows=32,382 width=48) (actual time=0.013..6.927 rows=11,417 loops=1)

  • Buffers: shared hit=301
29. 3.591 10.591 ↓ 1.0 12,488 1

Hash (cost=779.54..779.54 rows=12,376 width=8) (actual time=10.591..10.591 rows=12,488 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 616kB
  • Buffers: shared hit=624
30. 7.000 7.000 ↓ 1.0 12,488 1

Seq Scan on project pj_4 (cost=0.00..779.54 rows=12,376 width=8) (actual time=0.008..7.000 rows=12,488 loops=1)

  • Filter: (clientbillingallocationmethod = 0)
  • Rows Removed by Filter: 67
  • Buffers: shared hit=624
31. 0.445 19.849 ↓ 1.0 1,258 1

Hash (cost=97.33..97.33 rows=1,202 width=12) (actual time=19.849..19.849 rows=1,258 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 71kB
  • Buffers: shared hit=484 read=71
32. 19.404 19.404 ↓ 1.0 1,258 1

Index Scan using ixullocationid on userlocation userlocation5 (cost=0.28..97.33 rows=1,202 width=12) (actual time=0.011..19.404 rows=1,258 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=484 read=71
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.91 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.47 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.71 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 dm_timesheetlist_facts_pkey on dm_timesheetlist_facts tslist (cost=0.42..0.55 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.35 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.001 10,064,906.038 ↓ 0.0 0 1

Nested Loop Left Join (cost=368.29..654.79 rows=1 width=837) (actual time=10,064,906.038..10,064,906.038 rows=0 loops=1)

  • Buffers: shared hit=87799298 read=53109 written=5229
51. 0.000 10,064,906.037 ↓ 0.0 0 1

Nested Loop (cost=368.16..654.62 rows=1 width=837) (actual time=10,064,906.037..10,064,906.037 rows=0 loops=1)

  • Buffers: shared hit=87799298 read=53109 written=5229
52. 0.002 10,064,906.037 ↓ 0.0 0 1

Nested Loop Left Join (cost=368.02..654.46 rows=1 width=841) (actual time=10,064,906.037..10,064,906.037 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=87799298 read=53109 written=5229
53. 0.008 10,064,906.035 ↓ 0.0 0 1

Hash Join (cost=365.84..648.88 rows=1 width=856) (actual time=10,064,906.035..10,064,906.035 rows=0 loops=1)

  • Hash Cond: (uprh.userid = dmv_timesheetday_facts9.userid2)
  • Buffers: shared hit=87799298 read=53109 written=5229
54. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=351.33..611.04 rows=6,217 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..113.02 rows=25 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=284.56..284.56 rows=5,318 width=14) (never executed)

57. 0.000 0.000 ↓ 0.0 0

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

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

Hash (cost=14.50..14.50 rows=1 width=850) (actual time=10,064,906.027..10,064,906.027 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=87799298 read=53109 written=5229
59. 0.006 10,064,906.027 ↓ 0.0 0 1

Hash Right Join (cost=13.19..14.50 rows=1 width=850) (actual time=10,064,906.027..10,064,906.027 rows=0 loops=1)

  • Hash Cond: (currencyinfo21.id = dmv_timesheetday_facts9.currencyid5)
  • Buffers: shared hit=87799298 read=53109 written=5229
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.000 10,064,906.021 ↓ 0.0 0 1

Hash (cost=13.18..13.18 rows=1 width=846) (actual time=10,064,906.021..10,064,906.021 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=87799298 read=53109 written=5229
62. 0.002 10,064,906.021 ↓ 0.0 0 1

Nested Loop Left Join (cost=3.11..13.18 rows=1 width=846) (actual time=10,064,906.021..10,064,906.021 rows=0 loops=1)

  • Buffers: shared hit=87799298 read=53109 written=5229
63. 0.000 10,064,906.019 ↓ 0.0 0 1

Nested Loop Left Join (cost=2.83..12.82 rows=1 width=834) (actual time=10,064,906.019..10,064,906.019 rows=0 loops=1)

  • Buffers: shared hit=87799298 read=53109 written=5229
64. 0.001 10,064,906.019 ↓ 0.0 0 1

Nested Loop Left Join (cost=2.55..12.49 rows=1 width=830) (actual time=10,064,906.019..10,064,906.019 rows=0 loops=1)

  • Buffers: shared hit=87799298 read=53109 written=5229
65. 0.000 10,064,906.018 ↓ 0.0 0 1

Nested Loop (cost=2.27..10.18 rows=1 width=790) (actual time=10,064,906.018..10,064,906.018 rows=0 loops=1)

  • Buffers: shared hit=87799298 read=53109 written=5229
66. 0.001 10,064,906.018 ↓ 0.0 0 1

Nested Loop (cost=2.13..10.01 rows=1 width=757) (actual time=10,064,906.018..10,064,906.018 rows=0 loops=1)

  • Join Filter: (dmv_timesheetday_facts9.userid2 = du.userid)
  • Buffers: shared hit=87799298 read=53109 written=5229
67. 0.001 10,064,906.017 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.85..9.69 rows=1 width=749) (actual time=10,064,906.017..10,064,906.017 rows=0 loops=1)

  • Buffers: shared hit=87799298 read=53109 written=5229
68. 0.001 10,064,906.016 ↓ 0.0 0 1

Nested Loop Left Join (cost=1.27..6.47 rows=1 width=214) (actual time=10,064,906.016..10,064,906.016 rows=0 loops=1)

  • Buffers: shared hit=87799298 read=53109 written=5229
69. 0.000 10,064,906.015 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.98..4.15 rows=1 width=187) (actual time=10,064,906.015..10,064,906.015 rows=0 loops=1)

  • Buffers: shared hit=87799298 read=53109 written=5229
70. 0.001 10,064,906.015 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.85..3.99 rows=1 width=192) (actual time=10,064,906.015..10,064,906.015 rows=0 loops=1)

  • Buffers: shared hit=87799298 read=53109 written=5229
71. 0.000 10,064,906.014 ↓ 0.0 0 1

Nested Loop (cost=0.56..2.67 rows=1 width=180) (actual time=10,064,906.014..10,064,906.014 rows=0 loops=1)

  • Join Filter: (dmv_timesheetday_facts9.userid2 = login.userid)
  • Buffers: shared hit=87799298 read=53109 written=5229
72. 0.001 10,064,906.014 ↓ 0.0 0 1

Nested Loop (cost=0.28..2.33 rows=1 width=162) (actual time=10,064,906.014..10,064,906.014 rows=0 loops=1)

  • Buffers: shared hit=87799298 read=53109 written=5229
73. 10,064,906.013 10,064,906.013 ↓ 0.0 0 1

CTE Scan on dmv_timesheetday_facts0cte dmv_timesheetday_facts9 (cost=0.00..0.02 rows=1 width=133) (actual time=10,064,906.013..10,064,906.013 rows=0 loops=1)

  • Filter: (timesheetstatus7 = 2)
  • Buffers: shared hit=87799298 read=53109 written=5229
74. 0.000 0.000 ↓ 0.0 0

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

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

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

  • Index Cond: (userid = ui.id)
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.21 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.89 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 = login.userid)
  • 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.35 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.18..5.55 rows=1 width=45) (never executed)

88. 0.000 0.000 ↓ 0.0 0

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

89. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=1.29..3.42 rows=1 width=44) (never executed)

90. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=1.00..3.10 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.65 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.33 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

Hash Join (cost=0.61..1.79 rows=1 width=16) (never executed)

  • Hash Cond: (pc_1.projectid = pj_3.id)
98. 0.000 0.000 ↓ 0.0 0

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

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

Hash (cost=0.31..0.31 rows=1 width=4) (never executed)

100. 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)
101. 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)
102. 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)
Planning time : 96.537 ms
Execution time : 10,064,924.946 ms