Slow sequential PostgreSQL scan in RDS?

Question

I have a PostgreSQL RDS instance that runs simple queries, much slower than I expected - in particular, sequential checks such as 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(*) in a 30 GB table takes ~ 15 minutes (with indexes, immediately after vaccination).

This is RDS db.r3.large, 15 GB of memory, 400 GB of SSD. Watching the performance logs, I have never seen. Read IOPS exceeds 1,400, and usually around 500, well below my expected base.

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

Is this the expected deadline? Should I see higher IOPS?

Answer 1

There is not much that you can do in such simple calculations as in Postgres, except 9.6, which implemented parallel sequential scanning, which is not yet available in RDS.

An event, though there are a few tips that you can find here . As a general rule, it is recommended that you try to get Postgres to use Indexing Only by creating indexes and columns in the projection.

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

The table must have an index in this column.

The queries you set as an example will not prevent sequential scans. For CREATE TABLE, if you do not care about the order in the table, you can open several backends and import in parallel, filtering by a range of keys. In addition, the only way to speed it up on RDS is to increase IOP.