explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KYx6 : PHdynamic

Settings
# exclusive inclusive rows x rows loops node
1. 0.432 192,857.804 ↑ 1,691.5 163 1

Sort (cost=4,968,233.85..4,968,923.14 rows=275,716 width=267) (actual time=192,857.740..192,857.804 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=14050197 read=470696, temp read=273686 written=272797
2.          

CTE eexpensecostbilling2_facts0cte

3. 2.147 192,797.926 ↑ 1,691.5 163 1

GroupAggregate (cost=4,883,313.54..4,892,791.26 rows=275,716 width=64) (actual time=192,794.378..192,797.926 rows=163 loops=1)

  • Group Key: at_1.userid, at_1.projectid, at_1.taskid, pbrh.currencyid
  • Buffers: shared hit=14050190 read=470163, temp read=273686 written=272797
4. 5.686 192,795.779 ↑ 109.0 3,161 1

Sort (cost=4,883,313.54..4,884,175.15 rows=344,644 width=64) (actual time=192,794.355..192,795.779 rows=3,161 loops=1)

  • Sort Key: at_1.userid, at_1.projectid, at_1.taskid, pbrh.currencyid
  • Sort Method: quicksort Memory: 343kB
  • Buffers: shared hit=14050190 read=470163, temp read=273686 written=272797
5. 3.899 192,790.093 ↑ 109.0 3,161 1

Hash Semi Join (cost=2,505.07..4,838,654.85 rows=344,644 width=64) (actual time=426.479..192,790.093 rows=3,161 loops=1)

  • Hash Cond: (at_1.projectid = project4.id)
  • Buffers: shared hit=14050190 read=470163, temp read=273686 written=272797
6. 1,713.797 192,753.968 ↑ 218.1 3,161 1

Hash Join (cost=1,809.93..4,832,316.16 rows=689,288 width=76) (actual time=394.224..192,753.968 rows=3,161 loops=1)

  • Hash Cond: (at_1.projectid = pj_1.id)
  • Buffers: shared hit=14050046 read=470107, temp read=273686 written=272797
7. 2,957.409 191,036.435 ↓ 1.4 3,867,139 1

Append (cost=1,475.33..4,794,056.90 rows=2,757,152 width=2,207) (actual time=33.518..191,036.435 rows=3,867,139 loops=1)

  • Buffers: shared hit=14049900 read=470106, temp read=273686 written=272797
8. 2,185.618 49,209.354 ↓ 1.0 2,116,987 1

Result (cost=1,475.33..2,479,749.30 rows=2,115,683 width=2,811) (actual time=33.516..49,209.354 rows=2,116,987 loops=1)

  • Buffers: shared hit=13997165 read=49629
9. 1,633.709 47,023.736 ↓ 1.0 2,116,987 1

Append (cost=1,475.33..2,479,749.30 rows=2,115,683 width=2,811) (actual time=33.509..47,023.736 rows=2,116,987 loops=1)

  • Buffers: shared hit=13997165 read=49629
10. 3,746.100 45,386.678 ↓ 1.0 2,116,959 1

Hash Left Join (cost=1,475.33..2,458,100.79 rows=2,115,682 width=2,811) (actual time=33.508..45,386.678 rows=2,116,959 loops=1)

  • Hash Cond: ((at_1.projectid = ptm.projectid) AND (at_1.userid = ptm.userid) AND (at_1.entrydate = ptma.date))
  • Buffers: shared hit=13996920 read=49623
11.          

Initplan (forHash Left Join)

12. 0.010 0.010 ↑ 690.0 1 1

Seq Scan on projectsysteminformation projectsysteminformation_2 (cost=0.00..16.90 rows=690 width=16) (actual time=0.010..0.010 rows=1 loops=1)

  • Buffers: shared read=1
13. 0.002 0.002 ↑ 690.0 1 1

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

  • Buffers: shared hit=1
14. 2,300.383 41,640.297 ↓ 1.0 2,116,959 1

Hash Left Join (cost=1,194.76..2,412,861.96 rows=2,115,682 width=2,779) (actual time=33.186..41,640.297 rows=2,116,959 loops=1)

  • Hash Cond: (at_1.taskid = tk_1.id)
  • Buffers: shared hit=13996814 read=49609
15. 2,178.544 39,318.138 ↓ 1.0 2,116,959 1

Hash Left Join (cost=362.06..2,385,669.99 rows=2,115,682 width=2,775) (actual time=11.262..39,318.138 rows=2,116,959 loops=1)

  • Hash Cond: (at_1.billingrateid = br.id)
  • Buffers: shared hit=13996421 read=49609
16. 3,491.903 37,136.018 ↓ 1.0 2,116,959 1

Nested Loop Left Join (cost=264.76..2,360,760.68 rows=2,115,682 width=2,729) (actual time=7.636..37,136.018 rows=2,116,959 loops=1)

  • Join Filter: ((at_1.entrydate >= pbrh.effectivedate) AND (at_1.entrydate <= pbrh.enddate))
  • Rows Removed by Join Filter: 425331
  • Buffers: shared hit=13996416 read=49581
17. 3,258.579 25,176.279 ↓ 1.0 2,116,959 1

Nested Loop Left Join (cost=264.34..1,271,708.25 rows=2,115,682 width=2,733) (actual time=6.210..25,176.279 rows=2,116,959 loops=1)

  • Buffers: shared hit=7109426 read=43805
18. 2,183.156 13,449.864 ↓ 1.0 2,116,959 1

Hash Left Join (cost=263.91..250,992.78 rows=2,115,682 width=2,717) (actual time=4.208..13,449.864 rows=2,116,959 loops=1)

  • Hash Cond: (at_1.projectid = pj_3.id)
  • Buffers: shared hit=270088 read=39240
19. 11,093.304 11,262.554 ↓ 1.0 2,116,959 1

Merge Join (cost=39.16..222,009.56 rows=2,115,682 width=2,713) (actual time=0.024..11,262.554 rows=2,116,959 loops=1)

  • Merge Cond: (ts.id = at_1.timesheetid)
  • Buffers: shared hit=269953 read=39240
  • -> Index Scan using ixptatimesheetid on dm_projecttimeallocation_facts at_1 (cost=0.43..187920.54 rows=2121268 width=2701) (actual time=0.009..8914.914 rows=2116959 l (...)
20. 169.250 169.250 ↑ 1.0 103,252 1

Index Scan using timesheet_pkey on timesheet ts (cost=0.42..7,409.21 rows=103,253 width=28) (actual time=0.009..169.250 rows=103,252 loops=1)

  • Buffers: shared hit=103158 read=394
  • Filter: (timeoffcodeid IS NULL)
  • Rows Removed by Filter: 37779
  • Buffers: shared hit=166795 read=38846
21. 1.919 4.154 ↑ 1.0 3,989 1

Hash (cost=174.89..174.89 rows=3,989 width=8) (actual time=4.154..4.154 rows=3,989 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 173kB
  • Buffers: shared hit=135
22. 2.235 2.235 ↑ 1.0 3,989 1

Seq Scan on project pj_3 (cost=0.00..174.89 rows=3,989 width=8) (actual time=0.003..2.235 rows=3,989 loops=1)

  • Buffers: shared hit=135
23. 8,467.836 8,467.836 ↑ 1.0 1 2,116,959

Index Scan using ixpbr2projectid on projectbillingrate pbr (cost=0.42..0.47 rows=1 width=40) (actual time=0.003..0.004 rows=1 loops=2,116,959)

  • Index Cond: ((at_1.projectid = projectid) AND (at_1.billingrateid = billingrateid))
  • Filter: (((billingrateid = $10) AND (at_1.userid = userid)) OR ((billingrateid <> $11) AND (userid IS NULL)))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=6839338 read=4565
24. 8,467.836 8,467.836 ↑ 1.0 1 2,116,959

Index Scan using ixpbrhprojectbillingrateid on projectbillingratehistory pbrh (cost=0.42..0.50 rows=1 width=32) (actual time=0.003..0.004 rows=1 loops=2,116,959)

  • Index Cond: (pbr.id = projectbillingrateid)
  • Buffers: shared hit=6886990 read=5776
25. 1.618 3.576 ↑ 1.0 2,991 1

Hash (cost=59.91..59.91 rows=2,991 width=46) (actual time=3.576..3.576 rows=2,991 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 262kB
  • Buffers: shared hit=2 read=28
26. 1.958 1.958 ↑ 1.0 2,991 1

Seq Scan on billingrate br (cost=0.00..59.91 rows=2,991 width=46) (actual time=0.013..1.958 rows=2,991 loops=1)

  • Buffers: shared hit=2 read=28
27. 8.950 21.776 ↑ 1.0 19,542 1

Hash (cost=588.42..588.42 rows=19,542 width=8) (actual time=21.776..21.776 rows=19,542 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 944kB
  • Buffers: shared hit=393
28. 12.826 12.826 ↑ 1.0 19,542 1

Seq Scan on task tk_1 (cost=0.00..588.42 rows=19,542 width=8) (actual time=0.013..12.826 rows=19,542 loops=1)

  • Buffers: shared hit=393
29. 0.023 0.269 ↑ 1.0 29 1

Hash (cost=246.27..246.27 rows=29 width=44) (actual time=0.269..0.269 rows=29 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
  • Buffers: shared hit=103 read=14
30. 0.047 0.246 ↑ 1.0 29 1

Nested Loop (cost=0.42..246.27 rows=29 width=44) (actual time=0.055..0.246 rows=29 loops=1)

  • Buffers: shared hit=103 read=14
31. 0.025 0.025 ↑ 1.0 29 1

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

  • Buffers: shared read=1
32. 0.174 0.174 ↑ 1.0 1 29

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

  • Index Cond: (id = ptma.projectteammemberid)
  • Buffers: shared hit=103 read=13
33. 0.046 3.349 ↓ 28.0 28 1

Subquery Scan on *SELECT* 2 (cost=0.85..491.69 rows=1 width=44) (actual time=1.072..3.349 rows=28 loops=1)

  • Buffers: shared hit=245 read=6
34. 0.060 3.303 ↓ 28.0 28 1

Nested Loop Left Join (cost=0.85..491.68 rows=1 width=44) (actual time=1.067..3.303 rows=28 loops=1)

  • Filter: (at_2.id IS NULL)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=245 read=6
35. 0.063 0.169 ↑ 1.0 29 1

Nested Loop (cost=0.42..246.27 rows=29 width=44) (actual time=0.015..0.169 rows=29 loops=1)

  • Buffers: shared hit=117
36. 0.019 0.019 ↑ 1.0 29 1

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

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

Index Scan using projectteammember_pkey on projectteammember ptm_1 (cost=0.42..8.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
38. 3.074 3.074 ↓ 0.0 0 29

Index Scan using ixpta2userid on dm_projecttimeallocation_facts at_2 (cost=0.43..8.45 rows=1 width=28) (actual time=0.106..0.106 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=128 read=6
39. 32.232 158.850 ↓ 5.1 24,344 1

Subquery Scan on *SELECT* 3 (cost=3.57..1,148.74 rows=4,757 width=2,664) (actual time=0.106..158.850 rows=24,344 loops=1)

  • Buffers: shared hit=15259 read=458
40. 57.791 126.618 ↓ 5.1 24,344 1

Nested Loop (cost=3.57..1,101.17 rows=4,757 width=2,664) (actual time=0.102..126.618 rows=24,344 loops=1)

  • Buffers: shared hit=15259 read=458
41.          

Initplan (forNested Loop)

42. 0.009 0.009 ↑ 1.0 1 1

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

  • Buffers: shared read=1
43. 5.771 8.433 ↓ 5.1 4,645 1

Hash Join (cost=2.27..329.35 rows=908 width=30) (actual time=0.080..8.433 rows=4,645 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=10 read=63
44. 2.614 2.614 ↑ 1.0 4,645 1

Seq Scan on expense ex (cost=0.00..117.45 rows=4,645 width=27) (actual time=0.004..2.614 rows=4,645 loops=1)

  • Buffers: shared hit=10 read=61
45. 0.013 0.048 ↑ 1.1 12 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared read=2
46. 0.035 0.035 ↑ 1.1 12 1

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

  • Filter: (variablecurrencyid = $4)
  • Rows Removed by Filter: 77
  • Buffers: shared read=2
47. 60.385 60.385 ↑ 1.0 5 4,645

Index Scan using ixeeexpenseid on expenseentry ee (cost=0.29..0.72 rows=5 width=2,638) (actual time=0.008..0.013 rows=5 loops=4,645)

  • Index Cond: (expenseid = ex.id)
  • Buffers: shared hit=15249 read=395
48. 5.299 1,934.572 ↑ 1.2 4,013 1

Subquery Scan on *SELECT* 4 (cost=57,384.97..58,119.96 rows=4,886 width=8) (actual time=1,919.719..1,934.572 rows=4,013 loops=1)

  • Buffers: shared hit=31361 read=4
49. 18.301 1,929.273 ↑ 1.2 4,013 1

Seq Scan on task tsk (cost=57,384.97..58,071.10 rows=4,886 width=8) (actual time=1,919.695..1,929.273 rows=4,013 loops=1)

  • Filter: ((NOT (hashed SubPlan 3)) AND (NOT (hashed SubPlan 4)))
  • Rows Removed by Filter: 15529
  • Buffers: shared hit=31361 read=4
50.          

SubPlan (forSeq Scan)

51. 950.830 1,908.937 ↓ 2.0 15,521 1

HashAggregate (cost=56,845.78..56,925.15 rows=7,937 width=4) (actual time=1,901.326..1,908.937 rows=15,521 loops=1)

  • Group Key: at.taskid
  • Buffers: shared hit=30607
52. 958.107 958.107 ↓ 1.0 1,878,352 1

Seq Scan on dm_projecttimeallocation_facts at (cost=0.00..52,154.38 rows=1,876,561 width=4) (actual time=0.005..958.107 rows=1,878,352 loops=1)

  • Filter: (taskid IS NOT NULL)
  • Rows Removed by Filter: 276386
  • Buffers: shared hit=30607
53. 0.485 2.035 ↓ 20.0 180 1

Unique (cost=0.29..439.95 rows=9 width=4) (actual time=0.686..2.035 rows=180 loops=1)

  • Buffers: shared hit=361 read=4
54. 1.550 1.550 ↑ 1.0 1,086 1

Index Only Scan using ixeetaskid on expenseentry (cost=0.29..437.24 rows=1,086 width=4) (actual time=0.685..1.550 rows=1,086 loops=1)

  • Index Cond: (taskid IS NOT NULL)
  • Heap Fetches: 1086
  • Buffers: shared hit=361 read=4
55. 2,133.690 136,776.250 ↓ 2.7 1,721,795 1

Subquery Scan on *SELECT* 5 (cost=1,341,303.78..2,255,038.90 rows=631,826 width=199) (actual time=34,270.162..136,776.250 rows=1,721,795 loops=1)

  • Buffers: shared hit=6115 read=420015, temp read=273686 written=272797
56. 3,220.593 134,642.560 ↓ 2.7 1,721,795 1

Hash Left Join (cost=1,341,303.78..2,248,720.64 rows=631,826 width=199) (actual time=34,270.155..134,642.560 rows=1,721,795 loops=1)

  • Hash Cond: (bi.id = iibi.billingitemid)
  • Buffers: shared hit=6115 read=420015, temp read=273686 written=272797
57.          

Initplan (forHash Left Join)

58. 0.012 0.012 ↑ 690.0 1 1

Seq Scan on projectsysteminformation (cost=0.00..16.90 rows=690 width=16) (actual time=0.011..0.012 rows=1 loops=1)

  • Buffers: shared read=1
59. 0.001 0.001 ↑ 690.0 1 1

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

  • Buffers: shared hit=1
60. 3,572.651 131,421.951 ↓ 2.7 1,721,795 1

Hash Right Join (cost=1,341,235.90..2,235,223.35 rows=631,826 width=183) (actual time=34,270.115..131,421.951 rows=1,721,795 loops=1)

  • Hash Cond: (bi2m.billingitemid = bi.id)
  • Buffers: shared hit=6115 read=420015, temp read=273686 written=272797
61. 93,597.266 94,038.227 ↑ 1.0 2,149,515 1

Bitmap Heap Scan on billingitem2metadata bi2m (cost=44,402.38..879,802.05 rows=2,153,511 width=32) (actual time=447.232..94,038.227 rows=2,149,515 loops=1)

  • Recheck Cond: (upper(key) = 'URN:REPLICON:BILLING-ITEM-METADATA-KEY:TIME-WORKED'::text)
  • Rows Removed by Index Recheck: 5922146
  • Heap Blocks: exact=25435 lossy=317396
  • Buffers: shared read=351069
62. 440.961 440.961 ↑ 1.0 2,149,515 1

Bitmap Index Scan on ixbimbillingitemidkey (cost=0.00..43,864.00 rows=2,153,511 width=0) (actual time=440.961..440.961 rows=2,149,515 loops=1)

  • Buffers: shared read=8238
63. 1,351.356 33,811.073 ↓ 2.7 1,721,795 1

Hash (cost=1,274,126.69..1,274,126.69 rows=631,826 width=167) (actual time=33,811.073..33,811.073 rows=1,721,795 loops=1)

  • Buckets: 32768 (originally 32768) Batches: 128 (originally 64) Memory Usage: 3841kB
  • Buffers: shared hit=6115 read=68946, temp read=218946 written=259067
64. 1,316.659 32,459.717 ↓ 2.7 1,721,795 1

Hash Right Join (cost=1,130,929.09..1,274,126.69 rows=631,826 width=167) (actual time=31,143.213..32,459.717 rows=1,721,795 loops=1)

  • Hash Cond: (aggm.billingitemid = bi.id)
  • Filter: (aggm.uri IS NULL)
  • Buffers: shared hit=6115 read=68946, temp read=218946 written=218311
65. 0.003 0.029 ↓ 0.0 0 1

Bitmap Heap Scan on billingitem2metadata aggm (cost=11.71..102,332.65 rows=30,325 width=88) (actual time=0.029..0.029 rows=0 loops=1)

  • Recheck Cond: (upper(key) = 'URN:REPLICON:BILLING-ITEM-METADATA-KEY:BILLING-ITEM-AGGREGATION-FOR-INVOICING-HINT'::text)
  • Buffers: shared read=1
66. 0.026 0.026 ↓ 0.0 0 1

Bitmap Index Scan on ixbiminvoicinghint (cost=0.00..4.13 rows=30,325 width=0) (actual time=0.026..0.026 rows=0 loops=1)

  • Buffers: shared read=1
67. 1,524.808 31,143.029 ↓ 1.0 1,721,795 1

Hash (cost=1,070,107.26..1,070,107.26 rows=1,692,089 width=167) (actual time=31,143.029..31,143.029 rows=1,721,795 loops=1)

  • Buckets: 32768 Batches: 128 Memory Usage: 2993kB
  • Buffers: shared hit=6115 read=68945, temp read=177936 written=218184
68. 1,638.524 29,618.221 ↓ 1.0 1,721,795 1

Hash Left Join (cost=849,449.58..1,070,107.26 rows=1,692,089 width=167) (actual time=21,392.118..29,618.221 rows=1,721,795 loops=1)

  • Hash Cond: (bidm.billingrateid = br_1.id)
  • Buffers: shared hit=6115 read=68945, temp read=177936 written=177428
69. 2,288.527 27,976.660 ↓ 1.0 1,721,795 1

Hash Left Join (cost=849,352.28..1,050,284.72 rows=1,692,089 width=137) (actual time=21,389.048..27,976.660 rows=1,721,795 loops=1)

  • Hash Cond: (bidm.timesheetid = t.id)
  • Buffers: shared hit=6113 read=68917, temp read=177936 written=177428
70. 1,576.148 25,582.187 ↓ 1.0 1,721,795 1

Hash Join (cost=845,258.09..959,687.70 rows=1,692,089 width=125) (actual time=21,282.781..25,582.187 rows=1,721,795 loops=1)

  • Hash Cond: (bidm.projectid = pj_4.id)
  • Buffers: shared hit=5048 read=68917, temp read=161567 written=161061
71. 2,260.706 24,002.132 ↓ 1.0 1,721,795 1

Hash Right Join (cost=845,033.33..936,101.19 rows=1,717,566 width=125) (actual time=21,278.834..24,002.132 rows=1,721,795 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: 425331
  • Buffers: shared hit=4913 read=68917, temp read=161567 written=161061
72. 466.698 466.698 ↑ 1.0 804,773 1

Seq Scan on projectbillingratehistory pbrh_1 (cost=0.00..16,337.73 rows=804,773 width=24) (actual time=0.013..466.698 rows=804,773 loops=1)

  • Buffers: shared hit=2558 read=5732
73. 1,339.349 21,274.728 ↓ 1.0 1,721,795 1

Hash (cost=788,339.76..788,339.76 rows=1,717,566 width=137) (actual time=21,274.728..21,274.728 rows=1,721,795 loops=1)

  • Buckets: 32768 (originally 32768) Batches: 256 (originally 128) Memory Usage: 3841kB
  • Buffers: shared hit=2355 read=63185, temp read=108661 written=142797
74. 3,009.507 19,935.379 ↓ 1.0 1,721,795 1

Merge Left Join (cost=762,877.46..788,339.76 rows=1,717,566 width=137) (actual time=12,918.404..19,935.379 rows=1,721,795 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=2355 read=63185, temp read=108661 written=108599
75. 6,925.583 13,605.817 ↓ 1.0 1,721,795 1

Sort (cost=648,510.28..652,804.19 rows=1,717,566 width=121) (actual time=11,415.860..13,605.817 rows=1,721,795 loops=1)

  • Sort Key: bidm.projectid, bidm.billingrateid
  • Sort Method: external merge Disk: 234072kB
  • Buffers: shared hit=6 read=58899, temp read=104186 written=104124
76. 4,714.027 6,680.234 ↓ 1.0 1,721,795 1

Hash Join (cost=70,936.16..247,556.71 rows=1,717,566 width=121) (actual time=1,967.524..6,680.234 rows=1,721,795 loops=1)

  • Hash Cond: (bidm.billingitemid = bi.id)
  • Buffers: shared hit=2 read=58899, temp read=43240 written=43178
  • -> Seq Scan on billingitem2denormalizedmetadata bidm (cost=0.00..64653.59 rows=2173859 width=108) (actual time=0.649..1476.370 rows=2173 (...)
  • Filter: (userid IS NOT NULL)
  • Buffers: shared read=42915
77. 966.797 1,966.207 ↓ 1.0 1,721,795 1

Hash (cost=37,724.59..37,724.59 rows=1,717,566 width=29) (actual time=1,966.207..1,966.207 rows=1,721,795 loops=1)

  • Buckets: 65536 Batches: 32 Memory Usage: 3783kB
  • Buffers: shared hit=2 read=15984, temp written=10079
78. 999.410 999.410 ↓ 1.0 1,721,795 1

Seq Scan on billingitem2 bi (cost=0.00..37,724.59 rows=1,717,566 width=29) (actual time=0.602..999.410 rows=1,721,795 loops=1)

  • Filter: isbillable
  • Rows Removed by Filter: 452064
  • Buffers: shared hit=2 read=15984
79. 1,261.652 3,320.055 ↓ 3.1 2,476,442 1

Materialize (cost=114,367.18..118,346.75 rows=795,914 width=40) (actual time=1,502.502..3,320.055 rows=2,476,442 loops=1)

  • Buffers: shared hit=2348 read=4285, temp read=4475 written=4475
80. 1,620.611 2,058.403 ↑ 1.0 795,820 1

Sort (cost=114,367.18..116,356.96 rows=795,914 width=40) (actual time=1,502.497..2,058.403 rows=795,820 loops=1)

  • Sort Key: pbr_1.projectid, pbr_1.billingrateid
  • Sort Method: external merge Disk: 35792kB
  • Buffers: shared hit=2348 read=4285, temp read=4475 written=4475
81. 437.792 437.792 ↑ 1.0 795,914 1

Seq Scan on projectbillingrate pbr_1 (cost=0.00..14,592.14 rows=795,914 width=40) (actual time=0.012..437.792 rows=795,914 loops=1)

  • Buffers: shared hit=2348 read=4285
82. 1.964 3.907 ↑ 1.0 3,989 1

Hash (cost=174.89..174.89 rows=3,989 width=4) (actual time=3.907..3.907 rows=3,989 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 173kB
  • Buffers: shared hit=135
83. 1.943 1.943 ↑ 1.0 3,989 1

Seq Scan on project pj_4 (cost=0.00..174.89 rows=3,989 width=4) (actual time=0.004..1.943 rows=3,989 loops=1)

  • Buffers: shared hit=135
84. 57.537 105.946 ↑ 1.0 103,253 1

Hash (cost=2,097.53..2,097.53 rows=103,253 width=28) (actual time=105.946..105.946 rows=103,253 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 3559kB
  • Buffers: shared hit=1065, temp written=300
85. 48.409 48.409 ↑ 1.0 103,253 1

Seq Scan on timesheet t (cost=0.00..2,097.53 rows=103,253 width=28) (actual time=0.006..48.409 rows=103,253 loops=1)

  • Buffers: shared hit=1065
86. 1.649 3.037 ↑ 1.0 2,991 1

Hash (cost=59.91..59.91 rows=2,991 width=46) (actual time=3.037..3.037 rows=2,991 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 262kB
  • Buffers: shared hit=2 read=28
87. 1.388 1.388 ↑ 1.0 2,991 1

Seq Scan on billingrate br_1 (cost=0.00..59.91 rows=2,991 width=46) (actual time=0.015..1.388 rows=2,991 loops=1)

  • Buffers: shared hit=2 read=28
88. 0.002 0.003 ↓ 0.0 0 1

Hash (cost=20.70..20.70 rows=1,070 width=32) (actual time=0.003..0.003 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.007 3.736 ↑ 1.0 2 1

Hash (cost=334.57..334.57 rows=2 width=12) (actual time=3.736..3.736 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=146 read=1
91. 0.004 3.729 ↑ 1.0 2 1

Nested Loop Semi Join (cost=0.56..334.57 rows=2 width=12) (actual time=1.601..3.729 rows=2 loops=1)

  • Buffers: shared hit=146 read=1
92. 0.011 3.707 ↑ 10.0 2 1

Nested Loop (cost=0.28..324.98 rows=20 width=8) (actual time=1.583..3.707 rows=2 loops=1)

  • Buffers: shared hit=140 read=1
93. 2.266 2.266 ↑ 10.0 2 1

Seq Scan on project project2 (cost=0.00..194.84 rows=20 width=4) (actual time=0.150..2.266 rows=2 loops=1)

  • Filter: (upper((code)::text) = 'INCNYJAK18001'::text)
  • Rows Removed by Filter: 3987
  • Buffers: shared hit=135
94. 1.430 1.430 ↑ 1.0 1 2

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

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

Index Scan using project_pkey on project pj_2 (cost=0.28..0.48 rows=1 width=4) (actual time=0.009..0.009 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", (...)
  • Buffers: shared hit=6
96. 0.036 32.226 ↑ 67.8 44 1

Hash (cost=657.86..657.86 rows=2,983 width=4) (actual time=32.226..32.226 rows=44 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 34kB
  • Buffers: shared hit=144 read=56
97. 4.289 32.190 ↑ 67.8 44 1

Hash Left Join (cost=468.19..657.86 rows=2,983 width=4) (actual time=19.135..32.190 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: 8083
  • Buffers: shared hit=144 read=56
98. 6.553 27.450 ↓ 1.4 8,127 1

Hash Right Join (cost=467.15..633.94 rows=5,886 width=20) (actual time=18.149..27.450 rows=8,127 loops=1)

  • Hash Cond: (projectsharingassignment5.projectid = project4.id)
  • Buffers: shared hit=144 read=55
99. 3.359 3.359 ↑ 1.0 5,886 1

Seq Scan on projectsharingassignment projectsharingassignment5 (cost=0.00..85.86 rows=5,886 width=8) (actual time=0.583..3.359 rows=5,886 loops=1)

  • Buffers: shared hit=2 read=25
100. 2.078 17.538 ↑ 1.0 3,989 1

Hash (cost=417.28..417.28 rows=3,989 width=16) (actual time=17.538..17.538 rows=3,989 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 206kB
  • Buffers: shared hit=142 read=30
101. 3.594 15.460 ↑ 1.0 3,989 1

Hash Left Join (cost=132.90..417.28 rows=3,989 width=16) (actual time=5.551..15.460 rows=3,989 loops=1)

  • Hash Cond: (projectclient6.clientid = clients7.id)
  • Buffers: shared hit=142 read=30
102. 4.324 11.575 ↑ 1.0 3,989 1

Hash Left Join (cost=123.30..352.84 rows=3,989 width=12) (actual time=5.240..11.575 rows=3,989 loops=1)

  • Hash Cond: (project4.id = projectclient6.projectid)
  • Buffers: shared hit=139 read=30
103. 2.044 2.044 ↑ 1.0 3,989 1

Seq Scan on project project4 (cost=0.00..174.89 rows=3,989 width=8) (actual time=0.004..2.044 rows=3,989 loops=1)

  • Buffers: shared hit=135
104. 1.909 5.207 ↑ 1.0 3,969 1

Hash (cost=73.69..73.69 rows=3,969 width=8) (actual time=5.207..5.207 rows=3,969 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 188kB
  • Buffers: shared hit=4 read=30
105. 3.298 3.298 ↑ 1.0 3,969 1

Seq Scan on projectclient projectclient6 (cost=0.00..73.69 rows=3,969 width=8) (actual time=0.003..3.298 rows=3,969 loops=1)

  • Buffers: shared hit=4 read=30
106. 0.139 0.291 ↑ 1.0 293 1

Hash (cost=5.93..5.93 rows=293 width=8) (actual time=0.291..0.291 rows=293 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
  • Buffers: shared hit=3
107. 0.152 0.152 ↑ 1.0 293 1

Seq Scan on clients clients7 (cost=0.00..5.93 rows=293 width=8) (actual time=0.005..0.152 rows=293 loops=1)

  • Buffers: shared hit=3
108. 0.006 0.451 ↑ 1.0 2 1

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

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

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

  • Buffers: shared read=1
110.          

Initplan (forSort)

111. 0.020 0.020 ↑ 1.0 1 1

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

  • Buffers: shared read=1
112. 0.169 192,857.352 ↑ 1,691.5 163 1

Hash Left Join (cost=1,429.30..15,656.24 rows=275,716 width=267) (actual time=192,853.159..192,857.352 rows=163 loops=1)

  • Hash Cond: (imeexpensecostbilling2_facts12.currencyid5 = currencyinfo13.id)
  • Buffers: shared hit=14050191 read=470696, temp read=273686 written=272797
113. 0.201 192,857.163 ↑ 1,691.5 163 1

Hash Left Join (cost=1,428.14..14,524.65 rows=275,716 width=153) (actual time=192,853.129..192,857.163 rows=163 loops=1)

  • Hash Cond: (imeexpensecostbilling2_facts12.projectid3 = pj.id)
  • Buffers: shared hit=14050191 read=470695, temp read=273686 written=272797
114. 0.202 192,847.035 ↑ 1,691.5 163 1

Hash Left Join (cost=1,139.15..10,444.56 rows=275,716 width=100) (actual time=192,843.171..192,847.035 rows=163 loops=1)

  • Hash Cond: (imeexpensecostbilling2_facts12.taskid4 = tk.id)
  • Buffers: shared hit=14050190 read=470558, temp read=273686 written=272797
115. 192,798.107 192,798.107 ↑ 1,691.5 163 1

CTE Scan on eexpensecostbilling2_facts0cte imeexpensecostbilling2_facts12 (cost=0.00..5,514.32 rows=275,716 width=64) (actual time=192,794.383..192,798.107 rows=163 loops=1)

  • Buffers: shared hit=14050190 read=470163, temp read=273686 written=272797
116. 10.535 48.726 ↑ 1.0 19,542 1

Hash (cost=894.87..894.87 rows=19,542 width=40) (actual time=48.726..48.726 rows=19,542 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1656kB
  • Buffers: shared read=395
117. 19.071 38.191 ↑ 1.0 19,542 1

Hash Left Join (cost=3.53..894.87 rows=19,542 width=40) (actual time=0.100..38.191 rows=19,542 loops=1)

  • Hash Cond: (tk.estimatedcostcurrencyid = exchangerate.fixedcurrencyid)
  • Buffers: shared read=395
118. 19.047 19.047 ↑ 1.0 19,542 1

Seq Scan on task tk (cost=0.00..588.42 rows=19,542 width=44) (actual time=0.018..19.047 rows=19,542 loops=1)

  • Buffers: shared read=393
119. 0.006 0.073 ↑ 1.0 7 1

Hash (cost=3.45..3.45 rows=7 width=4) (actual time=0.073..0.073 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared read=2
120. 0.067 0.067 ↑ 1.0 7 1

Seq Scan on exchangerate (cost=0.00..3.45 rows=7 width=4) (actual time=0.050..0.067 rows=7 loops=1)

  • Filter: ((variablecurrencyid = $20) AND (('now'::cstring)::date >= effectivedate) AND (('now'::cstring)::date <= enddate))
  • Rows Removed by Filter: 82
  • Buffers: shared read=2
121. 2.399 9.927 ↑ 1.0 3,989 1

Hash (cost=239.13..239.13 rows=3,989 width=57) (actual time=9.927..9.927 rows=3,989 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 381kB
  • Buffers: shared hit=1 read=137
122. 4.115 7.528 ↑ 1.0 3,989 1

Hash Left Join (cost=9.59..239.13 rows=3,989 width=57) (actual time=0.855..7.528 rows=3,989 loops=1)

  • Hash Cond: (pj.clientid = cl.id)
  • Buffers: shared hit=1 read=137
123. 3.118 3.118 ↑ 1.0 3,989 1

Seq Scan on project pj (cost=0.00..174.89 rows=3,989 width=48) (actual time=0.548..3.118 rows=3,989 loops=1)

  • Buffers: shared hit=1 read=134
124. 0.151 0.295 ↑ 1.0 293 1

Hash (cost=5.93..5.93 rows=293 width=17) (actual time=0.295..0.295 rows=293 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 23kB
  • Buffers: shared read=3
125. 0.144 0.144 ↑ 1.0 293 1

Seq Scan on clients cl (cost=0.00..5.93 rows=293 width=17) (actual time=0.011..0.144 rows=293 loops=1)

  • Buffers: shared read=3
126. 0.009 0.020 ↑ 1.0 7 1

Hash (cost=1.07..1.07 rows=7 width=122) (actual time=0.020..0.020 rows=7 loops=1)

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

Seq Scan on currencyinfo currencyinfo13 (cost=0.00..1.07 rows=7 width=122) (actual time=0.007..0.011 rows=7 loops=1)

  • Buffers: shared read=1