explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 05C : srahl

Settings
# exclusive inclusive rows x rows loops node
1. 0.463 190,252.213 ↑ 1,700.9 163 1

Sort (cost=2,000,534.88..2,001,228.00 rows=277,246 width=153) (actual time=190,252.181..190,252.213 rows=163 loops=1)

  • Sort Key: ((cl.name)::character varying(255)) COLLATE "en_US", ((pj.code)::character varying(50)) COLLATE "en_US", ((pj.name)::character varying(255)) COLLATE "en_US", ((tk.code)::character varying(50)) COLLATE "en_US", ((tk.name)::character varying(255) (...)
  • Sort Method: quicksort Memory: 60kB
  • Buffers: shared hit=3049836 read=478981 dirtied=9827 written=5749, temp read=86303 written=86299
2.          

CTE eexpensecostbilling2_facts0cte

3. 8.063 190,216.733 ↑ 1,700.9 163 1

HashAggregate (cost=1,955,739.72..1,959,205.30 rows=277,246 width=64) (actual time=190,216.014..190,216.733 rows=163 loops=1)

  • Group Key: at_1.userid, at_1.projectid, at_1.taskid, pbrh.currencyid
  • Buffers: shared hit=3049078 read=478976 dirtied=9827 written=5749, temp read=86303 written=86299
4. 3.530 190,208.670 ↑ 109.6 3,161 1

Hash Semi Join (cost=126,008.48..1,950,541.37 rows=346,557 width=64) (actual time=2,841.728..190,208.670 rows=3,161 loops=1)

  • Hash Cond: (at_1.projectid = project4.id)
  • Buffers: shared hit=3049078 read=478976 dirtied=9827 written=5749, temp read=86303 written=86299
5. 1,345.201 190,184.531 ↑ 219.3 3,161 1

Hash Join (cost=124,987.23..1,943,845.25 rows=693,114 width=76) (actual time=2,821.087..190,184.531 rows=3,161 loops=1)

  • Hash Cond: (at_1.projectid = pj_1.id)
  • Buffers: shared hit=3048782 read=478942 dirtied=9827 written=5749, temp read=86303 written=86299
6. 1,995.848 188,836.923 ↓ 1.4 3,866,357 1

Append (cost=124,684.82..1,905,407.58 rows=2,772,456 width=2,214) (actual time=2,599.992..188,836.923 rows=3,866,357 loops=1)

  • Buffers: shared hit=3048563 read=478942 dirtied=9827 written=5749, temp read=86303 written=86299
7. 1,727.993 28,290.044 ↑ 1.0 2,116,552 1

Result (cost=124,684.82..595,539.53 rows=2,135,015 width=2,810) (actual time=2,599.991..28,290.044 rows=2,116,552 loops=1)

  • Buffers: shared hit=990591 read=40567 dirtied=2251 written=502
8. 1,157.987 26,562.051 ↑ 1.0 2,116,552 1

Append (cost=124,684.82..595,539.53 rows=2,135,015 width=2,810) (actual time=2,599.980..26,562.051 rows=2,116,552 loops=1)

  • Buffers: shared hit=990588 read=40567 dirtied=2251 written=502
9. 3,404.074 25,403.676 ↑ 1.0 2,116,524 1

Hash Left Join (cost=124,684.82..574,045.70 rows=2,135,014 width=2,810) (actual time=2,599.979..25,403.676 rows=2,116,524 loops=1)

  • Hash Cond: ((at_1.projectid = ptm.projectid) AND (at_1.userid = ptm.userid) AND (at_1.entrydate = ptma.date))
  • Buffers: shared hit=990344 read=40563 dirtied=2251 written=502
10.          

Initplan (forHash Left Join)

11. 0.348 0.348 ↑ 1.0 1 1

Seq Scan on projectsysteminformation projectsysteminformation_2 (cost=0.00..1.01 rows=1 width=16) (actual time=0.348..0.348 rows=1 loops=1)

  • Buffers: shared read=1
12. 0.001 0.001 ↑ 1.0 1 1

Seq Scan on projectsysteminformation projectsysteminformation_3 (cost=0.00..1.01 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=1)

  • Buffers: shared hit=1
13. 1,703.647 21,998.983 ↑ 1.0 2,116,524 1

Hash Left Join (cost=124,610.03..528,601.84 rows=2,135,014 width=2,778) (actual time=2,597.863..21,998.983 rows=2,116,524 loops=1)

  • Hash Cond: (at_1.taskid = tk_1.id)
  • Buffers: shared hit=989477 read=40553 dirtied=2251 written=502
14. 1,726.107 20,276.655 ↑ 1.0 2,116,524 1

Hash Left Join (cost=123,625.78..501,011.05 rows=2,135,014 width=2,774) (actual time=2,579.022..20,276.655 rows=2,116,524 loops=1)

  • Hash Cond: (at_1.billingrateid = br.id)
  • Buffers: shared hit=989086 read=40473 dirtied=2251 written=502
15. 1,974.373 18,548.147 ↑ 1.0 2,116,524 1

Hash Left Join (cost=123,528.50..476,087.84 rows=2,135,014 width=2,728) (actual time=2,576.566..18,548.147 rows=2,116,524 loops=1)

  • Hash Cond: (pbr.id = pbrh.projectbillingrateid)
  • Join Filter: ((at_1.entrydate >= pbrh.effectivedate) AND (at_1.entrydate <= pbrh.enddate))
  • Rows Removed by Join Filter: 425306
  • Buffers: shared hit=989081 read=40448 dirtied=2251 written=502
16. 1,598.473 15,635.341 ↑ 1.0 2,116,524 1

Hash Left Join (cost=97,009.48..267,926.51 rows=2,135,014 width=2,732) (actual time=1,633.439..15,635.341 rows=2,116,524 loops=1)

  • Hash Cond: (at_1.projectid = pj_3.id)
  • Buffers: shared hit=989067 read=32051 dirtied=2207 written=502
17. 1,866.280 14,033.518 ↑ 1.0 2,116,524 1

Hash Join (cost=96,714.29..238,648.51 rows=2,135,014 width=2,728) (actual time=1,630.073..14,033.518 rows=2,116,524 loops=1)

  • Hash Cond: (at_1.timesheetid = ts.id)
  • Buffers: shared hit=988861 read=32051 dirtied=2207 written=502
18. 9,576.211 12,067.743 ↑ 1.0 2,116,524 1

Merge Left Join (cost=93,271.68..205,826.98 rows=2,141,009 width=2,716) (actual time=1,530.076..12,067.743 rows=2,116,524 loops=1)

  • Merge Cond: ((at_1.billingrateid = pbr.billingrateid) AND (at_1.projectid = pbr.projectid))
  • Join Filter: (((pbr.billingrateid = $10) AND (at_1.userid = pbr.userid)) OR ((pbr.billingrateid <> $11) AND (pbr.userid IS NULL)))
  • Rows Removed by Join Filter: 1429
  • Buffers: shared hit=988549 read=31240 dirtied=2207 written=502
  • -> Index Scan using ixpta4billingrateid on dm_projecttimeallocation_facts at_1 (cost=0.43..89371.26 rows=2141009 width=2700) (actual time=0.694..6470.474 rows=2116524 loops (...)
  • Filter: (timeoffcodeid IS NULL)
  • Rows Removed by Filter: 37775
  • Buffers: shared hit=988523 read=24502 dirtied=2146 written=502
19. 2,179.131 2,491.532 ↓ 3.1 2,475,468 1

Sort (cost=93,271.25..95,272.87 rows=800,648 width=40) (actual time=1,529.005..2,491.532 rows=2,475,468 loops=1)

  • Sort Key: pbr.billingrateid, pbr.projectid
  • Sort Method: quicksort Memory: 86757kB
  • Buffers: shared hit=25 read=6737 dirtied=61
20. 312.401 312.401 ↑ 1.0 795,911 1

Seq Scan on projectbillingrate pbr (cost=0.00..14,764.48 rows=800,648 width=40) (actual time=0.353..312.401 rows=795,911 loops=1)

  • Buffers: shared hit=21 read=6737 dirtied=61
21. 44.285 99.495 ↓ 1.0 103,248 1

Hash (cost=2,152.27..2,152.27 rows=103,227 width=28) (actual time=99.495..99.495 rows=103,248 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 7074kB
  • Buffers: shared hit=309 read=811
22. 55.210 55.210 ↓ 1.0 103,248 1

Seq Scan on timesheet ts (cost=0.00..2,152.27 rows=103,227 width=28) (actual time=0.003..55.210 rows=103,248 loops=1)

  • Buffers: shared hit=309 read=811
23. 1.253 3.350 ↓ 1.0 3,988 1

Hash (cost=245.64..245.64 rows=3,964 width=8) (actual time=3.350..3.350 rows=3,988 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 173kB
  • Buffers: shared hit=206
24. 2.097 2.097 ↓ 1.0 3,988 1

Seq Scan on project pj_3 (cost=0.00..245.64 rows=3,964 width=8) (actual time=0.004..2.097 rows=3,988 loops=1)

  • Buffers: shared hit=206
25. 459.915 938.433 ↑ 1.0 804,770 1

Hash (cost=16,459.01..16,459.01 rows=804,801 width=32) (actual time=938.433..938.433 rows=804,770 loops=1)

  • Buckets: 1048576 Batches: 1 Memory Usage: 61573kB
  • Buffers: shared hit=14 read=8397 dirtied=44
26. 478.518 478.518 ↑ 1.0 804,770 1

Seq Scan on projectbillingratehistory pbrh (cost=0.00..16,459.01 rows=804,801 width=32) (actual time=0.735..478.518 rows=804,770 loops=1)

  • Buffers: shared hit=14 read=8397 dirtied=44
27. 1.270 2.401 ↓ 1.0 2,991 1

Hash (cost=59.90..59.90 rows=2,990 width=46) (actual time=2.401..2.401 rows=2,991 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 262kB
  • Buffers: shared hit=5 read=25
28. 1.131 1.131 ↓ 1.0 2,991 1

Seq Scan on billingrate br (cost=0.00..59.90 rows=2,990 width=46) (actual time=0.012..1.131 rows=2,991 loops=1)

  • Buffers: shared hit=5 read=25
29. 7.363 18.681 ↑ 1.2 19,542 1

Hash (cost=699.11..699.11 rows=22,811 width=8) (actual time=18.681..18.681 rows=19,542 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 944kB
  • Buffers: shared hit=391 read=80
30. 11.318 11.318 ↑ 1.2 19,542 1

Seq Scan on task tk_1 (cost=0.00..699.11 rows=22,811 width=8) (actual time=0.008..11.318 rows=19,542 loops=1)

  • Buffers: shared hit=391 read=80
31. 0.029 0.270 ↑ 1.0 29 1

Hash (cost=72.27..72.27 rows=29 width=44) (actual time=0.270..0.270 rows=29 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=108 read=10
32. 0.074 0.241 ↑ 1.0 29 1

Nested Loop (cost=0.42..72.27 rows=29 width=44) (actual time=0.041..0.241 rows=29 loops=1)

  • Buffers: shared hit=108 read=10
33. 0.022 0.022 ↑ 1.0 29 1

Seq Scan on projectteammemberallocation ptma (cost=0.00..1.29 rows=29 width=52) (actual time=0.014..0.022 rows=29 loops=1)

  • Buffers: shared read=1
34. 0.145 0.145 ↑ 1.0 1 29

Index Scan using projectteammember_pkey on projectteammember ptm (cost=0.42..2.44 rows=1 width=24) (actual time=0.005..0.005 rows=1 loops=29)

  • Index Cond: (id = ptma.projectteammemberid)
  • Buffers: shared hit=108 read=9
35. 0.031 0.388 ↓ 28.0 28 1

Subquery Scan on *SELECT* 2 (cost=0.85..143.69 rows=1 width=44) (actual time=0.036..0.388 rows=28 loops=1)

  • Buffers: shared hit=244 read=4
36. 0.039 0.357 ↓ 28.0 28 1

Nested Loop Left Join (cost=0.85..143.68 rows=1 width=44) (actual time=0.031..0.357 rows=28 loops=1)

  • Filter: (at_2.id IS NULL)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=244 read=4
37. 0.049 0.144 ↑ 1.0 29 1

Nested Loop (cost=0.42..72.27 rows=29 width=44) (actual time=0.016..0.144 rows=29 loops=1)

  • Buffers: shared hit=117
38. 0.008 0.008 ↑ 1.0 29 1

Seq Scan on projectteammemberallocation ptma_1 (cost=0.00..1.29 rows=29 width=52) (actual time=0.004..0.008 rows=29 loops=1)

  • Filter: (id IS NOT NULL)
  • Buffers: shared hit=1
39. 0.087 0.087 ↑ 1.0 1 29

Index Scan using projectteammember_pkey on projectteammember ptm_1 (cost=0.42..2.44 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=29)

  • Index Cond: (id = ptma_1.projectteammemberid)
  • Buffers: shared hit=116
40. 0.174 0.174 ↓ 0.0 0 29

Index Scan using ixpta2userid on dm_projecttimeallocation_facts at_2 (cost=0.43..2.45 rows=1 width=28) (actual time=0.006..0.006 rows=0 loops=29)

  • Index Cond: ((ptm_1.userid = userid) AND (ptma_1.date = entrydate))
  • Filter: (ptm_1.projectid = projectid)
  • Rows Removed by Filter: 5
  • Buffers: shared hit=127 read=4
41. 24.674 122.101 ↓ 5.1 24,335 1

Subquery Scan on *SELECT* 3 (cost=3.57..918.64 rows=4,745 width=2,664) (actual time=1.357..122.101 rows=24,335 loops=1)

  • Buffers: shared hit=16199 read=404
42. 47.099 97.427 ↓ 5.1 24,335 1

Nested Loop (cost=3.57..871.19 rows=4,745 width=2,664) (actual time=1.352..97.427 rows=24,335 loops=1)

  • Buffers: shared hit=16199 read=404
43.          

Initplan (forNested Loop)

44. 0.014 0.014 ↑ 1.0 1 1

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

  • Buffers: shared read=1
45. 5.600 8.518 ↓ 5.1 4,644 1

Hash Join (cost=2.27..332.85 rows=906 width=30) (actual time=1.315..8.518 rows=4,644 loops=1)

  • Hash Cond: (ex.reimbursementcurrencyid = exchangerate_1.fixedcurrencyid)
  • Join Filter: ((ex.expensedate >= exchangerate_1.effectivedate) AND (ex.expensedate <= exchangerate_1.enddate))
  • Rows Removed by Join Filter: 184
  • Buffers: shared hit=800 read=44
46. 2.863 2.863 ↓ 1.0 4,644 1

Seq Scan on expense ex (cost=0.00..121.36 rows=4,636 width=27) (actual time=0.002..2.863 rows=4,644 loops=1)

  • Buffers: shared hit=32 read=43
47. 0.008 0.055 ↑ 1.1 12 1

Hash (cost=2.11..2.11 rows=13 width=19) (actual time=0.055..0.055 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=12 read=1
48. 0.047 0.047 ↑ 1.1 12 1

Seq Scan on exchangerate exchangerate_1 (cost=0.00..2.11 rows=13 width=19) (actual time=0.037..0.047 rows=12 loops=1)

  • Filter: (variablecurrencyid = $4)
  • Rows Removed by Filter: 77
  • Buffers: shared hit=12 read=1
49. 41.796 41.796 ↑ 1.0 5 4,644

Index Scan using ixeeexpenseid on expenseentry ee (cost=0.29..0.46 rows=5 width=2,638) (actual time=0.006..0.009 rows=5 loops=4,644)

  • Index Cond: (expenseid = ex.id)
  • Buffers: shared hit=15396 read=360
50. 4.418 3,085.051 ↑ 1.4 4,014 1

Subquery Scan on *SELECT* 4 (cost=57,144.92..58,015.11 rows=5,703 width=8) (actual time=3,070.397..3,085.051 rows=4,014 loops=1)

  • Buffers: shared hit=222450 read=7744 dirtied=325 written=395
51. 25.215 3,080.633 ↑ 1.4 4,014 1

Seq Scan on task tsk (cost=57,144.92..57,958.08 rows=5,703 width=8) (actual time=3,070.366..3,080.633 rows=4,014 loops=1)

  • Filter: ((NOT (hashed SubPlan 3)) AND (NOT (hashed SubPlan 4)))
  • Rows Removed by Filter: 15528
  • Buffers: shared hit=222447 read=7744 dirtied=325 written=395
52.          

SubPlan (forSeq Scan)

53. 501.364 3,052.827 ↓ 2.0 15,520 1

Unique (cost=0.43..57,079.02 rows=7,884 width=4) (actual time=0.030..3,052.827 rows=15,520 loops=1)

  • Buffers: shared hit=221919 read=7737 dirtied=324 written=395
54. 2,551.463 2,551.463 ↑ 1.0 1,877,995 1

Index Only Scan using ixpta2taskid on dm_projecttimeallocation_facts at (cost=0.43..52,333.33 rows=1,898,277 width=4) (actual time=0.028..2,551.463 rows=1,877,995 loops=1)

  • Index Cond: (taskid IS NOT NULL)
  • Heap Fetches: 255980
  • Buffers: shared hit=221919 read=7737 dirtied=324 written=395
55. 0.281 2.591 ↓ 22.5 180 1

Unique (cost=0.29..46.16 rows=8 width=4) (actual time=0.753..2.591 rows=180 loops=1)

  • Buffers: shared hit=57 read=7 dirtied=1
56. 2.310 2.310 ↓ 1.0 1,086 1

Index Only Scan using ixeetaskid on expenseentry (cost=0.29..43.47 rows=1,077 width=4) (actual time=0.752..2.310 rows=1,086 loops=1)

  • Index Cond: (taskid IS NOT NULL)
  • Heap Fetches: 148
  • Buffers: shared hit=57 read=7 dirtied=1
57. 1,789.280 155,343.879 ↓ 2.7 1,721,456 1

Subquery Scan on *SELECT* 5 (cost=681,751.90..1,250,934.30 rows=626,993 width=199) (actual time=26,283.553..155,343.879 rows=1,721,456 loops=1)

  • Buffers: shared hit=1819323 read=430227 dirtied=7251 written=4852, temp read=86303 written=86299
58. 3,122.583 153,554.599 ↓ 2.7 1,721,456 1

Hash Left Join (cost=681,751.90..1,244,664.37 rows=626,993 width=199) (actual time=26,283.546..153,554.599 rows=1,721,456 loops=1)

  • Hash Cond: (bi.id = iibi.billingitemid)
  • Buffers: shared hit=1819323 read=430227 dirtied=7251 written=4852, temp read=86303 written=86299
59.          

Initplan (forHash Left Join)

60. 0.008 0.008 ↑ 1.0 1 1

Seq Scan on projectsysteminformation (cost=0.00..1.01 rows=1 width=16) (actual time=0.007..0.008 rows=1 loops=1)

  • Buffers: shared hit=1
61. 0.001 0.001 ↑ 1.0 1 1

Seq Scan on projectsysteminformation projectsysteminformation_1 (cost=0.00..1.01 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=1)

  • Buffers: shared hit=1
62. 4,006.924 150,432.006 ↓ 2.7 1,721,456 1

Hash Right Join (cost=681,715.81..1,231,301.60 rows=626,993 width=183) (actual time=26,283.498..150,432.006 rows=1,721,456 loops=1)

  • Hash Cond: (bi2m.billingitemid = bi.id)
  • Buffers: shared hit=1819316 read=430227 dirtied=7251 written=4852, temp read=86303 written=86299
63. 120,178.403 120,178.403 ↓ 1.0 2,149,080 1

Index Scan using ixbimbillingitemidkey on billingitem2metadata bi2m (cost=0.43..535,841.30 rows=2,074,033 width=32) (actual time=31.787..120,178.403 rows=2,149,080 loops=1)

  • Buffers: shared hit=1753193 read=418691 dirtied=7249 written=4259
64. 1,733.260 26,246.679 ↓ 2.7 1,721,456 1

Hash (cost=673,877.97..673,877.97 rows=626,993 width=167) (actual time=26,246.679..26,246.679 rows=1,721,456 loops=1)

  • Buckets: 2097152 (originally 1048576) Batches: 2 (originally 1) Memory Usage: 247541kB
  • Buffers: shared hit=66110 read=11536 dirtied=2 written=593, temp read=48418 written=68980
65. 1,152.507 24,513.419 ↓ 2.7 1,721,456 1

Hash Left Join (cost=509,824.57..673,877.97 rows=626,993 width=167) (actual time=17,293.039..24,513.419 rows=1,721,456 loops=1)

  • Hash Cond: (bi.id = aggm.billingitemid)
  • Filter: (aggm.uri IS NULL)
  • Buffers: shared hit=66110 read=11536 dirtied=2 written=593, temp read=48418 written=48416
66. 1,425.349 23,360.907 ↓ 1.0 1,721,456 1

Hash Left Join (cost=509,822.41..667,495.85 rows=1,701,319 width=167) (actual time=17,293.014..23,360.907 rows=1,721,456 loops=1)

  • Hash Cond: (bidm.timesheetid = t.id)
  • Buffers: shared hit=66109 read=11536 dirtied=2 written=593, temp read=48418 written=48416
67. 1,222.862 21,856.244 ↓ 1.0 1,721,456 1

Hash Left Join (cost=506,379.80..640,935.16 rows=1,701,319 width=155) (actual time=17,213.222..21,856.244 rows=1,721,456 loops=1)

  • Hash Cond: (bidm.billingrateid = br_1.id)
  • Buffers: shared hit=64989 read=11536 dirtied=2 written=593, temp read=48418 written=48416
68. 1,182.839 20,631.399 ↓ 1.0 1,721,456 1

Hash Join (cost=506,282.53..621,113.93 rows=1,701,319 width=125) (actual time=17,211.210..20,631.399 rows=1,721,456 loops=1)

  • Hash Cond: (bidm.projectid = pj_4.id)
  • Buffers: shared hit=64959 read=11536 dirtied=2 written=593, temp read=48418 written=48416
69. 1,965.650 19,445.506 ↑ 1.0 1,721,456 1

Hash Right Join (cost=506,066.24..597,411.07 rows=1,726,234 width=125) (actual time=17,208.130..19,445.506 rows=1,721,456 loops=1)

  • Hash Cond: (pbrh_1.projectbillingrateid = pbr_1.id)
  • Join Filter: ((bi.entrydate >= pbrh_1.effectivedate) AND (bi.entrydate <= pbrh_1.enddate))
  • Rows Removed by Join Filter: 425306
  • Buffers: shared hit=63304 read=11536 dirtied=2 written=593, temp read=48418 written=48416
70. 320.168 320.168 ↑ 1.0 804,770 1

Seq Scan on projectbillingratehistory pbrh_1 (cost=0.00..16,459.01 rows=804,801 width=24) (actual time=0.005..320.168 rows=804,770 loops=1)

  • Buffers: shared hit=8411
71. 1,076.620 17,159.688 ↑ 1.0 1,721,456 1

Hash (cost=449,086.31..449,086.31 rows=1,726,234 width=137) (actual time=17,159.688..17,159.688 rows=1,721,456 loops=1)

  • Buckets: 2097152 Batches: 2 Memory Usage: 167905kB
  • Buffers: shared hit=54840 read=11536 dirtied=2 written=593, temp read=29249 written=46251
72. 2,023.445 16,083.068 ↑ 1.0 1,721,456 1

Merge Left Join (cost=425,034.60..449,086.31 rows=1,726,234 width=137) (actual time=12,106.614..16,083.068 rows=1,721,456 loops=1)

  • Merge Cond: ((bidm.projectid = pbr_1.projectid) AND (bidm.billingrateid = pbr_1.billingrateid))
  • Join Filter: (((pbr_1.billingrateid = $0) AND (bidm.userid = pbr_1.userid)) OR ((pbr_1.billingrateid <> $1) AND (pbr_1.userid IS NULL)))
  • Rows Removed by Join Filter: 1429
  • Buffers: shared hit=54840 read=11536 dirtied=2 written=593, temp read=29249 written=29249
73. 7,177.155 12,280.631 ↑ 1.0 1,721,456 1

Sort (cost=331,763.35..336,078.93 rows=1,726,234 width=121) (actual time=10,984.972..12,280.631 rows=1,721,456 loops=1)

  • Sort Key: bidm.projectid, bidm.billingrateid
  • Sort Method: external merge Disk: 233984kB
  • Buffers: shared hit=48077 read=11536 dirtied=2 written=593, temp read=29249 written=29249
74. 2,187.594 5,103.476 ↑ 1.0 1,721,456 1

Hash Join (cost=59,588.15..152,932.44 rows=1,726,234 width=121) (actual time=1,770.750..5,103.476 rows=1,721,456 loops=1)

  • Hash Cond: (bidm.billingitemid = bi.id)
  • Buffers: shared hit=48061 read=11536 dirtied=2 written=593
75. 1,155.156 1,155.156 ↑ 1.0 2,173,415 1

Seq Scan on billingitem2denormalizedmetadata bidm (cost=0.00..65,180.97 rows=2,180,197 width=108) (actual time=0.004..1,155.156 rows=2,173,415 loops=1)

  • Filter: (userid IS NOT NULL)
  • Buffers: shared hit=31843 read=11536 dirtied=2 written=593
76. 981.757 1,760.726 ↑ 1.0 1,721,456 1

Hash (cost=38,010.22..38,010.22 rows=1,726,234 width=29) (actual time=1,760.726..1,760.726 rows=1,721,456 loops=1)

  • Buckets: 2097152 Batches: 1 Memory Usage: 119936kB
  • Buffers: shared hit=16218
77. 778.969 778.969 ↑ 1.0 1,721,456 1

Seq Scan on billingitem2 bi (cost=0.00..38,010.22 rows=1,726,234 width=29) (actual time=0.006..778.969 rows=1,721,456 loops=1)

  • Filter: isbillable
  • Rows Removed by Filter: 451959
  • Buffers: shared hit=16218
78. 1,487.028 1,778.992 ↓ 3.1 2,476,110 1

Sort (cost=93,271.25..95,272.87 rows=800,648 width=40) (actual time=1,121.590..1,778.992 rows=2,476,110 loops=1)

  • Sort Key: pbr_1.projectid, pbr_1.billingrateid
  • Sort Method: quicksort Memory: 86757kB
  • Buffers: shared hit=6758
79. 291.964 291.964 ↑ 1.0 795,911 1

Seq Scan on projectbillingrate pbr_1 (cost=0.00..14,764.48 rows=800,648 width=40) (actual time=0.007..291.964 rows=795,911 loops=1)

  • Buffers: shared hit=6758
80. 1.302 3.054 ↓ 1.0 3,988 1

Hash (cost=166.74..166.74 rows=3,964 width=4) (actual time=3.054..3.054 rows=3,988 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 173kB
  • Buffers: shared hit=1655
81. 1.752 1.752 ↓ 1.0 3,988 1

Index Only Scan using project_pkey on project pj_4 (cost=0.28..166.74 rows=3,964 width=4) (actual time=0.026..1.752 rows=3,988 loops=1)

  • Heap Fetches: 2334
  • Buffers: shared hit=1655
82. 1.135 1.983 ↓ 1.0 2,991 1

Hash (cost=59.90..59.90 rows=2,990 width=46) (actual time=1.983..1.983 rows=2,991 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 262kB
  • Buffers: shared hit=30
83. 0.848 0.848 ↓ 1.0 2,991 1

Seq Scan on billingrate br_1 (cost=0.00..59.90 rows=2,990 width=46) (actual time=0.003..0.848 rows=2,991 loops=1)

  • Buffers: shared hit=30
84. 43.258 79.314 ↓ 1.0 103,248 1

Hash (cost=2,152.27..2,152.27 rows=103,227 width=28) (actual time=79.314..79.314 rows=103,248 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 7074kB
  • Buffers: shared hit=1120
85. 36.056 36.056 ↓ 1.0 103,248 1

Seq Scan on timesheet t (cost=0.00..2,152.27 rows=103,227 width=28) (actual time=0.003..36.056 rows=103,248 loops=1)

  • Buffers: shared hit=1120
86. 0.000 0.005 ↓ 0.0 0 1

Hash (cost=2.15..2.15 rows=1 width=69) (actual time=0.005..0.005 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=1
87. 0.005 0.005 ↓ 0.0 0 1

Index Scan using ixbiminvoicinghint on billingitem2metadata aggm (cost=0.12..2.15 rows=1 width=69) (actual time=0.005..0.005 rows=0 loops=1)

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

Hash (cost=20.70..20.70 rows=1,070 width=32) (actual time=0.001..0.001 rows=0 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 16kB
89. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on invoiceitembillingitems iibi (cost=0.00..20.70 rows=1,070 width=32) (actual time=0.001..0.001 rows=0 loops=1)

90. 0.004 2.407 ↑ 1.0 2 1

Hash (cost=302.38..302.38 rows=2 width=12) (actual time=2.407..2.407 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=219
91. 0.007 2.403 ↑ 1.0 2 1

Nested Loop Semi Join (cost=0.56..302.38 rows=2 width=12) (actual time=0.181..2.403 rows=2 loops=1)

  • Buffers: shared hit=219
92. 0.009 2.384 ↑ 10.0 2 1

Nested Loop (cost=0.28..294.61 rows=20 width=8) (actual time=0.167..2.384 rows=2 loops=1)

  • Buffers: shared hit=213
93. 2.357 2.357 ↑ 10.0 2 1

Seq Scan on project project2 (cost=0.00..265.46 rows=20 width=4) (actual time=0.146..2.357 rows=2 loops=1)

  • Filter: (upper((code)::text) = 'INCNYJAK18001'::text)
  • Rows Removed by Filter: 3986
  • Buffers: shared hit=206
94. 0.018 0.018 ↑ 1.0 1 2

Index Only Scan using project_pkey on project pj_1 (cost=0.28..1.45 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=2)

  • Index Cond: (id = project2.id)
  • Heap Fetches: 2
  • Buffers: shared hit=7
95. 0.012 0.012 ↑ 1.0 1 2

Index Scan using project_pkey on project pj_2 (cost=0.28..0.39 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=2)

  • Index Cond: (id = pj_1.id)
  • Filter: ((upper((info1)::text) = ANY ('{"CORE ACCESS","GLOBAL PRICING AND PRODUCT STRATEGY","GLOBAL PRICING AND PRODUCT STRATEGY - UK","HOBART INNOVATIONS",ISA,"LEHMAN MILLET","PFV - CHI","PFV - LA","PFV - NJ","PFV - (...)
  • Buffers: shared hit=6
96. 0.025 20.609 ↑ 67.3 44 1

Hash (cost=984.21..984.21 rows=2,963 width=4) (actual time=20.609..20.609 rows=44 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 34kB
  • Buffers: shared hit=296 read=34
97. 2.927 20.584 ↑ 67.3 44 1

Hash Left Join (cost=795.65..984.21 rows=2,963 width=4) (actual time=11.916..20.584 rows=44 loops=1)

  • Hash Cond: (clients7.id = clientsharingassignment8.clientid)
  • Filter: ((project4.projectleaderapproverid = 619) OR (projectsharingassignment5.userid = 619) OR (clients7.clientmanageruserid = 619) OR (clientsharingassignment8.userid = 619))
  • Rows Removed by Filter: 8080
  • Buffers: shared hit=296 read=34
98. 4.537 17.651 ↓ 1.4 8,124 1

Hash Right Join (cost=794.60..960.44 rows=5,846 width=20) (actual time=11.556..17.651 rows=8,124 loops=1)

  • Hash Cond: (projectsharingassignment5.projectid = project4.id)
  • Buffers: shared hit=295 read=34
99. 1.595 1.595 ↓ 1.0 5,883 1

Seq Scan on projectsharingassignment projectsharingassignment5 (cost=0.00..85.46 rows=5,846 width=8) (actual time=0.010..1.595 rows=5,883 loops=1)

  • Buffers: shared hit=2 read=25
100. 1.485 11.519 ↓ 1.0 3,988 1

Hash (cost=745.05..745.05 rows=3,964 width=16) (actual time=11.519..11.519 rows=3,988 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 206kB
  • Buffers: shared hit=293 read=9
101. 2.490 10.034 ↓ 1.0 3,988 1

Hash Left Join (cost=390.60..745.05 rows=3,964 width=16) (actual time=3.044..10.034 rows=3,988 loops=1)

  • Hash Cond: (projectclient6.clientid = clients7.id)
  • Buffers: shared hit=293 read=9
102. 2.539 7.218 ↓ 1.0 3,988 1

Hash Left Join (cost=123.74..423.69 rows=3,964 width=12) (actual time=2.700..7.218 rows=3,988 loops=1)

  • Hash Cond: (project4.id = projectclient6.projectid)
  • Buffers: shared hit=241
103. 2.007 2.007 ↓ 1.0 3,988 1

Seq Scan on project project4 (cost=0.00..245.64 rows=3,964 width=8) (actual time=0.004..2.007 rows=3,988 loops=1)

  • Buffers: shared hit=206
104. 1.383 2.672 ↓ 1.0 3,968 1

Hash (cost=74.44..74.44 rows=3,944 width=8) (actual time=2.672..2.672 rows=3,968 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 187kB
  • Buffers: shared hit=35
105. 1.289 1.289 ↓ 1.0 3,968 1

Seq Scan on projectclient projectclient6 (cost=0.00..74.44 rows=3,944 width=8) (actual time=0.002..1.289 rows=3,968 loops=1)

  • Buffers: shared hit=35
106. 0.100 0.326 ↓ 1.0 293 1

Hash (cost=263.21..263.21 rows=292 width=8) (actual time=0.326..0.326 rows=293 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
  • Buffers: shared hit=52 read=9
107. 0.226 0.226 ↓ 1.0 293 1

Index Scan using clients_pkey on clients clients7 (cost=0.27..263.21 rows=292 width=8) (actual time=0.008..0.226 rows=293 loops=1)

  • Buffers: shared hit=52 read=9
108. 0.005 0.006 ↑ 1.0 2 1

Hash (cost=1.02..1.02 rows=2 width=8) (actual time=0.006..0.006 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
109. 0.001 0.001 ↑ 1.0 2 1

Seq Scan on clientsharingassignment clientsharingassignment8 (cost=0.00..1.02 rows=2 width=8) (actual time=0.001..0.001 rows=2 loops=1)

  • Buffers: shared hit=1
110.          

Initplan (forSort)

111. 0.011 0.011 ↑ 1.0 1 1

Seq Scan on systeminformation systeminformation_1 (cost=0.00..1.01 rows=1 width=4) (actual time=0.010..0.011 rows=1 loops=1)

  • Buffers: shared read=1
112. 0.123 190,251.739 ↑ 1,700.9 163 1

Hash Left Join (cost=1,958.52..16,264.42 rows=277,246 width=153) (actual time=190,250.564..190,251.739 rows=163 loops=1)

  • Hash Cond: (imeexpensecostbilling2_facts12.currencyid5 = currencyinfo13.id)
  • Buffers: shared hit=3049817 read=478981 dirtied=9827 written=5749, temp read=86303 written=86299
113. 0.129 190,251.605 ↑ 1,700.9 163 1

Hash Left Join (cost=1,957.36..15,126.55 rows=277,246 width=153) (actual time=190,250.537..190,251.605 rows=163 loops=1)

  • Hash Cond: (imeexpensecostbilling2_facts12.projectid3 = pj.id)
  • Buffers: shared hit=3049816 read=478981 dirtied=9827 written=5749, temp read=86303 written=86299
114. 0.266 190,244.786 ↑ 1,700.9 163 1

Hash Left Join (cost=1,341.00..10,698.06 rows=277,246 width=100) (actual time=190,243.817..190,244.786 rows=163 loops=1)

  • Hash Cond: (imeexpensecostbilling2_facts12.taskid4 = tk.id)
  • Buffers: shared hit=3049552 read=478977 dirtied=9827 written=5749, temp read=86303 written=86299
115. 190,216.853 190,216.853 ↑ 1,700.9 163 1

CTE Scan on eexpensecostbilling2_facts0cte imeexpensecostbilling2_facts12 (cost=0.00..5,544.92 rows=277,246 width=64) (actual time=190,216.017..190,216.853 rows=163 loops=1)

  • Buffers: shared hit=3049078 read=478976 dirtied=9827 written=5749, temp read=86303 written=86299
116. 8.404 27.667 ↑ 1.2 19,542 1

Hash (cost=1,055.87..1,055.87 rows=22,811 width=40) (actual time=27.667..27.667 rows=19,542 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1656kB
  • Buffers: shared hit=474 read=1
117. 13.666 19.263 ↑ 1.2 19,542 1

Hash Left Join (cost=2.59..1,055.87 rows=22,811 width=40) (actual time=0.064..19.263 rows=19,542 loops=1)

  • Hash Cond: (tk.estimatedcostcurrencyid = exchangerate.fixedcurrencyid)
  • Buffers: shared hit=474 read=1
118. 5.548 5.548 ↑ 1.2 19,542 1

Seq Scan on task tk (cost=0.00..699.11 rows=22,811 width=44) (actual time=0.006..5.548 rows=19,542 loops=1)

  • Buffers: shared hit=471
119. 0.005 0.049 ↑ 1.0 7 1

Hash (cost=2.50..2.50 rows=7 width=4) (actual time=0.049..0.049 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=3 read=1
120. 0.044 0.044 ↑ 1.0 7 1

Index Scan using uix3er_currencyideffectivedate on exchangerate (cost=0.15..2.50 rows=7 width=4) (actual time=0.035..0.044 rows=7 loops=1)

  • Index Cond: ((variablecurrencyid = $16) AND (('now'::cstring)::date >= effectivedate))
  • Filter: (('now'::cstring)::date <= enddate)
  • Rows Removed by Filter: 5
  • Buffers: shared hit=3 read=1
121. 1.971 6.690 ↓ 1.0 3,988 1

Hash (cost=566.81..566.81 rows=3,964 width=57) (actual time=6.690..6.690 rows=3,988 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 381kB
  • Buffers: shared hit=264 read=4
122. 2.410 4.719 ↓ 1.0 3,988 1

Hash Left Join (cost=266.86..566.81 rows=3,964 width=57) (actual time=0.300..4.719 rows=3,988 loops=1)

  • Hash Cond: (pj.clientid = cl.id)
  • Buffers: shared hit=264 read=4
123. 2.028 2.028 ↓ 1.0 3,988 1

Seq Scan on project pj (cost=0.00..245.64 rows=3,964 width=48) (actual time=0.005..2.028 rows=3,988 loops=1)

  • Buffers: shared hit=206
124. 0.108 0.281 ↓ 1.0 293 1

Hash (cost=263.21..263.21 rows=292 width=17) (actual time=0.281..0.281 rows=293 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
  • Buffers: shared hit=58 read=4
125. 0.173 0.173 ↓ 1.0 293 1

Index Scan using clients_pkey on clients cl (cost=0.27..263.21 rows=292 width=17) (actual time=0.010..0.173 rows=293 loops=1)

  • Buffers: shared hit=58 read=4
126. 0.005 0.011 ↑ 1.0 7 1

Hash (cost=1.07..1.07 rows=7 width=8) (actual time=0.011..0.011 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
127. 0.006 0.006 ↑ 1.0 7 1

Seq Scan on currencyinfo currencyinfo13 (cost=0.00..1.07 rows=7 width=8) (actual time=0.003..0.006 rows=7 loops=1)

  • Buffers: shared hit=1