explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HC8T : NECAU_12Mar2019

Settings
# exclusive inclusive rows x rows loops node
1. 50.886 19,168.683 ↓ 4.5 5,518 1

Sort (cost=1,250,599.52..1,250,602.62 rows=1,239 width=369) (actual time=19,167.467..19,168.683 rows=5,518 loops=1)

  • Sort Key: ((ui.info1)::character varying(255)) COLLATE "en_US", ((login.loginname)::character varying(255)) COLLATE "en_US", ((ui.lastname)::character varying(50)) COLLATE "en_US", ((ui.firstname)::character varying(50)) COLLATE "en_US", ui.duplicatename (...)
  • Sort Method: quicksort Memory: 2932kB
  • Buffers: shared hit=195762 read=159965
2.          

Initplan (forSort)

3. 0.003 0.003 ↑ 1.0 1 1

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

  • Buffers: shared hit=1
4. 8.586 19,117.794 ↓ 4.5 5,518 1

Hash Join (cost=92,735.64..1,250,534.86 rows=1,239 width=369) (actual time=1,513.511..19,117.794 rows=5,518 loops=1)

  • Hash Cond: (du.departmentid = dep.id)
  • Buffers: shared hit=195762 read=159965
5. 6.427 19,109.198 ↓ 4.5 5,518 1

Hash Join (cost=92,734.55..1,250,516.73 rows=1,239 width=368) (actual time=1,513.484..19,109.198 rows=5,518 loops=1)

  • Hash Cond: (ts.userid = du.userid)
  • Buffers: shared hit=195761 read=159965
6. 11.366 19,101.403 ↓ 4.5 5,518 1

Nested Loop Left Join (cost=92,660.89..1,250,426.04 rows=1,239 width=368) (actual time=1,512.090..19,101.403 rows=5,518 loops=1)

  • Buffers: shared hit=195746 read=159965
7. 8.633 18,775.511 ↓ 4.5 5,518 1

Nested Loop Left Join (cost=92,660.46..1,205,296.58 rows=1,239 width=313) (actual time=1,511.990..18,775.511 rows=5,518 loops=1)

  • Buffers: shared hit=169257 read=159965
8. 10.026 18,441.316 ↓ 4.5 5,518 1

Nested Loop Left Join (cost=92,660.02..1,160,179.51 rows=1,239 width=242) (actual time=1,511.873..18,441.316 rows=5,518 loops=1)

  • Buffers: shared hit=142768 read=159965
9. 5.807 18,392.664 ↓ 4.5 5,518 1

Hash Left Join (cost=92,659.45..1,159,371.27 rows=1,239 width=229) (actual time=1,511.820..18,392.664 rows=5,518 loops=1)

  • Hash Cond: (at.activityid = activities4.id)
  • Buffers: shared hit=122019 read=159965
10. 5.179 18,386.845 ↓ 4.5 5,518 1

Hash Left Join (cost=92,658.36..1,159,353.86 rows=1,239 width=214) (actual time=1,511.794..18,386.845 rows=5,518 loops=1)

  • Hash Cond: (pj.projectleaderapproverid = userinfo3.id)
  • Buffers: shared hit=122018 read=159965
11. 12.078 18,377.720 ↓ 4.5 5,518 1

Nested Loop Left Join (cost=92,491.35..1,159,181.20 rows=1,239 width=203) (actual time=1,507.806..18,377.720 rows=5,518 loops=1)

  • Buffers: shared hit=121910 read=159965
12. 543.147 18,349.088 ↓ 4.5 5,518 1

Hash Join (cost=92,491.06..1,158,789.85 rows=1,239 width=203) (actual time=1,507.788..18,349.088 rows=5,518 loops=1)

  • Hash Cond: (at.timesheetid = ts_1.id)
  • Buffers: shared hit=106356 read=159965
13. 6,297.568 17,586.476 ↑ 1.0 1,411,091 1

Hash Left Join (cost=58,070.08..1,104,953.98 rows=1,411,091 width=129) (actual time=1,287.895..17,586.476 rows=1,411,091 loops=1)

  • Hash Cond: (at.projectid = pj_1.id)
  • Join Filter: ((at.entrydate >= pc.effectivedate) AND (at.entrydate <= pc.enddate))
  • Buffers: shared hit=24708 read=159965
14. 705.159 11,197.984 ↑ 1.0 1,411,091 1

Hash Left Join (cost=55,371.69..336,345.38 rows=1,411,091 width=89) (actual time=1,196.759..11,197.984 rows=1,411,091 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=23393 read=159965
15. 1,286.983 10,478.741 ↑ 1.0 1,411,091 1

Hash Right Join (cost=54,001.55..324,392.03 rows=1,411,091 width=89) (actual time=1,182.656..10,478.741 rows=1,411,091 loops=1)

  • Hash Cond: (isbillable.factid = at.id)
  • Buffers: shared hit=22309 read=159965
16. 8,018.382 8,018.382 ↓ 36.3 1,329,128 1

Seq Scan on dm_attendancetimeallocation_metadata isbillable (cost=0.00..269,886.93 rows=36,622 width=17) (actual time=0.046..8,018.382 rows=1,329,128 loops=1)

  • Filter: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:IS-BILLABLE'::text)
  • Rows Removed by Filter: 5995201
  • Buffers: shared hit=57 read=159965
17. 658.540 1,173.376 ↑ 1.0 1,411,091 1

Hash (cost=36,362.91..36,362.91 rows=1,411,091 width=88) (actual time=1,173.376..1,173.376 rows=1,411,091 loops=1)

  • Buckets: 2097152 Batches: 1 Memory Usage: 159775kB
  • Buffers: shared hit=22252
18. 514.836 514.836 ↑ 1.0 1,411,091 1

Seq Scan on dm_attendancetimeallocation_facts at (cost=0.00..36,362.91 rows=1,411,091 width=88) (actual time=0.007..514.836 rows=1,411,091 loops=1)

  • Buffers: shared hit=22252
19. 0.001 14.084 ↓ 0.0 0 1

Hash (cost=1,370.13..1,370.13 rows=1 width=16) (actual time=14.084..14.084 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=1084
20. 0.001 14.083 ↓ 0.0 0 1

Nested Loop (cost=0.29..1,370.13 rows=1 width=16) (actual time=14.083..14.083 rows=0 loops=1)

  • Buffers: shared hit=1084
21. 14.082 14.082 ↓ 0.0 0 1

Seq Scan on project pj_2 (cost=0.00..1,367.81 rows=1 width=4) (actual time=14.082..14.082 rows=0 loops=1)

  • Filter: (clientbillingallocationmethod = 1)
  • Rows Removed by Filter: 22753
  • Buffers: shared hit=1084
22. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (projectid = pj_2.id)
  • Heap Fetches: 0
23. 13.226 90.924 ↓ 1.0 22,753 1

Hash (cost=2,416.60..2,416.60 rows=22,543 width=52) (actual time=90.924..90.924 rows=22,753 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1523kB
  • Buffers: shared hit=1315
24. 22.422 77.698 ↓ 1.0 22,753 1

Hash Join (cost=738.22..2,416.60 rows=22,543 width=52) (actual time=33.368..77.698 rows=22,753 loops=1)

  • Hash Cond: (pj_1.id = pc.projectid)
  • Buffers: shared hit=1315
25. 22.085 22.085 ↓ 1.0 22,753 1

Seq Scan on project pj_1 (cost=0.00..1,367.81 rows=22,705 width=8) (actual time=0.011..22.085 rows=22,753 loops=1)

  • Filter: (clientbillingallocationmethod = 0)
  • Buffers: shared hit=1084
26. 12.778 33.191 ↓ 1.0 22,753 1

Hash (cost=456.43..456.43 rows=22,543 width=48) (actual time=33.191..33.191 rows=22,753 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1434kB
  • Buffers: shared hit=231
27. 20.413 20.413 ↓ 1.0 22,753 1

Seq Scan on projectclient pc (cost=0.00..456.43 rows=22,543 width=48) (actual time=0.017..20.413 rows=22,753 loops=1)

  • Buffers: shared hit=231
28. 0.387 219.465 ↓ 2.6 427 1

Hash (cost=34,418.93..34,418.93 rows=164 width=183) (actual time=219.465..219.465 rows=427 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 102kB
  • Buffers: shared hit=81648
29. 0.457 219.078 ↓ 2.6 427 1

Nested Loop Left Join (cost=3.23..34,418.93 rows=164 width=183) (actual time=0.075..219.078 rows=427 loops=1)

  • Buffers: shared hit=81648
30. 0.507 217.767 ↓ 2.6 427 1

Nested Loop Left Join (cost=2.95..34,360.64 rows=164 width=172) (actual time=0.070..217.767 rows=427 loops=1)

  • Buffers: shared hit=80367
31. 0.403 215.979 ↓ 2.6 427 1

Nested Loop (cost=2.67..34,295.72 rows=164 width=168) (actual time=0.062..215.979 rows=427 loops=1)

  • Buffers: shared hit=78567
32. 0.579 214.722 ↓ 2.6 427 1

Nested Loop (cost=2.38..34,245.22 rows=164 width=151) (actual time=0.057..214.722 rows=427 loops=1)

  • Buffers: shared hit=77285
33. 0.347 213.289 ↓ 2.6 427 1

Nested Loop (cost=2.10..34,194.68 rows=164 width=124) (actual time=0.052..213.289 rows=427 loops=1)

  • Buffers: shared hit=75999
34. 0.542 212.088 ↓ 2.6 427 1

Nested Loop (cost=1.68..34,118.85 rows=164 width=104) (actual time=0.049..212.088 rows=427 loops=1)

  • Buffers: shared hit=74289
35. 2.262 210.265 ↓ 2.6 427 1

Nested Loop (cost=1.26..34,042.48 rows=165 width=80) (actual time=0.043..210.265 rows=427 loops=1)

  • Join Filter: (tah.timesheetid = tslist.timesheetid)
  • Buffers: shared hit=72579
  • -> Index Scan using dm_timesheetlist_facts_pkey on dm_timesheetlist_facts tslist (cost=0.42..0.54 rows=1 width=20) (actual time=0.003..0.003 rows=1 lo (...)
36. 208.003 208.003 ↓ 2.9 507 1

Nested Loop (cost=0.84..33,947.39 rows=172 width=60) (actual time=0.031..208.003 rows=507 loops=1)

  • Buffers: shared hit=70547
  • -> Index Scan using ixtahaction on timesheetapprovalhistory tah (cost=0.42..32873.19 rows=516 width=48) (actual time=0.018..205.849 rows=519 loo (...)
  • Index Cond: (action = ANY ('{2,3,6}'::integer[]))
  • Filter: (((systemprocessidentifier IS NULL) OR (systemprocessidentifier <> 'urn:replicon:approval-system-process:timesheet-submit-script-dat (...)
  • Rows Removed by Filter: 636144
  • Buffers: shared hit=68471
  • -> Index Scan using timesheetapprovalhistorylastsubmit_pkey on timesheetapprovalhistorylastsubmit tahls (cost=0.42..2.07 rows=1 width=20) (actua (...)
  • Index Cond: (timesheetid = tah.timesheetid)
  • Filter: (tah.serialnumber > lastsubmitserialnumber)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=2076
  • Index Cond: (timesheetid = tahls.timesheetid)
  • Filter: (timesheetstatus = 2)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=2032
37. 1.281 1.281 ↑ 1.0 1 427

Index Scan using timesheet_pkey on timesheet ts_1 (cost=0.42..0.45 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=427)

  • Index Cond: (id = tslist.timesheetid)
  • Buffers: shared hit=1710
38. 0.854 0.854 ↑ 1.0 1 427

Index Scan using timesheet_pkey on timesheet ts (cost=0.42..0.45 rows=1 width=20) (actual time=0.002..0.002 rows=1 loops=427)

  • Index Cond: (id = ts_1.id)
  • Buffers: shared hit=1710
39. 0.854 0.854 ↑ 1.0 1 427

Index Scan using userinfo_pkey on userinfo ui (cost=0.28..0.30 rows=1 width=27) (actual time=0.002..0.002 rows=1 loops=427)

  • Index Cond: (id = ts.userid)
  • Buffers: shared hit=1286
40. 0.854 0.854 ↑ 1.0 1 427

Index Scan using login_pkey on login (cost=0.28..0.30 rows=1 width=17) (actual time=0.002..0.002 rows=1 loops=427)

  • Index Cond: (userid = ts.userid)
  • Buffers: shared hit=1282
41. 1.281 1.281 ↑ 1.0 1 427

Index Scan using ix3uh_usersuperstart on userhierarchy userhierarchy13 (cost=0.28..0.39 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=427)

  • Index Cond: ((ui.id = userid) AND ('2019-03-12'::date >= startdate))
  • Filter: ('2019-03-12'::date <= enddate)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=1800
42. 0.854 0.854 ↑ 1.0 1 427

Index Scan using userinfo_pkey on userinfo userinfo2 (cost=0.28..0.35 rows=1 width=19) (actual time=0.001..0.002 rows=1 loops=427)

  • Index Cond: (userhierarchy13.supervisorid = id)
  • Buffers: shared hit=1281
43. 16.554 16.554 ↑ 1.0 1 5,518

Index Scan using project_pkey on project pj (cost=0.29..0.31 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=5,518)

  • Index Cond: (at.projectid = id)
  • Buffers: shared hit=15554
44. 1.408 3.946 ↓ 1.0 2,629 1

Hash (cost=134.23..134.23 rows=2,623 width=19) (actual time=3.946..3.946 rows=2,629 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 169kB
  • Buffers: shared hit=108
45. 2.538 2.538 ↓ 1.0 2,629 1

Seq Scan on userinfo userinfo3 (cost=0.00..134.23 rows=2,623 width=19) (actual time=0.006..2.538 rows=2,629 loops=1)

  • Buffers: shared hit=108
46. 0.009 0.012 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=23) (actual time=0.012..0.012 rows=4 loops=1)

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

Seq Scan on activities activities4 (cost=0.00..1.04 rows=4 width=23) (actual time=0.003..0.003 rows=4 loops=1)

  • Buffers: shared hit=1
48. 16.554 38.626 ↑ 1.0 1 5,518

Nested Loop Left Join (cost=0.57..0.64 rows=1 width=17) (actual time=0.006..0.007 rows=1 loops=5,518)

  • Buffers: shared hit=20749
49. 22.072 22.072 ↑ 1.0 1 5,518

Index Scan using task_pkey on task tk (cost=0.42..0.46 rows=1 width=21) (actual time=0.003..0.004 rows=1 loops=5,518)

  • Index Cond: (at.taskid = id)
  • Buffers: shared hit=20748
50. 0.000 0.000 ↓ 0.0 0 5,184

Index Scan using uix3er_currencyideffectivedate on exchangerate (cost=0.14..0.18 rows=1 width=4) (actual time=0.000..0.000 rows=0 loops=5,184)

  • Index Cond: ((variablecurrencyid = $0) AND (tk.estimatedcostcurrencyid = fixedcurrencyid) AND (('now'::cstring)::date >= effectivedate))
  • Filter: (('now'::cstring)::date <= enddate)
51. 325.562 325.562 ↑ 1.0 1 5,518

Index Scan using ixtahkvtimesheetapprovalhistoryid on timesheetapprovalhistorykeyvalue esheetapprovalhistorykeyvalue8 (cost=0.43..36.40 rows=1 width=87) (actual time=0.056..0.059 rows=1 loops=5,518)

  • Index Cond: (tah.id = timesheetapprovalhistoryid)
  • Filter: (upper(key) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:ACTING-ACTUAL-USER'::text)
  • Rows Removed by Filter: 25
  • Buffers: shared hit=26489
52. 314.526 314.526 ↑ 2.0 1 5,518

Index Scan using ixtahkvtimesheetapprovalhistoryid on timesheetapprovalhistorykeyvalue esheetapprovalhistorykeyvalue9 (cost=0.43..36.40 rows=2 width=87) (actual time=0.057..0.057 rows=1 loops=5,518)

  • Index Cond: (tah.id = timesheetapprovalhistoryid)
  • Filter: (upper(key) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:ACTING-EFFECTIVE-USER'::text)
  • Rows Removed by Filter: 25
  • Buffers: shared hit=26489
53. 0.737 1.368 ↓ 1.0 2,629 1

Hash (cost=41.07..41.07 rows=2,607 width=8) (actual time=1.368..1.368 rows=2,629 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 135kB
  • Buffers: shared hit=15
54. 0.631 0.631 ↓ 1.0 2,629 1

Seq Scan on departmentusers du (cost=0.00..41.07 rows=2,607 width=8) (actual time=0.004..0.631 rows=2,629 loops=1)

  • Buffers: shared hit=15
55. 0.004 0.010 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=9) (actual time=0.010..0.010 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
56. 0.006 0.006 ↑ 1.0 4 1

Seq Scan on departments dep (cost=0.00..1.04 rows=4 width=9) (actual time=0.003..0.006 rows=4 loops=1)

  • Buffers: shared hit=1