List page is too slow (times out) with a big database

[noamtamir]

With a big database loading the list page is very slow, and can time out. This seems to be caused by the fact that sqladmin is running count query when loading the list page, which basically queries the entire db.

sqladmin/sqladmin/models.py

Line 770 in fadf4d6

count = await self.count(request)

Has anybody else run into this issue and found a solution/workaround to make this faster?

[gopjak36]

Could you please provide me with the count of a specific table?

[noamtamir]

4144434 rows (about 7GB of data)

[aminalaee]

I’m also interested to know how big is your data and how have you confirmed this is the slow query.
You can override the method to disable count, i.e. disabling pagination, but I think it can be solved.

[noamtamir]

4144434 rows (about 7GB of data).
I looked in AWS RDS performance insights tool, and also ran count(*) from my machine and it took approx 1 minute to run.

Is there a way to disable count but keep the pagination somehow? infinite pagination or something like that?

[aminalaee]

Not right now. Please go ahead with a PR if you think this can be solved

[noamtamir]

After reading this article: https://www.citusdata.com/blog/2016/10/12/count-performance/#:~:text=copy-,Estimated%20Counts,-Full%20Table%20Estimates
I ended up overriding the count_query with the following code:

    def count_query(self, request: Request) -> Select:
        query = '''\
SELECT
  COALESCE(reltuples / NULLIF(relpages,0), reltuples) * (
    pg_relation_size(:tablename) /
    (current_setting('block_size')::integer)
  )
FROM pg_class where relname = :tablename;'''

        return text(query).params(tablename=self.model.__tablename__)

Note: the intention of NULLIF and COALESCE functions are there to accommodate for an empty db, otherwise you will get a division by zero error.

Pros:

• Very fast
• Above a certain amount of rows, this is a must, or the list pages will timeout.

Cons:

• An estimate, not exact
• Not useful for small databases, because the estimate is less precise on smaller dbs.
• Postgres specific, though other DBs should have their versions of this query.
• You should run Autovacuum (vacuum and analyze) to make sure the stats are up-to-date