explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cX5l : with index key + timeentryuri

Settings
# exclusive inclusive rows x rows loops node
1. 1,084.996 362,750.511 ↑ 1.0 1,465,153 1

Append (cost=1,342,115.20..2,077,676.20 rows=1,480,242 width=158) (actual time=343,636.115..362,750.511 rows=1,465,153 loops=1)

2. 4,176.806 361,664.285 ↑ 1.0 1,465,152 1

Hash Left Join (cost=1,342,115.20..2,062,725.61 rows=1,480,222 width=280) (actual time=343,636.113..361,664.285 rows=1,465,152 loops=1)

  • Hash Cond: (te.id = te_isbillable.timeentryid)
3. 2,268.478 357,487.438 ↑ 1.0 1,465,152 1

Hash Left Join (cost=1,342,106.61..1,642,704.02 rows=1,480,222 width=279) (actual time=343,631.992..357,487.438 rows=1,465,152 loops=1)

  • Hash Cond: (("substring"(te_client.uri, concat('^urn:replicon-tenant:', si.tenantslug, ':client:(.*)$')))::integer = client.id)
4. 1,261.554 355,218.297 ↑ 1.0 1,465,152 1

Hash Left Join (cost=1,342,104.13..1,624,864.86 rows=1,480,222 width=332) (actual time=343,631.311..355,218.297 rows=1,465,152 loops=1)

  • Hash Cond: (te.id = te_client.timeentryid)
5. 2,247.143 353,956.720 ↑ 1.0 1,465,152 1

Hash Left Join (cost=1,342,095.54..1,619,305.43 rows=1,480,222 width=275) (actual time=343,631.277..353,956.720 rows=1,465,152 loops=1)

  • Hash Cond: (("substring"(te_timeofftype.uri, concat('urn:replicon-tenant:', si.tenantslug, ':time-off-type:(.*)$')))::integer = toc.id)
6. 1,226.040 351,709.553 ↑ 1.0 1,465,152 1

Hash Left Join (cost=1,342,094.34..1,605,686.19 rows=1,480,222 width=328) (actual time=343,631.240..351,709.553 rows=1,465,152 loops=1)

  • Hash Cond: (te.id = te_timeofftype.timeentryid)
7. 2,487.916 350,483.479 ↑ 1.0 1,465,152 1

Hash Left Join (cost=1,342,085.75..1,600,126.76 rows=1,480,222 width=271) (actual time=343,631.198..350,483.479 rows=1,465,152 loops=1)

  • Hash Cond: (("substring"(te_breaktype.uri, concat('^urn:replicon-tenant:', si.tenantslug, ':break-type:(.*)$')))::uuid = bt.id)
8. 1,247.614 347,995.534 ↑ 1.0 1,465,152 1

Hash Left Join (cost=1,342,084.70..1,587,025.75 rows=1,480,222 width=312) (actual time=343,631.150..347,995.534 rows=1,465,152 loops=1)

  • Hash Cond: (te.id = te_breaktype.timeentryid)
9. 2,368.070 346,747.876 ↑ 1.0 1,465,152 1

Hash Right Join (cost=1,342,076.12..1,581,466.32 rows=1,480,222 width=255) (actual time=343,631.093..346,747.876 rows=1,465,152 loops=1)

  • Hash Cond: (te_billingrate.timeentryid = te.id)
10. 754.283 963.461 ↑ 1.0 1,296,802 1

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

  • Recheck Cond: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:BILLING-RATE'::text)
  • Heap Blocks: exact=27126
11. 209.178 209.178 ↑ 1.0 1,296,802 1

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

  • Index Cond: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:BILLING-RATE'::text)
12. 1,441.920 343,416.345 ↑ 1.0 1,465,152 1

Hash (cost=1,216,435.95..1,216,435.95 rows=1,480,222 width=198) (actual time=343,416.345..343,416.345 rows=1,465,152 loops=1)

  • Buckets: 32768 Batches: 128 Memory Usage: 2404kB
13. 155,598.653 341,974.425 ↑ 1.0 1,465,152 1

Hash Left Join (cost=842,097.49..1,216,435.95 rows=1,480,222 width=198) (actual time=181,538.437..341,974.425 rows=1,465,152 loops=1)

  • Hash Cond: (("substring"(te_task.uri, concat('^urn:replicon-tenant:', si.tenantslug, ':task:(.*)$')))::integer = task.id)
14. 2,682.665 186,243.424 ↑ 1.0 1,465,152 1

Hash Right Join (cost=838,216.98..1,083,274.29 rows=1,480,222 width=251) (actual time=181,404.970..186,243.424 rows=1,465,152 loops=1)

  • Hash Cond: (te_task.timeentryid = te.id)
15. 2,163.602 2,417.135 ↓ 1.0 1,426,221 1

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

  • Recheck Cond: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:TASK'::text)
  • Rows Removed by Index Recheck: 608692
  • Heap Blocks: exact=38114 lossy=26518
16. 253.533 253.533 ↓ 1.0 1,426,221 1

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

  • Index Cond: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:TASK'::text)
17. 1,462.985 181,143.624 ↑ 1.0 1,465,152 1

Hash (cost=707,262.95..707,262.95 rows=1,480,222 width=194) (actual time=181,143.624..181,143.624 rows=1,465,152 loops=1)

  • Buckets: 32768 Batches: 128 Memory Usage: 2361kB
18. 159,350.905 179,680.639 ↑ 1.0 1,465,152 1

Hash Left Join (cost=321,030.76..707,262.95 rows=1,480,222 width=194) (actual time=4,216.043..179,680.639 rows=1,465,152 loops=1)

  • Hash Cond: (("substring"(te_project.uri, concat('^urn:replicon-tenant:', si.tenantslug, ':project:(.*)$')))::integer = proj.id)
19. 2,653.958 20,326.600 ↑ 1.0 1,465,152 1

Hash Left Join (cost=320,866.39..679,344.42 rows=1,480,222 width=243) (actual time=4,212.723..20,326.600 rows=1,465,152 loops=1)

  • Hash Cond: (("substring"(te_activity.uri, concat('^urn:replicon-tenant:', si.tenantslug, ':activity:(.*)$')))::integer = act.id)
20. 1,279.020 17,672.073 ↑ 1.0 1,465,152 1

Hash Left Join (cost=320,865.28..666,021.30 rows=1,480,222 width=296) (actual time=4,212.131..17,672.073 rows=1,465,152 loops=1)

  • Hash Cond: (te.id = te_activity.timeentryid)
21. 1,503.747 16,392.998 ↑ 1.0 1,465,152 1

Hash Join (cost=320,856.69..660,461.87 rows=1,480,222 width=239) (actual time=4,212.063..16,392.998 rows=1,465,152 loops=1)

  • Hash Cond: (te.userid = ui.id)
22. 1,195.196 14,888.101 ↑ 1.0 1,465,152 1

Nested Loop (cost=320,816.33..640,068.46 rows=1,480,222 width=225) (actual time=4,210.903..14,888.101 rows=1,465,152 loops=1)

23. 0.006 0.011 ↑ 1.0 1 1

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

24. 0.002 0.002 ↑ 1.0 1 1

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

25. 0.003 0.003 ↑ 1.0 1 1

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

26. 2,560.434 13,692.894 ↑ 1.0 1,465,152 1

Hash Right Join (cost=320,816.33..625,264.21 rows=1,480,222 width=160) (actual time=4,210.893..13,692.894 rows=1,465,152 loops=1)

  • Hash Cond: (te_project.timeentryid = te.id)
27. 6,921.738 6,921.738 ↑ 1.0 1,456,325 1

Seq Scan on timeentrymetadata te_project (cost=0.00..223,617.83 rows=1,480,222 width=73) (actual time=0.051..6,921.738 rows=1,456,325 loops=1)

  • Filter: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:PROJECT'::text)
  • Rows Removed by Filter: 4928530
28. 838.678 4,210.722 ↑ 1.0 1,465,152 1

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

  • Buckets: 32768 Batches: 64 Memory Usage: 2350kB
29. 1,392.529 3,372.044 ↑ 1.0 1,465,152 1

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

  • Hash Cond: (te_comment.timeentryid = te.id)
30. 460.207 588.940 ↓ 1.0 727,165 1

Bitmap Heap Scan on timeentrymetadata te_comment (cost=35,952.64..174,396.50 rows=706,591 width=59) (actual time=131.918..588.940 rows=727,165 loops=1)

  • Recheck Cond: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:COMMENTS'::text)
  • Heap Blocks: exact=17102
31. 128.733 128.733 ↓ 1.0 727,165 1

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

  • Index Cond: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:COMMENTS'::text)
32. 716.614 1,390.575 ↑ 1.0 1,465,152 1

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

  • Buckets: 65536 Batches: 64 Memory Usage: 2110kB
33. 673.961 673.961 ↑ 1.0 1,465,152 1

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

  • Filter: ((timeallocationtype <> 2) OR (timeallocationtype IS NULL))
34. 0.469 1.150 ↑ 1.0 994 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 59kB
35. 0.681 0.681 ↑ 1.0 994 1

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

36. 0.000 0.055 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
37. 0.055 0.055 ↓ 0.0 0 1

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

  • Index Cond: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:ACTIVITY'::text)
38. 0.004 0.569 ↑ 1.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
39. 0.565 0.565 ↑ 1.0 5 1

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

40. 1.246 3.134 ↑ 1.0 2,772 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 141kB
41. 1.888 1.888 ↑ 1.0 2,772 1

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

42. 43.762 132.348 ↑ 1.0 86,689 1

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

  • Buckets: 131072 Batches: 2 Memory Usage: 2550kB
43. 88.586 88.586 ↑ 1.0 86,689 1

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

44. 0.001 0.044 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
45. 0.043 0.043 ↓ 0.0 0 1

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

  • Index Cond: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:BREAK-TYPE'::text)
46. 0.008 0.029 ↑ 1.0 2 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
47. 0.021 0.021 ↑ 1.0 2 1

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

48. 0.002 0.034 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
49. 0.032 0.032 ↓ 0.0 0 1

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

  • Index Cond: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:TIME-OFF-TYPE'::text)
50. 0.011 0.024 ↑ 1.0 9 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
51. 0.013 0.013 ↑ 1.0 9 1

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

52. 0.001 0.023 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
53. 0.022 0.022 ↓ 0.0 0 1

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

  • Index Cond: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:CLIENT'::text)
54. 0.034 0.663 ↑ 1.0 66 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
55. 0.629 0.629 ↑ 1.0 66 1

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

56. 0.002 0.041 ↓ 0.0 0 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
57. 0.039 0.039 ↓ 0.0 0 1

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

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

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

59. 0.002 0.039 ↓ 0.0 0 1

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

60. 0.037 0.037 ↓ 0.0 0 1

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

61. 0.000 0.000 ↓ 0.0 0

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

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

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

  • Index Cond: (at.projectid = id)
63. 0.010 1.173 ↑ 1.0 1 1

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

64. 0.016 1.129 ↑ 1.0 1 1

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

  • Join Filter: (toe.timeoffid = toff.id)
65. 0.601 0.601 ↑ 1.0 1 1

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

66. 0.512 0.512 ↑ 1.0 1 1

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

67. 0.034 0.034 ↑ 1.0 1 1

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

  • Index Cond: (id = toff.userid)
68. 0.001 0.017 ↓ 0.0 0 1

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

69. 0.002 0.016 ↓ 0.0 0 1

Nested Loop (cost=25.67..55.86 rows=18 width=146) (actual time=0.016..0.016 rows=0 loops=1)

70. 0.012 0.014 ↓ 0.0 0 1

Hash Join (cost=25.40..48.37 rows=18 width=132) (actual time=0.014..0.014 rows=0 loops=1)

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

Hash Join (cost=12.70..35.04 rows=120 width=92) (never executed)

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

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

73. 0.000 0.000 ↓ 0.0 0

Hash (cost=11.20..11.20 rows=120 width=72) (never executed)

74. 0.000 0.000 ↓ 0.0 0

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

75. 0.002 0.002 ↓ 0.0 0 1

Hash (cost=11.20..11.20 rows=120 width=72) (actual time=0.002..0.002 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
76. 0.000 0.000 ↓ 0.0 0 1

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

77. 0.000 0.000 ↓ 0.0 0

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

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

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

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