explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iKQG : Pike_02Oct2019

Settings
# exclusive inclusive rows x rows loops node
1. 0.394 26,331.237 ↓ 134.0 134 1

Sort (cost=2,096,712.16..2,096,712.17 rows=1 width=2,553) (actual time=26,331.231..26,331.237 rows=134 loops=1)

  • Sort Key: ((ui.lastname)::character varying(50)) COLLATE "en_US", ((ui.firstname)::character varying(50)) COLLATE "en_US", ui.duplicatename, ((login.loginname)::character varying(255)) COLLATE "en_US", ((pj.name)::character varying(255)) COLLATE "en_US", ((pj.code)::character varying(50)) COLLATE "en_US", ((at.comments)::text) COLLATE "en_US", ((activities4.name)::character varying(50)) COLLATE "en_US", ((timeoffcode5.name)::character varying(255)) COLLATE "en_US", (((CASE WHEN (at.timeoffcodeid IS NULL) THEN at.timeentryinfo1 ELSE NULL::character varying END))::character varying) COLLATE "en_US", ((tk.code)::character varying(50)) COLLATE "en_US", tslist.timesheetstatus, ((tah.approvalcomments)::text) COLLATE "en_US", ((br.name)::character varying(50)) COLLATE "en_US
  • Sort Method: quicksort Memory: 94kB
  • Buffers: shared hit=933908
2.          

Initplan (forSort)

3. 0.007 0.007 ↑ 1.0 1 1

Seq Scan on systeminformation (cost=0.00..1.01 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=1)

  • Buffers: shared hit=1
4. 0.005 0.005 ↑ 1.0 1 1

Seq Scan on projectsysteminformation (cost=0.00..1.01 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=1)

  • Buffers: shared hit=1
5. 0.001 0.001 ↑ 1.0 1 1

Seq Scan on projectsysteminformation projectsysteminformation_1 (cost=0.00..1.01 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=1)

  • Buffers: shared hit=1
6. 0.131 26,330.830 ↓ 134.0 134 1

Nested Loop Left Join (cost=7,577.74..2,096,709.12 rows=1 width=2,553) (actual time=22,739.586..26,330.830 rows=134 loops=1)

  • Buffers: shared hit=933908
7. 2.992 26,328.421 ↓ 134.0 134 1

Hash Semi Join (cost=7,575.76..2,096,706.42 rows=1 width=1,102) (actual time=22,739.484..26,328.421 rows=134 loops=1)

  • Hash Cond: (ts.userid = userlocation13.userid)
  • Buffers: shared hit=931302
8. 11.490 26,325.399 ↓ 237.5 21,372 1

Nested Loop Left Join (cost=7,561.00..2,096,691.41 rows=90 width=1,110) (actual time=12,357.799..26,325.399 rows=21,372 loops=1)

  • Buffers: shared hit=931297
9. 9.244 25,416.285 ↓ 237.5 21,372 1

Nested Loop Left Join (cost=7,560.43..2,096,434.71 rows=90 width=1,058) (actual time=12,357.746..25,416.285 rows=21,372 loops=1)

  • Buffers: shared hit=795799
10. 16.345 24,488.045 ↓ 237.5 21,372 1

Nested Loop (cost=7,559.87..2,096,178.01 rows=90 width=990) (actual time=12,357.675..24,488.045 rows=21,372 loops=1)

  • Join Filter: (ts.id = tslist.timesheetid)
  • Buffers: shared hit=660301
11. 8.078 24,407.584 ↓ 237.5 21,372 1

Nested Loop Left Join (cost=7,559.45..2,096,135.87 rows=90 width=1,018) (actual time=12,357.659..24,407.584 rows=21,372 loops=1)

  • Buffers: shared hit=574813
12. 1.992 24,292.646 ↓ 237.5 21,372 1

Nested Loop Left Join (cost=7,558.88..2,096,078.39 rows=90 width=1,006) (actual time=12,357.654..24,292.646 rows=21,372 loops=1)

  • Buffers: shared hit=471769
13. 17.024 24,151.230 ↓ 193.6 17,428 1

Nested Loop (cost=7,558.03..2,095,942.15 rows=90 width=952) (actual time=12,357.621..24,151.230 rows=17,428 loops=1)

  • Join Filter: (ts.id = ts_1.id)
  • Buffers: shared hit=286921
14. 22.753 24,099.350 ↓ 193.6 17,428 1

Nested Loop Left Join (cost=7,557.61..2,095,901.28 rows=90 width=936) (actual time=12,357.605..24,099.350 rows=17,428 loops=1)

  • Buffers: shared hit=217209
15. 8.403 24,059.169 ↓ 193.6 17,428 1

Nested Loop (cost=7,557.32..2,095,874.05 rows=90 width=882) (actual time=12,357.593..24,059.169 rows=17,428 loops=1)

  • Join Filter: (ts.userid = login.userid)
  • Buffers: shared hit=168990
16. 6,763.002 24,015.910 ↓ 193.6 17,428 1

Nested Loop (cost=7,557.04..2,095,843.60 rows=90 width=864) (actual time=12,357.572..24,015.910 rows=17,428 loops=1)

  • Join Filter: (ts.userid = ui.id)
  • Rows Removed by Join Filter: 100890692
  • Buffers: shared hit=116706
17. 10.288 10.288 ↓ 1.0 5,790 1

Index Scan using userinfo_pkey on userinfo ui (cost=0.28..226.09 rows=5,787 width=19) (actual time=0.027..10.288 rows=5,790 loops=1)

  • Buffers: shared hit=3817
18. 4,917.048 17,242.620 ↓ 193.6 17,428 5,790

Materialize (cost=7,556.76..2,087,805.29 rows=90 width=845) (actual time=0.004..2.978 rows=17,428 loops=5,790)

  • Buffers: shared hit=112889
19. 3.887 12,325.572 ↓ 193.6 17,428 1

Hash Left Join (cost=7,556.76..2,087,804.84 rows=90 width=845) (actual time=21.552..12,325.572 rows=17,428 loops=1)

  • Hash Cond: (at.timeoffcodeid = timeoffcode5.id)
  • Buffers: shared hit=112889
20. 4.054 12,321.677 ↓ 193.6 17,428 1

Hash Left Join (cost=7,555.53..2,087,803.38 rows=90 width=333) (actual time=21.539..12,321.677 rows=17,428 loops=1)

  • Hash Cond: (at.activityid = activities4.id)
  • Buffers: shared hit=112888
21. 445.310 12,317.610 ↓ 193.6 17,428 1

Hash Join (cost=7,553.88..2,087,801.48 rows=90 width=219) (actual time=21.519..12,317.610 rows=17,428 loops=1)

  • Hash Cond: (at.timesheetid = ts.id)
  • Buffers: shared hit=112887
22. 10,382.650 11,864.870 ↓ 1.0 3,458,787 1

Hash Left Join (cost=1,276.64..2,037,869.38 rows=3,457,803 width=619) (actual time=12.717..11,864.870 rows=3,458,787 loops=1)

  • Hash Cond: (at.projectid = pj_1.id)
  • Join Filter: ((at.entrydate >= pc.effectivedate) AND (at.entrydate <= pc.enddate))
  • Buffers: shared hit=110196
23. 581.952 1,472.094 ↓ 1.0 3,458,787 1

Hash Left Join (cost=443.73..170,406.30 rows=3,457,803 width=132) (actual time=2.489..1,472.094 rows=3,458,787 loops=1)

  • Hash Cond: ((at.projectid = pj_2.id) AND (at.userspecifiedclientid = pc_1.clientid))
  • Join Filter: ((at.entrydate >= pc_1.effectivedate) AND (at.entrydate <= pc_1.enddate))
  • Buffers: shared hit=109823
24. 887.661 887.661 ↓ 1.0 3,458,787 1

Seq Scan on dm_attendancetimeallocation_facts at (cost=0.00..144,029.03 rows=3,457,803 width=132) (actual time=0.004..887.661 rows=3,458,787 loops=1)

  • Buffers: shared hit=109451
25. 0.010 2.481 ↓ 1.0 34 1

Hash (cost=443.24..443.24 rows=33 width=16) (actual time=2.481..2.481 rows=34 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=372
26. 0.012 2.471 ↓ 1.0 34 1

Nested Loop (cost=0.29..443.24 rows=33 width=16) (actual time=0.018..2.471 rows=34 loops=1)

  • Buffers: shared hit=372
27. 2.391 2.391 ↑ 1.0 34 1

Seq Scan on project pj_2 (cost=0.00..408.61 rows=34 width=4) (actual time=0.008..2.391 rows=34 loops=1)

  • Filter: (clientbillingallocationmethod = 1)
  • Rows Removed by Filter: 8819
  • Buffers: shared hit=298
28. 0.068 0.068 ↑ 1.0 1 34

Index Only Scan using uix4pc_projectclienteffectiveend on projectclient pc_1 (cost=0.29..1.01 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=34)

  • Index Cond: (projectid = pj_2.id)
  • Heap Fetches: 5
  • Buffers: shared hit=74
29. 1.592 10.126 ↓ 1.0 8,640 1

Hash (cost=724.94..724.94 rows=8,637 width=52) (actual time=10.126..10.126 rows=8,640 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 609kB
  • Buffers: shared hit=373
30. 1.831 8.534 ↓ 1.0 8,640 1

Hash Join (cost=518.80..724.94 rows=8,637 width=52) (actual time=4.030..8.534 rows=8,640 loops=1)

  • Hash Cond: (pc.projectid = pj_1.id)
  • Buffers: shared hit=373
31. 2.750 2.750 ↓ 1.0 8,674 1

Seq Scan on projectclient pc (cost=0.00..183.38 rows=8,670 width=48) (actual time=0.010..2.750 rows=8,674 loops=1)

  • Buffers: shared hit=75
32. 1.201 3.953 ↓ 1.0 8,819 1

Hash (cost=408.61..408.61 rows=8,815 width=8) (actual time=3.952..3.953 rows=8,819 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 473kB
  • Buffers: shared hit=298
33. 2.752 2.752 ↓ 1.0 8,819 1

Seq Scan on project pj_1 (cost=0.00..408.61 rows=8,815 width=8) (actual time=0.003..2.752 rows=8,819 loops=1)

  • Filter: (clientbillingallocationmethod = 0)
  • Rows Removed by Filter: 34
  • Buffers: shared hit=298
34. 0.324 7.430 ↓ 194.7 1,947 1

Hash (cost=6,277.12..6,277.12 rows=10 width=20) (actual time=7.430..7.430 rows=1,947 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 115kB
  • Buffers: shared hit=2691
35. 7.106 7.106 ↓ 194.7 1,947 1

Index Scan using uix2tsuseridstartdate on timesheet ts (cost=0.42..6,277.12 rows=10 width=20) (actual time=0.016..7.106 rows=1,947 loops=1)

  • Index Cond: ((startdate >= '2019-09-23'::date) AND (startdate <= '2019-09-29'::date))
  • Filter: ((enddate >= '2019-09-23'::date) AND (enddate <= '2019-09-29'::date))
  • Buffers: shared hit=2691
36. 0.007 0.013 ↑ 1.0 29 1

Hash (cost=1.29..1.29 rows=29 width=122) (actual time=0.013..0.013 rows=29 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=1
37. 0.006 0.006 ↑ 1.0 29 1

Seq Scan on activities activities4 (cost=0.00..1.29 rows=29 width=122) (actual time=0.003..0.006 rows=29 loops=1)

  • Buffers: shared hit=1
38. 0.005 0.008 ↑ 1.0 10 1

Hash (cost=1.10..1.10 rows=10 width=520) (actual time=0.008..0.008 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
39. 0.003 0.003 ↑ 1.0 10 1

Seq Scan on timeoffcode timeoffcode5 (cost=0.00..1.10 rows=10 width=520) (actual time=0.002..0.003 rows=10 loops=1)

  • Buffers: shared hit=1
40. 34.856 34.856 ↑ 1.0 1 17,428

Index Scan using login_pkey on login (cost=0.28..0.33 rows=1 width=18) (actual time=0.002..0.002 rows=1 loops=17,428)

  • Index Cond: (userid = ui.id)
  • Buffers: shared hit=52284
41. 17.428 17.428 ↑ 1.0 1 17,428

Index Scan using project_pkey on project pj (cost=0.29..0.30 rows=1 width=58) (actual time=0.001..0.001 rows=1 loops=17,428)

  • Index Cond: (at.projectid = id)
  • Buffers: shared hit=48219
42. 34.856 34.856 ↑ 1.0 1 17,428

Index Only Scan using timesheet_pkey on timesheet ts_1 (cost=0.42..0.44 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=17,428)

  • Index Cond: (id = at.timesheetid)
  • Heap Fetches: 17428
  • Buffers: shared hit=69712
43. 23.944 139.424 ↑ 1.0 1 17,428

Nested Loop Left Join (cost=0.85..1.50 rows=1 width=70) (actual time=0.005..0.008 rows=1 loops=17,428)

  • Filter: ((tahls.timesheetid IS NULL) OR (tah.serialnumber > tahls.lastsubmitserialnumber))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=184848
44. 69.712 69.712 ↑ 2.0 1 17,428

Index Scan using ixtah2timesheetid on timesheetapprovalhistory tah (cost=0.43..0.59 rows=2 width=74) (actual time=0.002..0.004 rows=1 loops=17,428)

  • Index Cond: (ts_1.id = timesheetid)
  • Filter: (((systemprocessidentifier IS NULL) OR (systemprocessidentifier <> 'urn:replicon:approval-system-process:timesheet-submit-script-data-and-validation'::text)) AND (action = ANY ('{2,3,6}'::integer[])))
  • Rows Removed by Filter: 3
  • Buffers: shared hit=93339
45. 45.768 45.768 ↑ 1.0 1 22,884

Index Scan using timesheetapprovalhistorylastsubmit_pkey on timesheetapprovalhistorylastsubmit tahls (cost=0.42..0.45 rows=1 width=20) (actual time=0.002..0.002 rows=1 loops=22,884)

  • Index Cond: (tah.timesheetid = timesheetid)
  • Buffers: shared hit=91509
46. 44.650 106.860 ↑ 1.0 1 21,372

Nested Loop Left Join (cost=0.57..0.63 rows=1 width=16) (actual time=0.004..0.005 rows=1 loops=21,372)

  • Buffers: shared hit=103044
47. 42.744 42.744 ↑ 1.0 1 21,372

Index Scan using task_pkey on task tk (cost=0.42..0.44 rows=1 width=20) (actual time=0.002..0.002 rows=1 loops=21,372)

  • Index Cond: (at.taskid = id)
  • Buffers: shared hit=78143
48. 19.466 19.466 ↑ 1.0 1 19,466

Index Scan using uix3er_currencyideffectivedate on exchangerate (cost=0.14..0.18 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=19,466)

  • Index Cond: ((variablecurrencyid = $0) AND (tk.estimatedcostcurrencyid = fixedcurrencyid) AND (('now'::cstring)::date >= effectivedate))
  • Filter: (('now'::cstring)::date <= enddate)
  • Buffers: shared hit=24900
49. 64.116 64.116 ↑ 1.0 1 21,372

Index Scan using ixdtslsftimesheetid on dm_timesheetlist_facts tslist (cost=0.42..0.46 rows=1 width=20) (actual time=0.003..0.003 rows=1 loops=21,372)

  • Index Cond: (timesheetid = at.timesheetid)
  • Buffers: shared hit=85488
50. 918.996 918.996 ↑ 2.0 1 21,372

Index Scan using ixtahkvtimesheetapprovalhistoryid on timesheetapprovalhistorykeyvalue esheetapprovalhistorykeyvalue8 (cost=0.56..2.83 rows=2 width=84) (actual time=0.042..0.043 rows=1 loops=21,372)

  • Index Cond: (tah.id = timesheetapprovalhistoryid)
  • Filter: (upper(key) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:ACTING-ACTUAL-USER'::text)
  • Rows Removed by Filter: 36
  • Buffers: shared hit=135498
51. 897.624 897.624 ↑ 2.0 1 21,372

Index Scan using ixtahkvtimesheetapprovalhistoryid on timesheetapprovalhistorykeyvalue esheetapprovalhistorykeyvalue9 (cost=0.56..2.83 rows=2 width=84) (actual time=0.042..0.042 rows=1 loops=21,372)

  • Index Cond: (tah.id = timesheetapprovalhistoryid)
  • Filter: (upper(key) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:ACTING-EFFECTIVE-USER'::text)
  • Rows Removed by Filter: 36
  • Buffers: shared hit=135498
52. 0.004 0.030 ↓ 1.1 14 1

Hash (cost=14.60..14.60 rows=13 width=4) (actual time=0.030..0.030 rows=14 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=5
53. 0.026 0.026 ↓ 1.1 14 1

Index Scan using ixullocationid on userlocation userlocation13 (cost=0.28..14.60 rows=13 width=4) (actual time=0.020..0.026 rows=14 loops=1)

  • Index Cond: (locationid = 'dc2e8f9f-d75b-4a15-ac1c-446fe360037e'::uuid)
  • Filter: ((startdate <= '2019-10-03'::date) AND (enddate >= '2019-10-03'::date))
  • Buffers: shared hit=5
54. 0.030 2.278 ↑ 11.0 1 134

Nested Loop (cost=1.98..2.60 rows=11 width=44) (actual time=0.016..0.017 rows=1 loops=134)

  • Buffers: shared hit=2606
55. 0.164 1.876 ↑ 1.0 1 134

Nested Loop (cost=1.56..1.73 rows=1 width=52) (actual time=0.012..0.014 rows=1 loops=134)

  • Join Filter: ((dm_attendancetimeallocation_facts.entrydate >= pbrh.effectivedate) AND (dm_attendancetimeallocation_facts.entrydate <= pbrh.enddate))
  • Buffers: shared hit=2100
56. 0.030 1.340 ↑ 1.0 1 134

Nested Loop (cost=1.14..1.25 rows=1 width=68) (actual time=0.010..0.010 rows=1 loops=134)

  • Buffers: shared hit=1508
57. 0.268 0.938 ↑ 1.0 1 134

Nested Loop (cost=0.71..0.79 rows=1 width=88) (actual time=0.006..0.007 rows=1 loops=134)

  • Buffers: shared hit=1010
58. 0.536 0.536 ↑ 1.0 1 134

Index Scan using dm_attendancetimeallocation_facts_pkey on dm_attendancetimeallocation_facts (cost=0.43..0.49 rows=1 width=44) (actual time=0.004..0.004 rows=1 loops=134)

  • Index Cond: (at.id = id)
  • Buffers: shared hit=638
59. 0.134 0.134 ↑ 1.0 1 134

Index Scan using billingrate_pkey on billingrate br (cost=0.28..0.30 rows=1 width=44) (actual time=0.001..0.001 rows=1 loops=134)

  • Index Cond: (id = dm_attendancetimeallocation_facts.billingrateid)
  • Buffers: shared hit=372
60. 0.372 0.372 ↑ 1.0 1 124

Index Scan using ixpbr2projectid on projectbillingrate pbr (cost=0.42..0.46 rows=1 width=40) (actual time=0.003..0.003 rows=1 loops=124)

  • Index Cond: ((projectid = dm_attendancetimeallocation_facts.projectid) AND (billingrateid = dm_attendancetimeallocation_facts.billingrateid))
  • Filter: (((billingrateid = $1) AND (dm_attendancetimeallocation_facts.userid = userid)) OR ((billingrateid <> $2) AND (userid IS NULL)))
  • Buffers: shared hit=498
61. 0.372 0.372 ↑ 1.0 1 124

Index Scan using ixpbrhprojectbillingrateid on projectbillingratehistory pbrh (cost=0.42..0.46 rows=1 width=24) (actual time=0.002..0.003 rows=1 loops=124)

  • Index Cond: (projectbillingrateid = pbr.id)
  • Buffers: shared hit=592
62. 0.372 0.372 ↑ 8.0 1 124

Index Only Scan using ixtsuseridstartdateenddate on timesheet dmvts (cost=0.42..0.79 rows=8 width=12) (actual time=0.003..0.003 rows=1 loops=124)

  • Index Cond: ((userid = dm_attendancetimeallocation_facts.userid) AND (startdate <= dm_attendancetimeallocation_facts.entrydate) AND (enddate >= dm_attendancetimeallocation_facts.entrydate))
  • Heap Fetches: 124
  • Buffers: shared hit=506
Planning time : 24.945 ms
Execution time : 26,332.587 ms