explain.depesz.com

PostgreSQL's explain analyze made readable

Result: D3km : baseline - right after restore, original query

Settings
# exclusive inclusive rows x rows loops node
1. 138.048 602,536.332 ↑ 919,176,545.5 4,346 1

Sort (cost=19,550,007,727,768.08..19,559,994,580,935.44 rows=3,994,741,266,942 width=912) (actual time=602,527.768..602,536.332 rows=4,346 loops=1)

  • Sort Key: ((pj.code)::character varying(50)) COLLATE "en_US", ((pj.name)::character varying(255)) COLLATE "en_US", ((cl.name)::character varying(255)) COLLATE "en_US", ((tk.name)::character varying(255)) COLLATE "en_US", ((tk.code)::character varying(50)) COLLATE "en_US", ((ui.lastname)::character varying(50)) COLLATE "en_US", ((ui.firstname)::character varying(50)) COLLATE "en_US", ui.duplicatename, ((login.loginname)::character varying(255)) COLLATE "en_US", ((ui.externalid)::character varying(255)) COLLATE "en_US", ((ui.info5)::character varying(255)) COLLATE "en_US", ((dep.name)::character varying(255)) COLLATE "en_US", "*SELECT* 1".entrydate, ts.startdate, ts.enddate, tslist.timesheetstatus, ((tdh.hierarchysorting)::text) COLLATE "en_US", ((tdh.hierarchytaskname)::text) COLLATE "en_US", ((timeentrymetadata9.text)::text) COLLATE "en_US", ((userinfo10.displayname)::text) COLLATE "en_US", "*SELECT* 1".timeentryid, ((billingrate11.name)::character varying(50)) COLLATE "en_US
  • Sort Method: quicksort Memory: 2743kB
  • Buffers: shared hit=95178 read=620037, temp read=196521 written=170801
2.          

Initplan (forSort)

3. 0.012 0.012 ↑ 1.0 1 1

Seq Scan on systeminformation (cost=0.00..1.01 rows=1 width=4) (actual time=0.009..0.012 rows=1 loops=1)

  • Buffers: shared hit=1
4. 24.312 602,398.272 ↑ 919,176,545.5 4,346 1

Hash Left Join (cost=331,994,488.85..5,933,832,466,484.18 rows=3,994,741,266,942 width=912) (actual time=574,475.626..602,398.272 rows=4,346 loops=1)

  • Hash Cond: ("*SELECT* 1".billingrateid = billingrate11.id)
  • Buffers: shared hit=95167 read=620037, temp read=196521 written=170801
5. 19.781 602,372.152 ↑ 919,176,545.5 4,346 1

Hash Left Join (cost=331,994,476.36..5,878,904,774,051.24 rows=3,994,741,266,942 width=903) (actual time=574,473.798..602,372.152 rows=4,346 loops=1)

  • Hash Cond: (pj.projectleaderapproverid = userinfo10.id)
  • Buffers: shared hit=95163 read=620037, temp read=196521 written=170801
6. 507.704 602,347.118 ↑ 919,176,545.5 4,346 1

Hash Left Join (cost=331,994,436.00..5,824,077,958,895.05 rows=3,994,741,266,942 width=893) (actual time=574,468.524..602,347.118 rows=4,346 loops=1)

  • Hash Cond: ("*SELECT* 1".timeentryid = timeentrymetadata9.timeentryid)
  • Buffers: shared hit=95145 read=620037, temp read=196521 written=170801
7. 22.490 578,677.642 ↑ 919,176,545.5 4,346 1

Hash Join (cost=331,770,419.12..2,627,153,729,763.86 rows=3,994,741,266,942 width=847) (actual time=551,119.458..578,677.642 rows=4,346 loops=1)

  • Hash Cond: ("*SELECT* 1".userid = login.userid)
  • Buffers: shared hit=94445 read=492892, temp read=185442 written=159784
8. 54.885 578,635.763 ↑ 919,176,545.5 4,346 1

Hash Left Join (cost=331,770,320.93..2,572,226,037,245.21 rows=3,994,741,266,942 width=327) (actual time=551,100.046..578,635.763 rows=4,346 loops=1)

  • Hash Cond: (tk.id = tdh.taskid)
  • Buffers: shared hit=94441 read=492871, temp read=185442 written=159784
9. 22.632 578,101.496 ↑ 919,176,545.5 4,346 1

Hash Join (cost=331,764,913.43..2,228,615,864,115.26 rows=3,994,741,266,942 width=268) (actual time=550,620.341..578,101.496 rows=4,346 loops=1)

  • Hash Cond: ("*SELECT* 1".userid = ui.id)
  • Buffers: shared hit=94439 read=490348, temp read=184636 written=158984
10. 21.824 578,073.474 ↑ 919,176,545.5 4,346 1

Hash Left Join (cost=331,764,873.07..2,173,688,171,654.44 rows=3,994,741,266,942 width=229) (actual time=550,614.928..578,073.474 rows=4,346 loops=1)

  • Hash Cond: (COALESCE(clients17.id, pj.clientid) = cl.id)
  • Buffers: shared hit=94421 read=490348, temp read=184636 written=158984
11. 45.751 578,051.296 ↑ 919,176,545.5 4,346 1

Hash Left Join (cost=331,764,870.58..2,145,525,245,720.01 rows=3,994,741,266,942 width=221) (actual time=550,614.556..578,051.296 rows=4,346 loops=1)

  • Hash Cond: (task15.id = tk.id)
  • Buffers: shared hit=94420 read=490348, temp read=184636 written=158984
12. 19.016 577,140.323 ↑ 919,176,545.5 4,346 1

Merge Left Join (cost=331,760,221.95..1,887,739,592,926.94 rows=3,994,741,266,942 width=180) (actual time=549,749.268..577,140.323 rows=4,346 loops=1)

  • Merge Cond: ("*SELECT* 1".timeentryid = timeentrymetadata16.timeentryid)
  • Buffers: shared hit=92828 read=490347, temp read=184200 written=158550
13. 42.442 557,800.465 ↑ 919,176,545.5 4,346 1

Nested Loop Left Join (cost=331,533,788.76..1,876,056,025,985.31 rows=3,994,741,266,942 width=176) (actual time=530,428.418..557,800.465 rows=4,346 loops=1)

  • Buffers: shared hit=92160 read=363170, temp read=184200 written=158550
14. 3,366.595 557,705.871 ↑ 919,176,545.5 4,346 1

Merge Left Join (cost=331,533,788.48..2,058,336,187.80 rows=3,994,741,266,942 width=100) (actual time=530,428.379..557,705.871 rows=4,346 loops=1)

  • Merge Cond: ("*SELECT* 1".timeentryid = timeentrymetadata14.timeentryid)
  • Buffers: shared hit=79126 read=363165, temp read=184200 written=158550
15. 3,352.092 264,164.897 ↑ 2,120,630.2 4,346 1

Merge Left Join (cost=328,708,338.08..335,948,178.28 rows=9,216,258,734 width=92) (actual time=250,404.557..264,164.897 rows=4,346 loops=1)

  • Merge Cond: ("*SELECT* 1".timeentryid = timeentrymetadata12.timeentryid)
  • Buffers: shared hit=76899 read=235956, temp read=94490 written=94478
16. 27.247 21,064.596 ↑ 153,003.6 4,346 1

Sort (cost=328,419,259.07..330,081,643.40 rows=664,953,732 width=88) (actual time=21,054.361..21,064.596 rows=4,346 loops=1)

  • Sort Key: "*SELECT* 1".timeentryid
  • Sort Method: quicksort Memory: 802kB
  • Buffers: shared hit=76193 read=108715, temp read=1094 written=1082
17. 71.618 21,037.349 ↑ 153,003.6 4,346 1

Hash Join (cost=185,672.42..103,698,372.60 rows=664,953,732 width=88) (actual time=18,858.425..21,037.349 rows=4,346 loops=1)

  • Hash Cond: (ts.id = tslist.timesheetid)
  • Buffers: shared hit=76193 read=108715, temp read=1094 written=1082
18. 48.283 19,870.821 ↑ 153,391.1 4,346 1

Nested Loop (cost=172,673.64..73,702,726.13 rows=666,637,697 width=100) (actual time=17,689.002..19,870.821 rows=4,346 loops=1)

  • Buffers: shared hit=76191 read=100384, temp read=542 written=536
19. 21.076 19,570.470 ↑ 9,142.6 4,346 1

Append (cost=172,673.22..3,197,329.76 rows=39,733,667 width=72) (actual time=17,688.837..19,570.470 rows=4,346 loops=1)

  • Buffers: shared hit=53586 read=100384, temp read=542 written=536
20. 36.902 19,549.055 ↑ 9,142.6 4,346 1

Subquery Scan on *SELECT* 1 (cost=172,673.22..3,197,259.67 rows=39,733,650 width=72) (actual time=17,688.830..19,549.055 rows=4,346 loops=1)

  • Buffers: shared hit=53586 read=100383, temp read=542 written=536
21. 1,663.957 19,512.153 ↑ 9,142.6 4,346 1

Nested Loop (cost=172,673.22..2,799,923.17 rows=39,733,650 width=236) (actual time=17,688.823..19,512.153 rows=4,346 loops=1)

  • Buffers: shared hit=53586 read=100383, temp read=542 written=536
22. 517.854 17,830.812 ↑ 13.3 4,346 1

Hash Right Join (cost=172,673.22..316,551.42 rows=57,585 width=204) (actual time=17,501.274..17,830.812 rows=4,346 loops=1)

  • Hash Cond: (tspolicy.timesheetid = ts_1.id)
  • Buffers: shared hit=53585 read=100383, temp read=542 written=536
23. 13,855.196 13,855.196 ↓ 8.0 134,993 1

Seq Scan on timesheetpolicysettingssnapshot tspolicy (cost=0.00..141,833.13 rows=16,851 width=16) (actual time=0.099..13,855.196 rows=134,993 loops=1)

  • Filter: (upper(uri) = 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT:GEN4-TIMESHEET'::text)
  • Rows Removed by Filter: 3235149
  • Buffers: shared hit=3 read=91278
24. 20.501 3,457.762 ↑ 13.3 4,346 1

Hash (cost=170,209.40..170,209.40 rows=57,585 width=220) (actual time=3,457.762..3,457.762 rows=4,346 loops=1)

  • Buckets: 16384 Batches: 4 Memory Usage: 356kB
  • Buffers: shared hit=53582 read=9105, temp written=84
25. 48.451 3,437.261 ↑ 13.3 4,346 1

Nested Loop Left Join (cost=30,737.80..170,209.40 rows=57,585 width=220) (actual time=157.594..3,437.261 rows=4,346 loops=1)

  • Buffers: shared hit=53582 read=9105
26. 37.426 3,006.362 ↓ 1.9 4,346 1

Nested Loop Left Join (cost=30,737.38..159,456.27 rows=2,261 width=204) (actual time=155.132..3,006.362 rows=4,346 loops=1)

  • Buffers: shared hit=31365 read=8717
27. 54.126 2,803.788 ↓ 1.9 4,346 1

Nested Loop Left Join (cost=30,736.95..97,972.46 rows=2,261 width=146) (actual time=155.085..2,803.788 rows=4,346 loops=1)

  • Buffers: shared hit=13432 read=8646
28. 22.551 189.868 ↓ 1.9 4,346 1

Nested Loop (cost=30,736.51..36,522.56 rows=2,261 width=88) (actual time=153.300..189.868 rows=4,346 loops=1)

  • Buffers: shared hit=72 read=4002
29. 0.007 0.007 ↑ 1.0 1 1

Seq Scan on systeminformation si (cost=0.00..1.01 rows=1 width=32) (actual time=0.005..0.007 rows=1 loops=1)

  • Buffers: shared hit=1
30. 14.066 167.310 ↓ 1.9 4,346 1

Bitmap Heap Scan on timeentry te (cost=30,736.51..36,498.94 rows=2,261 width=56) (actual time=153.281..167.310 rows=4,346 loops=1)

  • Recheck Cond: ((entrydate >= '2019-03-30'::date) AND (entrydate <= '2019-04-05'::date))
  • Filter: ((timeallocationtype <> 2) OR (timeallocationtype IS NULL))
  • Heap Blocks: exact=67
  • Buffers: shared hit=71 read=4002
31. 153.244 153.244 ↓ 1.9 4,346 1

Bitmap Index Scan on ixte2userid (cost=0.00..30,735.95 rows=2,261 width=0) (actual time=153.244..153.244 rows=4,346 loops=1)

  • Index Cond: ((entrydate >= '2019-03-30'::date) AND (entrydate <= '2019-04-05'::date))
  • Buffers: shared hit=4 read=4002
32. 2,559.794 2,559.794 ↑ 1.0 1 4,346

Index Scan using ixtemtimeentryid on timeentrymetadata tembillingrate (cost=0.43..27.17 rows=1 width=74) (actual time=0.584..0.589 rows=1 loops=4,346)

  • Index Cond: (timeentryid = te.id)
  • Filter: (key = 'urn:replicon:time-entry-metadata-key:billing-rate'::text)
  • Rows Removed by Filter: 4
  • Buffers: shared hit=13360 read=4644
33. 165.148 165.148 ↓ 0.0 0 4,346

Index Scan using ixtemtimeentryid on timeentrymetadata temdbreaktype (cost=0.43..27.18 rows=1 width=74) (actual time=0.038..0.038 rows=0 loops=4,346)

  • Index Cond: (timeentryid = te.id)
  • Filter: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:BREAK-TYPE'::text)
  • Rows Removed by Filter: 5
  • Buffers: shared hit=17933 read=71
34. 382.448 382.448 ↑ 17.0 1 4,346

Index Scan using ixtsuseridstartdateenddate on timesheet ts_1 (cost=0.42..4.59 rows=17 width=28) (actual time=0.085..0.088 rows=1 loops=4,346)

  • Index Cond: ((userid = te.userid) AND (te.entrydate >= startdate) AND (te.entrydate <= enddate))
  • Buffers: shared hit=22217 read=388
35. 17.374 17.384 ↑ 690.0 1 4,346

Materialize (cost=0.00..20.35 rows=690 width=32) (actual time=0.002..0.004 rows=1 loops=4,346)

  • Buffers: shared hit=1
36. 0.010 0.010 ↑ 690.0 1 1

Seq Scan on projectsysteminformation psi (cost=0.00..16.90 rows=690 width=32) (actual time=0.005..0.010 rows=1 loops=1)

  • Buffers: shared hit=1
37. 0.004 0.339 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=5.05..70.09 rows=17 width=72) (actual time=0.339..0.339 rows=0 loops=1)

  • Buffers: shared read=1
38. 0.004 0.335 ↓ 0.0 0 1

Nested Loop Left Join (cost=5.05..69.92 rows=17 width=24) (actual time=0.335..0.335 rows=0 loops=1)

  • Buffers: shared read=1
39. 0.003 0.331 ↓ 0.0 0 1

Nested Loop (cost=0.00..2.04 rows=1 width=24) (actual time=0.331..0.331 rows=0 loops=1)

  • Join Filter: (toe.timeoffid = toff.id)
  • Buffers: shared read=1
40. 0.328 0.328 ↓ 0.0 0 1

Seq Scan on timeoffentries toe (cost=0.00..1.01 rows=1 width=24) (actual time=0.328..0.328 rows=0 loops=1)

  • Filter: ((entrydate >= '2019-03-30'::date) AND (entrydate <= '2019-04-05'::date))
  • Rows Removed by Filter: 1
  • Buffers: shared read=1
41. 0.000 0.000 ↓ 0.0 0

Seq Scan on timeoffs toff (cost=0.00..1.01 rows=1 width=8) (never executed)

42. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on timesheet ts_2 (cost=5.05..67.71 rows=17 width=12) (never executed)

  • Recheck Cond: ((userid = toff.userid) AND (toe.entrydate >= startdate) AND (toe.entrydate <= enddate))
43. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ixtsuseridstartdateenddate (cost=0.00..5.04 rows=17 width=0) (never executed)

  • Index Cond: ((userid = toff.userid) AND (toe.entrydate >= startdate) AND (toe.entrydate <= enddate))
44. 252.068 252.068 ↑ 17.0 1 4,346

Index Scan using ixtsuseridstartdateenddate on timesheet ts (cost=0.42..1.60 rows=17 width=28) (actual time=0.055..0.058 rows=1 loops=4,346)

  • Index Cond: ((userid = "*SELECT* 1".userid) AND ("*SELECT* 1".entrydate >= startdate) AND ("*SELECT* 1".entrydate <= enddate))
  • Buffers: shared hit=22605
45. 390.625 1,094.910 ↓ 1.0 134,993 1

Hash (cost=10,526.64..10,526.64 rows=134,652 width=20) (actual time=1,094.910..1,094.910 rows=134,993 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 2221kB
  • Buffers: shared hit=2 read=8331, temp written=493
46. 704.285 704.285 ↓ 1.0 134,993 1

Seq Scan on dm_timesheetlist_facts tslist (cost=0.00..10,526.64 rows=134,652 width=20) (actual time=0.856..704.285 rows=134,993 loops=1)

  • Filter: (timesheetstatus = ANY ('{0,1,2,3,4}'::integer[]))
  • Buffers: shared hit=2 read=8331
47. 5,768.336 239,748.209 ↓ 3.3 1,455,933 1

Materialize (cost=289,079.01..291,291.34 rows=442,467 width=20) (actual time=229,349.113..239,748.209 rows=1,455,933 loops=1)

  • Buffers: shared hit=706 read=127241, temp read=93396 written=93396
48. 12,419.098 233,979.873 ↓ 3.3 1,455,933 1

Sort (cost=289,079.01..290,185.18 rows=442,467 width=20) (actual time=229,349.105..233,979.873 rows=1,455,933 loops=1)

  • Sort Key: timeentrymetadata12.timeentryid
  • Sort Method: external merge Disk: 42680kB
  • Buffers: shared hit=706 read=127241, temp read=93396 written=93396
49. 6,222.389 221,560.775 ↓ 3.3 1,456,325 1

Merge Right Join (cost=226,294.35..238,510.70 rows=442,467 width=20) (actual time=211,819.673..221,560.775 rows=1,456,325 loops=1)

  • Merge Cond: ((upper(('urn:replicon-tenant:d39c603ba5ee46da9a42069eec872cf2:project:'::text || COALESCE((project13.id)::text, ''::text)))) = (upper(timeentrymetadata12.uri)))
  • Buffers: shared hit=706 read=127241, temp read=85741 written=85741
50. 192.660 209.146 ↑ 1.0 2,772 1

Sort (cost=288.23..295.16 rows=2,772 width=4) (actual time=203.087..209.146 rows=2,772 loops=1)

  • Sort Key: (upper(('urn:replicon-tenant:d39c603ba5ee46da9a42069eec872cf2:project:'::text || COALESCE((project13.id)::text, ''::text))))
  • Sort Method: quicksort Memory: 486kB
  • Buffers: shared hit=102
51. 16.486 16.486 ↑ 1.0 2,772 1

Seq Scan on project project13 (cost=0.00..129.72 rows=2,772 width=4) (actual time=0.668..16.486 rows=2,772 loops=1)

  • Buffers: shared hit=102
52. 186,518.577 215,129.240 ↓ 45.6 1,456,325 1

Sort (cost=226,006.12..226,085.93 rows=31,924 width=74) (actual time=211,616.552..215,129.240 rows=1,456,325 loops=1)

  • Sort Key: (upper(timeentrymetadata12.uri))
  • Sort Method: external sort Disk: 228944kB
  • Buffers: shared hit=604 read=127241, temp read=85741 written=85741
53. 28,610.663 28,610.663 ↓ 45.6 1,456,325 1

Seq Scan on timeentrymetadata timeentrymetadata12 (cost=0.00..223,617.83 rows=31,924 width=74) (actual time=0.177..28,610.663 rows=1,456,325 loops=1)

  • Filter: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:PROJECT'::text)
  • Rows Removed by Filter: 4928530
  • Buffers: shared hit=604 read=127241
54. 5,590.749 290,174.379 ↑ 9.7 1,425,836 1

Materialize (cost=2,825,450.40..2,894,636.89 rows=13,837,298 width=24) (actual time=280,022.770..290,174.379 rows=1,425,836 loops=1)

  • Buffers: shared hit=2227 read=127209, temp read=89710 written=64072
55. 12,854.856 284,583.630 ↑ 9.7 1,425,836 1

Sort (cost=2,825,450.40..2,860,043.65 rows=13,837,298 width=24) (actual time=280,022.761..284,583.630 rows=1,425,836 loops=1)

  • Sort Key: timeentrymetadata14.timeentryid
  • Sort Method: external merge Disk: 47360kB
  • Buffers: shared hit=2227 read=127209, temp read=89710 written=64072
56. 17,804.281 271,728.774 ↑ 9.7 1,426,221 1

Merge Left Join (cost=235,574.05..616,658.41 rows=13,837,298 width=24) (actual time=207,630.507..271,728.774 rows=1,426,221 loops=1)

  • Merge Cond: ((upper(timeentrymetadata14.uri)) = (upper(('urn:replicon-tenant:d39c603ba5ee46da9a42069eec872cf2:task:'::text || COALESCE((task15.id)::text, ''::text)))))
  • Buffers: shared hit=2227 read=127209, temp read=81455 written=55817
57. 209,930.704 237,710.606 ↓ 44.7 1,426,221 1

Sort (cost=226,006.12..226,085.93 rows=31,924 width=74) (actual time=194,838.287..237,710.606 rows=1,426,221 loops=1)

  • Sort Key: (upper(timeentrymetadata14.uri))
  • Sort Method: external merge Disk: 213872kB
  • Buffers: shared hit=636 read=127209, temp read=53998 written=53998
58. 27,779.902 27,779.902 ↓ 44.7 1,426,221 1

Seq Scan on timeentrymetadata timeentrymetadata14 (cost=0.00..223,617.83 rows=31,924 width=74) (actual time=0.043..27,779.902 rows=1,426,221 loops=1)

  • Filter: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:TASK'::text)
  • Rows Removed by Filter: 4958634
  • Buffers: shared hit=636 read=127209
59. 15,595.754 16,213.887 ↓ 17.1 1,483,474 1

Sort (cost=9,567.93..9,784.65 rows=86,689 width=8) (actual time=12,792.171..16,213.887 rows=1,483,474 loops=1)

  • Sort Key: (upper(('urn:replicon-tenant:d39c603ba5ee46da9a42069eec872cf2:task:'::text || COALESCE((task15.id)::text, ''::text))))
  • Sort Method: external sort Disk: 7272kB
  • Buffers: shared hit=1591, temp read=14638 written=1819
60. 618.133 618.133 ↑ 1.0 86,689 1

Seq Scan on task task15 (cost=0.00..2,457.89 rows=86,689 width=8) (actual time=0.038..618.133 rows=86,689 loops=1)

  • Buffers: shared hit=1591
61. 52.152 52.152 ↑ 1.0 1 4,346

Index Scan using project_pkey on project pj (cost=0.28..0.46 rows=1 width=84) (actual time=0.009..0.012 rows=1 loops=4,346)

  • Index Cond: (id = COALESCE(project13.id, task15.projectid))
  • Buffers: shared hit=13034 read=5
62. 0.016 19,320.842 ↓ 0.0 0 1

Sort (cost=226,433.19..226,513.00 rows=31,924 width=20) (actual time=19,320.842..19,320.842 rows=0 loops=1)

  • Sort Key: timeentrymetadata16.timeentryid
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=668 read=127177
63. 0.005 19,320.826 ↓ 0.0 0 1

Hash Left Join (cost=2.49..224,044.90 rows=31,924 width=20) (actual time=19,320.826..19,320.826 rows=0 loops=1)

  • Hash Cond: (upper(timeentrymetadata16.uri) = upper(('urn:replicon-tenant:d39c603ba5ee46da9a42069eec872cf2:client:'::text || COALESCE((clients17.id)::text, ''::text))))
  • Buffers: shared hit=668 read=127177
64. 19,320.821 19,320.821 ↓ 0.0 0 1

Seq Scan on timeentrymetadata timeentrymetadata16 (cost=0.00..223,617.83 rows=31,924 width=74) (actual time=19,320.821..19,320.821 rows=0 loops=1)

  • Filter: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:CLIENT'::text)
  • Rows Removed by Filter: 6384855
  • Buffers: shared hit=668 read=127177
65. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.66..1.66 rows=66 width=4) (never executed)

66. 0.000 0.000 ↓ 0.0 0

Seq Scan on clients clients17 (cost=0.00..1.66 rows=66 width=4) (never executed)

67. 232.847 865.222 ↑ 1.0 86,689 1

Hash (cost=2,803.01..2,803.01 rows=86,689 width=45) (actual time=865.222..865.222 rows=86,689 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 4087kB
  • Buffers: shared hit=1592 read=1, temp written=383
68. 445.836 632.375 ↑ 1.0 86,689 1

Hash Left Join (cost=2.36..2,803.01 rows=86,689 width=45) (actual time=1.085..632.375 rows=86,689 loops=1)

  • Hash Cond: (tk.estimatedcostcurrencyid = exchangerate.fixedcurrencyid)
  • Buffers: shared hit=1592 read=1
69. 185.479 185.479 ↑ 1.0 86,689 1

Seq Scan on task tk (cost=0.00..2,457.89 rows=86,689 width=49) (actual time=0.008..185.479 rows=86,689 loops=1)

  • Buffers: shared hit=1591
70. 0.025 1.060 ↓ 7.0 7 1

Hash (cost=2.35..2.35 rows=1 width=4) (actual time=1.060..1.060 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1 read=1
71. 1.035 1.035 ↓ 7.0 7 1

Seq Scan on exchangerate (cost=0.00..2.35 rows=1 width=4) (actual time=0.993..1.035 rows=7 loops=1)

  • Filter: ((variablecurrencyid = $0) AND (('now'::cstring)::date >= effectivedate) AND (('now'::cstring)::date <= enddate))
  • Rows Removed by Filter: 42
  • Buffers: shared hit=1 read=1
72. 0.163 0.354 ↑ 1.0 66 1

Hash (cost=1.66..1.66 rows=66 width=16) (actual time=0.354..0.354 rows=66 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
  • Buffers: shared hit=1
73. 0.191 0.191 ↑ 1.0 66 1

Seq Scan on clients cl (cost=0.00..1.66 rows=66 width=16) (actual time=0.011..0.191 rows=66 loops=1)

  • Buffers: shared hit=1
74. 2.559 5.390 ↑ 1.0 994 1

Hash (cost=27.94..27.94 rows=994 width=39) (actual time=5.390..5.390 rows=994 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 80kB
  • Buffers: shared hit=18
75. 2.831 2.831 ↑ 1.0 994 1

Seq Scan on userinfo ui (cost=0.00..27.94 rows=994 width=39) (actual time=0.015..2.831 rows=994 loops=1)

  • Buffers: shared hit=18
76. 233.342 479.382 ↑ 1.0 86,689 1

Hash (cost=3,391.89..3,391.89 rows=86,689 width=63) (actual time=479.382..479.382 rows=86,689 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 2584kB
  • Buffers: shared hit=2 read=2523, temp written=678
77. 246.040 246.040 ↑ 1.0 86,689 1

Seq Scan on taskdenormalizedhierarchy tdh (cost=0.00..3,391.89 rows=86,689 width=63) (actual time=0.325..246.040 rows=86,689 loops=1)

  • Buffers: shared hit=2 read=2523
78. 2.628 19.389 ↑ 1.0 994 1

Hash (cost=85.76..85.76 rows=994 width=532) (actual time=19.389..19.389 rows=994 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 69kB
  • Buffers: shared hit=4 read=21
79. 4.437 16.761 ↑ 1.0 994 1

Hash Join (cost=30.49..85.76 rows=994 width=532) (actual time=5.592..16.761 rows=994 loops=1)

  • Hash Cond: (du.departmentid = dep.id)
  • Buffers: shared hit=4 read=21
80. 4.327 12.044 ↑ 1.0 994 1

Hash Join (cost=28.37..69.97 rows=994 width=20) (actual time=5.283..12.044 rows=994 loops=1)

  • Hash Cond: (login.userid = du.userid)
  • Buffers: shared hit=3 read=21
81. 2.466 2.466 ↑ 1.0 994 1

Seq Scan on login (cost=0.00..27.94 rows=994 width=12) (actual time=0.008..2.466 rows=994 loops=1)

  • Buffers: shared hit=1 read=17
82. 2.601 5.251 ↑ 1.0 994 1

Hash (cost=15.94..15.94 rows=994 width=8) (actual time=5.251..5.251 rows=994 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 47kB
  • Buffers: shared hit=2 read=4
83. 2.650 2.650 ↑ 1.0 994 1

Seq Scan on departmentusers du (cost=0.00..15.94 rows=994 width=8) (actual time=0.018..2.650 rows=994 loops=1)

  • Buffers: shared hit=2 read=4
84. 0.134 0.280 ↑ 1.0 50 1

Hash (cost=1.50..1.50 rows=50 width=520) (actual time=0.280..0.280 rows=50 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=1
85. 0.146 0.146 ↑ 1.0 50 1

Seq Scan on departments dep (cost=0.00..1.50 rows=50 width=520) (actual time=0.009..0.146 rows=50 loops=1)

  • Buffers: shared hit=1
86. 1,979.817 23,161.772 ↓ 22.8 727,165 1

Hash (cost=223,617.83..223,617.83 rows=31,924 width=62) (actual time=23,161.772..23,161.772 rows=727,165 loops=1)

  • Buckets: 65536 (originally 32768) Batches: 32 (originally 1) Memory Usage: 3585kB
  • Buffers: shared hit=700 read=127145, temp written=6987
87. 21,181.955 21,181.955 ↓ 22.8 727,165 1

Seq Scan on timeentrymetadata timeentrymetadata9 (cost=0.00..223,617.83 rows=31,924 width=62) (actual time=0.034..21,181.955 rows=727,165 loops=1)

  • Filter: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:COMMENTS'::text)
  • Rows Removed by Filter: 5657690
  • Buffers: shared hit=700 read=127145
88. 2.334 5.253 ↑ 1.0 994 1

Hash (cost=27.94..27.94 rows=994 width=18) (actual time=5.253..5.253 rows=994 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 59kB
  • Buffers: shared hit=18
89. 2.919 2.919 ↑ 1.0 994 1

Seq Scan on userinfo userinfo10 (cost=0.00..27.94 rows=994 width=18) (actual time=0.012..2.919 rows=994 loops=1)

  • Buffers: shared hit=18
90. 0.940 1.808 ↑ 1.0 377 1

Hash (cost=7.77..7.77 rows=377 width=41) (actual time=1.808..1.808 rows=377 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 36kB
  • Buffers: shared hit=4
91. 0.868 0.868 ↑ 1.0 377 1

Seq Scan on billingrate billingrate11 (cost=0.00..7.77 rows=377 width=41) (actual time=0.012..0.868 rows=377 loops=1)

  • Buffers: shared hit=4
Planning time : 117.150 ms
Execution time : 602,665.532 ms