Postgresql extremely slow count (with index, simple query) I need to run these simple queries on a table with millions of rows:

SELECT COUNT(*) FROM "subscriptions" WHERE "subscriptions"."project_id" = 123;


SELECT COUNT(*) FROM "subscriptions" WHERE "subscriptions"."project_id" = 123 AND "subscriptions"."trashed_at" IS NULL;


The count result for both queries, for project 123, is about 5M.

I have an index in place on project_id, and also another index on (project_id, trashed_at):

"index_subscriptions_on_project_id_and_created_at" btree (project_id, created_at DESC)
"index_subscriptions_on_project_id_and_trashed_at" btree (project_id, trashed_at DESC)


The problem is that both queries are extremely slow and take about 17s each.

These are the results of EXPLAIN ANALIZE:

      QUERY PLAN                                                                                      
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=2068127.29..2068127.30 rows=1 width=0) (actual time=17342.420..17342.420 rows=1 loops=1)
   -&gt;  Bitmap Heap Scan on subscriptions  (cost=199573.94..2055635.23 rows=4996823 width=0) (actual time=1666.409..16855.610 rows=4994254 loops=1)
         Recheck Cond: (project_id = 123)
         Rows Removed by Index Recheck: 23746378
         Heap Blocks: exact=131205 lossy=1480411
         -&gt;  Bitmap Index Scan on index_subscriptions_on_project_id_and_trashed_at  (cost=0.00..198324.74 rows=4996823 width=0) (actual time=1582.717..1582.717 rows=4994877 loops=1)
               Index Cond: (project_id = 123)
 Planning time: 0.090 ms
 Execution time: 17344.182 ms
(9 rows)


      QUERY PLAN                                                                                      
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=2047881.69..2047881.70 rows=1 width=0) (actual time=17557.218..17557.218 rows=1 loops=1)
   -&gt;  Bitmap Heap Scan on subscriptions  (cost=187953.70..2036810.19 rows=4428599 width=0) (actual time=1644.966..17078.378 rows=4994130 loops=1)
         Recheck Cond: ((project_id = 123) AND (trashed_at IS NULL))
         Rows Removed by Index Recheck: 23746273
         Heap Blocks: exact=131144 lossy=1480409
         -&gt;  Bitmap Index Scan on index_subscriptions_on_project_id_and_trashed_at  (cost=0.00..186846.55 rows=4428599 width=0) (actual time=1566.163..1566.163 rows=4994749 loops=1)
               Index Cond: ((project_id = 123) AND (trashed_at IS NULL))
 Planning time: 0.084 ms
 Execution time: 17558.522 ms
(9 rows)


What is the problem?

What can I do to improve the performance (i.e. count in a few seconds)?
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.