explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9woe

Settings
# exclusive inclusive rows x rows loops node
1. 18.450 278,888.149 ↓ 5.5 4,332 1

Sort (cost=614,317.22..614,319.18 rows=781 width=386) (actual time=278,887.158..278,888.149 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=56621003 read=1851514 written=5497
2. 6.586 278,869.699 ↓ 5.5 4,332 1

Nested Loop (cost=2.65..614,279.70 rows=781 width=386) (actual time=3,303.558..278,869.699 rows=4,332 loops=1)

  • Buffers: shared hit=56621003 read=1851514 written=5497
3. 7.210 278,841.453 ↓ 5.5 4,332 1

Nested Loop (cost=2.23..613,923.98 rows=781 width=434) (actual time=3,303.101..278,841.453 rows=4,332 loops=1)

  • Join Filter: ("*SELECT* 1".timesheetid = ts.id)
  • Buffers: shared hit=56603801 read=1851339 written=5497
4. 4.226 278,825.579 ↓ 9.7 4,332 1

Nested Loop (cost=1.80..613,715.13 rows=446 width=410) (actual time=3,303.090..278,825.579 rows=4,332 loops=1)

  • Buffers: shared hit=56586419 read=1851339 written=5497
5. 1,530.329 278,808.357 ↓ 9.7 4,332 1

Nested Loop Semi Join (cost=1.52..613,289.31 rows=446 width=397) (actual time=3,303.082..278,808.357 rows=4,332 loops=1)

  • Buffers: shared hit=56573424 read=1851338 written=5497
6. 596.296 238,035.148 ↓ 1,220.7 1,090,080 1

Append (cost=1.10..601,635.90 rows=893 width=380) (actual time=1,224.526..238,035.148 rows=1,090,080 loops=1)

  • Buffers: shared hit=40142952 read=1845897 written=5497
7. 0.001 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.000 0.002 ↓ 0.0 0 1

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

  • Join Filter: (at.rootid = allocatedtime.rootid)
9. 0.001 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.000 0.001 ↓ 0.0 0 1

Nested Loop (cost=0.42..12.96 rows=1 width=92) (actual time=0.001..0.001 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) TH (...)
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- (...)
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.007 ↓ 0.0 0 1

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

16. 0.005 0.006 ↓ 0.0 0 1

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

  • One-Time Filter: (102 = ANY ('{1,2,3,4,100,101,102}'::integer[]))
17. 0.001 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.000 0.000 ↓ 0.0 0 1

Nested Loop (cost=0.42..12.79 rows=1 width=68) (actual time=0.000..0.000 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 >= (...)
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. 274.790 70,394.706 ↓ 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,224.515..70,394.706 rows=418,765 loops=1)

  • Buffers: shared hit=8090310 read=459589 written=2113
23. 700.046 70,119.916 ↓ 8,909.9 418,765 1

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

  • Buffers: shared hit=8090310 read=459589 written=2113
24. 670.458 66,069.750 ↓ 13,958.8 418,765 1

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

  • Buffers: shared hit=5552097 read=452692 written=2113
25. 387.134 60,374.112 ↓ 22,040.3 418,765 1

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

  • Buffers: shared hit=3033972 read=431640 written=2113
26. 261.172 3,872.468 ↓ 22,040.3 418,765 1

Nested Loop (cost=2,850.35..6,377.53 rows=19 width=100) (actual time=1,224.022..3,872.468 rows=418,765 loops=1)

  • Buffers: shared hit=1167473 read=182581 written=2113
27. 52.533 2,382.656 ↓ 763.1 17,552 1

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

  • Buffers: shared hit=792963 read=176878 written=2113
28. 293.807 1,363.153 ↓ 5,525.5 193,394 1

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

  • Group Key: timesheetpolicysettingssnapshot.timesheetid
  • Buffers: shared hit=17780 read=176805 written=2113
29. 1,069.346 1,069.346 ↓ 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.074..1,069.346 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=17780 read=176805 written=2113
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=775183 read=73
31. 1,228.640 1,228.640 ↓ 24.0 24 17,552

Index Scan using ixter2userid on timeentryrevision rev (cost=0.43..149.63 rows=1 width=88) (actual time=0.014..0.070 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 (previousrevisi (...)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=374510 read=5703
32. 56,114.510 56,114.510 ↓ 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.134..0.134 rows=0 loops=418,765)

  • Index Cond: ((revisionauditid = rev.openingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:COMMENT'::text))
  • Buffers: shared hit=1866499 read=249059
33. 5,025.180 5,025.180 ↑ 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.010..0.012 rows=1 loops=418,765)

  • Index Cond: ((revisionauditid = rev.openingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:EFFECTIVE-USER'::text))
  • Buffers: shared hit=2518125 read=21052
34. 3,350.120 3,350.120 ↑ 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.007..0.008 rows=1 loops=418,765)

  • Index Cond: ((revisionauditid = rev.openingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:ACTUAL-USER'::text))
  • Buffers: shared hit=2538213 read=6897
35. 276.011 51,905.089 ↓ 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,270.985..51,905.089 rows=484,353 loops=1)

  • Buffers: shared hit=13082172 read=428452 written=1593
36. 395.359 51,629.078 ↓ 484,353.0 484,353 1

Result (cost=2,852.72..62,470.74 rows=1 width=206) (actual time=1,270.984..51,629.078 rows=484,353 loops=1)

  • One-Time Filter: (101 = ANY ('{1,2,3,4,100,101,102}'::integer[]))
  • Buffers: shared hit=13082172 read=428452 written=1593
37. 534.430 51,233.719 ↓ 484,353.0 484,353 1

Nested Loop Left Join (cost=2,852.72..62,470.74 rows=1 width=206) (actual time=1,270.975..51,233.719 rows=484,353 loops=1)

  • Buffers: shared hit=13082172 read=428452 written=1593
38. 766.687 48,761.877 ↓ 484,353.0 484,353 1

Nested Loop Left Join (cost=2,852.02..62,468.18 rows=1 width=161) (actual time=1,270.968..48,761.877 rows=484,353 loops=1)

  • Buffers: shared hit=10141064 read=425306 written=1593
39. 851.486 44,120.366 ↓ 484,353.0 484,353 1

Nested Loop Anti Join (cost=2,851.34..62,465.62 rows=1 width=100) (actual time=1,270.943..44,120.366 rows=484,353 loops=1)

  • Buffers: shared hit=7299061 read=330071 written=1593
40. 577.221 39,307.615 ↓ 903.4 792,253 1

Nested Loop (cost=2,850.91..61,205.10 rows=877 width=116) (actual time=1,268.980..39,307.615 rows=792,253 loops=1)

  • Buffers: shared hit=4130976 read=320368 written=1593
41. 264.327 3,413.098 ↓ 62.3 436,016 1

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

  • Buffers: shared hit=1148854 read=201200 written=1593
42. 154.969 2,323.827 ↓ 763.1 17,552 1

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

  • Buffers: shared hit=775541 read=194300 written=1593
43. 1,395.282 1,395.282 ↓ 5,525.5 193,394 1

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

  • Group Key: timesheetpolicysettingssnapshot_1.timesheetid
  • Buffers: shared hit=366 read=194219 written=1593
  • -> Index Scan using ixtspss2key on timesheetpolicysettingssnapshot timesheetpolicysettingssnapshot_1 (cost=0.69..2842.64 rows=2741 width=16) (actual time=0.092..1115.66 (...)
  • Index Cond: ((upper(key) = 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT'::text) AND (upper(uri) = 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT:GEN4-TIMESHEET' (...)
  • Buffers: shared hit=366 read=194219 written=1593
44. 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=775175 read=81
45. 824.944 824.944 ↑ 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.011..0.047 rows=25 loops=17,552)

  • Index Cond: ((userid = ts_4.userid) AND (entrydate >= ts_4.startdate) AND (entrydate <= ts_4.enddate))
  • Buffers: shared hit=373313 read=6900
46. 35,317.296 35,317.296 ↓ 2.0 2 436,016

Index Scan using ixtemrtimeentryid on timeentrymetadatarevision mdrev (cost=0.56..7.83 rows=1 width=80) (actual time=0.066..0.081 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.fromtimestamp (...)
  • Rows Removed by Filter: 5
  • Buffers: shared hit=2982122 read=119168
47. 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=3168085 read=9703
48. 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.008..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=2842003 read=95235
49. 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=2941108 read=3146
50. 0.502 11,462.611 ↓ 1.2 542 1

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

  • Buffers: shared hit=7986689 read=217572 written=418
51. 0.880 11,462.109 ↓ 1.2 542 1

Result (cost=2,855.92..76,517.25 rows=457 width=182) (actual time=1,305.704..11,462.109 rows=542 loops=1)

  • One-Time Filter: (101 = ANY ('{1,2,3,4,100,101,102}'::integer[]))
  • Buffers: shared hit=7986689 read=217572 written=418
52. 0.799 11,461.229 ↓ 1.2 542 1

Nested Loop Anti Join (cost=2,855.92..76,517.25 rows=457 width=182) (actual time=1,305.693..11,461.229 rows=542 loops=1)

  • Buffers: shared hit=7986689 read=217572 written=418
53. 1.096 11,139.566 ↓ 1.2 542 1

Nested Loop Left Join (cost=2,855.04..73,885.07 rows=457 width=198) (actual time=1,303.256..11,139.566 rows=542 loops=1)

  • Buffers: shared hit=7982581 read=216348 written=418
54. 1.221 11,135.760 ↓ 1.9 542 1

Nested Loop Left Join (cost=2,854.35..73,039.90 rows=290 width=153) (actual time=1,303.242..11,135.760 rows=542 loops=1)

  • Buffers: shared hit=7979296 read=216346 written=418
55. 574.169 11,128.577 ↓ 2.9 542 1

Nested Loop Anti Join (cost=2,853.67..72,503.66 rows=184 width=92) (actual time=1,303.221..11,128.577 rows=542 loops=1)

  • Buffers: shared hit=7976074 read=216287 written=418
56. 388.358 6,496.288 ↓ 1,389.8 405,812 1

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

  • Buffers: shared hit=4238283 read=213061 written=418
57. 276.102 3,055.818 ↓ 62.3 436,016 1

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

  • Buffers: shared hit=1152396 read=197658 written=418
58. 144.422 2,218.052 ↓ 763.1 17,552 1

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

  • Buffers: shared hit=775569 read=194272 written=418
59. 1,300.054 1,300.054 ↓ 5,525.5 193,394 1

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

  • Group Key: timesheetpolicysettingssnapshot_2.timesheetid
  • Buffers: shared hit=357 read=194228 written=418
  • -> Index Scan using ixtspss2key on timesheetpolicysettingssnapshot timesheetpolicysettingssnapshot_2 (cost=0.69..2842.64 rows=2741 width=16) (actual time=0.083..1 (...)
  • Index Cond: ((upper(key) = 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT'::text) AND (upper(uri) = 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT:GEN4-TIME (...)
  • Buffers: shared hit=357 read=194228 written=418
60. 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=775212 read=44
61. 561.664 561.664 ↑ 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.032 rows=25 loops=17,552)

  • Index Cond: ((userid = ts_5.userid) AND (entrydate >= ts_5.startdate) AND (entrydate <= ts_5.enddate))
  • Buffers: shared hit=376827 read=3386
62. 3,052.112 3,052.112 ↑ 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.006..0.007 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 (...)
  • Rows Removed by Filter: 6
  • Buffers: shared hit=3085887 read=15403
63. 405.812 4,058.120 ↑ 1.0 1 405,812

Bitmap Heap Scan on timeentrymetadatarevision (cost=2.76..3.78 rows=1 width=85) (actual time=0.010..0.010 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=3737791 read=3226
64. 811.633 3,652.308 ↓ 0.0 0 405,812

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

  • Buffers: shared hit=3289264 read=3113
65. 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
66. 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=1663330 read=16
67. 5.962 5.962 ↑ 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.011..0.011 rows=1 loops=542)

  • Index Cond: ((revisionauditid = mdrev_1.openingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:EFFECTIVE-USER'::text))
  • Buffers: shared hit=3222 read=59
68. 2.710 2.710 ↑ 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.005 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
69. 2.168 320.864 ↓ 0.0 0 542

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

  • Filter: (child_rev.id IS NULL)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=4108 read=1224
70. 273.710 273.710 ↑ 1.0 1 542

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

  • Index Cond: (timeentryid = mdrev_1.timeentryid)
  • Filter: (totimestamputc = mdrev_1.totimestamputc)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=2520 read=633
71. 44.986 44.986 ↑ 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.083..0.083 rows=1 loops=542)

  • Index Cond: (previousrevisionid = rev_3.id)
  • Buffers: shared hit=1588 read=591
72. 75.270 19,225.727 ↓ 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,231.372..19,225.727 rows=124,307 loops=1)

  • Buffers: shared hit=4941240 read=349676 written=297
73. 114.431 19,150.457 ↓ 124,307.0 124,307 1

Result (cost=2,852.59..26,209.31 rows=1 width=206) (actual time=1,231.370..19,150.457 rows=124,307 loops=1)

  • One-Time Filter: (101 = ANY ('{1,2,3,4,100,101,102}'::integer[]))
  • Buffers: shared hit=4941240 read=349676 written=297
74. 127.263 19,036.026 ↓ 124,307.0 124,307 1

Nested Loop Left Join (cost=2,852.59..26,209.31 rows=1 width=206) (actual time=1,231.359..19,036.026 rows=124,307 loops=1)

  • Buffers: shared hit=4941240 read=349676 written=297
75. 163.590 18,287.228 ↓ 124,307.0 124,307 1

Nested Loop Left Join (cost=2,851.89..26,206.41 rows=1 width=161) (actual time=1,231.352..18,287.228 rows=124,307 loops=1)

  • Buffers: shared hit=4188648 read=346600 written=297
76. 148.658 15,264.577 ↓ 124,307.0 124,307 1

Nested Loop Anti Join (cost=2,851.21..26,203.53 rows=1 width=100) (actual time=1,231.328..15,264.577 rows=124,307 loops=1)

  • Buffers: shared hit=3553822 read=227558 written=297
77. 170.754 14,576.799 ↓ 563.9 134,780 1

Nested Loop (cost=2,850.78..25,878.18 rows=239 width=116) (actual time=1,231.314..14,576.799 rows=134,780 loops=1)

  • Buffers: shared hit=3016382 read=224152 written=297
78. 266.636 3,069.629 ↓ 62.3 436,016 1

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

  • Buffers: shared hit=1152077 read=197977 written=297
79. 118.620 2,241.329 ↓ 763.1 17,552 1

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

  • Buffers: shared hit=775602 read=194239 written=297
80. 1,349.133 1,349.133 ↓ 5,525.5 193,394 1

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

  • Group Key: timesheetpolicysettingssnapshot_3.timesheetid
  • Buffers: shared hit=390 read=194195 written=297
  • -> Index Scan using ixtspss2key on timesheetpolicysettingssnapshot timesheetpolicysettingssnapshot_3 (cost=0.69..2842.64 rows=2741 width=16) (actual time=0.091..1065.40 (...)
  • Index Cond: ((upper(key) = 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT'::text) AND (upper(uri) = 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT:GEN4-TIMESHEET' (...)
  • Buffers: shared hit=390 read=194195 written=297
81. 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=775212 read=44
82. 561.664 561.664 ↑ 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.032 rows=25 loops=17,552)

  • Index Cond: ((userid = ts_6.userid) AND (entrydate >= ts_6.startdate) AND (entrydate <= ts_6.enddate))
  • Buffers: shared hit=376475 read=3738
83. 11,336.416 11,336.416 ↓ 0.0 0 436,016

Index Scan using ixoefvrobjectid on objectextensionfieldvaluerevision oefvr (cost=0.43..2.84 rows=1 width=80) (actual time=0.024..0.026 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.fromtimestamp (...)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=1864305 read=26175
84. 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=537440 read=3406
85. 2,859.061 2,859.061 ↑ 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.022..0.023 rows=1 loops=124,307)

  • Index Cond: ((revisionauditid = oefvr.openingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:EFFECTIVE-USER'::text))
  • Buffers: shared hit=634826 read=119042
86. 621.535 621.535 ↑ 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.005 rows=1 loops=124,307)

  • Index Cond: ((revisionauditid = oefvr.openingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:ACTUAL-USER'::text))
  • Buffers: shared hit=752592 read=3076
87. 0.914 6,417.960 ↓ 21.0 1,178 1

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

  • Buffers: shared hit=3326037 read=200485 written=1076
88. 1.691 6,417.046 ↓ 21.0 1,178 1

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

  • One-Time Filter: (101 = ANY ('{1,2,3,4,100,101,102}'::integer[]))
  • Buffers: shared hit=3326037 read=200485 written=1076
89. 1.816 6,415.355 ↓ 21.0 1,178 1

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

  • Buffers: shared hit=3326037 read=200485 written=1076
90. 1.777 4,992.871 ↓ 21.0 1,178 1

Nested Loop Left Join (cost=2,852.58..28,945.56 rows=56 width=198) (actual time=1,261.181..4,992.871 rows=1,178 loops=1)

  • Buffers: shared hit=3315172 read=197818 written=1076
91. 2.495 4,985.204 ↓ 32.7 1,178 1

Nested Loop Left Join (cost=2,851.90..28,815.23 rows=36 width=153) (actual time=1,261.175..4,985.204 rows=1,178 loops=1)

  • Buffers: shared hit=3308028 read=197798 written=1076
92. 17.630 4,966.217 ↓ 51.2 1,178 1

Nested Loop Anti Join (cost=2,851.21..28,731.97 rows=23 width=92) (actual time=1,261.156..4,966.217 rows=1,178 loops=1)

  • Buffers: shared hit=3301047 read=197635 written=1076
93. 271.878 4,495.211 ↓ 2,491.1 64,768 1

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

  • Buffers: shared hit=3043494 read=197040 written=1076
94. 243.672 2,915.285 ↓ 62.3 436,016 1

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

  • Buffers: shared hit=1153135 read=196919 written=1076
95. 45.726 2,162.605 ↓ 763.1 17,552 1

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

  • Buffers: shared hit=775809 read=194032 written=1076
96. 1,343.303 1,343.303 ↓ 5,525.5 193,394 1

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

  • Group Key: timesheetpolicysettingssnapshot_4.timesheetid
  • Buffers: shared hit=588 read=193997 written=1076
  • -> Index Scan using ixtspss2key on timesheetpolicysettingssnapshot timesheetpolicysettingssnapshot_4 (cost=0.69..2842.64 rows=2741 width=16) (actual time=0.089..1 (...)
  • Index Cond: ((upper(key) = 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT'::text) AND (upper(uri) = 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT:GEN4-TIME (...)
  • Buffers: shared hit=588 read=193997 written=1076
97. 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=775221 read=35
98. 509.008 509.008 ↑ 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.006..0.029 rows=25 loops=17,552)

  • Index Cond: ((userid = ts_7.userid) AND (entrydate >= ts_7.startdate) AND (entrydate <= ts_7.enddate))
  • Buffers: shared hit=377326 read=2887
99. 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 (...)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=1890359 read=121
100. 453.376 453.376 ↑ 1.0 1 64,768

Index Scan using ixoefvr2fromtimestamputc on objectextensionfieldvaluerevision (cost=0.43..2.46 rows=1 width=56) (actual time=0.007..0.007 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=257553 read=595
101. 16.492 16.492 ↑ 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.014..0.014 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=6981 read=163
102. 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.004..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=7144 read=20
103. 4.089 1,420.668 ↓ 0.0 0 1,178

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

  • Filter: (child_rev_1.id IS NULL)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=10865 read=2667
104. 735.072 735.072 ↑ 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.593..0.624 rows=1 loops=1,178)

  • Index Cond: (timeentryid = oefvr_1.objectid)
  • Filter: (totimestamputc = oefvr_1.totimestamputc)
  • Rows Removed by Filter: 4
  • Buffers: shared hit=7469 read=1399
105. 681.507 681.507 ↑ 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.581..0.587 rows=1 loops=1,161)

  • Index Cond: (previousrevisionid = rev_6.id)
  • Buffers: shared hit=3396 read=1268
106. 6.843 15,653.845 ↓ 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,797.394..15,653.845 rows=11,015 loops=1)

  • Buffers: shared hit=1579693 read=55676
107. 10.646 15,647.002 ↓ 11,015.0 11,015 1

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

  • One-Time Filter: (102 = ANY ('{1,2,3,4,100,101,102}'::integer[]))
  • Buffers: shared hit=1579693 read=55676
108. 7.701 15,636.356 ↓ 11,015.0 11,015 1

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

  • Buffers: shared hit=1579693 read=55676
109. 11.277 15,573.580 ↓ 11,015.0 11,015 1

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

  • Buffers: shared hit=1523956 read=55676
110. 14.460 15,474.183 ↓ 11,015.0 11,015 1

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

  • Buffers: shared hit=1456993 read=55534
111. 127.516 14,115.893 ↓ 11,015.0 11,015 1

Nested Loop Left Join (cost=172,655.20..208,075.84 rows=1 width=76) (actual time=1,797.338..14,115.893 rows=11,015 loops=1)

  • Filter: (child_rev_2.id IS NULL)
  • Rows Removed by Filter: 278500
  • Buffers: shared hit=1395937 read=49719
112. 1,014.216 3,565.837 ↓ 15.8 289,515 1

Merge Join (cost=172,654.77..181,170.53 rows=18,337 width=76) (actual time=1,695.096..3,565.837 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
113. 96.950 96.950 ↓ 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.563..96.950 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
114. 899.108 2,454.671 ↓ 15.1 3,206,780 1

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

  • Sort Key: rev_7.userid
  • Sort Method: quicksort Memory: 53640kB
  • Buffers: shared hit=248929 read=27717
115. 1,555.563 1,555.563 ↓ 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=242.600..1,555.563 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'::timestam (...)
  • Buffers: shared hit=248929 read=27717
116. 10,422.540 10,422.540 ↑ 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.035..0.036 rows=1 loops=289,515)

  • Index Cond: (previousrevisionid = rev_7.id)
  • Buffers: shared hit=1130020 read=21161
117. 1,343.830 1,343.830 ↑ 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.120..0.122 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=61056 read=5815
118. 88.120 88.120 ↑ 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.008 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=66963 read=142
119. 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
120. 32.146 62,378.904 ↓ 152.2 49,920 1

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

  • Buffers: shared hit=1136811 read=134447
121. 103.936 62,346.758 ↓ 152.2 49,920 1

Nested Loop (cost=124,405.96..192,361.71 rows=328 width=310) (actual time=9,750.927..62,346.758 rows=49,920 loops=1)

  • Join Filter: (ts_9.id = tsformat_1.timesheetid)
  • Buffers: shared hit=1136811 read=134447
122. 104.283 41,176.582 ↓ 152.7 49,920 1

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

  • Buffers: shared hit=784504 read=99649
123. 73.448 34,482.859 ↓ 152.7 49,920 1

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

  • Buffers: shared hit=574486 read=77461
124. 72.174 10,397.891 ↓ 152.7 49,920 1

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

  • Hash Cond: (tah.timesheetid = ts_9.id)
  • Buffers: shared hit=396620 read=23121
125. 658.682 10,300.916 ↓ 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,721.577..10,300.916 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 (COAL (...)
  • Rows Removed by Filter: 921514
  • Buffers: shared hit=378790 read=23058
126.          

SubPlan (forSeq Scan)

127. 83.028 9,642.234 ↓ 115,648.0 115,648 1

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

  • Buffers: shared hit=358127 read=22993
128. 129.237 9,270.086 ↓ 57,824.0 57,824 1

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

  • Hash Cond: ((faas.timesheetid = tah_3.timesheetid) AND (faas.nextaction_serialnumber = tah_3.serialnumber))
  • Buffers: shared hit=110239 read=22993
129.          

CTE firstactionaftersubmit

130. 731.137 8,730.414 ↓ 10.0 254,938 1

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

  • Group Key: submissions.timesheetid, submissions.serialnumber
  • Buffers: shared hit=90121 read=22786
131.          

CTE submissions

132. 4,258.424 4,258.424 ↑ 1.0 254,938 1

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

  • Index Cond: (action = 1)
  • Buffers: shared hit=19729 read=18029
133. 850.692 7,999.277 ↓ 2.5 1,121,138 1

Merge Join (cost=28,011.07..77,893.07 rows=455,090 width=24) (actual time=4,674.885..7,999.277 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=90121 read=22786
134. 338.178 4,777.826 ↑ 1.0 254,938 1

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

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

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

  • Buffers: shared hit=19729 read=18029
136. 2,370.759 2,370.759 ↓ 1.7 1,649,334 1

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

  • Buffers: shared hit=70392 read=4757
  • -> Index Only Scan using ixtah2timesheetid on timesheetapprovalhistory tah_1 (cost=0.42..24714.69 rows=975751 width=20) (actual time=0.552..1773.354 row (...)
  • Heap Fetches: 84012
  • Buffers: shared hit=70392 read=4757
137. 8,872.391 8,872.391 ↓ 10.0 254,938 1

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

  • Buffers: shared hit=90121 read=22786
138. 21.178 268.458 ↓ 182.4 57,829 1

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

  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3449kB
  • Buffers: shared hit=20118 read=207
139. 247.280 247.280 ↓ 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=0.888..247.280 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
140. 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
141. 6.156 24.801 ↓ 10.9 17,552 1

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

  • Buckets: 32768 (originally 2048) Batches: 1 (originally 1) Memory Usage: 1148kB
  • Buffers: shared hit=17830 read=63
142. 18.645 18.645 ↓ 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.013..18.645 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
143. 24,011.520 24,011.520 ↑ 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.461..0.481 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
144. 6,589.440 6,589.440 ↑ 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.031..0.132 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
145. 21,066.240 21,066.240 ↑ 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.197..0.422 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=352307 read=34798
146. 4,491.026 39,242.880 ↓ 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.036..0.036 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=16430472 read=5441
147.          

SubPlan (forIndex Scan)

148. 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
149. 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))
150. 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
151. 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
152. 0.000 0.000 ↓ 0.0 0

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

153. 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
154. 0.000 0.000 ↓ 0.0 0

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

155. 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
156. 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)
157. 1,085.748 30,400.944 ↓ 0.0 0 1,085,748

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

  • Recheck Cond: ((timesheetid = timesheet3.id) AND (userid = 1300))
  • Buffers: shared hit=7666574 read=5440
158. 2,178.552 29,315.196 ↓ 0.0 0 1,085,748

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

  • Buffers: shared hit=7666574 read=5440
159. 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=3324926 read=5440
160. 22,793.652 22,793.652 ↓ 2.2 245 1,085,412

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

  • Index Cond: (userid = 1300)
  • Buffers: shared hit=4341648
161. 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)
162. 12.996 12.996 ↑ 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.003 rows=1 loops=4,332)

  • Index Cond: (id = "*SELECT* 1".userid)
  • Buffers: shared hit=12995 read=1
163. 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
164. 21.660 21.660 ↑ 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.005 rows=1 loops=4,332)

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