Postgresql extremely slow count (with index, simple query) [duplicate]

Question

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)
   ->  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
         ->  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)
   ->  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
         ->  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)?

Answer 1

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 “dead tuples” 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 “visibility map” 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.

Answer 2

TL;DR I have solved by running this command:

vacuum analyze subscriptions;

After that command the queries take only ~1s instead of ~17s. For a detailed explanation see @Laurenz answer.

Now I run the autovacuum more frequently using these settings in postgresql.conf:

autovacuum_vacuum_scale_factor = 0.01
autovacuum_analyze_scale_factor = 0.01

Update: the above settings are not enough as the table grows in size (because as it grows, the vacuum becomes less frequent). For very large tables (i.e. 10M+ records), I have used this:

ALTER TABLE subscriptions SET (autovacuum_vacuum_scale_factor = 0, autovacuum_analyze_scale_factor = 0,  autovacuum_vacuum_threshold = 10000, autovacuum_analyze_threshold = 10000);

Answer 3

The reason vacuuming your table worked is that it enabled an index-only scan to be used efficiently. Unfortunately, autovacuum's scheduler was only designed to handle space-reuse and wraparound prevention, it never got updated to handle the needs of index-only scans when that feature was added. It isn't really clear how to update it in a generic way to take IOS into account.

If your table is insert-only or insert-mostly, then I would just set up a cron job (or the scheduler of your choice) to vacuum the table every now and then at night or weekends or whenever you have a non-busy time, rather than trying to get autovac to do it for you. Autovacuum settings has no knobs to twist which are sensitive to the number of insertions. (autoanalyze does, but that doesn't help you.)

If the table is dirtied mostly by updates rather than insertions and those updates are randomly scattered throughout the table, then you can set the table's autovacuum_vacuum_scale_factor to zero, and set autovacuum_vacuum_threshold to about 1/10 of the number of blocks in the table (every update dirties about one block, and you want to keep it >90 all visible, maybe even more).

Answer 4

You've solved your problem, that's great. Recently, I got turned on to the benefits of getting estimated counts rather than "perfect" counts, in cases where it's slow, despite any cleanup efforts you might take. Here's a function adapted ("stolen") from a fine blog post by Laurenz Albe:

DROP FUNCTION IF EXISTS api.row_count_estimate (text);

CREATE OR REPLACE FUNCTION api.row_count_estimate (query text)
 RETURNS bigint
AS $$
DECLARE
   plan jsonb;
BEGIN
   EXECUTE 'EXPLAIN (FORMAT JSON) ' || query INTO plan;
   RETURN (plan->0->'Plan'->>'Plan Rows')::bigint;
END;
$$ LANGUAGE plpgsql;

ALTER FUNCTION api.row_count_estimate (text) OWNER TO user_bender;

And something similar for views:

DROP FUNCTION IF EXISTS api.view_count_estimate (regclass);

CREATE FUNCTION api.view_count_estimate(view_id regclass) RETURNS bigint 
AS $$
DECLARE
   plan jsonb;
BEGIN
   EXECUTE 'EXPLAIN (FORMAT JSON) ' || pg_get_viewdef(view_id) INTO plan;
   RETURN (plan->0->'Plan'->>'Plan Rows')::bigint;
END;
$$ LANGUAGE plpgsql;

ALTER FUNCTION api.view_count_estimate (regclass) OWNER TO user_bender;

I've got nothing for materialized views.

[Edit] Ha! Found a bug in my calling code just writing this up. Here's how to call the row estimate:

select * from row_count_estimate('select * from data.scan');

Here is how not to call it:

select * from row_count_estimate('select count(*) from data.assembly');

The second version is asking for an estimate of how many rows count(*) will return. It will return 1, which isn't what we want to know.