explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AhrL : without index key + timeentryuri

Settings
# exclusive inclusive rows x rows loops node
1. 1,107.716 415,262.984 ↑ 1.0 1,465,153 1

Append (cost=1,550,825.53..3,323,588.04 rows=1,480,242 width=158) (actual time=387,482.902..415,262.984 rows=1,465,153 loops=1)

2. 4,293.912 414,155.196 ↑ 1.0 1,465,152 1

Nested Loop Left Join (cost=1,550,825.53..3,308,637.45 rows=1,480,222 width=280) (actual time=387,482.901..414,155.196 rows=1,465,152 loops=1)

  • Join Filter: (te.id = te_isbillable.timeentryid)
3. 2,314.545 402,535.524 ↑ 1.0 1,465,152 1

Hash Left Join (cost=1,550,825.53..2,648,354.13 rows=1,480,222 width=279) (actual time=381,264.856..402,535.524 rows=1,465,152 loops=1)

  • Hash Cond: (("substring"(te_client.uri, concat('^urn:replicon-tenant:', si.tenantslug, ':client:(.*)$')))::integer = client.id)
4. 1,198.992 400,220.912 ↑ 1.0 1,465,152 1

Nested Loop Left Join (cost=1,550,823.05..2,630,514.98 rows=1,480,222 width=332) (actual time=381,264.752..400,220.912 rows=1,465,152 loops=1)

  • Join Filter: (te.id = te_client.timeentryid)
5. 2,247.171 391,696.160 ↑ 1.0 1,465,152 1

Hash Left Join (cost=1,550,823.05..2,384,693.82 rows=1,480,222 width=275) (actual time=375,000.696..391,696.160 rows=1,465,152 loops=1)

  • Hash Cond: (("substring"(te_timeofftype.uri, concat('urn:replicon-tenant:', si.tenantslug, ':time-off-type:(.*)$')))::integer = toc.id)
6. 1,148.673 389,448.961 ↑ 1.0 1,465,152 1

Nested Loop Left Join (cost=1,550,821.85..2,371,074.57 rows=1,480,222 width=328) (actual time=375,000.633..389,448.961 rows=1,465,152 loops=1)

  • Join Filter: (te.id = te_timeofftype.timeentryid)
7. 2,629.218 380,974.528 ↑ 1.0 1,465,152 1

Hash Left Join (cost=1,550,821.85..2,125,253.42 rows=1,480,222 width=271) (actual time=368,778.441..380,974.528 rows=1,465,152 loops=1)

  • Hash Cond: (("substring"(te_breaktype.uri, concat('^urn:replicon-tenant:', si.tenantslug, ':break-type:(.*)$')))::uuid = bt.id)
8. 1,231.356 378,345.299 ↑ 1.0 1,465,152 1

Nested Loop Left Join (cost=1,550,820.80..2,112,152.41 rows=1,480,222 width=312) (actual time=368,778.393..378,345.299 rows=1,465,152 loops=1)

  • Join Filter: (te.id = te_breaktype.timeentryid)
9. 2,432.246 369,788.183 ↑ 1.0 1,465,152 1

Hash Right Join (cost=1,550,820.80..1,866,331.25 rows=1,480,222 width=255) (actual time=362,527.485..369,788.183 rows=1,465,152 loops=1)

  • Hash Cond: (te_billingrate.timeentryid = te.id)
10. 6,798.699 6,798.699 ↑ 1.0 1,296,802 1

Seq Scan on timeentrymetadata te_billingrate (cost=0.00..223,617.83 rows=1,310,172 width=73) (actual time=1,970.055..6,798.699 rows=1,296,802 loops=1)

  • Filter: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:BILLING-RATE'::text)
  • Rows Removed by Filter: 5088053
11. 1,466.509 360,557.238 ↑ 1.0 1,465,152 1

Hash (cost=1,491,843.03..1,491,843.03 rows=1,480,222 width=198) (actual time=360,557.238..360,557.238 rows=1,465,152 loops=1)

  • Buckets: 32768 Batches: 128 Memory Usage: 2404kB
12. 158,794.902 359,090.729 ↑ 1.0 1,465,152 1

Hash Left Join (cost=1,042,951.80..1,491,843.03 rows=1,480,222 width=198) (actual time=190,713.663..359,090.729 rows=1,465,152 loops=1)

  • Hash Cond: (("substring"(te_task.uri, concat('^urn:replicon-tenant:', si.tenantslug, ':task:(.*)$')))::integer = task.id)
13. 2,703.792 200,211.346 ↑ 1.0 1,465,152 1

Hash Right Join (cost=1,039,071.30..1,358,681.36 rows=1,480,222 width=251) (actual time=190,628.138..200,211.346 rows=1,465,152 loops=1)

  • Hash Cond: (te_task.timeentryid = te.id)
14. 6,880.487 6,880.487 ↓ 1.0 1,426,221 1

Seq Scan on timeentrymetadata te_task (cost=0.00..223,617.83 rows=1,414,671 width=73) (actual time=0.050..6,880.487 rows=1,426,221 loops=1)

  • Filter: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:TASK'::text)
  • Rows Removed by Filter: 4958634
15. 1,450.273 190,627.067 ↑ 1.0 1,465,152 1

Hash (cost=980,093.52..980,093.52 rows=1,480,222 width=194) (actual time=190,627.067..190,627.067 rows=1,465,152 loops=1)

  • Buckets: 32768 Batches: 128 Memory Usage: 2361kB
16. 155,722.448 189,176.794 ↑ 1.0 1,465,152 1

Hash Left Join (cost=593,861.34..980,093.52 rows=1,480,222 width=194) (actual time=17,191.626..189,176.794 rows=1,465,152 loops=1)

  • Hash Cond: (("substring"(te_project.uri, concat('^urn:replicon-tenant:', si.tenantslug, ':project:(.*)$')))::integer = proj.id)
17. 2,587.723 33,451.288 ↑ 1.0 1,465,152 1

Hash Left Join (cost=593,696.97..952,174.99 rows=1,480,222 width=243) (actual time=17,188.402..33,451.288 rows=1,465,152 loops=1)

  • Hash Cond: (("substring"(te_activity.uri, concat('^urn:replicon-tenant:', si.tenantslug, ':activity:(.*)$')))::integer = act.id)
18. 1,273.387 30,863.533 ↑ 1.0 1,465,152 1

Hash Left Join (cost=593,695.85..938,851.88 rows=1,480,222 width=296) (actual time=17,188.336..30,863.533 rows=1,465,152 loops=1)

  • Hash Cond: (te.id = te_activity.timeentryid)
19. 1,513.875 22,630.356 ↑ 1.0 1,465,152 1

Hash Join (cost=370,078.02..709,683.20 rows=1,480,222 width=239) (actual time=10,228.530..22,630.356 rows=1,465,152 loops=1)

  • Hash Cond: (te.userid = ui.id)
20. 1,219.777 21,115.295 ↑ 1.0 1,465,152 1

Nested Loop (cost=370,037.65..689,289.78 rows=1,480,222 width=225) (actual time=10,227.335..21,115.295 rows=1,465,152 loops=1)

21. 0.005 0.020 ↑ 1.0 1 1

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

22. 0.007 0.007 ↑ 1.0 1 1

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

23. 0.008 0.008 ↑ 1.0 1 1

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

24. 2,591.573 19,895.498 ↑ 1.0 1,465,152 1

Hash Right Join (cost=370,037.65..674,485.53 rows=1,480,222 width=160) (actual time=10,227.316..19,895.498 rows=1,465,152 loops=1)

  • Hash Cond: (te_project.timeentryid = te.id)
25. 7,076.784 7,076.784 ↑ 1.0 1,456,325 1

Seq Scan on timeentrymetadata te_project (cost=0.00..223,617.83 rows=1,480,222 width=73) (actual time=0.052..7,076.784 rows=1,456,325 loops=1)

  • Filter: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:PROJECT'::text)
  • Rows Removed by Filter: 4928530
26. 849.107 10,227.141 ↑ 1.0 1,465,152 1

Hash (cost=328,830.25..328,830.25 rows=1,465,152 width=103) (actual time=10,227.141..10,227.141 rows=1,465,152 loops=1)

  • Buckets: 32768 Batches: 64 Memory Usage: 2350kB
27. 1,425.361 9,378.034 ↑ 1.0 1,465,152 1

Hash Right Join (cost=64,575.80..328,830.25 rows=1,465,152 width=103) (actual time=3,382.524..9,378.034 rows=1,465,152 loops=1)

  • Hash Cond: (te_comment.timeentryid = te.id)
28. 6,535.213 6,535.213 ↓ 1.0 727,165 1

Seq Scan on timeentrymetadata te_comment (cost=0.00..223,617.83 rows=706,591 width=59) (actual time=1,964.870..6,535.213 rows=727,165 loops=1)

  • Filter: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:COMMENTS'::text)
  • Rows Removed by Filter: 5657690
29. 723.387 1,417.460 ↑ 1.0 1,465,152 1

Hash (cost=30,522.40..30,522.40 rows=1,465,152 width=60) (actual time=1,417.460..1,417.460 rows=1,465,152 loops=1)

  • Buckets: 65536 Batches: 64 Memory Usage: 2110kB
30. 694.073 694.073 ↑ 1.0 1,465,152 1

Seq Scan on timeentry te (cost=0.00..30,522.40 rows=1,465,152 width=60) (actual time=0.011..694.073 rows=1,465,152 loops=1)

  • Filter: ((timeallocationtype <> 2) OR (timeallocationtype IS NULL))
31. 0.499 1.186 ↑ 1.0 994 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 59kB
32. 0.687 0.687 ↑ 1.0 994 1

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

33. 0.001 6,959.790 ↓ 0.0 0 1

Hash (cost=223,617.83..223,617.83 rows=1 width=73) (actual time=6,959.790..6,959.790 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
34. 6,959.789 6,959.789 ↓ 0.0 0 1

Seq Scan on timeentrymetadata te_activity (cost=0.00..223,617.83 rows=1 width=73) (actual time=6,959.789..6,959.789 rows=0 loops=1)

  • Filter: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:ACTIVITY'::text)
  • Rows Removed by Filter: 6384855
35. 0.005 0.032 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=4) (actual time=0.032..0.032 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
36. 0.027 0.027 ↑ 1.0 5 1

Seq Scan on activities act (cost=0.00..1.05 rows=5 width=4) (actual time=0.023..0.027 rows=5 loops=1)

37. 1.315 3.058 ↑ 1.0 2,772 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 141kB
38. 1.743 1.743 ↑ 1.0 2,772 1

Seq Scan on project proj (cost=0.00..129.72 rows=2,772 width=8) (actual time=0.015..1.743 rows=2,772 loops=1)

39. 41.713 84.481 ↑ 1.0 86,689 1

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

  • Buckets: 131072 Batches: 2 Memory Usage: 2550kB
40. 42.768 42.768 ↑ 1.0 86,689 1

Seq Scan on task (cost=0.00..2,457.89 rows=86,689 width=4) (actual time=0.011..42.768 rows=86,689 loops=1)

41. 1,074.861 7,325.760 ↓ 0.0 0 1,465,152

Materialize (cost=0.00..223,617.83 rows=1 width=73) (actual time=0.005..0.005 rows=0 loops=1,465,152)

42. 6,250.899 6,250.899 ↓ 0.0 0 1

Seq Scan on timeentrymetadata te_breaktype (cost=0.00..223,617.83 rows=1 width=73) (actual time=6,250.899..6,250.899 rows=0 loops=1)

  • Filter: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:BREAK-TYPE'::text)
  • Rows Removed by Filter: 6384855
43. 0.008 0.011 ↑ 1.0 2 1

Hash (cost=1.02..1.02 rows=2 width=16) (actual time=0.011..0.011 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
44. 0.003 0.003 ↑ 1.0 2 1

Seq Scan on breaktype bt (cost=0.00..1.02 rows=2 width=16) (actual time=0.003..0.003 rows=2 loops=1)

45. 1,103.575 7,325.760 ↓ 0.0 0 1,465,152

Materialize (cost=0.00..223,617.83 rows=1 width=73) (actual time=0.005..0.005 rows=0 loops=1,465,152)

46. 6,222.185 6,222.185 ↓ 0.0 0 1

Seq Scan on timeentrymetadata te_timeofftype (cost=0.00..223,617.83 rows=1 width=73) (actual time=6,222.185..6,222.185 rows=0 loops=1)

  • Filter: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:TIME-OFF-TYPE'::text)
  • Rows Removed by Filter: 6384855
47. 0.017 0.028 ↑ 1.0 9 1

Hash (cost=1.09..1.09 rows=9 width=4) (actual time=0.028..0.028 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
48. 0.011 0.011 ↑ 1.0 9 1

Seq Scan on timeoffcode toc (cost=0.00..1.09 rows=9 width=4) (actual time=0.004..0.011 rows=9 loops=1)

49. 1,061.713 7,325.760 ↓ 0.0 0 1,465,152

Materialize (cost=0.00..223,617.83 rows=1 width=73) (actual time=0.005..0.005 rows=0 loops=1,465,152)

50. 6,264.047 6,264.047 ↓ 0.0 0 1

Seq Scan on timeentrymetadata te_client (cost=0.00..223,617.83 rows=1 width=73) (actual time=6,264.047..6,264.047 rows=0 loops=1)

  • Filter: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:CLIENT'::text)
  • Rows Removed by Filter: 6384855
51. 0.033 0.067 ↑ 1.0 66 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
52. 0.034 0.034 ↑ 1.0 66 1

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

53. 1,107.911 7,325.760 ↓ 0.0 0 1,465,152

Materialize (cost=0.00..223,617.83 rows=1 width=17) (actual time=0.005..0.005 rows=0 loops=1,465,152)

54. 6,217.849 6,217.849 ↓ 0.0 0 1

Seq Scan on timeentrymetadata te_isbillable (cost=0.00..223,617.83 rows=1 width=17) (actual time=6,217.849..6,217.849 rows=0 loops=1)

  • Filter: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:IS-BILLABLE'::text)
  • Rows Removed by Filter: 6384855
55. 0.001 0.012 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.68..24.75 rows=1 width=134) (actual time=0.012..0.012 rows=0 loops=1)

56. 0.002 0.011 ↓ 0.0 0 1

Nested Loop (cost=0.40..16.44 rows=1 width=130) (actual time=0.011..0.011 rows=0 loops=1)

57. 0.009 0.009 ↓ 0.0 0 1

Index Scan using ixallocatedtimerootid on allocatedtime at (cost=0.12..8.14 rows=1 width=116) (actual time=0.009..0.009 rows=0 loops=1)

58. 0.000 0.000 ↓ 0.0 0

Index Scan using userinfo_pkey on userinfo ui_1 (cost=0.28..8.29 rows=1 width=18) (never executed)

  • Index Cond: (id = at.userid)
59. 0.000 0.000 ↓ 0.0 0

Index Scan using project_pkey on project pj (cost=0.28..8.30 rows=1 width=8) (never executed)

  • Index Cond: (at.projectid = id)
60. 0.010 0.044 ↑ 1.0 1 1

Nested Loop (cost=0.28..10.34 rows=1 width=108) (actual time=0.040..0.044 rows=1 loops=1)

61. 0.015 0.023 ↑ 1.0 1 1

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

  • Join Filter: (toe.timeoffid = toff.id)
62. 0.005 0.005 ↑ 1.0 1 1

Seq Scan on timeoffentries toe (cost=0.00..1.01 rows=1 width=40) (actual time=0.005..0.005 rows=1 loops=1)

63. 0.003 0.003 ↑ 1.0 1 1

Seq Scan on timeoffs toff (cost=0.00..1.01 rows=1 width=62) (actual time=0.002..0.003 rows=1 loops=1)

64. 0.011 0.011 ↑ 1.0 1 1

Index Scan using userinfo_pkey on userinfo ui_2 (cost=0.28..8.29 rows=1 width=18) (actual time=0.010..0.011 rows=1 loops=1)

  • Index Cond: (id = toff.userid)
65. 0.000 0.016 ↓ 0.0 0 1

Nested Loop Left Join (cost=25.95..113.08 rows=18 width=150) (actual time=0.016..0.016 rows=0 loops=1)

66. 0.002 0.016 ↓ 0.0 0 1

Nested Loop (cost=25.67..55.86 rows=18 width=146) (actual time=0.016..0.016 rows=0 loops=1)

67. 0.012 0.014 ↓ 0.0 0 1

Hash Join (cost=25.40..48.37 rows=18 width=132) (actual time=0.014..0.014 rows=0 loops=1)

  • Hash Cond: (punch_pair.endtimepunchid = tp_end.id)
68. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=12.70..35.04 rows=120 width=92) (never executed)

  • Hash Cond: (punch_pair.starttimepunchid = tp_start.id)
69. 0.000 0.000 ↓ 0.0 0

Seq Scan on denormalizedtimepunchpair punch_pair (cost=0.00..18.10 rows=810 width=52) (never executed)

70. 0.000 0.000 ↓ 0.0 0

Hash (cost=11.20..11.20 rows=120 width=72) (never executed)

71. 0.000 0.000 ↓ 0.0 0

Seq Scan on timepunch tp_start (cost=0.00..11.20 rows=120 width=72) (never executed)

72. 0.001 0.002 ↓ 0.0 0 1

Hash (cost=11.20..11.20 rows=120 width=72) (actual time=0.002..0.002 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
73. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on timepunch tp_end (cost=0.00..11.20 rows=120 width=72) (actual time=0.001..0.001 rows=0 loops=1)

74. 0.000 0.000 ↓ 0.0 0

Index Scan using userinfo_pkey on userinfo ui_3 (cost=0.28..0.41 rows=1 width=18) (never executed)

  • Index Cond: (id = punch_pair.userid)
75. 0.000 0.000 ↓ 0.0 0

Index Scan using project_pkey on project proj_1 (cost=0.28..3.16 rows=1 width=8) (never executed)

  • Index Cond: (COALESCE(tp_start.projectid, tp_end.projectid) = id)
Planning time : 14.308 ms
Execution time : 415,828.380 ms