explain.depesz.com

PostgreSQL's explain analyze made readable

Result: a11U : same query, but added index: timeentryid + key

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=10,570,203.25..3,108,171,217.05 rows=6,414,391,800 width=273) (actual rows= loops=)

  • Hash Cond: (te.id = te_isbillable.timeentryid)
2. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=9,668,369.29..1,287,185,702.94 rows=6,414,391,800 width=272) (actual rows= loops=)

  • Hash Cond: (("substring"(te_client.uri, (('^urn:replicon-tenant:'::text || si.tenantslug) || ':client:(.*)$'::text)))::integer = client.id)
3. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=9,668,231.71..1,158,897,729.36 rows=6,414,391,800 width=328) (actual rows= loops=)

  • Hash Cond: (te.id = te_client.timeentryid)
4. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=8,766,397.75..1,133,941,919.25 rows=6,414,391,800 width=268) (actual rows= loops=)

  • Hash Cond: (("substring"(te_timeofftype.uri, (('^urn:replicon-tenant:'::text || si.tenantslug) || ':time-off-type:(.*)$'::text)))::integer = toc.id)
5. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=8,766,395.78..1,056,007,056.91 rows=6,414,391,800 width=324) (actual rows= loops=)

  • Hash Cond: (("substring"(te_breaktype.uri, (('^urn:replicon-tenant:'::text || si.tenantslug) || ':break-type:(.*)$'::text)))::uuid = bt.id)
6. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=8,766,394.74..991,221,698.68 rows=6,414,391,800 width=368) (actual rows= loops=)

  • Hash Cond: (("substring"(te_task.uri, (('^urn:replicon-tenant:'::text || si.tenantslug) || ':task:(.*)$'::text)))::integer = task.id)
7. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=8,766,201.14..862,933,669.08 rows=6,414,391,800 width=424) (actual rows= loops=)

  • Hash Cond: (("substring"(te_project.uri, (('^urn:replicon-tenant:'::text || si.tenantslug) || ':project:(.*)$'::text)))::integer = proj.id)
8. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=8,765,658.66..734,645,290.61 rows=6,414,391,800 width=476) (actual rows= loops=)

  • Merge Cond: (te.id = te_timeofftype.timeentryid)
9. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=8,765,658.10..717,485,391.02 rows=6,414,391,800 width=416) (actual rows= loops=)

  • Merge Cond: (te.id = te_breaktype.timeentryid)
10. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=8,765,657.54..700,325,491.43 rows=6,414,391,800 width=356) (actual rows= loops=)

  • Merge Cond: (te.id = te_billingrate.timeentryid)
11. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=7,425,700.35..659,616,624.37 rows=6,414,391,800 width=296) (actual rows= loops=)

  • Merge Cond: (te.id = te_task.timeentryid)
12. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=6,019,704.44..615,458,676.89 rows=6,414,391,800 width=236) (actual rows= loops=)

  • Join Filter: (("substring"(te_activity.uri, (('^urn:replicon-tenant:'::text || si.tenantslug) || ':activity:(.*)$'::text)))::integer = act.id)
13. 0.000 0.000 ↓ 0.0

Nested Loop (cost=6,019,704.44..86,271,352.35 rows=6,414,391,800 width=292) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=6,019,704.44..6,091,428.32 rows=9,296,220 width=228) (actual rows= loops=)

  • Merge Cond: (te.id = te_activity.timeentryid)
15. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=5,117,870.48..5,166,353.79 rows=9,296,220 width=168) (actual rows= loops=)

  • Merge Cond: (te.id = te_comment.timeentryid)
16. 0.000 0.000 ↓ 0.0

Sort (cost=4,202,902.70..4,226,143.25 rows=9,296,220 width=136) (actual rows= loops=)

  • Sort Key: te.id
17. 0.000 0.000 ↓ 0.0

Hash Join (cost=402,241.37..1,855,979.31 rows=9,296,220 width=136) (actual rows= loops=)

  • Hash Cond: (te.userid = ui.id)
18. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=401,844.50..1,727,759.42 rows=9,296,220 width=120) (actual rows= loops=)

  • Hash Cond: (te_project.timeentryid = te.id)
19. 0.000 0.000 ↓ 0.0

Seq Scan on timeentrymetadata te_project (cost=0.00..901,833.95 rows=6,597,767 width=76) (actual rows= loops=)

  • Filter: (key = 'urn:replicon:time-entry-metadata-key:project'::text)
20. 0.000 0.000 ↓ 0.0

Hash (cost=185,779.75..185,779.75 rows=9,296,220 width=60) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Seq Scan on timeentry te (cost=0.00..185,779.75 rows=9,296,220 width=60) (actual rows= loops=)

  • Filter: ((timeallocationtype <> 2) OR (timeallocationtype IS NULL))
22. 0.000 0.000 ↓ 0.0

Hash (cost=299.72..299.72 rows=7,772 width=20) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Seq Scan on userinfo ui (cost=0.00..299.72 rows=7,772 width=20) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Materialize (cost=914,967.78..915,539.84 rows=114,412 width=48) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Sort (cost=914,967.78..915,253.81 rows=114,412 width=48) (actual rows= loops=)

  • Sort Key: te_comment.timeentryid
26. 0.000 0.000 ↓ 0.0

Seq Scan on timeentrymetadata te_comment (cost=0.00..901,833.95 rows=114,412 width=48) (actual rows= loops=)

  • Filter: (key = 'urn:replicon:time-entry-metadata-key:comments'::text)
27. 0.000 0.000 ↓ 0.0

Sort (cost=901,833.96..901,833.96 rows=1 width=76) (actual rows= loops=)

  • Sort Key: te_activity.timeentryid
28. 0.000 0.000 ↓ 0.0

Seq Scan on timeentrymetadata te_activity (cost=0.00..901,833.95 rows=1 width=76) (actual rows= loops=)

  • Filter: (key = 'urn:replicon:time-entry-metadata-key:activity'::text)
29. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..28.26 rows=690 width=64) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..24.81 rows=690 width=64) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

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

32. 0.000 0.000 ↓ 0.0

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

33. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..1.04 rows=3 width=4) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Seq Scan on activities act (cost=0.00..1.03 rows=3 width=4) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Materialize (cost=1,405,995.91..1,418,899.06 rows=2,580,630 width=76) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Sort (cost=1,405,995.91..1,412,447.48 rows=2,580,630 width=76) (actual rows= loops=)

  • Sort Key: te_task.timeentryid
37. 0.000 0.000 ↓ 0.0

Seq Scan on timeentrymetadata te_task (cost=0.00..901,833.95 rows=2,580,630 width=76) (actual rows= loops=)

  • Filter: (key = 'urn:replicon:time-entry-metadata-key:task'::text)
38. 0.000 0.000 ↓ 0.0

Materialize (cost=1,339,957.19..1,351,226.41 rows=2,253,845 width=76) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Sort (cost=1,339,957.19..1,345,591.80 rows=2,253,845 width=76) (actual rows= loops=)

  • Sort Key: te_billingrate.timeentryid
40. 0.000 0.000 ↓ 0.0

Seq Scan on timeentrymetadata te_billingrate (cost=0.00..901,833.95 rows=2,253,845 width=76) (actual rows= loops=)

  • Filter: (key = 'urn:replicon:time-entry-metadata-key:billing-rate'::text)
41. 0.000 0.000 ↓ 0.0

Materialize (cost=0.56..1,123,909.74 rows=1 width=76) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Index Scan using ixtemtimeentryid2 on timeentrymetadata te_breaktype (cost=0.56..1,123,909.74 rows=1 width=76) (actual rows= loops=)

  • Index Cond: (key = 'urn:replicon:time-entry-metadata-key:break-type'::text)
43. 0.000 0.000 ↓ 0.0

Materialize (cost=0.56..1,123,909.74 rows=1 width=76) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Index Scan using ixtemtimeentryid2 on timeentrymetadata te_timeofftype (cost=0.56..1,123,909.74 rows=1 width=76) (actual rows= loops=)

  • Index Cond: (key = 'urn:replicon:time-entry-metadata-key:time-off-type'::text)
45. 0.000 0.000 ↓ 0.0

Hash (cost=402.21..402.21 rows=11,221 width=8) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

Seq Scan on project proj (cost=0.00..402.21 rows=11,221 width=8) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

Hash (cost=126.60..126.60 rows=5,360 width=4) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

Seq Scan on task (cost=0.00..126.60 rows=5,360 width=4) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

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

50. 0.000 0.000 ↓ 0.0

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

51. 0.000 0.000 ↓ 0.0

Hash (cost=1.43..1.43 rows=43 width=4) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

Seq Scan on timeoffcode toc (cost=0.00..1.43 rows=43 width=4) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

Hash (cost=901,833.95..901,833.95 rows=1 width=76) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Seq Scan on timeentrymetadata te_client (cost=0.00..901,833.95 rows=1 width=76) (actual rows= loops=)

  • Filter: (key = 'urn:replicon:time-entry-metadata-key:client'::text)
55. 0.000 0.000 ↓ 0.0

Hash (cost=94.48..94.48 rows=3,448 width=4) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

Seq Scan on clients client (cost=0.00..94.48 rows=3,448 width=4) (actual rows= loops=)

57. 0.000 0.000 ↓ 0.0

Hash (cost=901,833.95..901,833.95 rows=1 width=17) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

Seq Scan on timeentrymetadata te_isbillable (cost=0.00..901,833.95 rows=1 width=17) (actual rows= loops=)

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