Slow PostgreSQL sequential scans on RDS?

Question

I have an RDS PostgreSQL instance that's running simple queries, much slower than I would expect - particularly sequential scans, like copying a table or counting a table.

Eg. create table copied_table as (select * from original_table) or select count(*) from some_table

Running count(*) on a 30GB table takes ~15 minutes (with indexes, immediately following a vaccuum).

It's an RDS db.r3.large, 15 GB memory, 400GB SSD. Watching the metrics logs, I've never seen Read IOPS exceed 1,400 and it's usually around 500, well below my expected base.

Configuration: work_mem: 2GB, shared_buffers: 3GB, effective_cache_size: 8GB wal_buffers: 16MB, checkpoint_segments: 16

Is this the expected timing? Should I be seeing higher IOPS?

Answer 1

There is not much you can do around plain count queries like that in Postgres, except in 9.6 that implemented parallel sequential scans, which is not available yet in RDS.

Event though, there is a some tips that you can find here. Generally, it's recommended to try to make Postgres to use Index Only Scan, by creating indexes and it's columns in the projection.

SELECT id FROM table WHERE id > 6 and id <100;
-- or
SELECT count(id) FROM table ...

Table should have an index on that column.

The queries that you exposed as example, won't avoid the sequential scan. For the CREATE TABLE, if you don't care about the order in the table, you can open a few backends and import in parallel by filtering by a key range. Also, the only way to speed up this on RDS is increasing IOPs.