pulls/issue page very slow with >19000 repos

[somera]

Description

On my gitea instance with 19000 repositories and 3200 ogas the pulls page need

in 1.20.5 it was fast.

Possible SQL query changes.

Gitea Version

1.21.0

Can you reproduce the bug on the Gitea demo site?

No

Log Gist

No response

Screenshots

No response

Git Version

2.42.0

Operating System

Ubuntu 20.04.x

How are you running Gitea?

gitea-1.21.0-linux-amd64

Database

PostgreSQL

[somera]

In older gitea versions I reported some performance issues with many repos/mirrors and orgas. ;)

I try to analyse which query is the problem here.

[somera]

I see

==> gitea.log <==
2023/11/14 17:45:15 ...eb/routing/logger.go:68:func1() [W] router: slow      GET /pulls for xx.xx.xx.xx:0, elapsed 3047.5ms @ user/home.go:329(user.Pulls)

and this

gitea.log:2023/11/14 17:51:40 ...sues/issue_search.go:499:IssueIDs() [I] [SQL] SELECT count(*) FROM "issue" INNER JOIN "repository" ON "issue".repo_id = "repository".id WHERE issue.repo_id=$1 AND (issue.is_closed=$2) AND (issue.is_pull=$3) [7954 false true] - 958.121µs
gitea.log:2023/11/14 17:51:40 ...sues/issue_search.go:499:IssueIDs() [I] [SQL] SELECT "issue".id FROM "issue" INNER JOIN "repository" ON "issue".repo_id = "repository".id WHERE issue.repo_id=$1 AND (issue.is_closed=$2) AND (issue.is_pull=$3) ORDER BY "issue"."updated_unix" DESC, "issue"."created_unix" DESC, "issue"."id" DESC [19500 false true] - 1.283654ms

is the problem:

$ grep "sues/issue_search.go:499:IssueIDs()" gitea.log | wc -l
38280

38280 queries on my instance for the pulls page. Wow.

Two queries for every repo in my gitea.

[KazzmanK]

what is the use for join here? Just to be sure what issue is still referenced by existing repo?
looks like repository join can easily be dropped here.

[somera]

I have no issues in my gitea instance. I have a lot of mirrors and for some projects I'm using pull requests. At the moment all pull requests are closed.

The question is, why the change in 1.21.0? Why the new two selets for every repository?

[somera]

@KazzmanK the design here is wrong.

When I have no issues but 1000000 project, then you check for every project; "is there an issue".

How did you come up with the idea of doing it like that?

[somera]

No issues but ~39000 SQL queries.

issues:

pulls:

In 1.20.x it was fast. Why this change?

Or how much queries will be made if there are 2000 issues/pulls?

[lng2020]

Or how much queries will be made if there are 2000 issues/pulls?

We refactor the search engine here to support multiple engines, such as melisearch, es, etc. But when using the default db engine, it indeed somehow includes some duplicate search. I notice this abnormal behavior too.
The refactor PR: #25174

[somera]

It's possible to fix this in 1.21.1?