From PostgreSQL wiki

A full count of rows in a table can be comparatively slow performing in PostgreSQL, typically using this SQL:

SELECT COUNT(*) FROM tbl;

The reason why this is slow is related to the MVCC implementation in PostgreSQL. The fact that multiple transactions can see different states of the data means that there can be no straightforward way for "COUNT(*)" to summarize data across the whole table; PostgreSQL must walk through all rows, in some sense. This normally results in a sequential scan reading information about every row in the table. EXPLAIN ANALYZE reveals what's going on:

It is worth observing that it is only this precise form of aggregate that must be so pessimistic; if augmented with a "WHERE" clause like

SELECT COUNT(*) FROM tbl WHERE status = 'something';

PostgreSQL will take advantage of available indexes against the restricted field(s) to limit how many records must be counted, which can greatly accelerate such queries. PostgreSQL will still need to read the resulting rows to verify that they exist; other database systems may only need to reference the index in this situation.

Estimating the row count

One PostgreSQL alternative when only an approximate count is needed is to use the reltuples field from the pg_class catalog table: