why is Count taking extremely long in one Postgres Database but not another

Question

I have two Postgres databases. In one I have two tables, each with about 8,000,000 rows, and a count on either of them takes about a second. In another database, also Postgres, there are tables that are 1,000,000 rows, and a count takes 10s, and one table thats about 6,000,000 rows, and count takes 3min to run. What factors determine how long this will take? They are on different machines, but the database that takes longer is on a faster machine.

I've read about how postgres count is slow in general, but this seems odd to me. I can't really use a workaround, because I am using django, and it does a count in the admin, which is taking forever and making it dificult to use.

Any information on this would be helpful.

Answer 1

Indexes, caches, disk speed, for starters all have an impact.

Answer 2

Speed of counting depends not just on the number of rows in the table but on the time taken to read the data from disk. The time depends on many things:

• Number of rows in the table - as you already mentioned.
• The number of records per page (if each record takes more space you need to read more pages to read the same number of rows).
• If pages are only partly full you have to read more pages.
• If the tables is already cached in memory (having more memory available helps here).
• If the table is indexed with a small index (the index can be counted instead).
• Hardware differences.
• etc....

Answer 3

Is the "slow table" properly vacuumed?

Do not use VACUUM FULL, it only creates table and index bloat. VACUUM is absolutely enough. VACUUM ANALYZE would even be better.

And make sure autovacuum is turned on and properly configured