explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kD6J

Settings
# exclusive inclusive rows x rows loops node
1. 18.984 264,942.386 ↓ 5.5 4,332 1

Sort (cost=614,317.22..614,319.18 rows=781 width=386) (actual time=264,941.388..264,942.386 rows=4,332 loops=1)

  • Sort Key: ts.startdate, ts.enddate, ((ui.lastname)::character varying(50)) COLLATE "en_US", ((ui.firstname)::character varying(50)) COLLATE "en_US", "*SELECT* 1".action, "*SELECT* 1".modifiedonutc, "*SELECT* 1".validuntilutc
  • Sort Method: quicksort Memory: 2402kB
  • Buffers: shared hit=56654838 read=1817692 written=4910
2. 7.679 264,923.402 ↓ 5.5 4,332 1

Nested Loop (cost=2.65..614,279.70 rows=781 width=386) (actual time=4,646.680..264,923.402 rows=4,332 loops=1)

  • Buffers: shared hit=56654828 read=1817689 written=4910
3. 5.761 264,898.395 ↓ 5.5 4,332 1

Nested Loop (cost=2.23..613,923.98 rows=781 width=434) (actual time=4,646.216..264,898.395 rows=4,332 loops=1)

  • Join Filter: ("*SELECT* 1".timesheetid = ts.id)
  • Buffers: shared hit=56637600 read=1817540 written=4910
4. 6.345 264,883.970 ↓ 9.7 4,332 1

Nested Loop (cost=1.80..613,715.13 rows=446 width=410) (actual time=4,646.205..264,883.970 rows=4,332 loops=1)

  • Buffers: shared hit=56620218 read=1817540 written=4910
5. 1,377.035 264,868.961 ↓ 9.7 4,332 1

Nested Loop Semi Join (cost=1.52..613,289.31 rows=446 width=397) (actual time=4,646.191..264,868.961 rows=4,332 loops=1)

  • Buffers: shared hit=56607222 read=1817540 written=4910
6. 559.361 225,339.126 ↓ 1,220.7 1,090,080 1

Append (cost=1.10..601,635.90 rows=893 width=380) (actual time=1,594.065..225,339.126 rows=1,090,080 loops=1)

  • Buffers: shared hit=40176666 read=1812183 written=4910
7. 0.000 0.003 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1 (cost=1.10..85.41 rows=1 width=342) (actual time=0.003..0.003 rows=0 loops=1)

8. 0.001 0.003 ↓ 0.0 0 1

Nested Loop (cost=1.10..85.40 rows=1 width=178) (actual time=0.003..0.003 rows=0 loops=1)

  • Join Filter: (at.rootid = allocatedtime.rootid)
9. 0.000 0.002 ↓ 0.0 0 1

Nested Loop (cost=0.98..83.25 rows=1 width=178) (actual time=0.002..0.002 rows=0 loops=1)

10. 0.001 0.002 ↓ 0.0 0 1

Nested Loop (cost=0.42..12.96 rows=1 width=92) (actual time=0.002..0.002 rows=0 loops=1)

11. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on allocatedtime at (cost=0.00..10.47 rows=1 width=80) (actual time=0.001..0.001 rows=0 loops=1)

  • Filter: ((fromtimestamputc >= '2017-12-31 14:30:00'::timestamp without time zone) AND (fromtimestamputc < '2018-03-02 14:30:00'::timestamp without time zone) AND (CASE WHEN (parentid IS NULL) THEN 100 ELSE 101 END = ANY ('{1,2,3,4,100,101,102}'::integer[])))
12. 0.000 0.000 ↓ 0.0 0

Index Scan using ixtsuseridstartdateenddate on timesheet ts_1 (cost=0.42..2.47 rows=1 width=28) (never executed)

  • Index Cond: ((userid = at.userid) AND (at.entrydate >= startdate) AND (startdate >= '2018-01-01'::date) AND (startdate <= '2018-03-02'::date) AND (at.entrydate <= enddate) AND (enddate >= '2018-01-01'::date) AND (enddate <= '2018-03-02'::date))
13. 0.000 0.000 ↓ 0.0 0

Index Scan using ixtpsstimesheetid on timesheetpolicysettingssnapshot tsformat (cost=0.56..70.28 rows=1 width=102) (never executed)

  • Index Cond: (timesheetid = ts_1.id)
  • Filter: ((upper(uri) <> 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT:GEN4-TIMESHEET'::text) AND (upper(key) = 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT'::text))
14. 0.000 0.000 ↓ 0.0 0

Index Scan using ixallocatedtimerootid on allocatedtime (cost=0.12..2.14 rows=1 width=16) (never executed)

15. 0.001 0.009 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=0.57..14.97 rows=1 width=288) (actual time=0.009..0.009 rows=0 loops=1)

16. 0.007 0.008 ↓ 0.0 0 1

Result (cost=0.57..14.96 rows=1 width=68) (actual time=0.008..0.008 rows=0 loops=1)

  • One-Time Filter: (102 = ANY ('{1,2,3,4,100,101,102}'::integer[]))
17. 0.000 0.001 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.57..14.96 rows=1 width=68) (actual time=0.001..0.001 rows=0 loops=1)

  • Filter: (child_at.id IS NULL)
18. 0.001 0.001 ↓ 0.0 0 1

Nested Loop (cost=0.42..12.79 rows=1 width=68) (actual time=0.001..0.001 rows=0 loops=1)

19. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on allocatedtime at_1 (cost=0.00..10.30 rows=1 width=56) (actual time=0.000..0.000 rows=0 loops=1)

  • Filter: ((totimestamputc IS NOT NULL) AND (totimestamputc >= '2017-12-31 14:30:00'::timestamp without time zone) AND (totimestamputc < '2018-03-02 14:30:00'::timestamp without time zone))
20. 0.000 0.000 ↓ 0.0 0

Index Scan using ixtsuseridstartdateenddate on timesheet ts_2 (cost=0.42..2.47 rows=1 width=28) (never executed)

  • Index Cond: ((userid = at_1.userid) AND (at_1.entrydate >= startdate) AND (startdate >= '2018-01-01'::date) AND (startdate <= '2018-03-02'::date) AND (at_1.entrydate <= enddate) AND (enddate >= '2018-01-01'::date) AND (enddate <= '2018-03-02'::date))
21. 0.000 0.000 ↓ 0.0 0

Index Scan using uixallocatedtime_parent on allocatedtime child_at (cost=0.14..2.16 rows=1 width=32) (never executed)

  • Index Cond: (parentid = at_1.id)
22. 241.377 64,974.123 ↓ 8,909.9 418,765 1

Subquery Scan on *SELECT* 3 (cost=2,852.41..6,608.69 rows=47 width=346) (actual time=1,594.052..64,974.123 rows=418,765 loops=1)

  • Buffers: shared hit=8079934 read=469965 written=519
23. 954.466 64,732.746 ↓ 8,909.9 418,765 1

Nested Loop Left Join (cost=2,852.41..6,608.22 rows=47 width=206) (actual time=1,594.050..64,732.746 rows=418,765 loops=1)

  • Buffers: shared hit=8079934 read=469965 written=519
24. 605.652 61,265.690 ↓ 13,958.8 418,765 1

Nested Loop Left Join (cost=2,851.72..6,498.19 rows=30 width=161) (actual time=1,594.037..61,265.690 rows=418,765 loops=1)

  • Buffers: shared hit=5541886 read=462903 written=519
25. 304.423 54,797.328 ↓ 22,040.3 418,765 1

Nested Loop Left Join (cost=2,851.04..6,428.49 rows=19 width=100) (actual time=1,593.592..54,797.328 rows=418,765 loops=1)

  • Buffers: shared hit=3029355 read=436257 written=519
26. 259.197 5,497.400 ↓ 22,040.3 418,765 1

Nested Loop (cost=2,850.35..6,377.53 rows=19 width=100) (actual time=1,592.730..5,497.400 rows=418,765 loops=1)

  • Buffers: shared hit=1160096 read=189958 written=519
27. 73.977 2,763.371 ↓ 763.1 17,552 1

Nested Loop (cost=2,849.92..2,935.86 rows=23 width=28) (actual time=1,592.692..2,763.371 rows=17,552 loops=1)

  • Buffers: shared hit=789254 read=180587 written=519
28. 304.295 1,722.424 ↓ 5,525.5 193,394 1

HashAggregate (cost=2,849.50..2,849.85 rows=35 width=16) (actual time=1,592.483..1,722.424 rows=193,394 loops=1)

  • Group Key: timesheetpolicysettingssnapshot.timesheetid
  • Buffers: shared hit=14257 read=180328 written=519
29. 1,418.129 1,418.129 ↓ 70.6 193,394 1

Index Scan using ixtspss2key on timesheetpolicysettingssnapshot (cost=0.69..2,842.64 rows=2,741 width=16) (actual time=0.120..1,418.129 rows=193,394 loops=1)

  • Index Cond: ((upper(key) = 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT'::text) AND (upper(uri) = 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT:GEN4-TIMESHEET'::text))
  • Buffers: shared hit=14257 read=180328 written=519
30. 966.970 966.970 ↓ 0.0 0 193,394

Index Scan using timesheet_pkey on timesheet ts_3 (cost=0.42..2.45 rows=1 width=28) (actual time=0.005..0.005 rows=0 loops=193,394)

  • Index Cond: (id = timesheetpolicysettingssnapshot.timesheetid)
  • Filter: ((startdate >= '2018-01-01'::date) AND (startdate <= '2018-03-02'::date) AND (enddate >= '2018-01-01'::date) AND (enddate <= '2018-03-02'::date))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=774997 read=259
31. 2,474.832 2,474.832 ↓ 24.0 24 17,552

Index Scan using ixter2userid on timeentryrevision rev (cost=0.43..149.63 rows=1 width=88) (actual time=0.061..0.141 rows=24 loops=17,552)

  • Index Cond: ((userid = ts_3.userid) AND (entrydate >= ts_3.startdate) AND (entrydate <= ts_3.enddate))
  • Filter: ((fromtimestamputc >= '2017-12-31 14:30:00'::timestamp without time zone) AND (fromtimestamputc < '2018-03-02 14:30:00'::timestamp without time zone) AND (CASE WHEN (previousrevisionid IS NULL) THEN 100 ELSE 101 END = ANY ('{1,2,3,4,100,101,102}'::integer[])))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=370842 read=9371
32. 48,995.505 48,995.505 ↓ 0.0 0 418,765

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue changereason (cost=0.69..2.67 rows=1 width=16) (actual time=0.117..0.117 rows=0 loops=418,765)

  • Index Cond: ((revisionauditid = rev.openingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:COMMENT'::text))
  • Buffers: shared hit=1869259 read=246299
33. 5,862.710 5,862.710 ↑ 2.0 1 418,765

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue effectiveuser (cost=0.69..3.65 rows=2 width=77) (actual time=0.013..0.014 rows=1 loops=418,765)

  • Index Cond: ((revisionauditid = rev.openingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:EFFECTIVE-USER'::text))
  • Buffers: shared hit=2512531 read=26646
34. 2,512.590 2,512.590 ↑ 2.0 1 418,765

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue actualuser (cost=0.69..3.65 rows=2 width=77) (actual time=0.006..0.006 rows=1 loops=418,765)

  • Index Cond: ((revisionauditid = rev.openingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:ACTUAL-USER'::text))
  • Buffers: shared hit=2538048 read=7062
35. 260.593 49,385.586 ↓ 484,353.0 484,353 1

Subquery Scan on *SELECT* 4 (cost=2,852.72..62,470.75 rows=1 width=346) (actual time=1,468.730..49,385.586 rows=484,353 loops=1)

  • Buffers: shared hit=13104664 read=405960 written=3295
36. 378.331 49,124.993 ↓ 484,353.0 484,353 1

Result (cost=2,852.72..62,470.74 rows=1 width=206) (actual time=1,468.729..49,124.993 rows=484,353 loops=1)

  • One-Time Filter: (101 = ANY ('{1,2,3,4,100,101,102}'::integer[]))
  • Buffers: shared hit=13104664 read=405960 written=3295
37. 552.584 48,746.662 ↓ 484,353.0 484,353 1

Nested Loop Left Join (cost=2,852.72..62,470.74 rows=1 width=206) (actual time=1,468.719..48,746.662 rows=484,353 loops=1)

  • Buffers: shared hit=13104664 read=405960 written=3295
38. 454.058 46,256.666 ↓ 484,353.0 484,353 1

Nested Loop Left Join (cost=2,852.02..62,468.18 rows=1 width=161) (actual time=1,468.712..46,256.666 rows=484,353 loops=1)

  • Buffers: shared hit=10162597 read=403773 written=3295
39. 478.591 41,927.784 ↓ 484,353.0 484,353 1

Nested Loop Anti Join (cost=2,851.34..62,465.62 rows=1 width=100) (actual time=1,468.693..41,927.784 rows=484,353 loops=1)

  • Buffers: shared hit=7299900 read=329232 written=3295
40. 891.219 37,487.928 ↓ 903.4 792,253 1

Nested Loop (cost=2,850.91..61,205.10 rows=877 width=116) (actual time=1,466.691..37,487.928 rows=792,253 loops=1)

  • Buffers: shared hit=4131802 read=319542 written=3295
41. 288.356 3,459.493 ↓ 62.3 436,016 1

Nested Loop (cost=2,850.35..6,344.92 rows=6,998 width=52) (actual time=1,465.038..3,459.493 rows=436,016 loops=1)

  • Buffers: shared hit=1149680 read=200374 written=3295
42. 171.776 2,539.265 ↓ 763.1 17,552 1

Nested Loop (cost=2,849.92..2,935.86 rows=23 width=28) (actual time=1,465.024..2,539.265 rows=17,552 loops=1)

  • Buffers: shared hit=775526 read=194315 written=3295
43. 285.263 1,593.913 ↓ 5,525.5 193,394 1

HashAggregate (cost=2,849.50..2,849.85 rows=35 width=16) (actual time=1,464.898..1,593.913 rows=193,394 loops=1)

  • Group Key: timesheetpolicysettingssnapshot_1.timesheetid
  • Buffers: shared hit=342 read=194243 written=3295
44. 1,308.650 1,308.650 ↓ 70.6 193,394 1

Index Scan using ixtspss2key on timesheetpolicysettingssnapshot timesheetpolicysettingssnapshot_1 (cost=0.69..2,842.64 rows=2,741 width=16) (actual time=0.072..1,308.650 rows=193,394 loops=1)

  • Index Cond: ((upper(key) = 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT'::text) AND (upper(uri) = 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT:GEN4-TIMESHEET'::text))
  • Buffers: shared hit=342 read=194243 written=3295
45. 773.576 773.576 ↓ 0.0 0 193,394

Index Scan using timesheet_pkey on timesheet ts_4 (cost=0.42..2.45 rows=1 width=28) (actual time=0.004..0.004 rows=0 loops=193,394)

  • Index Cond: (id = timesheetpolicysettingssnapshot_1.timesheetid)
  • Filter: ((startdate >= '2018-01-01'::date) AND (startdate <= '2018-03-02'::date) AND (enddate >= '2018-01-01'::date) AND (enddate <= '2018-03-02'::date))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=775184 read=72
46. 631.872 631.872 ↑ 10.6 25 17,552

Index Scan using ixter2userid on timeentryrevision rev_1 (cost=0.43..145.58 rows=264 width=40) (actual time=0.008..0.036 rows=25 loops=17,552)

  • Index Cond: ((userid = ts_4.userid) AND (entrydate >= ts_4.startdate) AND (entrydate <= ts_4.enddate))
  • Buffers: shared hit=374154 read=6059
47. 33,137.216 33,137.216 ↓ 2.0 2 436,016

Index Scan using ixtemrtimeentryid on timeentrymetadatarevision mdrev (cost=0.56..7.83 rows=1 width=80) (actual time=0.062..0.076 rows=2 loops=436,016)

  • Index Cond: (timeentryid = rev_1.timeentryid)
  • Filter: ((fromtimestamputc >= '2017-12-31 14:30:00'::timestamp without time zone) AND (fromtimestamputc < '2018-03-02 14:30:00'::timestamp without time zone) AND (rev_1.fromtimestamputc <= fromtimestamputc) AND ((rev_1.totimestamputc IS NULL) OR (rev_1.totimestamputc > fromtimestamputc)))
  • Rows Removed by Filter: 5
  • Buffers: shared hit=2982122 read=119168
48. 3,961.265 3,961.265 ↓ 0.0 0 792,253

Index Scan using timeentryrevision_singleinitialrevision on timeentryrevision (cost=0.43..1.43 rows=1 width=24) (actual time=0.005..0.005 rows=0 loops=792,253)

  • Index Cond: (timeentryid = mdrev.timeentryid)
  • Filter: (fromtimestamputc = mdrev.fromtimestamputc)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=3168098 read=9690
49. 3,874.824 3,874.824 ↑ 2.0 1 484,353

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue effectiveuser_1 (cost=0.69..2.54 rows=2 width=77) (actual time=0.007..0.008 rows=1 loops=484,353)

  • Index Cond: ((revisionauditid = mdrev.openingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:EFFECTIVE-USER'::text))
  • Buffers: shared hit=2862697 read=74541
50. 1,937.412 1,937.412 ↑ 2.0 1 484,353

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue actualuser_1 (cost=0.69..2.54 rows=2 width=77) (actual time=0.003..0.004 rows=1 loops=484,353)

  • Index Cond: ((revisionauditid = mdrev.openingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:ACTUAL-USER'::text))
  • Buffers: shared hit=2942067 read=2187
51. 0.406 10,779.179 ↓ 1.2 542 1

Subquery Scan on *SELECT* 5 (cost=2,855.92..76,521.82 rows=457 width=346) (actual time=1,682.234..10,779.179 rows=542 loops=1)

  • Buffers: shared hit=7974671 read=229590 written=834
52. 0.767 10,778.773 ↓ 1.2 542 1

Result (cost=2,855.92..76,517.25 rows=457 width=182) (actual time=1,682.232..10,778.773 rows=542 loops=1)

  • One-Time Filter: (101 = ANY ('{1,2,3,4,100,101,102}'::integer[]))
  • Buffers: shared hit=7974671 read=229590 written=834
53. 0.877 10,778.006 ↓ 1.2 542 1

Nested Loop Anti Join (cost=2,855.92..76,517.25 rows=457 width=182) (actual time=1,682.222..10,778.006 rows=542 loops=1)

  • Buffers: shared hit=7974671 read=229590 written=834
54. 1.063 10,531.603 ↓ 1.2 542 1

Nested Loop Left Join (cost=2,855.04..73,885.07 rows=457 width=198) (actual time=1,680.743..10,531.603 rows=542 loops=1)

  • Buffers: shared hit=7970564 read=228365 written=834
55. 1.005 10,528.372 ↓ 1.9 542 1

Nested Loop Left Join (cost=2,854.35..73,039.90 rows=290 width=153) (actual time=1,680.728..10,528.372 rows=542 loops=1)

  • Buffers: shared hit=7967279 read=228363 written=834
56. 490.144 10,521.947 ↓ 2.9 542 1

Nested Loop Anti Join (cost=2,853.67..72,503.66 rows=184 width=92) (actual time=1,680.708..10,521.947 rows=542 loops=1)

  • Buffers: shared hit=7964063 read=228298 written=834
57. 507.154 6,379.495 ↓ 1,389.8 405,812 1

Nested Loop (cost=2,850.91..71,397.37 rows=292 width=153) (actual time=1,554.883..6,379.495 rows=405,812 loops=1)

  • Buffers: shared hit=4226489 read=224855 written=834
58. 244.339 3,256.245 ↓ 62.3 436,016 1

Nested Loop (cost=2,850.35..6,845.56 rows=6,998 width=52) (actual time=1,554.850..3,256.245 rows=436,016 loops=1)

  • Buffers: shared hit=1151008 read=199046 written=834
59. 27.975 2,467.794 ↓ 763.1 17,552 1

Nested Loop (cost=2,849.92..2,935.86 rows=23 width=28) (actual time=1,554.837..2,467.794 rows=17,552 loops=1)

  • Buffers: shared hit=775525 read=194316 written=834
60. 328.655 1,666.243 ↓ 5,525.5 193,394 1

HashAggregate (cost=2,849.50..2,849.85 rows=35 width=16) (actual time=1,554.713..1,666.243 rows=193,394 loops=1)

  • Group Key: timesheetpolicysettingssnapshot_2.timesheetid
  • Buffers: shared hit=340 read=194245 written=834
61. 1,337.588 1,337.588 ↓ 70.6 193,394 1

Index Scan using ixtspss2key on timesheetpolicysettingssnapshot timesheetpolicysettingssnapshot_2 (cost=0.69..2,842.64 rows=2,741 width=16) (actual time=0.102..1,337.588 rows=193,394 loops=1)

  • Index Cond: ((upper(key) = 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT'::text) AND (upper(uri) = 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT:GEN4-TIMESHEET'::text))
  • Buffers: shared hit=340 read=194245 written=834
62. 773.576 773.576 ↓ 0.0 0 193,394

Index Scan using timesheet_pkey on timesheet ts_5 (cost=0.42..2.45 rows=1 width=28) (actual time=0.004..0.004 rows=0 loops=193,394)

  • Index Cond: (id = timesheetpolicysettingssnapshot_2.timesheetid)
  • Filter: ((startdate >= '2018-01-01'::date) AND (startdate <= '2018-03-02'::date) AND (enddate >= '2018-01-01'::date) AND (enddate <= '2018-03-02'::date))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=775185 read=71
63. 544.112 544.112 ↑ 10.6 25 17,552

Index Scan using ixter2userid on timeentryrevision rev_2 (cost=0.43..167.35 rows=264 width=40) (actual time=0.007..0.031 rows=25 loops=17,552)

  • Index Cond: ((userid = ts_5.userid) AND (entrydate >= ts_5.startdate) AND (entrydate <= ts_5.enddate))
  • Buffers: shared hit=375483 read=4730
64. 2,616.096 2,616.096 ↑ 1.0 1 436,016

Index Scan using ixtemrtimeentryid on timeentrymetadatarevision mdrev_1 (cost=0.56..9.21 rows=1 width=117) (actual time=0.005..0.006 rows=1 loops=436,016)

  • Index Cond: (timeentryid = rev_2.timeentryid)
  • Filter: ((totimestamputc IS NOT NULL) AND (totimestamputc >= '2017-12-31 14:30:00'::timestamp without time zone) AND (totimestamputc < '2018-03-02 14:30:00'::timestamp without time zone) AND (rev_2.fromtimestamputc <= totimestamputc) AND ((rev_2.totimestamputc IS NULL) OR (rev_2.totimestamputc > totimestamputc)))
  • Rows Removed by Filter: 6
  • Buffers: shared hit=3075481 read=25809
65. 405.812 3,652.308 ↑ 1.0 1 405,812

Bitmap Heap Scan on timeentrymetadatarevision (cost=2.76..3.78 rows=1 width=85) (actual time=0.009..0.009 rows=1 loops=405,812)

  • Recheck Cond: ((fromtimestamputc = mdrev_1.totimestamputc) AND (timeentryid = mdrev_1.timeentryid))
  • Filter: ((key = mdrev_1.key) AND (openingauditid = mdrev_1.closingauditid))
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=448640
  • Buffers: shared hit=3737574 read=3443
66. 405.821 3,246.496 ↓ 0.0 0 405,812

BitmapAnd (cost=2.76..2.76 rows=1 width=0) (actual time=0.008..0.008 rows=0 loops=405,812)

  • Buffers: shared hit=3289220 read=3157
67. 1,623.248 1,623.248 ↑ 2.7 3 405,812

Bitmap Index Scan on ixtemr2fromtimestamputc (cost=0.00..1.23 rows=8 width=0) (actual time=0.004..0.004 rows=3 loops=405,812)

  • Index Cond: (fromtimestamputc = mdrev_1.totimestamputc)
  • Buffers: shared hit=1625934 read=3097
68. 1,217.427 1,217.427 ↑ 1.3 10 405,809

Bitmap Index Scan on ixtemrtimeentryid (cost=0.00..1.28 rows=13 width=0) (actual time=0.003..0.003 rows=10 loops=405,809)

  • Index Cond: (timeentryid = mdrev_1.timeentryid)
  • Buffers: shared hit=1663286 read=60
69. 5.420 5.420 ↑ 2.0 1 542

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue effectiveuser_2 (cost=0.69..2.89 rows=2 width=77) (actual time=0.009..0.010 rows=1 loops=542)

  • Index Cond: ((revisionauditid = mdrev_1.openingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:EFFECTIVE-USER'::text))
  • Buffers: shared hit=3216 read=65
70. 2.168 2.168 ↑ 2.0 1 542

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue actualuser_2 (cost=0.69..2.89 rows=2 width=77) (actual time=0.004..0.004 rows=1 loops=542)

  • Index Cond: ((revisionauditid = mdrev_1.openingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:ACTUAL-USER'::text))
  • Buffers: shared hit=3285 read=2
71. 1.626 245.526 ↓ 0.0 0 542

Nested Loop Left Join (cost=0.86..5.74 rows=1 width=24) (actual time=0.453..0.453 rows=0 loops=542)

  • Filter: (child_rev.id IS NULL)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=4107 read=1225
72. 231.976 231.976 ↑ 1.0 1 542

Index Scan using ixtertimeentryid on timeentryrevision rev_3 (cost=0.43..4.10 rows=1 width=40) (actual time=0.418..0.428 rows=1 loops=542)

  • Index Cond: (timeentryid = mdrev_1.timeentryid)
  • Filter: (totimestamputc = mdrev_1.totimestamputc)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=2519 read=634
73. 11.924 11.924 ↑ 1.0 1 542

Index Scan using timeentryrevision_uniquepreviousrevisionid on timeentryrevision child_rev (cost=0.43..1.63 rows=1 width=32) (actual time=0.022..0.022 rows=1 loops=542)

  • Index Cond: (previousrevisionid = rev_3.id)
  • Buffers: shared hit=1588 read=591
74. 69.964 17,942.305 ↓ 124,307.0 124,307 1

Subquery Scan on *SELECT* 6 (cost=2,852.59..26,209.32 rows=1 width=346) (actual time=1,093.541..17,942.305 rows=124,307 loops=1)

  • Buffers: shared hit=4976700 read=314216 written=37
75. 108.219 17,872.341 ↓ 124,307.0 124,307 1

Result (cost=2,852.59..26,209.31 rows=1 width=206) (actual time=1,093.540..17,872.341 rows=124,307 loops=1)

  • One-Time Filter: (101 = ANY ('{1,2,3,4,100,101,102}'::integer[]))
  • Buffers: shared hit=4976700 read=314216 written=37
76. 217.869 17,764.122 ↓ 124,307.0 124,307 1

Nested Loop Left Join (cost=2,852.59..26,209.31 rows=1 width=206) (actual time=1,093.529..17,764.122 rows=124,307 loops=1)

  • Buffers: shared hit=4976700 read=314216 written=37
77. 196.881 17,049.025 ↓ 124,307.0 124,307 1

Nested Loop Left Join (cost=2,851.89..26,206.41 rows=1 width=161) (actual time=1,093.523..17,049.025 rows=124,307 loops=1)

  • Buffers: shared hit=4223851 read=311397 written=37
78. 114.951 14,614.618 ↓ 124,307.0 124,307 1

Nested Loop Anti Join (cost=2,851.21..26,203.53 rows=1 width=100) (actual time=1,093.491..14,614.618 rows=124,307 loops=1)

  • Buffers: shared hit=3579290 read=202090 written=37
79. 132.309 13,960.547 ↓ 563.9 134,780 1

Nested Loop (cost=2,850.78..25,878.18 rows=239 width=116) (actual time=1,093.475..13,960.547 rows=134,780 loops=1)

  • Buffers: shared hit=3040928 read=199606 written=37
80. 266.159 2,927.838 ↓ 62.3 436,016 1

Nested Loop (cost=2,850.35..5,907.60 rows=6,998 width=52) (actual time=1,091.058..2,927.838 rows=436,016 loops=1)

  • Buffers: shared hit=1176623 read=173431 written=37
81. 130.796 2,117.567 ↓ 763.1 17,552 1

Nested Loop (cost=2,849.92..2,935.86 rows=23 width=28) (actual time=1,091.044..2,117.567 rows=17,552 loops=1)

  • Buffers: shared hit=798014 read=171827 written=37
82. 271.622 1,213.195 ↓ 5,525.5 193,394 1

HashAggregate (cost=2,849.50..2,849.85 rows=35 width=16) (actual time=1,090.932..1,213.195 rows=193,394 loops=1)

  • Group Key: timesheetpolicysettingssnapshot_3.timesheetid
  • Buffers: shared hit=22766 read=171819 written=37
83. 941.573 941.573 ↓ 70.6 193,394 1

Index Scan using ixtspss2key on timesheetpolicysettingssnapshot timesheetpolicysettingssnapshot_3 (cost=0.69..2,842.64 rows=2,741 width=16) (actual time=0.095..941.573 rows=193,394 loops=1)

  • Index Cond: ((upper(key) = 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT'::text) AND (upper(uri) = 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT:GEN4-TIMESHEET'::text))
  • Buffers: shared hit=22766 read=171819 written=37
84. 773.576 773.576 ↓ 0.0 0 193,394

Index Scan using timesheet_pkey on timesheet ts_6 (cost=0.42..2.45 rows=1 width=28) (actual time=0.004..0.004 rows=0 loops=193,394)

  • Index Cond: (id = timesheetpolicysettingssnapshot_3.timesheetid)
  • Filter: ((startdate >= '2018-01-01'::date) AND (startdate <= '2018-03-02'::date) AND (enddate >= '2018-01-01'::date) AND (enddate <= '2018-03-02'::date))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=775248 read=8
85. 544.112 544.112 ↑ 10.6 25 17,552

Index Scan using ixter2userid on timeentryrevision rev_4 (cost=0.43..126.57 rows=264 width=40) (actual time=0.007..0.031 rows=25 loops=17,552)

  • Index Cond: ((userid = ts_6.userid) AND (entrydate >= ts_6.startdate) AND (entrydate <= ts_6.enddate))
  • Buffers: shared hit=378609 read=1604
86. 10,900.400 10,900.400 ↓ 0.0 0 436,016

Index Scan using ixoefvrobjectid on objectextensionfieldvaluerevision oefvr (cost=0.43..2.84 rows=1 width=80) (actual time=0.023..0.025 rows=0 loops=436,016)

  • Index Cond: (objectid = rev_4.timeentryid)
  • Filter: ((fromtimestamputc >= '2017-12-31 14:30:00'::timestamp without time zone) AND (fromtimestamputc < '2018-03-02 14:30:00'::timestamp without time zone) AND (rev_4.fromtimestamputc <= fromtimestamputc) AND ((rev_4.totimestamputc IS NULL) OR (rev_4.totimestamputc > fromtimestamputc)))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=1864305 read=26175
87. 539.120 539.120 ↓ 0.0 0 134,780

Index Scan using timeentryrevision_singleinitialrevision on timeentryrevision timeentryrevision_1 (cost=0.43..1.35 rows=1 width=24) (actual time=0.004..0.004 rows=0 loops=134,780)

  • Index Cond: (timeentryid = oefvr.objectid)
  • Filter: (fromtimestamputc = oefvr.fromtimestamputc)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=538362 read=2484
88. 2,237.526 2,237.526 ↑ 2.0 1 124,307

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue effectiveuser_3 (cost=0.69..2.86 rows=2 width=77) (actual time=0.018..0.018 rows=1 loops=124,307)

  • Index Cond: ((revisionauditid = oefvr.openingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:EFFECTIVE-USER'::text))
  • Buffers: shared hit=644561 read=109307
89. 497.228 497.228 ↑ 2.0 1 124,307

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue actualuser_3 (cost=0.69..2.86 rows=2 width=77) (actual time=0.004..0.004 rows=1 loops=124,307)

  • Index Cond: ((revisionauditid = oefvr.openingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:ACTUAL-USER'::text))
  • Buffers: shared hit=752849 read=2819
90. 0.896 6,594.420 ↓ 21.0 1,178 1

Subquery Scan on *SELECT* 7 (cost=2,853.46..29,275.92 rows=56 width=346) (actual time=1,308.901..6,594.420 rows=1,178 loops=1)

  • Buffers: shared hit=3325082 read=201440 written=225
91. 1.602 6,593.524 ↓ 21.0 1,178 1

Result (cost=2,853.46..29,275.36 rows=56 width=182) (actual time=1,308.899..6,593.524 rows=1,178 loops=1)

  • One-Time Filter: (101 = ANY ('{1,2,3,4,100,101,102}'::integer[]))
  • Buffers: shared hit=3325082 read=201440 written=225
92. 1.448 6,591.922 ↓ 21.0 1,178 1

Nested Loop Anti Join (cost=2,853.46..29,275.36 rows=56 width=182) (actual time=1,308.889..6,591.922 rows=1,178 loops=1)

  • Buffers: shared hit=3325082 read=201440 written=225
93. 2.221 5,365.354 ↓ 21.0 1,178 1

Nested Loop Left Join (cost=2,852.58..28,945.56 rows=56 width=198) (actual time=1,306.840..5,365.354 rows=1,178 loops=1)

  • Buffers: shared hit=3314130 read=198860 written=225
94. 2.378 5,357.243 ↓ 32.7 1,178 1

Nested Loop Left Join (cost=2,851.90..28,815.23 rows=36 width=153) (actual time=1,306.833..5,357.243 rows=1,178 loops=1)

  • Buffers: shared hit=3306988 read=198838 written=225
95. 63.063 5,337.195 ↓ 51.2 1,178 1

Nested Loop Anti Join (cost=2,851.21..28,731.97 rows=23 width=92) (actual time=1,306.814..5,337.195 rows=1,178 loops=1)

  • Buffers: shared hit=3300320 read=198362 written=225
96. 498.135 4,885.524 ↓ 2,491.1 64,768 1

Nested Loop (cost=2,850.78..28,667.64 rows=26 width=124) (actual time=1,281.685..4,885.524 rows=64,768 loops=1)

  • Buffers: shared hit=3042769 read=197765 written=225
97. 268.872 3,079.341 ↓ 62.3 436,016 1

Nested Loop (cost=2,850.35..6,168.27 rows=6,998 width=52) (actual time=1,281.651..3,079.341 rows=436,016 loops=1)

  • Buffers: shared hit=1152607 read=197447 written=225
98. 96.030 2,266.357 ↓ 763.1 17,552 1

Nested Loop (cost=2,849.92..2,935.86 rows=23 width=28) (actual time=1,281.638..2,266.357 rows=17,552 loops=1)

  • Buffers: shared hit=775799 read=194042 written=225
99. 292.139 1,396.751 ↓ 5,525.5 193,394 1

HashAggregate (cost=2,849.50..2,849.85 rows=35 width=16) (actual time=1,281.511..1,396.751 rows=193,394 loops=1)

  • Group Key: timesheetpolicysettingssnapshot_4.timesheetid
  • Buffers: shared hit=581 read=194004 written=225
100. 1,104.612 1,104.612 ↓ 70.6 193,394 1

Index Scan using ixtspss2key on timesheetpolicysettingssnapshot timesheetpolicysettingssnapshot_4 (cost=0.69..2,842.64 rows=2,741 width=16) (actual time=0.113..1,104.612 rows=193,394 loops=1)

  • Index Cond: ((upper(key) = 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT'::text) AND (upper(uri) = 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT:GEN4-TIMESHEET'::text))
  • Buffers: shared hit=581 read=194004 written=225
101. 773.576 773.576 ↓ 0.0 0 193,394

Index Scan using timesheet_pkey on timesheet ts_7 (cost=0.42..2.45 rows=1 width=28) (actual time=0.004..0.004 rows=0 loops=193,394)

  • Index Cond: (id = timesheetpolicysettingssnapshot_4.timesheetid)
  • Filter: ((startdate >= '2018-01-01'::date) AND (startdate <= '2018-03-02'::date) AND (enddate >= '2018-01-01'::date) AND (enddate <= '2018-03-02'::date))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=775218 read=38
102. 544.112 544.112 ↑ 10.6 25 17,552

Index Scan using ixter2userid on timeentryrevision rev_5 (cost=0.43..137.90 rows=264 width=40) (actual time=0.007..0.031 rows=25 loops=17,552)

  • Index Cond: ((userid = ts_7.userid) AND (entrydate >= ts_7.startdate) AND (entrydate <= ts_7.enddate))
  • Buffers: shared hit=376808 read=3405
103. 1,308.048 1,308.048 ↓ 0.0 0 436,016

Index Scan using ixoefvrobjectid on objectextensionfieldvaluerevision oefvr_1 (cost=0.43..3.21 rows=1 width=88) (actual time=0.003..0.003 rows=0 loops=436,016)

  • Index Cond: (objectid = rev_5.timeentryid)
  • Filter: ((totimestamputc IS NOT NULL) AND (totimestamputc >= '2017-12-31 14:30:00'::timestamp without time zone) AND (totimestamputc < '2018-03-02 14:30:00'::timestamp without time zone) AND (rev_5.fromtimestamputc <= totimestamputc) AND ((rev_5.totimestamputc IS NULL) OR (rev_5.totimestamputc > totimestamputc)))
  • Rows Removed by Filter: 2
  • Buffers: shared hit=1890162 read=318
104. 388.608 388.608 ↑ 1.0 1 64,768

Index Scan using ixoefvr2fromtimestamputc on objectextensionfieldvaluerevision (cost=0.43..2.46 rows=1 width=56) (actual time=0.006..0.006 rows=1 loops=64,768)

  • Index Cond: (fromtimestamputc = oefvr_1.totimestamputc)
  • Filter: ((objectid = oefvr_1.objectid) AND (definitionid = oefvr_1.definitionid) AND (openingauditid = oefvr_1.closingauditid))
  • Buffers: shared hit=257551 read=597
105. 17.670 17.670 ↑ 2.0 1 1,178

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue effectiveuser_4 (cost=0.69..3.60 rows=2 width=77) (actual time=0.015..0.015 rows=1 loops=1,178)

  • Index Cond: ((revisionauditid = oefvr_1.openingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:EFFECTIVE-USER'::text))
  • Buffers: shared hit=6668 read=476
106. 5.890 5.890 ↑ 2.0 1 1,178

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue actualuser_4 (cost=0.69..3.60 rows=2 width=77) (actual time=0.005..0.005 rows=1 loops=1,178)

  • Index Cond: ((revisionauditid = oefvr_1.openingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:ACTUAL-USER'::text))
  • Buffers: shared hit=7142 read=22
107. 3.856 1,225.120 ↓ 0.0 0 1,178

Nested Loop Left Join (cost=0.86..5.79 rows=1 width=24) (actual time=1.040..1.040 rows=0 loops=1,178)

  • Filter: (child_rev_1.id IS NULL)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=10952 read=2580
108. 636.120 636.120 ↑ 1.0 1 1,178

Index Scan using ixtertimeentryid on timeentryrevision rev_6 (cost=0.43..4.43 rows=1 width=40) (actual time=0.529..0.540 rows=1 loops=1,178)

  • Index Cond: (timeentryid = oefvr_1.objectid)
  • Filter: (totimestamputc = oefvr_1.totimestamputc)
  • Rows Removed by Filter: 4
  • Buffers: shared hit=7506 read=1362
109. 585.144 585.144 ↑ 1.0 1 1,161

Index Scan using timeentryrevision_uniquepreviousrevisionid on timeentryrevision child_rev_1 (cost=0.43..1.35 rows=1 width=32) (actual time=0.500..0.504 rows=1 loops=1,161)

  • Index Cond: (previousrevisionid = rev_6.id)
  • Buffers: shared hit=3446 read=1218
110. 7.157 15,290.119 ↓ 11,015.0 11,015 1

Subquery Scan on *SELECT* 8 (cost=172,657.27..208,084.02 rows=1 width=346) (actual time=1,781.428..15,290.119 rows=11,015 loops=1)

  • Buffers: shared hit=1578847 read=56522
111. 10.713 15,282.962 ↓ 11,015.0 11,015 1

Result (cost=172,657.27..208,084.01 rows=1 width=182) (actual time=1,781.425..15,282.962 rows=11,015 loops=1)

  • One-Time Filter: (102 = ANY ('{1,2,3,4,100,101,102}'::integer[]))
  • Buffers: shared hit=1578847 read=56522
112. 12.709 15,272.249 ↓ 11,015.0 11,015 1

Nested Loop Left Join (cost=172,657.27..208,084.01 rows=1 width=182) (actual time=1,781.414..15,272.249 rows=11,015 loops=1)

  • Buffers: shared hit=1578847 read=56522
113. 15.454 15,204.465 ↓ 11,015.0 11,015 1

Nested Loop Left Join (cost=172,656.57..208,081.68 rows=1 width=198) (actual time=1,781.406..15,204.465 rows=11,015 loops=1)

  • Buffers: shared hit=1523110 read=56522
114. 20.822 15,111.906 ↓ 11,015.0 11,015 1

Nested Loop Left Join (cost=172,655.89..208,078.76 rows=1 width=137) (actual time=1,781.398..15,111.906 rows=11,015 loops=1)

  • Buffers: shared hit=1456175 read=56352
115. 198.177 13,956.539 ↓ 11,015.0 11,015 1

Nested Loop Left Join (cost=172,655.20..208,075.84 rows=1 width=76) (actual time=1,781.371..13,956.539 rows=11,015 loops=1)

  • Filter: (child_rev_2.id IS NULL)
  • Rows Removed by Filter: 278500
  • Buffers: shared hit=1395938 read=49718
116. 1,074.351 3,625.337 ↓ 15.8 289,515 1

Merge Join (cost=172,654.77..181,170.53 rows=18,337 width=76) (actual time=1,670.329..3,625.337 rows=289,515 loops=1)

  • Merge Cond: (ts_8.userid = rev_7.userid)
  • Join Filter: ((rev_7.entrydate >= ts_8.startdate) AND (rev_7.entrydate <= ts_8.enddate))
  • Rows Removed by Join Filter: 2917253
  • Buffers: shared hit=265917 read=28558
117. 99.621 99.621 ↓ 10.9 17,551 1

Index Scan using ixtsuseridstartdateenddate on timesheet ts_8 (cost=0.42..4,973.30 rows=1,606 width=28) (actual time=1.884..99.621 rows=17,551 loops=1)

  • Index Cond: ((startdate >= '2018-01-01'::date) AND (startdate <= '2018-03-02'::date) AND (enddate >= '2018-01-01'::date) AND (enddate <= '2018-03-02'::date))
  • Buffers: shared hit=16988 read=841
118. 935.268 2,451.365 ↓ 15.1 3,206,780 1

Sort (cost=172,654.35..173,186.04 rows=212,676 width=64) (actual time=1,668.368..2,451.365 rows=3,206,780 loops=1)

  • Sort Key: rev_7.userid
  • Sort Method: quicksort Memory: 53640kB
  • Buffers: shared hit=248929 read=27717
119. 1,516.097 1,516.097 ↓ 1.4 294,055 1

Index Scan using ixter2fromtimestamputc on timeentryrevision rev_7 (cost=0.43..153,834.33 rows=212,676 width=64) (actual time=273.506..1,516.097 rows=294,055 loops=1)

  • Index Cond: ((totimestamputc IS NOT NULL) AND (totimestamputc >= '2017-12-31 14:30:00'::timestamp without time zone) AND (totimestamputc < '2018-03-02 14:30:00'::timestamp without time zone))
  • Buffers: shared hit=248929 read=27717
120. 10,133.025 10,133.025 ↑ 1.0 1 289,515

Index Scan using timeentryrevision_uniquepreviousrevisionid on timeentryrevision child_rev_2 (cost=0.43..1.46 rows=1 width=32) (actual time=0.034..0.035 rows=1 loops=289,515)

  • Index Cond: (previousrevisionid = rev_7.id)
  • Buffers: shared hit=1130021 read=21160
121. 1,134.545 1,134.545 ↑ 2.0 1 11,015

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue effectiveuser_5 (cost=0.69..2.90 rows=2 width=77) (actual time=0.101..0.103 rows=1 loops=11,015)

  • Index Cond: ((revisionauditid = rev_7.closingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:EFFECTIVE-USER'::text))
  • Buffers: shared hit=60237 read=6634
122. 77.105 77.105 ↑ 2.0 1 11,015

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue actualuser_5 (cost=0.69..2.90 rows=2 width=77) (actual time=0.007..0.007 rows=1 loops=11,015)

  • Index Cond: ((revisionauditid = rev_7.closingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:ACTUAL-USER'::text))
  • Buffers: shared hit=66935 read=170
123. 55.075 55.075 ↓ 0.0 0 11,015

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue changereason_1 (cost=0.69..2.31 rows=1 width=16) (actual time=0.005..0.005 rows=0 loops=11,015)

  • Index Cond: ((revisionauditid = rev_7.closingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:COMMENT'::text))
  • Buffers: shared hit=55737
124. 33.038 59,814.021 ↓ 152.2 49,920 1

Subquery Scan on *SELECT* 9 (cost=124,405.96..192,364.99 rows=328 width=438) (actual time=9,387.547..59,814.021 rows=49,920 loops=1)

  • Buffers: shared hit=1136768 read=134490
125. 131.560 59,780.983 ↓ 152.2 49,920 1

Nested Loop (cost=124,405.96..192,361.71 rows=328 width=310) (actual time=9,387.546..59,780.983 rows=49,920 loops=1)

  • Join Filter: (ts_9.id = tsformat_1.timesheetid)
  • Buffers: shared hit=1136768 read=134490
126. 67.491 39,731.343 ↓ 152.7 49,920 1

Nested Loop Left Join (cost=124,405.40..185,126.69 rows=327 width=240) (actual time=9,385.752..39,731.343 rows=49,920 loops=1)

  • Buffers: shared hit=784465 read=99688
127. 92.743 33,324.012 ↓ 152.7 49,920 1

Nested Loop Left Join (cost=124,404.96..180,452.31 rows=327 width=185) (actual time=9,385.738..33,324.012 rows=49,920 loops=1)

  • Buffers: shared hit=574447 read=77500
128. 71.998 10,068.389 ↓ 152.7 49,920 1

Hash Join (cost=124,404.53..175,774.65 rows=327 width=114) (actual time=9,383.516..10,068.389 rows=49,920 loops=1)

  • Hash Cond: (tah.timesheetid = ts_9.id)
  • Buffers: shared hit=396581 read=23160
129. 694.761 9,969.674 ↓ 1.3 51,640 1

Seq Scan on timesheetapprovalhistory tah (cost=119,411.15..170,631.37 rows=39,101 width=94) (actual time=9,356.778..9,969.674 rows=51,640 loops=1)

  • Filter: ((NOT (hashed SubPlan 9)) AND (timestamputc >= '2017-12-31 14:30:00'::timestamp without time zone) AND (timestamputc < '2018-03-02 14:30:00'::timestamp without time zone) AND (COALESCE(upper(systemprocessidentifier), ''::text) <> 'URN:REPLICON:APPROVAL-SYSTEM-PROCESS:TIMESHEET-SUBMIT-SCRIPT-DATA-AND-VALIDATION'::text) AND (action = ANY ('{1,2,3,4,100,101,102}'::integer[])))
  • Rows Removed by Filter: 921514
  • Buffers: shared hit=378751 read=23097
130.          

SubPlan (forSeq Scan)

131. 90.233 9,274.913 ↓ 115,648.0 115,648 1

Nested Loop (cost=118,707.57..119,411.15 rows=1 width=16) (actual time=8,478.505..9,274.913 rows=115,648 loops=1)

  • Buffers: shared hit=358088 read=23032
132. 132.922 8,895.560 ↓ 57,824.0 57,824 1

Hash Join (cost=118,707.15..119,408.68 rows=1 width=24) (actual time=8,478.485..8,895.560 rows=57,824 loops=1)

  • Hash Cond: ((faas.timesheetid = tah_3.timesheetid) AND (faas.nextaction_serialnumber = tah_3.serialnumber))
  • Buffers: shared hit=110200 read=23032
133.          

CTE firstactionaftersubmit

134. 657.638 8,429.833 ↓ 10.0 254,938 1

HashAggregate (cost=102,351.68..102,606.78 rows=25,510 width=24) (actual time=8,288.004..8,429.833 rows=254,938 loops=1)

  • Group Key: submissions.timesheetid, submissions.serialnumber
  • Buffers: shared hit=90082 read=22825
135.          

CTE submissions

136. 4,406.839 4,406.839 ↑ 1.0 254,938 1

Index Scan using ixtahaction on timesheetapprovalhistory (cost=0.42..21,045.44 rows=255,098 width=20) (actual time=2.316..4,406.839 rows=254,938 loops=1)

  • Index Cond: (action = 1)
  • Buffers: shared hit=19729 read=18029
137. 744.609 7,772.195 ↓ 2.5 1,121,138 1

Merge Join (cost=28,011.07..77,893.07 rows=455,090 width=24) (actual time=4,780.567..7,772.195 rows=1,121,138 loops=1)

  • Merge Cond: (submissions.timesheetid = tah_1.timesheetid)
  • Join Filter: (tah_1.serialnumber > submissions.serialnumber)
  • Rows Removed by Join Filter: 527883
  • Buffers: shared hit=90082 read=22825
138. 284.799 4,874.963 ↑ 1.0 254,938 1

Sort (cost=28,010.64..28,648.39 rows=255,098 width=20) (actual time=4,780.008..4,874.963 rows=254,938 loops=1)

  • Sort Key: submissions.timesheetid
  • Sort Method: quicksort Memory: 26062kB
  • Buffers: shared hit=19729 read=18029
139. 4,590.164 4,590.164 ↑ 1.0 254,938 1

CTE Scan on submissions (cost=0.00..5,101.96 rows=255,098 width=20) (actual time=2.319..4,590.164 rows=254,938 loops=1)

  • Buffers: shared hit=19729 read=18029
140. 529.323 2,152.623 ↓ 1.7 1,649,334 1

Materialize (cost=0.42..27,154.07 rows=975,751 width=20) (actual time=0.539..2,152.623 rows=1,649,334 loops=1)

  • Buffers: shared hit=70353 read=4796
141. 1,623.300 1,623.300 ↑ 1.0 973,154 1

Index Only Scan using ixtah2timesheetid on timesheetapprovalhistory tah_1 (cost=0.42..24,714.69 rows=975,751 width=20) (actual time=0.534..1,623.300 rows=973,154 loops=1)

  • Heap Fetches: 84012
  • Buffers: shared hit=70353 read=4796
142. 8,572.209 8,572.209 ↓ 10.0 254,938 1

CTE Scan on firstactionaftersubmit faas (cost=0.00..510.20 rows=25,510 width=24) (actual time=8,288.006..8,572.209 rows=254,938 loops=1)

  • Buffers: shared hit=90082 read=22825
143. 21.807 190.429 ↓ 182.4 57,829 1

Hash (cost=16,095.61..16,095.61 rows=317 width=20) (actual time=190.429..190.429 rows=57,829 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3449kB
  • Buffers: shared hit=20118 read=207
144. 168.622 168.622 ↓ 182.4 57,829 1

Index Scan using ixtahaction on timesheetapprovalhistory tah_3 (cost=0.42..16,095.61 rows=317 width=20) (actual time=1.414..168.622 rows=57,829 loops=1)

  • Index Cond: (action = 3)
  • Filter: (upper(systemprocessidentifier) = 'URN:REPLICON:APPROVAL-SYSTEM-PROCESS:TIMESHEET-SUBMIT-SCRIPT-DATA-AND-VALIDATION'::text)
  • Rows Removed by Filter: 6476
  • Buffers: shared hit=20118 read=207
145. 289.120 289.120 ↓ 2.0 2 57,824

Index Scan using ixtah2timesheetid on timesheetapprovalhistory tah_2 (cost=0.42..2.45 rows=1 width=36) (actual time=0.004..0.005 rows=2 loops=57,824)

  • Index Cond: ((timesheetid = faas.timesheetid) AND (serialnumber >= faas.submission_serialnumber) AND (serialnumber <= faas.nextaction_serialnumber))
  • Buffers: shared hit=247888
146. 6.030 26.717 ↓ 10.9 17,552 1

Hash (cost=4,973.30..4,973.30 rows=1,606 width=20) (actual time=26.717..26.717 rows=17,552 loops=1)

  • Buckets: 32768 (originally 2048) Batches: 1 (originally 1) Memory Usage: 1148kB
  • Buffers: shared hit=17830 read=63
147. 20.687 20.687 ↓ 10.9 17,552 1

Index Scan using ixtsuseridstartdateenddate on timesheet ts_9 (cost=0.42..4,973.30 rows=1,606 width=20) (actual time=0.011..20.687 rows=17,552 loops=1)

  • Index Cond: ((startdate >= '2018-01-01'::date) AND (startdate <= '2018-03-02'::date) AND (enddate >= '2018-01-01'::date) AND (enddate <= '2018-03-02'::date))
  • Buffers: shared hit=17830 read=63
148. 23,162.880 23,162.880 ↑ 2.0 1 49,920

Index Scan using ixtahkvtimesheetapprovalhistoryid on timesheetapprovalhistorykeyvalue kv_ea (cost=0.43..14.28 rows=2 width=87) (actual time=0.444..0.464 rows=1 loops=49,920)

  • Index Cond: (timesheetapprovalhistoryid = tah.id)
  • Filter: (upper(key) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:ACTING-EFFECTIVE-USER'::text)
  • Rows Removed by Filter: 14
  • Buffers: shared hit=177866 read=54340
149. 6,339.840 6,339.840 ↑ 1.0 1 49,920

Index Scan using ixtahkvtimesheetapprovalhistoryid on timesheetapprovalhistorykeyvalue kv_aa (cost=0.43..14.28 rows=1 width=87) (actual time=0.030..0.127 rows=1 loops=49,920)

  • Index Cond: (timesheetapprovalhistoryid = tah.id)
  • Filter: (upper(key) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:ACTING-ACTUAL-USER'::text)
  • Rows Removed by Filter: 14
  • Buffers: shared hit=210018 read=22188
150. 19,918.080 19,918.080 ↑ 1.0 1 49,920

Index Scan using ixtpsstimesheetid on timesheetpolicysettingssnapshot tsformat_1 (cost=0.56..22.11 rows=1 width=102) (actual time=0.184..0.399 rows=1 loops=49,920)

  • Index Cond: (timesheetid = tah.timesheetid)
  • Filter: (upper(key) = 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT'::text)
  • Rows Removed by Filter: 68
  • Buffers: shared hit=352303 read=34802
151. 4,486.694 38,152.800 ↓ 0.0 0 1,090,080

Index Scan using timesheet_pkey on timesheet timesheet3 (cost=0.42..15.04 rows=1 width=16) (actual time=0.035..0.035 rows=0 loops=1,090,080)

  • Index Cond: (id = "*SELECT* 1".timesheetid)
  • Filter: ((userid = 1300) OR (alternatives: SubPlan 1 or hashed SubPlan 2) OR (alternatives: SubPlan 3 or hashed SubPlan 4) OR (alternatives: SubPlan 5 or hashed SubPlan 6))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=16430556 read=5357
152.          

SubPlan (forIndex Scan)

153. 2,179.414 2,179.414 ↓ 0.0 0 1,089,707

Index Scan using ix3uh_usersuperstart on userhierarchy userhierarchy4 (cost=0.28..2.31 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=1,089,707)

  • Index Cond: ((userid = timesheet3.userid) AND (supervisorid = 1300) AND (startdate <= '2019-04-03'::date))
  • Filter: (enddate >= '2019-04-03'::date)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=2190110 read=1
154. 0.000 0.000 ↓ 0.0 0

Index Scan using ixuhsupervisorid on userhierarchy userhierarchy4_1 (cost=0.28..10.79 rows=4 width=4) (never executed)

  • Index Cond: (supervisorid = 1300)
  • Filter: ((startdate <= '2019-04-03'::date) AND (enddate >= '2019-04-03'::date))
155. 1,085.748 2,171.496 ↓ 0.0 0 1,085,748

Hash Join (cost=1.73..5.82 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=1,085,748)

  • Hash Cond: (timesheetapprovalnodes6.id = tsapprovalrequest5.nodeid)
  • Buffers: shared hit=2204422
156. 1,085.748 1,085.748 ↓ 0.0 0 1,085,748

Index Scan using ixtantimesheetid on timesheetapprovalnodes timesheetapprovalnodes6 (cost=0.28..4.33 rows=3 width=16) (actual time=0.001..0.001 rows=0 loops=1,085,748)

  • Index Cond: (timesheetid = timesheet3.id)
  • Buffers: shared hit=2204422
157. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.38..1.38 rows=6 width=16) (never executed)

158. 0.000 0.000 ↓ 0.0 0

Index Only Scan using uix2tsar_nodeid_userid on tsapprovalrequest tsapprovalrequest5 (cost=0.28..1.38 rows=6 width=16) (never executed)

  • Index Cond: (userid = 1300)
  • Heap Fetches: 0
159. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.55..15.21 rows=6 width=16) (never executed)

160. 0.000 0.000 ↓ 0.0 0

Index Only Scan using uix2tsar_nodeid_userid on tsapprovalrequest tsapprovalrequest5_1 (cost=0.28..1.38 rows=6 width=16) (never executed)

  • Index Cond: (userid = 1300)
  • Heap Fetches: 0
161. 0.000 0.000 ↓ 0.0 0

Index Scan using timesheetapprovalnodes_pkey on timesheetapprovalnodes timesheetapprovalnodes6_1 (cost=0.28..2.29 rows=1 width=32) (never executed)

  • Index Cond: (id = tsapprovalrequest5_1.nodeid)
162. 1,085.748 29,315.196 ↓ 0.0 0 1,085,748

Bitmap Heap Scan on timesheetapprovalhistory timesheetapprovalhistory7 (cost=3.98..4.99 rows=1 width=0) (actual time=0.027..0.027 rows=0 loops=1,085,748)

  • Recheck Cond: ((timesheetid = timesheet3.id) AND (userid = 1300))
  • Buffers: shared hit=7666658 read=5356
163. 2,178.216 28,229.448 ↓ 0.0 0 1,085,748

BitmapAnd (cost=3.98..3.98 rows=1 width=0) (actual time=0.026..0.026 rows=0 loops=1,085,748)

  • Buffers: shared hit=7666658 read=5356
164. 4,342.992 4,342.992 ↑ 1.0 5 1,085,748

Bitmap Index Scan on ixtah2timesheetid (cost=0.00..1.46 rows=5 width=0) (actual time=0.004..0.004 rows=5 loops=1,085,748)

  • Index Cond: (timesheetid = timesheet3.id)
  • Buffers: shared hit=3325010 read=5356
165. 21,708.240 21,708.240 ↓ 2.2 245 1,085,412

Bitmap Index Scan on ixtahuserid (cost=0.00..2.26 rows=112 width=0) (actual time=0.020..0.020 rows=245 loops=1,085,412)

  • Index Cond: (userid = 1300)
  • Buffers: shared hit=4341648
166. 0.000 0.000 ↓ 0.0 0

Index Scan using ixtahuserid on timesheetapprovalhistory timesheetapprovalhistory7_1 (cost=0.42..115.29 rows=112 width=16) (never executed)

  • Index Cond: (userid = 1300)
167. 8.664 8.664 ↑ 1.0 1 4,332

Index Scan using userinfo_pkey on userinfo ui (cost=0.28..0.94 rows=1 width=17) (actual time=0.002..0.002 rows=1 loops=4,332)

  • Index Cond: (id = "*SELECT* 1".userid)
  • Buffers: shared hit=12996
168. 8.664 8.664 ↑ 1.0 1 4,332

Index Scan using timesheet_pkey on timesheet ts (cost=0.42..0.46 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=4,332)

  • Index Cond: (id = timesheet3.id)
  • Buffers: shared hit=17382
169. 17.328 17.328 ↑ 1.0 1 4,332

Index Only Scan using dm_timesheetlist_facts_pkey on dm_timesheetlist_facts tslist (cost=0.42..0.45 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=4,332)

  • Index Cond: (timesheetid = ts.id)
  • Heap Fetches: 4332
  • Buffers: shared hit=17228 read=149