(db-postgres): Current method for counting rows in findMany / count operations is very heavy for tables with high amount of records. #6321
Labels
db-postgres
@payloadcms/db-postgres
status: needs-triage
Possible bug which hasn't been reproduced yet
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: