explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Kpn2 : SRAHL first

Settings
# exclusive inclusive rows x rows loops node
1. 15.734 249,234.121 ↑ 1.2 3,906 1

Sort (cost=2,981,415.19..2,981,427.36 rows=4,867 width=353) (actual time=249,233.128..249,234.121 rows=3,906 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: 2098kB
  • Buffers: shared hit=11392890 read=462262 dirtied=6325
2. 8.934 249,218.387 ↑ 1.2 3,906 1

Nested Loop (cost=59.77..2,981,117.12 rows=4,867 width=353) (actual time=97,744.290..249,218.387 rows=3,906 loops=1)

  • Join Filter: (ts.id = tslist.timesheetid)
  • Buffers: shared hit=11392890 read=462262 dirtied=6325
3. 6.227 249,189.923 ↑ 1.2 3,906 1

Nested Loop (cost=59.36..2,978,888.37 rows=4,867 width=385) (actual time=97,744.239..249,189.923 rows=3,906 loops=1)

  • Buffers: shared hit=11379732 read=462228 dirtied=6325
4. 211.397 249,171.978 ↑ 1.2 3,906 1

Hash Join (cost=58.94..2,976,696.65 rows=4,867 width=361) (actual time=97,744.214..249,171.978 rows=3,906 loops=1)

  • Hash Cond: ("*SELECT* 1".userid = ui.id)
  • Buffers: shared hit=11364110 read=462226 dirtied=6325
5. 288.193 248,960.458 ↓ 1.4 462,008 1

Append (cost=0.85..2,975,383.45 rows=321,719 width=348) (actual time=4.824..248,960.458 rows=462,008 loops=1)

  • Buffers: shared hit=11363995 read=462226 dirtied=6325
6. 0.000 0.007 ↓ 0.0 0 1

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

  • Buffers: shared hit=1
7. 0.001 0.007 ↓ 0.0 0 1

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

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

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

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

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

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

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

  • Buffers: shared hit=1
11. 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[]))
12. 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 >= '2019-01-06'::date) AND (startdate <= '2019-01-12'::date))
  • Filter: ((enddate >= '2019-01-06'::date) AND (enddate <= '2019-01-12'::date) AND (at.entrydate <= enddate))
13. 0.000 0.000 ↓ 0.0 0

Index Scan using ixtpsstimesheetid on timesheetpolicysettingssnapshot tsformat (cost=0.43..267.04 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))
14. 0.000 0.018 ↓ 0.0 0 1

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

15. 0.016 0.018 ↓ 0.0 0 1

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

  • One-Time Filter: (102 = ANY ('{1,2,3,4,6,100,101,102}'::integer[]))
16. 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)

17. 0.000 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)
18. 0.001 0.001 ↓ 0.0 0 1

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

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

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

20. 0.000 0.000 ↓ 0.0 0

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

21. 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 >= '2019-01-06'::date) AND (startdate <= '2019-01-12'::date))
  • Filter: ((enddate >= '2019-01-06'::date) AND (enddate <= '2019-01-12'::date) AND (at_1.entrydate <= enddate))
22. 113.565 105,063.008 ↓ 42.7 140,924 1

Subquery Scan on *SELECT* 3 (cost=2.97..13,063.95 rows=3,299 width=348) (actual time=4.795..105,063.008 rows=140,924 loops=1)

  • Buffers: shared hit=1951183 read=158652 dirtied=1751
23. 300.313 104,949.443 ↓ 42.7 140,924 1

Nested Loop Left Join (cost=2.97..13,030.96 rows=3,299 width=208) (actual time=4.794..104,949.443 rows=140,924 loops=1)

  • Buffers: shared hit=1951183 read=158652 dirtied=1751
24. 341.815 102,112.498 ↓ 195.5 140,924 1

Nested Loop Left Join (cost=2.41..11,001.75 rows=721 width=162) (actual time=4.774..102,112.498 rows=140,924 loops=1)

  • Buffers: shared hit=1246297 read=156306 dirtied=1735
25. 273.490 89,933.067 ↓ 891.9 140,924 1

Nested Loop Left Join (cost=1.85..10,557.07 rows=158 width=100) (actual time=3.881..89,933.067 rows=140,924 loops=1)

  • Buffers: shared hit=558587 read=139150 dirtied=283
26. 118.171 8,628.277 ↓ 891.9 140,924 1

Nested Loop (cost=1.29..10,246.65 rows=158 width=100) (actual time=3.375..8,628.277 rows=140,924 loops=1)

  • Buffers: shared hit=102252 read=31596 dirtied=283
27. 7.220 3,381.306 ↓ 178.1 2,137 1

Nested Loop Semi Join (cost=0.85..6,152.51 rows=12 width=28) (actual time=1.512..3,381.306 rows=2,137 loops=1)

  • Buffers: shared hit=6892 read=7622 dirtied=25
28. 10.448 10.448 ↓ 54.8 2,137 1

Index Scan using ixtsuseridstartdateenddate on timesheet ts_3 (cost=0.42..2,165.66 rows=39 width=28) (actual time=0.012..10.448 rows=2,137 loops=1)

  • Index Cond: ((startdate >= '2019-01-06'::date) AND (startdate <= '2019-01-12'::date) AND (enddate >= '2019-01-06'::date) AND (enddate <= '2019-01-12'::date))
  • Buffers: shared hit=1784 read=508 dirtied=24
29. 3,363.638 3,363.638 ↑ 3.0 1 2,137

Index Scan using ixtpsstimesheetid on timesheetpolicysettingssnapshot (cost=0.43..265.73 rows=3 width=16) (actual time=1.574..1.574 rows=1 loops=2,137)

  • Index Cond: (timesheetid = ts_3.id)
  • Filter: (upper(uri) = 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT:GEN4-TIMESHEET'::text)
  • Rows Removed by Filter: 37
  • Buffers: shared hit=5108 read=7114 dirtied=1
30. 5,128.800 5,128.800 ↓ 4.7 66 2,137

Index Scan using ixter2userid on timeentryrevision rev (cost=0.43..341.04 rows=14 width=88) (actual time=0.696..2.400 rows=66 loops=2,137)

  • 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=95360 read=23974 dirtied=258
31. 81,031.300 81,031.300 ↓ 0.0 0 140,924

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue changereason (cost=0.56..1.95 rows=1 width=16) (actual time=0.575..0.575 rows=0 loops=140,924)

  • Index Cond: ((revisionauditid = rev.openingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:COMMENT'::text))
  • Buffers: shared hit=456335 read=107554
32. 11,837.616 11,837.616 ↑ 7.0 1 140,924

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue actualuser (cost=0.56..2.74 rows=7 width=78) (actual time=0.084..0.084 rows=1 loops=140,924)

  • Index Cond: ((revisionauditid = rev.openingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:ACTUAL-USER'::text))
  • Buffers: shared hit=687710 read=17156 dirtied=1452
33. 2,536.632 2,536.632 ↑ 7.0 1 140,924

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue effectiveuser (cost=0.56..2.74 rows=7 width=78) (actual time=0.008..0.018 rows=1 loops=140,924)

  • Index Cond: ((revisionauditid = rev.openingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:EFFECTIVE-USER'::text))
  • Buffers: shared hit=704886 read=2346 dirtied=16
34. 144.559 53,298.435 ↓ 226,374.0 226,374 1

Subquery Scan on *SELECT* 4 (cost=281,287.57..301,366.01 rows=1 width=348) (actual time=11,653.018..53,298.435 rows=226,374 loops=1)

  • Buffers: shared hit=3894352 read=189660 dirtied=1213
35. 187.241 53,153.876 ↓ 226,374.0 226,374 1

Result (cost=281,287.57..301,366.00 rows=1 width=208) (actual time=11,653.016..53,153.876 rows=226,374 loops=1)

  • One-Time Filter: (101 = ANY ('{1,2,3,4,6,100,101,102}'::integer[]))
  • Buffers: shared hit=3894352 read=189660 dirtied=1213
36. 270.168 52,966.635 ↓ 226,374.0 226,374 1

Nested Loop Left Join (cost=281,287.57..301,366.00 rows=1 width=208) (actual time=11,653.004..52,966.635 rows=226,374 loops=1)

  • Buffers: shared hit=3894352 read=189660 dirtied=1213
37. 283.969 51,790.971 ↓ 226,374.0 226,374 1

Nested Loop Left Join (cost=281,287.00..301,365.17 rows=1 width=162) (actual time=11,652.985..51,790.971 rows=226,374 loops=1)

  • Buffers: shared hit=2762073 read=189660 dirtied=1213
38. 435.347 50,148.758 ↓ 226,374.0 226,374 1

Nested Loop Anti Join (cost=281,286.44..301,364.35 rows=1 width=100) (actual time=11,652.962..50,148.758 rows=226,374 loops=1)

  • Buffers: shared hit=1626130 read=189660 dirtied=1213
39. 271.748 44,592.816 ↓ 14.8 341,373 1

Nested Loop (cost=281,286.01..290,511.99 rows=23,113 width=116) (actual time=11,642.085..44,592.816 rows=341,373 loops=1)

  • Buffers: shared hit=268754 read=179472 dirtied=724
40. 544.261 12,306.540 ↓ 63.4 43,976 1

Hash Join (cost=281,285.45..289,251.70 rows=694 width=36) (actual time=11,640.672..12,306.540 rows=43,976 loops=1)

  • Hash Cond: (ts_4.userid = timeentryrevision_1.userid)
  • Join Filter: ((timeentryrevision_1.entrydate >= ts_4.startdate) AND (timeentryrevision_1.entrydate <= ts_4.enddate))
  • Rows Removed by Join Filter: 1908365
  • Buffers: shared hit=36668 read=107795 dirtied=127
41. 116.563 122.797 ↓ 178.1 2,137 1

Nested Loop Semi Join (cost=0.85..6,152.51 rows=12 width=28) (actual time=0.091..122.797 rows=2,137 loops=1)

  • Buffers: shared hit=14437 read=17
  • -> Index Scan using ixtpsstimesheetid on timesheetpolicysettingssnapshot timesheetpolicysettingssnapshot_1 (cost=0.43..265.73 rows=3 width=16) (actual time=0.053..0.053 rows=1 loop (...)
42. 6.234 6.234 ↓ 54.8 2,137 1

Index Scan using ixtsuseridstartdateenddate on timesheet ts_4 (cost=0.42..2,165.66 rows=39 width=28) (actual time=0.018..6.234 rows=2,137 loops=1)

  • Index Cond: ((startdate >= '2019-01-06'::date) AND (startdate <= '2019-01-12'::date) AND (enddate >= '2019-01-06'::date) AND (enddate <= '2019-01-12'::date))
  • Buffers: shared hit=2215 read=17
  • Index Cond: (timesheetid = ts_4.id)
  • Filter: (upper(uri) = 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT:GEN4-TIMESHEET'::text)
  • Rows Removed by Filter: 37
  • Buffers: shared hit=12222
43. 794.839 11,639.482 ↓ 1.8 2,038,907 1

Hash (cost=266,947.11..266,947.11 rows=1,146,999 width=24) (actual time=11,639.482..11,639.482 rows=2,038,907 loops=1)

  • Buckets: 2097152 Batches: 1 Memory Usage: 127887kB
  • Buffers: shared hit=22231 read=107778 dirtied=127
44. 4,549.143 10,844.643 ↓ 1.8 2,038,907 1

HashAggregate (cost=244,007.13..255,477.12 rows=1,146,999 width=24) (actual time=9,737.143..10,844.643 rows=2,038,907 loops=1)

  • Group Key: timeentryrevision_1.timeentryid, timeentryrevision_1.userid, timeentryrevision_1.entrydate
  • Buffers: shared hit=22231 read=107778 dirtied=127
45. 6,295.500 6,295.500 ↓ 1.0 6,516,769 1

Seq Scan on timeentryrevision timeentryrevision_1 (cost=0.00..195,150.79 rows=6,514,179 width=24) (actual time=0.002..6,295.500 rows=6,516,769 loops=1)

  • Buffers: shared hit=22231 read=107778 dirtied=127
46. 32,014.528 32,014.528 ↑ 4.1 8 43,976

Index Scan using ixtemrtimeentryid on timeentrymetadatarevision mdrev (cost=0.56..1.49 rows=33 width=80) (actual time=0.445..0.728 rows=8 loops=43,976)

  • Index Cond: (timeentryid = timeentryrevision_1.timeentryid)
  • Buffers: shared hit=232086 read=71677 dirtied=597
47. 5,120.595 5,120.595 ↓ 0.0 0 341,373

Index Scan using timeentryrevision_singleinitialrevision on timeentryrevision (cost=0.43..0.46 rows=1 width=24) (actual time=0.015..0.015 rows=0 loops=341,373)

  • Index Cond: (timeentryid = mdrev.timeentryid)
  • Filter: (fromtimestamputc = mdrev.fromtimestamputc)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=1357376 read=10188 dirtied=489
48. 1,358.244 1,358.244 ↑ 7.0 1 226,374

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

  • Index Cond: ((revisionauditid = mdrev.openingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:EFFECTIVE-USER'::text))
  • Buffers: shared hit=1135943
49. 905.496 905.496 ↑ 7.0 1 226,374

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

  • Index Cond: ((revisionauditid = mdrev.openingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:ACTUAL-USER'::text))
  • Buffers: shared hit=1132279
50. 34.418 31,139.759 ↑ 3.8 45,949 1

Subquery Scan on *SELECT* 5 (cost=281,288.57..542,440.51 rows=175,487 width=348) (actual time=7,438.592..31,139.759 rows=45,949 loops=1)

  • Buffers: shared hit=3333496 read=42504 dirtied=1640
51. 49.612 31,105.341 ↑ 3.8 45,949 1

Result (cost=281,288.57..540,685.64 rows=175,487 width=184) (actual time=7,438.590..31,105.341 rows=45,949 loops=1)

  • One-Time Filter: (101 = ANY ('{1,2,3,4,6,100,101,102}'::integer[]))
  • Buffers: shared hit=3333496 read=42504 dirtied=1640
52. 57.127 31,055.729 ↑ 3.8 45,949 1

Nested Loop Anti Join (cost=281,288.57..540,685.64 rows=175,487 width=184) (actual time=7,438.581..31,055.729 rows=45,949 loops=1)

  • Buffers: shared hit=3333496 read=42504 dirtied=1640
53. 123.345 12,254.353 ↑ 2.9 60,661 1

Nested Loop Left Join (cost=281,287.69..352,823.98 rows=175,487 width=200) (actual time=7,437.861..12,254.353 rows=60,661 loops=1)

  • Buffers: shared hit=2760402 read=1777 dirtied=19
54. 100.935 11,827.703 ↓ 1.6 60,661 1

Nested Loop Left Join (cost=281,287.13..320,409.89 rows=38,386 width=154) (actual time=7,437.852..11,827.703 rows=60,661 loops=1)

  • Buffers: shared hit=2456018 read=1738 dirtied=19
55. 302.730 10,331.565 ↓ 7.2 60,661 1

Nested Loop Anti Join (cost=281,286.57..313,310.81 rows=8,407 width=92) (actual time=7,437.840..10,331.565 rows=60,661 loops=1)

  • Buffers: shared hit=2154331
56. 211.593 8,752.305 ↓ 16.1 255,306 1

Nested Loop (cost=281,286.01..290,442.59 rows=15,826 width=140) (actual time=7,437.758..8,752.305 rows=255,306 loops=1)

  • Buffers: shared hit=447920
57. 549.334 8,100.952 ↓ 63.4 43,976 1

Hash Join (cost=281,285.45..289,251.70 rows=694 width=36) (actual time=7,437.688..8,100.952 rows=43,976 loops=1)

  • Hash Cond: (ts_5.userid = timeentryrevision_2.userid)
  • Join Filter: ((timeentryrevision_2.entrydate >= ts_5.startdate) AND (timeentryrevision_2.entrydate <= ts_5.enddate))
  • Rows Removed by Join Filter: 1908365
  • Buffers: shared hit=144463
58. 117.289 123.304 ↓ 178.1 2,137 1

Nested Loop Semi Join (cost=0.85..6,152.51 rows=12 width=28) (actual time=0.055..123.304 rows=2,137 loops=1)

  • Buffers: shared hit=14454
  • -> Index Scan using ixtpsstimesheetid on timesheetpolicysettingssnapshot timesheetpolicysettingssnapshot_2 (cost=0.43..265.73 rows=3 width=16) (actual time=0.053..0.053 rows= (...)
59. 6.015 6.015 ↓ 54.8 2,137 1

Index Scan using ixtsuseridstartdateenddate on timesheet ts_5 (cost=0.42..2,165.66 rows=39 width=28) (actual time=0.010..6.015 rows=2,137 loops=1)

  • Index Cond: ((startdate >= '2019-01-06'::date) AND (startdate <= '2019-01-12'::date) AND (enddate >= '2019-01-06'::date) AND (enddate <= '2019-01-12'::date))
  • Buffers: shared hit=2232
  • Index Cond: (timesheetid = ts_5.id)
  • Filter: (upper(uri) = 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT:GEN4-TIMESHEET'::text)
  • Rows Removed by Filter: 37
  • Buffers: shared hit=12222
60. 711.908 7,428.314 ↓ 1.8 2,038,907 1

Hash (cost=266,947.11..266,947.11 rows=1,146,999 width=24) (actual time=7,428.314..7,428.314 rows=2,038,907 loops=1)

  • Buckets: 2097152 Batches: 1 Memory Usage: 127887kB
  • Buffers: shared hit=130009
61. 4,195.893 6,716.406 ↓ 1.8 2,038,907 1

HashAggregate (cost=244,007.13..255,477.12 rows=1,146,999 width=24) (actual time=5,663.180..6,716.406 rows=2,038,907 loops=1)

  • Group Key: timeentryrevision_2.timeentryid, timeentryrevision_2.userid, timeentryrevision_2.entrydate
  • Buffers: shared hit=130009
62. 2,520.513 2,520.513 ↓ 1.0 6,516,769 1

Seq Scan on timeentryrevision timeentryrevision_2 (cost=0.00..195,150.79 rows=6,514,179 width=24) (actual time=0.003..2,520.513 rows=6,516,769 loops=1)

  • Buffers: shared hit=130009
63. 439.760 439.760 ↑ 3.8 6 43,976

Index Scan using ixtemrtimeentryid on timeentrymetadatarevision mdrev_1 (cost=0.56..1.49 rows=23 width=104) (actual time=0.006..0.010 rows=6 loops=43,976)

  • Index Cond: (timeentryid = timeentryrevision_2.timeentryid)
  • Filter: (totimestamputc IS NOT NULL)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=303457
64. 1,276.530 1,276.530 ↑ 1.0 1 255,306

Index Scan using ixtemrtimeentryid on timeentrymetadatarevision (cost=0.56..1.43 rows=1 width=88) (actual time=0.005..0.005 rows=1 loops=255,306)

  • 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=1706411
65. 1,395.203 1,395.203 ↑ 7.0 1 60,661

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue actualuser_2 (cost=0.56..0.77 rows=7 width=78) (actual time=0.023..0.023 rows=1 loops=60,661)

  • Index Cond: ((revisionauditid = mdrev_1.closingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:ACTUAL-USER'::text))
  • Buffers: shared hit=301687 read=1738 dirtied=19
66. 303.305 303.305 ↑ 7.0 1 60,661

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue effectiveuser_2 (cost=0.56..0.77 rows=7 width=78) (actual time=0.005..0.005 rows=1 loops=60,661)

  • Index Cond: ((revisionauditid = mdrev_1.closingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:EFFECTIVE-USER'::text))
  • Buffers: shared hit=304384 read=39
67. 121.322 18,744.249 ↓ 0.0 0 60,661

Nested Loop Left Join (cost=0.86..1.06 rows=1 width=24) (actual time=0.309..0.309 rows=0 loops=60,661)

  • Filter: (child_rev.id IS NULL)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=573094 read=40727 dirtied=1621
68. 12,071.539 12,071.539 ↑ 1.0 1 60,661

Index Scan using ixtertimeentryid on timeentryrevision rev_1 (cost=0.43..0.57 rows=1 width=40) (actual time=0.197..0.199 rows=1 loops=60,661)

  • Index Cond: (timeentryid = mdrev_1.timeentryid)
  • Filter: (totimestamputc = mdrev_1.totimestamputc)
  • Rows Removed by Filter: 3
  • Buffers: shared hit=364091 read=19948 dirtied=506
69. 6,551.388 6,551.388 ↑ 1.0 1 60,661

Index Scan using timeentryrevision_uniquepreviousrevisionid on timeentryrevision child_rev (cost=0.43..0.48 rows=1 width=32) (actual time=0.106..0.108 rows=1 loops=60,661)

  • Index Cond: (previousrevisionid = rev_1.id)
  • Buffers: shared hit=208885 read=20779 dirtied=997
70. 25.090 40,400.298 ↓ 34,414.0 34,414 1

Subquery Scan on *SELECT* 6 (cost=281,287.58..374,427.63 rows=1 width=348) (actual time=7,423.240..40,400.298 rows=34,414 loops=1)

  • Buffers: shared hit=801730 read=54537 dirtied=555
71. 35.516 40,375.208 ↓ 34,414.0 34,414 1

Result (cost=281,287.58..374,427.62 rows=1 width=208) (actual time=7,423.237..40,375.208 rows=34,414 loops=1)

  • One-Time Filter: (101 = ANY ('{1,2,3,4,6,100,101,102}'::integer[]))
  • Buffers: shared hit=801730 read=54537 dirtied=555
72. 67.649 40,339.692 ↓ 34,414.0 34,414 1

Nested Loop Left Join (cost=281,287.58..374,427.62 rows=1 width=208) (actual time=7,423.223..40,339.692 rows=34,414 loops=1)

  • Buffers: shared hit=801730 read=54537 dirtied=555
73. 55.664 40,134.387 ↓ 34,414.0 34,414 1

Nested Loop Left Join (cost=281,287.01..374,426.83 rows=1 width=162) (actual time=7,423.211..40,134.387 rows=34,414 loops=1)

  • Buffers: shared hit=629598 read=54537 dirtied=555
74. 40.024 39,803.411 ↓ 34,414.0 34,414 1

Nested Loop Anti Join (cost=281,286.45..374,426.05 rows=1 width=100) (actual time=7,423.187..39,803.411 rows=34,414 loops=1)

  • Buffers: shared hit=456913 read=54537 dirtied=555
75. 70.665 39,557.552 ↑ 4.2 41,167 1

Nested Loop (cost=281,286.02..295,441.04 rows=171,869 width=116) (actual time=7,419.367..39,557.552 rows=41,167 loops=1)

  • Buffers: shared hit=291978 read=54535 dirtied=555
76. 584.039 8,131.999 ↓ 63.4 43,976 1

Hash Join (cost=281,285.45..289,251.70 rows=694 width=36) (actual time=7,418.693..8,131.999 rows=43,976 loops=1)

  • Hash Cond: (ts_6.userid = timeentryrevision_4.userid)
  • Join Filter: ((timeentryrevision_4.entrydate >= ts_6.startdate) AND (timeentryrevision_4.entrydate <= ts_6.enddate))
  • Rows Removed by Join Filter: 1908365
  • Buffers: shared hit=144463
77. 131.481 138.569 ↓ 178.1 2,137 1

Nested Loop Semi Join (cost=0.85..6,152.51 rows=12 width=28) (actual time=0.067..138.569 rows=2,137 loops=1)

  • Buffers: shared hit=14454
  • -> Index Scan using ixtpsstimesheetid on timesheetpolicysettingssnapshot timesheetpolicysettingssnapshot_3 (cost=0.43..265.73 rows=3 width=16) (actual time=0.059..0.059 rows=1 loop (...)
78. 7.088 7.088 ↓ 54.8 2,137 1

Index Scan using ixtsuseridstartdateenddate on timesheet ts_6 (cost=0.42..2,165.66 rows=39 width=28) (actual time=0.009..7.088 rows=2,137 loops=1)

  • Index Cond: ((startdate >= '2019-01-06'::date) AND (startdate <= '2019-01-12'::date) AND (enddate >= '2019-01-06'::date) AND (enddate <= '2019-01-12'::date))
  • Buffers: shared hit=2232
  • Index Cond: (timesheetid = ts_6.id)
  • Filter: (upper(uri) = 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT:GEN4-TIMESHEET'::text)
  • Rows Removed by Filter: 37
  • Buffers: shared hit=12222
79. 706.210 7,409.391 ↓ 1.8 2,038,907 1

Hash (cost=266,947.11..266,947.11 rows=1,146,999 width=24) (actual time=7,409.391..7,409.391 rows=2,038,907 loops=1)

  • Buckets: 2097152 Batches: 1 Memory Usage: 127887kB
  • Buffers: shared hit=130009
80. 4,174.831 6,703.181 ↓ 1.8 2,038,907 1

HashAggregate (cost=244,007.13..255,477.12 rows=1,146,999 width=24) (actual time=5,664.150..6,703.181 rows=2,038,907 loops=1)

  • Group Key: timeentryrevision_4.timeentryid, timeentryrevision_4.userid, timeentryrevision_4.entrydate
  • Buffers: shared hit=130009
81. 2,528.350 2,528.350 ↓ 1.0 6,516,769 1

Seq Scan on timeentryrevision timeentryrevision_4 (cost=0.00..195,150.79 rows=6,514,179 width=24) (actual time=0.003..2,528.350 rows=6,516,769 loops=1)

  • Buffers: shared hit=130009
82. 31,354.888 31,354.888 ↑ 248.0 1 43,976

Index Scan using ixoefvrobjectid on objectextensionfieldvaluerevision oefvr (cost=0.57..6.44 rows=248 width=80) (actual time=0.666..0.713 rows=1 loops=43,976)

  • Index Cond: (objectid = timeentryrevision_4.timeentryid)
  • Buffers: shared hit=147515 read=54535 dirtied=555
83. 205.835 205.835 ↓ 0.0 0 41,167

Index Scan using timeentryrevision_singleinitialrevision on timeentryrevision timeentryrevision_3 (cost=0.43..0.45 rows=1 width=24) (actual time=0.005..0.005 rows=0 loops=41,167)

  • Index Cond: (timeentryid = oefvr.objectid)
  • Filter: (fromtimestamputc = oefvr.fromtimestamputc)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=164935 read=2
84. 275.312 275.312 ↑ 7.0 1 34,414

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue effectiveuser_3 (cost=0.56..0.71 rows=7 width=78) (actual time=0.008..0.008 rows=1 loops=34,414)

  • Index Cond: ((revisionauditid = oefvr.openingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:EFFECTIVE-USER'::text))
  • Buffers: shared hit=172685
85. 137.656 137.656 ↑ 7.0 1 34,414

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue actualuser_3 (cost=0.56..0.71 rows=7 width=78) (actual time=0.004..0.004 rows=1 loops=34,414)

  • Index Cond: ((revisionauditid = oefvr.openingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:ACTUAL-USER'::text))
  • Buffers: shared hit=172132
86. 0.545 8,485.310 ↑ 169.3 844 1

Subquery Scan on *SELECT* 7 (cost=281,288.58..1,660,058.32 rows=142,882 width=348) (actual time=7,473.921..8,485.310 rows=844 loops=1)

  • Buffers: shared hit=522832 read=262 dirtied=16
87. 0.791 8,484.765 ↑ 169.3 844 1

Result (cost=281,288.58..1,658,629.50 rows=142,882 width=184) (actual time=7,473.919..8,484.765 rows=844 loops=1)

  • One-Time Filter: (101 = ANY ('{1,2,3,4,6,100,101,102}'::integer[]))
  • Buffers: shared hit=522832 read=262 dirtied=16
88. 1.488 8,483.974 ↑ 169.3 844 1

Nested Loop Anti Join (cost=281,288.58..1,658,629.50 rows=142,882 width=184) (actual time=7,473.907..8,483.974 rows=844 loops=1)

  • Buffers: shared hit=522832 read=262 dirtied=16
89. 5.418 8,360.918 ↑ 37.6 3,799 1

Nested Loop Left Join (cost=281,287.71..1,508,247.48 rows=142,882 width=200) (actual time=7,473.869..8,360.918 rows=3,799 loops=1)

  • Buffers: shared hit=491814 read=4
90. 3.412 8,344.103 ↑ 37.6 3,799 1

Nested Loop Left Join (cost=281,287.15..1,396,751.75 rows=142,882 width=154) (actual time=7,473.861..8,344.103 rows=3,799 loops=1)

  • Buffers: shared hit=472815 read=4
91. 13.444 8,317.897 ↑ 37.6 3,799 1

Nested Loop Anti Join (cost=281,286.59..1,285,256.02 rows=142,882 width=92) (actual time=7,473.847..8,317.897 rows=3,799 loops=1)

  • Buffers: shared hit=453706
92. 46.350 8,246.448 ↑ 7.5 19,335 1

Nested Loop (cost=281,286.02..295,177.32 rows=145,429 width=108) (actual time=7,472.863..8,246.448 rows=19,335 loops=1)

  • Buffers: shared hit=346444
93. 460.972 8,024.194 ↓ 63.4 43,976 1

Hash Join (cost=281,285.45..289,251.70 rows=694 width=36) (actual time=7,472.708..8,024.194 rows=43,976 loops=1)

  • Hash Cond: (ts_7.userid = timeentryrevision_5.userid)
  • Join Filter: ((timeentryrevision_5.entrydate >= ts_7.startdate) AND (timeentryrevision_5.entrydate <= ts_7.enddate))
  • Rows Removed by Join Filter: 1908365
  • Buffers: shared hit=144463
94. 95.203 99.883 ↓ 178.1 2,137 1

Nested Loop Semi Join (cost=0.85..6,152.51 rows=12 width=28) (actual time=0.060..99.883 rows=2,137 loops=1)

  • Buffers: shared hit=14454
  • -> Index Scan using ixtpsstimesheetid on timesheetpolicysettingssnapshot timesheetpolicysettingssnapshot_4 (cost=0.43..265.73 rows=3 width=16) (actual time=0.043..0.043 rows= (...)
95. 4.680 4.680 ↓ 54.8 2,137 1

Index Scan using ixtsuseridstartdateenddate on timesheet ts_7 (cost=0.42..2,165.66 rows=39 width=28) (actual time=0.011..4.680 rows=2,137 loops=1)

  • Index Cond: ((startdate >= '2019-01-06'::date) AND (startdate <= '2019-01-12'::date) AND (enddate >= '2019-01-06'::date) AND (enddate <= '2019-01-12'::date))
  • Buffers: shared hit=2232
  • Index Cond: (timesheetid = ts_7.id)
  • Filter: (upper(uri) = 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT:GEN4-TIMESHEET'::text)
  • Rows Removed by Filter: 37
  • Buffers: shared hit=12222
96. 716.102 7,463.339 ↓ 1.8 2,038,907 1

Hash (cost=266,947.11..266,947.11 rows=1,146,999 width=24) (actual time=7,463.339..7,463.339 rows=2,038,907 loops=1)

  • Buckets: 2097152 Batches: 1 Memory Usage: 127887kB
  • Buffers: shared hit=130009
97. 4,218.994 6,747.237 ↓ 1.8 2,038,907 1

HashAggregate (cost=244,007.13..255,477.12 rows=1,146,999 width=24) (actual time=5,705.011..6,747.237 rows=2,038,907 loops=1)

  • Group Key: timeentryrevision_5.timeentryid, timeentryrevision_5.userid, timeentryrevision_5.entrydate
  • Buffers: shared hit=130009
98. 2,528.243 2,528.243 ↓ 1.0 6,516,769 1

Seq Scan on timeentryrevision timeentryrevision_5 (cost=0.00..195,150.79 rows=6,514,179 width=24) (actual time=0.004..2,528.243 rows=6,516,769 loops=1)

  • Buffers: shared hit=130009
99. 175.904 175.904 ↓ 0.0 0 43,976

Index Scan using ixoefvrobjectid on objectextensionfieldvaluerevision oefvr_1 (cost=0.57..6.44 rows=210 width=72) (actual time=0.004..0.004 rows=0 loops=43,976)

  • Index Cond: (objectid = timeentryrevision_5.timeentryid)
  • Filter: (totimestamputc IS NOT NULL)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=201981
100. 58.005 58.005 ↑ 1.0 1 19,335

Index Scan using ixoefvrobjectid on objectextensionfieldvaluerevision (cost=0.57..6.80 rows=1 width=56) (actual time=0.003..0.003 rows=1 loops=19,335)

  • 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=107262
101. 22.794 22.794 ↑ 7.0 1 3,799

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

  • Index Cond: ((revisionauditid = oefvr_1.closingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:EFFECTIVE-USER'::text))
  • Buffers: shared hit=19109 read=4
102. 11.397 11.397 ↑ 7.0 1 3,799

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,799)

  • Index Cond: ((revisionauditid = oefvr_1.closingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:ACTUAL-USER'::text))
  • Buffers: shared hit=18999
103. 7.598 121.568 ↑ 1.0 1 3,799

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

  • Filter: (child_rev_1.id IS NULL)
  • Rows Removed by Filter: 0
  • Buffers: shared hit=31018 read=258 dirtied=16
104. 83.578 83.578 ↑ 1.0 1 3,799

Index Scan using ixtertimeentryid on timeentryrevision rev_2 (cost=0.43..0.56 rows=1 width=40) (actual time=0.021..0.022 rows=1 loops=3,799)

  • Index Cond: (timeentryid = oefvr_1.objectid)
  • Filter: (totimestamputc = oefvr_1.totimestamputc)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=18838 read=166 dirtied=4
105. 30.392 30.392 ↓ 0.0 0 3,799

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

  • Index Cond: (previousrevisionid = rev_2.id)
  • Buffers: shared hit=12180 read=92 dirtied=12
106. 5.468 1,733.618 ↓ 9,430.0 9,430 1

Subquery Scan on *SELECT* 8 (cost=2.97..20,219.97 rows=1 width=348) (actual time=0.153..1,733.618 rows=9,430 loops=1)

  • Buffers: shared hit=665402 read=6228 dirtied=1007
107. 7.286 1,728.150 ↓ 9,430.0 9,430 1

Result (cost=2.97..20,219.96 rows=1 width=184) (actual time=0.151..1,728.150 rows=9,430 loops=1)

  • One-Time Filter: (102 = ANY ('{1,2,3,4,6,100,101,102}'::integer[]))
  • Buffers: shared hit=665402 read=6228 dirtied=1007
108. 14.113 1,720.864 ↓ 9,430.0 9,430 1

Nested Loop Left Join (cost=2.97..20,219.96 rows=1 width=184) (actual time=0.137..1,720.864 rows=9,430 loops=1)

  • Buffers: shared hit=665402 read=6228 dirtied=1007
109. 7.866 1,669.031 ↓ 9,430.0 9,430 1

Nested Loop Left Join (cost=2.40..20,219.24 rows=1 width=200) (actual time=0.131..1,669.031 rows=9,430 loops=1)

  • Buffers: shared hit=627648 read=6228 dirtied=1007
110. 14.301 1,623.445 ↓ 9,430.0 9,430 1

Nested Loop Left Join (cost=1.84..20,218.35 rows=1 width=138) (actual time=0.125..1,623.445 rows=9,430 loops=1)

  • Buffers: shared hit=580463 read=6228 dirtied=1007
111. 132.394 1,307.384 ↓ 9,430.0 9,430 1

Nested Loop Left Join (cost=1.28..20,217.46 rows=1 width=76) (actual time=0.115..1,307.384 rows=9,430 loops=1)

  • Filter: (child_rev_2.id IS NULL)
  • Rows Removed by Filter: 96948
  • Buffers: shared hit=533457 read=5836 dirtied=1007
112. 57.128 217.588 ↓ 11.0 106,378 1

Nested Loop (cost=0.85..15,432.23 rows=9,645 width=76) (actual time=0.029..217.588 rows=106,378 loops=1)

  • Buffers: shared hit=117870 read=3397
113. 4.459 4.459 ↓ 54.8 2,137 1

Index Scan using ixtsuseridstartdateenddate on timesheet ts_8 (cost=0.42..2,165.66 rows=39 width=28) (actual time=0.009..4.459 rows=2,137 loops=1)

  • Index Cond: ((startdate >= '2019-01-06'::date) AND (startdate <= '2019-01-12'::date) AND (enddate >= '2019-01-06'::date) AND (enddate <= '2019-01-12'::date))
  • Buffers: shared hit=2232
114. 156.001 156.001 ↑ 5.2 50 2,137

Index Scan using ixter2userid on timeentryrevision rev_3 (cost=0.43..337.57 rows=260 width=64) (actual time=0.012..0.073 rows=50 loops=2,137)

  • 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: 16
  • Buffers: shared hit=115638 read=3397
115. 957.402 957.402 ↑ 1.0 1 106,378

Index Scan using timeentryrevision_uniquepreviousrevisionid on timeentryrevision child_rev_2 (cost=0.43..0.49 rows=1 width=32) (actual time=0.009..0.009 rows=1 loops=106,378)

  • Index Cond: (previousrevisionid = rev_3.id)
  • Buffers: shared hit=415587 read=2439 dirtied=1007
116. 301.760 301.760 ↑ 7.0 1 9,430

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue effectiveuser_5 (cost=0.56..0.82 rows=7 width=78) (actual time=0.031..0.032 rows=1 loops=9,430)

  • Index Cond: ((revisionauditid = rev_3.closingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:EFFECTIVE-USER'::text))
  • Buffers: shared hit=47006 read=392
117. 37.720 37.720 ↑ 7.0 1 9,430

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

  • Index Cond: ((revisionauditid = rev_3.closingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:ACTUAL-USER'::text))
  • Buffers: shared hit=47185
118. 37.720 37.720 ↓ 0.0 0 9,430

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=9,430)

  • Index Cond: ((revisionauditid = rev_3.closingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:COMMENT'::text))
  • Buffers: shared hit=37754
119. 2.835 8,551.812 ↓ 88.5 4,073 1

Subquery Scan on *SELECT* 9 (cost=48,587.20..63,501.72 rows=46 width=430) (actual time=1,691.549..8,551.812 rows=4,073 loops=1)

  • Buffers: shared hit=194999 read=10383 dirtied=143
120. 9.533 8,548.977 ↓ 88.5 4,073 1

Nested Loop Left Join (cost=48,587.20..63,501.26 rows=46 width=302) (actual time=1,691.548..8,548.977 rows=4,073 loops=1)

  • Buffers: shared hit=194999 read=10383 dirtied=143
121. 6.931 8,384.670 ↓ 88.5 4,073 1

Nested Loop Left Join (cost=48,586.76..63,270.20 rows=46 width=247) (actual time=1,691.538..8,384.670 rows=4,073 loops=1)

  • Buffers: shared hit=176373 read=10359 dirtied=143
122. 6.879 3,636.767 ↓ 88.5 4,073 1

Nested Loop (cost=48,586.33..63,038.22 rows=46 width=176) (actual time=1,691.523..3,636.767 rows=4,073 loops=1)

  • Join Filter: (ts_9.id = tsformat_1.timesheetid)
  • Buffers: shared hit=165343 read=2739 dirtied=1
123. 80.129 2,554.616 ↓ 81.5 4,073 1

Hash Join (cost=48,585.90..62,615.95 rows=50 width=102) (actual time=1,691.453..2,554.616 rows=4,073 loops=1)

  • Hash Cond: (tah.timesheetid = ts_9.id)
  • Buffers: shared hit=136193 read=1286
124. 796.313 2,469.911 ↓ 1.7 231,508 1

Index Scan using ixtahaction on timesheetapprovalhistory tah (cost=46,419.75..59,935.88 rows=136,911 width=82) (actual time=1,686.850..2,469.911 rows=231,508 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: 138683
  • Buffers: shared hit=133961 read=1286
125.          

SubPlan (forIndex Scan)

126. 17.038 1,673.598 ↓ 24,568.0 24,568 1

Nested Loop (cost=46,068.41..46,419.32 rows=1 width=16) (actual time=1,418.353..1,673.598 rows=24,568 loops=1)

  • Buffers: shared hit=78613 read=487
127. 47.135 1,607.424 ↓ 12,284.0 12,284 1

Hash Join (cost=46,067.98..46,416.86 rows=1 width=24) (actual time=1,418.339..1,607.424 rows=12,284 loops=1)

  • Hash Cond: ((faas.timesheetid = tah_3.timesheetid) AND (faas.nextaction_serialnumber = tah_3.serialnumber))
  • Buffers: shared hit=27652 read=487
128.          

CTE firstactionaftersubmit

129. 239.882 1,423.301 ↓ 10.0 126,395 1

HashAggregate (cost=40,496.29..40,623.15 rows=12,686 width=24) (actual time=1,358.727..1,423.301 rows=126,395 loops=1)

  • Group Key: submissions.timesheetid, submissions.serialnumber
  • Buffers: shared hit=21519 read=435
130.          

CTE submissions

131. 360.753 360.753 ↑ 1.0 126,395 1

Index Scan using ixtahaction on timesheetapprovalhistory (cost=0.42..8,094.36 rows=126,851 width=20) (actual time=0.008..360.753 rows=126,395 loops=1)

  • Index Cond: (action = 1)
  • Buffers: shared hit=17706 read=434
132. 294.272 1,183.419 ↓ 2.0 345,495 1

Merge Join (cost=13,289.83..31,093.45 rows=174,464 width=24) (actual time=520.647..1,183.419 rows=345,495 loops=1)

  • Merge Cond: (submissions.timesheetid = tah_1.timesheetid)
  • Join Filter: (tah_1.serialnumber > submissions.serialnumber)
  • Rows Removed by Join Filter: 218338
  • Buffers: shared hit=21519 read=435
133. 129.519 567.343 ↑ 1.0 126,395 1

Sort (cost=13,289.40..13,606.53 rows=126,851 width=20) (actual time=520.139..567.343 rows=126,395 loops=1)

  • Sort Key: submissions.timesheetid
  • Sort Method: quicksort Memory: 12947kB
  • Buffers: shared hit=17706 read=434
134. 437.824 437.824 ↑ 1.0 126,395 1

CTE Scan on submissions (cost=0.00..2,537.02 rows=126,851 width=20) (actual time=0.009..437.824 rows=126,395 loops=1)

  • Buffers: shared hit=17706 read=434
135. 321.804 321.804 ↓ 1.5 563,840 1

Materialize (cost=0.42..8,936.25 rows=370,333 width=20) (actual time=0.495..321.804 rows=563,840 loops=1)

  • Buffers: shared hit=3813 read=1
  • -> Index Only Scan using ixtah2timesheetid on timesheetapprovalhistory tah_1 (cost=0.42..8010.42 rows=370333 width=20) (actual time=0.492..104.051 rows=370191 (...)
  • Heap Fetches: 2139
  • Buffers: shared hit=3813 read=1
136. 1,500.706 1,500.706 ↓ 10.0 126,395 1

CTE Scan on firstactionaftersubmit faas (cost=0.00..253.72 rows=12,686 width=24) (actual time=1,358.729..1,500.706 rows=126,395 loops=1)

  • Buffers: shared hit=21519 read=435
137. 4.948 59.583 ↓ 163.8 12,287 1

Hash (cost=5,443.70..5,443.70 rows=75 width=20) (actual time=59.583..59.583 rows=12,287 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 752kB
  • Buffers: shared hit=6133 read=52
138. 54.635 54.635 ↓ 163.8 12,287 1

Index Scan using ixtahaction on timesheetapprovalhistory tah_3 (cost=0.42..5,443.70 rows=75 width=20) (actual time=0.037..54.635 rows=12,287 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: 2239
  • Buffers: shared hit=6133 read=52
139. 49.136 49.136 ↓ 2.0 2 12,284

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

  • Index Cond: ((timesheetid = faas.timesheetid) AND (serialnumber >= faas.submission_serialnumber) AND (serialnumber <= faas.nextaction_serialnumber))
  • Buffers: shared hit=50961
140. 0.814 4.576 ↓ 54.8 2,137 1

Hash (cost=2,165.66..2,165.66 rows=39 width=20) (actual time=4.576..4.576 rows=2,137 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 141kB
  • Buffers: shared hit=2232
141. 3.762 3.762 ↓ 54.8 2,137 1

Index Scan using ixtsuseridstartdateenddate on timesheet ts_9 (cost=0.42..2,165.66 rows=39 width=20) (actual time=0.011..3.762 rows=2,137 loops=1)

  • Index Cond: ((startdate >= '2019-01-06'::date) AND (startdate <= '2019-01-12'::date) AND (enddate >= '2019-01-06'::date) AND (enddate <= '2019-01-12'::date))
  • Buffers: shared hit=2232
142. 1,075.272 1,075.272 ↑ 3.0 1 4,073

Index Scan using ixtpsstimesheetid on timesheetpolicysettingssnapshot tsformat_1 (cost=0.43..8.41 rows=3 width=106) (actual time=0.054..0.264 rows=1 loops=4,073)

  • Index Cond: (timesheetid = tah.timesheetid)
  • Filter: (upper(key) = 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT'::text)
  • Rows Removed by Filter: 82
  • Buffers: shared hit=29150 read=1453 dirtied=1
143. 4,740.972 4,740.972 ↑ 7.0 1 4,073

Index Scan using ixtahkvtimesheetapprovalhistoryid on timesheetapprovalhistorykeyvalue kv_ea (cost=0.43..4.97 rows=7 width=87) (actual time=1.164..1.164 rows=1 loops=4,073)

  • 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=11030 read=7620 dirtied=142
144. 154.774 154.774 ↑ 5.0 1 4,073

Index Scan using ixtahkvtimesheetapprovalhistoryid on timesheetapprovalhistorykeyvalue kv_aa (cost=0.43..4.97 rows=5 width=87) (actual time=0.032..0.038 rows=1 loops=4,073)

  • 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=18626 read=24
145. 0.024 0.123 ↑ 1.0 39 1

Hash (cost=57.60..57.60 rows=39 width=17) (actual time=0.123..0.123 rows=39 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=115
146. 0.099 0.099 ↑ 1.0 39 1

Index Scan using userinfo_pkey on userinfo ui (cost=0.28..57.60 rows=39 width=17) (actual time=0.008..0.099 rows=39 loops=1)

  • Index Cond: (id = ANY ('{4540,4458,4444,4571,4294,4296,4462,4453,4547,4340,4319,4311,4489,4470,4473,4382,4315,4457,4312,4472,4397,4483,4356,4381,4407,4496,4393,4426,4403,4404,4509,4518,4384,4406,4502,4411,4408,4419,4529}'::i (...)
  • Buffers: shared hit=115
147. 11.718 11.718 ↑ 1.0 1 3,906

Index Scan using timesheet_pkey on timesheet ts (cost=0.42..0.44 rows=1 width=24) (actual time=0.002..0.003 rows=1 loops=3,906)

  • Index Cond: (id = "*SELECT* 1".timesheetid)
  • Buffers: shared hit=15622 read=2
148. 19.530 19.530 ↑ 1.0 1 3,906

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.005..0.005 rows=1 loops=3,906)

  • Index Cond: (timesheetid = "*SELECT* 1".timesheetid)
  • Heap Fetches: 1028
  • Buffers: shared hit=13158 read=34