PostgreSQL – How to Optimize COUNT(*) Using Only Foreign Key Index

The official wiki pages give an answer to that:

[...] 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. [...]

Furthermore, you could try an ANALYZE to rebuild the info for the query planer.

You should get a better performance using COUNT(an uniquly indexed field) but if this is very big, a seq scan is the only way to do it.

If you need very quick numbers and are not afraid of querying the schema, you can do the following

SELECT reltuples FROM pg_class WHERE oid = 'your_table'::regclass

But don't rely on this values as it is only an "estimated" (although often the exact) number of tuples in the table.

Yes, the large offset is a problem. MySQL does early row lookups, which do not allow throwing data away as easy as one imagines. MySQL retrieves the whole records as it processes the database and each record may be of variable length. Throwing rows away afterwards becomes as cumbersome as throwing each single row away. It's not like "throw away x*y bytes".

There are tricks to force MySQL into late row lookup, which means process the index and do all operations concerning that, than retrieve the row. This offers the possibility to throw rows away much faster.

See

• this stackoverflow thread for an easy answer
• or this page for in-depth info.