Postgresql extremely slow count (with index, simple query)
Fetching and counting 5 million rows is slow business.

There are two problems:


The bitmap heap scan is taking longer than necessary, because work_mem is so small that it cannot contain a bitmap with one bit per table row.

It then degrades to storing a bit per 8KB block, which leads to more heap fetches in the bitmap heap scan phase to sort out the false positives.

So raising work_mem will speed up the execution, but according to your comments not a lot.
Rather than consulting the table itself, which is slow, PostgreSQL could also read only one of the indexes, because they contain all the required data.

That would be much faster, so why does PostgreSQL not do that?

The explanation is that PostgreSQL tables and indexes contain row versions (tuples) that are visible and tuples that are not visible (because they have been deleted or updated and not vacuumed yet). These &ldquo;dead tuples&rdquo; are required for PostgreSQL multi-versioning implementation.

Now the information which tuples are visible and which are not is only stored in the table (the heap), not in the index. So PostgreSQL has to consult the heap to figure out which tuples to count and which not.

If you run VACUUM on the table, PostgreSQL will update a nice thing called the &ldquo;visibility map&rdquo; that contains one bit per 8KB block that indicates if all tuples in the block are visible to everyone or not.

If the visibility map has a block as all-visible, the index scan doesn't have to consult the table to determine if it can see a tuple in the block or not. If that works for most tuples, you get an index only scan which is obviously much faster.


So you should see that the table is vacuumed often enough.

For that, make autovacuum run faster and more often on this table:

ALTER TABLE subscriptions SET (
   autovacuum_vacuum_scale_factor = 0.05,
   autovacuum_vacuum_cost_delay = 2
);


You would be even faster if you had an index on only project_id.