How to optimize count(*) using only foreign key index on PostgreSQL

Question

I have a web app that extensively uses data tables that connect to the database to fetch data.
The problem though, it always needs first to count the total results before it could paginate the data by means of limit and offset

Is there any type of special indexes or some configurations to always force the count to be calculated purely by index without relying on the end table? because now, it seems, it ignores the index when querying for the count.

Answer 1

PostgreSQL can only use an index to count the number of rows if the table has been vacuumed recently so that most table blocks are marked "all visible" in the visibility map. Otherwise it has to inspect the table to check ifbthe row is visible or not, and then an index scan is more efficient.

I think that the solution is not to count the rows. For a cheap estimate, use pg_class.reltuples. For a more in-depth treatment of the topic, read my blog.