explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4qLe : SRAHL

Settings
# exclusive inclusive rows x rows loops node
1. 1,821.298 64,869.538 ↓ 2.4 311,894 1

Sort (cost=2,634,260.03..2,634,583.15 rows=129,248 width=353) (actual time=64,756.725..64,869.538 rows=311,894 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: 171631kB
  • Buffers: shared hit=7941661 read=393342 dirtied=820
2. 302.622 63,048.240 ↓ 2.4 311,894 1

Hash Join (cost=9,690.00..2,623,287.01 rows=129,248 width=353) (actual time=149.339..63,048.240 rows=311,894 loops=1)

  • Hash Cond: (tslist.timesheetid = ts.id)
  • Buffers: shared hit=7941661 read=393342 dirtied=820
3. 245.063 62,678.618 ↓ 2.4 311,894 1

Hash Join (cost=6,225.35..2,618,045.35 rows=129,248 width=377) (actual time=82.200..62,678.618 rows=311,894 loops=1)

  • Hash Cond: ("*SELECT* 1".timesheetid = tslist.timesheetid)
  • Buffers: shared hit=7940526 read=393342 dirtied=820
4. 244.523 62,355.282 ↓ 2.4 311,894 1

Hash Join (cost=516.25..2,610,559.10 rows=129,248 width=361) (actual time=3.794..62,355.282 rows=311,894 loops=1)

  • Hash Cond: ("*SELECT* 1".userid = ui.id)
  • Buffers: shared hit=7906839 read=392672 dirtied=820
5. 252.230 62,108.912 ↓ 2.4 311,894 1

Hash Join (cost=270.47..2,608,536.16 rows=129,248 width=352) (actual time=1.932..62,108.912 rows=311,894 loops=1)

  • Hash Cond: ("*SELECT* 1".userid = ui_1.id)
  • Buffers: shared hit=7906651 read=392672 dirtied=820
6. 153.280 61,854.944 ↓ 1.2 311,894 1

Append (cost=0.85..2,604,860.20 rows=258,495 width=348) (actual time=0.183..61,854.944 rows=311,894 loops=1)

  • Buffers: shared hit=7906463 read=392672 dirtied=820
7. 0.002 0.007 ↓ 0.0 0 1

Subquery Scan on *SELECT* 1 (cost=0.85..275.45 rows=1 width=346) (actual time=0.007..0.007 rows=0 loops=1)

  • Buffers: shared hit=1
8. 0.001 0.005 ↓ 0.0 0 1

Nested Loop (cost=0.85..275.44 rows=1 width=182) (actual time=0.005..0.005 rows=0 loops=1)

  • Buffers: shared hit=1
9. 0.000 0.004 ↓ 0.0 0 1

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

  • Buffers: shared hit=1
10. 0.001 0.004 ↓ 0.0 0 1

Nested Loop (cost=0.12..12.64 rows=1 width=80) (actual time=0.004..0.004 rows=0 loops=1)

  • Join Filter: (at.rootid = allocatedtime.rootid)
  • Buffers: shared hit=1
11. 0.003 0.003 ↓ 0.0 0 1

Index Scan using ixallocatedtimerootid on allocatedtime (cost=0.12..2.14 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=1)

  • Buffers: shared hit=1
12. 0.000 0.000 ↓ 0.0 0

Seq Scan on allocatedtime at (cost=0.00..10.40 rows=8 width=80) (never executed)

  • Filter: (CASE WHEN (parentid IS NULL) THEN 100 ELSE 101 END = ANY ('{1,2,3,4,6,100,101,102}'::integer[]))
13. 0.000 0.000 ↓ 0.0 0

Index Scan using uix2tsuseridstartdate on timesheet ts_1 (cost=0.29..2.32 rows=1 width=28) (never executed)

  • Index Cond: ((userid = at.userid) AND (at.entrydate >= startdate) AND (startdate >= '2018-12-30'::date) AND (startdate <= '2019-01-05'::date))
  • Filter: ((enddate >= '2018-12-30'::date) AND (enddate <= '2019-01-05'::date) AND (at.entrydate <= enddate))
14. 0.000 0.000 ↓ 0.0 0

Index Scan using ixtpsstimesheetid on timesheetpolicysettingssnapshot tsformat (cost=0.43..260.44 rows=3 width=106) (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))
15. 0.001 0.008 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=10.75..23.28 rows=1 width=288) (actual time=0.008..0.008 rows=0 loops=1)

16. 0.005 0.007 ↓ 0.0 0 1

Result (cost=10.75..23.27 rows=1 width=68) (actual time=0.007..0.007 rows=0 loops=1)

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

Nested Loop (cost=10.75..23.27 rows=1 width=68) (actual time=0.002..0.002 rows=0 loops=1)

18. 0.001 0.001 ↓ 0.0 0 1

Hash Left Join (cost=10.45..20.92 rows=1 width=56) (actual time=0.001..0.001 rows=0 loops=1)

  • Hash Cond: (at_1.id = child_at.parentid)
  • Filter: (child_at.id IS NULL)
19. 0.000 0.000 ↓ 0.0 0 1

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

  • Filter: (totimestamputc IS NOT NULL)
20. 0.000 0.000 ↓ 0.0 0

Hash (cost=10.20..10.20 rows=20 width=32) (never executed)

21. 0.000 0.000 ↓ 0.0 0

Seq Scan on allocatedtime child_at (cost=0.00..10.20 rows=20 width=32) (never executed)

22. 0.000 0.000 ↓ 0.0 0

Index Scan using uix2tsuseridstartdate on timesheet ts_2 (cost=0.29..2.32 rows=1 width=28) (never executed)

  • Index Cond: ((userid = at_1.userid) AND (at_1.entrydate >= startdate) AND (startdate >= '2018-12-30'::date) AND (startdate <= '2019-01-05'::date))
  • Filter: ((enddate >= '2018-12-30'::date) AND (enddate <= '2019-01-05'::date) AND (at_1.entrydate <= enddate))
23. 49.449 5,062.422 ↓ 24.0 90,290 1

Subquery Scan on *SELECT* 3 (cost=2.96..12,374.17 rows=3,766 width=348) (actual time=0.167..5,062.422 rows=90,290 loops=1)

  • Buffers: shared hit=1236999 read=124666 dirtied=151
24. 142.010 5,012.973 ↓ 24.0 90,290 1

Nested Loop Left Join (cost=2.96..12,336.51 rows=3,766 width=208) (actual time=0.166..5,012.973 rows=90,290 loops=1)

  • Buffers: shared hit=1236999 read=124666 dirtied=151
25. 94.899 4,509.803 ↓ 120.5 90,290 1

Nested Loop Left Join (cost=2.40..10,202.50 rows=749 width=162) (actual time=0.157..4,509.803 rows=90,290 loops=1)

  • Buffers: shared hit=785570 read=122992 dirtied=149
26. 91.924 3,873.164 ↓ 606.0 90,290 1

Nested Loop Left Join (cost=1.84..9,777.98 rows=149 width=100) (actual time=0.140..3,873.164 rows=90,290 loops=1)

  • Buffers: shared hit=350317 read=106576 dirtied=71
27. 53.838 1,794.860 ↓ 606.0 90,290 1

Nested Loop (cost=1.28..9,484.74 rows=149 width=100) (actual time=0.115..1,794.860 rows=90,290 loops=1)

  • Buffers: shared hit=66143 read=29436 dirtied=71
28. 3.082 434.366 ↓ 170.2 1,872 1

Nested Loop Semi Join (cost=0.85..5,698.12 rows=11 width=28) (actual time=0.092..434.366 rows=1,872 loops=1)

  • Buffers: shared hit=5825 read=6766 dirtied=2
29. 6.340 6.340 ↓ 52.0 1,872 1

Index Scan using ixtsuseridstartdateenddate on timesheet ts_3 (cost=0.42..2,116.34 rows=36 width=28) (actual time=0.010..6.340 rows=1,872 loops=1)

  • Index Cond: ((startdate >= '2018-12-30'::date) AND (startdate <= '2019-01-05'::date) AND (enddate >= '2018-12-30'::date) AND (enddate <= '2019-01-05'::date))
  • Buffers: shared hit=1769 read=338 dirtied=2
30. 424.944 424.944 ↑ 3.0 1 1,872

Index Scan using ixtpsstimesheetid on timesheetpolicysettingssnapshot (cost=0.43..259.16 rows=3 width=16) (actual time=0.227..0.227 rows=1 loops=1,872)

  • Index Cond: (timesheetid = ts_3.id)
  • Filter: (upper(uri) = 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT:GEN4-TIMESHEET'::text)
  • Rows Removed by Filter: 38
  • Buffers: shared hit=4056 read=6428
31. 1,306.656 1,306.656 ↓ 3.4 48 1,872

Index Scan using ixter2userid on timeentryrevision rev (cost=0.43..344.10 rows=14 width=88) (actual time=0.024..0.698 rows=48 loops=1,872)

  • Index Cond: ((userid = ts_3.userid) AND (entrydate >= ts_3.startdate) AND (entrydate <= ts_3.enddate))
  • Filter: (CASE WHEN (previousrevisionid IS NULL) THEN 100 ELSE 101 END = ANY ('{1,2,3,4,6,100,101,102}'::integer[]))
  • Buffers: shared hit=60318 read=22670 dirtied=69
32. 1,986.380 1,986.380 ↓ 0.0 0 90,290

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue changereason (cost=0.56..1.96 rows=1 width=16) (actual time=0.022..0.022 rows=0 loops=90,290)

  • Index Cond: ((revisionauditid = rev.openingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:COMMENT'::text))
  • Buffers: shared hit=284174 read=77140
33. 541.740 541.740 ↑ 7.0 1 90,290

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue actualuser (cost=0.56..2.78 rows=7 width=78) (actual time=0.006..0.006 rows=1 loops=90,290)

  • Index Cond: ((revisionauditid = rev.openingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:ACTUAL-USER'::text))
  • Buffers: shared hit=435253 read=16416 dirtied=78
34. 361.160 361.160 ↑ 7.0 1 90,290

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue effectiveuser (cost=0.56..2.78 rows=7 width=78) (actual time=0.004..0.004 rows=1 loops=90,290)

  • Index Cond: ((revisionauditid = rev.openingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:EFFECTIVE-USER'::text))
  • Buffers: shared hit=451429 read=1674 dirtied=2
35. 88.106 24,140.366 ↓ 158,360.0 158,360 1

Subquery Scan on *SELECT* 4 (cost=241,259.01..287,329.50 rows=1 width=348) (actual time=13,164.596..24,140.366 rows=158,360 loops=1)

  • Buffers: shared hit=2679441 read=177551 dirtied=283
36. 113.415 24,052.260 ↓ 158,360.0 158,360 1

Result (cost=241,259.01..287,329.49 rows=1 width=208) (actual time=13,164.595..24,052.260 rows=158,360 loops=1)

  • One-Time Filter: (101 = ANY ('{1,2,3,4,6,100,101,102}'::integer[]))
  • Buffers: shared hit=2679441 read=177551 dirtied=283
37. 244.749 23,938.845 ↓ 158,360.0 158,360 1

Nested Loop Left Join (cost=241,259.01..287,329.49 rows=1 width=208) (actual time=13,164.586..23,938.845 rows=158,360 loops=1)

  • Buffers: shared hit=2679441 read=177551 dirtied=283
38. 245.772 23,219.016 ↓ 158,360.0 158,360 1

Nested Loop Left Join (cost=241,258.44..287,328.65 rows=1 width=162) (actual time=13,164.580..23,219.016 rows=158,360 loops=1)

  • Buffers: shared hit=1887248 read=177542 dirtied=283
39. 197.511 22,181.444 ↓ 158,360.0 158,360 1

Nested Loop Anti Join (cost=241,257.88..287,327.83 rows=1 width=100) (actual time=13,164.523..22,181.444 rows=158,360 loops=1)

  • Buffers: shared hit=1092694 read=177450 dirtied=283
40. 171.202 20,589.899 ↓ 12.3 232,339 1

Nested Loop (cost=241,257.45..278,470.93 rows=18,868 width=116) (actual time=13,164.505..20,589.899 rows=232,339 loops=1)

  • Buffers: shared hit=171376 read=168221 dirtied=221
41. 835.866 15,152.401 ↓ 41.5 24,381 1

Hash Join (cost=241,256.89..277,401.64 rows=587 width=36) (actual time=13,164.477..15,152.401 rows=24,381 loops=1)

  • Hash Cond: (timeentryrevision_1.userid = ts_4.userid)
  • Join Filter: ((timeentryrevision_1.entrydate >= ts_4.startdate) AND (timeentryrevision_1.entrydate <= ts_4.enddate))
  • Rows Removed by Join Filter: 1873060
  • Buffers: shared hit=33458 read=103551 dirtied=96
42. 4,711.384 14,234.025 ↓ 2.0 1,988,019 1

HashAggregate (cost=235,558.64..245,508.36 rows=994,972 width=24) (actual time=13,081.611..14,234.025 rows=1,988,019 loops=1)

  • Group Key: timeentryrevision_1.timeentryid, timeentryrevision_1.userid, timeentryrevision_1.entrydate
  • Buffers: shared hit=20871 read=103551 dirtied=96
43. 9,522.641 9,522.641 ↓ 1.0 6,358,633 1

Seq Scan on timeentryrevision timeentryrevision_1 (cost=0.00..187,928.65 rows=6,350,665 width=24) (actual time=0.003..9,522.641 rows=6,358,633 loops=1)

  • Buffers: shared hit=20871 read=103551 dirtied=96
44. 0.857 82.510 ↓ 170.2 1,872 1

Hash (cost=5,698.12..5,698.12 rows=11 width=28) (actual time=82.510..82.510 rows=1,872 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 126kB
  • Buffers: shared hit=12587
45. 78.026 81.653 ↓ 170.2 1,872 1

Nested Loop Semi Join (cost=0.85..5,698.12 rows=11 width=28) (actual time=0.056..81.653 rows=1,872 loops=1)

  • Buffers: shared hit=12587
  • -> Index Scan using ixtpsstimesheetid on timesheetpolicysettingssnapshot timesheetpolicysettingssnapshot_1 (cost=0.43..259.16 rows=3 width=16) (actual time=0.041..0.041 (...)
46. 3.627 3.627 ↓ 52.0 1,872 1

Index Scan using ixtsuseridstartdateenddate on timesheet ts_4 (cost=0.42..2,116.34 rows=36 width=28) (actual time=0.010..3.627 rows=1,872 loops=1)

  • Index Cond: ((startdate >= '2018-12-30'::date) AND (startdate <= '2019-01-05'::date) AND (enddate >= '2018-12-30'::date) AND (enddate <= '2019-01-05'::date))
  • Buffers: shared hit=2103
  • Index Cond: (timesheetid = ts_4.id)
  • Filter: (upper(uri) = 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT:GEN4-TIMESHEET'::text)
  • Rows Removed by Filter: 38
  • Buffers: shared hit=10484
47. 5,266.296 5,266.296 ↑ 3.2 10 24,381

Index Scan using ixtemrtimeentryid on timeentrymetadatarevision mdrev (cost=0.56..1.50 rows=32 width=80) (actual time=0.068..0.216 rows=10 loops=24,381)

  • Index Cond: (timeentryid = timeentryrevision_1.timeentryid)
  • Buffers: shared hit=137918 read=64670 dirtied=125
48. 1,394.034 1,394.034 ↓ 0.0 0 232,339

Index Scan using timeentryrevision_singleinitialrevision on timeentryrevision (cost=0.43..0.46 rows=1 width=24) (actual time=0.006..0.006 rows=0 loops=232,339)

  • Index Cond: (timeentryid = mdrev.timeentryid)
  • Filter: (fromtimestamputc = mdrev.fromtimestamputc)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=921318 read=9229 dirtied=62
49. 791.800 791.800 ↑ 7.0 1 158,360

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue effectiveuser_1 (cost=0.56..0.75 rows=7 width=78) (actual time=0.005..0.005 rows=1 loops=158,360)

  • Index Cond: ((revisionauditid = mdrev.openingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:EFFECTIVE-USER'::text))
  • Buffers: shared hit=794554 read=92
50. 475.080 475.080 ↑ 7.0 1 158,360

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue actualuser_1 (cost=0.56..0.75 rows=7 width=78) (actual time=0.003..0.003 rows=1 loops=158,360)

  • Index Cond: ((revisionauditid = mdrev.openingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:ACTUAL-USER'::text))
  • Buffers: shared hit=792193 read=9
51. 19.200 11,821.400 ↑ 4.2 32,656 1

Subquery Scan on *SELECT* 5 (cost=241,260.01..474,780.20 rows=135,620 width=348) (actual time=6,096.293..11,821.400 rows=32,656 loops=1)

  • Buffers: shared hit=2459968 read=34771 dirtied=167
52. 27.790 11,802.200 ↑ 4.2 32,656 1

Result (cost=241,260.01..473,424.00 rows=135,620 width=184) (actual time=6,096.291..11,802.200 rows=32,656 loops=1)

  • One-Time Filter: (101 = ANY ('{1,2,3,4,6,100,101,102}'::integer[]))
  • Buffers: shared hit=2459968 read=34771 dirtied=167
53. 30.470 11,774.410 ↑ 4.2 32,656 1

Nested Loop Anti Join (cost=241,260.01..473,424.00 rows=135,620 width=184) (actual time=6,096.282..11,774.410 rows=32,656 loops=1)

  • Buffers: shared hit=2459968 read=34771 dirtied=167
54. 61.456 9,901.777 ↑ 3.2 42,841 1

Nested Loop Left Join (cost=241,259.13..328,286.84 rows=135,620 width=200) (actual time=6,096.236..9,901.777 rows=42,841 loops=1)

  • Buffers: shared hit=2055144 read=1496 dirtied=1
55. 40.101 9,668.957 ↓ 1.4 42,841 1

Nested Loop Left Join (cost=241,258.57..302,435.92 rows=30,535 width=154) (actual time=6,096.226..9,668.957 rows=42,841 loops=1)

  • Buffers: shared hit=1840243 read=1461 dirtied=1
56. 72.679 9,328.969 ↓ 6.2 42,841 1

Nested Loop Anti Join (cost=241,258.01..296,588.45 rows=6,907 width=92) (actual time=6,096.215..9,328.969 rows=42,841 loops=1)

  • Buffers: shared hit=1627401
57. 117.963 8,375.750 ↓ 13.8 176,108 1

Nested Loop (cost=241,257.45..278,412.23 rows=12,805 width=140) (actual time=6,096.176..8,375.750 rows=176,108 loops=1)

  • Buffers: shared hit=339553
58. 802.621 7,989.596 ↓ 41.5 24,381 1

Hash Join (cost=241,256.89..277,401.64 rows=587 width=36) (actual time=6,096.159..7,989.596 rows=24,381 loops=1)

  • Hash Cond: (timeentryrevision_2.userid = ts_5.userid)
  • Join Filter: ((timeentryrevision_2.entrydate >= ts_5.startdate) AND (timeentryrevision_2.entrydate <= ts_5.enddate))
  • Rows Removed by Join Filter: 1873060
  • Buffers: shared hit=137009
59. 4,551.661 7,104.030 ↓ 2.0 1,988,019 1

HashAggregate (cost=235,558.64..245,508.36 rows=994,972 width=24) (actual time=6,012.906..7,104.030 rows=1,988,019 loops=1)

  • Group Key: timeentryrevision_2.timeentryid, timeentryrevision_2.userid, timeentryrevision_2.entrydate
  • Buffers: shared hit=124422
60. 2,552.369 2,552.369 ↓ 1.0 6,358,633 1

Seq Scan on timeentryrevision timeentryrevision_2 (cost=0.00..187,928.65 rows=6,350,665 width=24) (actual time=0.003..2,552.369 rows=6,358,633 loops=1)

  • Buffers: shared hit=124422
61. 0.856 82.945 ↓ 170.2 1,872 1

Hash (cost=5,698.12..5,698.12 rows=11 width=28) (actual time=82.945..82.945 rows=1,872 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 126kB
  • Buffers: shared hit=12587
62. 78.419 82.089 ↓ 170.2 1,872 1

Nested Loop Semi Join (cost=0.85..5,698.12 rows=11 width=28) (actual time=0.060..82.089 rows=1,872 loops=1)

  • Buffers: shared hit=12587
  • -> Index Scan using ixtpsstimesheetid on timesheetpolicysettingssnapshot timesheetpolicysettingssnapshot_2 (cost=0.43..259.16 rows=3 width=16) (actual time=0.041. (...)
63. 3.670 3.670 ↓ 52.0 1,872 1

Index Scan using ixtsuseridstartdateenddate on timesheet ts_5 (cost=0.42..2,116.34 rows=36 width=28) (actual time=0.011..3.670 rows=1,872 loops=1)

  • Index Cond: ((startdate >= '2018-12-30'::date) AND (startdate <= '2019-01-05'::date) AND (enddate >= '2018-12-30'::date) AND (enddate <= '2019-01-05'::date))
  • Buffers: shared hit=2103
  • Index Cond: (timesheetid = ts_5.id)
  • Filter: (upper(uri) = 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT:GEN4-TIMESHEET'::text)
  • Rows Removed by Filter: 38
  • Buffers: shared hit=10484
64. 268.191 268.191 ↑ 3.1 7 24,381

Index Scan using ixtemrtimeentryid on timeentrymetadatarevision mdrev_1 (cost=0.56..1.50 rows=22 width=104) (actual time=0.006..0.011 rows=7 loops=24,381)

  • Index Cond: (timeentryid = timeentryrevision_2.timeentryid)
  • Filter: (totimestamputc IS NOT NULL)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=202544
65. 880.540 880.540 ↑ 1.0 1 176,108

Index Scan using ixtemrtimeentryid on timeentrymetadatarevision (cost=0.56..1.41 rows=1 width=88) (actual time=0.005..0.005 rows=1 loops=176,108)

  • Index Cond: (timeentryid = mdrev_1.timeentryid)
  • Filter: ((key = mdrev_1.key) AND (fromtimestamputc = mdrev_1.totimestamputc) AND (openingauditid = mdrev_1.closingauditid))
  • Rows Removed by Filter: 6
  • Buffers: shared hit=1287848
66. 299.887 299.887 ↑ 7.0 1 42,841

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue actualuser_2 (cost=0.56..0.78 rows=7 width=78) (actual time=0.006..0.007 rows=1 loops=42,841)

  • Index Cond: ((revisionauditid = mdrev_1.closingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:ACTUAL-USER'::text))
  • Buffers: shared hit=212842 read=1461 dirtied=1
67. 171.364 171.364 ↑ 7.0 1 42,841

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue effectiveuser_2 (cost=0.56..0.78 rows=7 width=78) (actual time=0.004..0.004 rows=1 loops=42,841)

  • Index Cond: ((revisionauditid = mdrev_1.closingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:EFFECTIVE-USER'::text))
  • Buffers: shared hit=214901 read=35
68. 42.841 1,842.163 ↓ 0.0 0 42,841

Nested Loop Left Join (cost=0.86..1.06 rows=1 width=24) (actual time=0.043..0.043 rows=0 loops=42,841)

  • Filter: (child_rev.id IS NULL)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=404824 read=33275 dirtied=166
69. 728.297 728.297 ↑ 1.0 1 42,841

Index Scan using ixtertimeentryid on timeentryrevision rev_1 (cost=0.43..0.57 rows=1 width=40) (actual time=0.016..0.017 rows=1 loops=42,841)

  • Index Cond: (timeentryid = mdrev_1.timeentryid)
  • Filter: (totimestamputc = mdrev_1.totimestamputc)
  • Rows Removed by Filter: 3
  • Buffers: shared hit=261455 read=15096 dirtied=49
70. 1,071.025 1,071.025 ↑ 1.0 1 42,841

Index Scan using timeentryrevision_uniquepreviousrevisionid on timeentryrevision child_rev (cost=0.43..0.48 rows=1 width=32) (actual time=0.024..0.025 rows=1 loops=42,841)

  • Index Cond: (previousrevisionid = rev_1.id)
  • Buffers: shared hit=143358 read=18179 dirtied=106
71. 12.228 9,168.308 ↓ 22,595.0 22,595 1

Subquery Scan on *SELECT* 6 (cost=241,259.02..347,917.81 rows=1 width=348) (actual time=6,284.879..9,168.308 rows=22,595 loops=1)

  • Buffers: shared hit=547909 read=38171 dirtied=109
72. 17.092 9,156.080 ↓ 22,595.0 22,595 1

Result (cost=241,259.02..347,917.80 rows=1 width=208) (actual time=6,284.877..9,156.080 rows=22,595 loops=1)

  • One-Time Filter: (101 = ANY ('{1,2,3,4,6,100,101,102}'::integer[]))
  • Buffers: shared hit=547909 read=38171 dirtied=109
73. 37.017 9,138.988 ↓ 22,595.0 22,595 1

Nested Loop Left Join (cost=241,259.02..347,917.80 rows=1 width=208) (actual time=6,284.869..9,138.988 rows=22,595 loops=1)

  • Buffers: shared hit=547909 read=38171 dirtied=109
74. 34.465 9,034.186 ↓ 22,595.0 22,595 1

Nested Loop Left Join (cost=241,258.45..347,917.01 rows=1 width=162) (actual time=6,284.863..9,034.186 rows=22,595 loops=1)

  • Buffers: shared hit=434876 read=38171 dirtied=109
75. 33.234 8,864.151 ↓ 22,595.0 22,595 1

Nested Loop Anti Join (cost=241,257.89..347,916.23 rows=1 width=100) (actual time=6,284.846..8,864.151 rows=22,595 loops=1)

  • Buffers: shared hit=321463 read=38171 dirtied=109
76. 26.243 8,750.922 ↑ 5.3 26,665 1

Nested Loop (cost=241,257.46..282,652.43 rows=142,030 width=116) (actual time=6,284.832..8,750.922 rows=26,665 loops=1)

  • Buffers: shared hit=214622 read=38171 dirtied=109
77. 769.245 8,115.154 ↓ 41.5 24,381 1

Hash Join (cost=241,256.89..277,401.64 rows=587 width=36) (actual time=6,284.791..8,115.154 rows=24,381 loops=1)

  • Hash Cond: (timeentryrevision_4.userid = ts_6.userid)
  • Join Filter: ((timeentryrevision_4.entrydate >= ts_6.startdate) AND (timeentryrevision_4.entrydate <= ts_6.enddate))
  • Rows Removed by Join Filter: 1873060
  • Buffers: shared hit=137009
78. 4,631.376 7,260.852 ↓ 2.0 1,988,019 1

HashAggregate (cost=235,558.64..245,508.36 rows=994,972 width=24) (actual time=6,199.415..7,260.852 rows=1,988,019 loops=1)

  • Group Key: timeentryrevision_4.timeentryid, timeentryrevision_4.userid, timeentryrevision_4.entrydate
  • Buffers: shared hit=124422
79. 2,629.476 2,629.476 ↓ 1.0 6,358,633 1

Seq Scan on timeentryrevision timeentryrevision_4 (cost=0.00..187,928.65 rows=6,350,665 width=24) (actual time=0.003..2,629.476 rows=6,358,633 loops=1)

  • Buffers: shared hit=124422
80. 0.907 85.057 ↓ 170.2 1,872 1

Hash (cost=5,698.12..5,698.12 rows=11 width=28) (actual time=85.057..85.057 rows=1,872 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 126kB
  • Buffers: shared hit=12587
81. 80.387 84.150 ↓ 170.2 1,872 1

Nested Loop Semi Join (cost=0.85..5,698.12 rows=11 width=28) (actual time=0.061..84.150 rows=1,872 loops=1)

  • Buffers: shared hit=12587
  • -> Index Scan using ixtpsstimesheetid on timesheetpolicysettingssnapshot timesheetpolicysettingssnapshot_3 (cost=0.43..259.16 rows=3 width=16) (actual time=0.042..0.042 (...)
82. 3.763 3.763 ↓ 52.0 1,872 1

Index Scan using ixtsuseridstartdateenddate on timesheet ts_6 (cost=0.42..2,116.34 rows=36 width=28) (actual time=0.011..3.763 rows=1,872 loops=1)

  • Index Cond: ((startdate >= '2018-12-30'::date) AND (startdate <= '2019-01-05'::date) AND (enddate >= '2018-12-30'::date) AND (enddate <= '2019-01-05'::date))
  • Buffers: shared hit=2103
  • Index Cond: (timesheetid = ts_6.id)
  • Filter: (upper(uri) = 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT:GEN4-TIMESHEET'::text)
  • Rows Removed by Filter: 38
  • Buffers: shared hit=10484
83. 609.525 609.525 ↑ 242.0 1 24,381

Index Scan using ixoefvrobjectid on objectextensionfieldvaluerevision oefvr (cost=0.57..6.53 rows=242 width=80) (actual time=0.021..0.025 rows=1 loops=24,381)

  • Index Cond: (objectid = timeentryrevision_4.timeentryid)
  • Buffers: shared hit=77613 read=38171 dirtied=109
84. 79.995 79.995 ↓ 0.0 0 26,665

Index Scan using timeentryrevision_singleinitialrevision on timeentryrevision timeentryrevision_3 (cost=0.43..0.45 rows=1 width=24) (actual time=0.003..0.003 rows=0 loops=26,665)

  • Index Cond: (timeentryid = oefvr.objectid)
  • Filter: (fromtimestamputc = oefvr.fromtimestamputc)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=106841
85. 135.570 135.570 ↑ 7.0 1 22,595

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue effectiveuser_3 (cost=0.56..0.71 rows=7 width=78) (actual time=0.006..0.006 rows=1 loops=22,595)

  • Index Cond: ((revisionauditid = oefvr.openingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:EFFECTIVE-USER'::text))
  • Buffers: shared hit=113413
86. 67.785 67.785 ↑ 7.0 1 22,595

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue actualuser_3 (cost=0.56..0.71 rows=7 width=78) (actual time=0.003..0.003 rows=1 loops=22,595)

  • Index Cond: ((revisionauditid = oefvr.openingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:ACTUAL-USER'::text))
  • Buffers: shared hit=113033
87. 0.454 8,159.976 ↑ 169.4 703 1

Subquery Scan on *SELECT* 7 (cost=241,260.02..1,400,805.10 rows=119,059 width=348) (actual time=6,056.544..8,159.976 rows=703 loops=1)

  • Buffers: shared hit=394168 read=270 dirtied=8
88. 0.712 8,159.522 ↑ 169.4 703 1

Result (cost=241,260.02..1,399,614.51 rows=119,059 width=184) (actual time=6,056.543..8,159.522 rows=703 loops=1)

  • One-Time Filter: (101 = ANY ('{1,2,3,4,6,100,101,102}'::integer[]))
  • Buffers: shared hit=394168 read=270 dirtied=8
89. 2.587 8,158.810 ↑ 169.4 703 1

Nested Loop Anti Join (cost=241,260.02..1,399,614.51 rows=119,059 width=184) (actual time=6,056.534..8,158.810 rows=703 loops=1)

  • Buffers: shared hit=394168 read=270 dirtied=8
90. 5.716 8,119.142 ↑ 35.3 3,371 1

Nested Loop Left Join (cost=241,259.15..1,274,361.43 rows=119,059 width=200) (actual time=6,055.165..8,119.142 rows=3,371 loops=1)

  • Buffers: shared hit=366770
91. 4.329 8,103.313 ↑ 35.3 3,371 1

Nested Loop Left Join (cost=241,258.59..1,181,462.89 rows=119,059 width=154) (actual time=6,055.160..8,103.313 rows=3,371 loops=1)

  • Buffers: shared hit=349906
92. 15.790 8,075.387 ↑ 35.3 3,371 1

Nested Loop Anti Join (cost=241,258.03..1,088,564.36 rows=119,059 width=92) (actual time=6,055.142..8,075.387 rows=3,371 loops=1)

  • Buffers: shared hit=333020
93. 29.619 8,018.098 ↑ 8.8 13,833 1

Nested Loop (cost=241,257.46..282,441.11 rows=121,086 width=108) (actual time=6,053.981..8,018.098 rows=13,833 loops=1)

  • Buffers: shared hit=252770
94. 764.948 7,866.574 ↓ 41.5 24,381 1

Hash Join (cost=241,256.89..277,401.64 rows=587 width=36) (actual time=6,053.642..7,866.574 rows=24,381 loops=1)

  • Hash Cond: (timeentryrevision_5.userid = ts_7.userid)
  • Join Filter: ((timeentryrevision_5.entrydate >= ts_7.startdate) AND (timeentryrevision_5.entrydate <= ts_7.enddate))
  • Rows Removed by Join Filter: 1873060
  • Buffers: shared hit=137009
95. 4,472.832 7,015.292 ↓ 2.0 1,988,019 1

HashAggregate (cost=235,558.64..245,508.36 rows=994,972 width=24) (actual time=5,966.990..7,015.292 rows=1,988,019 loops=1)

  • Group Key: timeentryrevision_5.timeentryid, timeentryrevision_5.userid, timeentryrevision_5.entrydate
  • Buffers: shared hit=124422
96. 2,542.460 2,542.460 ↓ 1.0 6,358,633 1

Seq Scan on timeentryrevision timeentryrevision_5 (cost=0.00..187,928.65 rows=6,350,665 width=24) (actual time=0.003..2,542.460 rows=6,358,633 loops=1)

  • Buffers: shared hit=124422
97. 0.906 86.334 ↓ 170.2 1,872 1

Hash (cost=5,698.12..5,698.12 rows=11 width=28) (actual time=86.334..86.334 rows=1,872 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 126kB
  • Buffers: shared hit=12587
98. 81.562 85.428 ↓ 170.2 1,872 1

Nested Loop Semi Join (cost=0.85..5,698.12 rows=11 width=28) (actual time=0.062..85.428 rows=1,872 loops=1)

  • Buffers: shared hit=12587
  • -> Index Scan using ixtpsstimesheetid on timesheetpolicysettingssnapshot timesheetpolicysettingssnapshot_4 (cost=0.43..259.16 rows=3 width=16) (actual time=0.043. (...)
99. 3.866 3.866 ↓ 52.0 1,872 1

Index Scan using ixtsuseridstartdateenddate on timesheet ts_7 (cost=0.42..2,116.34 rows=36 width=28) (actual time=0.011..3.866 rows=1,872 loops=1)

  • Index Cond: ((startdate >= '2018-12-30'::date) AND (startdate <= '2019-01-05'::date) AND (enddate >= '2018-12-30'::date) AND (enddate <= '2019-01-05'::date))
  • Buffers: shared hit=2103
  • Index Cond: (timesheetid = ts_7.id)
  • Filter: (upper(uri) = 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT:GEN4-TIMESHEET'::text)
  • Rows Removed by Filter: 38
  • Buffers: shared hit=10484
100. 121.905 121.905 ↑ 206.0 1 24,381

Index Scan using ixoefvrobjectid on objectextensionfieldvaluerevision oefvr_1 (cost=0.57..6.53 rows=206 width=72) (actual time=0.005..0.005 rows=1 loops=24,381)

  • Index Cond: (objectid = timeentryrevision_5.timeentryid)
  • Filter: (totimestamputc IS NOT NULL)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=115761
101. 41.499 41.499 ↑ 1.0 1 13,833

Index Scan using ixoefvrobjectid on objectextensionfieldvaluerevision (cost=0.57..6.65 rows=1 width=56) (actual time=0.003..0.003 rows=1 loops=13,833)

  • Index Cond: (objectid = oefvr_1.objectid)
  • Filter: ((definitionid = oefvr_1.definitionid) AND (fromtimestamputc = oefvr_1.totimestamputc) AND (openingauditid = oefvr_1.closingauditid))
  • Rows Removed by Filter: 2
  • Buffers: shared hit=80250
102. 23.597 23.597 ↑ 7.0 1 3,371

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue effectiveuser_4 (cost=0.56..0.71 rows=7 width=78) (actual time=0.006..0.007 rows=1 loops=3,371)

  • Index Cond: ((revisionauditid = oefvr_1.closingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:EFFECTIVE-USER'::text))
  • Buffers: shared hit=16886
103. 10.113 10.113 ↑ 7.0 1 3,371

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue actualuser_4 (cost=0.56..0.71 rows=7 width=78) (actual time=0.003..0.003 rows=1 loops=3,371)

  • Index Cond: ((revisionauditid = oefvr_1.closingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:ACTUAL-USER'::text))
  • Buffers: shared hit=16864
104. 3.371 37.081 ↑ 1.0 1 3,371

Nested Loop Left Join (cost=0.86..1.04 rows=1 width=24) (actual time=0.011..0.011 rows=1 loops=3,371)

  • Filter: (child_rev_1.id IS NULL)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=27398 read=270 dirtied=8
105. 20.226 20.226 ↑ 1.0 1 3,371

Index Scan using ixtertimeentryid on timeentryrevision rev_2 (cost=0.43..0.56 rows=1 width=40) (actual time=0.005..0.006 rows=1 loops=3,371)

  • Index Cond: (timeentryid = oefvr_1.objectid)
  • Filter: (totimestamputc = oefvr_1.totimestamputc)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=16683 read=146 dirtied=4
106. 13.484 13.484 ↓ 0.0 0 3,371

Index Scan using timeentryrevision_uniquepreviousrevisionid on timeentryrevision child_rev_1 (cost=0.43..0.48 rows=1 width=32) (actual time=0.004..0.004 rows=0 loops=3,371)

  • Index Cond: (previousrevisionid = rev_2.id)
  • Buffers: shared hit=10715 read=124 dirtied=4
107. 2.027 589.355 ↓ 3,814.0 3,814 1

Subquery Scan on *SELECT* 8 (cost=2.97..18,902.76 rows=1 width=348) (actual time=1.779..589.355 rows=3,814 loops=1)

  • Buffers: shared hit=407633 read=6404 dirtied=94
108. 2.767 587.328 ↓ 3,814.0 3,814 1

Result (cost=2.97..18,902.75 rows=1 width=184) (actual time=1.779..587.328 rows=3,814 loops=1)

  • One-Time Filter: (102 = ANY ('{1,2,3,4,6,100,101,102}'::integer[]))
  • Buffers: shared hit=407633 read=6404 dirtied=94
109. 4.002 584.561 ↓ 3,814.0 3,814 1

Nested Loop Left Join (cost=2.97..18,902.75 rows=1 width=184) (actual time=1.771..584.561 rows=3,814 loops=1)

  • Buffers: shared hit=407633 read=6404 dirtied=94
110. 5.384 565.303 ↓ 3,814.0 3,814 1

Nested Loop Left Join (cost=2.40..18,902.03 rows=1 width=200) (actual time=1.764..565.303 rows=3,814 loops=1)

  • Buffers: shared hit=392372 read=6404 dirtied=94
111. 3.289 548.477 ↓ 3,814.0 3,814 1

Nested Loop Left Join (cost=1.84..18,901.14 rows=1 width=138) (actual time=1.759..548.477 rows=3,814 loops=1)

  • Buffers: shared hit=373297 read=6404 dirtied=94
112. 26.091 522.304 ↓ 3,814.0 3,814 1

Nested Loop Left Join (cost=1.28..18,900.24 rows=1 width=76) (actual time=1.747..522.304 rows=3,814 loops=1)

  • Filter: (child_rev_2.id IS NULL)
  • Rows Removed by Filter: 65909
  • Buffers: shared hit=354174 read=6403 dirtied=94
113. 34.986 147.598 ↓ 7.8 69,723 1

Nested Loop (cost=0.85..14,472.92 rows=8,935 width=76) (actual time=0.029..147.598 rows=69,723 loops=1)

  • Buffers: shared hit=82278 read=2776
114. 4.036 4.036 ↓ 52.0 1,872 1

Index Scan using ixtsuseridstartdateenddate on timesheet ts_8 (cost=0.42..2,116.34 rows=36 width=28) (actual time=0.011..4.036 rows=1,872 loops=1)

  • Index Cond: ((startdate >= '2018-12-30'::date) AND (startdate <= '2019-01-05'::date) AND (enddate >= '2018-12-30'::date) AND (enddate <= '2019-01-05'::date))
  • Buffers: shared hit=2103
115. 108.576 108.576 ↑ 7.1 37 1,872

Index Scan using ixter2userid on timeentryrevision rev_3 (cost=0.43..340.61 rows=263 width=64) (actual time=0.012..0.058 rows=37 loops=1,872)

  • Index Cond: ((userid = ts_8.userid) AND (entrydate >= ts_8.startdate) AND (entrydate <= ts_8.enddate))
  • Filter: (totimestamputc IS NOT NULL)
  • Rows Removed by Filter: 11
  • Buffers: shared hit=80175 read=2776
116. 348.615 348.615 ↑ 1.0 1 69,723

Index Scan using timeentryrevision_uniquepreviousrevisionid on timeentryrevision child_rev_2 (cost=0.43..0.49 rows=1 width=32) (actual time=0.004..0.005 rows=1 loops=69,723)

  • Index Cond: (previousrevisionid = rev_3.id)
  • Buffers: shared hit=271896 read=3627 dirtied=94
117. 22.884 22.884 ↑ 7.0 1 3,814

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue effectiveuser_5 (cost=0.56..0.82 rows=7 width=78) (actual time=0.005..0.006 rows=1 loops=3,814)

  • Index Cond: ((revisionauditid = rev_3.closingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:EFFECTIVE-USER'::text))
  • Buffers: shared hit=19123 read=1
118. 11.442 11.442 ↑ 7.0 1 3,814

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue actualuser_5 (cost=0.56..0.82 rows=7 width=78) (actual time=0.003..0.003 rows=1 loops=3,814)

  • Index Cond: ((revisionauditid = rev_3.closingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:ACTUAL-USER'::text))
  • Buffers: shared hit=19075
119. 15.256 15.256 ↓ 0.0 0 3,814

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue changereason_1 (cost=0.56..0.70 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=3,814)

  • Index Cond: ((revisionauditid = rev_3.closingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:COMMENT'::text))
  • Buffers: shared hit=15261
120. 2.056 2,759.822 ↓ 77.2 3,476 1

Subquery Scan on *SELECT* 9 (cost=47,794.11..62,451.92 rows=45 width=430) (actual time=1,388.985..2,759.822 rows=3,476 loops=1)

  • Buffers: shared hit=180344 read=10839 dirtied=8
121. 4.701 2,757.766 ↓ 77.2 3,476 1

Nested Loop Left Join (cost=47,794.11..62,451.47 rows=45 width=302) (actual time=1,388.984..2,757.766 rows=3,476 loops=1)

  • Buffers: shared hit=180344 read=10839 dirtied=8
122. 6.159 2,655.737 ↓ 77.2 3,476 1

Nested Loop Left Join (cost=47,793.68..62,228.98 rows=45 width=247) (actual time=1,388.974..2,655.737 rows=3,476 loops=1)

  • Buffers: shared hit=163832 read=10828 dirtied=8
123. 4.036 2,253.314 ↓ 77.2 3,476 1

Nested Loop (cost=47,793.24..62,005.13 rows=45 width=176) (actual time=1,388.940..2,253.314 rows=3,476 loops=1)

  • Join Filter: (ts_9.id = tsformat_1.timesheetid)
  • Buffers: shared hit=155022 read=3115
124. 73.571 1,807.826 ↓ 74.0 3,476 1

Hash Join (cost=47,792.81..61,615.87 rows=47 width=102) (actual time=1,388.869..1,807.826 rows=3,476 loops=1)

  • Hash Cond: (tah.timesheetid = ts_9.id)
  • Buffers: shared hit=131149 read=1264
125. 362.189 1,730.025 ↓ 1.7 226,493 1

Index Scan using ixtahaction on timesheetapprovalhistory tah (cost=45,676.02..58,995.97 rows=134,040 width=82) (actual time=1,384.619..1,730.025 rows=226,493 loops=1)

  • Index Cond: (action = ANY ('{1,2,3,4,6,100,101,102}'::integer[]))
  • Filter: ((NOT (hashed SubPlan 3)) AND (COALESCE(upper(systemprocessidentifier), ''::text) <> 'URN:REPLICON:APPROVAL-SYSTEM-PROCESS:TIMESHEET-SUBMIT-SCRIPT-DATA-AND-VALIDATION'::text))
  • Rows Removed by Filter: 135716
  • Buffers: shared hit=129046 read=1264
126.          

SubPlan (forIndex Scan)

127. 23.335 1,367.836 ↓ 24,014.0 24,014 1

Nested Loop (cost=45,333.62..45,675.60 rows=1 width=16) (actual time=1,059.071..1,367.836 rows=24,014 loops=1)

  • Buffers: shared hit=79334 read=480
128. 57.771 1,284.466 ↓ 12,007.0 12,007 1

Hash Join (cost=45,333.20..45,673.13 rows=1 width=24) (actual time=1,059.058..1,284.466 rows=12,007 loops=1)

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

CTE firstactionaftersubmit

130. 247.377 1,102.920 ↓ 10.0 123,705 1

HashAggregate (cost=39,813.03..39,936.64 rows=12,361 width=24) (actual time=1,027.263..1,102.920 rows=123,705 loops=1)

  • Group Key: submissions.timesheetid, submissions.serialnumber
  • Buffers: shared hit=23806 read=427
131.          

CTE submissions

132. 62.724 62.724 ↓ 1.0 123,705 1

Index Scan using ixtahaction on timesheetapprovalhistory (cost=0.42..7,987.08 rows=123,608 width=20) (actual time=0.008..62.724 rows=123,705 loops=1)

  • Index Cond: (action = 1)
  • Buffers: shared hit=15638 read=426
133. 285.708 855.543 ↓ 2.0 337,679 1

Merge Join (cost=12,926.98..30,540.95 rows=171,333 width=24) (actual time=215.247..855.543 rows=337,679 loops=1)

  • Merge Cond: (submissions.timesheetid = tah_1.timesheetid)
  • Join Filter: (tah_1.serialnumber > submissions.serialnumber)
  • Rows Removed by Join Filter: 213461
  • Buffers: shared hit=23806 read=427
134. 124.003 259.990 ↓ 1.0 123,705 1

Sort (cost=12,926.56..13,235.58 rows=123,608 width=20) (actual time=215.210..259.990 rows=123,705 loops=1)

  • Sort Key: submissions.timesheetid
  • Sort Method: quicksort Memory: 12737kB
  • Buffers: shared hit=15638 read=426
135. 135.987 135.987 ↓ 1.0 123,705 1

CTE Scan on submissions (cost=0.00..2,472.16 rows=123,608 width=20) (actual time=0.010..135.987 rows=123,705 loops=1)

  • Buffers: shared hit=15638 read=426
136. 309.845 309.845 ↓ 1.5 551,147 1

Materialize (cost=0.42..8,906.52 rows=362,063 width=20) (actual time=0.025..309.845 rows=551,147 loops=1)

  • Buffers: shared hit=8168 read=1
  • -> Index Only Scan using ixtah2timesheetid on timesheetapprovalhistory tah_1 (cost=0.42..8001.37 rows=362063 width=20) (actual time=0.022..102.794 rows= (...)
  • Heap Fetches: 6366
  • Buffers: shared hit=8168 read=1
137. 1,194.929 1,194.929 ↓ 10.0 123,705 1

CTE Scan on firstactionaftersubmit faas (cost=0.00..247.22 rows=12,361 width=24) (actual time=1,027.265..1,194.929 rows=123,705 loops=1)

  • Buffers: shared hit=23806 read=427
138. 5.554 31.766 ↓ 164.5 12,010 1

Hash (cost=5,395.46..5,395.46 rows=73 width=20) (actual time=31.766..31.766 rows=12,010 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 738kB
  • Buffers: shared hit=5869 read=51
139. 26.212 26.212 ↓ 164.5 12,010 1

Index Scan using ixtahaction on timesheetapprovalhistory tah_3 (cost=0.42..5,395.46 rows=73 width=20) (actual time=0.038..26.212 rows=12,010 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: 2180
  • Buffers: shared hit=5869 read=51
140. 60.035 60.035 ↓ 2.0 2 12,007

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

  • Index Cond: ((timesheetid = faas.timesheetid) AND (serialnumber >= faas.submission_serialnumber) AND (serialnumber <= faas.nextaction_serialnumber))
  • Buffers: shared hit=49659 read=2
141. 0.683 4.230 ↓ 52.0 1,872 1

Hash (cost=2,116.34..2,116.34 rows=36 width=20) (actual time=4.230..4.230 rows=1,872 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 112kB
  • Buffers: shared hit=2103
142. 3.547 3.547 ↓ 52.0 1,872 1

Index Scan using ixtsuseridstartdateenddate on timesheet ts_9 (cost=0.42..2,116.34 rows=36 width=20) (actual time=0.011..3.547 rows=1,872 loops=1)

  • Index Cond: ((startdate >= '2018-12-30'::date) AND (startdate <= '2019-01-05'::date) AND (enddate >= '2018-12-30'::date) AND (enddate <= '2019-01-05'::date))
  • Buffers: shared hit=2103
143. 441.452 441.452 ↑ 3.0 1 3,476

Index Scan using ixtpsstimesheetid on timesheetpolicysettingssnapshot tsformat_1 (cost=0.43..8.24 rows=3 width=106) (actual time=0.052..0.127 rows=1 loops=3,476)

  • Index Cond: (timesheetid = tah.timesheetid)
  • Filter: (upper(key) = 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT'::text)
  • Rows Removed by Filter: 81
  • Buffers: shared hit=23873 read=1851
144. 396.264 396.264 ↑ 7.0 1 3,476

Index Scan using ixtahkvtimesheetapprovalhistoryid on timesheetapprovalhistorykeyvalue kv_ea (cost=0.43..4.90 rows=7 width=87) (actual time=0.114..0.114 rows=1 loops=3,476)

  • Index Cond: (timesheetapprovalhistoryid = tah.id)
  • Filter: (upper(key) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:ACTING-EFFECTIVE-USER'::text)
  • Rows Removed by Filter: 17
  • Buffers: shared hit=8810 read=7713 dirtied=8
145. 97.328 97.328 ↑ 4.0 1 3,476

Index Scan using ixtahkvtimesheetapprovalhistoryid on timesheetapprovalhistorykeyvalue kv_aa (cost=0.43..4.90 rows=4 width=87) (actual time=0.026..0.028 rows=1 loops=3,476)

  • Index Cond: (timesheetapprovalhistoryid = tah.id)
  • Filter: (upper(key) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:ACTING-ACTUAL-USER'::text)
  • Rows Removed by Filter: 17
  • Buffers: shared hit=16512 read=11
146. 0.888 1.738 ↓ 1.1 2,570 1

Hash (cost=239.36..239.36 rows=2,421 width=4) (actual time=1.738..1.738 rows=2,570 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 443kB
  • Buffers: shared hit=188
147. 0.850 0.850 ↓ 1.1 2,570 1

Seq Scan on userinfo ui_1 (cost=0.00..239.36 rows=2,421 width=4) (actual time=0.004..0.850 rows=2,570 loops=1)

  • Filter: ((upper((info3)::text) = ANY ('{ABATEC,BMR,"NOW PERM",PERTEMPS,PIPER,VIVID}'::text[])) OR (info3 IS NULL))
  • Buffers: shared hit=188
148. 0.903 1.847 ↓ 1.0 2,570 1

Hash (cost=213.68..213.68 rows=2,568 width=17) (actual time=1.847..1.847 rows=2,570 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 163kB
  • Buffers: shared hit=188
149. 0.944 0.944 ↓ 1.0 2,570 1

Seq Scan on userinfo ui (cost=0.00..213.68 rows=2,568 width=17) (actual time=0.004..0.944 rows=2,570 loops=1)

  • Buffers: shared hit=188
150. 35.343 78.273 ↑ 1.0 103,552 1

Hash (cost=4,414.70..4,414.70 rows=103,552 width=16) (actual time=78.273..78.273 rows=103,552 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 5878kB
  • Buffers: shared hit=33687 read=670
151. 42.930 42.930 ↑ 1.0 103,552 1

Index Only Scan using dm_timesheetlist_facts_pkey on dm_timesheetlist_facts tslist (cost=0.42..4,414.70 rows=103,552 width=16) (actual time=0.017..42.930 rows=103,552 loops=1)

  • Heap Fetches: 33837
  • Buffers: shared hit=33687 read=670
152. 34.904 67.000 ↓ 1.0 103,552 1

Hash (cost=2,170.40..2,170.40 rows=103,540 width=24) (actual time=67.000..67.000 rows=103,552 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 6687kB
  • Buffers: shared hit=1135
153. 32.096 32.096 ↓ 1.0 103,552 1

Seq Scan on timesheet ts (cost=0.00..2,170.40 rows=103,540 width=24) (actual time=0.004..32.096 rows=103,552 loops=1)

  • Buffers: shared hit=1135