Postgresql query is very slow for my big table
Counting rows is slow, because all rows of the table have to be visited.
Counting id is even slower, because PostgreSQL first has to check if id is NULL or not (NULL values are not counted).

There are a few options to speed things up:


Use a more recent version of PostgreSQL.

Then you can get parallel query, which will make the execution even more expensive, but faster.
Use the index on id and keep the table well vacuumed.

Then you can get an index only scan.
Use an extra table with a counter that gets updated on every data modifying statement on the large table using a trigger.


Have a look at my blog post for an in-depth discussion.