explain.depesz.com

PostgreSQL's explain analyze made readable

Result: meoI

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

Append (cost=1,550,612.88..3,323,102.44 rows=1,479,889 width=158) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,550,612.88..3,308,155.38 rows=1,479,869 width=280) (actual rows= loops=)

  • Join Filter: (te.id = te_isbillable.timeentryid)
3. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,550,612.88..2,647,999.06 rows=1,479,869 width=279) (actual rows= loops=)

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

Nested Loop Left Join (cost=1,550,610.39..2,630,164.15 rows=1,479,869 width=332) (actual rows= loops=)

  • Join Filter: (te.id = te_client.timeentryid)
5. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,550,610.39..2,384,371.15 rows=1,479,869 width=275) (actual rows= loops=)

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

Nested Loop Left Join (cost=1,550,609.19..2,370,755.15 rows=1,479,869 width=328) (actual rows= loops=)

  • Join Filter: (te.id = te_timeofftype.timeentryid)
7. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,550,609.19..2,124,962.15 rows=1,479,869 width=271) (actual rows= loops=)

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

Nested Loop Left Join (cost=1,550,608.15..2,111,864.26 rows=1,479,869 width=312) (actual rows= loops=)

  • Join Filter: (te.id = te_breaktype.timeentryid)
9. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=1,550,608.15..1,866,071.26 rows=1,479,869 width=255) (actual rows= loops=)

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

Seq Scan on timeentrymetadata te_billingrate (cost=0.00..223,594.96 rows=1,309,860 width=73) (actual rows= loops=)

  • Filter: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:BILLING-RATE'::text)
11. 0.000 0.000 ↓ 0.0

Hash (cost=1,491,643.78..1,491,643.78 rows=1,479,869 width=198) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,042,831.13..1,491,643.78 rows=1,479,869 width=198) (actual rows= loops=)

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

Hash Right Join (cost=1,038,950.62..1,358,512.74 rows=1,479,869 width=251) (actual rows= loops=)

  • Hash Cond: (te_task.timeentryid = te.id)
14. 0.000 0.000 ↓ 0.0

Seq Scan on timeentrymetadata te_task (cost=0.00..223,594.96 rows=1,414,333 width=73) (actual rows= loops=)

  • Filter: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:TASK'::text)
15. 0.000 0.000 ↓ 0.0

Hash (cost=979,986.26..979,986.26 rows=1,479,869 width=194) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=593,809.29..979,986.26 rows=1,479,869 width=194) (actual rows= loops=)

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

Hash Left Join (cost=593,644.92..952,074.35 rows=1,479,869 width=243) (actual rows= loops=)

  • Hash Cond: (("substring"(te_activity.uri, concat('^urn:replicon-tenant:', si.tenantslug, ':activity:(.*)$')))::integer = act.id)
18. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=593,643.81..938,754.41 rows=1,479,869 width=296) (actual rows= loops=)

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

Hash Join (cost=370,048.83..709,609.91 rows=1,479,869 width=239) (actual rows= loops=)

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

Nested Loop (cost=370,008.47..689,221.35 rows=1,479,869 width=225) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..2.03 rows=1 width=65) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

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

23. 0.000 0.000 ↓ 0.0

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

24. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=370,008.47..674,420.63 rows=1,479,869 width=160) (actual rows= loops=)

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

Seq Scan on timeentrymetadata te_project (cost=0.00..223,594.96 rows=1,479,869 width=73) (actual rows= loops=)

  • Filter: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:PROJECT'::text)
26. 0.000 0.000 ↓ 0.0

Hash (cost=328,801.07..328,801.07 rows=1,465,152 width=103) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=64,575.80..328,801.07 rows=1,465,152 width=103) (actual rows= loops=)

  • Hash Cond: (te_comment.timeentryid = te.id)
28. 0.000 0.000 ↓ 0.0

Seq Scan on timeentrymetadata te_comment (cost=0.00..223,594.96 rows=706,422 width=59) (actual rows= loops=)

  • Filter: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:COMMENTS'::text)
29. 0.000 0.000 ↓ 0.0

Hash (cost=30,522.40..30,522.40 rows=1,465,152 width=60) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Seq Scan on timeentry te (cost=0.00..30,522.40 rows=1,465,152 width=60) (actual rows= loops=)

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

Hash (cost=27.94..27.94 rows=994 width=18) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Seq Scan on userinfo ui (cost=0.00..27.94 rows=994 width=18) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Hash (cost=223,594.96..223,594.96 rows=1 width=73) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Seq Scan on timeentrymetadata te_activity (cost=0.00..223,594.96 rows=1 width=73) (actual rows= loops=)

  • Filter: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:ACTIVITY'::text)
35. 0.000 0.000 ↓ 0.0

Hash (cost=1.05..1.05 rows=5 width=4) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

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

37. 0.000 0.000 ↓ 0.0

Hash (cost=129.72..129.72 rows=2,772 width=8) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Seq Scan on project proj (cost=0.00..129.72 rows=2,772 width=8) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Hash (cost=2,457.89..2,457.89 rows=86,689 width=4) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Seq Scan on task (cost=0.00..2,457.89 rows=86,689 width=4) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..223,594.97 rows=1 width=73) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Seq Scan on timeentrymetadata te_breaktype (cost=0.00..223,594.96 rows=1 width=73) (actual rows= loops=)

  • Filter: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:BREAK-TYPE'::text)
43. 0.000 0.000 ↓ 0.0

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

44. 0.000 0.000 ↓ 0.0

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

45. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..223,594.97 rows=1 width=73) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

Seq Scan on timeentrymetadata te_timeofftype (cost=0.00..223,594.96 rows=1 width=73) (actual rows= loops=)

  • Filter: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:TIME-OFF-TYPE'::text)
47. 0.000 0.000 ↓ 0.0

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

48. 0.000 0.000 ↓ 0.0

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

49. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..223,594.97 rows=1 width=73) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

Seq Scan on timeentrymetadata te_client (cost=0.00..223,594.96 rows=1 width=73) (actual rows= loops=)

  • Filter: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:CLIENT'::text)
51. 0.000 0.000 ↓ 0.0

Hash (cost=1.66..1.66 rows=66 width=4) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

Seq Scan on clients client (cost=0.00..1.66 rows=66 width=4) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..223,594.97 rows=1 width=17) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

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

  • Filter: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:IS-BILLABLE'::text)
55. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.68..24.75 rows=1 width=134) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.40..16.44 rows=1 width=130) (actual rows= loops=)

57. 0.000 0.000 ↓ 0.0

Index Scan using ixallocatedtimerootid on allocatedtime at (cost=0.12..8.14 rows=1 width=116) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

Index Scan using userinfo_pkey on userinfo ui_1 (cost=0.28..8.29 rows=1 width=18) (actual rows= loops=)

  • Index Cond: (id = at.userid)
59. 0.000 0.000 ↓ 0.0

Index Scan using project_pkey on project pj (cost=0.28..8.30 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (at.projectid = id)
60. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.28..10.34 rows=1 width=108) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..2.03 rows=1 width=94) (actual rows= loops=)

  • Join Filter: (toe.timeoffid = toff.id)
62. 0.000 0.000 ↓ 0.0

Seq Scan on timeoffentries toe (cost=0.00..1.01 rows=1 width=40) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

Seq Scan on timeoffs toff (cost=0.00..1.01 rows=1 width=62) (actual rows= loops=)

64. 0.000 0.000 ↓ 0.0

Index Scan using userinfo_pkey on userinfo ui_2 (cost=0.28..8.29 rows=1 width=18) (actual rows= loops=)

  • Index Cond: (id = toff.userid)
65. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=25.95..113.08 rows=18 width=150) (actual rows= loops=)

66. 0.000 0.000 ↓ 0.0

Nested Loop (cost=25.67..55.86 rows=18 width=146) (actual rows= loops=)

67. 0.000 0.000 ↓ 0.0

Hash Join (cost=25.40..48.37 rows=18 width=132) (actual rows= loops=)

  • Hash Cond: (punch_pair.endtimepunchid = tp_end.id)
68. 0.000 0.000 ↓ 0.0

Hash Join (cost=12.70..35.04 rows=120 width=92) (actual rows= loops=)

  • Hash Cond: (punch_pair.starttimepunchid = tp_start.id)
69. 0.000 0.000 ↓ 0.0

Seq Scan on denormalizedtimepunchpair punch_pair (cost=0.00..18.10 rows=810 width=52) (actual rows= loops=)

70. 0.000 0.000 ↓ 0.0

Hash (cost=11.20..11.20 rows=120 width=72) (actual rows= loops=)

71. 0.000 0.000 ↓ 0.0

Seq Scan on timepunch tp_start (cost=0.00..11.20 rows=120 width=72) (actual rows= loops=)

72. 0.000 0.000 ↓ 0.0

Hash (cost=11.20..11.20 rows=120 width=72) (actual rows= loops=)

73. 0.000 0.000 ↓ 0.0

Seq Scan on timepunch tp_end (cost=0.00..11.20 rows=120 width=72) (actual rows= loops=)

74. 0.000 0.000 ↓ 0.0

Index Scan using userinfo_pkey on userinfo ui_3 (cost=0.28..0.41 rows=1 width=18) (actual rows= loops=)

  • Index Cond: (id = punch_pair.userid)
75. 0.000 0.000 ↓ 0.0

Index Scan using project_pkey on project proj_1 (cost=0.28..3.16 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (COALESCE(tp_start.projectid, tp_end.projectid) = id)