github-starred/gitea
2
0
Fork 0
mirror of https://github.com/go-gitea/gitea.git synced 2026-03-21 14:06:26 -05:00
Code Issues 2.6k Packages Projects Releases 100 Wiki Activity

Index Page Incredibly Slow to Load - Slow SQL Query #13523

New Issue
Closed
opened 2025-11-02 10:45:02 -06:00 by GiteaMirror · 7 comments
GiteaMirror commented 2025-11-02 10:45:02 -06:00
Owner
Copy Link

Originally created by @davidsenk on GitHub (Sep 23, 2024).

Description

Recently on my Gitea instance, loading the index page started taking an absolute eternity, 30 seconds+ quite often. This is a bug report / analysis as to why.

I took a look in the logs for the container and noticed this:

2024/09/23 17:16:08 ...eb/routing/logger.go:102:func1() [I] router: completed GET /user/events for 10.250.250.11:0, 200 OK in 292770.1ms @ events/events.go:18(events.Events)
2024/09/23 17:16:11 ...eb/routing/logger.go:68:func1() [W] router: slow      GET / for 10.250.250.11:0, elapsed 3380.0ms @ web/home.go:32(web.Home)
2024/09/23 17:16:29 ...activities/action.go:461:GetFeeds() [W] [Slow SQL Query] SELECT `action`.* FROM `action` INNER JOIN `repository` ON `repository`.id = `action`.repo_id WHERE user_id=? AND is_deleted=? ORDER BY `action`.`created_unix` DESC LIMIT 20 [1 false] - 22.823196669s

Hmm... that's odd. Why would that query be so slow?

Running similar query against database using my actual user_id=2 and is_deleted = 0 returns in 0.006 seconds

MariaDB [gitea]> analyze select action.* from action inner join repository on repository.id = action.repo_id where user_id=3 and is_deleted=0 order by action.created_unix desc limit 20;
+------+-------------+------------+--------+-------------------------------------+--------------------+---------+----------------------+------+--------+----------+------------+-----------------------------+
| id   | select_type | table      | type   | possible_keys                       | key                | key_len | ref                  | rows | r_rows | filtered | r_filtered | Extra                       |
+------+-------------+------------+--------+-------------------------------------+--------------------+---------+----------------------+------+--------+----------+------------+-----------------------------+
|    1 | SIMPLE      | action     | ref    | IDX_action_user_id,IDX_action_r_u_d | IDX_action_user_id | 9       | const                | 340  | 20.00  |   100.00 |     100.00 | Using where; Using filesort |
|    1 | SIMPLE      | repository | eq_ref | PRIMARY                             | PRIMARY            | 8       | gitea.action.repo_id | 1    | 1.00   |   100.00 |     100.00 | Using index                 |
+------+-------------+------------+--------+-------------------------------------+--------------------+---------+----------------------+------+--------+----------+------------+-----------------------------+
2 rows in set (0.006 sec)

Strange... similar query gets results quite quickly.

Let's try removing the where...

Wow! That makes the query take significantly longer!

MariaDB [gitea]> analyze select action.* from action inner join repository on repository.id = action.repo_id order by action.created_unix desc limit 20;
+------+-------------+------------+-------+------------------+----------------------------+---------+---------------------+------+---------+----------+------------+----------------------------------------------+
| id   | select_type | table      | type  | possible_keys    | key                        | key_len | ref                 | rows | r_rows  | filtered | r_filtered | Extra                                        |
+------+-------------+------------+-------+------------------+----------------------------+---------+---------------------+------+---------+----------+------------+----------------------------------------------+
|    1 | SIMPLE      | repository | index | PRIMARY          | IDX_repository_is_template | 1       | NULL                | 60   | 60.00   |   100.00 |     100.00 | Using index; Using temporary; Using filesort |
|    1 | SIMPLE      | action     | ref   | IDX_action_r_u_d | IDX_action_r_u_d           | 9       | gitea.repository.id | 398  | 1437.77 |   100.00 |     100.00 |                                              |
+------+-------------+------------+-------+------------------+----------------------------+---------+---------------------+------+---------+----------+------------+----------------------------------------------+
2 rows in set (45.996 sec)

Output from the actual select statement:


MariaDB [gitea]> select action.* from action inner join repository on repository.id = action.repo_id order by action.created_unix desc limit 20;
+-------+---------+---------+-------------+---------+------------+------------+----------+------------+--------------------------+--------------+
| id    | user_id | op_type | act_user_id | repo_id | comment_id | is_deleted | ref_name | is_private | content                  | created_unix |
+-------+---------+---------+-------------+---------+------------+------------+----------+------------+--------------------------+--------------+
| 86272 |       1 |       8 |           1 |      40 |          0 |          0 |          |          0 | Removed for Report       |   1727109844 |
| 86274 |       7 |       8 |           1 |      40 |          0 |          0 |          |          0 | Removed for Report       |   1727109844 |
| 86273 |       8 |       8 |           1 |      40 |          0 |          0 |          |          0 | Removed for Report       |   1727109844 |
| 86269 |       1 |       8 |           1 |      42 |          0 |          0 |          |          0 | Removed for Report       |   1727109836 |
| 86271 |       7 |       8 |           1 |      42 |          0 |          0 |          |          0 | Removed for Report       |   1727109836 |
| 86270 |       8 |       8 |           1 |      42 |          0 |          0 |          |          0 | Removed for Report       |   1727109836 |
| 86266 |       1 |       8 |           1 |      46 |          0 |          0 |          |          0 | Removed for Report       |   1727109821 |
| 86268 |       7 |       8 |           1 |      46 |          0 |          0 |          |          0 | Removed for Report       |   1727109821 |
| 86267 |       8 |       8 |           1 |      46 |          0 |          0 |          |          0 | Removed for Report       |   1727109821 |
| 86263 |       1 |       8 |           1 |      48 |          0 |          0 |          |          0 | Removed for Report       |   1727109811 |
| 86265 |       7 |       8 |           1 |      48 |          0 |          0 |          |          0 | Removed for Report       |   1727109811 |
| 86264 |       8 |       8 |           1 |      48 |          0 |          0 |          |          0 | Removed for Report       |   1727109811 |
| 86260 |       1 |       8 |           1 |      64 |          0 |          0 |          |          0 | Removed for Report       |   1727109803 |
| 86262 |       7 |       8 |           1 |      64 |          0 |          0 |          |          0 | Removed for Report       |   1727109803 |
| 86261 |       8 |       8 |           1 |      64 |          0 |          0 |          |          0 | Removed for Report       |   1727109803 |
| 86257 |       1 |       8 |           1 |      39 |          0 |          0 |          |          0 | Removed for Report       |   1727109795 |
| 86259 |       7 |       8 |           1 |      39 |          0 |          0 |          |          0 | Removed for Report       |   1727109795 |
| 86258 |       8 |       8 |           1 |      39 |          0 |          0 |          |          0 | Removed for Report       |   1727109795 |
| 86254 |       1 |       8 |           1 |      67 |          0 |          0 |          |          0 | Removed for Report       |   1727109786 |
| 86256 |       7 |       8 |           1 |      67 |          0 |          0 |          |          0 | Removed for Report       |   1727109786 |
+-------+---------+---------+-------------+---------+------------+------------+----------+------------+--------------------------+--------------+
20 rows in set (38.802 sec)

Changing the select to remove the inner join:


MariaDB [gitea]> select * from action order by created_unix desc limit 20;
+-------+---------+---------+-------------+---------+------------+------------+----------+------------+--------------------------+--------------+
| id    | user_id | op_type | act_user_id | repo_id | comment_id | is_deleted | ref_name | is_private | content                  | created_unix |
+-------+---------+---------+-------------+---------+------------+------------+----------+------------+--------------------------+--------------+
| 86273 |       8 |       8 |           1 |      40 |          0 |          0 |          |          0 | Removed for Report       |   1727109844 |
| 86274 |       7 |       8 |           1 |      40 |          0 |          0 |          |          0 | Removed for Report       |   1727109844 |
| 86272 |       1 |       8 |           1 |      40 |          0 |          0 |          |          0 | Removed for Report       |   1727109844 |
| 86270 |       8 |       8 |           1 |      42 |          0 |          0 |          |          0 | Removed for Report       |   1727109836 |
| 86271 |       7 |       8 |           1 |      42 |          0 |          0 |          |          0 | Removed for Report       |   1727109836 |
| 86269 |       1 |       8 |           1 |      42 |          0 |          0 |          |          0 | Removed for Report       |   1727109836 |
| 86267 |       8 |       8 |           1 |      46 |          0 |          0 |          |          0 | Removed for Report       |   1727109821 |
| 86268 |       7 |       8 |           1 |      46 |          0 |          0 |          |          0 | Removed for Report       |   1727109821 |
| 86266 |       1 |       8 |           1 |      46 |          0 |          0 |          |          0 | Removed for Report       |   1727109821 |
| 86264 |       8 |       8 |           1 |      48 |          0 |          0 |          |          0 | Removed for Report       |   1727109811 |
| 86265 |       7 |       8 |           1 |      48 |          0 |          0 |          |          0 | Removed for Report       |   1727109811 |
| 86263 |       1 |       8 |           1 |      48 |          0 |          0 |          |          0 | Removed for Report       |   1727109811 |
| 86261 |       8 |       8 |           1 |      64 |          0 |          0 |          |          0 | Removed for Report       |   1727109803 |
| 86262 |       7 |       8 |           1 |      64 |          0 |          0 |          |          0 | Removed for Report       |   1727109803 |
| 86260 |       1 |       8 |           1 |      64 |          0 |          0 |          |          0 | Removed for Report       |   1727109803 |
| 86258 |       8 |       8 |           1 |      39 |          0 |          0 |          |          0 | Removed for Report       |   1727109795 |
| 86259 |       7 |       8 |           1 |      39 |          0 |          0 |          |          0 | Removed for Report       |   1727109795 |
| 86257 |       1 |       8 |           1 |      39 |          0 |          0 |          |          0 | Removed for Report       |   1727109795 |
| 86255 |       8 |       8 |           1 |      67 |          0 |          0 |          |          0 | Removed for Report       |   1727109786 |
| 86256 |       7 |       8 |           1 |      67 |          0 |          0 |          |          0 | Removed for Report       |   1727109786 |
+-------+---------+---------+-------------+---------+------------+------------+----------+------------+--------------------------+--------------+
20 rows in set (0.003 sec)

From reviewing the source code, the columns for repository are intentionally ignored. e1f0598c8f/models/activities/action.go (L456-L459)

With this knowledge, I was able to build a significantly faster query that achieves the same thing (only getting actions for repositories that exist in the repository table):


MariaDB [gitea]> select action.* from action where repo_id not in (select unique(repo_id) from action where repo_id not in (select id from repository)) order by action.created_unix desc limit 20;
+-------+---------+---------+-------------+---------+------------+------------+----------+------------+--------------------------+--------------+
| id    | user_id | op_type | act_user_id | repo_id | comment_id | is_deleted | ref_name | is_private | content                  | created_unix |
+-------+---------+---------+-------------+---------+------------+------------+----------+------------+--------------------------+--------------+
| 86273 |       8 |       8 |           1 |      40 |          0 |          0 |          |          0 | Removed for Report       |   1727109844 |
| 86274 |       7 |       8 |           1 |      40 |          0 |          0 |          |          0 | Removed for Report       |   1727109844 |
| 86272 |       1 |       8 |           1 |      40 |          0 |          0 |          |          0 | Removed for Report       |   1727109844 |
| 86270 |       8 |       8 |           1 |      42 |          0 |          0 |          |          0 | Removed for Report       |   1727109836 |
| 86271 |       7 |       8 |           1 |      42 |          0 |          0 |          |          0 | Removed for Report       |   1727109836 |
| 86269 |       1 |       8 |           1 |      42 |          0 |          0 |          |          0 | Removed for Report       |   1727109836 |
| 86267 |       8 |       8 |           1 |      46 |          0 |          0 |          |          0 | Removed for Report       |   1727109821 |
| 86268 |       7 |       8 |           1 |      46 |          0 |          0 |          |          0 | Removed for Report       |   1727109821 |
| 86266 |       1 |       8 |           1 |      46 |          0 |          0 |          |          0 | Removed for Report       |   1727109821 |
| 86264 |       8 |       8 |           1 |      48 |          0 |          0 |          |          0 | Removed for Report       |   1727109811 |
| 86265 |       7 |       8 |           1 |      48 |          0 |          0 |          |          0 | Removed for Report       |   1727109811 |
| 86263 |       1 |       8 |           1 |      48 |          0 |          0 |          |          0 | Removed for Report       |   1727109811 |
| 86261 |       8 |       8 |           1 |      64 |          0 |          0 |          |          0 | Removed for Report       |   1727109803 |
| 86262 |       7 |       8 |           1 |      64 |          0 |          0 |          |          0 | Removed for Report       |   1727109803 |
| 86260 |       1 |       8 |           1 |      64 |          0 |          0 |          |          0 | Removed for Report       |   1727109803 |
| 86258 |       8 |       8 |           1 |      39 |          0 |          0 |          |          0 | Removed for Report       |   1727109795 |
| 86259 |       7 |       8 |           1 |      39 |          0 |          0 |          |          0 | Removed for Report       |   1727109795 |
| 86257 |       1 |       8 |           1 |      39 |          0 |          0 |          |          0 | Removed for Report       |   1727109795 |
| 86255 |       8 |       8 |           1 |      67 |          0 |          0 |          |          0 | Removed for Report       |   1727109786 |
| 86256 |       7 |       8 |           1 |      67 |          0 |          0 |          |          0 | Removed for Report       |   1727109786 |
+-------+---------+---------+-------------+---------+------------+------------+----------+------------+--------------------------+--------------+
20 rows in set (0.028 sec)

Look at that! 0.028 seconds! Much better! But wait? What in the world is that query? Let me break it down a bit:

The original query is completing an "inner join" for the apparently sole purpose of determing if the actions belong to repositories that currently exist.

The first thing I tried was to change it from an inner join to a subquery:

MariaDB [gitea]> analyze select action.* from action where repo_id in (select id from repository) order by action.created_unix desc limit 20;
+------+-------------+------------+-------+------------------+----------------------------+---------+---------------------+------+---------+----------+------------+----------------------------------------------+
| id   | select_type | table      | type  | possible_keys    | key                        | key_len | ref                 | rows | r_rows  | filtered | r_filtered | Extra                                        |
+------+-------------+------------+-------+------------------+----------------------------+---------+---------------------+------+---------+----------+------------+----------------------------------------------+
|    1 | PRIMARY     | repository | index | PRIMARY          | IDX_repository_is_template | 1       | NULL                | 60   | 60.00   |   100.00 |     100.00 | Using index; Using temporary; Using filesort |
|    1 | PRIMARY     | action     | ref   | IDX_action_r_u_d | IDX_action_r_u_d           | 9       | gitea.repository.id | 398  | 1437.77 |   100.00 |     100.00 |                                              |
+------+-------------+------------+-------+------------------+----------------------------+---------+---------------------+------+---------+----------+------------+----------------------------------------------+
2 rows in set (42.362 sec)

Unfortunately... this is also just as slow...

So thinking about it... how do you make it faster? What are we trying to do? Exclude actions that have a repo_id that is not in repositories right?

Let's take a look at the queries we just used above!

MariaDB [gitea]> select unique(repo_id) from action;
+---------+
| repo_id |
+---------+
|       1 |
|  ...    |
|      70 |
+---------+
55 rows in set (0.001 sec)


MariaDB [gitea]> select id from repository;
+-----+
|  id |
+-----+
|   1 |
| ... |
|  70 |
+-----+
60 rows in set (0.000 sec)

Ok, both of those look to be pretty similar, but what's happening is MariaDB needs to compare all of the ids against the "select id from repository" part of the subquery.
This is likely the "same thing" that MariaDB is doing when completing the above inner join.

So how do you make this faster?

Let's flip it around and only get the repo_ids of actions that are not in the repository table!

MariaDB [gitea]> select unique(repo_id) from action where repo_id not in (select id from repository);
Empty set (0.001 sec)

Neat! For me it's an empty set! (and it generally should be; however, repo_id is not a foreign key in the current schema)

Now, when you do a select against that subquery, it is much faster to execute as there is "less" that needs to be scanned for each row.

Bringing us back to this...


MariaDB [gitea]> select action.* from action where repo_id not in (select unique(repo_id) from action where repo_id not in (select id from repository)) order by action.created_unix desc limit 20;
+-------+---------+---------+-------------+---------+------------+------------+----------+------------+--------------------------+--------------+
| id    | user_id | op_type | act_user_id | repo_id | comment_id | is_deleted | ref_name | is_private | content                  | created_unix |
+-------+---------+---------+-------------+---------+------------+------------+----------+------------+--------------------------+--------------+
| 86273 |       8 |       8 |           1 |      40 |          0 |          0 |          |          0 | Removed for Report       |   1727109844 |
| 86274 |       7 |       8 |           1 |      40 |          0 |          0 |          |          0 | Removed for Report       |   1727109844 |
| 86272 |       1 |       8 |           1 |      40 |          0 |          0 |          |          0 | Removed for Report       |   1727109844 |
| 86270 |       8 |       8 |           1 |      42 |          0 |          0 |          |          0 | Removed for Report       |   1727109836 |
| 86271 |       7 |       8 |           1 |      42 |          0 |          0 |          |          0 | Removed for Report       |   1727109836 |
| 86269 |       1 |       8 |           1 |      42 |          0 |          0 |          |          0 | Removed for Report       |   1727109836 |
| 86267 |       8 |       8 |           1 |      46 |          0 |          0 |          |          0 | Removed for Report       |   1727109821 |
| 86268 |       7 |       8 |           1 |      46 |          0 |          0 |          |          0 | Removed for Report       |   1727109821 |
| 86266 |       1 |       8 |           1 |      46 |          0 |          0 |          |          0 | Removed for Report       |   1727109821 |
| 86264 |       8 |       8 |           1 |      48 |          0 |          0 |          |          0 | Removed for Report       |   1727109811 |
| 86265 |       7 |       8 |           1 |      48 |          0 |          0 |          |          0 | Removed for Report       |   1727109811 |
| 86263 |       1 |       8 |           1 |      48 |          0 |          0 |          |          0 | Removed for Report       |   1727109811 |
| 86261 |       8 |       8 |           1 |      64 |          0 |          0 |          |          0 | Removed for Report       |   1727109803 |
| 86262 |       7 |       8 |           1 |      64 |          0 |          0 |          |          0 | Removed for Report       |   1727109803 |
| 86260 |       1 |       8 |           1 |      64 |          0 |          0 |          |          0 | Removed for Report       |   1727109803 |
| 86258 |       8 |       8 |           1 |      39 |          0 |          0 |          |          0 | Removed for Report       |   1727109795 |
| 86259 |       7 |       8 |           1 |      39 |          0 |          0 |          |          0 | Removed for Report       |   1727109795 |
| 86257 |       1 |       8 |           1 |      39 |          0 |          0 |          |          0 | Removed for Report       |   1727109795 |
| 86255 |       8 |       8 |           1 |      67 |          0 |          0 |          |          0 | Removed for Report       |   1727109786 |
| 86256 |       7 |       8 |           1 |      67 |          0 |          0 |          |          0 | Removed for Report       |   1727109786 |
+-------+---------+---------+-------------+---------+------------+------------+----------+------------+--------------------------+--------------+
20 rows in set (0.028 sec)

That is where the odd looking subquery comes from; however, it leads to significantly faster queries that still maintain the "same" restrictions
as the existing inner join!

Finally, let's add user_id=2 and is_deleted=0 back just for a "apples to apples" comparison with original query:

MariaDB [gitea]> analyze select action.* from action where repo_id not in (select unique(repo_id) from action where repo_id not in (select id from repository)) and user_id=2 and is_deleted=0 order by action.created_unix desc limit 20;
+------+--------------+------------+----------------+--------------------+----------------------------+---------+-------+------+--------+----------+------------+-------------------------------------------------+
| id   | select_type  | table      | type           | possible_keys      | key                        | key_len | ref   | rows | r_rows | filtered | r_filtered | Extra                                           |
+------+--------------+------------+----------------+--------------------+----------------------------+---------+-------+------+--------+----------+------------+-------------------------------------------------+
|    1 | PRIMARY      | action     | ref            | IDX_action_user_id | IDX_action_user_id         | 9       | const | 186  | 20.00  |   100.00 |     100.00 | Using where; Using filesort                     |
|    2 | SUBQUERY     | action     | index_subquery | IDX_action_r_u_d   | IDX_action_r_u_d           | 9       | func  | 796  | NULL   |   100.00 |       NULL | Using index; Using where; Full scan on NULL key |
|    3 | MATERIALIZED | repository | index          | PRIMARY            | IDX_repository_is_template | 1       | NULL  | 60   | 60.00  |   100.00 |     100.00 | Using index                                     |
+------+--------------+------------+----------------+--------------------+----------------------------+---------+-------+------+--------+----------+------------+-------------------------------------------------+
3 rows in set (0.002 sec)

Neat! Just as fast (0.002 seconds), I'm omitting the actual output from this query as it's just an absolute load of json in the content column, but otherwise looks similar to above samples.

I'd test changing the query in the source code; however, I do not have a Go development environment currently setup. I do hope this knowledge can be incorporated into the next version update for faster index page loads!

Thanks!

Gitea Version

1.22.2

Can you reproduce the bug on the Gitea demo site?

No

Log Gist

No response

Screenshots

No response

Git Version

No response

Operating System

AlmaLinux 9.4 - Official Gitea container

How are you running Gitea?

Official gitea container, running on podman:

REPOSITORY                 TAG         IMAGE ID      CREATED       SIZE
docker.io/gitea/gitea      latest      f5b290987d7d  2 weeks ago   168 MB

Database

MySQL/MariaDB

Originally created by @davidsenk on GitHub (Sep 23, 2024). ### Description Recently on my Gitea instance, loading the index page started taking an *absolute* eternity, 30 seconds+ quite often. This is a bug report / analysis as to why. I took a look in the logs for the container and noticed this: ``` 2024/09/23 17:16:08 ...eb/routing/logger.go:102:func1() [I] router: completed GET /user/events for 10.250.250.11:0, 200 OK in 292770.1ms @ events/events.go:18(events.Events) 2024/09/23 17:16:11 ...eb/routing/logger.go:68:func1() [W] router: slow GET / for 10.250.250.11:0, elapsed 3380.0ms @ web/home.go:32(web.Home) 2024/09/23 17:16:29 ...activities/action.go:461:GetFeeds() [W] [Slow SQL Query] SELECT `action`.* FROM `action` INNER JOIN `repository` ON `repository`.id = `action`.repo_id WHERE user_id=? AND is_deleted=? ORDER BY `action`.`created_unix` DESC LIMIT 20 [1 false] - 22.823196669s ``` Hmm... that's odd. Why would that query be so slow? Running similar query against database using my actual user_id=2 and is_deleted = 0 returns in 0.006 seconds ``` MariaDB [gitea]> analyze select action.* from action inner join repository on repository.id = action.repo_id where user_id=3 and is_deleted=0 order by action.created_unix desc limit 20; +------+-------------+------------+--------+-------------------------------------+--------------------+---------+----------------------+------+--------+----------+------------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+------------+--------+-------------------------------------+--------------------+---------+----------------------+------+--------+----------+------------+-----------------------------+ | 1 | SIMPLE | action | ref | IDX_action_user_id,IDX_action_r_u_d | IDX_action_user_id | 9 | const | 340 | 20.00 | 100.00 | 100.00 | Using where; Using filesort | | 1 | SIMPLE | repository | eq_ref | PRIMARY | PRIMARY | 8 | gitea.action.repo_id | 1 | 1.00 | 100.00 | 100.00 | Using index | +------+-------------+------------+--------+-------------------------------------+--------------------+---------+----------------------+------+--------+----------+------------+-----------------------------+ 2 rows in set (0.006 sec) ``` Strange... similar query gets results quite quickly. Let's try removing the where... Wow! That makes the query take significantly longer! ``` MariaDB [gitea]> analyze select action.* from action inner join repository on repository.id = action.repo_id order by action.created_unix desc limit 20; +------+-------------+------------+-------+------------------+----------------------------+---------+---------------------+------+---------+----------+------------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+------------+-------+------------------+----------------------------+---------+---------------------+------+---------+----------+------------+----------------------------------------------+ | 1 | SIMPLE | repository | index | PRIMARY | IDX_repository_is_template | 1 | NULL | 60 | 60.00 | 100.00 | 100.00 | Using index; Using temporary; Using filesort | | 1 | SIMPLE | action | ref | IDX_action_r_u_d | IDX_action_r_u_d | 9 | gitea.repository.id | 398 | 1437.77 | 100.00 | 100.00 | | +------+-------------+------------+-------+------------------+----------------------------+---------+---------------------+------+---------+----------+------------+----------------------------------------------+ 2 rows in set (45.996 sec) ``` Output from the actual select statement: ``` MariaDB [gitea]> select action.* from action inner join repository on repository.id = action.repo_id order by action.created_unix desc limit 20; +-------+---------+---------+-------------+---------+------------+------------+----------+------------+--------------------------+--------------+ | id | user_id | op_type | act_user_id | repo_id | comment_id | is_deleted | ref_name | is_private | content | created_unix | +-------+---------+---------+-------------+---------+------------+------------+----------+------------+--------------------------+--------------+ | 86272 | 1 | 8 | 1 | 40 | 0 | 0 | | 0 | Removed for Report | 1727109844 | | 86274 | 7 | 8 | 1 | 40 | 0 | 0 | | 0 | Removed for Report | 1727109844 | | 86273 | 8 | 8 | 1 | 40 | 0 | 0 | | 0 | Removed for Report | 1727109844 | | 86269 | 1 | 8 | 1 | 42 | 0 | 0 | | 0 | Removed for Report | 1727109836 | | 86271 | 7 | 8 | 1 | 42 | 0 | 0 | | 0 | Removed for Report | 1727109836 | | 86270 | 8 | 8 | 1 | 42 | 0 | 0 | | 0 | Removed for Report | 1727109836 | | 86266 | 1 | 8 | 1 | 46 | 0 | 0 | | 0 | Removed for Report | 1727109821 | | 86268 | 7 | 8 | 1 | 46 | 0 | 0 | | 0 | Removed for Report | 1727109821 | | 86267 | 8 | 8 | 1 | 46 | 0 | 0 | | 0 | Removed for Report | 1727109821 | | 86263 | 1 | 8 | 1 | 48 | 0 | 0 | | 0 | Removed for Report | 1727109811 | | 86265 | 7 | 8 | 1 | 48 | 0 | 0 | | 0 | Removed for Report | 1727109811 | | 86264 | 8 | 8 | 1 | 48 | 0 | 0 | | 0 | Removed for Report | 1727109811 | | 86260 | 1 | 8 | 1 | 64 | 0 | 0 | | 0 | Removed for Report | 1727109803 | | 86262 | 7 | 8 | 1 | 64 | 0 | 0 | | 0 | Removed for Report | 1727109803 | | 86261 | 8 | 8 | 1 | 64 | 0 | 0 | | 0 | Removed for Report | 1727109803 | | 86257 | 1 | 8 | 1 | 39 | 0 | 0 | | 0 | Removed for Report | 1727109795 | | 86259 | 7 | 8 | 1 | 39 | 0 | 0 | | 0 | Removed for Report | 1727109795 | | 86258 | 8 | 8 | 1 | 39 | 0 | 0 | | 0 | Removed for Report | 1727109795 | | 86254 | 1 | 8 | 1 | 67 | 0 | 0 | | 0 | Removed for Report | 1727109786 | | 86256 | 7 | 8 | 1 | 67 | 0 | 0 | | 0 | Removed for Report | 1727109786 | +-------+---------+---------+-------------+---------+------------+------------+----------+------------+--------------------------+--------------+ 20 rows in set (38.802 sec) ``` Changing the select to remove the inner join: ``` MariaDB [gitea]> select * from action order by created_unix desc limit 20; +-------+---------+---------+-------------+---------+------------+------------+----------+------------+--------------------------+--------------+ | id | user_id | op_type | act_user_id | repo_id | comment_id | is_deleted | ref_name | is_private | content | created_unix | +-------+---------+---------+-------------+---------+------------+------------+----------+------------+--------------------------+--------------+ | 86273 | 8 | 8 | 1 | 40 | 0 | 0 | | 0 | Removed for Report | 1727109844 | | 86274 | 7 | 8 | 1 | 40 | 0 | 0 | | 0 | Removed for Report | 1727109844 | | 86272 | 1 | 8 | 1 | 40 | 0 | 0 | | 0 | Removed for Report | 1727109844 | | 86270 | 8 | 8 | 1 | 42 | 0 | 0 | | 0 | Removed for Report | 1727109836 | | 86271 | 7 | 8 | 1 | 42 | 0 | 0 | | 0 | Removed for Report | 1727109836 | | 86269 | 1 | 8 | 1 | 42 | 0 | 0 | | 0 | Removed for Report | 1727109836 | | 86267 | 8 | 8 | 1 | 46 | 0 | 0 | | 0 | Removed for Report | 1727109821 | | 86268 | 7 | 8 | 1 | 46 | 0 | 0 | | 0 | Removed for Report | 1727109821 | | 86266 | 1 | 8 | 1 | 46 | 0 | 0 | | 0 | Removed for Report | 1727109821 | | 86264 | 8 | 8 | 1 | 48 | 0 | 0 | | 0 | Removed for Report | 1727109811 | | 86265 | 7 | 8 | 1 | 48 | 0 | 0 | | 0 | Removed for Report | 1727109811 | | 86263 | 1 | 8 | 1 | 48 | 0 | 0 | | 0 | Removed for Report | 1727109811 | | 86261 | 8 | 8 | 1 | 64 | 0 | 0 | | 0 | Removed for Report | 1727109803 | | 86262 | 7 | 8 | 1 | 64 | 0 | 0 | | 0 | Removed for Report | 1727109803 | | 86260 | 1 | 8 | 1 | 64 | 0 | 0 | | 0 | Removed for Report | 1727109803 | | 86258 | 8 | 8 | 1 | 39 | 0 | 0 | | 0 | Removed for Report | 1727109795 | | 86259 | 7 | 8 | 1 | 39 | 0 | 0 | | 0 | Removed for Report | 1727109795 | | 86257 | 1 | 8 | 1 | 39 | 0 | 0 | | 0 | Removed for Report | 1727109795 | | 86255 | 8 | 8 | 1 | 67 | 0 | 0 | | 0 | Removed for Report | 1727109786 | | 86256 | 7 | 8 | 1 | 67 | 0 | 0 | | 0 | Removed for Report | 1727109786 | +-------+---------+---------+-------------+---------+------------+------------+----------+------------+--------------------------+--------------+ 20 rows in set (0.003 sec) ``` From reviewing the source code, the columns for `repository` are intentionally ignored. https://github.com/go-gitea/gitea/blob/e1f0598c8f5af5ac95f5e13b74fbab99506762db/models/activities/action.go#L456-L459 With this knowledge, I was able to build a *significantly* faster query that achieves the same thing (only getting actions for repositories that exist in the `repository` table): ``` MariaDB [gitea]> select action.* from action where repo_id not in (select unique(repo_id) from action where repo_id not in (select id from repository)) order by action.created_unix desc limit 20; +-------+---------+---------+-------------+---------+------------+------------+----------+------------+--------------------------+--------------+ | id | user_id | op_type | act_user_id | repo_id | comment_id | is_deleted | ref_name | is_private | content | created_unix | +-------+---------+---------+-------------+---------+------------+------------+----------+------------+--------------------------+--------------+ | 86273 | 8 | 8 | 1 | 40 | 0 | 0 | | 0 | Removed for Report | 1727109844 | | 86274 | 7 | 8 | 1 | 40 | 0 | 0 | | 0 | Removed for Report | 1727109844 | | 86272 | 1 | 8 | 1 | 40 | 0 | 0 | | 0 | Removed for Report | 1727109844 | | 86270 | 8 | 8 | 1 | 42 | 0 | 0 | | 0 | Removed for Report | 1727109836 | | 86271 | 7 | 8 | 1 | 42 | 0 | 0 | | 0 | Removed for Report | 1727109836 | | 86269 | 1 | 8 | 1 | 42 | 0 | 0 | | 0 | Removed for Report | 1727109836 | | 86267 | 8 | 8 | 1 | 46 | 0 | 0 | | 0 | Removed for Report | 1727109821 | | 86268 | 7 | 8 | 1 | 46 | 0 | 0 | | 0 | Removed for Report | 1727109821 | | 86266 | 1 | 8 | 1 | 46 | 0 | 0 | | 0 | Removed for Report | 1727109821 | | 86264 | 8 | 8 | 1 | 48 | 0 | 0 | | 0 | Removed for Report | 1727109811 | | 86265 | 7 | 8 | 1 | 48 | 0 | 0 | | 0 | Removed for Report | 1727109811 | | 86263 | 1 | 8 | 1 | 48 | 0 | 0 | | 0 | Removed for Report | 1727109811 | | 86261 | 8 | 8 | 1 | 64 | 0 | 0 | | 0 | Removed for Report | 1727109803 | | 86262 | 7 | 8 | 1 | 64 | 0 | 0 | | 0 | Removed for Report | 1727109803 | | 86260 | 1 | 8 | 1 | 64 | 0 | 0 | | 0 | Removed for Report | 1727109803 | | 86258 | 8 | 8 | 1 | 39 | 0 | 0 | | 0 | Removed for Report | 1727109795 | | 86259 | 7 | 8 | 1 | 39 | 0 | 0 | | 0 | Removed for Report | 1727109795 | | 86257 | 1 | 8 | 1 | 39 | 0 | 0 | | 0 | Removed for Report | 1727109795 | | 86255 | 8 | 8 | 1 | 67 | 0 | 0 | | 0 | Removed for Report | 1727109786 | | 86256 | 7 | 8 | 1 | 67 | 0 | 0 | | 0 | Removed for Report | 1727109786 | +-------+---------+---------+-------------+---------+------------+------------+----------+------------+--------------------------+--------------+ 20 rows in set (0.028 sec) ``` Look at that! 0.028 seconds! Much better! But wait? What in the world is that query? Let me break it down a bit: The original query is completing an "inner join" for the apparently sole purpose of determing if the actions belong to repositories that currently exist. The first thing I tried was to change it from an inner join to a subquery: ``` MariaDB [gitea]> analyze select action.* from action where repo_id in (select id from repository) order by action.created_unix desc limit 20; +------+-------------+------------+-------+------------------+----------------------------+---------+---------------------+------+---------+----------+------------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+------------+-------+------------------+----------------------------+---------+---------------------+------+---------+----------+------------+----------------------------------------------+ | 1 | PRIMARY | repository | index | PRIMARY | IDX_repository_is_template | 1 | NULL | 60 | 60.00 | 100.00 | 100.00 | Using index; Using temporary; Using filesort | | 1 | PRIMARY | action | ref | IDX_action_r_u_d | IDX_action_r_u_d | 9 | gitea.repository.id | 398 | 1437.77 | 100.00 | 100.00 | | +------+-------------+------------+-------+------------------+----------------------------+---------+---------------------+------+---------+----------+------------+----------------------------------------------+ 2 rows in set (42.362 sec) ``` Unfortunately... this is also just as slow... So thinking about it... how do you make it faster? What are we trying to do? Exclude actions that have a repo_id that is not in repositories right? Let's take a look at the queries we just used above! ``` MariaDB [gitea]> select unique(repo_id) from action; +---------+ | repo_id | +---------+ | 1 | | ... | | 70 | +---------+ 55 rows in set (0.001 sec) MariaDB [gitea]> select id from repository; +-----+ | id | +-----+ | 1 | | ... | | 70 | +-----+ 60 rows in set (0.000 sec) ``` Ok, both of those look to be pretty similar, but what's happening is MariaDB needs to compare all of the ids against the "select id from repository" part of the subquery. This is likely the "same thing" that MariaDB is doing when completing the above inner join. So how do you make this faster? Let's flip it around and only get the repo_ids of actions that are **not** in the repository table! ``` MariaDB [gitea]> select unique(repo_id) from action where repo_id not in (select id from repository); Empty set (0.001 sec) ``` Neat! For me it's an empty set! (and it generally should be; however, repo_id **is not** a foreign key in the current schema) Now, when you do a select against that subquery, it is *much* faster to execute as there is "less" that needs to be scanned for each row. Bringing us back to this... ``` MariaDB [gitea]> select action.* from action where repo_id not in (select unique(repo_id) from action where repo_id not in (select id from repository)) order by action.created_unix desc limit 20; +-------+---------+---------+-------------+---------+------------+------------+----------+------------+--------------------------+--------------+ | id | user_id | op_type | act_user_id | repo_id | comment_id | is_deleted | ref_name | is_private | content | created_unix | +-------+---------+---------+-------------+---------+------------+------------+----------+------------+--------------------------+--------------+ | 86273 | 8 | 8 | 1 | 40 | 0 | 0 | | 0 | Removed for Report | 1727109844 | | 86274 | 7 | 8 | 1 | 40 | 0 | 0 | | 0 | Removed for Report | 1727109844 | | 86272 | 1 | 8 | 1 | 40 | 0 | 0 | | 0 | Removed for Report | 1727109844 | | 86270 | 8 | 8 | 1 | 42 | 0 | 0 | | 0 | Removed for Report | 1727109836 | | 86271 | 7 | 8 | 1 | 42 | 0 | 0 | | 0 | Removed for Report | 1727109836 | | 86269 | 1 | 8 | 1 | 42 | 0 | 0 | | 0 | Removed for Report | 1727109836 | | 86267 | 8 | 8 | 1 | 46 | 0 | 0 | | 0 | Removed for Report | 1727109821 | | 86268 | 7 | 8 | 1 | 46 | 0 | 0 | | 0 | Removed for Report | 1727109821 | | 86266 | 1 | 8 | 1 | 46 | 0 | 0 | | 0 | Removed for Report | 1727109821 | | 86264 | 8 | 8 | 1 | 48 | 0 | 0 | | 0 | Removed for Report | 1727109811 | | 86265 | 7 | 8 | 1 | 48 | 0 | 0 | | 0 | Removed for Report | 1727109811 | | 86263 | 1 | 8 | 1 | 48 | 0 | 0 | | 0 | Removed for Report | 1727109811 | | 86261 | 8 | 8 | 1 | 64 | 0 | 0 | | 0 | Removed for Report | 1727109803 | | 86262 | 7 | 8 | 1 | 64 | 0 | 0 | | 0 | Removed for Report | 1727109803 | | 86260 | 1 | 8 | 1 | 64 | 0 | 0 | | 0 | Removed for Report | 1727109803 | | 86258 | 8 | 8 | 1 | 39 | 0 | 0 | | 0 | Removed for Report | 1727109795 | | 86259 | 7 | 8 | 1 | 39 | 0 | 0 | | 0 | Removed for Report | 1727109795 | | 86257 | 1 | 8 | 1 | 39 | 0 | 0 | | 0 | Removed for Report | 1727109795 | | 86255 | 8 | 8 | 1 | 67 | 0 | 0 | | 0 | Removed for Report | 1727109786 | | 86256 | 7 | 8 | 1 | 67 | 0 | 0 | | 0 | Removed for Report | 1727109786 | +-------+---------+---------+-------------+---------+------------+------------+----------+------------+--------------------------+--------------+ 20 rows in set (0.028 sec) ``` That is where the odd looking subquery comes from; however, it leads to *significantly* faster queries that still maintain the "same" restrictions as the existing inner join! Finally, let's add user_id=2 and is_deleted=0 back just for a "apples to apples" comparison with original query: ``` MariaDB [gitea]> analyze select action.* from action where repo_id not in (select unique(repo_id) from action where repo_id not in (select id from repository)) and user_id=2 and is_deleted=0 order by action.created_unix desc limit 20; +------+--------------+------------+----------------+--------------------+----------------------------+---------+-------+------+--------+----------+------------+-------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+--------------+------------+----------------+--------------------+----------------------------+---------+-------+------+--------+----------+------------+-------------------------------------------------+ | 1 | PRIMARY | action | ref | IDX_action_user_id | IDX_action_user_id | 9 | const | 186 | 20.00 | 100.00 | 100.00 | Using where; Using filesort | | 2 | SUBQUERY | action | index_subquery | IDX_action_r_u_d | IDX_action_r_u_d | 9 | func | 796 | NULL | 100.00 | NULL | Using index; Using where; Full scan on NULL key | | 3 | MATERIALIZED | repository | index | PRIMARY | IDX_repository_is_template | 1 | NULL | 60 | 60.00 | 100.00 | 100.00 | Using index | +------+--------------+------------+----------------+--------------------+----------------------------+---------+-------+------+--------+----------+------------+-------------------------------------------------+ 3 rows in set (0.002 sec) ``` Neat! Just as fast (0.002 seconds), I'm omitting the actual output from this query as it's just an absolute load of json in the `content` column, but otherwise looks similar to above samples. I'd test changing the query in the source code; however, I do not have a Go development environment currently setup. I do hope this knowledge can be incorporated into the next version update for faster index page loads! Thanks! ### Gitea Version 1.22.2 ### Can you reproduce the bug on the Gitea demo site? No ### Log Gist _No response_ ### Screenshots _No response_ ### Git Version _No response_ ### Operating System AlmaLinux 9.4 - Official Gitea container ### How are you running Gitea? Official gitea container, running on podman: ``` REPOSITORY TAG IMAGE ID CREATED SIZE docker.io/gitea/gitea latest f5b290987d7d 2 weeks ago 168 MB ``` ### Database MySQL/MariaDB
GiteaMirror added the performance/speedtype/bug labels 2025-11-02 10:45:02 -06:00
GiteaMirror commented 2025-11-02 10:45:03 -06:00
Author
Owner
Copy Link

@KazzmanK commented on GitHub (Sep 23, 2024):

Any time we need to teach dumb database engine to execute simple queries with such a mess, it leads to "wtf is happening here???" in the feature.
Have you tried to update statistics/rebuild indexes?

@KazzmanK commented on GitHub (Sep 23, 2024): Any time we need to teach dumb database engine to execute simple queries with such a mess, it leads to "wtf is happening here???" in the feature. Have you tried to update statistics/rebuild indexes?
GiteaMirror commented 2025-11-02 10:45:04 -06:00
Author
Owner
Copy Link

@davidsenk commented on GitHub (Sep 23, 2024):

The indexes are already there, the query that is being ran is just not optimized to use the indexes appropriately.

The tl;dr is the because of the inner join, every row on actions needs to be compared repo_id == repository.id?

Limiting the number of comparisons (by only comparing against the set of repo_id that do not exist in repository.id) massively increases performance. In my example, I have ~70 repositories and ~86k actions.

Each of the 86k actions needs to be compared against ~70 repository.id values, leading to 86k * 70 = ~6 million comparisons. Reducing that to... 0 (in my case) means that the query can run quite a lot faster. It's making the database do less work.

Instead of asking "get me the set of actions for the repo_ids in repository.id" ask "get me the set of actions with a repo_id not in the set of ids that do not exist in repository.id"

i.e. it's a "double negative" to get back to the same place, but let's the database figure it out a lot faster.

Ideally action.repo_id should be a foreign key to repository.id, then the database would already know if the repository exists in the repository table already. Without it being a foreign key, it needs to do all this computation every time (or use the "not not" trick I showed above).

@davidsenk commented on GitHub (Sep 23, 2024): The indexes are already there, the query that is being ran is just not optimized to use the indexes appropriately. The tl;dr is the because of the inner join, *every* row on actions needs to be compared `repo_id == repository.id?` Limiting the number of comparisons (by only comparing against the set of repo_id that do not exist in repository.id) massively increases performance. In my example, I have ~70 repositories and ~86k actions. Each of the 86k actions needs to be compared against ~70 repository.id values, leading to 86k * 70 = ~6 million comparisons. Reducing that to... 0 (in my case) means that the query can run quite a lot faster. It's making the database do less work. Instead of asking "get me the set of actions for the repo_ids in repository.id" ask "get me the set of actions with a repo_id not in the set of ids that do not exist in repository.id" i.e. it's a "double negative" to get back to the same place, but let's the database figure it out a lot faster. *Ideally* action.repo_id *should be* a foreign key to repository.id, then the database would *already know* if the repository exists in the repository table already. Without it being a foreign key, it needs to do all this computation every time (or use the "not not" trick I showed above).
GiteaMirror commented 2025-11-02 10:45:04 -06:00
Author
Owner
Copy Link

@KazzmanK commented on GitHub (Sep 24, 2024):

inner join is not that straightforward. It leads to inner loop in case of bad planning or, for example, because of query hint (inner loop join for MSSQL). Mostly it is a main tool for queries and highly optimized. No 6 mils comparisons in loops.
Imagine your tables (or indexes) are sorted by join key. You need two pointers to walk through tables. you move left or right pointer when you need to advance to next row or to next join predicate value. if join keys are same it is okay, if not - advance required number of rows on sorted list and you`ll determine a new join pair or determine that there are no join pairs.
It is one-time pass. There is a number of other optimizations, like hash, bitmap etc helper structures to make that basic kind of joins run extremely fast. It this case with double negative you put db engine into state that it cannot chouse wrong execution plan. That is great in terms of fine-tuning (i'm doing that twice a day all the time) but if not great in general. It is better to find the roots of issue. First - reindex, reanalyse, garbage collect and so on, to make db optimizations awake.

@KazzmanK commented on GitHub (Sep 24, 2024): inner join is not that straightforward. It leads to inner loop in case of bad planning or, for example, because of query hint (inner loop join for MSSQL). Mostly it is a main tool for queries and highly optimized. No 6 mils comparisons in loops. Imagine your tables (or indexes) are sorted by join key. You need two pointers to walk through tables. you move left or right pointer when you need to advance to next row or to next join predicate value. if join keys are same it is okay, if not - advance required number of rows on sorted list and you`ll determine a new join pair or determine that there are no join pairs. It is one-time pass. There is a number of other optimizations, like hash, bitmap etc helper structures to make that basic kind of joins run extremely fast. It this case with double negative you put db engine into state that it cannot chouse wrong execution plan. That is great in terms of fine-tuning (i'm doing that twice a day all the time) but if not great in general. It is better to find the roots of issue. First - reindex, reanalyse, garbage collect and so on, to make db optimizations awake.
GiteaMirror commented 2025-11-02 10:45:04 -06:00
Author
Owner
Copy Link

@lunny commented on GitHub (Sep 24, 2024):

Maybe we can remove the inner join directly and provide a doctor command to clean the dirty records on action table.

@lunny commented on GitHub (Sep 24, 2024): Maybe we can remove the inner join directly and provide a doctor command to clean the dirty records on action table.
GiteaMirror commented 2025-11-02 10:45:04 -06:00
Author
Owner
Copy Link

@davidsenk commented on GitHub (Sep 24, 2024):

@lunny that would probably work pretty well, maybe have it be one of the "cronjobs" where it removes action entries if they refer to repos that do not exist in the repository table. This could be done using the select unique(xxx) not in (select id) query example.

The select without the join is plenty fast, it's just the comparison between tables that's slow.

Ideally, I believe that action.repo_id should be a foreign key to the repository table and then a delete cascade can be added to automatically "clean up" the action table as well.

@davidsenk commented on GitHub (Sep 24, 2024): @lunny that would probably work pretty well, maybe have it be one of the "cronjobs" where it removes action entries if they refer to repos that do not exist in the repository table. This could be done using the select unique(xxx) not in (select id) query example. The select without the join is plenty fast, it's just the comparison between tables that's slow. Ideally, I believe that action.repo_id should be a foreign key to the repository table and then a delete cascade can be added to automatically "clean up" the action table as well.
GiteaMirror commented 2025-11-02 10:45:05 -06:00
Author
Owner
Copy Link

@somera commented on GitHub (Sep 25, 2024):

On the index page (and on pagging)

Description

Recently on my Gitea instance, loading the index page started taking an absolute eternity, 30 seconds+ quite often. This is a bug report / analysis as to why.

On which hardware do you run gitea?

After #31752 the whole start page need ~3-5 seconds. It run on an NUC with an Intel Pentium Silver J5005 and 32GB RAM and PostgreSQL.

giteadb=# select count(*) from repository;
 count
-------
 24853
(1 Zeile)

giteadb=# select count(*) from action;
  count
---------
 3951400
(1 Zeile)

In my case the problem is this

2024-09-25 07:48:35.236 CEST [2120998] gitea@giteadb LOG:  Dauer: 2207.702 ms  Ausführen <unnamed>: SELECT count(*) FROM "action" INNER JOIN "repository" ON "repository".id = "action".repo_id WHERE user_id=$1 AND is_deleted=$2
2024-09-25 07:48:35.236 CEST [2120998] gitea@giteadb DETAIL:  Parameter: $1 = '1', $2 = 'f'

which is made on every page. All other stuff is fast now.

@somera commented on GitHub (Sep 25, 2024): On the index page (and on pagging) > ### Description > Recently on my Gitea instance, loading the index page started taking an _absolute_ eternity, 30 seconds+ quite often. This is a bug report / analysis as to why. On which hardware do you run gitea? After #31752 the whole start page need ~3-5 seconds. It run on an NUC with an Intel Pentium Silver J5005 and 32GB RAM and PostgreSQL. ``` giteadb=# select count(*) from repository; count ------- 24853 (1 Zeile) giteadb=# select count(*) from action; count --------- 3951400 (1 Zeile) ``` In my case the problem is this ``` 2024-09-25 07:48:35.236 CEST [2120998] gitea@giteadb LOG: Dauer: 2207.702 ms Ausführen <unnamed>: SELECT count(*) FROM "action" INNER JOIN "repository" ON "repository".id = "action".repo_id WHERE user_id=$1 AND is_deleted=$2 2024-09-25 07:48:35.236 CEST [2120998] gitea@giteadb DETAIL: Parameter: $1 = '1', $2 = 'f' ``` which is made on every page. All other stuff is fast now.
GiteaMirror commented 2025-11-02 10:45:05 -06:00
Author
Owner
Copy Link

@lunny commented on GitHub (Sep 25, 2024):

@somera #32127 will make it more faster after #31752 .

@lunny commented on GitHub (Sep 25, 2024): @somera #32127 will make it more faster after #31752 .
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
$20
$250
$50
$500
backport/done
💎 Bounty
docs-update-needed
good first issue
hacktoberfest
issue/bounty
issue/confirmed
issue/critical
issue/duplicate
issue/needs-feedback
issue/not-a-bug
issue/regression
issue/stale
issue/workaround
lgtm/need 2
modifies/api
modifies/translation
outdated/backport/v1.18
outdated/theme/markdown
outdated/theme/timetracker
performance/bigrepo
performance/cpu
performance/memory
performance/speed
pr/breaking
proposal/accepted
proposal/rejected
pr/wip
pull-request
Mirrored from GitHub Pull Request
 reviewed/wontfix
💰 Rewarded
skip-changelog
status/blocked
topic/accessibility
topic/api
topic/authentication
topic/build
topic/code-linting
topic/commit-signing
topic/content-rendering
topic/deployment
topic/distribution
topic/federation
topic/gitea-actions
topic/issues
topic/lfs
topic/mobile
topic/moderation
topic/packages
topic/pr
topic/projects
topic/repo
topic/repo-migration
topic/security
topic/theme
topic/ui
topic/ui-interaction
topic/ux
topic/webhooks
topic/wiki
type/bug
type/deprecation
type/docs
type/enhancement
type/feature
type/miscellaneous
type/proposal
type/question
type/refactoring
type/summary
type/testing
type/upstream
No Label performance/speed type/bug
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
GiteaMirror ninjasurge
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: github-starred/gitea#13523
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?