Feature Request for better performance on paging and lees memory usage on database #31752

somera · 2024-08-01T18:07:28Z

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

somera · 2024-08-01T18:55:49Z

I know why it's getting slower.

https://readyset.io/blog/optimizing-sql-pagination-in-postgres

somera · 2024-08-01T21:50:39Z

On slow paging I see this "cache context ..." log entries

2024/08/01 23:49:01 ...eb/routing/logger.go:68:func1() [W] router: slow      GET /?page=90000&date= for 192.168.178.21:0, elapsed 3969.6ms @ web/home.go:32(web.Home)
2024/08/01 23:49:07 ...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=$1 AND is_deleted=$2 ORDER BY "action"."created_unix" DESC LIMIT 20 OFFSET 1799980 [1 false] - 9.526588923s
2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:08 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029684656701459 22593705060787 0x6a1a080} false}
2024/08/01 23:49:17 ...eb/routing/logger.go:68:func1() [W] router: slow      GET /?page=89999&date= for 192.168.178.21:0, elapsed 3604.4ms @ web/home.go:32(web.Home)
2024/08/01 23:49:23 ...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=$1 AND is_deleted=$2 ORDER BY "action"."created_unix" DESC LIMIT 20 OFFSET 1799960 [1 false] - 9.456084079s
2024/08/01 23:49:24 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
2024/08/01 23:49:24 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
2024/08/01 23:49:24 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
2024/08/01 23:49:24 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
2024/08/01 23:49:24 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
2024/08/01 23:49:24 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
2024/08/01 23:49:24 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
2024/08/01 23:49:24 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
2024/08/01 23:49:24 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
2024/08/01 23:49:24 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
2024/08/01 23:49:24 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
2024/08/01 23:49:24 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
2024/08/01 23:49:24 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
2024/08/01 23:49:24 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
2024/08/01 23:49:24 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
2024/08/01 23:49:24 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
2024/08/01 23:49:24 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
2024/08/01 23:49:24 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
2024/08/01 23:49:24 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
2024/08/01 23:49:24 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
2024/08/01 23:49:24 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
2024/08/01 23:49:24 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
2024/08/01 23:49:24 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
2024/08/01 23:49:24 ...les/cache/context.go:147:SetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}
2024/08/01 23:49:24 ...les/cache/context.go:134:GetContextData() [W] cache context is expired, may be misused for long-life tasks: &{map[] {{0 0} 0 0 {{} 0} {{} 0}} {13953029702275280671 22610070028993 0x6a1a080} false}

somera · 2024-08-12T10:48:20Z

Step 1:

OLD:
SELECT count(*) FROM "action" INNER JOIN "repository" ON "repository".id = "action".repo_id WHERE user_id=$1 AND is_deleted=$2

NEW:
SELECT count(action.id) FROM "action" INNER JOIN "repository" ON "repository".id = "action".repo_id WHERE user_id=$1 AND is_deleted=$2

Step 2:

OLD (expample with LIMIT and OFFSET -> SELECT action.* uses too much memory on database):
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

NEW:

get XX action.id's (SELECT action.id -> uses less memory on database and it's faster)
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
Select for XX action.id's
SELECT action.* FROM action WHERE action.id IN (....)

lunny · 2024-08-12T18:40:04Z

Please can you help to confirm #31821 will resolve this issue?

somera · 2024-08-12T19:01:19Z

@lunny yes, I can help. I can check it on my 2nd instance with the same account of data like my main instance.

I need an executable gitea-1.21.*-linux-amd64? Nigtly?

lunny · 2024-08-12T19:11:27Z

@lunny yes, I can help. I can check it on my 2nd instance with the same account of data like my main instance.

I need an executable gitea-1.21.*-linux-amd64? Nigtly?

No. It's just a PR. I don't think you can easily replace it to do a test and revert back easily.

somera · 2024-08-12T19:14:25Z

Means I should compile it?

Tell me what I have to do? Cause I didn't compile gitea for my use with PostgreSQL.

lunny · 2024-08-14T02:40:25Z

Means I should compile it?

Tell me what I have to do? Cause I didn't compile gitea for my use with PostgreSQL.

No. I can help to compile one but once your upgrade the version I provided, it's difficult for you to downgrade if there is no Gitea develop env for you.

somera · 2024-08-14T17:20:09Z

I have 2nd gitea instance for testing. I can backup the database and after test restore the backup.

lunny · 2024-08-14T17:57:38Z

Please test https://gitea.com/lunny/packages/src/branch/main/gitea-pr-31821-amd64-linux.tar.gz

somera · 2024-08-14T20:15:28Z

@lunny thx. I tested this PR.

My 24/7 NUC with main gitea instance has an Intel Pentium Silver J5005 (4) @ 2.800GHz. The results in this PR was made on this NUC.

My test now I run on a faster Intel NUC with an Intel i5-8259U (8) @ 3.800GHz. Has more cores/threads and it's faster.

How I made the test.

I started the gitea-1.22.1-linux-amd64 release and I opened some pages.
I started the gitea-pr-31821-amd64-linux from you and I opened the same pages. Cause I expected the same projects on the pages.

My expectation is:

The same order on the pages.
Slightly slower times on the lower pages (due to the 3rd query) and the further you scroll, the more positive the 3rd query will be.

I see same projects on the same pages. But the order is reverted. Project which are on the 1. place on the page are in the PR version on the last place. But I see same projects!!!

Times:

somera · 2024-08-14T20:17:12Z

Sorry, but I didn't open more page at the beginning. But the new version need less memory or tmp files in PostgreSQL as I show it in my description,

somera · 2024-08-14T20:20:38Z

Last page in 1.22.1

Last page in 1.22.0+PR

As you see the order is reverted.

somera · 2024-08-14T20:21:49Z

And on my slower NUC I have 11233 more pages.

I wait for you feedback before I restore my DB backup.

lunny · 2024-08-14T21:55:36Z

And on my slower NUC I have 11233 more pages.

I wait for you feedback before I restore my DB backup.

Yes, it's a bug. Please try version 2 https://gitea.com/lunny/packages/src/branch/main/gitea-pr-31821-amd64-linux-2.tar.gz

somera · 2024-08-14T22:12:17Z

I started this Version

1.22.0+rc1-415-gcd09625a76

and the sorting looks good. But I see the times are the same like in 1.22.1 and in PG log I see the action.* query

2024-08-15 00:09:56.653 CEST [6735] gitea@giteadb LOG: Dauer: 2259.538 ms Ausführen <unnamed>: SELECT "action".* FROM "action" INNER JOIN "repository" ON "repository".id = "action".repo_id WHERE user_id=$1 AND is_deleted=$2 ORDER BY "action"."created_unix" DESC LIMIT 20 OFFSET 1819340

The second statement should be SELECT action.id FROM ...

Like here #31752 (comment)

In the first version it was ok

postgresql-Wed.log:2024-08-14 21:55:59.113 CEST [3399] gitea@giteadb LOG:  Dauer: 1029.815 ms  Ausführen <unnamed>: SELECT "action".id FROM "action" INNER JOIN "repository" ON "repository".id = "action".repo_id WHERE user_id=$1 AND is_deleted=$2 ORDER BY "action"."created_unix" DESC LIMIT 20 OFFSET 1819420
postgresql-Wed.log:2024-08-14 21:56:04.272 CEST [3399] gitea@giteadb LOG:  Dauer: 1037.622 ms  Ausführen <unnamed>: SELECT "action".id FROM "action" INNER JOIN "repository" ON "repository".id = "action".repo_id WHERE user_id=$1 AND is_deleted=$2 ORDER BY "action"."created_unix" DESC LIMIT 20 OFFSET 1819400
postgresql-Wed.log:2024-08-14 21:56:10.563 CEST [3399] gitea@giteadb LOG:  Dauer: 1020.780 ms  Ausführen <unnamed>: SELECT "action".id FROM "action" INNER JOIN "repository" ON "repository".id = "action".repo_id WHERE user_id=$1 AND is_deleted=$2 ORDER BY "action"."created_unix" DESC LIMIT 20 OFFSET 1819380
postgresql-Wed.log:2024-08-14 21:56:21.066 CEST [3399] gitea@giteadb LOG:  Dauer: 1015.299 ms  Ausführen <unnamed>: SELECT "action".id FROM "action" INNER JOIN "repository" ON "repository".id = "action".repo_id WHERE user_id=$1 AND is_deleted=$2 ORDER BY "action"."created_unix" DESC LIMIT 20 OFFSET 1819360

lunny · 2024-08-15T00:09:53Z

In this version, it will use the old code when the page less than 10, otherwise it will use the new code.

somera · 2024-08-15T12:15:22Z

@lunny sounds good, but when you see the OFFSET

2024-08-15 00:09:56.653 CEST [6735] gitea@giteadb LOG: Dauer: 2259.538 ms Ausführen <unnamed>: SELECT "action".* FROM "action" INNER JOIN "repository" ON "repository".id = "action".repo_id WHERE user_id=$1 AND is_deleted=$2 ORDER BY "action"."created_unix" DESC LIMIT 20 OFFSET 1819340

SELECT action.* should be uses, cause I'm over 10 pages. And the new method was not used.

lunny · 2024-08-16T02:01:48Z

:(. I made a mistake. version 3 https://gitea.com/lunny/packages/src/branch/main/gitea-pr-31821-amd64-linux-3.tar.gz

somera · 2024-08-16T15:12:17Z

Looks good.

Page 1-9 old method. Pages 10+ new method. It was good idea to do it.

And the sorting looks good too.

somera · 2024-08-16T19:39:38Z

@lunny can I restore my datanse now? Or should I wait?

lunny · 2024-08-16T19:43:00Z

@lunny can I restore my datanse now? Or should I wait?

Yes, you can restore now. Thank you for your testing.

somera · 2024-08-16T19:44:14Z

I have to thank you for the implementation of the PR.

somera · 2024-10-09T18:30:06Z

@lunny I installed 1.22.3 release. I saw the changes.

I see this

SELECT
	"id", "user_id", "op_type", "act_user_id", "repo_id", "comment_id", "is_deleted", "ref_name", "is_private", "content", "created_unix"
FROM
	"action"
WHERE
	user_id=1 AND is_deleted='f'
ORDER BY "action"."created_unix" DESC LIMIT 20 OFFSET 1976620

SQL on paging.

I was sure, the change will be:

SELECT
	"id"
FROM
	"action"
WHERE
	user_id=1 AND is_deleted='f'
ORDER BY "action"."created_unix" DESC LIMIT 20 OFFSET 1976620

and than on SELECT for all the 20 id's.

which in my case need 50% of the time in the higher pages and uses only 10% of the memory.

vs

lunny · 2024-10-11T07:09:56Z

Please follow #32224

somera · 2024-11-25T21:32:09Z

Just update for the start page ...

thx!

somera added the type/proposal The new feature has not been accepted yet but needs to be discussed first. label Aug 1, 2024

lunny added the performance/speed performance issues with slow downs label Aug 1, 2024

somera mentioned this issue Aug 1, 2024

Index page incredibly slow after update to 1.21.8 #29769

Closed

lunny mentioned this issue Aug 12, 2024

Improve get feed with pagination #31821

Merged

lunny closed this as completed in #31821 Sep 2, 2024

lunny closed this as completed in 85b1f30 Sep 2, 2024

somera mentioned this issue Sep 25, 2024

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

Closed

somera mentioned this issue Oct 11, 2024

Horrible Dashboard Delay After Upgrade #32224

Closed

go-gitea locked as resolved and limited conversation to collaborators Dec 2, 2024

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Feature Request for better performance on paging and lees memory usage on database #31752

Feature Request for better performance on paging and lees memory usage on database #31752

somera commented Aug 1, 2024 •

edited

Loading

somera commented Aug 1, 2024 •

edited

Loading

somera commented Aug 1, 2024

somera commented Aug 12, 2024 •

edited

Loading

lunny commented Aug 12, 2024

somera commented Aug 12, 2024

lunny commented Aug 12, 2024

somera commented Aug 12, 2024

lunny commented Aug 14, 2024

somera commented Aug 14, 2024

lunny commented Aug 14, 2024

somera commented Aug 14, 2024 •

edited

Loading

somera commented Aug 14, 2024 •

edited

Loading

somera commented Aug 14, 2024

somera commented Aug 14, 2024 •

edited

Loading

lunny commented Aug 14, 2024

somera commented Aug 14, 2024 •

edited

Loading

lunny commented Aug 15, 2024

somera commented Aug 15, 2024 •

edited

Loading

lunny commented Aug 16, 2024

somera commented Aug 16, 2024

somera commented Aug 16, 2024

lunny commented Aug 16, 2024

somera commented Aug 16, 2024

somera commented Oct 9, 2024 •

edited

Loading

lunny commented Oct 11, 2024

somera commented Nov 25, 2024

Feature Request for better performance on paging and lees memory usage on database #31752

Feature Request for better performance on paging and lees memory usage on database #31752

Comments

somera commented Aug 1, 2024 • edited Loading

Feature Description

Screenshots

somera commented Aug 1, 2024 • edited Loading

somera commented Aug 1, 2024

somera commented Aug 12, 2024 • edited Loading

lunny commented Aug 12, 2024

somera commented Aug 12, 2024

lunny commented Aug 12, 2024

somera commented Aug 12, 2024

lunny commented Aug 14, 2024

somera commented Aug 14, 2024

lunny commented Aug 14, 2024

somera commented Aug 14, 2024 • edited Loading

somera commented Aug 14, 2024 • edited Loading

somera commented Aug 14, 2024

somera commented Aug 14, 2024 • edited Loading

lunny commented Aug 14, 2024

somera commented Aug 14, 2024 • edited Loading

lunny commented Aug 15, 2024

somera commented Aug 15, 2024 • edited Loading

lunny commented Aug 16, 2024

somera commented Aug 16, 2024

somera commented Aug 16, 2024

lunny commented Aug 16, 2024

somera commented Aug 16, 2024

somera commented Oct 9, 2024 • edited Loading

lunny commented Oct 11, 2024

somera commented Nov 25, 2024

somera commented Aug 1, 2024 •

edited

Loading

somera commented Aug 1, 2024 •

edited

Loading

somera commented Aug 12, 2024 •

edited

Loading

somera commented Aug 14, 2024 •

edited

Loading

somera commented Aug 14, 2024 •

edited

Loading

somera commented Aug 14, 2024 •

edited

Loading

somera commented Aug 14, 2024 •

edited

Loading

somera commented Aug 15, 2024 •

edited

Loading

somera commented Oct 9, 2024 •

edited

Loading