explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EUap

Settings
# exclusive inclusive rows x rows loops node
1. 37,163.756 1,267,575.100 ↑ 690.0 9,296,220 1

Hash Left Join (cost=12,404,110.77..3,107,691,813.74 rows=6,414,391,800 width=280) (actual time=114,950.684..1,267,575.100 rows=9,296,220 loops=1)

  • Hash Cond: (te.id = te_isbillable.timeentryid)
2. 11,875.076 1,226,615.192 ↑ 690.0 9,296,220 1

Hash Left Join (cost=11,499,489.98..1,286,703,512.80 rows=6,414,391,800 width=279) (actual time=110,936.313..1,226,615.192 rows=9,296,220 loops=1)

  • Hash Cond: (("substring"(te_client.uri, (('^urn:replicon-tenant:'::text || si.tenantslug) || ':client:(.*)$'::text)))::integer = client.id)
3. 9,216.759 1,214,734.035 ↑ 690.0 9,296,220 1

Hash Left Join (cost=11,499,352.40..1,158,415,539.22 rows=6,414,391,800 width=335) (actual time=110,930.174..1,214,734.035 rows=9,296,220 loops=1)

  • Hash Cond: (te.id = te_client.timeentryid)
4. 11,326.006 1,201,709.253 ↑ 690.0 9,296,220 1

Hash Left Join (cost=10,594,731.61..1,133,456,942.28 rows=6,414,391,800 width=275) (actual time=107,122.131..1,201,709.253 rows=9,296,220 loops=1)

  • Hash Cond: (("substring"(te_timeofftype.uri, (('^urn:replicon-tenant:'::text || si.tenantslug) || ':time-off-type:(.*)$'::text)))::integer = toc.id)
5. 10,790.347 1,190,382.266 ↑ 690.0 9,296,220 1

Hash Left Join (cost=10,594,729.64..1,055,522,079.94 rows=6,414,391,800 width=331) (actual time=107,121.123..1,190,382.266 rows=9,296,220 loops=1)

  • Hash Cond: (("substring"(te_breaktype.uri, (('^urn:replicon-tenant:'::text || si.tenantslug) || ':break-type:(.*)$'::text)))::uuid = bt.id)
6. 256,243.395 1,179,590.877 ↑ 690.0 9,296,220 1

Hash Left Join (cost=10,594,728.60..990,736,721.72 rows=6,414,391,800 width=375) (actual time=107,120.052..1,179,590.877 rows=9,296,220 loops=1)

  • Hash Cond: (("substring"(te_task.uri, (('^urn:replicon-tenant:'::text || si.tenantslug) || ':task:(.*)$'::text)))::integer = task.id)
7. 673,086.210 923,339.785 ↑ 690.0 9,296,220 1

Hash Left Join (cost=10,594,535.00..862,448,692.12 rows=6,414,391,800 width=431) (actual time=107,112.321..923,339.785 rows=9,296,220 loops=1)

  • Hash Cond: (("substring"(te_project.uri, (('^urn:replicon-tenant:'::text || si.tenantslug) || ':project:(.*)$'::text)))::integer = proj.id)
8. 8,485.642 250,232.847 ↑ 690.0 9,296,220 1

Merge Left Join (cost=10,593,992.53..734,160,313.65 rows=6,414,391,800 width=483) (actual time=107,091.141..250,232.847 rows=9,296,220 loops=1)

  • Merge Cond: (te.id = te_timeofftype.timeentryid)
9. 7,926.195 237,935.369 ↑ 690.0 9,296,220 1

Merge Left Join (cost=9,689,371.74..717,219,703.01 rows=6,414,391,800 width=423) (actual time=103,279.299..237,935.369 rows=9,296,220 loops=1)

  • Merge Cond: (te.id = te_breaktype.timeentryid)
10. 10,466.424 226,191.497 ↑ 690.0 9,296,220 1

Merge Left Join (cost=8,784,750.96..700,279,092.37 rows=6,414,391,800 width=363) (actual time=99,461.617..226,191.497 rows=9,296,220 loops=1)

  • Merge Cond: (te.id = te_billingrate.timeentryid)
11. 10,712.721 201,842.777 ↑ 690.0 9,296,220 1

Merge Left Join (cost=7,438,861.80..659,402,784.00 rows=6,414,391,800 width=303) (actual time=90,688.159..201,842.777 rows=9,296,220 loops=1)

  • Merge Cond: (te.id = te_task.timeentryid)
12. 34,002.664 175,586.442 ↑ 690.0 9,296,220 1

Nested Loop Left Join (cost=6,034,518.39..615,473,550.40 rows=6,414,391,800 width=243) (actual time=81,176.656..175,586.442 rows=9,296,220 loops=1)

  • Join Filter: (("substring"(te_activity.uri, (('^urn:replicon-tenant:'::text || si.tenantslug) || ':activity:(.*)$'::text)))::integer = act.id)
  • Rows Removed by Join Filter: 27888660
13. 13,456.093 132,287.558 ↑ 690.0 9,296,220 1

Nested Loop (cost=6,034,518.39..86,286,225.86 rows=6,414,391,800 width=299) (actual time=81,175.982..132,287.558 rows=9,296,220 loops=1)

14. 7,557.936 109,535.245 ↑ 1.0 9,296,220 1

Merge Left Join (cost=6,034,518.39..6,106,301.82 rows=9,296,220 width=235) (actual time=81,175.939..109,535.245 rows=9,296,220 loops=1)

  • Merge Cond: (te.id = te_activity.timeentryid)
15. 9,545.827 98,164.623 ↑ 1.0 9,296,220 1

Merge Left Join (cost=5,129,897.61..5,178,440.47 rows=9,296,220 width=175) (actual time=77,363.249..98,164.623 rows=9,296,220 loops=1)

  • Merge Cond: (te.id = te_comment.timeentryid)
16. 32,668.581 84,239.643 ↑ 1.0 9,296,220 1

Sort (cost=4,211,324.68..4,234,565.23 rows=9,296,220 width=136) (actual time=73,264.959..84,239.643 rows=9,296,220 loops=1)

  • Sort Key: te.id
  • Sort Method: external merge Disk: 1036776kB
17. 8,658.923 51,571.062 ↑ 1.0 9,296,220 1

Hash Join (cost=402,241.37..1,864,401.29 rows=9,296,220 width=136) (actual time=22,894.308..51,571.062 rows=9,296,220 loops=1)

  • Hash Cond: (te.userid = ui.id)
18. 11,824.687 42,902.693 ↑ 1.0 9,296,220 1

Hash Right Join (cost=401,844.50..1,736,181.39 rows=9,296,220 width=120) (actual time=22,884.842..42,902.693 rows=9,296,220 loops=1)

  • Hash Cond: (te_project.timeentryid = te.id)
19. 21,687.044 21,687.044 ↑ 1.0 6,655,991 1

Seq Scan on timeentrymetadata te_project (cost=0.00..904,620.78 rows=6,712,426 width=76) (actual time=12,858.526..21,687.044 rows=6,655,991 loops=1)

  • Filter: (key = 'urn:replicon:time-entry-metadata-key:project'::text)
  • Rows Removed by Filter: 15777764
20. 4,690.757 9,390.962 ↑ 1.0 9,296,220 1

Hash (cost=185,779.75..185,779.75 rows=9,296,220 width=60) (actual time=9,390.962..9,390.962 rows=9,296,220 loops=1)

  • Buckets: 65536 Batches: 256 Memory Usage: 2794kB
21. 4,700.205 4,700.205 ↑ 1.0 9,296,220 1

Seq Scan on timeentry te (cost=0.00..185,779.75 rows=9,296,220 width=60) (actual time=0.009..4,700.205 rows=9,296,220 loops=1)

  • Filter: ((timeallocationtype <> 2) OR (timeallocationtype IS NULL))
22. 3.730 9.446 ↑ 1.0 7,772 1

Hash (cost=299.72..299.72 rows=7,772 width=20) (actual time=9.446..9.446 rows=7,772 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 471kB
23. 5.716 5.716 ↑ 1.0 7,772 1

Seq Scan on userinfo ui (cost=0.00..299.72 rows=7,772 width=20) (actual time=0.025..5.716 rows=7,772 loops=1)

24. 110.708 4,379.153 ↓ 1.1 130,925 1

Materialize (cost=918,572.93..919,162.00 rows=117,815 width=55) (actual time=4,098.280..4,379.153 rows=130,925 loops=1)

25. 395.704 4,268.445 ↓ 1.1 130,925 1

Sort (cost=918,572.93..918,867.47 rows=117,815 width=55) (actual time=4,098.273..4,268.445 rows=130,925 loops=1)

  • Sort Key: te_comment.timeentryid
  • Sort Method: external merge Disk: 6952kB
26. 3,872.741 3,872.741 ↓ 1.1 130,925 1

Seq Scan on timeentrymetadata te_comment (cost=0.00..904,620.78 rows=117,815 width=55) (actual time=0.051..3,872.741 rows=130,925 loops=1)

  • Filter: (key = 'urn:replicon:time-entry-metadata-key:comments'::text)
  • Rows Removed by Filter: 22302830
27. 0.016 3,812.686 ↓ 0.0 0 1

Sort (cost=904,620.79..904,620.79 rows=1 width=76) (actual time=3,812.686..3,812.686 rows=0 loops=1)

  • Sort Key: te_activity.timeentryid
  • Sort Method: quicksort Memory: 25kB
28. 3,812.670 3,812.670 ↓ 0.0 0 1

Seq Scan on timeentrymetadata te_activity (cost=0.00..904,620.78 rows=1 width=76) (actual time=3,812.670..3,812.670 rows=0 loops=1)

  • Filter: (key = 'urn:replicon:time-entry-metadata-key:activity'::text)
  • Rows Removed by Filter: 22433755
29. 9,296.184 9,296.220 ↑ 690.0 1 9,296,220

Materialize (cost=0.00..28.26 rows=690 width=64) (actual time=0.000..0.001 rows=1 loops=9,296,220)

30. 0.008 0.036 ↑ 690.0 1 1

Nested Loop (cost=0.00..24.81 rows=690 width=64) (actual time=0.032..0.036 rows=1 loops=1)

31. 0.015 0.015 ↑ 1.0 1 1

Seq Scan on systeminformation si (cost=0.00..1.01 rows=1 width=32) (actual time=0.014..0.015 rows=1 loops=1)

32. 0.013 0.013 ↑ 690.0 1 1

Seq Scan on projectsysteminformation psi (cost=0.00..16.90 rows=690 width=32) (actual time=0.011..0.013 rows=1 loops=1)

33. 9,295.576 9,296.220 ↑ 1.0 3 9,296,220

Materialize (cost=0.00..1.04 rows=3 width=4) (actual time=0.000..0.001 rows=3 loops=9,296,220)

34. 0.644 0.644 ↑ 1.0 3 1

Seq Scan on activities act (cost=0.00..1.03 rows=3 width=4) (actual time=0.642..0.644 rows=3 loops=1)

35. 2,183.453 15,543.614 ↓ 1.0 2,593,241 1

Materialize (cost=1,404,343.41..1,417,136.89 rows=2,558,697 width=76) (actual time=9,511.495..15,543.614 rows=2,593,241 loops=1)

36. 8,329.111 13,360.161 ↓ 1.0 2,593,241 1

Sort (cost=1,404,343.41..1,410,740.15 rows=2,558,697 width=76) (actual time=9,511.491..13,360.161 rows=2,593,241 loops=1)

  • Sort Key: te_task.timeentryid
  • Sort Method: external merge Disk: 225048kB
37. 5,031.050 5,031.050 ↓ 1.0 2,593,241 1

Seq Scan on timeentrymetadata te_task (cost=0.00..904,620.78 rows=2,558,697 width=76) (actual time=1,865.565..5,031.050 rows=2,593,241 loops=1)

  • Filter: (key = 'urn:replicon:time-entry-metadata-key:task'::text)
  • Rows Removed by Filter: 19840514
38. 1,806.613 13,882.296 ↑ 1.0 2,229,746 1

Materialize (cost=1,345,889.15..1,357,236.38 rows=2,269,446 width=76) (actual time=8,773.451..13,882.296 rows=2,229,746 loops=1)

39. 7,194.649 12,075.683 ↑ 1.0 2,229,746 1

Sort (cost=1,345,889.15..1,351,562.77 rows=2,269,446 width=76) (actual time=8,773.446..12,075.683 rows=2,229,746 loops=1)

  • Sort Key: te_billingrate.timeentryid
  • Sort Method: external merge Disk: 150448kB
40. 4,881.034 4,881.034 ↑ 1.0 2,229,746 1

Seq Scan on timeentrymetadata te_billingrate (cost=0.00..904,620.78 rows=2,269,446 width=76) (actual time=0.039..4,881.034 rows=2,229,746 loops=1)

  • Filter: (key = 'urn:replicon:time-entry-metadata-key:billing-rate'::text)
  • Rows Removed by Filter: 20204009
41. 0.004 3,817.677 ↓ 0.0 0 1

Materialize (cost=904,620.79..904,620.79 rows=1 width=76) (actual time=3,817.677..3,817.677 rows=0 loops=1)

42. 0.015 3,817.673 ↓ 0.0 0 1

Sort (cost=904,620.79..904,620.79 rows=1 width=76) (actual time=3,817.673..3,817.673 rows=0 loops=1)

  • Sort Key: te_breaktype.timeentryid
  • Sort Method: quicksort Memory: 25kB
43. 3,817.658 3,817.658 ↓ 0.0 0 1

Seq Scan on timeentrymetadata te_breaktype (cost=0.00..904,620.78 rows=1 width=76) (actual time=3,817.658..3,817.658 rows=0 loops=1)

  • Filter: (key = 'urn:replicon:time-entry-metadata-key:break-type'::text)
  • Rows Removed by Filter: 22433755
44. 0.006 3,811.836 ↓ 0.0 0 1

Materialize (cost=904,620.79..904,620.79 rows=1 width=76) (actual time=3,811.836..3,811.836 rows=0 loops=1)

45. 0.014 3,811.830 ↓ 0.0 0 1

Sort (cost=904,620.79..904,620.79 rows=1 width=76) (actual time=3,811.830..3,811.830 rows=0 loops=1)

  • Sort Key: te_timeofftype.timeentryid
  • Sort Method: quicksort Memory: 25kB
46. 3,811.816 3,811.816 ↓ 0.0 0 1

Seq Scan on timeentrymetadata te_timeofftype (cost=0.00..904,620.78 rows=1 width=76) (actual time=3,811.816..3,811.816 rows=0 loops=1)

  • Filter: (key = 'urn:replicon:time-entry-metadata-key:time-off-type'::text)
  • Rows Removed by Filter: 22433755
47. 5.253 20.728 ↑ 1.0 11,221 1

Hash (cost=402.21..402.21 rows=11,221 width=8) (actual time=20.728..20.728 rows=11,221 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 567kB
48. 15.475 15.475 ↑ 1.0 11,221 1

Seq Scan on project proj (cost=0.00..402.21 rows=11,221 width=8) (actual time=0.571..15.475 rows=11,221 loops=1)

49. 2.429 7.697 ↑ 1.0 5,360 1

Hash (cost=126.60..126.60 rows=5,360 width=4) (actual time=7.697..7.697 rows=5,360 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 253kB
50. 5.268 5.268 ↑ 1.0 5,360 1

Seq Scan on task (cost=0.00..126.60 rows=5,360 width=4) (actual time=0.547..5.268 rows=5,360 loops=1)

51. 0.006 1.042 ↑ 1.0 2 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
52. 1.036 1.036 ↑ 1.0 2 1

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

53. 0.026 0.981 ↑ 1.0 43 1

Hash (cost=1.43..1.43 rows=43 width=4) (actual time=0.981..0.981 rows=43 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
54. 0.955 0.955 ↑ 1.0 43 1

Seq Scan on timeoffcode toc (cost=0.00..1.43 rows=43 width=4) (actual time=0.938..0.955 rows=43 loops=1)

55. 0.002 3,808.023 ↓ 0.0 0 1

Hash (cost=904,620.78..904,620.78 rows=1 width=76) (actual time=3,808.023..3,808.023 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
56. 3,808.021 3,808.021 ↓ 0.0 0 1

Seq Scan on timeentrymetadata te_client (cost=0.00..904,620.78 rows=1 width=76) (actual time=3,808.021..3,808.021 rows=0 loops=1)

  • Filter: (key = 'urn:replicon:time-entry-metadata-key:client'::text)
  • Rows Removed by Filter: 22433755
57. 1.587 6.081 ↑ 1.0 3,448 1

Hash (cost=94.48..94.48 rows=3,448 width=4) (actual time=6.081..6.081 rows=3,448 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 154kB
58. 4.494 4.494 ↑ 1.0 3,448 1

Seq Scan on clients client (cost=0.00..94.48 rows=3,448 width=4) (actual time=0.687..4.494 rows=3,448 loops=1)

59. 0.002 3,796.152 ↓ 0.0 0 1

Hash (cost=904,620.78..904,620.78 rows=1 width=17) (actual time=3,796.152..3,796.152 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
60. 3,796.150 3,796.150 ↓ 0.0 0 1

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

  • Filter: (key = 'urn:replicon:time-entry-metadata-key:is-billable'::text)
  • Rows Removed by Filter: 22433755
Planning time : 3.876 ms
Execution time : 1,271,458.483 ms