(db-postgres): Current method for counting rows in findMany / count operations is very heavy for tables with high amount of records.

[r1tsuu]

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:

  const countResult = await chainMethods({
      methods: selectCountMethods,
      query: db
        .select({
          count: sql<number>`count
              (DISTINCT ${adapter.tables[tableName].id})`,
        })
        .from(table)
        .where(where),
    })

It should be represented as the following SQL query:

SELECT COUNT(DISTINCT id) from public.tests

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

SELECT COUNT(*) FROM (SELECT DISTINCT id FROM public.tests) AS temp;

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

[jmikrut]

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!