Geek Answers Handbook

Why does count (*) take so long in one PostgreSQL database, but not in another?

I have two Postgres databases. In one, I have two tables, each of which contains about 8,000,000 rows, and the account on any of them takes about a second. In another Postgres database, there are tables that make up 1,000,000 rows, and count - 10, and one table - about 6,000,000 rows, and the account takes 3 minutes to run. What factors determine how long it takes? They are on different machines, but the database, which takes more time, is on a faster machine.

I read about how counts are generally slow, but it seems strange to me. I cannot use a workaround because I use django and it does an admin account which takes forever and makes it useful.

Any information on this would be helpful.

+5
source share
3 answers

The counting speed depends not only on the number of rows in the table, but also on the time taken to read data from the disk. Time depends on many things:

  • The number of rows in the table - as you already mentioned.
  • The number of records per page (if each record requires more space to read more pages, to read the same number of lines).
  • If the pages are only partially filled, you need to read more pages.
  • ( ).
  • ( ).
  • .
  • .....
+4

, , .

0

Is the "slow table" vacuuming right?

Do not use VACUUM FULL; it only creates table and index bloating. VACUUM is absolutely sufficient. VACUUM ANALYSIS would be even better.

And make sure autovacuum is turned on and properly configured.

0
source

More articles:


All Articles