explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4e2U : optimize join : parse Uri before join (cost down 1000 times trillions -> billions)

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

Hash Left Join (cost=12,386,623.84..3,107,906,097.44 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=11,483,858.23..1,286,919,651.68 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=11,483,720.65..1,158,631,678.10 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=10,580,955.04..1,133,674,936.34 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=10,580,953.07..1,055,740,074.00 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=10,580,952.03..990,954,715.78 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=10,580,758.43..862,666,686.18 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=10,580,215.95..734,378,307.70 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=9,677,450.34..717,439,552.24 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,774,684.73..700,500,796.77 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,432,286.66..659,711,969.32 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,023,621.16..615,462,600.26 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,023,621.16..86,275,275.73 rows=6,414,391,800 width=292) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=6,023,621.16..6,095,351.69 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,120,855.55..5,169,345.51 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,204,910.95..4,228,151.50 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,857,987.56 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,729,767.67 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..902,765.60 rows=6,619,687 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=915,944.60..916,518.56 rows=114,792 width=48) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Sort (cost=915,944.60..916,231.58 rows=114,792 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..902,765.60 rows=114,792 width=48) (actual rows= loops=)

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

Sort (cost=902,765.61..902,765.62 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..902,765.60 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,408,665.50..1,421,611.51 rows=2,589,203 width=76) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Sort (cost=1,408,665.50..1,415,138.50 rows=2,589,203 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..902,765.60 rows=2,589,203 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,342,398.07..1,353,704.74 rows=2,261,333 width=76) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Sort (cost=1,342,398.07..1,348,051.41 rows=2,261,333 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..902,765.60 rows=2,261,333 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=902,765.61..902,765.62 rows=1 width=76) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Sort (cost=902,765.61..902,765.62 rows=1 width=76) (actual rows= loops=)

  • Sort Key: te_breaktype.timeentryid
43. 0.000 0.000 ↓ 0.0

Seq Scan on timeentrymetadata te_breaktype (cost=0.00..902,765.60 rows=1 width=76) (actual rows= loops=)

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

Materialize (cost=902,765.61..902,765.62 rows=1 width=76) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Sort (cost=902,765.61..902,765.62 rows=1 width=76) (actual rows= loops=)

  • Sort Key: te_timeofftype.timeentryid
46. 0.000 0.000 ↓ 0.0

Seq Scan on timeentrymetadata te_timeofftype (cost=0.00..902,765.60 rows=1 width=76) (actual rows= loops=)

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

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

48. 0.000 0.000 ↓ 0.0

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

49. 0.000 0.000 ↓ 0.0

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

50. 0.000 0.000 ↓ 0.0

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

51. 0.000 0.000 ↓ 0.0

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

52. 0.000 0.000 ↓ 0.0

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

53. 0.000 0.000 ↓ 0.0

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

54. 0.000 0.000 ↓ 0.0

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

55. 0.000 0.000 ↓ 0.0

Hash (cost=902,765.60..902,765.60 rows=1 width=76) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

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

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

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

58. 0.000 0.000 ↓ 0.0

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

59. 0.000 0.000 ↓ 0.0

Hash (cost=902,765.60..902,765.60 rows=1 width=17) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

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

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