-
Notifications
You must be signed in to change notification settings - Fork 1.5k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
(db-postgres): Current method for counting rows in findMany / count operations is very heavy for tables with high amount of records. #6321
Labels
Comments
r1tsuu
added
the
status: needs-triage
Possible bug which hasn't been reproduced yet
label
May 11, 2024
I am way down to do some experimentation here. We will crack this open on Monday and post back with our findings! Thanks for the recon, as always, it’s very much appreciated! |
github-actions
bot
removed
the
status: needs-triage
Possible bug which hasn't been reproduced yet
label
Jul 29, 2024
3 tasks
Opened a PR #7749 |
DanRibbens
pushed a commit
that referenced
this issue
Sep 16, 2024
Closes #6321 To run benchmark: `git checkout b840222` - from r1tsuu/payload b840222 `pnpm dev:postgres _community` Benchmark results: (Before / After) Postgres 400 000 rows: ![image](https://github.com/user-attachments/assets/cd7c478f-2057-4c7c-adec-5dbf0b05ec7b) Postgres 2 000 000 rows: ![image](https://github.com/user-attachments/assets/04224f95-77eb-42ab-9591-887b197c597a) SQLite 400 000 rows: ![image](https://github.com/user-attachments/assets/ba7482c2-30f1-4498-892d-59710639a7b3) SQLite 2 000 000 rows: ![image](https://github.com/user-attachments/assets/c0a889f8-8e21-4b98-ac92-65ac735b8b32) ## Description <!-- Please include a summary of the pull request and any related issues it fixes. Please also include relevant motivation and context. --> - [x] I have read and understand the [CONTRIBUTING.md](https://github.com/payloadcms/payload/blob/main/CONTRIBUTING.md) document in this repository. ## Type of change <!-- Please delete options that are not relevant. --> - [x] Bug fix (non-breaking change which fixes an issue) ## Checklist: - [x] Existing test suite passes locally with my changes - See #7749 (comment)
Fixed there 👍 |
This issue has been automatically locked. |
🚀 This is included in version v3.0.0-beta.108 |
Sign up for free
to subscribe to this conversation on GitHub.
Already have an account?
Sign in.
Link to reproduction
https://github.com/r1tsuu/payload/tree/heavy-count-postgres
Describe the Bug
Current method for getting count of rows is very heavy when dealing with a large amount of rows.
From here:
It should be represented as the following SQL query:
Running this in pgAdmin results in 403! ms waiting (with 1.7kk docs)
For compare:
Retrieving the latest 100 rows, which is a not bad amount is only 43 ms
Isn't it a huge amount of time just for COUNT? What if you don't need count, it's a good question too?
However, there's a trick SQL query that gives us at least 167 ms for count
I don't know drawbacks of this. Though it's still 3x times slower than just the latest 100 rows query.
Discord thread https://discord.com/channels/967097582721572934/1238410124775915530 originally created from user that compared Payload and Directus, i just did some additional research on that.
To Reproduce
Configure the amount of docs that will be generated, by default it's 2kk (line 47 community config)
pnpm dev:postgres _community
...wait while docs will be created, should be fast as i'm using here raw sql insert many
Payload Version
3.0
Adapters and Plugins
db-postgres
The text was updated successfully, but these errors were encountered: