Open Pull-Requests view with > 500 organizations need more than 1 minute

[somera]

• Gitea version (or commit ref): 1.14.0
• Git version:
• Operating system: 20.04.2
• Database (use [x]):
  • PostgreSQL
  • MySQL
  • MSSQL
  • SQLite
• Can you reproduce the bug at https://try.gitea.io:
  • Yes (provide example URL)
  • No
• Log gist:

Description

This with 1 minute is on my internal mini-server configuration.

I try to analyze what are you doing after I open the Pull-Requests link.

What I see.

First this PostgreSQL query:

explain analyze SELECT "repository"."id" FROM "repository" INNER JOIN "team_user" ON repository.owner_id = team_user.org_id INNER JOIN "team_repo" ON ('true' != 'false' and repository.is_private != 't') OR (team_user.team_id = team_repo.team_id AND repository.id = team_repo.repo_id) WHERE (team_user.uid = '1') GROUP BY repository.id

Which needs ~7 seconds on my NUC (https://ark.intel.com/content/www/us/en/ark/products/126137/intel-nuc-kit-nuc7pjyh.html -> I'm running here Gitea 1.14.0, PostgreSQL 13, Nginx, InfluxDB, Grafana), And after the first query I see a lot small PostgreSQL queries. I think one for every organization.

And when I choose other organization from the long list

I see > 500 http requests to the Gitea server

Looks like the process is "complicated" and not scalable for a lot of organizations and repos. Cause I have no pull requests on my Gitea instance

I think you should return back only all organizations which has pull requests. In my case: no one. Just the info: you dind't have any pull requests.

But this "problem" has only user with a lot of organizations and repos.

Am I right?

[nsmith5]

This looks like the offending call stack for anyone interested:

• gitea/routers/user/home.go

  Line 340 in 66f0fd0

  buildIssueOverview(ctx, models.UnitTypePullRequests)

• gitea/routers/user/home.go

  Line 425 in 66f0fd0

  userRepoIDs, err := getActiveUserRepoIDs(ctxUser, team, unitType)

• gitea/routers/user/home.go

  Line 751 in 66f0fd0

  userRepoIDs, err = ctxUser.GetActiveAccessRepoIDs(unitType)

• gitea/models/user.go

  Line 588 in 66f0fd0

  ids2, err := u.GetActiveOrgRepositoryIDs(units...)

• gitea/models/user.go

  Lines 547 to 565 in 66f0fd0

  	func (u *User) GetActiveOrgRepositoryIDs(units ...UnitType) ([]int64, error) {
  	var ids []int64
  	if err := x.Table("repository").
  	Cols("repository.id").
  	Join("INNER", "team_user", "repository.owner_id = team_user.org_id").
  	Join("INNER", "team_repo", "(? != ? and repository.is_private != ?) OR (team_user.team_id = team_repo.team_id AND repository.id = team_repo.repo_id)", true, u.IsRestricted, true).
  	Where("team_user.uid = ?", u.ID).
  	Where(builder.Eq{"is_archived": false}).
  	GroupBy("repository.id").Find(&ids); err != nil {
  	return nil, err
  	}
  	if len(units) > 0 {
  	return FilterOutRepoIdsWithoutUnitAccess(u, ids, units...)
  	}
  	return ids, nil
  	}

Since this code path only appears to be used in this pull request page it might be worth making a more specific functions for this query?

[somera]

@nsmith5 the first question is, how the "show pulls" process should be changed?

I see two ways:

1. First fast fix: get only repos with opened or closed pull requests back -> use the current view
2. Rebuild the whole process -> has new view

Imagine the user has 1000 or 2000 repos? -> The speed of this view will be much slower.

Collecting all the informations is not the best solution. Show only list with opened pull requests. Than add an option for showing the closed pull requests. And get the information for the selected option after users action. As user is my goal to see first and fast opened pull requests. Cause I will approve or deny them.

Which option you choose depends on your target group.

[lunny]

It depends on the machine I think. I tested my local instance (ec69f34) with postgres 9.6 (Macbook pro 13 2020), I created 600 orgs, and it took 1229ms on first org pull requests page and 494ms on second org pull requests page.

@somera Could you try v1.14.1 ?

[somera]

It depends on the machine I think. I tested my local instance (ec69f34) with postgres 9.6 (Macbook pro 13 2020), I created 600 orgs, and it took 1229ms on first org pull requests page and 494ms on second org pull requests page.

@somera Could you try v1.14.1 ?

Have you created empty orgs?

Yes, it will be faster with better hardware. But I would say, that the current flow is not scalable.

At the moment I have:

• 550 organisations
• 5461 repos

And there are ZERO pull requests.

Here the result ...

[somera]

First call

Second call

Third call

And I'm using PostgreSQL 13 and Memcached.