explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sZdQ

Settings
# exclusive inclusive rows x rows loops node
1. 249.094 1,481.842 ↑ 690.0 1,351 1

Hash Left Join (cost=1,641.22..380,350.01 rows=932,190 width=284) (actual time=39.070..1,481.842 rows=1,351 loops=1)

  • Hash Cond: (te.id = te_isbillable.timeentryid)
2. 9.474 1,232.640 ↑ 690.0 1,351 1

Hash Left Join (cost=1,633.07..115,826.05 rows=932,190 width=283) (actual time=38.888..1,232.640 rows=1,351 loops=1)

  • Hash Cond: (("substring"(te_client.uri, (('^urn:replicon-tenant:'::text || si.tenantslug) || ':client:(.*)$'::text)))::integer = client.id)
3. 8.381 1,223.129 ↑ 690.0 1,351 1

Hash Left Join (cost=1,631.98..106,316.62 rows=932,190 width=350) (actual time=38.827..1,223.129 rows=1,351 loops=1)

  • Hash Cond: (te.id = te_client.timeentryid)
4. 9.289 1,214.696 ↑ 690.0 1,351 1

Hash Left Join (cost=1,623.83..102,805.86 rows=932,190 width=279) (actual time=38.756..1,214.696 rows=1,351 loops=1)

  • Hash Cond: (("substring"(te_timeofftype.uri, (('^urn:replicon-tenant:'::text || si.tenantslug) || ':time-off-type:(.*)$'::text)))::integer = toc.id)
5. 8.450 1,205.340 ↑ 690.0 1,351 1

Hash Left Join (cost=1,622.63..93,063.27 rows=932,190 width=346) (actual time=38.661..1,205.340 rows=1,351 loops=1)

  • Hash Cond: (te.id = te_timeofftype.timeentryid)
6. 91.167 1,196.839 ↑ 690.0 1,351 1

Hash Left Join (cost=1,614.48..89,552.50 rows=932,190 width=275) (actual time=38.591..1,196.839 rows=1,351 loops=1)

  • Hash Cond: (("substring"(te_breaktype.uri, (('^urn:replicon-tenant:'::text || si.tenantslug) || ':break-type:(.*)$'::text)))::uuid = bt.id)
7. 8.625 1,105.647 ↑ 690.0 1,351 1

Hash Left Join (cost=1,613.43..80,136.34 rows=932,190 width=330) (actual time=38.533..1,105.647 rows=1,351 loops=1)

  • Hash Cond: (te.id = te_breaktype.timeentryid)
8. 14.149 1,096.018 ↑ 690.0 1,351 1

Hash Left Join (cost=1,446.15..69,798.46 rows=932,190 width=259) (actual time=37.506..1,096.018 rows=1,351 loops=1)

  • Hash Cond: (te.id = te_billingrate.timeentryid)
9. 455.272 1,072.650 ↑ 690.0 1,351 1

Hash Left Join (cost=1,222.83..53,670.64 rows=932,190 width=188) (actual time=28.267..1,072.650 rows=1,351 loops=1)

  • Hash Cond: (("substring"(te_task.uri, (('^urn:replicon-tenant:'::text || si.tenantslug) || ':task:(.*)$'::text)))::integer = task.id)
10. 18.926 616.464 ↑ 690.0 1,351 1

Hash Left Join (cost=1,218.00..38,471.11 rows=932,190 width=255) (actual time=27.305..616.464 rows=1,351 loops=1)

  • Hash Cond: (te.id = te_task.timeentryid)
11. 249.759 588.956 ↑ 690.0 1,351 1

Hash Left Join (cost=994.44..22,205.05 rows=932,190 width=184) (actual time=18.702..588.956 rows=1,351 loops=1)

  • Hash Cond: (("substring"(te_project.uri, (('^urn:replicon-tenant:'::text || si.tenantslug) || ':project:(.*)$'::text)))::integer = proj.id)
12. 8.916 339.021 ↑ 690.0 1,351 1

Hash Join (cost=991.87..11,715.34 rows=932,190 width=247) (actual time=18.494..339.021 rows=1,351 loops=1)

  • Hash Cond: (te.userid = ui.id)
13. 10.137 329.339 ↑ 1.0 1,351 1

Hash Left Join (cost=456.10..689.05 rows=1,351 width=183) (actual time=17.553..329.339 rows=1,351 loops=1)

  • Hash Cond: (te.id = te_project.timeentryid)
14. 277.163 315.562 ↑ 1.0 1,351 1

Hash Left Join (cost=258.61..473.10 rows=1,351 width=112) (actual time=13.895..315.562 rows=1,351 loops=1)

  • Hash Cond: (("substring"(te_activity.uri, (('^urn:replicon-tenant:'::text || si.tenantslug) || ':activity:(.*)$'::text)))::integer = act.id)
15. 8.173 38.305 ↑ 1.0 1,351 1

Nested Loop (cost=257.45..457.96 rows=1,351 width=179) (actual time=13.196..38.305 rows=1,351 loops=1)

16. 0.011 0.011 ↑ 1.0 1 1

Seq Scan on systeminformation si (cost=0.00..1.01 rows=1 width=32) (actual time=0.007..0.011 rows=1 loops=1)

17. 8.558 30.121 ↑ 1.0 1,351 1

Hash Left Join (cost=257.45..443.44 rows=1,351 width=147) (actual time=13.175..30.121 rows=1,351 loops=1)

  • Hash Cond: (te.id = te_comment.timeentryid)
18. 6.585 20.210 ↑ 1.0 1,351 1

Hash Right Join (cost=74.08..244.70 rows=1,351 width=131) (actual time=11.801..20.210 rows=1,351 loops=1)

  • Hash Cond: (te_activity.timeentryid = te.id)
19. 1.876 2.139 ↑ 1.0 519 1

Bitmap Heap Scan on timeentrymetadata te_activity (cost=28.30..191.79 rows=519 width=87) (actual time=0.284..2.139 rows=519 loops=1)

  • Recheck Cond: (key = 'urn:replicon:time-entry-metadata-key:activity'::text)
  • Heap Blocks: exact=57
20. 0.263 0.263 ↑ 1.0 519 1

Bitmap Index Scan on ixtemdkey2 (cost=0.00..28.17 rows=519 width=0) (actual time=0.263..0.263 rows=519 loops=1)

  • Index Cond: (key = 'urn:replicon:time-entry-metadata-key:activity'::text)
21. 5.917 11.486 ↑ 1.0 1,351 1

Hash (cost=28.89..28.89 rows=1,351 width=60) (actual time=11.486..11.486 rows=1,351 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 111kB
22. 5.569 5.569 ↑ 1.0 1,351 1

Seq Scan on timeentry te (cost=0.00..28.89 rows=1,351 width=60) (actual time=0.021..5.569 rows=1,351 loops=1)

  • Filter: ((timeallocationtype <> 2) OR (timeallocationtype IS NULL))
23. 0.604 1.353 ↑ 1.0 186 1

Hash (cost=181.04..181.04 rows=186 width=32) (actual time=1.353..1.353 rows=186 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
24. 0.649 0.749 ↑ 1.0 186 1

Bitmap Heap Scan on timeentrymetadata te_comment (cost=13.72..181.04 rows=186 width=32) (actual time=0.112..0.749 rows=186 loops=1)

  • Recheck Cond: (key = 'urn:replicon:time-entry-metadata-key:comments'::text)
  • Heap Blocks: exact=20
25. 0.100 0.100 ↑ 1.0 186 1

Bitmap Index Scan on ixtemdkey2 (cost=0.00..13.68 rows=186 width=0) (actual time=0.100..0.100 rows=186 loops=1)

  • Index Cond: (key = 'urn:replicon:time-entry-metadata-key:comments'::text)
26. 0.066 0.094 ↑ 1.0 7 1

Hash (cost=1.07..1.07 rows=7 width=4) (actual time=0.094..0.094 rows=7 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
27. 0.028 0.028 ↑ 1.0 7 1

Seq Scan on activities act (cost=0.00..1.07 rows=7 width=4) (actual time=0.005..0.028 rows=7 loops=1)

28. 1.745 3.640 ↑ 1.0 495 1

Hash (cost=191.31..191.31 rows=495 width=87) (actual time=3.640..3.640 rows=495 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 62kB
29. 1.705 1.895 ↑ 1.0 495 1

Bitmap Heap Scan on timeentrymetadata te_project (cost=28.12..191.31 rows=495 width=87) (actual time=0.208..1.895 rows=495 loops=1)

  • Recheck Cond: (key = 'urn:replicon:time-entry-metadata-key:project'::text)
  • Heap Blocks: exact=56
30. 0.190 0.190 ↑ 1.0 495 1

Bitmap Index Scan on ixtemdkey2 (cost=0.00..27.99 rows=495 width=0) (actual time=0.190..0.190 rows=495 loops=1)

  • Index Cond: (key = 'urn:replicon:time-entry-metadata-key:project'::text)
31. 0.142 0.766 ↑ 690.0 30 1

Hash (cost=277.02..277.02 rows=20,700 width=68) (actual time=0.766..0.766 rows=30 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 259kB
32. 0.239 0.624 ↑ 690.0 30 1

Nested Loop (cost=0.00..277.02 rows=20,700 width=68) (actual time=0.041..0.624 rows=30 loops=1)

33. 0.012 0.012 ↑ 690.0 1 1

Seq Scan on projectsysteminformation psi (cost=0.00..16.90 rows=690 width=32) (actual time=0.008..0.012 rows=1 loops=1)

34. 0.232 0.373 ↑ 1.0 30 1

Materialize (cost=0.00..1.45 rows=30 width=36) (actual time=0.021..0.373 rows=30 loops=1)

35. 0.141 0.141 ↑ 1.0 30 1

Seq Scan on userinfo ui (cost=0.00..1.30 rows=30 width=36) (actual time=0.010..0.141 rows=30 loops=1)

36. 0.085 0.176 ↑ 1.0 25 1

Hash (cost=2.25..2.25 rows=25 width=8) (actual time=0.176..0.176 rows=25 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
37. 0.091 0.091 ↑ 1.0 25 1

Seq Scan on project proj (cost=0.00..2.25 rows=25 width=8) (actual time=0.017..0.091 rows=25 loops=1)

38. 3.332 8.582 ↑ 1.0 974 1

Hash (cost=211.39..211.39 rows=974 width=87) (actual time=8.582..8.582 rows=974 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 112kB
39. 5.250 5.250 ↑ 1.0 974 1

Seq Scan on timeentrymetadata te_task (cost=0.00..211.39 rows=974 width=87) (actual time=0.770..5.250 rows=974 loops=1)

  • Filter: (key = 'urn:replicon:time-entry-metadata-key:task'::text)
  • Rows Removed by Filter: 3377
40. 0.441 0.914 ↑ 1.0 126 1

Hash (cost=3.26..3.26 rows=126 width=4) (actual time=0.914..0.914 rows=126 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
41. 0.473 0.473 ↑ 1.0 126 1

Seq Scan on task (cost=0.00..3.26 rows=126 width=4) (actual time=0.023..0.473 rows=126 loops=1)

42. 3.318 9.219 ↑ 1.0 954 1

Hash (cost=211.39..211.39 rows=954 width=87) (actual time=9.219..9.219 rows=954 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 134kB
43. 5.901 5.901 ↑ 1.0 954 1

Seq Scan on timeentrymetadata te_billingrate (cost=0.00..211.39 rows=954 width=87) (actual time=0.011..5.901 rows=954 loops=1)

  • Filter: (key = 'urn:replicon:time-entry-metadata-key:billing-rate'::text)
  • Rows Removed by Filter: 3397
44. 0.445 1.004 ↑ 1.0 123 1

Hash (cost=165.75..165.75 rows=123 width=87) (actual time=1.004..1.004 rows=123 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 26kB
45. 0.473 0.559 ↑ 1.0 123 1

Bitmap Heap Scan on timeentrymetadata te_breaktype (cost=9.24..165.75 rows=123 width=87) (actual time=0.100..0.559 rows=123 loops=1)

  • Recheck Cond: (key = 'urn:replicon:time-entry-metadata-key:break-type'::text)
  • Heap Blocks: exact=29
46. 0.086 0.086 ↑ 1.0 123 1

Bitmap Index Scan on ixtemdkey2 (cost=0.00..9.21 rows=123 width=0) (actual time=0.086..0.086 rows=123 loops=1)

  • Index Cond: (key = 'urn:replicon:time-entry-metadata-key:break-type'::text)
47. 0.013 0.025 ↑ 1.0 2 1

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

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

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

49. 0.006 0.051 ↓ 0.0 0 1

Hash (cost=8.14..8.14 rows=1 width=87) (actual time=0.051..0.051 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
50. 0.045 0.045 ↓ 0.0 0 1

Index Scan using ixtemdkey2 on timeentrymetadata te_timeofftype (cost=0.28..8.14 rows=1 width=87) (actual time=0.045..0.045 rows=0 loops=1)

  • Index Cond: (key = 'urn:replicon:time-entry-metadata-key:time-off-type'::text)
51. 0.038 0.067 ↑ 1.0 9 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
52. 0.029 0.029 ↑ 1.0 9 1

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

53. 0.004 0.052 ↓ 0.0 0 1

Hash (cost=8.14..8.14 rows=1 width=87) (actual time=0.052..0.052 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
54. 0.048 0.048 ↓ 0.0 0 1

Index Scan using ixtemdkey2 on timeentrymetadata te_client (cost=0.28..8.14 rows=1 width=87) (actual time=0.048..0.048 rows=0 loops=1)

  • Index Cond: (key = 'urn:replicon:time-entry-metadata-key:client'::text)
55. 0.022 0.037 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=4) (actual time=0.037..0.037 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
56. 0.015 0.015 ↑ 1.0 4 1

Seq Scan on clients client (cost=0.00..1.04 rows=4 width=4) (actual time=0.005..0.015 rows=4 loops=1)

57. 0.005 0.108 ↓ 0.0 0 1

Hash (cost=8.14..8.14 rows=1 width=17) (actual time=0.108..0.108 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
58. 0.103 0.103 ↓ 0.0 0 1

Index Scan using ixtemdkey2 on timeentrymetadata te_isbillable (cost=0.28..8.14 rows=1 width=17) (actual time=0.103..0.103 rows=0 loops=1)

  • Index Cond: (key = 'urn:replicon:time-entry-metadata-key:is-billable'::text)