PostgreSQL extremely slow counts

I'm experiencing extremely slow count(*) speeds, inside a table with 5845897rows, counting the number of rows takes around 2 minutes (113832.950 ms).

EXPLAIN ANALYZE SELECT COUNT(*) FROM campaigns;

QUERY PLAN
--------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=1295067.02..1295067.03 rows=1 width=8) (actual time=113830.691..113830.691 rows=1 loops=1)
   ->  Gather  (cost=1295066.80..1295067.01 rows=2 width=8) (actual time=113830.603..113832.899 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=1294066.80..1294066.81 rows=1 width=8) (actual time=113828.327..113828.328 rows=1 loops=3)
               ->  Parallel Seq Scan on campaigns  (cost=0.00..1287889.84 rows=2470784 width=0) (actual time=2.560..113139.782 rows=1948632 loops=3)
 Planning Time: 0.130 ms
 Execution Time: 113832.950 ms
(8 rows)

After vacuuming the table, I got the following results, but the query times remaining unchanged.

SELECT relname AS TableName,n_live_tup AS LiveTuples,n_dead_tup AS DeadTuples, last_autovacuum AS Autovacuum, last_autoanalyze AS Autoanalyze FROM pg_stat_user_tables;

tablename          | livetuples | deadtuples |          autovacuum           |          autoanalyze
-----------------------------+------------+------------+-------------------------------+-------------------------------
| campaigns                  |    5848489 |      84122 | 2020-11-21 15:27:54.309192+00 | 2020-11-21 15:29:38.547147+00

I would expect that this count would be quite quick even accounting for the size of the database.

The database is AWS RDS PostgreSQL 11.8, with 16GB of ram.

Update 1

Machine class: db.m4.xlarge - vCPU: 4, ECU: 13, RAM: 16GB, Storage: General Purpose (SSD) with 440GB

Repeating the query now with the (ANALYZE, BUFFERS) and io timings enabled:

EXPLAIN (ANALYZE, BUFFERS) SELECT COUNT(*) FROM campaigns;

```
QUERY PLA

Essentially all the time is spent waiting for data to be read from disk. Indeed, 3 times as much time is spent waiting as the total time, because you have 3 processes to do the waiting simultaneously. You might want to turn off parallelization, as it is unlikely to actually be helpful here. It might actually be harmful, and it at least makes the plans harder to interpret.

Since you are reading about 10GB of data but have 16GB of RAM, you might hope that it would stay in cache and so be faster in the future. Have you tried running this repeatedly back to back to see if it does get faster? Is this the only large table you have or is it just one of several? Maybe you could try pg_prewarm on it.

now running the query with the sufficient IOPS we get better results, query time came down from 124552.079 ms to 26939.749 ms, a ~78% performance increase, but even so, 26 seconds for the query to run is quite a lot.

I don't know what you mean by 'sufficient IOPS'. If you just mean you have enough credits to get the burst rate of 3000 IOPS (rather than the base rate of 3*440 = 1320) you would expect 1279489/3000 = 426 seconds, which is way slower than you are actually seeing. So maybe most of your data is cached in RAM, but the part that is not cached is still enough to dominate your run time. But anyway, essentially all your time is still spent waiting for IO, so the IOPS is empirically not sufficient for something else to have become the bottleneck.

Your tuples seem to average 1792 bytes each (1279489*8k/5845897). This is very large, and doesn't even include the size which has been pushed off to TOAST. Is this size sensible given your data? Maybe you have a lot of bloat and your tuples aren't really this big. Given that large size, if the table is well vacuumed then doing an index-only scan should be able to count the tuples with far less IO. Do you have a qualifying index? If you vacuum the table and set enable_seqscan to off, what execution plan do you get? You could encourage index-only-scan by lowering random_page_cost to be close to seq_page_cost, which is probably a reasonable thing to do anyway if you are on SSD.

But this doesn't seem like the type of query you need to run all that often. Why do you care much about its performance? Are you taking it as a bellwether for performance in general? If so, that is probably a mistake.

StackExchange Database Administrators Q#280095, answer score: 3