explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1P3eE

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

Append (cost=1,342,115.20..2,077,676.20 rows=1,480,242 width=158) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,342,115.20..2,062,725.61 rows=1,480,222 width=280) (actual rows= loops=)

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

Hash Left Join (cost=1,342,106.61..1,642,704.02 rows=1,480,222 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

Hash Left Join (cost=1,342,104.13..1,624,864.86 rows=1,480,222 width=332) (actual rows= loops=)

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

Hash Left Join (cost=1,342,095.54..1,619,305.43 rows=1,480,222 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

Hash Left Join (cost=1,342,094.34..1,605,686.19 rows=1,480,222 width=328) (actual rows= loops=)

  • Hash Cond: (te.id = te_timeofftype.timeentryid)
7. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,342,085.75..1,600,126.76 rows=1,480,222 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

Hash Left Join (cost=1,342,084.70..1,587,025.75 rows=1,480,222 width=312) (actual rows= loops=)

  • Hash Cond: (te.id = te_breaktype.timeentryid)
9. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=1,342,076.12..1,581,466.32 rows=1,480,222 width=255) (actual rows= loops=)

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

Bitmap Heap Scan on timeentrymetadata te_billingrate (cost=66,662.39..214,159.97 rows=1,310,172 width=73) (actual rows= loops=)

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

Bitmap Index Scan on ixtem2key (cost=0.00..66,334.85 rows=1,310,172 width=0) (actual rows= loops=)

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

Hash (cost=1,216,435.95..1,216,435.95 rows=1,480,222 width=198) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=842,097.49..1,216,435.95 rows=1,480,222 width=198) (actual rows= loops=)

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

Hash Right Join (cost=838,216.98..1,083,274.29 rows=1,480,222 width=251) (actual rows= loops=)

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

Bitmap Heap Scan on timeentrymetadata te_task (cost=71,976.26..221,041.32 rows=1,414,671 width=73) (actual rows= loops=)

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

Bitmap Index Scan on ixtem2key (cost=0.00..71,622.59 rows=1,414,671 width=0) (actual rows= loops=)

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

Hash (cost=707,262.95..707,262.95 rows=1,480,222 width=194) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=321,030.76..707,262.95 rows=1,480,222 width=194) (actual rows= loops=)

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

Hash Left Join (cost=320,866.39..679,344.42 rows=1,480,222 width=243) (actual rows= loops=)

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

Hash Left Join (cost=320,865.28..666,021.30 rows=1,480,222 width=296) (actual rows= loops=)

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

Hash Join (cost=320,856.69..660,461.87 rows=1,480,222 width=239) (actual rows= loops=)

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

Nested Loop (cost=320,816.33..640,068.46 rows=1,480,222 width=225) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

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

24. 0.000 0.000 ↓ 0.0

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

25. 0.000 0.000 ↓ 0.0

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

26. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=320,816.33..625,264.21 rows=1,480,222 width=160) (actual rows= loops=)

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

Seq Scan on timeentrymetadata te_project (cost=0.00..223,617.83 rows=1,480,222 width=73) (actual rows= loops=)

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

Hash (cost=279,608.93..279,608.93 rows=1,465,152 width=103) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=100,528.44..279,608.93 rows=1,465,152 width=103) (actual rows= loops=)

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

Bitmap Heap Scan on timeentrymetadata te_comment (cost=35,952.64..174,396.50 rows=706,591 width=59) (actual rows= loops=)

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

Bitmap Index Scan on ixtem2key (cost=0.00..35,775.99 rows=706,591 width=0) (actual rows= loops=)

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

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

33. 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))
34. 0.000 0.000 ↓ 0.0

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

35. 0.000 0.000 ↓ 0.0

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

36. 0.000 0.000 ↓ 0.0

Hash (cost=8.57..8.57 rows=1 width=73) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Index Scan using ixtem2key on timeentrymetadata te_activity (cost=0.56..8.57 rows=1 width=73) (actual rows= loops=)

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

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

39. 0.000 0.000 ↓ 0.0

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

40. 0.000 0.000 ↓ 0.0

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

41. 0.000 0.000 ↓ 0.0

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

42. 0.000 0.000 ↓ 0.0

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

43. 0.000 0.000 ↓ 0.0

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

44. 0.000 0.000 ↓ 0.0

Hash (cost=8.57..8.57 rows=1 width=73) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Index Scan using ixtem2key on timeentrymetadata te_breaktype (cost=0.56..8.57 rows=1 width=73) (actual rows= loops=)

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

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

47. 0.000 0.000 ↓ 0.0

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

48. 0.000 0.000 ↓ 0.0

Hash (cost=8.57..8.57 rows=1 width=73) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

Index Scan using ixtem2key on timeentrymetadata te_timeofftype (cost=0.56..8.57 rows=1 width=73) (actual rows= loops=)

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

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

51. 0.000 0.000 ↓ 0.0

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

52. 0.000 0.000 ↓ 0.0

Hash (cost=8.57..8.57 rows=1 width=73) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

Index Scan using ixtem2key on timeentrymetadata te_client (cost=0.56..8.57 rows=1 width=73) (actual rows= loops=)

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

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

55. 0.000 0.000 ↓ 0.0

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

56. 0.000 0.000 ↓ 0.0

Hash (cost=8.57..8.57 rows=1 width=17) (actual rows= loops=)

57. 0.000 0.000 ↓ 0.0

Index Scan using ixtem2key on timeentrymetadata te_isbillable (cost=0.56..8.57 rows=1 width=17) (actual rows= loops=)

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

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

59. 0.000 0.000 ↓ 0.0

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

60. 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=)

61. 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)
62. 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)
63. 0.000 0.000 ↓ 0.0

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

64. 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)
65. 0.000 0.000 ↓ 0.0

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

66. 0.000 0.000 ↓ 0.0

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

67. 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)
68. 0.000 0.000 ↓ 0.0

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

69. 0.000 0.000 ↓ 0.0

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

70. 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)
71. 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)
72. 0.000 0.000 ↓ 0.0

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

73. 0.000 0.000 ↓ 0.0

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

74. 0.000 0.000 ↓ 0.0

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

75. 0.000 0.000 ↓ 0.0

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

76. 0.000 0.000 ↓ 0.0

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

77. 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)
78. 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)