Re: Very poor read performance, query independent

Hmm - how are you measuring that sequential scan speed of 4MB/s? I'd
recommend doing a very simple test e.g, here's one on my workstation -
13 GB single table on 1 SATA drive - cold cache after reboot, sequential
scan using Postgres 9.6.2:

bench=# EXPLAIN SELECT count(*) FROM pgbench_accounts;
QUERY PLAN
------------------------------------------------------------------------------------
Aggregate (cost=2889345.00..2889345.01 rows=1 width=8)
-> Seq Scan on pgbench_accounts (cost=0.00..2639345.00
rows=100000000 width=0)
(2 rows)

bench=# SELECT pg_relation_size('pgbench_accounts');
pg_relation_size
------------------
13429514240
(1 row)

bench=# SELECT count(*) FROM pgbench_accounts;
count
-----------
100000000
(1 row)

Time: 118884.277 ms

So doing the math seq read speed is about 110MB/s (i.e 13 GB in 120
sec). Sure enough, while I was running the query iostat showed:

Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz
avgqu-sz await r_await w_await svctm %util
sda 0.00 0.00 926.00 0.00 114.89 0.00
254.10 1.90 2.03 2.03 0.00 1.08 100.00

So might be useful for us to see something like that from your system -
note you need to check you really have flushed the cache, and that no
other apps are using the db.

regards

Mark

On 12/07/17 00:46, Charles Nadeau wrote:
> After reducing random_page_cost to 4 and testing more, I can report
> that the aggregate read throughput for parallel sequential scan is
> about 90MB/s. However the throughput for sequential scan is still
> around 4MB/s.
>