Description
Feature Description
I'm again. The gitea user with 3655 organisations and 22573 repos.
I wondered that my paging is slow. Not on the first pages.
First page need 1-3 seconds. 1 second if the data is precached.
On the last pages ...
But this is after my 1st optimisation.
You made this queries
- SELECT count(*) FROM "action" INNER JOIN "repository" ON "repository".id = "action".repo_id WHERE user_id=$1 AND is_deleted=$2
- SELECT action.* FROM action INNER JOIN repository ON repository.id = action.repo_id WHERE user_id='1' AND is_deleted='f' ORDER BY action.created_unix DESC LIMIT 20 OFFSET 2043740
on pagging.
Explain for the count query is
And than I changed the query to
SELECT count(action.id) FROM "action" INNER JOIN "repository" ON "repository".id = "action".repo_id WHERE user_id=$1 AND is_deleted=$2
and ...
it's faster.
Explain for the SELECT query is
very bad. I increased work_mem to 6GB and it's better now
After this fix it need ~11 seconds.
But it can be better. Cause the problem is this
SELECT action.* FROM action INNER JOIN repository ON repository.id = action.repo_id WHERE user_id='1' AND is_deleted='f' ORDER BY action.created_unix DESC LIMIT 20 OFFSET 2043740
query. When you change it to
SELECT action.id FROM action INNER JOIN repository ON repository.id = action.repo_id WHERE user_id='1' AND is_deleted='f' ORDER BY action.created_unix DESC LIMIT 20 OFFSET 2043740
-> you will get 20 action.id's than explain is
and need ~5 seconds and a very less memory on the database.
Than gitea should get the action.* infos only for the 20 id's in the new query.
This optimisation will be good for all gitea users.
Screenshots
No response