explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Lh87 : after vacuum analyze

Settings
# exclusive inclusive rows x rows loops node
1. 143.953 618,620.861 ↑ 1,626,957.8 4,346 1

Sort (cost=17,228,345,580.17..17,246,022,476.63 rows=7,070,758,584 width=408) (actual time=618,612.247..618,620.861 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=111235 read=599631, temp read=200253 written=174588
2.          

Initplan (forSort)

3. 0.007 0.007 ↑ 1.0 1 1

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

  • Buffers: shared hit=1
4. 24.539 618,476.901 ↑ 1,626,957.8 4,346 1

Hash Left Join (cost=6,030,617,637.75..9,546,336,514.06 rows=7,070,758,584 width=408) (actual time=614,925.617..618,476.901 rows=4,346 loops=1)

  • Hash Cond: ("*SELECT* 1".billingrateid = billingrate11.id)
  • Buffers: shared hit=111235 read=599631, temp read=200253 written=174588
5. 19.751 618,450.575 ↑ 1,626,957.8 4,346 1

Hash Left Join (cost=6,030,617,625.26..9,449,113,571.05 rows=7,070,758,584 width=399) (actual time=614,923.796..618,450.575 rows=4,346 loops=1)

  • Hash Cond: (pj.projectleaderapproverid = userinfo10.id)
  • Buffers: shared hit=111231 read=599631, temp read=200253 written=174588
6. 20.469 618,425.416 ↑ 1,626,957.8 4,346 1

Hash Join (cost=6,030,617,584.90..9,352,069,154.67 rows=7,070,758,584 width=389) (actual time=614,918.336..618,425.416 rows=4,346 loops=1)

  • Hash Cond: ("*SELECT* 1".userid = login.userid)
  • Buffers: shared hit=111213 read=599631, temp read=200253 written=174588
7. 55.581 618,370.143 ↑ 1,626,957.8 4,346 1

Hash Left Join (cost=6,030,617,486.71..9,254,846,125.95 rows=7,070,758,584 width=371) (actual time=614,883.513..618,370.143 rows=4,346 loops=1)

  • Hash Cond: (tk.id = tdh.taskid)
  • Buffers: shared hit=111188 read=599631, temp read=200253 written=174588
8. 1,594.246 617,885.727 ↑ 1,626,957.8 4,346 1

Merge Left Join (cost=6,030,612,079.21..8,577,593,689.91 rows=7,070,758,584 width=312) (actual time=614,454.411..617,885.727 rows=4,346 loops=1)

  • Merge Cond: ("*SELECT* 1".timeentryid = timeentrymetadata9.timeentryid)
  • Buffers: shared hit=108663 read=599631, temp read=199435 written=173776
9. 37.268 588,740.661 ↑ 1,626,957.8 4,346 1

Nested Loop Left Join (cost=6,030,385,962.54..8,556,525,930.60 rows=7,070,758,584 width=268) (actual time=588,638.626..588,740.661 rows=4,346 loops=1)

  • Buffers: shared hit=107866 read=472583, temp read=184747 written=159087
10. 20.872 588,668.625 ↑ 1,626,957.8 4,346 1

Merge Left Join (cost=6,030,385,962.40..6,068,904,501.51 rows=7,070,758,584 width=260) (actual time=588,638.528..588,668.625 rows=4,346 loops=1)

  • Merge Cond: ("*SELECT* 1".timeentryid = timeentrymetadata16.timeentryid)
  • Buffers: shared hit=99174 read=472583, temp read=184747 written=159087
11. 35.979 569,347.251 ↑ 1,626,957.8 4,346 1

Sort (cost=6,030,159,418.18..6,047,836,314.64 rows=7,070,758,584 width=256) (actual time=569,337.987..569,347.251 rows=4,346 loops=1)

  • Sort Key: "*SELECT* 1".timeentryid
  • Sort Method: quicksort Memory: 2326kB
  • Buffers: shared hit=98409 read=345503, temp read=184747 written=159087
12. 23.334 569,311.272 ↑ 1,626,957.8 4,346 1

Hash Join (cost=3,899,491.17..644,075,383.08 rows=7,070,758,584 width=256) (actual time=541,933.226..569,311.272 rows=4,346 loops=1)

  • Hash Cond: ("*SELECT* 1".userid = ui.id)
  • Buffers: shared hit=98409 read=345503, temp read=184747 written=159087
13. 44.999 569,282.488 ↑ 1,626,957.8 4,346 1

Hash Left Join (cost=3,899,450.81..546,852,412.19 rows=7,070,758,584 width=217) (actual time=541,927.756..569,282.488 rows=4,346 loops=1)

  • Hash Cond: (task15.id = tk.id)
  • Buffers: shared hit=98391 read=345503, temp read=184747 written=159087
14. 26.650 568,389.619 ↑ 1,626,957.8 4,346 1

Hash Left Join (cost=3,893,303.60..104,370,688.45 rows=7,070,758,584 width=176) (actual time=541,079.813..568,389.619 rows=4,346 loops=1)

  • Hash Cond: (COALESCE(project13.id, task15.projectid) = pj.id)
  • Buffers: shared hit=96798 read=345503, temp read=184313 written=158655
15. 3,285.534 568,348.284 ↑ 1,626,957.8 4,346 1

Merge Left Join (cost=3,893,139.23..7,147,593.55 rows=7,070,758,584 width=100) (actual time=541,065.097..568,348.284 rows=4,346 loops=1)

  • Merge Cond: ("*SELECT* 1".timeentryid = timeentrymetadata14.timeentryid)
  • Buffers: shared hit=96696 read=345503, temp read=184313 written=158655
16. 3,380.682 273,476.244 ↑ 3,753.6 4,346 1

Merge Left Join (cost=1,064,541.12..1,078,834.81 rows=16,312,931 width=92) (actual time=259,627.207..273,476.244 rows=4,346 loops=1)

  • Merge Cond: ("*SELECT* 1".timeentryid = timeentrymetadata12.timeentryid)
  • Buffers: shared hit=94372 read=218391, temp read=94603 written=94583
17. 25.706 18,718.319 ↑ 270.8 4,346 1

Sort (cost=775,324.80..778,267.25 rows=1,176,978 width=88) (actual time=18,708.468..18,718.319 rows=4,346 loops=1)

  • Sort Key: "*SELECT* 1".timeentryid
  • Sort Method: quicksort Memory: 802kB
  • Buffers: shared hit=93662 read=91246, temp read=1207 written=1187
18. 57.407 18,692.613 ↑ 270.8 4,346 1

Hash Join (cost=205,857.79..544,002.25 rows=1,176,978 width=88) (actual time=16,682.644..18,692.613 rows=4,346 loops=1)

  • Hash Cond: (ts.id = tslist.timesheetid)
  • Buffers: shared hit=93662 read=91246, temp read=1207 written=1187
19. 47.643 17,612.903 ↑ 271.5 4,346 1

Nested Loop (cost=192,859.05..477,143.75 rows=1,179,994 width=100) (actual time=15,644.151..17,612.903 rows=4,346 loops=1)

  • Buffers: shared hit=85329 read=91246, temp read=655 written=641
20. 17.355 17,343.614 ↑ 16.3 4,346 1

Append (cost=192,858.63..343,896.54 rows=70,882 width=72) (actual time=15,644.045..17,343.614 rows=4,346 loops=1)

  • Buffers: shared hit=62724 read=91246, temp read=655 written=641
21. 39.652 17,326.234 ↑ 16.3 4,346 1

Subquery Scan on *SELECT* 1 (cost=192,858.63..343,888.95 rows=70,865 width=72) (actual time=15,644.041..17,326.234 rows=4,346 loops=1)

  • Buffers: shared hit=62723 read=91246, temp read=655 written=641
22. 1,811.434 17,286.582 ↑ 16.3 4,346 1

Hash Right Join (cost=192,858.63..343,180.30 rows=70,865 width=237) (actual time=15,644.036..17,286.582 rows=4,346 loops=1)

  • Hash Cond: (tspolicy.timesheetid = ts_1.id)
  • Buffers: shared hit=62723 read=91246, temp read=655 written=641
23. 14,419.437 14,419.437 ↑ 1.1 134,993 1

Seq Scan on timesheetpolicysettingssnapshot tspolicy (cost=0.00..141,856.29 rows=145,994 width=16) (actual time=1.660..14,419.437 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=35 read=91246
24. 16.004 1,055.711 ↑ 15.1 4,346 1

Hash (cost=189,799.57..189,799.57 rows=65,525 width=253) (actual time=1,055.711..1,055.711 rows=4,346 loops=1)

  • Buckets: 16384 Batches: 8 Memory Usage: 252kB
  • Buffers: shared hit=62688, temp written=111
25. 36.065 1,039.707 ↑ 15.1 4,346 1

Nested Loop Left Join (cost=30,737.89..189,799.57 rows=65,525 width=253) (actual time=423.993..1,039.707 rows=4,346 loops=1)

  • Buffers: shared hit=62688
26. 26.826 764.612 ↓ 1.7 4,346 1

Nested Loop Left Join (cost=30,737.47..178,522.37 rows=2,620 width=237) (actual time=423.744..764.612 rows=4,346 loops=1)

  • Buffers: shared hit=40083
27. 38.434 633.482 ↓ 1.7 4,346 1

Nested Loop Left Join (cost=30,737.03..107,817.49 rows=2,620 width=179) (actual time=423.702..633.482 rows=4,346 loops=1)

  • Buffers: shared hit=22079
28. 17.265 451.630 ↓ 1.7 4,346 1

Nested Loop (cost=30,736.60..37,151.91 rows=2,620 width=121) (actual time=423.562..451.630 rows=4,346 loops=1)

  • Buffers: shared hit=4075
29. 0.019 0.031 ↑ 1.0 1 1

Nested Loop (cost=0.00..2.03 rows=1 width=65) (actual time=0.020..0.031 rows=1 loops=1)

  • Buffers: shared hit=2
30. 0.006 0.006 ↑ 1.0 1 1

Seq Scan on systeminformation si (cost=0.00..1.01 rows=1 width=33) (actual time=0.004..0.006 rows=1 loops=1)

  • Buffers: shared hit=1
31. 0.006 0.006 ↑ 1.0 1 1

Seq Scan on projectsysteminformation psi (cost=0.00..1.01 rows=1 width=32) (actual time=0.003..0.006 rows=1 loops=1)

  • Buffers: shared hit=1
32. 10.842 434.334 ↓ 1.7 4,346 1

Bitmap Heap Scan on timeentry te (cost=30,736.60..37,123.68 rows=2,620 width=56) (actual time=423.532..434.334 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=4073
33. 423.492 423.492 ↓ 1.7 4,346 1

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

  • Index Cond: ((entrydate >= '2019-03-30'::date) AND (entrydate <= '2019-04-05'::date))
  • Buffers: shared hit=4006
34. 143.418 143.418 ↑ 1.0 1 4,346

Index Scan using ixtemtimeentryid on timeentrymetadata tembillingrate (cost=0.43..26.96 rows=1 width=74) (actual time=0.030..0.033 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=18004
35. 104.304 104.304 ↓ 0.0 0 4,346

Index Scan using ixtemtimeentryid on timeentrymetadata temdbreaktype (cost=0.43..26.98 rows=1 width=74) (actual time=0.024..0.024 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=18004
36. 239.030 239.030 ↑ 17.0 1 4,346

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

  • Index Cond: ((userid = te.userid) AND (te.entrydate >= startdate) AND (te.entrydate <= enddate))
  • Buffers: shared hit=22605
37. 0.004 0.025 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=0.42..7.59 rows=17 width=72) (actual time=0.025..0.025 rows=0 loops=1)

  • Buffers: shared hit=1
38. 0.004 0.021 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.42..7.42 rows=17 width=24) (actual time=0.021..0.021 rows=0 loops=1)

  • Buffers: shared hit=1
39. 0.003 0.017 ↓ 0.0 0 1

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

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

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

  • Filter: ((entrydate >= '2019-03-30'::date) AND (entrydate <= '2019-04-05'::date))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=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

Index Only Scan using ixtsuseridstartdateenddate on timesheet ts_2 (cost=0.42..5.21 rows=17 width=12) (never executed)

  • Index Cond: ((userid = toff.userid) AND (startdate <= toe.entrydate) AND (enddate >= toe.entrydate))
  • Heap Fetches: 0
43. 221.646 221.646 ↑ 17.0 1 4,346

Index Scan using ixtsuseridstartdateenddate on timesheet ts (cost=0.42..1.71 rows=17 width=28) (actual time=0.049..0.051 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
44. 374.793 1,022.303 ↓ 1.0 134,993 1

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

  • Buckets: 65536 Batches: 4 Memory Usage: 2221kB
  • Buffers: shared hit=8333, temp written=493
45. 647.510 647.510 ↓ 1.0 134,993 1

Seq Scan on dm_timesheetlist_facts tslist (cost=0.00..10,526.64 rows=134,648 width=20) (actual time=0.241..647.510 rows=134,993 loops=1)

  • Filter: (timesheetstatus = ANY ('{0,1,2,3,4}'::integer[]))
  • Buffers: shared hit=8333
46. 5,767.968 251,377.243 ↓ 3.3 1,455,933 1

Materialize (cost=289,216.32..291,431.15 rows=442,966 width=20) (actual time=240,917.253..251,377.243 rows=1,455,933 loops=1)

  • Buffers: shared hit=710 read=127145, temp read=93396 written=93396
47. 13,334.642 245,609.275 ↓ 3.3 1,455,933 1

Sort (cost=289,216.32..290,323.73 rows=442,966 width=20) (actual time=240,917.244..245,609.275 rows=1,455,933 loops=1)

  • Sort Key: timeentrymetadata12.timeentryid
  • Sort Method: external merge Disk: 42680kB
  • Buffers: shared hit=710 read=127145, temp read=93396 written=93396
48. 6,734.421 232,274.633 ↓ 3.3 1,456,325 1

Merge Right Join (cost=226,357.04..238,587.11 rows=442,966 width=20) (actual time=221,761.484..232,274.633 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=710 read=127145, temp read=85741 written=85741
49. 230.155 248.428 ↑ 1.0 2,772 1

Sort (cost=240.37..247.30 rows=2,772 width=4) (actual time=241.267..248.428 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=9 read=1
50. 18.273 18.273 ↑ 1.0 2,772 1

Index Only Scan using project_pkey on project project13 (cost=0.28..81.86 rows=2,772 width=4) (actual time=0.047..18.273 rows=2,772 loops=1)

  • Heap Fetches: 0
  • Buffers: shared hit=9 read=1
51. 195,625.550 225,291.784 ↓ 45.6 1,456,325 1

Sort (cost=226,116.66..226,196.56 rows=31,960 width=74) (actual time=221,520.176..225,291.784 rows=1,456,325 loops=1)

  • Sort Key: (upper(timeentrymetadata12.uri))
  • Sort Method: external sort Disk: 228944kB
  • Buffers: shared hit=701 read=127144, temp read=85741 written=85741
52. 29,666.234 29,666.234 ↓ 45.6 1,456,325 1

Seq Scan on timeentrymetadata timeentrymetadata12 (cost=0.00..223,725.42 rows=31,960 width=74) (actual time=0.223..29,666.234 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=701 read=127144
53. 5,627.192 291,586.506 ↑ 9.7 1,425,836 1

Materialize (cost=2,828,598.11..2,897,862.62 rows=13,852,902 width=24) (actual time=281,436.857..291,586.506 rows=1,425,836 loops=1)

  • Buffers: shared hit=2324 read=127112, temp read=89710 written=64072
54. 12,869.794 285,959.314 ↑ 9.7 1,425,836 1

Sort (cost=2,828,598.11..2,863,230.37 rows=13,852,902 width=24) (actual time=281,436.850..285,959.314 rows=1,425,836 loops=1)

  • Sort Key: timeentrymetadata14.timeentryid
  • Sort Method: external merge Disk: 47360kB
  • Buffers: shared hit=2324 read=127112, temp read=89710 written=64072
55. 18,054.003 273,089.520 ↑ 9.7 1,426,221 1

Merge Left Join (cost=235,684.60..617,198.70 rows=13,852,902 width=24) (actual time=208,785.775..273,089.520 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=2324 read=127112, temp read=81455 written=55817
56. 211,179.280 238,969.848 ↓ 44.6 1,426,221 1

Sort (cost=226,116.66..226,196.56 rows=31,960 width=74) (actual time=196,184.666..238,969.848 rows=1,426,221 loops=1)

  • Sort Key: (upper(timeentrymetadata14.uri))
  • Sort Method: external merge Disk: 213872kB
  • Buffers: shared hit=733 read=127112, temp read=53998 written=53998
57. 27,790.568 27,790.568 ↓ 44.6 1,426,221 1

Seq Scan on timeentrymetadata timeentrymetadata14 (cost=0.00..223,725.42 rows=31,960 width=74) (actual time=0.042..27,790.568 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=733 read=127112
58. 15,468.432 16,065.669 ↓ 17.1 1,483,474 1

Sort (cost=9,567.93..9,784.65 rows=86,689 width=8) (actual time=12,601.057..16,065.669 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
59. 597.237 597.237 ↑ 1.0 86,689 1

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

  • Buffers: shared hit=1591
60. 6.967 14.685 ↑ 1.0 2,772 1

Hash (cost=129.72..129.72 rows=2,772 width=84) (actual time=14.685..14.685 rows=2,772 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 361kB
  • Buffers: shared hit=102
61. 7.718 7.718 ↑ 1.0 2,772 1

Seq Scan on project pj (cost=0.00..129.72 rows=2,772 width=84) (actual time=0.011..7.718 rows=2,772 loops=1)

  • Buffers: shared hit=102
62. 239.538 847.870 ↑ 1.0 86,689 1

Hash (cost=4,301.60..4,301.60 rows=86,689 width=45) (actual time=847.870..847.870 rows=86,689 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 4087kB
  • Buffers: shared hit=1593, temp written=383
63. 436.874 608.332 ↑ 1.0 86,689 1

Hash Left Join (cost=2.44..4,301.60 rows=86,689 width=45) (actual time=0.115..608.332 rows=86,689 loops=1)

  • Hash Cond: (tk.estimatedcostcurrencyid = exchangerate.fixedcurrencyid)
  • Buffers: shared hit=1593
64. 171.363 171.363 ↑ 1.0 86,689 1

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

  • Buffers: shared hit=1591
65. 0.022 0.095 ↑ 1.0 7 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=2
66. 0.073 0.073 ↑ 1.0 7 1

Seq Scan on exchangerate (cost=0.00..2.35 rows=7 width=4) (actual time=0.034..0.073 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=2
67. 2.511 5.450 ↑ 1.0 994 1

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

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

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

  • Buffers: shared hit=18
69. 0.059 19,300.502 ↓ 0.0 0 1

Sort (cost=226,544.22..226,624.12 rows=31,960 width=20) (actual time=19,300.502..19,300.502 rows=0 loops=1)

  • Sort Key: timeentrymetadata16.timeentryid
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=765 read=127080
70. 0.040 19,300.443 ↓ 0.0 0 1

Hash Left Join (cost=2.49..224,152.97 rows=31,960 width=20) (actual time=19,300.443..19,300.443 rows=0 loops=1)

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

Seq Scan on timeentrymetadata timeentrymetadata16 (cost=0.00..223,725.42 rows=31,960 width=74) (actual time=19,300.403..19,300.403 rows=0 loops=1)

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

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

73. 0.000 0.000 ↓ 0.0 0

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

74. 34.768 34.768 ↑ 1.0 1 4,346

Index Scan using clients_pkey on clients cl (cost=0.14..0.34 rows=1 width=16) (actual time=0.005..0.008 rows=1 loops=4,346)

  • Index Cond: (id = COALESCE(clients17.id, pj.clientid))
  • Buffers: shared hit=8692
75. 6,701.558 27,550.820 ↓ 22.7 726,976 1

Sort (cost=226,116.66..226,196.56 rows=31,960 width=60) (actual time=25,815.492..27,550.820 rows=726,976 loops=1)

  • Sort Key: timeentrymetadata9.timeentryid
  • Sort Method: external sort Disk: 53640kB
  • Buffers: shared hit=797 read=127048, temp read=14688 written=14689
76. 20,849.262 20,849.262 ↓ 22.8 727,165 1

Seq Scan on timeentrymetadata timeentrymetadata9 (cost=0.00..223,725.42 rows=31,960 width=60) (actual time=0.040..20,849.262 rows=727,165 loops=1)

  • Filter: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:COMMENTS'::text)
  • Rows Removed by Filter: 5657690
  • Buffers: shared hit=797 read=127048
77. 225.145 428.835 ↑ 1.0 86,689 1

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

  • Buckets: 65536 Batches: 4 Memory Usage: 2584kB
  • Buffers: shared hit=2525, temp written=678
78. 203.690 203.690 ↑ 1.0 86,689 1

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

  • Buffers: shared hit=2525
79. 2.642 34.804 ↑ 1.0 994 1

Hash (cost=85.76..85.76 rows=994 width=30) (actual time=34.804..34.804 rows=994 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 69kB
  • Buffers: shared hit=25
80. 4.565 32.162 ↑ 1.0 994 1

Hash Join (cost=30.49..85.76 rows=994 width=30) (actual time=8.314..32.162 rows=994 loops=1)

  • Hash Cond: (du.departmentid = dep.id)
  • Buffers: shared hit=25
81. 17.218 27.282 ↑ 1.0 994 1

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

  • Hash Cond: (login.userid = du.userid)
  • Buffers: shared hit=24
82. 2.102 2.102 ↑ 1.0 994 1

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

  • Buffers: shared hit=18
83. 2.675 7.962 ↑ 1.0 994 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 47kB
  • Buffers: shared hit=6
84. 5.287 5.287 ↑ 1.0 994 1

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

  • Buffers: shared hit=6
85. 0.168 0.315 ↑ 1.0 50 1

Hash (cost=1.50..1.50 rows=50 width=18) (actual time=0.315..0.315 rows=50 loops=1)

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

Seq Scan on departments dep (cost=0.00..1.50 rows=50 width=18) (actual time=0.008..0.147 rows=50 loops=1)

  • Buffers: shared hit=1
87. 2.449 5.408 ↑ 1.0 994 1

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

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

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

  • Buffers: shared hit=18
89. 0.977 1.787 ↑ 1.0 377 1

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

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

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

  • Buffers: shared hit=4
Planning time : 92.090 ms
Execution time : 618,749.379 ms