explain.depesz.com

PostgreSQL's explain analyze made readable

Result: y1jB : NTTData - RR

Settings
# exclusive inclusive rows x rows loops node
1. 164.622 1,205,161.878 ↓ 10.9 30,022 1

Sort (cost=2,692,697.84..2,692,704.76 rows=2,765 width=4,823) (actual time=1,205,158.052..1,205,161.878 rows=30,022 loops=1)

  • Sort Key: ((ui.externalid)::character varying(255)) COLLATE "en_US", ((ui.firstname)::character varying(50)) COLLATE "en_US", ((ui.lastname)::character varying(50)) COLLATE "en_US", ((employeetype1.name)::character varying(50)) COLLATE "en_US", ((userinfo2.displayname)::text) COLLATE "en_US", at.entrydate, ta.timestamputc, ((dep.name)::character varying(255)) COLLATE "en_US", ((ui.info2)::character varying(255)) COLLATE "en_US", ((cl.name)::character varying(255)) COLLATE "en_US", ((pj.code)::character varying(50)) COLLATE "en_US", ((pj.name)::character varying(255)) COLLATE "en_US", tah.timestamputc, ((costcenter16.name)::character varying(50)) COLLATE "en_US", ((division17.name)::character varying(50)) COLLATE "en_US", ((pj.info3)::character varying(255)) COLLATE "en_US
  • Sort Method: quicksort Memory: 16555kB
  • Buffers: shared hit=3937561 read=837852
2. 47.240 1,204,997.256 ↓ 10.9 30,022 1

Hash Left Join (cost=2,071,863.99..2,692,539.78 rows=2,765 width=4,823) (actual time=1,024,926.094..1,204,997.256 rows=30,022 loops=1)

  • Hash Cond: (userdivision21.divisionid = division17.id)
  • Buffers: shared hit=3937548 read=837852
3. 21.361 1,202,848.179 ↓ 10.9 30,022 1

Hash Left Join (cost=2,071,833.62..2,685,683.31 rows=2,765 width=1,208) (actual time=1,024,925.760..1,202,848.179 rows=30,022 loops=1)

  • Hash Cond: (at.userid = userdivision21.userid)
  • Join Filter: ((at.entrydate >= userdivision21.startdate) AND (at.entrydate <= userdivision21.enddate))
  • Rows Removed by Join Filter: 12577
  • Buffers: shared hit=3821679 read=836349
4. 12.627 1,202,673.249 ↓ 10.9 30,022 1

Hash Left Join (cost=2,071,307.55..2,685,053.37 rows=2,765 width=1,192) (actual time=1,024,772.009..1,202,673.249 rows=30,022 loops=1)

  • Hash Cond: (usercostcenter20.costcenterid = costcenter16.id)
  • Buffers: shared hit=3821638 read=836233
5. 19.802 1,202,660.610 ↓ 10.9 30,022 1

Hash Left Join (cost=2,071,306.50..2,685,014.30 rows=2,765 width=1,090) (actual time=1,024,771.971..1,202,660.610 rows=30,022 loops=1)

  • Hash Cond: (at.userid = usercostcenter20.userid)
  • Join Filter: ((at.entrydate >= usercostcenter20.startdate) AND (at.entrydate <= usercostcenter20.enddate))
  • Rows Removed by Join Filter: 2263
  • Buffers: shared hit=3821637 read=836233
6. 9.397 1,202,566.014 ↓ 10.9 30,022 1

Hash Left Join (cost=2,070,976.97..2,684,607.73 rows=2,765 width=1,074) (actual time=1,024,697.083..1,202,566.014 rows=30,022 loops=1)

  • Hash Cond: (tsar.userid = login15.userid)
  • Buffers: shared hit=3821596 read=836177
7. 26.711 1,202,553.108 ↓ 10.9 30,022 1

Hash Semi Join (cost=2,070,588.13..2,684,180.89 rows=2,765 width=1,055) (actual time=1,024,693.501..1,202,553.108 rows=30,022 loops=1)

  • Hash Cond: (ts.userid = ui_1.id)
  • Buffers: shared hit=3821451 read=836177
8. 39.509 1,202,519.879 ↓ 2.2 153,399 1

Hash Left Join (cost=2,070,021.34..2,683,392.65 rows=69,143 width=1,059) (actual time=1,024,686.963..1,202,519.879 rows=153,399 loops=1)

  • Hash Cond: (at.projectid = pj.id)
  • Buffers: shared hit=3820879 read=836177
9. 36.313 1,202,480.200 ↓ 2.2 153,399 1

Hash Left Join (cost=2,070,006.25..2,683,063.30 rows=69,143 width=1,002) (actual time=1,024,686.776..1,202,480.200 rows=153,399 loops=1)

  • Hash Cond: ((CASE WHEN (pj_1.clientbillingallocationmethod = 0) THEN pc.clientid ELSE at.userspecifiedclientid END) = cl.id)
  • Buffers: shared hit=3820870 read=836177
10. 45.070 1,202,443.865 ↓ 2.2 153,399 1

Hash Left Join (cost=2,070,003.54..2,682,538.57 rows=69,143 width=988) (actual time=1,024,686.729..1,202,443.865 rows=153,399 loops=1)

  • Hash Cond: (ts_1.id = tan.timesheetid)
  • Buffers: shared hit=3820869 read=836177
11. 523.916 1,202,295.865 ↓ 2.2 153,399 1

Hash Right Join (cost=2,065,498.65..2,677,725.41 rows=69,143 width=967) (actual time=1,024,583.656..1,202,295.865 rows=153,399 loops=1)

  • Hash Cond: (sheetapprovalhistorykeyvalue12.timesheetapprovalhistoryid = tah.id)
  • Buffers: shared hit=3817743 read=835955
12. 196,090.148 196,090.148 ↑ 1.0 1,358,363 1

Index Scan using ixtahkvkey on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue12 (cost=0.69..604,745.13 rows=1,360,516 width=87) (actual time=0.074..196,090.148 rows=1,358,363 loops=1)

  • Index Cond: (upper(key) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:ACTING-EFFECTIVE-USER'::text)
  • Buffers: shared hit=569625 read=150493
13. 167.675 1,005,681.801 ↓ 2.2 153,399 1

Hash (cost=2,064,633.67..2,064,633.67 rows=69,143 width=912) (actual time=1,005,681.801..1,005,681.801 rows=153,399 loops=1)

  • Buckets: 262144 (originally 131072) Batches: 1 (originally 1) Memory Usage: 54012kB
  • Buffers: shared hit=3248115 read=685462
14. 692.050 1,005,514.126 ↓ 2.2 153,399 1

Hash Right Join (cost=1,652,525.82..2,064,633.67 rows=69,143 width=912) (actual time=193,871.078..1,005,514.126 rows=153,399 loops=1)

  • Hash Cond: (sheetapprovalhistorykeyvalue11.timesheetapprovalhistoryid = tah.id)
  • Buffers: shared hit=3248115 read=685462
15. 869,860.453 869,860.453 ↓ 1.0 726,969 1

Index Scan using ixtahkvkey on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue11 (cost=0.69..408,161.53 rows=717,687 width=87) (actual time=3.990..869,860.453 rows=726,969 loops=1)

  • Index Cond: (upper(key) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:ACTING-ACTUAL-USER'::text)
  • Buffers: shared hit=8855 read=557063
16. 179.134 134,961.623 ↓ 2.2 153,399 1

Hash (cost=1,651,660.85..1,651,660.85 rows=69,143 width=841) (actual time=134,961.623..134,961.623 rows=153,399 loops=1)

  • Buckets: 262144 (originally 131072) Batches: 1 (originally 1) Memory Usage: 44672kB
  • Buffers: shared hit=3239260 read=128399
17. 843.855 134,782.489 ↓ 2.2 153,399 1

Hash Join (cost=162,511.59..1,651,660.85 rows=69,143 width=841) (actual time=60,269.112..134,782.489 rows=153,399 loops=1)

  • Hash Cond: (at.timesheetid = ts_1.id)
  • Buffers: shared hit=3239260 read=128399
18. 11,104.245 77,192.887 ↓ 1.0 2,543,213 1

Hash Left Join (cost=64,198.06..1,517,724.60 rows=2,540,455 width=616) (actual time=3,477.961..77,192.887 rows=2,543,213 loops=1)

  • Hash Cond: (at.projectid = pj_1.id)
  • Join Filter: ((at.entrydate >= pc.effectivedate) AND (at.entrydate <= pc.enddate))
  • Buffers: shared hit=2698608 read=87214
19. 480.337 66,088.280 ↓ 1.0 2,541,356 1

Hash Left Join (cost=64,169.23..177,886.44 rows=2,540,455 width=85) (actual time=2,882.655..66,088.280 rows=2,541,356 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=2698547 read=86808
20. 942.387 65,607.834 ↓ 1.0 2,541,356 1

Merge Left Join (cost=64,151.08..158,814.86 rows=2,540,455 width=85) (actual time=2,882.525..65,607.834 rows=2,541,356 loops=1)

  • Merge Cond: (at.id = isbillable.factid)
  • Buffers: shared hit=2698532 read=86808
21. 61,719.235 61,719.235 ↓ 1.0 2,541,356 1

Index Scan using dm_attendancetimeallocation_facts_pkey on dm_attendancetimeallocation_facts at (cost=0.43..88,186.25 rows=2,540,455 width=84) (actual time=0.008..61,719.235 rows=2,541,356 loops=1)

  • Buffers: shared hit=2660012 read=48948
22. 152.966 2,946.212 ↓ 24.1 203,437 1

Sort (cost=64,150.63..64,171.77 rows=8,456 width=17) (actual time=2,882.511..2,946.212 rows=203,437 loops=1)

  • Sort Key: isbillable.factid
  • Sort Method: quicksort Memory: 22038kB
  • Buffers: shared hit=38520 read=37860
23. 2,793.246 2,793.246 ↓ 24.1 203,437 1

Seq Scan on dm_attendancetimeallocation_metadata isbillable (cost=0.00..63,599.06 rows=8,456 width=17) (actual time=5.616..2,793.246 rows=203,437 loops=1)

  • Filter: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:IS-BILLABLE'::text)
  • Rows Removed by Filter: 1446836
  • Buffers: shared hit=38516 read=37860
24. 0.008 0.109 ↑ 2.0 2 1

Hash (cost=18.09..18.09 rows=4 width=16) (actual time=0.109..0.109 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=15
25. 0.004 0.101 ↑ 2.0 2 1

Nested Loop (cost=0.15..18.09 rows=4 width=16) (actual time=0.034..0.101 rows=2 loops=1)

  • Buffers: shared hit=15
26. 0.077 0.077 ↑ 1.0 4 1

Seq Scan on project pj_2 (cost=0.00..12.39 rows=4 width=4) (actual time=0.014..0.077 rows=4 loops=1)

  • Filter: (clientbillingallocationmethod = 1)
  • Rows Removed by Filter: 275
  • Buffers: shared hit=9
27. 0.020 0.020 ↓ 0.0 0 4

Index Only Scan using uix4pc_projectclienteffectiveend on projectclient pc_1 (cost=0.15..1.41 rows=1 width=16) (actual time=0.005..0.005 rows=0 loops=4)

  • Index Cond: (projectid = pj_2.id)
  • Heap Fetches: 2
  • Buffers: shared hit=6
28. 0.063 0.362 ↓ 1.0 264 1

Hash (cost=25.59..25.59 rows=259 width=52) (actual time=0.362..0.362 rows=264 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
  • Buffers: shared hit=12
29. 0.071 0.299 ↓ 1.0 264 1

Hash Join (cost=15.72..25.59 rows=259 width=52) (actual time=0.136..0.299 rows=264 loops=1)

  • Hash Cond: (pc.projectid = pj_1.id)
  • Buffers: shared hit=12
30. 0.119 0.119 ↓ 1.0 266 1

Seq Scan on projectclient pc (cost=0.00..6.29 rows=263 width=48) (actual time=0.012..0.119 rows=266 loops=1)

  • Buffers: shared hit=3
31. 0.034 0.109 ↓ 1.0 275 1

Hash (cost=12.39..12.39 rows=267 width=8) (actual time=0.109..0.109 rows=275 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
  • Buffers: shared hit=9
32. 0.075 0.075 ↓ 1.0 275 1

Seq Scan on project pj_1 (cost=0.00..12.39 rows=267 width=8) (actual time=0.004..0.075 rows=275 loops=1)

  • Filter: (clientbillingallocationmethod = 0)
  • Rows Removed by Filter: 4
  • Buffers: shared hit=9
33. 26.606 56,745.747 ↓ 2.0 21,446 1

Hash (cost=98,178.65..98,178.65 rows=10,790 width=793) (actual time=56,745.747..56,745.747 rows=21,446 loops=1)

  • Buckets: 32768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 5152kB
  • Buffers: shared hit=540652 read=41185
34. 12.696 56,719.141 ↓ 2.0 21,446 1

Hash Join (cost=12,970.58..98,178.65 rows=10,790 width=793) (actual time=5,215.002..56,719.141 rows=21,446 loops=1)

  • Hash Cond: (du.departmentid = dep.id)
  • Buffers: shared hit=540652 read=41185
35. 12.979 56,706.421 ↓ 2.0 21,446 1

Hash Left Join (cost=12,968.55..98,028.26 rows=10,790 width=281) (actual time=5,214.964..56,706.421 rows=21,446 loops=1)

  • Hash Cond: (userhierarchy19.supervisorid = userinfo2.id)
  • Buffers: shared hit=540651 read=41185
36. 13.463 56,687.221 ↓ 2.0 21,446 1

Nested Loop Left Join (cost=12,407.21..97,318.55 rows=10,790 width=267) (actual time=5,208.667..56,687.221 rows=21,446 loops=1)

  • Buffers: shared hit=540079 read=41185
37. 16.104 47,966.682 ↓ 2.0 21,446 1

Hash Join (cost=12,405.93..81,458.05 rows=10,790 width=259) (actual time=5,201.683..47,966.682 rows=21,446 loops=1)

  • Hash Cond: (ts.userid = du.userid)
  • Buffers: shared hit=287012 read=35393
38. 15.394 47,947.605 ↓ 2.0 21,446 1

Hash Left Join (cost=12,104.86..81,006.61 rows=10,935 width=251) (actual time=5,198.630..47,947.605 rows=21,446 loops=1)

  • Hash Cond: (ui.id = userhierarchy19.userid)
  • Buffers: shared hit=286953 read=35393
39. 10.074 47,927.422 ↓ 2.0 21,446 1

Hash Join (cost=11,627.00..80,317.91 rows=10,935 width=247) (actual time=5,193.760..47,927.422 rows=21,446 loops=1)

  • Hash Cond: (ui.employeetypeid = employeetype1.id)
  • Buffers: shared hit=286836 read=35393
40. 17.953 47,917.341 ↓ 2.0 21,446 1

Hash Join (cost=11,625.86..80,166.42 rows=10,935 width=133) (actual time=5,193.734..47,917.341 rows=21,446 loops=1)

  • Hash Cond: (ts.userid = ui.id)
  • Buffers: shared hit=286835 read=35393
41. 23.686 47,892.682 ↓ 2.0 21,446 1

Nested Loop (cost=11,064.52..79,454.72 rows=10,935 width=100) (actual time=5,186.950..47,892.682 rows=21,446 loops=1)

  • Buffers: shared hit=286263 read=35393
42. 15.002 47,826.104 ↓ 2.0 21,446 1

Nested Loop (cost=11,064.10..74,323.17 rows=10,935 width=80) (actual time=5,186.944..47,826.104 rows=21,446 loops=1)

  • Buffers: shared hit=199171 read=35390
43. 20.461 42,673.767 ↓ 1.7 21,861 1

Nested Loop (cost=11,063.68..67,447.46 rows=12,943 width=60) (actual time=5,184.469..42,673.767 rows=21,861 loops=1)

  • Buffers: shared hit=113535 read=32778
44. 24.750 37,144.334 ↓ 1.7 21,861 1

Hash Left Join (cost=11,063.26..59,086.71 rows=12,943 width=44) (actual time=5,182.193..37,144.334 rows=21,861 loops=1)

  • Hash Cond: (tah.timesheetid = tahls.timesheetid)
  • Filter: ((tahls.timesheetid IS NULL) OR (tah.serialnumber > tahls.lastsubmitserialnumber))
  • Rows Removed by Filter: 1359
  • Buffers: shared hit=28449 read=29099
45. 36,650.248 36,650.248 ↑ 1.7 23,220 1

Index Scan using ixtahaction on timesheetapprovalhistory tah (cost=0.43..47,392.93 rows=38,828 width=48) (actual time=4,710.720..36,650.248 rows=23,220 loops=1)

  • Index Cond: (action = ANY ('{2,3,6}'::integer[]))
  • Filter: (((systemprocessidentifier IS NULL) OR (systemprocessidentifier <> 'urn:replicon:approval-system-process:timesheet-submit-script-data-and-validation'::text)) AND (timestamputc >= '2019-04-01 06:00:00'::timestamp without time zone) AND (timestamputc < '2019-05-01 06:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 810447
  • Buffers: shared hit=26139 read=26562
46. 66.202 469.336 ↑ 1.0 368,792 1

Hash (cost=6,329.59..6,329.59 rows=378,659 width=20) (actual time=469.336..469.336 rows=368,792 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 22824kB
  • Buffers: shared hit=2310 read=2537
47. 403.134 403.134 ↑ 1.0 368,792 1

Seq Scan on timesheetapprovalhistorylastsubmit tahls (cost=0.00..6,329.59 rows=378,659 width=20) (actual time=4.102..403.134 rows=368,792 loops=1)

  • Buffers: shared hit=2310 read=2537
48. 5,508.972 5,508.972 ↑ 1.0 1 21,861

Index Only Scan using timesheet_pkey on timesheet ts_1 (cost=0.42..0.64 rows=1 width=16) (actual time=0.252..0.252 rows=1 loops=21,861)

  • Index Cond: (id = tah.timesheetid)
  • Heap Fetches: 22170
  • Buffers: shared hit=85086 read=3679
49. 5,137.335 5,137.335 ↑ 1.0 1 21,861

Index Scan using ixdtslsftimesheetid on dm_timesheetlist_facts tslist (cost=0.42..0.52 rows=1 width=20) (actual time=0.231..0.235 rows=1 loops=21,861)

  • Index Cond: (timesheetid = ts_1.id)
  • Filter: (timesheetstatus = 2)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=85636 read=2612
50. 42.892 42.892 ↑ 1.0 1 21,446

Index Scan using timesheet_pkey on timesheet ts (cost=0.42..0.46 rows=1 width=20) (actual time=0.002..0.002 rows=1 loops=21,446)

  • Index Cond: (id = ts_1.id)
  • Buffers: shared hit=87092 read=3
51. 2.573 6.706 ↑ 1.0 10,841 1

Hash (cost=425.04..425.04 rows=10,904 width=33) (actual time=6.706..6.706 rows=10,841 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 864kB
  • Buffers: shared hit=572
52. 4.133 4.133 ↑ 1.0 10,841 1

Seq Scan on userinfo ui (cost=0.00..425.04 rows=10,904 width=33) (actual time=0.136..4.133 rows=10,841 loops=1)

  • Buffers: shared hit=572
53. 0.004 0.007 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=122) (actual time=0.007..0.007 rows=6 loops=1)

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

Seq Scan on employeetype employeetype1 (cost=0.00..1.06 rows=6 width=122) (actual time=0.003..0.003 rows=6 loops=1)

  • Buffers: shared hit=1
55. 1.747 4.789 ↑ 1.0 9,907 1

Hash (cost=351.35..351.35 rows=10,121 width=8) (actual time=4.789..4.789 rows=9,907 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 515kB
  • Buffers: shared hit=117
56. 3.042 3.042 ↑ 1.0 9,907 1

Seq Scan on userhierarchy userhierarchy19 (cost=0.00..351.35 rows=10,121 width=8) (actual time=0.014..3.042 rows=9,907 loops=1)

  • Filter: (('2019-06-04'::date >= startdate) AND ('2019-06-04'::date <= enddate))
  • Rows Removed by Filter: 5616
  • Buffers: shared hit=117
57. 1.662 2.973 ↓ 1.0 10,841 1

Hash (cost=166.59..166.59 rows=10,759 width=8) (actual time=2.973..2.973 rows=10,841 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 552kB
  • Buffers: shared hit=59
58. 1.311 1.311 ↓ 1.0 10,841 1

Seq Scan on departmentusers du (cost=0.00..166.59 rows=10,759 width=8) (actual time=0.005..1.311 rows=10,841 loops=1)

  • Buffers: shared hit=59
59. 22.536 8,707.076 ↑ 1.0 1 21,446

Nested Loop (cost=1.27..1.46 rows=1 width=24) (actual time=0.397..0.406 rows=1 loops=21,446)

  • Buffers: shared hit=253067 read=5792
60. 3.406 8,471.170 ↑ 1.0 1 21,446

Nested Loop (cost=0.85..0.99 rows=1 width=24) (actual time=0.393..0.395 rows=1 loops=21,446)

  • Buffers: shared hit=166530 read=5681
61. 3,602.928 3,602.928 ↑ 1.0 1 21,446

Index Scan using timesheetapprovalhistorylastsubmit_pkey on timesheetapprovalhistorylastsubmit tahls_1 (cost=0.42..0.45 rows=1 width=20) (actual time=0.166..0.168 rows=1 loops=21,446)

  • Index Cond: (ts_1.id = timesheetid)
  • Buffers: shared hit=84199 read=2289
62. 4,864.836 4,864.836 ↑ 1.0 1 21,337

Index Scan using uixtahsn on timesheetapprovalhistory ta (cost=0.43..0.53 rows=1 width=12) (actual time=0.227..0.228 rows=1 loops=21,337)

  • Index Cond: (serialnumber = tahls_1.lastsubmitserialnumber)
  • Buffers: shared hit=82331 read=3392
63. 213.370 213.370 ↑ 1.0 1 21,337

Index Scan using timesheet_pkey on timesheet ts_2 (cost=0.42..0.46 rows=1 width=16) (actual time=0.003..0.010 rows=1 loops=21,337)

  • Index Cond: (id = tahls_1.timesheetid)
  • Filter: (approvalstatus = ANY ('{1,2}'::integer[]))
  • Buffers: shared hit=86537 read=111
64. 2.110 6.221 ↑ 1.0 10,841 1

Hash (cost=425.04..425.04 rows=10,904 width=22) (actual time=6.221..6.221 rows=10,841 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 718kB
  • Buffers: shared hit=572
65. 4.111 4.111 ↑ 1.0 10,841 1

Seq Scan on userinfo userinfo2 (cost=0.00..425.04 rows=10,904 width=22) (actual time=0.127..4.111 rows=10,841 loops=1)

  • Buffers: shared hit=572
66. 0.009 0.024 ↑ 1.0 46 1

Hash (cost=1.46..1.46 rows=46 width=520) (actual time=0.024..0.024 rows=46 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=1
67. 0.015 0.015 ↑ 1.0 46 1

Seq Scan on departments dep (cost=0.00..1.46 rows=46 width=520) (actual time=0.007..0.015 rows=46 loops=1)

  • Buffers: shared hit=1
68. 7.730 102.930 ↑ 1.0 27,885 1

Hash (cost=4,155.42..4,155.42 rows=27,958 width=37) (actual time=102.930..102.930 rows=27,885 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 2146kB
  • Buffers: shared hit=3126 read=222
69. 7.069 95.200 ↑ 1.0 27,885 1

Hash Left Join (cost=1,608.29..4,155.42 rows=27,958 width=37) (actual time=21.624..95.200 rows=27,885 loops=1)

  • Hash Cond: (tsar.userid = userinfo14.id)
  • Buffers: shared hit=3126 read=222
70. 13.672 81.616 ↑ 1.0 27,885 1

Hash Join (cost=1,046.95..3,209.74 rows=27,958 width=21) (actual time=15.028..81.616 rows=27,885 loops=1)

  • Hash Cond: (tan.id = tsar.nodeid)
  • Buffers: shared hit=2554 read=222
71. 58.057 58.057 ↓ 1.1 56,512 1

Seq Scan on timesheetapprovalnodes tan (cost=0.00..1,682.70 rows=53,470 width=32) (actual time=4.984..58.057 rows=56,512 loops=1)

  • Buffers: shared hit=1950 read=222
72. 4.568 9.887 ↑ 1.0 27,885 1

Hash (cost=697.48..697.48 rows=27,958 width=20) (actual time=9.887..9.887 rows=27,885 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1673kB
  • Buffers: shared hit=604
73. 5.319 5.319 ↑ 1.0 27,885 1

Seq Scan on tsapprovalrequest tsar (cost=0.00..697.48 rows=27,958 width=20) (actual time=0.119..5.319 rows=27,885 loops=1)

  • Filter: ((systemprocessidentifier IS NULL) OR (systemprocessidentifier <> 'urn:replicon:approval-system-process:timesheet-submit-script-data-and-validation'::text))
  • Rows Removed by Filter: 9
  • Buffers: shared hit=604
74. 2.352 6.515 ↑ 1.0 10,841 1

Hash (cost=425.04..425.04 rows=10,904 width=20) (actual time=6.515..6.515 rows=10,841 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 709kB
  • Buffers: shared hit=572
75. 4.163 4.163 ↑ 1.0 10,841 1

Seq Scan on userinfo userinfo14 (cost=0.00..425.04 rows=10,904 width=20) (actual time=0.115..4.163 rows=10,841 loops=1)

  • Buffers: shared hit=572
76. 0.010 0.022 ↑ 1.8 42 1

Hash (cost=1.76..1.76 rows=76 width=14) (actual time=0.022..0.022 rows=42 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=1
77. 0.012 0.012 ↑ 1.8 42 1

Seq Scan on clients cl (cost=0.00..1.76 rows=76 width=14) (actual time=0.006..0.012 rows=42 loops=1)

  • Buffers: shared hit=1
78. 0.074 0.170 ↓ 1.0 279 1

Hash (cost=11.71..11.71 rows=271 width=61) (actual time=0.170..0.170 rows=279 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 34kB
  • Buffers: shared hit=9
79. 0.096 0.096 ↓ 1.0 279 1

Seq Scan on project pj (cost=0.00..11.71 rows=271 width=61) (actual time=0.004..0.096 rows=279 loops=1)

  • Buffers: shared hit=9
80. 0.212 6.518 ↓ 2.9 1,249 1

Hash (cost=561.34..561.34 rows=436 width=4) (actual time=6.518..6.518 rows=1,249 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 60kB
  • Buffers: shared hit=572
81. 6.306 6.306 ↓ 2.9 1,249 1

Seq Scan on userinfo ui_1 (cost=0.00..561.34 rows=436 width=4) (actual time=0.138..6.306 rows=1,249 loops=1)

  • Filter: (upper((info2)::text) = ANY ('{ARE,ARG,BRA,CHN,COL,IDN,JPN,THA}'::text[]))
  • Rows Removed by Filter: 9592
  • Buffers: shared hit=572
82. 1.969 3.509 ↓ 1.0 10,841 1

Hash (cost=253.37..253.37 rows=10,837 width=23) (actual time=3.509..3.509 rows=10,841 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 722kB
  • Buffers: shared hit=145
83. 1.540 1.540 ↓ 1.0 10,841 1

Seq Scan on login login15 (cost=0.00..253.37 rows=10,837 width=23) (actual time=0.005..1.540 rows=10,841 loops=1)

  • Buffers: shared hit=145
84. 2.238 74.794 ↑ 1.0 10,217 1

Hash (cost=200.35..200.35 rows=10,335 width=28) (actual time=74.794..74.794 rows=10,217 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 727kB
  • Buffers: shared hit=41 read=56
85. 72.556 72.556 ↑ 1.0 10,217 1

Seq Scan on usercostcenter usercostcenter20 (cost=0.00..200.35 rows=10,335 width=28) (actual time=0.008..72.556 rows=10,217 loops=1)

  • Buffers: shared hit=41 read=56
86. 0.007 0.012 ↑ 1.0 2 1

Hash (cost=1.02..1.02 rows=2 width=134) (actual time=0.012..0.012 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
87. 0.005 0.005 ↑ 1.0 2 1

Seq Scan on costcenter costcenter16 (cost=0.00..1.02 rows=2 width=134) (actual time=0.005..0.005 rows=2 loops=1)

  • Buffers: shared hit=1
88. 3.672 153.569 ↑ 1.0 16,151 1

Hash (cost=321.03..321.03 rows=16,403 width=28) (actual time=153.569..153.569 rows=16,151 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1203kB
  • Buffers: shared hit=41 read=116
89. 149.897 149.897 ↑ 1.0 16,151 1

Seq Scan on userdivision userdivision21 (cost=0.00..321.03 rows=16,403 width=28) (actual time=1.011..149.897 rows=16,151 loops=1)

  • Buffers: shared hit=41 read=116
90. 0.177 0.297 ↑ 1.1 874 1

Hash (cost=18.50..18.50 rows=950 width=27) (actual time=0.297..0.297 rows=874 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 59kB
  • Buffers: shared hit=9
91. 0.120 0.120 ↑ 1.1 874 1

Seq Scan on division division17 (cost=0.00..18.50 rows=950 width=27) (actual time=0.006..0.120 rows=874 loops=1)

  • Buffers: shared hit=9
92.          

SubPlan (forHash Left Join)

93. 2,101.540 2,101.540 ↑ 1.0 1 30,022

Index Scan using dm_userworkschedule_facts_pkey on dm_userworkschedule_facts dm_userworkschedule_facts8 (cost=0.43..2.45 rows=1 width=16) (actual time=0.068..0.070 rows=1 loops=30,022)

  • Index Cond: ((userid = at.userid) AND (date = at.entrydate))
  • Buffers: shared hit=115860 read=1503
Planning time : 123.052 ms
Execution time : 1,205,177.954 ms