explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Xq9S : optimized, working

Settings
# exclusive inclusive rows x rows loops node
1. 145.253 6,243.360 ↑ 6.2 4,346 1

Sort (cost=576,227.89..576,295.39 rows=27,000 width=408) (actual time=6,233.496..6,243.360 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=175058, temp read=1059 written=1051
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. 27.031 6,098.100 ↑ 6.2 4,346 1

Hash Left Join (cost=77,393.83..569,255.59 rows=27,000 width=408) (actual time=5,298.259..6,098.100 rows=4,346 loops=1)

  • Hash Cond: ("*SELECT* 1".billingrateid = billingrate11.id)
  • Buffers: shared hit=175058, temp read=1059 written=1051
5. 21.950 6,068.885 ↑ 6.2 4,346 1

Hash Left Join (cost=77,381.35..568,871.86 rows=27,000 width=399) (actual time=5,296.046..6,068.885 rows=4,346 loops=1)

  • Hash Cond: (pj.projectleaderapproverid = userinfo10.id)
  • Buffers: shared hit=175054, temp read=1059 written=1051
6. 39.448 6,040.807 ↑ 6.2 4,346 1

Nested Loop Left Join (cost=77,340.99..568,460.92 rows=27,000 width=389) (actual time=5,289.885..6,040.807 rows=4,346 loops=1)

  • Buffers: shared hit=175036, temp read=1059 written=1051
7. 41.929 5,962.245 ↑ 6.2 4,346 1

Nested Loop Left Join (cost=77,340.69..555,382.02 rows=27,000 width=330) (actual time=5,289.866..5,962.245 rows=4,346 loops=1)

  • Buffers: shared hit=162477, temp read=1059 written=1051
8. 22.003 5,837.742 ↑ 6.2 4,346 1

Hash Join (cost=77,340.14..325,379.30 rows=27,000 width=286) (actual time=5,289.837..5,837.742 rows=4,346 loops=1)

  • Hash Cond: ("*SELECT* 1".userid = login.userid)
  • Buffers: shared hit=141964, temp read=1059 written=1051
9. 22.088 5,794.919 ↑ 6.2 4,346 1

Hash Join (cost=77,241.95..324,909.86 rows=27,000 width=268) (actual time=5,268.998..5,794.919 rows=4,346 loops=1)

  • Hash Cond: ("*SELECT* 1".userid = ui.id)
  • Buffers: shared hit=141939, temp read=1059 written=1051
10. 20.654 5,757.127 ↑ 6.2 4,346 1

Hash Left Join (cost=77,201.58..324,498.24 rows=27,000 width=229) (actual time=5,253.266..5,757.127 rows=4,346 loops=1)

  • Hash Cond: (tk.estimatedcostcurrencyid = exchangerate.fixedcurrencyid)
  • Buffers: shared hit=141921, temp read=1059 written=1051
11. 22.850 5,736.377 ↑ 6.2 4,346 1

Hash Left Join (cost=77,199.15..323,922.33 rows=27,000 width=233) (actual time=5,253.155..5,736.377 rows=4,346 loops=1)

  • Hash Cond: (COALESCE(clients17.id, pj.clientid) = cl.id)
  • Buffers: shared hit=141919, temp read=1059 written=1051
12. 20.213 5,713.132 ↑ 6.2 4,346 1

Hash Left Join (cost=77,196.66..323,729.49 rows=27,000 width=225) (actual time=5,252.742..5,713.132 rows=4,346 loops=1)

  • Hash Cond: (("substring"(timeentrymetadata16.uri, '^urn:replicon-tenant:d39c603ba5ee46da9a42069eec872cf2:client:(.*)$'::text))::integer = clients17.id)
  • Buffers: shared hit=141918, temp read=1059 written=1051
13. 30.221 5,692.617 ↑ 6.2 4,346 1

Nested Loop Left Join (cost=77,194.18..323,435.41 rows=27,000 width=279) (actual time=5,252.421..5,692.617 rows=4,346 loops=1)

  • Buffers: shared hit=141917, temp read=1059 written=1051
14. 55.757 5,566.784 ↑ 6.2 4,346 1

Hash Left Join (cost=77,193.62..93,432.69 rows=27,000 width=221) (actual time=5,252.376..5,566.784 rows=4,346 loops=1)

  • Hash Cond: (task15.id = tk.id)
  • Buffers: shared hit=124488, temp read=1059 written=1051
15. 22.941 4,970.080 ↑ 6.2 4,346 1

Hash Left Join (cost=72,805.12..86,505.93 rows=27,000 width=176) (actual time=4,709.853..4,970.080 rows=4,346 loops=1)

  • Hash Cond: (COALESCE(project13.id, task15.projectid) = pj.id)
  • Buffers: shared hit=122897, temp read=371 written=369
16. 406.874 4,927.871 ↑ 6.2 4,346 1

Hash Join (cost=72,640.75..85,970.31 rows=27,000 width=100) (actual time=4,690.526..4,927.871 rows=4,346 loops=1)

  • Hash Cond: (tslist.timesheetid = ts.id)
  • Buffers: shared hit=122795, temp read=371 written=369
17. 577.547 577.547 ↓ 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.020..577.547 rows=134,993 loops=1)

  • Filter: (timesheetstatus = ANY ('{0,1,2,3,4}'::integer[]))
  • Buffers: shared hit=8333
18. 21.976 3,943.450 ↑ 6.2 4,346 1

Hash (cost=71,852.38..71,852.38 rows=27,069 width=112) (actual time=3,943.450..3,943.450 rows=4,346 loops=1)

  • Buckets: 32768 Batches: 2 Memory Usage: 554kB
  • Buffers: shared hit=114462, temp written=37
19. 52.285 3,921.474 ↑ 6.2 4,346 1

Nested Loop (cost=40.32..71,852.38 rows=27,069 width=112) (actual time=2.136..3,921.474 rows=4,346 loops=1)

  • Buffers: shared hit=114462
20. 1,184.090 3,582.353 ↓ 2.7 4,346 1

Nested Loop Left Join (cost=39.90..61,529.53 rows=1,626 width=84) (actual time=2.069..3,582.353 rows=4,346 loops=1)

  • Buffers: shared hit=91857
21. 43.667 2,320.035 ↓ 2.7 4,346 1

Nested Loop Left Join (cost=39.60..60,566.11 rows=1,626 width=134) (actual time=1.703..2,320.035 rows=4,346 loops=1)

  • Buffers: shared hit=79298
22. 151.308 2,180.756 ↓ 2.7 4,346 1

Nested Loop Left Join (cost=39.04..46,714.84 rows=1,626 width=76) (actual time=1.666..2,180.756 rows=4,346 loops=1)

  • Buffers: shared hit=57647
23. 38.570 2,012.064 ↓ 2.7 4,346 1

Nested Loop Left Join (cost=38.76..46,200.61 rows=1,626 width=130) (actual time=1.346..2,012.064 rows=4,346 loops=1)

  • Buffers: shared hit=56826
24. 19.068 1,873.536 ↓ 2.7 4,346 1

Append (cost=38.20..32,349.34 rows=1,626 width=72) (actual time=1.316..1,873.536 rows=4,346 loops=1)

  • Buffers: shared hit=38985
25. 41.553 1,854.447 ↓ 2.7 4,346 1

Subquery Scan on *SELECT* 1 (cost=38.20..32,347.29 rows=1,625 width=72) (actual time=1.312..1,854.447 rows=4,346 loops=1)

  • Buffers: shared hit=38984
26. 1,530.558 1,812.894 ↓ 2.7 4,346 1

Nested Loop Left Join (cost=38.20..32,331.04 rows=1,625 width=237) (actual time=1.306..1,812.894 rows=4,346 loops=1)

  • Buffers: shared hit=38984
27. 48.152 195.416 ↓ 2.7 4,346 1

Nested Loop Left Join (cost=37.64..18,407.10 rows=1,625 width=179) (actual time=1.267..195.416 rows=4,346 loops=1)

  • Buffers: shared hit=21555
28. 21.550 38.614 ↓ 2.7 4,346 1

Nested Loop (cost=37.08..4,564.41 rows=1,625 width=121) (actual time=1.211..38.614 rows=4,346 loops=1)

  • Buffers: shared hit=84
29. 0.027 0.040 ↑ 1.0 1 1

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

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

Seq Scan on systeminformation si (cost=0.00..1.01 rows=1 width=33) (actual time=0.004..0.007 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.004..0.006 rows=1 loops=1)

  • Buffers: shared hit=1
32. 15.901 17.024 ↓ 2.7 4,346 1

Bitmap Heap Scan on timeentry te (cost=37.08..4,546.13 rows=1,625 width=56) (actual time=1.167..17.024 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=82
33. 1.123 1.123 ↓ 2.7 4,346 1

Bitmap Index Scan on ixtemp_teentrydate (cost=0.00..36.68 rows=1,625 width=0) (actual time=1.123..1.123 rows=4,346 loops=1)

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

Index Scan using istemp_temdkey2 on timeentrymetadata tembillingrate (cost=0.56..8.51 rows=1 width=74) (actual time=0.023..0.025 rows=1 loops=4,346)

  • Index Cond: ((timeentryid = te.id) AND (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:BILLING-RATE'::text))
  • Buffers: shared hit=21471
35. 86.920 86.920 ↓ 0.0 0 4,346

Index Scan using istemp_temdkey2 on timeentrymetadata temdbreaktype (cost=0.56..8.51 rows=1 width=74) (actual time=0.020..0.020 rows=0 loops=4,346)

  • Index Cond: ((timeentryid = te.id) AND (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:BREAK-TYPE'::text))
  • Buffers: shared hit=17429
36. 0.005 0.021 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=0.00..2.05 rows=1 width=72) (actual time=0.021..0.021 rows=0 loops=1)

  • Buffers: shared hit=1
37. 0.003 0.016 ↓ 0.0 0 1

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

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

Seq Scan on timeoffentries toe (cost=0.00..1.01 rows=1 width=24) (actual time=0.013..0.013 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
39. 0.000 0.000 ↓ 0.0 0

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

40. 99.958 99.958 ↓ 0.0 0 4,346

Index Scan using istemp_temdkey2 on timeentrymetadata timeentrymetadata12 (cost=0.56..8.51 rows=1 width=74) (actual time=0.022..0.023 rows=0 loops=4,346)

  • Index Cond: (("*SELECT* 1".timeentryid = timeentryid) AND (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:PROJECT'::text))
  • Buffers: shared hit=17841
41. 17.384 17.384 ↓ 0.0 0 4,346

Index Only Scan using project_pkey on project project13 (cost=0.29..0.31 rows=1 width=4) (actual time=0.003..0.004 rows=0 loops=4,346)

  • Index Cond: (id = ("substring"(timeentrymetadata12.uri, '^urn:replicon-tenant:d39c603ba5ee46da9a42069eec872cf2:project:(.*)$'::text))::integer)
  • Heap Fetches: 0
  • Buffers: shared hit=821
42. 95.612 95.612 ↑ 1.0 1 4,346

Index Scan using istemp_temdkey2 on timeentrymetadata timeentrymetadata14 (cost=0.56..8.51 rows=1 width=74) (actual time=0.019..0.022 rows=1 loops=4,346)

  • Index Cond: (("*SELECT* 1".timeentryid = timeentryid) AND (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:TASK'::text))
  • Buffers: shared hit=21651
43. 78.228 78.228 ↑ 1.0 1 4,346

Index Scan using task_pkey on task task15 (cost=0.30..0.58 rows=1 width=8) (actual time=0.016..0.018 rows=1 loops=4,346)

  • Index Cond: (("substring"(timeentrymetadata14.uri, '^urn:replicon-tenant:d39c603ba5ee46da9a42069eec872cf2:task:(.*)$'::text))::integer = id)
  • Buffers: shared hit=12559
44. 286.836 286.836 ↑ 17.0 1 4,346

Index Scan using ixtsuseridstartdateenddate on timesheet ts (cost=0.42..6.18 rows=17 width=28) (actual time=0.064..0.066 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. 8.818 19.268 ↑ 1.0 2,772 1

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

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

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

  • Buffers: shared hit=102
47. 260.271 540.947 ↑ 1.0 86,689 1

Hash (cost=2,457.89..2,457.89 rows=86,689 width=49) (actual time=540.947..540.947 rows=86,689 loops=1)

  • Buckets: 65536 Batches: 4 Memory Usage: 2391kB
  • Buffers: shared hit=1591, temp written=604
48. 280.676 280.676 ↑ 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..280.676 rows=86,689 loops=1)

  • Buffers: shared hit=1591
49. 95.612 95.612 ↓ 0.0 0 4,346

Index Scan using istemp_temdkey2 on timeentrymetadata timeentrymetadata16 (cost=0.56..8.51 rows=1 width=74) (actual time=0.022..0.022 rows=0 loops=4,346)

  • Index Cond: (("*SELECT* 1".timeentryid = timeentryid) AND (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:CLIENT'::text))
  • Buffers: shared hit=17429
50. 0.156 0.302 ↑ 1.0 66 1

Hash (cost=1.66..1.66 rows=66 width=4) (actual time=0.302..0.302 rows=66 loops=1)

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

Seq Scan on clients clients17 (cost=0.00..1.66 rows=66 width=4) (actual time=0.006..0.146 rows=66 loops=1)

  • Buffers: shared hit=1
52. 0.246 0.395 ↑ 1.0 66 1

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

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

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

  • Buffers: shared hit=1
54. 0.020 0.096 ↑ 1.0 7 1

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

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

Seq Scan on exchangerate (cost=0.00..2.35 rows=7 width=4) (actual time=0.035..0.076 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
56. 3.875 15.704 ↑ 1.0 994 1

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

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

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

  • Buffers: shared hit=18
58. 2.737 20.820 ↑ 1.0 994 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 69kB
  • Buffers: shared hit=25
59. 4.645 18.083 ↑ 1.0 994 1

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

  • Hash Cond: (du.departmentid = dep.id)
  • Buffers: shared hit=25
60. 5.230 13.106 ↑ 1.0 994 1

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

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

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

  • Buffers: shared hit=18
62. 2.963 5.713 ↑ 1.0 994 1

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

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

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

  • Buffers: shared hit=6
64. 0.151 0.332 ↑ 1.0 50 1

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

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

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

  • Buffers: shared hit=1
66. 82.574 82.574 ↑ 1.0 1 4,346

Index Scan using istemp_temdkey2 on timeentrymetadata timeentrymetadata9 (cost=0.56..8.51 rows=1 width=60) (actual time=0.018..0.019 rows=1 loops=4,346)

  • Index Cond: (("*SELECT* 1".timeentryid = timeentryid) AND (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:COMMENTS'::text))
  • Buffers: shared hit=20513
67. 39.114 39.114 ↑ 1.0 1 4,346

Index Scan using taskdenormalizedhierarchy_pkey on taskdenormalizedhierarchy tdh (cost=0.29..0.47 rows=1 width=63) (actual time=0.007..0.009 rows=1 loops=4,346)

  • Index Cond: (tk.id = taskid)
  • Buffers: shared hit=12559
68. 2.630 6.128 ↑ 1.0 994 1

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

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

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

  • Buffers: shared hit=18
70. 1.101 2.184 ↑ 1.0 377 1

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

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

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

  • Buffers: shared hit=4