explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2uqr : 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,594,870.32..3,108,133,735.83 rows=6,414,391,800 width=280) (actual rows= loops=)

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

Hash Left Join (cost=9,690,249.53..1,287,145,434.89 rows=6,414,391,800 width=279) (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,690,111.95..1,158,857,461.31 rows=6,414,391,800 width=335) (actual rows= loops=)

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

Hash Left Join (cost=8,785,491.17..1,133,898,864.38 rows=6,414,391,800 width=275) (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,785,489.20..1,055,964,002.04 rows=6,414,391,800 width=331) (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,785,488.15..991,178,643.81 rows=6,414,391,800 width=375) (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,785,294.55..862,890,614.21 rows=6,414,391,800 width=431) (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,784,752.08..734,602,235.74 rows=6,414,391,800 width=483) (actual rows= loops=)

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

Merge Left Join (cost=8,784,751.52..717,440,664.05 rows=6,414,391,800 width=423) (actual rows= loops=)

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

Merge Left Join (cost=8,784,750.96..700,279,092.37 rows=6,414,391,800 width=363) (actual rows= loops=)

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

Merge Left Join (cost=7,438,861.80..659,402,784.00 rows=6,414,391,800 width=303) (actual rows= loops=)

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

Nested Loop Left Join (cost=6,034,518.39..615,473,550.40 rows=6,414,391,800 width=243) (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,034,518.39..86,286,225.86 rows=6,414,391,800 width=299) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=6,034,518.39..6,106,301.82 rows=9,296,220 width=235) (actual rows= loops=)

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

Merge Left Join (cost=5,129,897.61..5,178,440.47 rows=9,296,220 width=175) (actual rows= loops=)

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

Sort (cost=4,211,324.68..4,234,565.23 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,864,401.29 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,736,181.39 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..904,620.78 rows=6,712,426 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=918,572.93..919,162.00 rows=117,815 width=55) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Sort (cost=918,572.93..918,867.47 rows=117,815 width=55) (actual rows= loops=)

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

Seq Scan on timeentrymetadata te_comment (cost=0.00..904,620.78 rows=117,815 width=55) (actual rows= loops=)

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

Sort (cost=904,620.79..904,620.79 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..904,620.78 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,404,343.41..1,417,136.89 rows=2,558,697 width=76) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Sort (cost=1,404,343.41..1,410,740.15 rows=2,558,697 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..904,620.78 rows=2,558,697 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,345,889.15..1,357,236.38 rows=2,269,446 width=76) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Sort (cost=1,345,889.15..1,351,562.77 rows=2,269,446 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..904,620.78 rows=2,269,446 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,125,581.84 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,125,581.84 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,125,581.84 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,125,581.84 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=904,620.78..904,620.78 rows=1 width=76) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Seq Scan on timeentrymetadata te_client (cost=0.00..904,620.78 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=904,620.78..904,620.78 rows=1 width=17) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

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

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