explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bEti : optimize join by materialize uri with index

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

Merge Left Join (cost=5,402,982.11..3,328,735,101,348.35 rows=11,857,477,610 width=277) (actual rows= loops=)

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

Nested Loop Left Join (cost=5,402,981.54..3,325,384,239,909.03 rows=11,857,477,610 width=276) (actual rows= loops=)

  • Join Filter: (te_client.uri = ((('urn:replicon-tenant:'::text || si.tenantslug) || ':client:'::text) || client.uri))
3. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=5,402,981.54..2,405,481,126,792.13 rows=11,857,477,610 width=332) (actual rows= loops=)

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

Nested Loop Left Join (cost=5,402,980.98..2,405,450,359,083.60 rows=11,857,477,610 width=272) (actual rows= loops=)

  • Join Filter: (te_timeofftype.uri = ((('urn:replicon-tenant:'::text || si.tenantslug) || ':time-off-type:'::text) || toc.uri))
5. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=5,402,980.98..2,393,978,249,493.39 rows=11,857,477,610 width=328) (actual rows= loops=)

  • Join Filter: (te_project.uri = ((('urn:replicon-tenant:'::text || si.tenantslug) || ':project:'::text) || proj.uri))
6. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=5,402,980.98..774,520,715,241.63 rows=6,414,391,800 width=380) (actual rows= loops=)

  • Join Filter: (te_task.uri = ((('urn:replicon-tenant:'::text || si.tenantslug) || ':task:'::text) || task.uri))
7. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=5,402,980.98..945,063,978.63 rows=6,414,391,800 width=436) (actual rows= loops=)

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

Merge Left Join (cost=5,402,980.42..890,501,563.55 rows=6,414,391,800 width=376) (actual rows= loops=)

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

Merge Left Join (cost=5,402,979.86..873,341,578.33 rows=6,414,391,800 width=316) (actual rows= loops=)

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

Nested Loop Left Join (cost=5,402,979.29..824,433,901.93 rows=6,414,391,800 width=256) (actual rows= loops=)

  • Join Filter: (te_breaktype.uri = ((('urn:replicon-tenant:'::text || si.tenantslug) || ':break-type:'::text) || bt.uri))
11. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=5,402,979.29..535,786,269.90 rows=6,414,391,800 width=300) (actual rows= loops=)

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

Nested Loop Left Join (cost=5,402,978.73..518,626,284.69 rows=6,414,391,800 width=240) (actual rows= loops=)

  • Join Filter: (te_activity.uri = ((('urn:replicon-tenant:'::text || si.tenantslug) || ':activity:'::text) || act.uri))
13. 0.000 0.000 ↓ 0.0

Nested Loop (cost=5,402,978.73..85,654,837.15 rows=6,414,391,800 width=296) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=5,402,978.73..5,474,913.12 rows=9,296,220 width=232) (actual rows= loops=)

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

Merge Left Join (cost=4,706,343.07..4,755,036.89 rows=9,296,220 width=172) (actual rows= loops=)

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

Sort (cost=3,994,670.25..4,017,910.80 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,647,746.85 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,519,526.96 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..696,635.65 rows=6,536,034 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=711,672.83..712,305.03 rows=126,441 width=52) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Sort (cost=711,672.83..711,988.93 rows=126,441 width=52) (actual rows= loops=)

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

Seq Scan on timeentrymetadata te_comment (cost=0.00..696,635.65 rows=126,441 width=52) (actual rows= loops=)

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

Sort (cost=696,635.66..696,635.67 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..696,635.65 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=36) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

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

35. 0.000 0.000 ↓ 0.0

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

36. 0.000 0.000 ↓ 0.0

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

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

Materialize (cost=0.00..1.03 rows=2 width=48) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

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

39. 0.000 0.000 ↓ 0.0

Materialize (cost=0.56..9,151,087.91 rows=2,292,400 width=76) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Index Scan using ixtemtimeentryid2 on timeentrymetadata te_billingrate (cost=0.56..9,145,356.91 rows=2,292,400 width=76) (actual rows= loops=)

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

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

42. 0.000 0.000 ↓ 0.0

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

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

Materialize (cost=0.56..10,578,852.13 rows=2,700,902 width=76) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Index Scan using ixtemtimeentryid2 on timeentrymetadata te_task (cost=0.56..10,572,099.88 rows=2,700,902 width=76) (actual rows= loops=)

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

Materialize (cost=0.00..196.40 rows=5,360 width=66) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

Seq Scan on task2 task (cost=0.00..169.60 rows=5,360 width=66) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..554.32 rows=11,221 width=74) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

Seq Scan on project2 proj (cost=0.00..498.21 rows=11,221 width=74) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..2.65 rows=43 width=36) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

Seq Scan on timeoffcode2 toc (cost=0.00..2.43 rows=43 width=36) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

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

52. 0.000 0.000 ↓ 0.0

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

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

Materialize (cost=0.00..141.72 rows=3,448 width=68) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Seq Scan on clients2 client (cost=0.00..124.48 rows=3,448 width=68) (actual rows= loops=)

55. 0.000 0.000 ↓ 0.0

Materialize (cost=0.56..1,123,995.36 rows=1 width=17) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

Index Scan using ixtemtimeentryid2 on timeentrymetadata te_isbillable (cost=0.56..1,123,995.36 rows=1 width=17) (actual rows= loops=)

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